For the most part we create a datagrid at design time by just adding a Datagrid control to a web form. One of the disadvantages of this as a developer is that for each report a user requests, a new Web Form needs to be created. Before long your project has hundreds of pages, which becomes in due course a maintenance nightmare.
In this article, we are going to create a simple web form, and create a datagrid dynamically using code. We are then going to add columns to this datagrid based on the results returned via a SQL stored procedure. This dynamically created datagrid will render the results of any SQL Query or stored procedure.
Let’s start with our simple web page:
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:PlaceHolder id="PlaceHolder" runat="server" />
</form>
</body>
I have added a Placeholder control here, inside of which my Data Grid is going to reside. Now let’s create a stored procedure using the AdventureWorks2000 database called Dynamic_DataGrid which contains a simple Select statement
SELECT distinct P.ProductID AS Product_ID,
PSC.Name AS Product_Sub_Category, PC.Name AS Product_Category,
P.StandardCost AS Standard_Cost, P.ListPrice AS List_Price,
PV.AverageLeadTime AS Average_List_Time, PV.MinOrderQty AS Minimum_Quantity,
PV.MaxOrderQty AS Maximum_Quantity, PV.LastReceiptDate AS Last_Reciept_Date
FROM Product P
INNER JOIN ProductSubCategory PSC ON
P.ProductSubCategoryID = PSC.ProductSubCategoryID
INNER JOIN ProductCategory PC ON
PSC.ProductCategoryID = PC.ProductCategoryID
INNER JOIN ProductVendor PV ON P.ProductID = PV.ProductID
Create a datagrid programatically
Let's see what our code behind file to create a datagrid looks like.
// Instantiate the data grid control
System.Web.UI.WebControls.DataGrid DataGrid1 =
new System.Web.UI.WebControls.DataGrid();
// the GetDataSet method executes the Stored procedure and populates a dataset ds
ds = GetDataSet("DynamicDataGridSP1");
// the dataset is used as the data source for our newly created datatrid DataGrid1
DataGrid1.DataSource=ds;
DataGrid1.DataBind();
// DataGrid1 is added to the PlaceHolder
PlaceHolder.Controls.Add(DataGrid1);
So let’s build and compile this, and see what we get.
The results are not bad: we did what we set out to do. We created the datagrid on the fly, in the code behind file, and populated it with the results of our stored procedure Dynamic_Datagrid. The problem is that our datagrid looks mediocre and our users are not very happy with it. Fortunately, you can set Datagrid properties programmatically.
Setting Datagrid properties in code
DataGrid1.Width = 600;
DataGrid1.Height= 400;
DataGrid1.GridLines = GridLines.Both;
DataGrid1.CellPadding =1;
DataGrid1.ForeColor=System.Drawing.Color.Black;
DataGrid1.BackColor = System.Drawing.Color.Beige;
DataGrid1.AlternatingItemStyle.BackColor = System.Drawing.Color.Gainsboro;
DataGrid1.HeaderStyle.BackColor = System.Drawing.Color.Brown;
Now we have a better looking grid, - this example is just a representative sample of a few DataGrid properties. Refer the DataGrid class to get a comprehensive list of the properties of the DataGrid.
Add Columns to a datagrid dynamically in the code behind
So far we have been making use of the AutoGenerateColumns property of the Datagrid control. When this property is set to true the Grid creates a bound column for each field in data source of the Datagrid. Whereas we haven’t specifically set the property to true, this is the default behavior of the Datagrid. Our columns are not formatted however, and we might get a little more functionality from this code, if we don’t rely on AutoGenerateColumns.
DataGrid1.AutoGenerateColumns=false;
After adding the above line of code, we will need to add columns individually to the datagrid. Since I am building on the first example in this article, we will assume I already have a populated DataSet (ds), and I only have one table, so the data is returned in Table[0]. We are going to loop through the columns on the DataTable and add them to our DataGrid. To set the properties of each column we will write a new method: the CreateBoundColumn method.
foreach(DataColumn c in ds.Tables[0].Columns)
{
DataGrid1.Columns.Add(CreateBoundColumn(c));
}
Now, it is time to take a look at the CreateBoundColumn method.
BoundColumn column = new BoundColumn();
column.DataField = c.ColumnName;
column.HeaderText = c.ColumnName.Replace("_"," ");
column.DataFormatString =setFormating(c);
return column;
This method creates a new BoundColumn object and binds the column to the associated field in the DataSet. Next the method prettifies the column heading by stripping the '_', and then sets the formatting of the column based on the data type in the setFormatting method.
Setting Datagrid column formatting in code
Its time to take a look at the setFormatting method.
private string setFormating(DataColumn bc)
{
string dataType = null;
switch(bc.DataType.ToString())
{
case "System.Int32":
dataType = "{0:#,###}";
break;
case "System.Decimal":
dataType = "{0:c}";
break;
case "System.DateTime":
dataType="{0:dd-mm-yyyy}";
break;
case "System.String":
dataType="";
break;
default:
dataType = "";
break;
}
return dataType;
}
Of course I haven’t covered all data types, but you can expand the switch statement to cover other data types you might be using.
Note: the data types being used here are NOT the SQL data types, but the .NET data types.
Let’s take a look at our page now.
Conclusion : There are a lot more properties you could set to make this data grid fit your application needs. In a real world scenario, we would want to add sorting and paging to this too. The intent of this article is, to provide an insight into how to create a web form with a dynamic datagrid, created based on the data returned. Hopefully this will help building applications faster.
by Poonam Lall