interactive | editorial | code | resource 
Demonstrations > DataGrid demo
 

Using DbCombo with a DataGrid

In this example we show you how to easily use DbCombo to edit data in a DataGrid.

OrderIDOrderDateCustomerEmployeeEditColumn
1024804-07-96 Vins et alcools Chevalier Buchanan, Steven Edit
1024905-07-96 Toms Spezialitäten Suyama, Michael Edit
1025008-07-96 Hanari Carnes Peacock, Margaret Edit
1025108-07-96 Victuailles en stock Leverling, Janet Edit
1025209-07-96 Suprêmes délices Peacock, Margaret Edit
1025310-07-96 Hanari Carnes Leverling, Janet Edit
1025411-07-96 Chop-suey Chinese Buchanan, Steven Edit
1025512-07-96 Richter Supermarkt Dodsworth, Anne Edit
1025615-07-96 Wellington Importadora Leverling, Janet Edit
1025716-07-96 HILARION-Abastos Peacock, Margaret Edit
1025817-07-96 Ernst Handel Davolio, Nancy Edit
1025918-07-96 Centro comercial Moctezuma Peacock, Margaret Edit
1026019-07-96 Ottilies Käseladen Peacock, Margaret Edit
1026119-07-96 Que Delícia Peacock, Margaret Edit
1026222-07-96 Rattlesnake Canyon Grocery Callahan, Laura Edit
1026323-07-96 Ernst Handel Dodsworth, Anne Edit
1026424-07-96 Folk och fä HB Suyama, Michael Edit
1026525-07-96 Blondesddsl père et fils Fuller, Andrew Edit
1026626-07-96 Wartian Herkku Leverling, Janet Edit
1026729-07-96 Frankenversand Peacock, Margaret Edit
1 2 3 4 5 6 7 8 9 10 ...

You'll notice that this example uses the Northwind database, so you can set up the demo with as little hassle as possible. However, DbCombo is more useful when working with large numbers of records. To this end, we've added about 2,500 random names, and about 500 random companies to the database.

You'll also notice that we haven't included any code to save the changed data to the database. This is to keep the demo as simple as possible. The code will be virtually identical to the code that would be written is DbCombo was not present.

The Code:

The code for this page follows (see the bottom of the page for a link to the entire code listing without interruptions):

First we define our page, and import some namespaces we will be using. Note that we have specified AutoEventWireup="true". This is important as we will be using the Page_Load function. 

<%@ Page 
    Language="c#" 
    AutoEventWireup="true" %>
    
<%@ Register 
    TagPrefix="DbCombo" 
    Namespace="Cambro.Web.DbCombo" 
    Assembly="Cambro.Web.DbCombo" %>
    
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

This is the start of the page code (something like this is inserted by Visual Studio).

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > 
<html>
<head><title>DemoDataGrid</title></head>
<body MS_POSITIONING="GridLayout" 
    XMLNS:DbCombo="http://schemas.cambro.net/dbcombo">
<form id="DemoDataGrid" method="post" runat="server">

Here is our DataGrid. We set up a few standard properties, and include a BoundColumn for the OrderID and one for the OrderDate. 

    <asp:DataGrid Runat="server" ID="Datagrid1" AutoGenerateColumns="false" 
            AllowPaging="True" OnPageIndexChanged="Dg1OnPageIndexChanged" 
            PagerStyle-Mode="NumericPages" PageSize="20"
            OnEditCommand="Dg1OnEdit" OnCancelCommand="Dg1OnCancel" 
            OnUpdateCommand="Dg1OnUpdate">
        <Columns>
            <asp:BoundColumn DataField="OrderID" HeaderText="OrderID">
            </asp:BoundColumn>
            <asp:BoundColumn DataField="OrderDate" HeaderText="OrderDate" 
                DataFormatString="{0:dd-MM-yy}">
            </asp:BoundColumn>

Our DbCombo edit columns are TemplateColumns. 

            <asp:TemplateColumn HeaderText="Customer">

The ItemTemplate simply uses the standard DataBinder.Eval function to output the plain text of the column we want displayed. 

                <ItemTemplate>
                    <%#DataBinder.Eval(Container.DataItem,"CompanyName")%>
                </ItemTemplate>

The EditItemTemplate includes a DbCombo tag. Because there are two DbCombo’s with different ServerMethods, we specify the ServerMethod for each one. 

                <EditItemTemplate>
                    <DbCombo:DbCombo 
                        runat="server" ID="CustomerDbCombo"
                        ServerMethod="DbComboGetCustomers"

The initial Text and Value properties of the DbCombo are set up with the DataBinder.Eval function. Note we use single quotes when DataBinding to a property. 

                        Text='<%#DataBinder.Eval(Container.DataItem,"CompanyName")%>' 
                        Value='<%#DataBinder.Eval(Container.DataItem,"CustomerID")%>'

Here we set the initial query to be used when the page loads. In this example we use the first character of the CompanyName column. This will give appropriate results for this data set. Note we also set ReQueryOnLoad to true, so that DbCombo performs an automatic search when the page initially loads. 

                        ReQueryText='<%#DataBinder.Eval(Container.DataItem,"CompanyName").ToString().Substring(0,1)%>' 
                        ReQueryOnLoad="true">
                    </DbCombo:DbCombo>
                </EditItemTemplate>
            </asp:TemplateColumn>

In the Employee table the first and last names are in different columns. We must concatenate two columns together to give the required output. 

            <asp:TemplateColumn HeaderText="Employee">
                <ItemTemplate>
                    <%#DataBinder.Eval(Container.DataItem,"LastName") + ", " + DataBinder.Eval(Container.DataItem,"FirstName")%>
                </ItemTemplate>
                <EditItemTemplate>
                    <DbCombo:DbCombo 
                        runat="server" ID="EmployeeDbCombo"
                        ServerMethod="DbComboGetEmployees"
                        Text='<%#DataBinder.Eval(Container.DataItem,"LastName") + ", " + DataBinder.Eval(Container.DataItem,"FirstName")%>' 
                        Value='<%#DataBinder.Eval(Container.DataItem,"EmployeeID")%>' 
                        ReQueryText='<%#DataBinder.Eval(Container.DataItem,"LastName").ToString().Substring(0,1)%>' 
                        ReQueryOnLoad="true">
                    </DbCombo:DbCombo>
                </EditItemTemplate>
            </asp:TemplateColumn>

This is the standard EditCommandColumn. 

            <asp:EditCommandColumn 
                EditText="Edit" 
                CancelText="Cancel" 
                HeaderText="EditColumn" 
                UpdateText="Update">
            </asp:EditCommandColumn>
        </Columns>
    </asp:DataGrid>
</form>
</body>
</html>
<script runat="server">

These functions are all completely standard when setting up a DataGrid. 

    private void Page_Load(object sender, System.EventArgs e)
    {
        if (!Page.IsPostBack)
            bindGrid();
    }
    public void Dg1OnEdit(object o, 
        DataGridCommandEventArgs e)
    {
        dg1.EditItemIndex=e.Item.ItemIndex;
        bindGrid();
    }
    public void Dg1OnCancel(object o, 
        DataGridCommandEventArgs e)
    {
        dg1.EditItemIndex=-1;
        bindGrid();
    }
    public void Dg1OnUpdate(object o, 
        DataGridCommandEventArgs e)
    {
        dg1.EditItemIndex=-1;
        bindGrid();
    }
    public void Dg1OnPageIndexChanged(object o, 
        DataGridPageChangedEventArgs e)
    {
        dg1.EditItemIndex=-1;
        dg1.CurrentPageIndex=e.NewPageIndex;
        bindGrid();
    }

Our bindGrid function binds the DataGrid to the database. Note we perform INNER JOIN’s in the SQL query to get the relevant text fields from linked tables. Note you may have to change the SqlConnection to run this example. 

    void bindGrid()
    {
        DataSet dataset=new DataSet();
        SqlConnection conn = new SqlConnection(
            "Data Source=(local);" +
            "Initial Catalog=Northwind;" +
            "Integrated Security=SSPI;");
            
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = new SqlCommand(@"
                SELECT * FROM Orders
                INNER JOIN Customers ON 
                    Orders.CustomerID = Customers.CustomerID 
                INNER JOIN Employees ON 
                    Orders.EmployeeID = Employees.EmployeeID 
                ORDER BY OrderDate", conn);
                
        adapter.Fill(dataset);
        conn.Close();
        dg1.DataSource=dataset;
        dg1.DataBind();
    }

This is the first ServerMethod. It’s completely standard – very similar to the ServerMethods used in other DbCombo examples. 

    [Cambro.Web.DbCombo.ResultsMethod(true)]
    public static object DbComboGetCustomers(
        Cambro.Web.DbCombo.ServerMethodArgs args)
    {
        DataSet dataset=new DataSet();
        SqlConnection conn = new SqlConnection(
            "Data Source=(local);" +
            "Initial Catalog=Northwind;" +
            "Integrated Security=SSPI;");
            
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = new SqlCommand(@"
                SELECT TOP "+args.Top+@" 
                    CompanyName AS DbComboText, 
                    CustomerID AS DbComboValue
                FROM Customers 
                WHERE CompanyName LIKE @Query 
                ORDER BY CompanyName", conn);
                
        adapter.SelectCommand.Parameters.Add(
            "@Query", args.Query+"%");

        adapter.Fill(dataset);
        conn.Close();
        return dataset;
    }

This is the second ServerMethod. Again it’s pretty standard. Note that it concatenates the first and last name columns before outputting as DbComboText. 

    [Cambro.Web.DbCombo.ResultsMethod(true)]
    public static object DbComboGetEmployees(
        Cambro.Web.DbCombo.ServerMethodArgs args)
    {
        DataSet dataset=new DataSet();
        SqlConnection conn = new SqlConnection(
            "Data Source=(local);" +
            "Initial Catalog=Northwind;" +
            "Integrated Security=SSPI;");
            
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = new SqlCommand(@"
                SELECT TOP "+args.Top+@" 
                    LastName+', '+FirstName AS DbComboText, 
                    EmployeeID AS DbComboValue
                FROM Employees 
                WHERE LastName+', '+FirstName LIKE @Query 
                ORDER BY LastName", conn);
                
        adapter.SelectCommand.Parameters.Add(
            "@Query", args.Query+"%");

        adapter.Fill(dataset);
        conn.Close();
        return dataset;
    }
</script>

DbCombo DataGrid demo code
Click here for the whole code listing without interruptions.


 
Demonstrations > DataGrid demo