Introduction
One of the best new features of ASP.NET 2.0 is the SqlDataSource
control. However, as I found out in my experimentation, what seems like a simple control has some complex behaviours to master to get it working properly.
I found lots of brief articles on the web about SqlDataSource
, but very few covered any detail, with no complete examples but by using datasets and data readers. This article is designed to take you from the first-principles to a working ASP.NET 2.0 web application, with selecting, updating, inserting, and deleting, and concurrency checking with Transactions.
Create a New Web Site
- Create an ASP.NET Web Site (my code is in C#, so I will suggest selecting C# for this example).
- Drag and drop a
GridView
into the Default.aspx page (by default, the ASP.NET 2005 IDE will create a Default.aspx when we create a new project). - Drag and drop a
SqlDataSource
Custom Control. - Configure the
SqlDataSource
properties with a connection string and a SQL query. - Select
GridView1
and select the GridView Task Pane, and choose Data Source as SqlDataSource1
.
<asp:GridView id=GridView1 EnableSortingAndPagingCallbacks="True"
PageSize="5" GridLines="None" ForeColor="#333333"
DataSourceID="SqlDataSource1" DataKeyNames="CategoryID"
CellPadding="4" AutoGenerateColumns="False"
AllowSorting="True" AllowPaging="True"
runat="server">
<FOOTERSTYLE ForeColor="White" Font-Bold="True"
BackColor="#5D7B9D" />
<COLUMNS>
<asp:BoundField SortExpression="CategoryName"
HeaderText="CategoryName"
DataField="CategoryName">
<ITEMSTYLE Width="150px" />
</asp:BoundField>
<asp:BoundField SortExpression="Description"
HeaderText="Description"
DataField="Description">
<ITEMSTYLE Width="250px" />
</asp:BoundField>
<asp:HyperLinkField Text="Edit"
DataNavigateUrlFormatString="Default2.aspx?CID={0}"
DataNavigateUrlFields="CategoryID">
<ITEMSTYLE Width="50px" />
</asp:HyperLinkField>
</COLUMNS>
<ROWSTYLE ForeColor="#333333" BackColor="#F7F6F3" />
<EDITROWSTYLE BackColor="#999999" />
<SELECTEDROWSTYLE ForeColor="#333333"
Font-Bold="True" BackColor="#E2DED6" />
<PAGERSTYLE ForeColor="White" BackColor="#284775"
HorizontalAlign="Left" />
<HEADERSTYLE ForeColor="White" Font-Bold="True"
BackColor="#5D7B9D" HorizontalAlign="Left" />
<ALTERNATINGROWSTYLE ForeColor="#284775" BackColor="White" />
</asp:GridView>
<asp:SqlDataSource id=SqlDataSource1 runat="server"
SelectCommand="SELECT [CategoryID], [CategoryName],
[Description] FROM [Categories]"
ConnectionString="<%$ConnectionStrings:NorthwindConnectionString %>">
</asp:SqlDataSource>
Create another .aspx page and name it Default2.aspx.
Modify one of the bound fields to HyperLinkField
and assign a DataNavigateUrlFormatString
and your target page with the appropriate query string. So when you click on that hyper link, the details will be edited.
<asp:HyperLinkField DataNavigateUrlFields="CategoryID"
DataNavigateUrlFormatString="Default2.aspx?CID={0}"
Text="Edit" >
<ItemStyle Width="50px" />
</asp:HyperLinkField>
Now open the Default2.aspx page and create SqlDataSource
.
Create all the needed controls on the Default2.aspx page.
Configure or edit in the Source Editor in the .NET IDE for Select, Update, and Insert parameters, and write your DML statements and specify the target controls as shown below for SelectParameters
, UpdateParameters
, and InsertParameters
.
For SelectParameters
, the value comes from the query string, so we need to configure it as QueryStringParameter
and DataType
of that parameter.
<asp:QueryStringParameter Name="CategoryID" QueryStringField="CID" Type="Int32" />
For UpdateParameters
, one parameter should contain QueryStringParameter
.
<UpdateParameters>
<asp:ControlParameter Name="CategoryName" ControlID="txtName" />
<asp:ControlParameter Name="Description" ControlID="txtDesription" />
<asp:QueryStringParameter Name="CategoryID"
QueryStringField="CID" Type="Int32" />
</UpdateParameters>
For InsertParameters
:
<InsertParameters>
<asp:ControlParameter Name="CategoryName" ControlID="txtName" />
<asp:ControlParameter Name="Description" ControlID="txtDesription" />
</InsertParameters>
Now your code should look similar to this:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT [CategoryID], [CategoryName],
[Description] FROM [Categories] WHERE ([CategoryID] = @CategoryID)"
InsertCommand="INSERT INTO Categories (CategoryName, Description)
VALUES (@CategoryName, @Description)"
UpdateCommand="UPDATE [Categories] SET
[CategoryName]= @CategoryName, [Description] = @Description
WHERE ([CategoryID] = @CategoryID)"
DataSourceMode="DataReader"
OnInserted="SqlDataSource1_Inserted"
OnInserting="SqlDataSource1_Inserting"
OnUpdated="SqlDataSource1_Updated"
OnUpdating="SqlDataSource1_Updating">
<SelectParameters>
<asp:QueryStringParameter Name="CategoryID"
QueryStringField="CID" Type="Int32" />
</SelectParameters>
<UpdateParameters>
<asp:ControlParameter Name="CategoryName"
ControlID="txtName" />
<asp:ControlParameter Name="Description"
ControlID="txtDesription" />
<asp:QueryStringParameter Name="CategoryID"
QueryStringField="CID"
Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:ControlParameter Name="CategoryName"
ControlID="txtName" />
<asp:ControlParameter Name="Description"
ControlID="txtDesription" />
</InsertParameters>
</asp:SqlDataSource>
Make sure that you have configured the events OnInserted
, OnInserting
, OnUpdated
, OnUpdating
because the goal of this sample is to perform database operations with a transaction.
C# code in the code-behind file for select, update, and insert
Your C# code should be similar to the code below, to perform a Select operation when the page loads. If the query string is present in that page, it fetches the data from the database and shows the Update mode. Else the screen will be rendered for Insert mode.
protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString["CID"] != null)
{
Button1.Text = "Update";
IDataReader reader = ((IDataReader)((IEnumerable)
SqlDataSource1.Select(DataSourceSelectArguments.Empty)));
while (reader.Read())
{
txtName.Text = reader["CategoryName"].ToString();
txtDesription.Text = reader["Description"].ToString();
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
if (Button1.Text != "Update")
{
SqlDataSource1.Insert();
}
else
{
SqlDataSource1.Update();
}
Response.Redirect("Default.aspx");
}
catch (Exception Ex)
{
Response.Write(Ex.Message);
}
}
protected void SqlDataSource1_Inserted(object sender,
SqlDataSourceStatusEventArgs e)
{
bool OtherProcessSucceeded = true;
if (OtherProcessSucceeded)
{
e.Command.Transaction.Commit();
Response.Write("The record was updated successfully");
}
else
{
e.Command.Transaction.Rollback();
Response.Write("The record was not updated");
}
}
protected void SqlDataSource1_Inserting(object sender,
SqlDataSourceCommandEventArgs e)
{
e.Command.Connection.Open();
e.Command.Transaction = e.Command.Connection.BeginTransaction();
}
protected void SqlDataSource1_Updated(object sender,
SqlDataSourceStatusEventArgs e)
{
bool OtherProcessSucceeded = true;
if (OtherProcessSucceeded)
{
e.Command.Transaction.Commit();
Response.Write("The record was updated successfully");
}
else
{
e.Command.Transaction.Rollback();
Response.Write("The record was not updated");
}
}
protected void SqlDataSource1_Updating(object sender,
SqlDataSourceCommandEventArgs e)
{
e.Command.Connection.Open();
e.Command.Transaction = e.Command.Connection.BeginTransaction();
}