65.9K
CodeProject 正在变化。 阅读更多。
Home

数据访问组件

stephanpeters4000
starIconstarIcon
emptyStarIcon
starIcon
emptyStarIconemptyStarIcon

2.88/5 (17投票s)

2003年10月27日

4分钟阅读

viewsIcon

98835

downloadIcon

1443

用于与任何数据库交互的数据访问组件。

IDbProviderFactory, cSqlProviderFactory, cOleDbProviderFactory, cOdbcProviderFactory

IDbProviderFactory 是一个用于创建 ADO.NET 公共类的接口。使用了工厂方法设计模式。该类由 cSqlProviderFactory、cOleDbProviderFactory 和 cOdbcProviderFactory 实现。您可以自己实现此接口以使用其他提供程序。

这种工厂方法机制有助于导入泛化。例如，您可以创建与特定数据库交互的类和方法。更改到其他数据库时，您只需实例化另一个提供程序工厂。例如，假设您使用 Oracle 数据库并计划切换数据库，例如切换到 SQL Server。您不必更改所有代码，只需更改一行代码即可。

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

新的提供程序工厂将自动创建正确的类！

这是一个例子。

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);

cSqlProviderFactory、cOdbcProviderFactory 和 cOleDbProviderFactory 实现的 IDbProviderFactory 方法是

  • 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);

接受接口实例的方法可能会抛出 cProviderException 类型的异常，如果它引用了不正确的提供程序对象。

cDB 类

此类执行数据访问逻辑。cDB 包含填充 DataSet 对象的方法，以及执行非查询过程和标量过程（只需生成一个值）。由于使用了 IDbProviderFactory，因此此类可用于所有提供程序。

填充 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());

使用存储过程填充 DataSet

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());

执行非查询过程（您也可以提供参数集合或使用存储过程）

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);

执行标量过程（您也可以提供参数集合或使用存储过程）

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());

其他有用的 cDB 静态方法

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);

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 是一个抽象类，由 cSQLDataSetProcedure 派生。cSQLDataSetProcedure 用于需要填充 DataSet 的过程。cSQLNonQueryProcedure 是另一个派生类，用于不需要返回结果的过程。只返回受影响的记录数。cSQLScalarProcedure 是最后一个派生类，用于只有一个结果的过程。生成类型为 object 的值。

生成 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());

使用存储过程生成 DataSet

//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());

执行非查询过程

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());
}

执行标量过程

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 是一个用于执行多个过程的抽象类。此类包含一个 cQueryCollection，其中包含 cQuery 类型的对象。该查询可以是存储过程，也可以是普通过程。也可以使用参数。cSQLMultiBase 由 3 个不同的类派生。

cSQLMultiSelector 是一个用于生成多个表的类。这些表可以在一个 DataSet 中找到。使用 cSQLMultiScalar，您可以执行多个标量过程，而 cSQLMultiNonQuery 用于执行多个非查询过程。您可以决定是否使用事务。

执行多个 SELECT 过程并将结果放入一个 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"];
    //...
}

执行多个非查询过程

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());
    }
}

执行多个标量过程

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

首先，您需要了解以下类：cProcedure、cSelectProcedure、cNonQueryProcedure、cScalarProcedure。

  • cProcedure 是一个表示过程的抽象类。它包含定义过程的属性，如 CommandText、CommandType 和 Parameters (cIDbDataParameterCollection)。它还包含一个抽象方法 object GetResult()。要返回过程的结果，您必须将其强制转换为结果的类型。
  • cSelectProcedure 继承 cProcedure 以提供要填充的 DataSet 和表的名称。
  • cNonQueryProcedure 继承 cProcedure 以提供非查询功能。它提供了一个 RecordsAffected 属性。
  • cScalarProcedure 继承 cProcedure 以提供标量功能。它提供了一个类型为 object 的 Value 属性，用于保存过程的单个结果。

cSQLMultiExecuter 与这些类进行交互，以了解要执行的过程类型。它能够在一个连接中执行过程集合，通过迭代和检查要执行的过程类型。cSQLMultiExecuter 包含一个类型为 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;
}
© . All rights reserved.