The ObjectDataSource control can help you significantly reduce lines of your code (and "save" your time!) that you need to write in order to bind the data-bound controls in the presentation layer and the class that retrieves data from the database. Main steps of using ObjectDataSource control in data-driven Web applications were given in the article (by Mahesh Chand) and the article (by Rehaman SK). In this article I will show how you can use the ObjectDataSource control with the data access class (DA.dll, but it can be one of yours), described earlier, in your Web applications. The examples are written using C# and T-SQL.
OK! We will define our task as follows: to bind an ObjectDataSource control to a GridView control, that will allow Select, Update, Insert and Delete operations for the table, named S_PERSONS (with the three columns: Person_ID as Primary Key, FirstName and LastName); to use the compiled assembly DA.dll in order to access data; for Select, Update, Insert and Delete operations (for the S_PERSONS table) to use stored procedures.
To your web project add the form, named Persons; add reference to our DA.dll; to the folder App_Code add the class BL_Main.cs. Now our project looks like this (don't pay attention to the others classes and folders! It just for my test project):
Figure 1.
Add to the Persons form the following controls (see figure 2):
Figure 2.
Now we are ready to write code for our classes and stored procedures. We will consider in more details the Update case, as most interesting and complicated for our task. Indeed, to update the table we have to pass at least four parameters: three parameters for updating and one for primary key (for row that has to be updated); we have to receive some messages when updating, etc.
First of all we will write the stored procedure usp_S_PERSONS_Update, that allows us to update the S_PERSONS table in the cases if the "old" Person_Id equals the "new" Person_Id or there is no such Person_Id in the table, that equals the "new" Person_Id. For simplicity we will not use any "try--catch" block and treatment of errors.
Our stored procedure looks like this:
USE MICLIV;
GO
IF OBJECT_ID ( '[dbo].[usp_S_Persons_Update]', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.usp_S_Persons_Update;
GO
CREATE PROCEDURE dbo.usp_S_Persons_Update
@original_Person_ID nvarchar(9),
@Person_ID nvarchar(9),
@FirstName nvarchar(50),
@LastName nvarchar(50)
AS
if
(
not exists(select 1 from dbo.S_Persons where Person_ID = @Person_ID and @original_Person_ID <> @Person_ID)
)
begin
update dbo.S_Persons
set
Person_ID = @Person_ID,
LastName = @LastName,
FirstName = @FirstName
where Person_ID = @original_Person_ID;
end
GO
In a similar way we write the procedures usp_S_Persons_Insert, usp_S_Persons_Select, usp_S_Persons_Delete.
Well! Now we have the stored procedures and are ready to add some code to the BL_Main.cs class. That our class will be suitable for binding to the ObjectDataSurcePersons object, we add the attribute:
[DataObject(true)]
public class BL_Main
{
------------------------
If we check the "Show Only Data Components" box of the Data Source Configuration Wizard we will see only our class (as business object):
Figure 3.
Now, using our DA.dll (remember? We have made the reference to it) we will write the method usp_S_Persons_Update (named as the written stored procedure) for update data operation:
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.Data.SqlClient;
using System.ComponentModel;
using System.Collections.Generic;
/// <summary>
/// Summary description for BL_Main
/// </summary>
[DataObject(true)]
public class BL_Main
{
public BL_Main()
{
//
// TODO: Add constructor logic here
//
}
#region "forClass"
DA.DataAccess data_Acc = new DA.DataAccess(ConfigurationManager.
ConnectionStrings["MICLIVConnectionString"].ConnectionString);
#endregion
[DataObjectMethod(DataObjectMethodType.Update)]
public string usp_S_Persons_Update(string original_Person_ID,
string Person_ID, string FirstName, string LastName)
{
string sNameSP = "usp_S_Persons_Update";
List<SqlParameter> ListSqlParam = new List<SqlParameter>();
SqlParameter sqlParam0 =
new SqlParameter("@original_Person_ID",
original_Person_ID);
ListSqlParam.Add(sqlParam0);
SqlParameter sqlParam1 = new SqlParameter("@Person_ID",
Person_ID);
ListSqlParam.Add(sqlParam1);
SqlParameter sqlParam2 = new SqlParameter("@FirstName",
FirstName);
ListSqlParam.Add(sqlParam2);
SqlParameter sqlParam3 = new SqlParameter("@LastName",
LastName);
ListSqlParam.Add(sqlParam3);
return (data_Acc.execNonQuery(ListSqlParam, sNameSP));
}
//Similar code for usp_S_Persons_Delete,usp_S_Persons_Insert,
//usp_S_Persons_Select methods
}
As you can see we use the attribute [DataObjectMethod(DataObjectMethodType.Update)] in order to "allow" to choose only the methods , that are associated with the Update operation (see fig 4.):
Figure 4.
Similar attributes we use for select, insert and delete operations.
Some words about the original_Person_ID parameter. As you have paid attention (see the stored procedure above) our primary key field is updatable (this is we need the original key value to retrieve the record that has to be updated) and because of that the old value of the primary key is passed via the "original_{0}" parameter (see fig. 5 ). Of course, we can change (if we want) setting of the OldValuesParameterFormatString property of our ObjectDataSourcePersons objects (for example, to "old_Person_Id").
Figure 5.
You remember, that every method of our DA.dll always returns some value. In the "NonQuery" case (delete, insert, update operations) the method returns string. If the process is completed successfully, the returned string looks like that: "OK;6 rows affected". If the process is completed non successfully , the returned string is just the error message. Now with the help of the members of the string class (such methods as Substring, Contains) you can process the returned string according to your needed logic.
The returned string you can "catch" with the help of the ObjectDataSource's events (fig. 6) and such property of the ObjectDataSourceStatusEventArgs as ReturnValue.
Figure 6.
For example, you want to inform the user, that the Person_Id, to which he/she tried to change (update) the original Person_Id, already exists. You know, that in this case (according to our stored procedure and DA.dll) no row will be affected (this is: affected rows will be "-1"), but process will be finished without any errors (this is: "OK!"). In any other cases the information about our process will consist just of the returned value of our method. We can execute this task by means of the following code that we add to the ObjectDataSurcePersons_Updated method (in the Persons.aspx):
protected void ObjectDataSurcePersons_Updated(object sender,
ObjectDataSourceStatusEventArgs e)
{
string sMessage = e.ReturnValue.ToString();
if (sMessage.Contains("OK;-1"))
{
sMessage = "The Person_ID, to which you want to change" +
" original Person_ID, already exists!";
}
LabelMessage.Text = sMessage;
}
CONCLUSION
I hope that this article will help you to use the ObjectDataSource control with your own data access class in your Web applications.
Good luck in programming!