SqlDataSource with Transactions

• Download source files - 4.07 KB

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