More Ways to Update Databases using ASP.NET 2.0 SqlDataSource

In the previous article, we saw how to insert a new record using the SqlDataSource control, programmatically, by assigning appropriate values for the SqlDataSource.InsertCommand property, SqlDataSoruce.InsertParameters collection property and calling the SqlDataSource.Insert() method. Today we will write code to update the Northwind's Employees table and provide the delete functionality.

Contributed by
Rating: 5 stars5 stars5 stars5 stars5 stars / 15
November 06, 2007
Rate this Article:
MEH MEH++


SEARCH ASP FREE
TOOLS YOU CAN USE

advertisement

Note that in this article we explain the basics so when we discuss how the Data-Bound Controls work with the SqlDataSource control you wouldn't feel lost. If you have used the Visual Studio.NET 2005 Task Menu feature, for a SqlDataSource Control as we did in the article "Introduction to the ASP.NET 2.0 SqlDataSource Control," you may have noticed that it generates the SELECT, INSERT, UPDATE and DELETE statements for the SqlDataSource control that is set as the Data Source for the GridView.

Those statements may be parameterized queries too, so where do the values of the parameters come from? And in what syntax? What are the default names for the parameters? All of these questions will be answered when we discuss the Data-Bound Controls. But today we discuss the basic concepts of updating and deleting data in databases using the SqlDataSource control. So let's start with our code example.

We are going to have two pages for this example. The first web page is going to use a SqlDataSource control (declaratively) to retrieve data from the Employees table into a ListBox control. The second page will be used to update a record in the Employees table. 

Usually you will use a Data-Bound Control to perform the Update, Delete and Insert operations, but in this article we write the code without using a Data-Bound Control. So let's begin developing the first page.

Using a ListBox and a SqlDataSource control for Default.aspx page

First of all, create a new website and add a Configuration file to it. Then locate the <connectionStrings /> element in the Configuration file and replace it with the following:

<connectionStrings>

<add name="NorthwindConnection"
connectionString="Data Source=(local);Initial
Catalog=Northwind;Integrated Security=True"

providerName="System.Data.SqlClient" />

</connectionStrings>

Now open the Default.aspx page and drop a ListBox control along with a SqlDataSource control on the page. Configure the SqlDataSource control to retrieve the EmployeeID, FirstName and LastName columns from the Employees table of the Northwind database. Here is the code that you need to put as the markup of the Default.aspx page:

<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

  <head runat="server">

   <title>Untitled Page</title>

  </head>

<body>

  <form id="form1" runat="server">

   <div>

<asp:ListBox ID="ListBox1" runat="server" AutoPostBack="True"
Height="193px"
OnSelectedIndexChanged="ListBox1_SelectedIndexChanged"
Width="171px">

 </asp:ListBox>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
SelectCommand="SELECT [EmployeeID], [LastName], [FirstName] FROM
[Employees]">

 </asp:SqlDataSource>

</div>

</form>

</body>

</html>
 

Actually, the SqlDataSource control we added to the page is not bound to the ListBox control because we are going to do it in the code behind file. This is the code for the Default.aspx.cs file:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Data.Common;

public partial class _Default : System.Web.UI.Page

{

  private string[] employeeFields = new string[3];

  private char[] splitChar = new char[1] { ',' };

   protected void Page_Load(object sender, EventArgs e)

{

SqlDataSource1.DataSourceMode = SqlDataSourceMode.DataReader;
IEnumerable iterator = SqlDataSource1.Select
(DataSourceSelectArguments.Empty);

  foreach (DbDataRecord record in iterator)

{

 ListItem item = new ListItem();

item.Text = record["EmployeeID"].ToString() + ", " +
record["FirstName"].ToString() + ", " +

  record["LastName"].ToString();

   item.Value = record["EmployeeID"].ToString();

 this.ListBox1.Items.Add(item);

 }

}

protected void ListBox1_SelectedIndexChanged(object sender,
EventArgs e)

{

 employeeFields = ListBox1.SelectedItem.Text.Split(splitChar);

  string employeeID = employeeFields[0].Trim();

  string firstName = employeeFields[1].Trim();

  string lastName = employeeFields[2].Trim();

Response.Redirect("Default2.aspx?EmpID=" + employeeID +
"&FirstName=" + firstName +
"&LastName=" + lastName);

 }

}
 

In this code, we set the SqlDataSource.DataSourceMode property to return a data reader object through assigning the enumeration value SqlDataSourceMode.DataReader. We call the Select() method to return an IEnumerable object, of type IDataReader, so we can iterate through the returned data. In each of the foreach iterations we create a ListItem and set its Text property to a string value that represents the EmployeeID concatenated with the FirstName and LastName fields. We also assign the EmployeeID field's value to the ListItem.Value property. Finally, we add this ListItem object to our ListBox control.

We have set the AutoPostBack attribute to true, so when the user changes a selection in the ListBox, the page posts back to the server and the SelectedIndexChanged of the ListBox control fires. In the event handler method ListBox1_SelectedIndexChanged() we use the Split() method of the Text of the ListBox.SelectedItem to separate the fields into 3 string variables. We pass these variables to another page, called Default2.aspx, through the Query String. We have redirected the user to the the Default2.aspx page through the Response.Redirect() method as you can see in the above code.

Let's move to the Default2.aspx where we will do the update of the Employee's record.

Using a SqlDataSource control in the Default2.aspx page

On this page, we are going to put Labels and TextBoxes to get the updated fields from the users. The code that you need to put in the Default2.aspx page is as follows:

<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

  <head runat="server">

   <title>Untitled Page</title>

  </head>

 <body>

  <form id="form1" runat="server">

    <div>

<asp:Label ID="EmpIdLabel" runat="server" Text="You are Updating
a record for the Employee with the ID: "></asp:Label><br />

<asp:Label ID="Label1" runat="server" Text="First
Name"></asp:Label>

<asp:TextBox ID="TextBoxFirstName"
runat="server"></asp:TextBox><br />

<asp:Label ID="Label2" runat="server" Text="Last
Name"></asp:Label>

<asp:TextBox ID="TextBoxLastName"
runat="server"></asp:TextBox><br />

<asp:Button ID="Button1" runat="server" OnClick="Button1_Click"
Text="Update" /></div>

</form>

</body>

</html>
 

As you can see, we haven't put the SqlDataSource control in the markup. We have just placed TextBoxes, Labels and a Button control to get the data from the user and update it using a SqlDataSource control that we create programmatically. The code takes place in the click event's handler for the Button1 control. The following is the complete code for the Default.aspx.cs file

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Web.Configuration;

public partial class Default2 : System.Web.UI.Page

{

  protected void Page_Load(object sender, EventArgs e)

{

   if (!IsPostBack)

{

 EmpIdLabel.Text += Request.QueryString["EmpID"];

  TextBoxFirstName.Text = Request.QueryString["FirstName"];

  TextBoxLastName.Text = Request.QueryString["LastName"];

 }

}

  protected void Button1_Click(object sender, EventArgs e)

{

  SqlDataSource sqlDataSource1 = new SqlDataSource();

   this.Controls.Add(sqlDataSource1);

sqlDataSource1.ConnectionString =
WebConfigurationManager.ConnectionStrings
["NorthwindConnection"].ConnectionString;

sqlDataSource1.UpdateCommand = "UPDATE Employees SET LastName =
@LastName, FirstName = @FirstName " +
"WHERE EmployeeID = @EmployeeID";

  QueryStringParameter qsParameter = new QueryStringParameter();

   qsParameter.Name = "EmployeeID";

   qsParameter.QueryStringField = "EmpID";

   qsParameter.Type = TypeCode.String;

    sqlDataSource1.UpdateParameters.Add(qsParameter);

 ControlParameter parameter = new ControlParameter();

  parameter.Name = "FirstName";

  parameter.ControlID = "TextBoxFirstName";

  parameter.PropertyName = "Text";

  parameter.Type = TypeCode.String;

 sqlDataSource1.UpdateParameters.Add(parameter);

  parameter = new ControlParameter();

  parameter.Name = "LastName";

  parameter.ControlID = "TextBoxLastName";

  parameter.PropertyName = "Text";

  parameter.Type = TypeCode.String;

 sqlDataSource1.UpdateParameters.Add(parameter);

 sqlDataSource1.Update();

  Response.Redirect("Default.aspx");

 }

}
 

Testing the Example

Run the Default.aspx page, which is shown in the next screen shot, and you will find that it retrieves the data from the Employee's table of the Northwind database. We are retrieving only the EmployeeID, FirstName and LastName fields.

Now select the last employee item, Paul Mic, and you will be taken to the Default2.aspx where you can update this employee's data as shown in the next screen shot.

As you can see, the TextBoxes of the Default2.aspx page show the first and last names of the employee. Now change the Last Name to something like Mick and the First Name to Joseph for example, and click on the Update button. You will be taken to the Default.aspx again, but this time you will see the updated record, as shown in the next screen shot.

If you don't have that 10th record, then update whatever record you want. Your result set might be different from mine of course. Let's see what we have done in the code.

Using the QueryStringParameter and the ControlParameter

When you click on a list item you will be taken to the Default2.aspx page. The first thing that we do in this page is in the Page_Load() event handler.

if (!IsPostBack)

{

  EmpIdLabel.Text += Request.QueryString["EmpID"];

   TextBoxFirstName.Text = Request.QueryString["FirstName"];

   TextBoxLastName.Text = Request.QueryString["LastName"];

}

We simply get the EmpID, FirstName and LastName from the QueryString fields that are passed to us from the Default.aspx page. We have used those QueryString fields to give the user the ability to update a record, through assigning those QueryString fields to the Text property of the appropriate Controls in order to display the values of this employee's record on the page so the user can update it. Note that this record has been retrieved from the database in the Default.aspx through the use of a SqlDataSource control.

The code that does the update is in the click event handler of the Button control.

 protected void Button1_Click(object sender, EventArgs e)

{

 SqlDataSource sqlDataSource1 = new SqlDataSource();

  this.Controls.Add(sqlDataSource1);

sqlDataSource1.ConnectionString =
WebConfigurationManager.ConnectionStrings
["NorthwindConnection"].ConnectionString;

sqlDataSource1.UpdateCommand = "UPDATE Employees SET LastName =
@LastName, FirstName = @FirstName " +
"WHERE EmployeeID = @EmployeeID";

 QueryStringParameter qsParameter = new QueryStringParameter();

  qsParameter.Name = "EmployeeID";

  qsParameter.QueryStringField = "EmpID";

  qsParameter.Type = TypeCode.String;

 sqlDataSource1.UpdateParameters.Add(qsParameter);

 ControlParameter parameter = new ControlParameter();

  parameter.Name = "FirstName";

  parameter.ControlID = "TextBoxFirstName";

  parameter.PropertyName = "Text";

  parameter.Type = TypeCode.String;

 sqlDataSource1.UpdateParameters.Add(parameter);

  parameter = new ControlParameter();

  parameter.Name = "LastName";

  parameter.ControlID = "TextBoxLastName";

  parameter.PropertyName = "Text";

  parameter.Type = TypeCode.String;

 sqlDataSource1.UpdateParameters.Add(parameter);

 sqlDataSource1.Update();

Response.Redirect("Default.aspx");

}

In the event handler method we start by creating the required SqlDataSource control and then adding it to the Controls collection of the page. As we said before, the SqlDataSource control requires a connection string to connect and makes the required operation. In our case we are executing an UPDATE statement. We have retrieved the connection string from the Web.Config file of the website through the use of the WebConfigurationManager class as you have seen before. Now we need to set the UPDATE statement to the SqlDataSource control along with the required Parameter objects.

The T-SQL UPDATE statement that we need to execute looks like this:

UPDATE Employees
SET LastName = @LastName,
FirstName = @FirstName
WHERE EmployeeID = @EmployeeID

As you can see, our UPDATE statement needs three parameters.

The first parameter we need comes from the QueryString field, which is the employee id (the EmpID QueryString field). As we said in the previous article, the SqlDataSource control can get values of its parameters through QueryString fields, so we are going to use a QueryStringParameter object to obtain the value for the employee id. This is demonstrated in the following code:

QueryStringParameter qsParameter = new QueryStringParameter();

  qsParameter.Name = "EmployeeID";

  qsParameter.QueryStringField = "EmpID";

  qsParameter.Type = TypeCode.String;

sqlDataSource1.UpdateParameters.Add(qsParameter);

What you should note about the above code is the QueryStringField property of the QueryStringParameter object. It's how we make the link between the parameter and its value.

The values for the @LastName and @FirstName parameters are assigned using ControlParameter objects, as we discussed in the previous article. We simply set the ControlParameter.ControlID property to the appropriate control and set the ControlParameter.PropertyName property to the appropriate property name of this control.

Finally, we call the SqlDataSource.Update() method to do the update operation for us. Then we redirect the user to the Default.aspx page where he will get a new version of the data with his updates.

If you want, you can provide the Delete functionality through the use of the SqlDataSource.DeleteCommand property, the SqlDataSource.DeleteParameters collection property and calling the SqlDataSource.Delete() method.

blog comments powered by Disqus
ASP.NET ARTICLES

- Implementing ASP.NET 4.0 Page.MetaDescriptio...
- ASP.Net Development Tips
- Intro to Sessions in ASP.Net
- Google Maps API Introduction in ASP.NET usin...
- Creating an ASP.NET 3.5 Gridview Image Galle...
- Encrypt QueryString in ASP.NET 3.5 using VB....
- ASP.NET 3.5 Drop Down List Controls
- Connect to Access Database with ASP.Net
- Secure Audio Streaming with ASP.Net and Flash
- Dynamic Sitemap and Navigation in ASP.Net
- Implement Gzip and Deflate Compression in AS...
- Run ASP.Net in Ubuntu with Apache
- ASP.Net Mono Website Contact Forms
- ASP.Net URL Rewriting Methods
- Murach`s ASP.NET 4 Web Programming with C# 2...

ASP Web Hosting ASP.Net Web Hosting Windows Web Hosting
ASP Free Forums 
 RSS  Tutorials RSS
 RSS  Forums RSS
 RSS  All Feeds
Site Map 
Request Media Kit
Write For Us Get Paid 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Privacy Policy 
Support 


© 2003-2012 by Developer Shed. All rights reserved. DS Cluster 10 - Follow our Sitemap
Most Popular Topics
All ASP.Net Tutorials