HomeASP.NET More Ways to Update Databases using ASP.NE...
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 Michael Youssef Rating: / 15 November 06, 2007
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.
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:
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:
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
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.
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:
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
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.
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.
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.
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.
ASP.NET 
Secure Audio Streaming with ASP.Net and Flash
