Populating and Editing a DataGrid in ASP.NET

Abstract:

I will show here step-by-step, how to develop DataGrid, which is able to retrieve data from joined tables and editable. I have used classical MS-SQL Server database  northwind in my sample.

Figure 1:

Task:

I want to create an ASP.NET Page, which displays  products of an Order. I am going to use tables Order Details and Product to accumulate required Information. It must be also able update quantity of ordered products.

You need change only the class OrderGrid.strConnection to adapt the source code in your  system.

Implementation :

Create an ASP.NET Web  Project and place  the following webcontrols (I am giving only  the important controls) and create their event handlers.

Button
Id	Search
Eventhandler : Click	SearchClick

Textbox
Id	OrderIDid

DataGrid
Id	OrdGrid
Eventhandler : CancelCommand	CancelGrid
Eventhandler :  EditCommand	EditGrid
Eventhandler: UpdateCommand	UpdateGrid

Now  I want to create the columns  for the DataGrid.  In order to do that you must set  DataGrid property  AutoGenrateColumns=false. I use the Property Builder to create the columns (See Figure 2)

 

Figure 2:

Column Edit 
 

type	Button/Edit,Update/Cancel(See Fig3)

 

Figure 3:

Column PID
 

type	Bound Column
Readonly	yes
DataField	Product ID

 

 

Column Quantity
 

type	Bound Column
Readonly	No(Editable)
DataField	Quantity

 

Please see the source code to understand what each Event handler do, when the a certain event occurs.

Now I want to look a partial source code from the method PopulateGrid().

private void PopulateGrid()

{

    // Connect to the Database          

    SqlConnection objConnection = GetConnection();

    objConnection.Open();

    DataSet objDataSet = new DataSet("OD-Prod");

    SqlDataAdapter objDataAdapter = new SqlDataAdapter();

    //  Construct the SqlStatement             

    string strCmd = "SELECT OD.ProductID,OD.Quantity,P.ProductName  FROM [Order Details] OD, ";

    strCmd += " Products P WHERE OD.ProductID=P.ProductID AND  "; 

    if (OrderID.Text.Length > 0)

    {

        strCmd += "OD.OrderID = " + OrderID.Text;

    }

    else

    {

        objConnection.Close();

        return;

    } 

    SqlCommand selCommand = new SqlCommand(strCmd, objConnection);

    objDataAdapter.SelectCommand = selCommand;

    objDataAdapter.Fill(objDataSet, "JoinTable");

    OrdGrid.DataSource = objDataSet.Tables["JoinTable"];

    OrdGrid.DataBind(); 

}

Note: SqlDataAdapter will create Table with the Name "JoinTable"  according to the Sql Command, there fore you dont need to define it.