Farseer.net Lightweight Open Source Framework intermediate: Execute SQL statements

Navigation

Catalog: Farseer.net Lightweight Open source Framework Catalog

Previous: Farseer.net lightweight open source Framework intermediate: The use of transactions

Filed under: Farseer.net Lightweight Open source Framework intermediate: Dbfactory Data Factory

Use custom SQL, or stored procedures. Still used: Dbexecutor, yes, the framework eventually executes, all in this class to bring in the generated SQL.

So you can also use your own incoming SQL to perform the results you want.

Let's take a look at some of these methods. (In fact, much like what you've been exposed to before: DBHelper ....) ）

1         /// <summary>2         ///Returns the first column of data in the first row3         /// </summary>4         /// <param name= "Cmdtype" >Execution Mode</param>5         /// <param name= "Cmdtext" >sql or stored procedure name</param>6         /// <param name= "Parameters" >Parameters</param>7          Public ObjectExecuteScalar (CommandType Cmdtype,stringCmdtext,paramsdbparameter[] Parameters)8 9         /// <summary>Ten         ///returns the number of rows affected One         /// </summary> A         /// <param name= "Cmdtype" >Execution Mode</param> -         /// <param name= "Cmdtext" >sql or stored procedure name</param> -         /// <param name= "Parameters" >Parameters</param> the          Public intExecuteNonQuery (CommandType Cmdtype,stringCmdtext,paramsdbparameter[] Parameters) -  -         /// <summary> -         ///return Data (IDataReader) +         /// </summary> -         /// <param name= "Cmdtype" >Execution Mode</param> +         /// <param name= "Cmdtext" >sql or stored procedure name</param> A         /// <param name= "Parameters" >Parameters</param> at          PublicIDataReader Getreader (CommandType cmdtype,stringCmdtext,paramsdbparameter[] Parameters) -  -         /// <summary> -         ///return Data (DataSet) -         /// </summary> -         /// <param name= "Cmdtype" >Execution Mode</param> in         /// <param name= "Cmdtext" >sql or stored procedure name</param> -         /// <param name= "Parameters" >Parameters</param> to          PublicDataSet GetDataSet (CommandType cmdtype,stringCmdtext,paramsdbparameter[] Parameters) +  -         /// <summary> the         ///return Data (DataTable) *         /// </summary> $         /// <param name= "Cmdtype" >Execution Mode</param>Panax Notoginseng         /// <param name= "Cmdtext" >sql or stored procedure name</param> -         /// <param name= "Parameters" >Parameters</param> the          PublicDataTable getdatatable (CommandType cmdtype,stringCmdtext,paramsdbparameter[] Parameters)

CommandType Cmdtype: enumeration, meaning which command to use for transmission.

1 namespaceSystem.Data2 {3     //Summary:4     //specifies how the command string is interpreted. 5      Public enumCommandType6     {7         //Summary:8         //SQL text command. Default )9Text =1,Ten         // One         //Summary: A         //the name of the stored procedure.  -StoredProcedure =4, -         // the         //Summary: -         //the name of the table.  -TableDirect = +, -     } +}

The string Cmdtext:sql statement, or the name of the stored procedure.

params dbparameter[] Parameters: Parameter list

Like what:

1         //The most primitive way. 2         using(Dbexecutor db =NewDbexecutor (Databasetype.sqlserver,"User Id=sa; password=123456; Pooling=true;data Source=.;i Nitial Catalog=farseer;", -, System.Data.IsolationLevel.ReadCommitted))3         {4             //performs an insert operation. 5Db. ExecuteNonQuery (System.Data.CommandType.Text,"INSERT into Users (Username,roleid) Values (' Zhang San ', 0)");6             //when the operation is complete, remember to close and release the resource. If you use a using the following as I do, you can not do it. If it doesn't work, it must be added. 7Db. Close (true);8}

The demo above is done with an insert operation.  ExecuteNonQuery returns the "number of rows affected" int type. This means that the number of affected rows is recorded in the database table.

1         //using Dbfactory, Dbexecutor objects are created2         //the 1th parameter 0 represents the index entry for the database configuration: Dbconfig. In: ~/app_data/db.config3         using(Dbexecutor db = Dbfactory.createdbexecutor (0, System.Data.IsolationLevel.ReadCommitted))4         {5             //performs an insert operation. 6DataTable dt = db. Getdatatable (System.Data.CommandType.Text,"SELECT * from Users");7             //can rely on the extension method to turn the DataTable into list<user> Oh8list<users> lst = dt. Tolist<users>();9             //when the operation is complete, remember to close and release the resource. If you use a using the following as I do, you can not do it. If it doesn't work, it must be added. TenDb. Close (true); One}

The above demo. Use the Getdatatable method to get a DataTable. The framework provides an extension method to the DataTable that goes to the List entity class.

1         //the Dbexecutor object was created with the users generic2         //The database connection for this entity is queried by the reflection results for the users cache. (In fact, go to the Database Configuration index Entry)3         using(Dbexecutor db = dbfactory.createdbexecutor<users>(System.Data.IsolationLevel.ReadCommitted))4         {5             //here, we used a new class: Sqlserverprovider or Dbprovider.6             //Sqlserverprovider inheritance in Dbprovider7             varProvider =NewSqlserverprovider ();8 9 Ten             //or you can use Dbfactory.createdbprovider to determine your database type using the incoming users (you might not have SQL Server) One             //Dbprovider is used to provide us with some features of different databases. For example, the parameters for creating a database below, or the protection symbol for a database, or the prefix of a parameter symbol. These are not the same for different databases.  A             varProvider = dbfactory.createdbprovider<users>(); -  -  theDb. ExecuteNonQuery (System.Data.CommandType.Text,"Update users set username = @userName where id = @id", provider. Createdbparam ("UserName","Zhang San", Dbtype.string, -)); -}

In this way, a parameter is used to commit the method. Can avoid the most dangerous vulnerability of ASP at the time: SQL injection

Dbprovider

What is Dbprovider, we first look at the way Dbprovider works, such as:

Each database type will contain its own provider. This provider is mainly to provide the unique characteristics of the database.

For instance, in SQL Server. The protection symbol for the table name, field name is: [] in brackets. To prevent some names from being defined as SQL Server specific keywords, it can be understood as an escape character. and MySQL uses: ' two single quotes.

Summarize

What do you think? The feeling is not back to the DBHelper era. If you're worried about the slow performance of ORM, with this kind of strong break, you can use this approach to access the underlying.

The level of access to this ORM performance can be divided into three types:

Use ORM completely to return the list entity.

Only ORM operations are used, but DataTable data is returned. The performance cost of the DataTable to the entity is reduced (very expensive, seemingly foreign documents are so translated).

Not used at all. Back to the DBHelper way.

The above three ways are supported.

Navigation

Catalog: Farseer.net Lightweight Open source Framework Catalog

Previous: Farseer.net lightweight open source Framework intermediate: The use of transactions

Filed under: Farseer.net Lightweight Open source Framework intermediate: Dbfactory Data Factory

Advertising time

QQ Group:116228666 (farseer.net Open source frame exchange) Please specify: Blog Park

Farseer.net is an ORM framework + Common tool + extension collection.

Farseer: Prophets and seers usually offer tricks and strategies in certain situations. Also hope that the framework can provide you with maximum convenience.

ORM: The English name is:object relational(relationship) Mapping(map)

Farseer.net's goal is: Quick start, rapid development, simple and convenient.

1 New 1 " Zhang San " }. Insert ()

Farseer.net Lightweight Open Source Framework intermediate: Execute SQL statements