Populating and Editing a DataGrid in ASP.NET

I will show here , how to develop DataGrid, which is able to retrieve data from joined tables and editable.


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.

DBGrid1.gif

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)

DBGrid2.jpg 

Figure 2:

Column Edit 
 

type

Button/Edit,Update/Cancel(See Fig3)

 
DBGrid3.jpg

Figure 3:

Column PID
 

type Bound Column
Readonly yes

DataField

 Product ID

 

 


Column Quantity
 

typeBound 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.