Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Highlighted
Tuned Listener
Posts: 26
Registered: ‎06-28-2010

Binding External Data to a DataGrid

I have been looking for a way to bind and show external information inside of SalesLogix. I have not been able to pinpoint exactly what I need to do. Does anyone know if this is possible or how to go about doing this? It would be very help to a lot of people. Here is what I have so far in the load step for my form.

 

            System.Web.UI.WebControls.DataGrid dataGrid =
            (System.Web.UI.WebControls.DataGrid)form.formDataGrid.NativeControl;
            //System.Web.UI.WebControls.DataGrid dataGrid = new System.Web.UI.WebControls.DataGrid();
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            conn.ConnectionString =
            "data source=DATASOURCE ; initial catalog = CATALOG ;" +
            "Integrated Security = true; user id = UID ; password = PASSWORD";
            conn.Open();
            string commandString = "select * from tableX";
            System.Data.SqlClient.SqlCommand command =
            new System.Data.SqlClient.SqlCommand(commandString, conn);

            dataGrid.DataSource = command.ExecuteReader();
            dataGrid.DataBind();

 

Any help would be greatly appreciated!

 

 

Thanks,

 

Matthew

Highlighted
Tuned Listener
Posts: 26
Registered: ‎06-28-2010

Re: Binding External Data to a DataGrid

I actually have the solution to this problem and I will hopefully post it late this week if anyone is interested?

 

 

Matthew

Highlighted
Nickel Contributor
Posts: 57
Registered: ‎03-18-2010

Re: Binding External Data to a DataGrid

[ Edited ]

I'm curious to know your solution...

Libert Damien
Highlighted
Tuned Listener
Posts: 26
Registered: ‎06-28-2010

Re: Binding External Data to a DataGrid

Here are the steps that I took in order to get an external data source to bind to a datagrid with a datasource.

 

Step 1: Create an entity in sales logix as a new table with the fields that you want to pull in. I made an OrderQuote entity with the order number, date, amount, status and type. Create each field according to the type of data you want to put in it (string, date, double etc)

 

Step 2: Where ever you want to use this entity, create a new business rule in that entity. For example, my Order/Quote table will be located inside of my contact entity becuase each contact will have Orders and Quotes. Inside of this business rule create a new step which returns and object.

 

Step 3: Write the code for the business rule. This code will return a list of the entiy type that you had created in step 1. In my case this is an IOrderQuote. This business rule will sure as the "get by method" for binding your datagrid to your data source. So the next step in creating this is to set up your data connection, command strings and readers. Here is what mine looked like.

 

System.Collections.Generic.IList<IOrderQuote> list =   new System.Collections.Generic.List<IOrderQuote>();

 

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
     
conn.ConnectionString =  "data source=XXXXX ; initial catalog = XXXXX ; Integrated Security = true; user id = XXXXX ; password = XXXXX";
                
conn.Open();

              
string custnumber = contact.UserField5.Trim(), name = contact.Name.Trim();
              
string commandString = "Select * from openquery (wcw_new, 'Select order_nbr, order_type, order_date, order_status, ord_product_amt from ordersummary where cust_nbr = ''" + custnumber + "'' and contact_name = ''" + name + "''')";
                
                System.Data.SqlClient.SqlCommand command =
                new System.Data.SqlClient.SqlCommand(commandString, conn);
    

                System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader();

 

So now you have a reader set up to read all of the data that comes back from your command. It would be a good idea to get this part set  up outside of saleslogix to make sure that everything works properly and you can acutally display the data to avoid having to trouble shoot. Next you need to reader the data. You will create a new entiy for each of the records that the reader brings back, and then set their properties, the fields that you had created before.

 

while (reader.Read())
                {
                    //set up a new entity
                    Sage.Entity.Interfaces.IOrderQuote quote =
                    Sage.Platform.EntityFactory.Create<IOrderQuote>();
                    
                    //set the order number
                    quote.OrderNumber = (int)reader[0];
                    
                    //set the type to quote or order depeneding on if it is a q or not
                    quote.Type = (string)reader[1];
                    if (quote.Type == "Q")
                        quote.Type = "Quote";
                    else
                        quote.Type = "Order";
    
                    //set the date
                    quote.Date = (DateTime)reader[2];
    
                    //try to set the status, if error status = unknown
                    try
                    {
                    quote.Status = (string)reader[3];
                    }
                    catch (Exception e)
                    {
                    quote.Status = "Unknown";
                    }
    
                    // set the status to completed or finished or if nothing unknown
                    if (quote.Status == "C")
                        quote.Status = "Completed";
                    else if (quote.Status == "F")
                        quote.Status = "Finsihed";
                    else if (quote.Status == "L")
                        quote.Status = "Lost";
                    else
                        quote.Status = "Unknown";
    
                    //set the amount
                    quote.Amount = (decimal)reader[4];
                    
                    
                    //quote.Save();
                    
                    //add the current quote
                    list.Add(quote);
                }

 

So as you can see from about you create a new instance of your entity, set all of its properties and add it to a list of the entity type which was created at the top of code. I would suggest doing some error handling to hand instances when nothing is returned. I just instert a blank record if there is nothing. then you just return the list so in my case my definition is:

 

public static void GetOrderQuotesStep( IContact contact, out object result)

{}

 

So I just need to set result to the list and it will take care of this for me.

 

Step 4: Full compile.

 

Step 5: Create a new form with a data source. Set the data source "Get by method property" to the business rule that you created and the "entity type" to the type of entity that you had created in step one.

 

Step 6: Now set up data grid to pull from the data source. The columns should show up and you should be able to access them.

 

Step 7: You're done. If something does not work. Save, exit, full compile and check it again.

 

 

Hope this was helpful and if anyone has any questions feel free to ask me. There are lots more things that you can do with this such as save the data, which I would suggest being careful with because each time the grid is pulled up it will create a new set of unique records so you will have tons of multiple records.

 

Good luck!

 

Matthew

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Highlighted
Nickel Contributor
Posts: 57
Registered: ‎03-18-2010

Re: Binding External Data to a DataGrid

Thanks.
Libert Damien
Highlighted
Tuned Listener
Posts: 26
Registered: ‎06-28-2010

Re: Binding External Data to a DataGrid

Hope it helps.