You need to sign in to do that
Don't have an account?
Juan Romero
Get Query response into a dataset
Hi guys,
Does anyone out there have a piece of code to insert the results of a query into a dataset?
Thanks!
function readOnly(count){ }
You need to sign in to do that
Don't have an account?
Hi guys,
Does anyone out there have a piece of code to insert the results of a query into a dataset?
Thanks!
Hi Juan,
Ask and you shall recieve.
I'll give you some code to put the response into a DataTable, you can then add the DataTable to a DataSet.
This piece of code could be further enhanced to incorporate meta-data from the describe call to type the DataColumns appropriately. This will allow for proper formatting to be applied to numerical values and currencies. Using the describe results you can also set column captions to the label values for each field as well as handle the custom fields a little easier.
static public DataTable GetDataTable(Object results)
{
DataTable table = new DataTable();
table.TableName = "sforce";
System.Xml.XmlNode[] nodes = (System.Xml.XmlNode[])results;
//create the columns in the datatable
foreach (System.Xml.XmlNode resNode in nodes)
{
foreach (System.Xml.XmlNode rowNode in resNode.ChildNodes)
{
foreach (System.Xml.XmlNode columnNode in rowNode.ChildNodes)
{
if (table.Columns[columnNode.Name] == null)
{
DataColumn dtc = table.Columns.Add(columnNode.Name);
if (columnNode.Attributes.Item(0).InnerText == "xsd:double")
dtc.DataType = typeof(Double);
if (columnNode.Name.StartsWith("cf"))
//look up the lable of the field
dtc.Caption = LookupCFLabel(columnNode.Name);
}
}
}
}
//loop through the rows and add them to the datatable
foreach (System.Xml.XmlNode resNode in nodes)
{
foreach (System.Xml.XmlNode rowNode in resNode.ChildNodes)
{
if (rowNode is System.Xml.XmlElement)
{
DataRow row = table.NewRow();
foreach (System.Xml.XmlNode columnNode in rowNode.ChildNodes)
{
row[columnNode.Name] = columnNode.InnerText;
}
//add the row to the datatable
table.Rows.Add(row);
}
}
}
return table;
}
And in VB:
Public Function GetDataTable(ByVal results As Object) As DataTable
Dim table As New DataTable
Dim nodes() As System.Xml.XmlNode = CType(results, System.Xml.XmlNode())
'create the columns in the datatable
For Each resNode As System.Xml.XmlNode In nodes
For Each rowNode As System.Xml.XmlNode In resNode.ChildNodes
For Each columnNode As System.Xml.XmlNode In rownode.ChildNodes
If table.Columns(columnnode.Name) Is Nothing Then table.Columns.Add(columnNode.Name)
Next
Next
Next
For Each ds As Data.DataColumn In table.Columns
Debug.WriteLine(ds.ColumnName)
Next
'loop through the rows and add them to the datatable
For Each resNode As System.Xml.XmlNode In nodes
For Each rowNode As System.Xml.XmlNode In resnode.ChildNodes
If TypeOf rowNode Is System.Xml.XmlElement Then
Dim row As DataRow = table.NewRow()
For Each columnNode As System.Xml.XmlNode In rowNode.ChildNodes
row(columnNode.Name) = columnNode.InnerText
Next
'add the row to the datatable
table.Rows.Add(row)
End If
Next
Next
Return table
End Function
Hi,
I'm pretty new at this so... here goes,
From the above I can see how to get the response into the dataset. Thanks.
Now, where can I find info on how to actually get the results from the query so that I can put them into the dataset? Could you provide an example script like the one above for an Asp.Net page, or point me somewhere?
Thanks.
Message Edited by eLe on 10-29-2003 03:22 PM
Hi eLe,
Try downloading the query sample from the samples section of the sforce web site.
Thanks for the quick reply Dave, you're great!
I have downloaded the examples you provide, but because I am new at this I'm having a little trouble turning the console application into a working asp.net web page. Any other materials or docs I could take a look at?
My goal is to put together a page with a datagrid that displays account name, phone, fax, and address, filtered by account type, city, and state.
I would appreciate any and all help.
Thanks.
Take a look at the library I posted in the General area. It takes the query as text and generates XML output that goes right into a dataset. Here's the total ASP, without the command string and sql:
Armeta.Sfdc.SfdcClient.SfdcConnection conn = new Armeta.Sfdc.SfdcClient.SfdcConnection(CONNECTION_STRING);
Armeta.Sfdc.SfdcClient.SfdcCommand com = conn.CreateCommand();
com.CommandText = SQL;
com.CommandType = Armeta.Sfdc.SfdcClient.SfdcCommandType.XMLSQLText;
//-- Populate the dataset and display
DataSet dataSet1 = new DataSet();
dataSet1.ReadXml(com.ExecuteXmlReader());
DataGrid1.DataSource = dataSet1.Tables[0];
DataGrid1.DataBind();
Thanks, I appreciate the reply. Do you have the source code available? I would actually like to understand how the connection and retreival of data is done.
Thanks again.
Here you go....
Dim describeSObjectResult As DescribeSObjectResult = binding.describeSObject(sObject)
Dim fields() As Field = describeSObjectResult.fields
Dim oDCol As System.Data.DataColumn
If Not fields Is Nothing Then
Dim dt As New DataTable(sObject)
For i As Integer = 0 To fields.GetUpperBound(0)
Dim Field As Field = fields(i)
oDCol = New DataColumn
oDCol.ColumnName = Field.name
Try
Select Case Field.type
Case fieldType.int
oDCol.DataType = System.Type.GetType("System.Int32")
Case fieldType.boolean
oDCol.DataType = Type.GetType("System.Boolean")
Case fieldType.currency
oDCol.DataType = Type.GetType("System.Decimal")
Case fieldType.double
oDCol.DataType = Type.GetType("System.Double")
Case fieldType.date
oDCol.DataType = Type.GetType("System.DateTime")
Case fieldType.datetime
oDCol.DataType = Type.GetType("System.DateTime")
Case Else
oDCol.DataType = Type.GetType("System.String")
End Select
Catch ex As Exception
oDCol.DataType = Type.GetType("System.String")
End Try
dt.Columns.Add(oDCol)
Next
Dim ds As New DataSet("ds" & sObject)
ds.Tables.Add(dt)
ds.WriteXmlSchema("c:\sfDs\" & "ds" & sObject & ".xsd")
ds.Dispose()
End If
Or, better yet, check out this open source project manged by Karl Houseknecht at Suntrust bank.
http://sourceforge.net/project/showfiles.php?group_id=96634&package_id=135890
It's an implementation of a .net managed data provider.