Rafy framework-Execute SQL or stored procedure

Sometimes, developers do not want to manipulate the database through entities, but instead want to access the database directly through SQL statements or stored procedures. Rafy also provides a set of APIs to facilitate the implementation of such requirements.

Idbaccesser interface

To try to block the different identities of SQL statement parameters in each database, but also to make it easier for developers to implement parameterized queries. The Idbaccesser interface is available in the Rafy to facilitate the use of developers. The interface is defined as follows:

// <summary>// A db Accesser which can use formatted SQL to communicate with data base.// </summary> Public Interfaceidbaccesser:idisposable{// <summary>    //The underlying DB connection    // </summary>IDbConnection Connection {get;}// <summary>    /// data connection Structure    // </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& gt;    /// <param name= "Parameters" >if this SQL have some parameters, these is its parameters.</param>    //<returns>the number of rows effected.</returns>    intExecutetext (stringFormattedsql,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& gt;    /// <param name= "Parameters" >if this SQL have some parameters, these is its parameters.</param>    // <returns>dbnull or value object.</returns>    ObjectQueryValue (stringFormattedsql,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& gt;    /// <param name= "Parameters" >if this SQL have some parameters, these is its parameters.</param>    // <returns></returns>IDataReader Querydatareader (stringFormattedsql,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& gt;    /// <param name= "CloseConnection" >indicates whether to close the corresponding connection when the reader is CLO Sed?</param>    /// <param name= "Parameters" >if this SQL have some parameters, these is its parameters.</param>    // <returns></returns>IDataReader Querydatareader (stringFormattedsql,BOOLCloseConnection,params Object[] parameters);// <summary>    /// Query out a row from database.    // If There is no 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 have some parameters, these is its parameters.</param>    // <returns></returns>DataRow Querydatarow (stringFormattedsql,params Object[] parameters);// <summary>    /I 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& gt;    /// <param name= "Parameters" >if this SQL have some parameters, these is its parameters.</param>    // <returns></returns>DataTable querydatatable (stringFormattedsql,params Object[] parameters);// <summary>    /// Query out a row from database.    // If There is no records, return null.    // </summary>    /// <param name= "formattedsql" >a formatted SQL which format looks like the parameter of string.format</param& gt;    /// <param name= "Parameters" >if this SQL have some parameters, these is its parameters.</param>    // <returns></returns>Litedatarow Querylitedatarow (stringFormattedsql,params Object[] parameters);// <summary>    /I 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& gt;    /// <param name= "Parameters" >if this SQL have some parameters, these is its parameters.</param>    // <returns></returns>Litedatatable querylitedatatable (stringFormattedsql,params Object[] parameters);}

The interface uses a string format similar to that in String.Format to express parameters in SQL. And when you connect to different databases, generate the corresponding parameter format for the appropriate database.

Here's how to use it:

To execute a query code example:

var Bookrepo = RF. Concrete<bookrepository> (); using (var dba = dbaccesserfactory.create (Bookrepo)) {    DataTable table = dba. Querydatatable ("select * from Books where ID > {0}", 0);}

To execute a non-query code example:

var Bookrepo = RF. Concrete<bookrepository> (); using (var dba = dbaccesserfactory.create (Bookrepo)) {    int linesaffected = dba. Executetext ("DELETE from Books WHERE ID > {0}", 0);}

In addition, Dbaccesserfactory provides a way to build directly using the ' database connection's configuration name ' without a warehouse object, such as:

using (var dba = dbaccesserfactory.create ("JXC")) {    int linesaffected = dba. Executetext ("DELETE from Books WHERE ID > {0}", 0);}

When there are too many parameters, you can sequentially pass in in order:

using  (var dba = dbaccesserfactory.create (Bookrepo)) {for  (int  i = 0; i <; i++) {dba. Executetext ( "INSERT into book (Author,bookcategoryid,booklocid,code,content,name,price,publishe R) VALUES ({0},{1},{2},{3},{4},{5},{6},{7}) ", string . Empty, null , null , string. Empty, string . Empty, i.ToString (), null , string .    Empty); }}

Irawdbaccesser interface

Because the Idbaccesser interface encapsulates the change in the names of the parameters in the SQL statement to different databases, it is also more convenient for developers to use, so this interface is generally recommended. However, the Idbaccesser interface does not support calls to stored procedures. In addition, sometimes developers want to build their own native SQL statements and parameters, then need to use the Irawdbaccesser interface. (The interface definition is too long and is no longer posted here.) ）

This interface is used in a similar way to Idbaccesser, where the parameter names of a particular database need to be passed in SQL, and the parameters need to be constructed themselves, for example:

using (var dba = dbaccesserfactory.create (Bookrepo)) {for     (int i = 0; i <; i++)    {        dba. Rawaccesser.executetext (            "INSERT into book" (Author,bookcategoryid,booklocid,code,content,name,price, Publisher,id) VALUES (' ', NULL, NULL, ' ', ',:p 0, NULL, ' ',:p 1) ',            dba. RawAccesser.ParameterFactory.CreateParameter ("P0", i),            dba. RawAccesser.ParameterFactory.CreateParameter ("P1", i)            );}    }

In addition, the Irawdbaccesser interface can also use stored procedures, such as:

using  (var dba = dbaccesserfactory.create (Bookrepo)) {for  (int  i = 0; i <; i++) {dba. Rawaccesser.executeprocedure ( "insertbookprocedure" , dba. RawAccesser.ParameterFactory.CreateParameter ( "P0" , i), DBA.    RawAccesser.ParameterFactory.CreateParameter ( "P1" , i)); }}

PS: This article has been included in the Rafy user manual.

Rafy framework-Execute SQL or stored procedure