Data Access Components

• Download source files - 23.5 Kb

IDbProviderFactory, cSqlProviderFactory, cOleDbProviderFactory, cOdbcProviderFactory

IDbProviderFactory is an interface for creating ADO.NET common classes. The factory method design pattern is used. This class is implemented by cSqlProviderFactory, cOleDbProviderFactory and cOdbcProviderFactory. You could implement this interface by yourself to use another provider.

This factory method mechanism helps to import genericity. You could, for example, create classes and methods interacting with a certain database. You would only have to instantiate another provider factory when changing to another database. Let’s for example, assume that you work with an Oracle database and are planning to switch database, like for example, to SQL Server. Instead of having to change all the code, you would have to change only one line of code:

IDbProviderFactory factory = new cOdbcProviderFactory();
IDbProviderFactory factory = new cSqlProviderFactory();

The correct classes will be created automatically by the new provider factory!

Here is an example:

IDbProviderFactory factory = new cSqlProviderFactory();
 
string connectionString = 
  "server = localhost; database = NorthWind; uid = sa; pwd = ";
 
//create a connection of type System.Data.SqlClient.SqlConnection
IDbConnection connection = factory.CreateConnection(connectionString);
 
connectionString = 
  "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=northWind.mdb";
factory = new cOleDbProviderFactory();
//create a connection of type System.Data.OleDb.OleDbConnection
connection = factory.CreateConnection(connectionString);
connection.Open();
 
IDbCommand command;
command = factory.CreateCommand("SELECT * FROM Employee",connection);
Console.WriteLine("invalid connection");
 
IDbDataAdapter adapter = factory.CreateDataAdapter(command);
DataSet theSet = new DataSet();
adapter.Fill(theSet);

IDbProviderFactory methods implemented by both cSqlProviderFactory, cOdbcProviderFactory and cOleDbProviderFactory are:

• IDbConnection CreateConnection();
• IDbConnection CreateConnection(string connectionString);
• IDbCommand CreateCommand();
• IDbCommand CreateCommand(string commandText);
• IDbCommand CreateCommand(string commandText, IDbConnection connection);
• IDbCommand CreateCommand(string commandText, IDbConnection connection, IDbTransaction transaction);
• IDbDataAdapter CreateDataAdapter();
• IDbDataAdapter CreateDataAdapter(IDbCommand command);
• IDbDataAdapter CreateDataAdapter(string commandText, IDbConnection connection);
• IDbDataAdapter CreateDataAdapter(string commandText, string connectionString);

Methods that accept an interface instance can throw an exception of type cProviderException if it refers to an incorrect provider object.

cDB class

This class performs data access logic. cDB contains methods to fill DataSet objects and to perform non-query procedures and scalar procedures where only one value has to be generated. Thanks to the use of IDbProviderFactory, this class can be used for all providers.

Fill a DataSet:

string connectionString = 
   "server=localhost ; database=dbTest ; pwd= ; uid=sa";
//instantiate the class and provide a provider factory and connection string
cDB db = new cDB(new cSqlProviderFactory(), connectionString);
 
DataSet mySet = new DataSet();
 
//if the method returns false an error has occured
if(!db.RunDataSetProcedure("SELECT * FROM Message", out mySet))
    Console.WriteLine(db.ErrorMessage);
else
    Console.WriteLine(mySet.GetXml());

Fill a DataSet by using a stored procedure:

string connectionString = 
  "server=localhost ; database=NorthWind ; pwd= ; uid=sa";
cDB db = new cDB(new cSqlProviderFactory(), connectionString);
 
DataSet mySet = new DataSet();
 
//create a parameter collection (this is a collection of 
//IDbDataParameter objects and this interface is 
//implemented by SqlParameter, OleDbParameter…)
cIDbDataParameterCollection parameters = new cIDbDataParameterCollection();
IDbDataParameter param = coll.Add(new SqlParameter("@ID", SqlDbType.Int, 4));
param.Direction = ParameterDirection.Input;
param.Value = 100;
 
if(!db.RunDataSetProcedure("spSelectEmployeeByID", parameters, out mySet))
    Console.WriteLine(db.ErrorMessage);            
else
    Console.WriteLine(mySet.GetXml());

Perform a non-query procedure (you can also provide a parameter collection or use stored procedures):

cDB db = new cDB(new cSqlProviderFactory(),connectionString);
 
string title = "DALComponents";
string message = 
   "Data Access Components preventing reinventing the wheel...";
 
int recordsAffected;
string query = 
  "INSERT INTO Message (title, message) VALUES ('" + 
  title + "','" + message + "')";
 
if(!db.RunNonQueryProcedure(query,out recordsAffected))
    Console.WriteLine(db.ErrorMessage);            
else
    Console.WriteLine("records affected: " + recordsAffected);

Perform a scalar procedure (you can also provide a parameter collection or use stored procedures):

cDB db = new cDB(new cSqlProviderFactory(),connectionString);
 
object value;
string query = "SELECT COUNT(*) FROM Message";
 
if(!db.RunNonQueryProcedure(query,out value))
    Console.WriteLine(db.ErrorMessage);            
else
    Console.WriteLine("records affected: " + 
       Int32.Parse(value).ToString());

Other helpful cDB static methods:

string name = "Stephan Peters";
 
IDbDataParameter param = 
  cDB.SqlInputParam("@Name", SqlDbType.VarChar, 50, name);
param = cDB.OleDbInputParam("@Name", OleDbType.VarChar, 50, name);
param = cDB.SqlOutputParam("@ID", SqlDbType.Int,   4);
param = cDB.OdbcOutputParam("@ID", OdbcType..Integer, 4);

Constructors, methods and properties of cDB:

• cDB(IDbProviderFactory factory);
• cDB(IDbProviderFactory factory, string connectionString)
• bool RunDataSetProcedure(string procedure, out DataSet)
• bool RunDataSetProcedure(string procedure, CommandType commandType, out DataSet dataSet)
• bool RunDataSetProcedure(string procedure, cIDbDataParameterCollection parameters, out DataSet dataSet)
• bool RunNonQueryProcedure(string procedure, CommandType commandType, out int recordsAffected)
• bool RunNonQueryProcedure(string procedure, cIDbDataParameterCollection parameters, out int recordsAffected)
• bool RunNonQueryProcedure(string procedure, out int recordsAffected)
• bool RunScalarProcedure(string procedure, out object value)
• bool RunScalarProcedure(string procedure, CommandType commandType, out object value)
• bool RunScalarProcedure(string procedure, cIDbDataParameterCollection parameters, out object value)
• string ConnectionString { get; set; }
• string ErrorMessage { get };

<<abstract>>cSQLProcedure, cSQLDataSetProcedure, cSQLNonQueryProcedure, cSQLScalarProcedure

cSQLProcedure is an abstract class that is derived by cSQLDataSetProcedure. cSQLDataSetProcedure is used for procedures where a DataSet has to be filled. cSQLNonQueryProcedure is another derived class that is used for procedures where no result is wanted. Only the records affected are returned. cSQLScalarProcedure is the final derived class that is used for procedures with only one result. A value of type object is generated.

Generate a DataSet:

cSQLDataSetProcedure proc = 
  new cSQLDataSetProcedure(new cSqlProviderFactory());
proc.ConnectionString = connectionString;
proc.CommandText = "SELECT * FROM Message";
 
if(!proc.Execute())
    Console.WriteLine(proc.ErrorMessage);
else
    Console.WriteLine(proc.DataSet.GetXml());

Generate a DataSet with a stored procedure:

//don’t forget to set the correct CommandType (default: CommandType.Text)
cSQLDataSetProcedure proc = new 
    cSQLDataSetProcedure(new cSqlProviderFactory(), 
    connectionString,CommandType.StoredProcedure);
proc.CommandText = "spSelectEmployeeByID";
//cSQLProcedure contains a collection of type 
//cIDbDataParameterCollection that is derived by this class
proc.Parameters.Add(cDB.SqlInputParam("@ID", SqlDbType.Int, 4, 100));
 
if(!proc.Execute())
    Console.WriteLine(proc.ErrorMessage);
else
    Console.WriteLine(proc.DataSet.GetXml());

Perform a non-query procedure:

cSQLNonQueryProcedure proc = 
   new cSQLNonQueryProcedure(factory,connectionString);
proc.CommandType = CommandType.StoredProcedure;
proc.CommandText = "spInsertMessage";
                                                
IDbDataParameter idParam = 
   proc.Parameters.Add(cDB.SqlOutputParam("@ID", SqlDbType.Int, 4));
proc.Parameters.Add(cDB.SqlInputParam("@Title", 
   SqlDbType.VarChar,100, "This is the title."));
proc.Parameters.Add(cDB.SqlInputParam("@Message", 
   SqlDbType.VarChar, 500, "This is the message."));
 
if(!proc.Execute())
    Console.WriteLine(proc.ErrorMessage);
else
{
    Console.WriteLine("records affected: {0}", proc.RecordsAffected);
    Console.WriteLine("new id: {0}", 
        int.Parse(idParam.Value.ToString()).ToString());
}

Perform a scalar procedure:

cSQLScalarProcedure proc = new cSQLScalarProcedure(factory,connectionString);
proc.CommandType = CommandType.Text;
proc.CommandText = "SELECT COUNT(*) FROM Employee";
                                                
if(!proc.Execute())
    Console.WriteLine(proc.ErrorMessage);
else
    Console.WriteLine("result: {0}",proc.Value.ToString());

<<abstract>>cSQLMultiBase, cSQLMultiSelector, cSQLMultiScalar, cSQLMultiNonQuery

cSQLMultiBase is an abstract class for executing multiple procedures. This class contains a cQueryCollection containing objects of type cQuery. This query can be a stored procedure or a normal procedure. It is also possible to use parameters. cSQLMultiBase is derived by 3 different classes.

cSQLMultiSelector is a class for generating multiple tables. Those tables can be found in one DataSet. With cSQLMultiScalar, you can execute multiple scalar procedures and cSQLMultiNonQuery is used for executing multiple non-query procedures. You can decide whether to use transaction or not.

Perform multiple select procedures and put the results in one DataSet:

cSQLMultiSelector proc = new cSQLMultiSelector(factory, connectionString);
proc.Queries.Add(new cQuery("SELECT * FROM Employee", "Employees"));
                                                
cQuery query = proc.Queries.Add(new 
  cQuery("spSelectEmployeeByID", CommandType.StoredProcedure, "Employee"));
query.Parameters.Add(cDB.SqlInputParam("@ID", SqlDbType.Int, 4, 100));
 
if(!proc.Execute())
{
    //a property Error of type cMultiExecutionError is avaible
    Console.WriteLine(proc.Error.Message);
}
else
{
    DataSet mySet = proc.DataSet;
    DataTable employees = mySet.Tables["Employees"];
    DataTable employee = mySet.Tables["Employee"];
    //...
}

Perform multiple non-query procedures:

cSQLMultiNonQuery proc = new cSQLMultiNonQuery(factory,connectionString);
//use transaction
proc.IsTransactional = true;
                                                
string txt = 
  "INSERT INTO Message (Message) VALUES('This is a new message...')";
proc.Queries.Add(new cQuery(txt));
                                                
cQuery query = proc.Queries.Add(new 
  cQuery("spInsertEmployee", CommandType.StoredProcedure));
query.Parameters.Add(cDB.SqlOutputParam("@ID", SqlDbType.Int, 4));
query.Parameters.Add(cDB.SqlInputParam("@Name", 
  SqlDbType.VarChar, 50, "Stephan Peters"));
 
if(!proc.Execute())
{
    cMultiExecutionError error = proc.Error;

    Console.WriteLine(error.Message);
    if(error.IsProcedureFault)
        Console.WriteLine("The following" + 
          " procedure caused an error: " + error.CommandText);
}
else
{
//the records affected for all procedures
    int [] recordsAffected = proc.RecordsAffected;
    //the first parameter of the second query was an 
    //output value, so an id must have been generated
    if(recordsAffected[1] > 0)
    {
        int newID = 
          int.Parse(proc.Queries[1].Parameters[0].Value.ToString());
    }
}

Perform multiple scalar procedures:

cSQLMultiScalar proc = new cSQLMultiScalar(factory,connectionString);
proc.Queries.Add(new cQuery("SELECT COUNT(*) FROM Employee"));
proc.Queries.Add(new cQuery("SELECT SUM(salary) FROM Employee"));
                                                
if(!proc.Execute())
    Console.WriteLine(proc.Error.Message);
else
{
    object [] values = proc.Values;
    foreach(object value in values)
    {
        Console.WriteLine(value.ToString());
    }
}

cSQLMultiExecuter

First you need to know more about the following classes: cProcedure, cSelectProcedure, cNonQueryProcedure, cScalarProcedure.

• cProcedure is an abstract class representing a procedure. It contains properties that define a procedure like the CommandText, CommandType and Parameters (cIDbDataParameterCollection). It also contains an abstract method object GetResult(). To return the result of the procedure, you have to cast it to the type of the result.
• cSelectProcedure inherits cProcedure to provide a DataSet to be filled and the name of the table.
• cNonQueryProcedure inherits cProcedure to provide non-query functionality. It provides a property RecordsAffected.
• cScalarProcedure inherits cProcedure to provide scalar functionality. It provides a property Value of type object for holding the single result of the procedure.

cSQLMultiExecuter interacts with these classes to know what type of procedure to execute. It is able to execute a collection of procedures during one connection, by iterating and checking the type of procedure to execute. cSQLMultiExecuter contains a collection of type cProcedureCollection.

cSqlProviderFactory factory = new cSqlProviderFactory();
 
cSQLMultiExecuter proc = new cSQLMultiExecuter(factory,connectionString);
//you can of course use parameters and stored procedures
cSelectProcedure select = (cSelectProcedur) 
    proc.Procedures.Add(new 
    cSelectProcedure("SELECT * FROM Cursus", CommandType.Text));
proc.Procedures.Add(new 
    cScalarProcedure("SELECT COUNT(*) FROM Cursus", CommandType.Text));
proc.Procedures.Add(new 
    cNonQueryProcedure("INSERT INTO Message VALUES (‘the message’)");
 
if(!proc.Execute())
{
    cMultiExecutionError error = proc.Error;
    Console.WriteLine(error.Message);
}
else
{
    DataSet mySet = ((cSelectProcedure)proc.Procedures[0]).DataSet;
    int count = 
      int.Parse(((cScalarProcedure)proc.Procedures[1]).Value.ToString());
    //following could also be done like this:
    // (int)proc.Procedures[2].GetResult();
    int records = ((cNonQueryProcedure)proc.Procedures[2]).RecordsAffected;
}