function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
EdgeLineEdgeLine 

Grid Control: sql datasource [connect] to salesforce in easy manner?

I realise this will come across as a very naive question. I wish to quickly and effectively utilize the inbuilt functionality of the asp.net GRID or 'sql data source' control which allows you to visually connect to a underlying sql server database.
 
Is it possible to somehow replace the sql server assignment below with a connection to salesforce? I have messed around with wsdl 'loginresult' etc but it is very unclear to me as to how to tie in the grid control quickly and easily to salesforce.  Here is snapshot of typical grid control source code:
 
Code:
<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
            SelectCommand="SELECT * FROM [Customer]"></asp:SqlDataSource>
    
    </div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CustomerID"
            DataSourceID="SqlDataSource1" Style="z-index: 100; left: 54px; position: absolute;
            top: 78px">
            <Columns>
                <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" InsertVisible="False"
                    ReadOnly="True" SortExpression="CustomerID" />
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
                <asp:BoundField DataField="CustomerSince" HeaderText="CustomerSince" SortExpression="CustomerSince" />
                <asp:BoundField DataField="CreditLimit" HeaderText="CreditLimit" SortExpression="CreditLimit" />
            </Columns>
        </asp:GridView>
    </form>
</body>
</html>

 
There is no clear example in the KB on how to do this.   Any straightword code examples or explanations would be very much appreciated.
 
 
 
Thanks
Edgeline
 
ps: Yes, I know I need to buy some .asp net books and teach myself before anyone offers me this advice again :)
 
Gareth DaviesGareth Davies
I don't think the approach you are taking will work.
 
If you want to do this you might consider setting up SQL Server to reflect SFDC using something like DBAmp.
Then SQLServer will integrate intp the GridControl for you.
 
We have done this integration in Datascope (see appexchanges) but have written an extensive set of libraries to make it work.
 
 
Gareth.
SuperfellSuperfell
You can't use SqlDataSource, but now that in .NET 2.0 the classes generated from the wsdl have properties, you should be able to databind your grid to then without too much trouble.

Message Edited by SimonF on 08-21-2006 02:47 PM
SuperfellSuperfell
I tried this, the regular databinding works fine (in .NET 2.0), e.g., if you have an asp:gridview control on your page called dg, then this code will run the query, and do a databind to display the grid table.

        sforce.QueryResult qr = svc.query("select id, name, accountNumber from Account");
        dg.DataSource = qr.records;
        dg.DataBind();

This is the standard object data binding support in .NET.
EdgeLineEdgeLine
Code:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using sForce;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        // Create service object for sforce 
        SforceService sfdc = new SforceService();
        // Invoke the login call and save results in LoginResult 
        LoginResult lr = sfdc.login("username", "password");
        // Reset the SOAP endpoint to the returned server URL 
        sfdc.Url = lr.serverUrl;
        // Create a new session header object 
        // Add the session ID returned from the login 
        sfdc.SessionHeaderValue = new SessionHeader();
        sfdc.SessionHeaderValue.sessionId = lr.sessionId;
        //queryString = "select id, name, accountNumber from Account";
        QueryResult qr = sfdc.query("select id, name, accountNumber from Account");
        dg.DataSource = qr.records;
        dg.DataBind();
    }

  
}

 
Thanks, Simon, I finally extracted data out of sales force using a .net visual grid control doing the databinding in the .cs file in the page load event.
 
That was extremly helpful to me as it gives me a visual connection point with the salesforce system. Some comments:
 
1. I don't understand the data grid control well enough but it seems to show extraneous columns despite only using the id and name in the 'select' clause...any ideas why? Is there way to 'force' the control to follow the columns specified in the select clause?
 
2. There is obviously a performance hit to use this control...it is slooow roughly 17 seconds to load
 
3. Strangely enough it only pulls out about 200 records...there must be some limit to the control?
 
Any further feedback appreciated. But, I think this is a greater starter example for those new to .net and the salesforce api.
 
Thanks
Edgeline
 
 
 
 
 
 
 
SuperfellSuperfell
By default the grid will show all the properties on teh object you bind to it, you can override this and show a limited subset by adding elements to the datagrid tag. (see the MSFT datagrid docs & samples)

Depending on what you query we'll reduce the # of rows returned to keep the response message size within managable limits for the client soap stack (if you have 1M rows, you don't want them all in one go). you can call queryMore to get the next set of rows (see the API docs & samples).
Mike LeachMike Leach
1. I don't understand the data grid control well enough but it seems to show extraneous columns despite only using the id and name in the 'select' clause...any ideas why? Is there way to 'force' the control to follow the columns specified in the select clause?

The <columns> XML attributes will override the default "show everything" behavior.


2. There is obviously a performance hit to use this control...it is slooow roughly 17 seconds to load.

Correct. Best practice is to asynchronously replicate and cache Salesforce records for use in low latency web sites.


3. Strangely enough it only pulls out about 200 records...there must be some limit to the control?

Correct. Saleforce will return records 200 at a time and indicate there are more records to be retrieved by setting the queryResult.done flag to false.

Here is one approach (in C#) to retrieving all records.


ArrayList remoteObjects = new ArrayList();
   sObject[] records = qr.records;
   
   if(records.Length > 0)
   {
    bool done = false;
    while(done == false)
    {
     for(int i=0; i < records.Length; i++)
      remoteObjects.Add(records[i]);
     
     done = qr.done;
     
     if(done == false)
     {
      qr = sForceService.queryMore(qr.queryLocator);
      records = qr.records;
     }
    }
   }
   
   sObject [] sObjects = new sObject[remoteObjects.Count];
   for(int i=0; i < remoteObjects.Count; i++)
    sObjects[i] = (sObject)remoteObjects[i];
   
   return sObjects;