function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Juan RomeroJuan 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!

DevAngelDevAngel

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

 

 

eLeeLe

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

DevAngelDevAngel

Hi eLe,

Try downloading the query sample from the samples section of the sforce web site.

eLeeLe

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.

Barry LBarry L

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();

eLeeLe

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.

Donnie PDonnie P

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

DevAngelDevAngel

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.