Programmatically Updating Databases using ASP.NET 2.0 SqlDataSource - Using ControlParameter objects with SqlDataSource
(Page 3 of 4 )
The code that does the insertion is in the InsertRow() method as follows:
protected int InsertRow()
{
SqlDataSource1.InsertCommand = "INSERT INTO Employees(FirstName,
LastName, Title)" +
"VALUES(@FirstName, @LastName, @Title)";
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
ControlParameter parameter = new ControlParameter();
parameter.Name = "FirstName";
parameter.ControlID = "TextBox1";
parameter.PropertyName = "Text";
parameter.Type = TypeCode.String;
SqlDataSource1.InsertParameters.Add(parameter);
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.
Next: Completing the Code Example >>
More ASP.NET Articles
More By Michael Youssef
/ 9 
