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

 


本文转自BloodyAngel博客园博客,原文链接:http://www.cnblogs.com/zgynhqf/p/4925195.html,如需转载请自行联系原作者

相关文章
|
1月前
|
存储 SQL 数据库
数据库sql语句-----游标和存储过程
数据库sql语句-----游标和存储过程
24 1
|
16天前
|
存储 SQL 数据库
SQL 语言:存储过程和触发器
SQL 语言:存储过程和触发器
31 6
|
17天前
|
存储 SQL Oracle
oracle 存储过程导出sql语句 导出为文件
oracle 存储过程导出sql语句 导出为文件
|
1月前
|
SQL 自然语言处理 关系型数据库
NL2SQL进阶系列(3):Data-Copilot、Chat2DB、Vanna Text2SQL优化框架开源应用实践详解[Text2SQL]
NL2SQL进阶系列(3):Data-Copilot、Chat2DB、Vanna Text2SQL优化框架开源应用实践详解[Text2SQL]
NL2SQL进阶系列(3):Data-Copilot、Chat2DB、Vanna Text2SQL优化框架开源应用实践详解[Text2SQL]
|
1月前
|
SQL 安全 前端开发
Go语言Gin框架安全加固:全面解析SQL注入、XSS与CSRF的解决方案
Go语言Gin框架安全加固:全面解析SQL注入、XSS与CSRF的解决方案
|
1月前
|
存储 SQL
SQL Server 存储过程 触发器 事务处理
SQL Server 存储过程 触发器 事务处理
|
1月前
|
存储 SQL 安全
使用SQL存储过程有什么好处 用视图有什么好处
使用SQL存储过程有什么好处 用视图有什么好处
12 0
|
10月前
|
存储 SQL
sql_存储过程、函数、分支、循环
sql_存储过程、函数、分支、循环
105 0
|
存储 SQL 安全
SQL 存储过程和函数的对比、变量、条件和处理程序、游标、流程控制详解+代码示例
SQL 存储过程和函数的对比、变量、条件和处理程序、游标、流程控制详解+代码示例

热门文章

最新文章