SqlDataSource Example in ASP.NET using C#

In this article we will introduce with SqlDataSource control in ASP.NET using C#.

SqlDataSource Example in ASP.NET using C#

In this article we will discuss how to use SqlDataSource in your application. SqlDataSources similar like other data sources as ObjectDataSources, EntityDataSources, XmlDataSources etc. In this example we use GridView and provide data using SqlDataSource. Click smart tag button of GridView and Choose Data Source.

Step 1. Now select SqlDataSource and click OK.

Step 2. Now Choose your Data Connection and click next.

Step 3. Now Configure the select statement and click Advanced button.

Step 4. Now enable Generate INSERT, UPDATE and DELETE Statement checkbox  and click OK and next.

Step 5. Now Click Test Query and Click finish.

Now click smart tag button of GridView and enable paging, sorting, editing, deleting and selection.

SqlDataSource.aspx (Design Page):

SqlDataSource.aspx (source code):

<%@ Page Title="" Language="C#" MasterPageFile="~/RoseindiaMaster.master" 
AutoEventWireup="true" CodeFile="SqlDataSource.aspx.cs" Inherits="SqlDataSource" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<div>
<h2 style="color:Green">SqlDataSource in ASP.NET 4, C#</h2>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" 
AutoGenerateColumns="False" CellPadding="4" DataKeyNames="ProductID" 
DataSourceID="SqlDataSource1" Font-Bold="True" Font-Names="Verdana" 
Font-Size="Small" ForeColor="#333333" GridLines="None" PageSize="4">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" 
ShowSelectButton="True" />
<asp:BoundField DataField="ProductID" HeaderText="ProductID" ReadOnly="True" 
SortExpression="ProductID" />
<asp:BoundField DataField="ProductName" HeaderText="ProductName" 
SortExpression="ProductName" />
<asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />
</Columns>
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:ChartDatabaseConnectionString %>" 
DeleteCommand="DELETE FROM [Product] WHERE [ProductID] = @ProductID" 
InsertCommand="INSERT INTO [Product] ([ProductID], [ProductName], [Price]) VALUES (@ProductID, @ProductName, @Price)" 
SelectCommand="SELECT * FROM [Product]" 
UpdateCommand="UPDATE [Product] SET [ProductName] = @ProductName, [Price] = @Price WHERE [ProductID] = @ProductID">
<DeleteParameters>
<asp:Parameter Name="ProductID" Type="Int32" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="ProductID" Type="Int32" />
<asp:Parameter Name="ProductName" Type="String" />
<asp:Parameter Name="Price" Type="String" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="ProductName" Type="String" />
<asp:Parameter Name="Price" Type="String" />
<asp:Parameter Name="ProductID" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>
</div>
</asp:Content>

Output: