Click here to Skip to main content
15,867,939 members
Articles / Web Development / ASP.NET

SqlDataSource with Transactions

Rate me:
Please Sign up or sign in to vote.
3.88/5 (4 votes)
14 Sep 2006CPOL2 min read 75.2K   301   30   6
One of the best new features of ASP.NET 2.0 will be 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.

Sample Image - SqlDataSource_Transaction.jpg

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

  1. Create an ASP.NET Web Site (my code is in C#, so I will suggest selecting C# for this example).
  2. 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).
  3. Drag and drop a SqlDataSource Custom Control.
  4. Configure the SqlDataSource properties with a connection string and a SQL query.
  5. Select GridView1 and select the GridView Task Pane, and choose Data Source as SqlDataSource1.
ASP.NET
<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.NET
<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.NET
<asp:QueryStringParameter Name="CategoryID" QueryStringField="CID" Type="Int32" />

For UpdateParameters, one parameter should contain QueryStringParameter.

ASP.NET
<UpdateParameters>
  <asp:ControlParameter Name="CategoryName" ControlID="txtName" />
  <asp:ControlParameter Name="Description" ControlID="txtDesription" />
  <asp:QueryStringParameter Name="CategoryID" 
           QueryStringField="CID" Type="Int32" />
</UpdateParameters>

For InsertParameters:

ASP.NET
<InsertParameters>
  <asp:ControlParameter Name="CategoryName" ControlID="txtName" />
  <asp:ControlParameter Name="Description" ControlID="txtDesription" />
</InsertParameters>

Now your code should look similar to this:

ASP.NET
<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.

C#
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();
}

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
India India
Anil is from Hyderabad, Andhra Pradesh. He is Currently Working with VisualSoft Technologies Ltd., As a Software Engineer.

Anil has more than 4 yrs of experience in the IT industry working on Microsoft Technologies. He is involved in various project activities like System Architecture, Design, and Development. Technical experience most specifically ASP.NET , Javascript, Biztalk 2004/2006, Webservices, C# and .NET framework. He has have worked on various language and platforms. He is Microsoft Certified professional.

The author has won several programming awards within the organizations worked and is well-recognized.


--

Comments and Discussions

 
QuestionFor Oracle Pin
kasyan27-Nov-07 6:00
kasyan27-Nov-07 6:00 
QuestionTransation Support for nested formview sqldatasources Pin
srktirumala19-Sep-07 2:23
srktirumala19-Sep-07 2:23 
AnswerRe: Transation Support for nested formview sqldatasources [modified] Pin
khwaja13068-Nov-07 19:49
khwaja13068-Nov-07 19:49 
GeneralControlParameter Pin
DeltaSoft20-Apr-07 2:09
DeltaSoft20-Apr-07 2:09 
GeneralException received when trying Pin
crxpert5-Oct-06 6:22
crxpert5-Oct-06 6:22 
GeneralRe: Exception received when trying Pin
SilverPaladin4-Apr-07 13:55
SilverPaladin4-Apr-07 13:55 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.