GridView Object in ASP .NET
Using a GridView in ASP .NET - Static and Dynamic
This article describes how to use a GridView and ObjectDataSource to output data from a SQL Stored Procedure. The first part of the article uses a hard-coded GridView and ObjectDataSource. The second part uses 2 buttons to link the GridView and the ObjectDataSource dynamically.
This article uses the AdventureWorks database in SQL Server. I have created a SQL Stored Procedure to retrieve data from the database.
select SalesPersonID, FirstName + Char(13) + CASE WHEN Len(IsNull(MiddleName,'')) = 0 THEN '' ELSE ' ' + MiddleName END +
Char(13) + ' ' + LastName as SalesName,
AddressLine1 as Address1,
CASE WHEN IsNull(AddressLine2,'^') = '^' THEN City + ', ' + StateProvinceName + ' ' + PostalCode ELSE AddressLine2 END as Address2,
CASE WHEN IsNull(AddressLine2,'^') <> '^' THEN City + ', ' + StateProvinceName + ' ' + PostalCode ELSE '' END as Address3,
CountryRegionName, SalesYTD, Convert(varchar(100),null) as PerformanceRating
INTO #tmp
from Sales.vSalesPerson
Update #tmp
SET PerformanceRating =
CASE WHEN SalesYTD = 0 THEN 'Poor'
WHEN SalesYTD > 0 and SalesYTD < 500000 THEN 'Average'
WHEN SalesYTD >= 500000 and SalesYTD < 1000000 THEN 'Greater Than Average'
WHEN SalesYTD >= 1000000 THEN 'Outstanding'
ELSE '' END
SELECT * FROM #tmp
DROP TABLE #tmp
Static ASP .Net GridView
Create a new website project in VisualStudio and add a Class for the Data Layer. Note the additions of the System.Data.SqlClient class and the addition of the Namespace to the class.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
namespace GVE.DL
{
public class sqlSalesData
{
public static string getConn()
{
string path = @"Data Source=MSSQLSERVER2008;Initial Catalog=AdventureWorks;Integrated Security=SSPI";
return path;
}
public static SqlDataReader SqlSales()
{
SqlConnection conn = new SqlConnection(getConn());
conn.Open();
string cmdText = "EXEC Sales.usp_SalesPerformance";
SqlCommand cmd = new SqlCommand(cmdText, conn);
SqlDataReader sr = cmd.ExecuteReader();
return sr;
}
}
}
Add another class as the Business layer for the site. Note the addition of the System.Data.SqlClient and the GVE.DL using statements and the addition of the namespace GVE.BL to the class.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using GVE.DL;
namespace GVE.BL
{
public class SalesBL
{
public static SqlDataReader sdrSales()
{
SqlDataReader dr = sqlSalesData.SqlSales();
return dr;
}
}
}
In the main page, add the Grid View and ObjectDataSource and set the DataSourceID of the GridView to the name of the ObjectDataSource. Set the TypeName to the fully qualified SalesBL class (namespace + classname) of the BusinessLayer class and the SelectMethod as the static method returning the SqlDataReader (sdrSales).
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td align="center" style="font:Arial">Adventure Works Sales</td>
</tr>
<tr>
<td>
<asp:GridView ID="gvSales" runat="server" DataSourceID="odsSales"
AutoGenerateColumns="false" Font-Names="Arial" >
<Columns>
<asp:BoundField HeaderText="SalesPersonID" DataField="SalesPersonID" />
<asp:BoundField HeaderText="SalesName" DataField="SalesName" />
<asp:BoundField HeaderText="Address1" DataField="Address1" />
<asp:BoundField HeaderText="Address2" DataField="Address2" />
<asp:BoundField HeaderText="Address3" DataField="Address3" />
<asp:BoundField HeaderText="Country" DataField="CountryRegionName" />
<asp:BoundField HeaderText="Rating" DataField="PerformanceRating" />
</Columns>
</asp:GridView>
</td>
</tr>
</table>
</div>
</form>
<asp:ObjectDataSource ID="odsSales" runat="server" TypeName="GVE.BL.SalesBL"
SelectMethod="sdrSales" EnablePaging="false"></asp:ObjectDataSource>
</body>
</html>
Test the site to see that the GridView data is correct.
What if you want to use the grid view for more than 1 data source in the page?
Dynamic ASP .Net GridView
1. Unbind the GridView
<asp:GridView ID="gvSales" runat="server" AutoGenerateColumns="true" Font-Names="Arial" ></asp:GridView>
2. Remove the TypeName and SelectMethod from the ObjectDataSource.
<asp:ObjectDataSource ID="odsSales" runat="server" EnablePaging="false" > </asp:ObjectDataSource>
3. Add a couple of buttons to retrieve different datasets back to the GridView
<asp:Button ID="btnSales" runat="server" Text="Get Sales Data" onclick="btnSales_Click" /> <asp:Button ID="btnEmps" runat="server" Text="Get Employee Data" onclick="btnEmps_Click" />
4. In the DataLayer class, add another method to retrieve data from a different stored procedure.
public static SqlDataReader SqlEmps()
{
SqlConnection conn = new SqlConnection(getConn());
conn.Open();
string cmdText = "EXEC dbo.p_getEmpDeptData";
SqlCommand cmd = new SqlCommand(cmdText, conn);
SqlDataReader sr = cmd.ExecuteReader();
return sr;
}
5. Add a method in the Business Layer to access the data from the page.
public static SqlDataReader sdrEmps()
{
SqlDataReader dr = sqlSalesData.SqlEmps();
return dr;
}
6. Code the events for the 2 buttons added to change the TypeName and SelectMethod for the ObjectDataSource and the DataSourceID for the GridView.
protected void btnEmps_Click(object sender, EventArgs e)
{
odsSales.TypeName = "GVE.BL.SalesBL";
odsSales.SelectMethod = "sdrEmps";
this.gvSales.DataSourceID = "odsSales";
}
protected void btnSales_Click(object sender, EventArgs e)
{
odsSales.TypeName = "GVE.BL.SalesBL";
odsSales.SelectMethod = "sdrSales";
this.gvSales.DataSourceID = "odsSales";
}
Test the site to see that the grid view results are returned.
After pressing Get Sales Data
After pressing Get Employee Data
