In my previous article, I described how to use a GridView control to display data using a SqlDataSource control. In this article, I will show you how to use an ASP.NET 2.0 GridView control to view, add, update, and delete data in a SQL database.
We will use SQL Client data provider to provide database connectivity.
Before you can use any classes related to SQL Client data adapter, we need to import the SqlClient namespace in your application by using the following using statement.
using System.Data.SqlClient;
Next, we need to define the database connection string.
The below is my connection string which is stored in web.config file. You can change this connection string according to your SQL server database setting. I am storing my database file in App_Data folder. If you want use my database file then attach that file.
<appSettings>
<add key="ConnectionString" value="Data Source=.;Initial Catalog=Vendor;User ID=sa;Password=wintellect;"/>
</appSettings>
The following code snippet shows how to connect to a database and create other database access related objects.
SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString());
SqlDataAdapter ad = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
DataTable dataTable;
This function is use to fetch data from the Vendor table, fills data in a DataTable object and find it to a GridView control using the DataSource property. In the end, the code calls the GridView.DataBind method to apply the binding.
private void FillVendorGrid()
{
dataTable = new DataTable();
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM Vendor";
ad = new SqlDataAdapter(cmd);
ad.Fill(dataTable);
ResultGridView.DataSource = dataTable;
ResultGridView.DataBind();
}
Now on the page load method, we call the FillVendorGrid method.
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
FillVendorGrid();
}
}
Now, next step is to set the GridView control settings.
The ASP.NET code for the DataView control. In this code below code, you see database table columns binding with the bound fields and formatting is provided using the template fields. If you are using my database, just copy and paste the code or use the attached application. If you are using your database, you need to replace column binding with your database table columns.
<asp:GridView ID="ResultGridView" runat="server" AutoGenerateColumns="False" ShowFooter="true"
DataKeyNames="VendorId"
AllowPaging="True"
CellPadding="3"
OnPageIndexChanging="ResultGridView_PageIndexChanging"
OnRowDeleting="ResultGridView_RowDeleting"
OnRowEditing="ResultGridView_RowEditing"
OnRowUpdating="ResultGridView_RowUpdating" OnRowCancelingEdit="ResultGridView_RowCancelingEdit"
PageSize="5"
BackColor="#DEBA84"
BorderColor="#DEBA84"
BorderStyle="None"
BorderWidth="1px"
CellSpacing="2"
OnRowCommand="ResultGridView_RowCommand">
<Columns>
<asp:BoundField DataField="VendorId" HeaderText="VendorId" InsertVisible="False"
ReadOnly="True" SortExpression="VendorId" />
<asp:TemplateField HeaderText="FirstName" SortExpression="VendorFName">
<EditItemTemplate>
<asp:TextBox ID="txtFName" Width="100px" runat="server" Text='<%# Bind("VendorFName") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtFName1" runat="server" Width="100px"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("VendorFName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="LastName" SortExpression="VendorLName">
<EditItemTemplate>
<asp:TextBox ID="txtLName" Width="100px" runat="server" Text='<%# Bind("VendorLName") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtLName1" Width="100px" runat="server" ></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("VendorLName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City" SortExpression="VendorCity">
<EditItemTemplate>
<asp:TextBox ID="txtCity" Width="100px" runat="server" Text='<%# Bind("VendorCity") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtCity1" Width="100px" runat="server" ></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("VendorCity") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="State" SortExpression="VendorState">
<EditItemTemplate>
<asp:TextBox ID="txtState" Width="100px" runat="server" Text='<%# Bind("VendorState") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtState1" Width="100px" runat="server" ></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("VendorState") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country" SortExpression="VendorCountry">
<EditItemTemplate>
<asp:TextBox ID="txtCountry" Width="100px" runat="server" Text='<%# Bind("VendorCountry") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtCountry1" Width="100px" runat="server" ></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label5" runat="server" Text='<%# Bind("VendorCountry") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="VendorDescription" SortExpression="VendorDescription">
<EditItemTemplate>
<asp:TextBox ID="txtDescription" TextMode="MultiLine" runat="server" Text='<%# Bind("VendorDescription") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtDescription1" runat="server" TextMode="MultiLine" ></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label6" runat="server" Text='<%# Bind("VendorDescription") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Edit" ShowHeader="False">
<EditItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" CommandName="Update" Text="Update"></asp:LinkButton>
<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel"></asp:LinkButton>
</EditItemTemplate>
<FooterTemplate>
<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="AddNew" Text="Add New"></asp:LinkButton>
</FooterTemplate>
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField HeaderText="Delete" ShowDeleteButton="True" ShowHeader="True" />
<asp:CommandField HeaderText="Select" ShowSelectButton="True" ShowHeader="True" />
</Columns>
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
</asp:GridView>
Now build and run the application. The results looks like following.
Figure 1.
Select command is use to select a particular row on select LinkButton click:
<asp:CommandField HeaderText="Select" ShowSelectButton="True" ShowHeader="True" />
Result looks like this on the select link.
Figure 2.
This event is used for paging. As you can see from the code below, we simply set a new page index and rebind the data.
protected void ResultGridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
ResultGridView.PageIndex = e.NewPageIndex;
FillVendorGrid();
}
This event shows how to delete a row on delete LinkButton click.
protected void ResultGridView_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
cmd.Connection = conn;
cmd.CommandText = "DELETE FROM Vendor WHERE VendorId='" + ResultGridView.DataKeys[e.RowIndex].Values[0].ToString() + "'";
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
FillVendorGrid();
}
This event is used to show a row in editable mode.
protected void ResultGridView_RowEditing(object sender, GridViewEditEventArgs e)
{
ResultGridView.EditIndex = e.NewEditIndex;
FillVendorGrid();
}
This event will update information in database.
protected void ResultGridView_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
TextBox txtFName = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtFName");
TextBox txtLName = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtLName");
TextBox txtCity = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtCity");
TextBox txtState = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtState");
TextBox txtCountry = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtCountry");
TextBox txtDescription = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtDescription");
cmd.Connection = conn;
cmd.CommandText = "UPDATE Vendor SET VendorFName ='" + txtFName.Text + "',VendorLName ='" + txtLName.Text + "',VendorCity ='" + txtCity.Text + "',VendorState ='" + txtState.Text + "',VendorCountry ='" + txtCountry.Text + "',VendorDescription ='" + txtDescription.Text + "' WHERE VendorId='" + ResultGridView.DataKeys[e.RowIndex].Values[0].ToString() + "'";
conn.Open();
cmd.ExecuteNonQuery();
ResultGridView.EditIndex = -1;
FillVendorGrid();
conn.Close();
}
Result will look like this :
Figure 3.
This event is use to cancel editable model.
protected void ResultGridView_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
ResultGridView.EditIndex = -1;
FillVendorGrid();
}
This RowCommand event is use to add a new row in database.
protected void ResultGridView_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("AddNew"))
{
TextBox txtFName = (TextBox)ResultGridView.FooterRow.FindControl("txtFName1");
TextBox txtLName = (TextBox)ResultGridView.FooterRow.FindControl("txtLName1");
TextBox txtCity = (TextBox)ResultGridView.FooterRow.FindControl("txtCity1");
TextBox txtState = (TextBox)ResultGridView.FooterRow.FindControl("txtState1");
TextBox txtCountry = (TextBox)ResultGridView.FooterRow.FindControl("txtCountry1");
TextBox txtDescription = (TextBox)ResultGridView.FooterRow.FindControl("txtDescription1");
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO Vendor(VendorFName, VendorLName,VendorCity,VendorState,VendorCountry,VendorDescription) Values('" + txtFName.Text + "', '" + txtLName.Text + "', '" + txtCity.Text + "', '" + txtState.Text + "', '" + txtCountry.Text + "' , '" + txtDescription.Text + "')";
conn.Open();
cmd.ExecuteNonQuery();
FillVendorGrid();
conn.Close();
}
}
For more information I am attaching my test application and database, so download it for better result and if you liked this article or you have any advice about my any article don't be hesitate drop me a comment on c-sharpcorner comment section.