Rafy 框架 - 执行SQL或存储过程
有时候，开发者不想通过实体来操作数据库，而是希望通过 SQL 语句或存储过程来直接访问数据库。Rafy 也提供了一组 API 来方便实现这类需求。
IDbAccesser 接口
为了尽量屏蔽各数据库中 SQL 语句参数的不同标识，同时也为了使开发者更简单地实现参数化的查询。Rafy 中提供了 IDbAccesser 接口来方便开发者使用。接口定义如下：
/// <summary> /// A db accesser which can use formatted sql to communicate with data base. /// </summary> public interface IDbAccesser : IDisposable { /// <summary> /// The underlying db connection /// </summary> IDbConnection Connection { get; } /// <summary> /// 数据连接结构 /// </summary> DbConnectionSchema ConnectionSchema { get; } /// <summary> /// Gets a raw accesser which is oriented to raw sql and <c>IDbDataParameter</c>。 /// </summary> IRawDbAccesser RawAccesser { get; } /// <summary> /// Execute a sql which is not a database procudure, return rows effected. /// </summary> /// <param name="formattedSql">a formatted sql which format looks like the parameter of String.Format</param> /// <param name="parameters">If this sql has some parameters, these are its parameters.</param> /// <returns>The number of rows effected.</returns> int ExecuteText(string formattedSql, params object[] parameters); /// <summary> /// Execute the sql, and return the element of first row and first column, ignore the other values. /// </summary> /// <param name="formattedSql">a formatted sql which format looks like the parameter of String.Format</param> /// <param name="parameters">If this sql has some parameters, these are its parameters.</param> /// <returns>DBNull or value object.</returns> object QueryValue(string formattedSql, params object[] parameters); /// <summary> /// Query out some data from database. /// </summary> /// <param name="formattedSql">a formatted sql which format looks like the parameter of String.Format</param> /// <param name="parameters">If this sql has some parameters, these are its parameters.</param> /// <returns></returns> IDataReader QueryDataReader(string formattedSql, params object[] parameters); /// <summary> /// Query out some data from database. /// </summary> /// <param name="formattedSql">a formatted sql which format looks like the parameter of String.Format</param> /// <param name="closeConnection">Indicates whether to close the corresponding connection when the reader is closed?</param> /// <param name="parameters">If this sql has some parameters, these are its parameters.</param> /// <returns></returns> IDataReader QueryDataReader(string formattedSql, bool closeConnection, params object[] parameters); /// <summary> /// Query out a row from database. /// If there is not any records, return null. /// </summary> /// <param name="formattedSql">a formatted sql which format looks like the parameter of String.Format</param> /// <param name="parameters">If this sql has some parameters, these are its parameters.</param> /// <returns></returns> DataRow QueryDataRow(string formattedSql, params object[] parameters); /// <summary> /// Query out a DataTable object from database by the specific sql. /// </summary> /// <param name="formattedSql">a formatted sql which format looks like the parameter of String.Format</param> /// <param name="parameters">If this sql has some parameters, these are its parameters.</param> /// <returns></returns> DataTable QueryDataTable(string formattedSql, params object[] parameters); /// <summary> /// Query out a row from database. /// If there is not any records, return null. /// </summary> /// <param name="formattedSql">a formatted sql which format looks like the parameter of String.Format</param> /// <param name="parameters">If this sql has some parameters, these are its parameters.</param> /// <returns></returns> LiteDataRow QueryLiteDataRow(string formattedSql, params object[] parameters); /// <summary> /// Query out a DataTable object from database by the specific sql. /// </summary> /// <param name="formattedSql">a formatted sql which format looks like the parameter of String.Format</param> /// <param name="parameters">If this sql has some parameters, these are its parameters.</param> /// <returns></returns> LiteDataTable QueryLiteDataTable(string formattedSql, params object[] parameters); }
该接口使用类似于 String.Format 中的字符串格式来表达 SQL 中的参数。并在连接不同的数据库时，生成相应数据库对应的参数格式。
具体使用方法如下：
执行查询代码示例：
var bookRepo = RF.Concrete<BookRepository>(); using (var dba = DbAccesserFactory.Create(bookRepo)) { DataTable table = dba.QueryDataTable("SELECT * FROM Books WHERE id > {0}", 0); }
执行非查询代码示例：
var bookRepo = RF.Concrete<BookRepository>(); using (var dba = DbAccesserFactory.Create(bookRepo)) { int linesAffected = dba.ExecuteText("DELETE FROM Books WHERE id > {0}", 0); }
另外，DbAccesserFactory 中也提供了不通过仓库对象，而直接使用‘数据库连接的配置名’进行构建的方法，例如：
using (var dba = DbAccesserFactory.Create("JXC")) { int linesAffected = dba.ExecuteText("DELETE FROM Books WHERE id > {0}", 0); }
参数过多时，则依次按顺序传入即可：
using (var dba = DbAccesserFactory.Create(bookRepo)) { for (int i = 0; i < 10; i++) { dba.ExecuteText( "INSERT INTO Book (Author,BookCategoryId,BookLocId,Code,Content,Name,Price,Publisher) VALUES ({0},{1},{2},{3},{4},{5},{6},{7})", string.Empty, null, null, string.Empty, string.Empty, i.ToString(), null, string.Empty ); } }
IRawDbAccesser 接口
由于 IDbAccesser 接口封装了 SQL 语句中参数对应不同数据库中名称的变化，同时也更方便开发者使用，所以一般情况下，都推荐使用该接口。但是，IDbAccesser 接口并不支持存储过程的调用。另外，有时开发者希望自己来构建原生的 SQL 语句和参数，这时，就需要用到 IRawDbAccesser 接口了。（接口定义过长，这里不再贴出。）
该接口的使用方法与 IDbAccesser 类似，不同的地方在于 SQL 中需要传入特定数据库的参数名，并且参数需要自行构造，例如：
using (var dba = DbAccesserFactory.Create(bookRepo)) { for (int i = 0; i < 10; i++) { dba.RawAccesser.ExecuteText( "INSERT INTO Book (Author,BookCategoryId,BookLocId,Code,Content,Name,Price,Publisher,Id) VALUES ('', NULL, NULL, '', '', :p0, NULL, '', :p1)", dba.RawAccesser.ParameterFactory.CreateParameter("p0", i), dba.RawAccesser.ParameterFactory.CreateParameter("p1", i) ); } }
另外，IRawDbAccesser 接口也可以使用存储过程了，例如：
using (var dba = DbAccesserFactory.Create(bookRepo)) { for (int i = 0; i < 10; i++) { dba.RawAccesser.ExecuteProcedure( "InsertBookProcedure", dba.RawAccesser.ParameterFactory.CreateParameter("p0", i), dba.RawAccesser.ParameterFactory.CreateParameter("p1", i) ); } }
PS:该文已经纳入《 Rafy 用户手册》中。
