HomeASP.NET Programmatically Updating Databases using ...
Programmatically Updating Databases using ASP.NET 2.0 SqlDataSource
In the previous article we talked about using the SqlDataSource control programmatically to obtain a result set from the database using a Data Reader object and a Data View object. We used an HtmlTable control to create a visual representation to hold the returned result set. Today we are going to use the SqlDataSource control to insert data into the Employees table of the Northwind database.
Contributed by Michael Youssef Rating: / 12 November 05, 2007
In case you haven't read the previous article I will show you the relevant code from it again, but I advise you to read the complete article when you can. Meanwhile, before we learn how to insert data, let's run a similar version of the previous article's code again. Next is the complete code for the code behind the Default.aspx.cs file.
using System; using System.Data; using System.Configuration; 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.Collections; using System.Data.Common;
public partial class _Default : System.Web.UI.Page { SqlDataSource SqlDataSource1 = new SqlDataSource();
if (SqlDataSource1.DataSourceMode == SqlDataSourceMode.DataReader) { foreach (DbDataRecord record in iteratorObject) { HtmlTableRow row = new HtmlTableRow();
When you run the code you will get the result shown in the next screen shot.
We have made some minor modifications to the previous article's code. We've simply moved the code that initializes the SqlDataSource control to a method called InitializeDataSource() and called this method from the Page_Load() event handler method. We have also added column headers to the table, as well as the InsertRow() method which we will implement today.
If you don't understand how we got this result set back without writing any ADO.NET Data Access code, it's the SqlDataSource control that accomplished this for us. We passed the connection string and the T-SQL SELECT statement through the SqlDataSource.ConnectionString and the SqlDataSource.SelectCommand properties, and it opened the database for us and retrieved the data. The catch here is that we need to write code to handle this returned result set, which can be a DataView object or a Data Reader object; actually it's an object of type IDataReader, depending on the value of the SqlDataSource.DataSourceMode property.
Both the DataView and the IDataReader implement the IEnumerable interface, and this is what we are asking for. We use the IEnumerable interface on the returned object to iterate through its collection of rows, create HtmlTableRow objects for each row and add them to the table. Please consult my previous article for more information. Now let's continue to the next section where we are going to add the functionality of inserting rows into the table.
We need to provide the user with text box controls so that they can enter values for a new record, and then click on a button to insert these values into the Employees table. The T-SQL INSERT statement that we need looks like this:
INSERT INTO Employees(FirstName, LastName, Title) VALUES(@FirstName, @LastName, @Title)
Where are @FirstName, @LastName and @Title are parameters? If you have read my ADO.NET articles you must have seen the examples that create SqlParameter objects that are needed in order to execute a parameterized T-SQL statement on the server. With the SqlDataSource control, we don't create SqlParameter objects; we create parameters of type System.Web.UI.Controls.Parameter or from a specific parameter class that inherits from the System.Web.UI.Controls.Parameter class. What is this supposed to mean? First let's see the code and try it, and then we will talk about this parameter issue.
using System; using System.Data; using System.Configuration; 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.Collections; using System.Data.Common;
public partial class _Default : System.Web.UI.Page
{
SqlDataSource SqlDataSource1 = new SqlDataSource();
Run the page and insert values in the text boxes as shown in the following screen shot.
Click on insert to insert a record into the database. The page will return the new record as part of the table control as shown in the next screen shot.
Without writing any ADO.NET Data Access code we managed to insert a record into the database table. Before we explain the code that we have written above I have to tell you that you are not going to write such code when you use Data-Bound Controls. If you are using a Data-Bound Control, like the GridView control, the control itself will pass the new values to the SqlDataSource control along with the parameters (in most cases as we are going to discuss in upcoming articles about Data-Bound Controls).
ControlParameter parameter = new ControlParameter();
parameter.Name = "FirstName";
parameter.ControlID = "TextBox1";
parameter.PropertyName = "Text";
parameter.Type = TypeCode.String;
SqlDataSource1.InsertParameters.Add(parameter);
parameter = new ControlParameter();
parameter.Name = "LastName";
parameter.ControlID = "TextBox2";
parameter.PropertyName = "Text";
parameter.Type = TypeCode.String;
SqlDataSource1.InsertParameters.Add(parameter);
parameter = new ControlParameter();
parameter.Name = "Title";
parameter.ControlID = "TextBox3";
parameter.PropertyName = "Text";
parameter.Type = TypeCode.String;
SqlDataSource1.InsertParameters.Add(parameter);
return SqlDataSource1.Insert();
}
As we selected data from the table using the SelectCommand property and the Select() method we do the same with the insertion operation through the use of the InsertCommand property and the Insert() method. You set the SqlDataSource.InsertCommand property to the T-SQL INSERT statement, including the parameters that you want use in the statement as shown next, and then call the SqlDataSource.Insert() method. This method does the insertion and returns the number of rows that have been inserted. The T-SQL INSERT that we have assigned to the InsertCommand property is very simple:
INSERT INTO Employees(FirstName, LastName, Title) VALUES(@FirstName, @LastName, @Title)
Now let's talk about the parameters.
Unlike the SqlCommand object, which accepts parameters of type SqlParameter, the SqlDataSource control accepts parameters of type System.Web.UI.Controls.Parameter or its derived types as we are going to see. The parameters that are associated with the Insert() method are stored in the SqlDataSource.InsertParameters collection property. So we have the SqlDataSource.InsertCommand property, the SqlDataSource.Insert() method and the SqlDataSource.InsertParameters as the required ingredients for the insertion operation.
In the code example, we used the text box controls to obtain values for the parameters of the T-SQL INSERT statement. Because the values are obtained from a control we have used a derived type of parameter called ControlParameter. The ControlParameter represents a parameter that gets its value from a control.
You have other types of parameters, too, such as the SessionParameter, CookieParameter, FormParameter, QueryStringParameter and ProfileParameter. As you might have guessed, each one of those parameters get its value in a different way. For example, the SessionParameter object gets the value for the parameter from a Session object. For our example, we used the ControlParameter to get the parameter's value from a text box control as shown next
We simply create an instance of the ControlParameter object and assign the name of the parameter to its Name property. Note that we don't prefix the parameter name with the @ sign as we do in T-SQL code. Then we tell the ControlParameter which control on the form has the value for this parameter, through the ControlID property, and which property of this control contains the value itself, through the use of the PropertyName property. We finally set the data type for the parameter to a value of the TypeCode enumeration and add this ControlParameter to the SqlDataSource.InsertParameters collection property. We do the same for all the parameters we need to execute the INSERT statement on the server.
If you haven't used the SqlDataSource control with Data-Bound Controls then you might wonder why we would use these types of parameters. Actually, using those parameter types is very helpful where you need to accept a value from the user and return a result set based on this value in a GridView. Maybe you accept the EmployeeID from a QueryString, through a QueryStringParameter object, or from a Session object, through a SessionParameter object, and return related data depending on this EmployeeID. Don't worry, when you read my article on Data-Bound controls you will understand how exactly you can use those parameter types. For now let's continue with explaining the example.
We have explained the code of the InsertRow() method so let's continue explaining the rest of the code. Each time the page initializes we add the SqlDataSource control to the page through the Controls.Add() method and assign its ConnectionString property to a valid value that connects the control to the Northwind database as shown in the Page_Init() event handler method.
In the Page_Load() event handler method we determine the action that we want to take. If the page is loading for the first time we simply call the InitializeDataSource() method to create the HtmlTable control. This control, in turn, contains the data we have returned from the database through the SqlDataSource control. If the page is posted back to the server, which means that the user has clicked on the button control and needs to insert a record in the database table, we call the InsertRow() method to insert a record into the Employees table based on the T-SQL INSERT statement assigned to the SqlDataSource.InsertCommand property, and also based on the values that are entered into the TextBoxes.
The InsertRow() method returns a value that indicates how many rows have been inserted into the database. We test the return value and if it is equal to 1 we print out a simple message to the user through the Response.Write() method. After that we call the InitializeDataSource(), again, to get the data from the database table.
Response.Write("A New Row has been inserted into the Employees Table");
}
InitializeDataSource();
}
}
catch
{
Response.Write("An Error has occured");
}
}
We have placed our code inside a try/catch block to catch any exceptions. In the next article, we are going to talk about updating operations using the SqlDataSource.
ASP.NET 
/ 12 
Programming the ASP.NET 2.0 SqlDataSource Control
