More Ways to Update Databases using ASP.NET 2.0 SqlDataSource - Using the QueryStringParameter and the ControlParameter
(Page 5 of 5 )
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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
