Connecting to SQL Server Database and binding a GridView in ASP.NET
This article will be very useful to all the users who are working with Asp.NET Gridview. We have a GridView control which is more powerful than the DataGrid. The GridView was introduced in .NET 2.0 version and DataGrid was introduced in .NET 1.1 version. Are you looking for Connecting to SQL Server Database and binding a GridView in ASP.NET?
Connecting to SQL Server Database and binding a GridView in ASP.NET
Overview:
The basic idea of this presentation is to show how much easy to work with a GridView control than the DataGrid Control. Let us have one data grid control and see how we can bind it. Here we are connecting to the sql server database and retrieving the data based on passed input variable and Binding to a GridView Control.
We can easily connect to SQL server database and retrieve the data and bind to a grid in a single click of a button control of a webpage, but connecting using the layer architecture is bit difficult. To data we will look into that how we can use the layer architecture and pass the data across the layers.
Following are the Layers in 3 tier architecture
1) Presentation Layer
2) Business Access Layer
3) Data Access Layer
Let us have a sample GridView and add the few item templates to it. Below gridview contains BoundField and ItemTemplate. Within the ItemTemplate we have a LinkButton control in it. ASPX Page:
<asp:GridView id="ModuleGrid" runat="server" Width="706px" ForeColor="#333333" OnRowDataBound="ModuleGrid_RowDataBound" OnRowCommand="ModuleGrid_RowCommand" OnSelectedIndexChanged="ModuleGrid_SelectedIndexChanged" OnPageIndexChanging="ModuleGrid_PageIndexChanging1" CellPadding="4" PageSize="5" AutoGenerateColumns="False" DataKeyNames="mo_moid" GridLines="Both">
<Columns >
<asp:BoundField DataField ="mo_moid" HeaderText="moduleid" Visible ="False"/>
<asp:TemplateField HeaderText="Module Name">
<ItemTemplate>
<asp:LinkButton ID ="Lblstatus" CssClass="hover" Text= '<%#Eval("mo_name") %>' runat="server"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField ="mo_desc" HeaderText="Description" />
<asp:BoundField DataField ="mo_multilogin" HeaderText="Multilogin" />
<asp:BoundField DataField ="mo_status" HeaderText ="Status" />
</Columns>
<FooterStyle CssClass ="Gvfooter" />
<RowStyle CssClass =" Gvrow" Wrap="False" />
<SelectedRowStyle CssClass ="Gvselectedrow" />
<PagerStyle CssClass ="Gvpager" />
<HeaderStyle CssClass ="GVheader"/>
<AlternatingRowStyle CssClass ="Gvalternatingrow" />
</asp:GridView>
Below is the code behind code to connect the database and bind the gridview. ASPX.cs File:
ModuleBusiness objModuleBusiness = BusinessFactory.ModuleBusinessObj();
clsErrorHandlerALL ErrorHandler = DataFactory.ErrorHandlerObj();
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindData();
}
}
private void BindData()
{
string mo_name=string.Empty;
try
{
if (DropDownsearch.SelectedIndex > 1)
{
mo_name = DropDownsearch.SelectedItem.Text.ToString();
}
else
{
mo_name = "";
}
DataSet ds = new DataSet();
ds = objModuleBusiness.GetModules(currentPageNumber, PAGE_SIZE, mo_name);
if (ds.Tables[0].Rows.Count <= 0)
{
lblDisplay.Text = "No Records Available";
}
else
{
ModuleGrid.DataSource = ds;
this.ModuleGrid.Attributes.Add("style", "table-layout:fixed;width:700px;border-collapse:collapse;");
ModuleGrid.DataBind();
}
}
catch (Exception ex)
{
ErrorHandler.ErrorsEntry(ex.GetBaseException().ToString(), ex.Source, 1, "AddModule", "BindData()", clsErrorHandlerALL.Logtype.file);
}
If you are keenly observe the above code we are instantiating the ModuleBusiness class, that is a business entity class and calling the GetModules() with the parameters. We are calling the business access level class like as below.
DataSet ds = new DataSet();
ds = objModuleBusiness.GetModules(currentPageNumber, PAGE_SIZE, mo_name); BusinessAccess.cs file:
The implementation of business access class will be like as below code.In the business access class creating the object for the Data Acess Layer class and called the method GetModule() which is existed in the Data Acess Layer.
ModuleData objModuleData = DataFactory.ModuleDataObj();
public DataSet GetModules(int currentpage,int pagesize,string mo_name)
{
DataSet ds = new DataSet();
try
{
ds = objModuleData.GetModule(currentpage, pagesize,mo_name);
}
catch (Exception ex)
{
objErrorHandler.ErrorsEntry(ex.GetBaseException().ToString(), ex.Source, 1, "ModuleBusiness", "GetModules()", clsErrorHandlerALL.Logtype.file);
}
return ds;
} DataAccess.cs file:
The below code demonstrate the Data Access Layer class and called the helper class to connect and execute the sql stored procedure.
public DataSet GetModule(int currentpage,int pagesize,string mo_name)
{
DataSet ds = new DataSet();
try
{
SqlCommand cmd = new SqlCommand();
cmd.Parameters.Add("@currentpage",SqlDbType.Int);
cmd.Parameters["@currentpage"].Value =currentpage;
cmd.Parameters.Add("@pagesize",SqlDbType.Int);
cmd.Parameters["@pagesize"].Value =pagesize;
cmd.Parameters.Add("@mo_name", SqlDbType.VarChar);
cmd.Parameters["@mo_name"].Value = mo_name;
ds = ACLData.Helper.myAdapter(cmd, CommandType.StoredProcedure,"Get_Modules");
}
catch (Exception ex)
{
objErrorHandler.ErrorsEntry(ex.GetBaseException().ToString(), ex.Source, 1, "ModuleData", "GetModule()", clsErrorHandlerALL.Logtype.file);
}
return ds;
} Helper.cs file:
Below code tells us how to reuse the code for the Execute adapter and the PrepareCommand. Here in the Helper.cs file first we are getting the connection string details from the Web.Config file and assigning to CONN_STRING Variable. The Execute Adapter method describes how to connect the database and execute the Storedprocedure.
//Database connection strings
private static readonly string CONN_STRING =ConfigurationSettings.AppSettings["ConStr"];
public static DataSet MyAdapter(SqlCommand cmd, CommandType cmdType, string cmdText)
{
using (SqlConnection conn = new SqlConnection(CONN_STRING))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText);
SqlDataAdapter da=new SqlDataAdapter();
da.SelectCommand=cmd;
DataSet ds= new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
}
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn,
SqlTransaction trans, CommandType cmdType, string cmdText)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
}