Introduction
XQuiSoft Data is an open source component for database neutral .NET applications. It is part of the XQuiSoft application framework, which is entirely open source. The source download includes the demo business component and demo web site. All the source code in this article can be found in the download. You can get the latest version of the project here.
This data component has a dependency on another open source project in the xquisoft framework, the xquisoft provider component. It is basically an implementation of the provider pattern for the 1.1 framework similar to that in the 2.0 framework. This open source project can be found here.
In this article, I will show examples of how to use the XQuiSoft Data component from the business tier of your application. The downloadable source will include a sample web project that calls these components, which I will only briefly discuss.
Demo Setup
First of all, make sure you download the correct version. The latest version at the time of this article is 2.2.50727.0. Earlier versions show a different way to use the component that may be obsolete in the future. Future versions will also include the demo projects. To get the required xqs-provider reference, you can either download the XQuiSoft Data binaries which include it in compiled form, or you can download the source from the xqs-provider downloads area.
The source download for XQuiSoft.Data contains three projects. The first is the Data class library source, which compiles to XQuiSoft.Data.dll. The second is a demo library that contains most of the code snippets in this article, which compiles to XQuiSoft.Data.Demo.dll. The third project is a web application project showing usage of the code in the sample code below.
- Create a new solution file in the folder above these projects, and add each project to it. I have a different solution that contains other core xquisoft projects, and I could not include it.
- Update the web.config of the web application. You must update the database connection string to a database of which you have an account with create table access. This access is required for the installer. If you prefer to run the scripts yourself feel free, then all the user in the connection string needs to have access to do is run the stored procedures created by the script.
- The solution should now run / debug normally. The first time you run it, set the start page as Install.aspx. Then navigate to the EmployeeList.aspx page to edit records.
The goal of this demo is to demonstrate how to design your business layer code. The user interface is bare minimum in appearance.
Basic Features
Ease of use: You don't have to worry about opening connections, creating commands, or disposing of resources such as data readers. You just have to know which command you want to call, and pass the command the appropriate parameter values.
Easy to learn: The method names on the supplied classes correspond to method names in the base .NET Framework. This should make it easy to upgrade your current code to use this component.
Extensibility: The data component is based on the provider pattern. This means that the DataManager class delegates the actual writing call to one or more configured providers (implementations) that derive from the base DataProvider class. Each provider defines the type of database that it interacts with, and how to fulfill database commands. Currently there is a built in SqlDataProvider and a OracleDataProvider.
Application Design
Configuration Setup
The first thing you need to do is add some configuration required by the data component. The first portion tells the framework where to load the class that will handle the next custom configuration section. There can be only one 'configSections' tag in your application configuration. So if you have other components with custom configuration sections, merge those with this one.
<configSections>
<sectionGroup name="XQuiSoft.Data">
<section name= "DataManager" type="XQuiSoft.Data.DataManagerConfiguration,
XQuiSoft.Data" />
</sectionGroup>
</configSections>
The next part you need is the configuration section that defines all your database connections, which was just described in the 'configSections'.
<XQuiSoft.Data>
<DataManager defaultProvider="Production">
<providers>
<add name="Development"
type="XQuiSoft.Data.SqlClient.SqlDataProvider, XQuiSoft.Data"
connectionString="Password=pw;Persist Security Info=True;
User ID=DATABASEUSER
;Initial Catalog=DATABASENAME;Data Source=SERVERNAME\INSTANCENAME" />
<add name="Production"
type="XQuiSoft.Data.SqlClient.SqlDataProvider, XQuiSoft.Data"
connectionString="Password=pw;Persist Security Info=True;
User ID=DATABASEUSER;
Initial Catalog=DATABASENAME;Data Source=SERVERNAME\INSTANCENAME" />
</providers>
</DataManager>
</XQuiSoft.Data>
The providers node contains child nodes for each database connection you want to configure. The type attribute is the database implementation provider that represents the type of database you wish to connect to. In the case of a built in type, you really don't need the full assembly name. In this case, "XQuiSoft.Data.SqlClient.SqlDataProvider" would have been sufficient. The provider component will search the executing assembly for the specified type if the assembly is not specified. The connectionString attribute must be valid for the database type you are using.
Notice that there are two attributes that can be on the DataManager node. You can either have a 'defaultProvider' which equals one of the names of the child nodes, or you can have a 'defaultProviderAppSettingName' which equals a setting name in the app settings node. The value of that setting must equal one of the provider names in the child nodes.
<appSettings file="Environment.config">
<add key="ENVCODE" value="Development" />
</appSettings>
The reason for doing this is so you can keep your web.config identical in all deployed environments. Then each environment can have a separate Environment.config file that has a value for "ENVCODE". That file should look identical to the appSettings node above, except without the file attribute specified.
Basic Entities
In our sample application, we are going to view and edit employees only. This is just to keep it simple. So the first thing we need to do is define our custom entity class 'Employee', and a strongly typed collection 'EmployeeCollection' which derives from collection base. This is provided in the download, and there is nothing special about these classes. I'm not going to explain them here.
Concrete Provider Implementation using DataManager
Start with a static service class defining methods required by your application. In our case we need to get employees, save, and delete employees. Next define a base provider class with abstract methods of the same signature. Next we need a concrete implementation class for EmployeeProvider called DbEmployeeProvider. Below is an outline of the class with a few of the method implementations shown for discussion. To see the EmployeeProvider base class or the EmployeeManager service class, see the code in the download.
public class DbEmployeeProvider: EmployeeProvider
{
#region Fields
#endregion Fields
#region Constructors
#endregion Constructors
#region Properties
#endregion Properties
#region Methods
public override void Initialize(string name,
NameValueCollection configValue)
public override ExecutionResults Install()
public override Employee GetEmployee(int id)
{
Procedure proc = new Procedure("Demo_GetEmployee");
proc.AddInputParameter("EmployeeID", DbType.Int32, id);
DataFactoryDelegate builder =
new DataFactoryDelegate(this.AddEmployee);
EmployeeCollection col = new EmployeeCollection();
dataProvider_.Execute(proc, builder, col);
if (col.Count > 0)
return col[0];
else
return null;
}
public override EmployeeCollection GetSubOrdinates(int managerID)
public override bool SaveEmployee(Employee item)
{
Procedure proc = new Procedure("Demo_SaveEmployee");
proc.AddParameter("EmployeeID",
DbType.Int32, ParameterDirection.InputOutput, item.ID);
proc.AddInputParameter("ManagerID", DbType.Int32, item.ManagerID);
proc.AddInputParameter("FirstName", DbType.AnsiString, item.FirstName);
proc.AddInputParameter("LastName", DbType.AnsiString, item.LastName);
int rows = dataProvider_.ExecuteNonQuery(proc);
Parameter prmID = proc.Parameters.Find("EmployeeID");
item.ID = dataProvider_.GetInt32(prmID.Value);
return (rows > 0);
}
public override bool DeleteEmployee(int id)
protected virtual void AddEmployee(IList target, IDataRecord record)
{
Employee item = new Employee();
item.ID = dataProvider_.GetInt32(record, "EmployeeID", -1);
item.ManagerID = dataProvider_.GetInt32(record, "ManagerID", -1);
item.FirstName = dataProvider_.GetString(record, "FirstName");
item.LastName = dataProvider_.GetString(record, "LastName");
target.Add(item);
}
#endregion Methods
}
Stored procedures can be called by instatiating the Procedure class with the required name, adding parameter values, and then calling the appropriate Execute method. Note that a procedure parameter name should not include a database provider specific prefix. The data provider will handle adding that, if applicable, before the parameter is passed to ADO.NET. The default SqlDataManager provider class adds a @ prefix to ADO.NET parameters if it does not already exist.
The GetEmployee method executes the procedure, then another method AddEmployee is passed into the DataFactoryDelegate parameter of the Execute method. This delegate method will be called for each record the data reader returned from the database connection. This method instantiates a new data object instance (in this case class Employee), populates the properties, and adds the new item to the supplied collection.
The SaveEmployee method uses a procedure also, but it inserts or updates a record (and does not return any records.) It calls ExecuteNonQuery passing the Procedure instance filled with parameters. The output parameter defined on the procedure is used to populate the id of the record after the ExecuteNonQuery call completes.
Provider Model Configuration
In order to use this EmployeeManager service, you'll need to configure it to use the DbEmployeeProvider as follows. Note you'll have to merge the contents of this new sectionGroup into the same configSections node used to configure XQuiSoft.Data. Notice the connection name is not specified below. When the connectionName is not specified, the DbEmployeeProvider uses the default DataProvider defined in the DataManager section of the configuration.
<configSections>
<sectionGroup name="XQuiSoft.Data.Demo">
<section name= "EmployeeManager"
type="XQuiSoft.Data.Demo.EmployeeManagerConfiguration" />
</sectionGroup>
</configSections>
<XQuiSoft.Data.Demo>
<EmployeeManager>
<providers>
<add name="Default"
type="XQuiSoft.Data.Demo.Data.DbEmployeeProvider, XQuiSoft.Data.Demo" />
</providers>
</EmployeeManager>
</XQuiSoft.Data.Demo>
If your database is shared so that records are split across databases, you will likely want to specify each of the multiple connections to be used by the DbEmployeeProvider, or the default connection could be used to execute a query on a main database connection to determine which connection holds the data for a given query. Database sharing design is beyond the scope of this article. However, the DataManager does support an easy programmatic experience for scenarios that require database sharing.
Application Usage (UI)
Usage of the service classes from the user interface is really simple. First we need a form that can bind to the results. Since EmployeeCollection derives from CollectionBase, it implements IList which is what the DataGrid is designed to work with (not just datasets).
EmployeeCollection emps = EmployeeManager.GetSubordinates(mgrId);
DataGrid1.DataSource = emps;
DataGrid1.DataBind();
That's it! You can now swap out the DbEmployeeProvider with a different implementation, and the user interface code does not have to change, or even be recompiled!
Conclusion
I've convered the basics features and usage of the XQuiSoft Data component. Some of the features are really inherited from the XQuiSoft Provider component, so be sure to check that out too. I would be happy to hear your results in using these components. I didn't cover all the code that you'll see in the demo project. Some of the code is for supporting the provider auto-installation feature. I may discuss that feature in a future article. It is not really necassary in a simple application like this demo, but is very useful in a plugin architecture.
If you start a new project, consider the benefits of XQuiSoft Data. Evaluate it, and see if it fits into your toolbox. It would be great to hear feedback on your evaluation in either case.
History:
- April 27, 2006: Initial submission of article
- April 28, 2006: Added extra download instructions
- June 16, 2009: Updated to use the latest features of
XQuiSoft.Data introduced in the last year and a half. This update was way overdue.
