CRUD operations in gridview with sqldatasource

gridview

image credit:commons.wikimedia.org

CRUD operations in gridview with sqldatasource

CRUD operations in gridview with sqldatasource can be achieved in a very straight-forward manner by following the correct configuration of both the data source and the gridview control.
So as to perform Addition, Deletion, Alter and Erase operations, the GridView will be populated with SQL Server database table utilizing SqlDataSource control.
I have made a basic table named Employees whose pattern is demonstrated as follows. I have set the employeeID section with the goal that its values are auto-increased when record is added.

table employees

The GridView markup will be as follows:

Default.aspx

<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”false” DataSourceID=”SqlDataSource1″
DataKeyNames=”employeeID” OnRowDataBound=”OnRowDataBound” EmptyDataText=”No records has been added.”>
<Columns>
<asp:BoundField DataField=”employeeName” HeaderText=”employeeName” ItemStyle-Width=”150″ />
<asp:BoundField DataField=”designation” HeaderText=”designation” ItemStyle-Width=”150″ />
<asp:CommandField ButtonType=”Link” ShowEditButton=”true” ShowDeleteButton=”true”
ItemStyle-Width=”100″ />
</Columns>
</asp:GridView>
<table border=”1″ cellpadding=”0″ cellspacing=”0″ style=”border-collapse: collapse”>
<tr>
<td style=”width: 150px”>
EmployeeName:<br />
<asp:TextBox ID=”txtemployeeName” runat=”server” Width=”140″ />
</td>
<td style=”width: 150px”>
Designation:<br />
<asp:TextBox ID=”txtDesignation” runat=”server” Width=”140″ />
</td>
<td style=”width: 100px”>
<asp:Button ID=”btnAdd” runat=”server” Text=”Add” OnClick=”Insert” />
</td>
</tr>
</table>
<asp:SqlDataSource ID=”SqlDataSource1″ runat=”server” ConnectionString=”<%$ ConnectionStrings:constr %>”
SelectCommand=”SELECT employeeID, employeeName, designation FROM Employees”
InsertCommand=”INSERT INTO Employees VALUES (@employeeName, @designation)”
UpdateCommand=”UPDATE Employees SET employeeName = @employeeName, designation = @designation WHERE employeeID = @employeeID”
DeleteCommand=”DELETE FROM Employees WHERE employeeID = @employeeID”>
<InsertParameters>
<asp:ControlParameter Name=”employeeName” ControlID=”txtemployeeName” Type=”String” />
<asp:ControlParameter Name=”designation” ControlID=”txtDesignation” Type=”String” />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name=”employeeID” Type=”Int32″ />
<asp:Parameter Name=”employeeName” Type=”String” />
<asp:Parameter Name=”designation” Type=”String” />
</UpdateParameters>
<DeleteParameters>
<asp:Parameter Name=”employeeID” Type=”Int32″ />
</DeleteParameters>
</asp:SqlDataSource>

The Web.Config has the following connectionstring


<connectionStrings>
<add name=”constr” connectionString=”Data Source=adeel-PC;Initial Catalog=testDB; integrated security=SSPI” />
</connectionStrings>

The code-behind Default.aspx.cs

protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow && GridView1.EditIndex != e.Row.RowIndex)
{
(e.Row.Cells[2].Controls[2] as LinkButton).Attributes[“onclick”] = “return confirm(‘Do you want to delete this row?’);”;
}
}

protected void Insert(object sender, EventArgs e)
{
SqlDataSource1.Insert();

}

output

Syed Adeel Ahmed
Syed Adeel Ahmed
Analyst, Programmer, Educationist and Blogger at Technofranchise
Computer Systems Engineer from Sir Syed University Of Engineering & Technology.I am passionate about all types of programming.

Published by

Syed Adeel Ahmed

Computer Systems Engineer from Sir Syed University Of Engineering & Technology.I am passionate about all types of programming.

Leave a Reply