In this article I will explain with an example, how to perform CRUD operation using SqlDataSource in ASP.Net using C# and VB.Net.
CRUD operation means performing Select, Insert, Edit, Update and Delete using SqlDataSource in ASP.Net using C# and VB.Net.
Database
I have made use of the following table Customers with the schema as follows. CustomerId is an Auto-Increment (Identity) column.
Note: You can download the database table SQL by clicking the download link below.
HTML Markup
The HTML Markup consists of an ASP.Net GridView along with a SqlDataSource control. Below the GridView, there’s a Form which will be used to insert records.
The fields from database table are displayed using BoundField column. There is also a CommandField column with ShowEditButton and ShowDeleteButton set to true so that the GridView displays Edit and Delete buttons respectively.
Note: If you do not want a particular field to be editable, simply set ReadOnly property to True for the respective BoundField column in GridView.
The CustomerId field has been set to the DataKeyNames property, it is very necessary as SqlDataSource uses DataKeyNames value for Updating and Deleting records.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" DataSourceID="SqlDataSource1"
DataKeyNames="CustomerId" OnRowDataBound="OnRowDataBound" EmptyDataText="No records has been added.">
<Columns>
<asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="150" />
<asp:BoundField DataField="Country" HeaderText="Country" 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">
Name:<br />
<asp:TextBox ID="txtName" runat="server" Width="140" />
</td>
<td style="width: 150px">
Country:<br />
<asp:TextBox ID="txtCountry" 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 CustomerId, Name, Country FROM Customers"
InsertCommand="INSERT INTO Customers VALUES (@Name, @Country)"
UpdateCommand="UPDATE Customers SET Name = @Name, Country = @Country WHERE CustomerId = @CustomerId"
DeleteCommand="DELETE FROM Customers WHERE CustomerId = @CustomerId">
<InsertParameters>
<asp:ControlParameter Name="Name" ControlID="txtName" Type="String" />
<asp:ControlParameter Name="Country" ControlID="txtCountry" Type="String" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="CustomerId" Type="Int32" />
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Country" Type="String" />
</UpdateParameters>
<DeleteParameters>
<asp:Parameter Name="CustomerId" Type="Int32" />
</DeleteParameters>
</asp:SqlDataSource>
Populating GridView from Database
1. Define and set the Connection String to the database in Web.Config file.
<connectionStrings>
<add name="constr" connectionString="Data Source=.\SQLExpress;Initial Catalog=Samples;integrated security=true" />
</connectionStrings>
2. Apply the Connection String to SqlDataSource. Here constr is the name of the Connection String setting in Web.Config file.
ConnectionString="<%$ ConnectionStrings:constr %>"
3. Set the ID of the SqlDataSource as DataSourceID for the GridView.
DataSourceID="SqlDataSource1"
4. Set the Select Command.
SelectCommand="SELECT CustomerId, Name, Country FROM Customers"
Following is the GridView containing records.
![SqlDataSource CRUD: Select Insert Edit Update and Delete using SqlDataSource in ASP.Net]()
For the GridView I have set EmptyDataText to display message when no records are present.
![SqlDataSource CRUD: Select Insert Edit Update and Delete using SqlDataSource in ASP.Net]()
Inserting records to GridView using SqlDataSource
1. Set the InsertCommand.
InsertCommand="INSERT INTO Customers VALUES (@Name, @Country)"
2. Set the Insert Parameters. ControlParameters are used in order to get the Parameter values directly from the TextBoxes. The ControlID property is set to the ID of the respective TextBoxes.
For example, the value of @Name Parameter is fetched from txtName TextBox.
<InsertParameters>
<asp:ControlParameter Name="Name" ControlID="txtName" Type="String" />
<asp:ControlParameter Name="Country" ControlID="txtCountry" Type="String" />
</InsertParameters>
3. There’s a Click event handler assigned to the Add Button. When the Button is clicked, the Insert function of the SqlDataSource is executed which then inserts the records to the database and binds the GridView again to display the newly inserted record.
C#
protected void Insert(object sender, EventArgs e)
{
SqlDataSource1.Insert();
}
VB.Net
Protected Sub Insert(sender As Object, e As EventArgs)
SqlDataSource1.Insert()
End Sub
Editing and Updating GridView records using SqlDataSource
1. Set the UpdateCommand. Here you simply need to set parameter names same as their corresponding column fields.
For example if the column name is Name its corresponding parameter name will be @Name. Once this is done the SqlDataSource will automatically pick values from respective TextBoxes inside the GridView row to be edited.
UpdateCommand="UPDATE Customers SET Name = @Name, Country = @Country WHERE CustomerId = @CustomerId"
2. Set the Update Parameters with name and data type.
<UpdateParameters>
<asp:Parameter Name="CustomerId" Type="Int32" />
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Country" Type="String" />
</UpdateParameters>
![SqlDataSource CRUD: Select Insert Edit Update and Delete using SqlDataSource in ASP.Net]()
Deleting GridView records using SqlDataSource
1. Set the DeleteCommand.
DeleteCommand="DELETE FROM Customers WHERE CustomerId = @CustomerId"
2. Set the Delete Parameters with name and data type. Here the CustomerId field will automatically be picked from the DataKeyNames property of the GridView row to be deleted.
<DeleteParameters>
<asp:Parameter Name="CustomerId" Type="Int32" />
</DeleteParameters>
3. In order to display a Confirmation Message when deleting row, the Delete Button is first reference in the OnRowDataBound event handler and a JavaScript Confirm function is set to its Client Side Click event handler.
C#
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?');";
}
}
VB.Net
Protected Sub OnRowDataBound(sender As Object, e As GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow AndAlso GridView1.EditIndex <> e.Row.RowIndex Then
TryCast(e.Row.Cells(2).Controls(2), LinkButton).Attributes("onclick") = "return confirm('Do you want to delete this row?');"
End If
End Sub
![SqlDataSource CRUD: Select Insert Edit Update and Delete using SqlDataSource in ASP.Net]()
Downloads
