asp: Using database classes to execute SQL statements in the EF framework

Introduction

Starting with EF6, the Datebase class was added, and he obtains instances of this class from the DbContext object . Can be used to manage the actual database that supports the database context or connection. This includes creating, deleting, and checking the existence of a database.

Before 6, we used EF, and we needed to use ADO to execute SQL statements directly. Because of some short boards in the LINQ query itself, such as the following scenario: LINQ shows its own shortcomings in the case of tens of thousands of data capacity, when it is necessary to federate query statistics below 3 tables. Someone with some experience must have thought of using stored procedures to put complex logical operations in the database execution

Here I have some common methods provided by the database class to help us read and write the database directly in the program.

Body 1. Sqlquery<t> (string sql,params object[] parameter)

Use Sqlquery<t> () to execute SQL statements, stored procedures, views

Storage Layer Code

        Public iqueryable<tentity> querysql<tentity> (string strSQL, params sqlparameter[] dbparameter) where Tentity:class        {            return dbcontext. Database.sqlquery<tentity> (strSQL, DbParameter). Asqueryable<tentity> ();        }

Dbcontext:ef context

Execute SQL statement

var data = new Repositorybase (). Querysql<customerlistviewmodel> (SQLSTR);

Customerlistviewmodel: Is the DTO object of your query results, in this way, only the properties of your dto are consistent with the returned fields of the query results, you can tell the query results map to the DTO

Executing stored procedures

        Public list<storelistviewmodel> querystorelistsplit (pagination pagination, string uId, String shopcode)            {list<storelistviewmodel> Dtoresult = new list<storelistviewmodel> ();            String sqlwhere = "";            String msg = ""; String sqlstr = "EXEC [dbo]."            [Sp_customerstorelist] @UId, @Where, @msg out "; if (!string.            IsNullOrEmpty (Shopcode)) {sqlwhere = "and S.f_shopcode = '" + Shopcode + "'";                 } sqlparameter[] parms = new sqlparameter[] {new SqlParameter ("@UId", UId),            New SqlParameter ("@Where", Sqlwhere), New SqlParameter ("@msg", system.data.sqldbtype.nvarchar,200)            }; PARMS[2].            Direction = System.Data.ParameterDirection.Output;            Dtoresult = Customerrepository.getcustomerstorelist (sqlstr, pagination, parms); msg = parms[2].            Value.tostring ();  return dtoresult;      } 

2. Create, update, delete using the Executesqlcommand () implementation

The using (var db = new Dbmodel ())//Creates a database context {//synchronously executes SQL and returns the number of rows affected by int result = db. Database.executesqlcommand (@ "CREATE TABLE ' test ' (' ID ' INT not null,primary KEY (' id ')); ");
Use SqlParameter to avoid SQL injection var p_name = new SqlParameter ("@name", "Lori"), var p_age = new SqlParameter ("@age", 13);
If you are using a MySQL database you need to replace SqlParameter with Mysqlparameter//var p_name = new Mysqlparameter ("@name", "Lori");//var p_age = new Mysqlparameter ("@age", 13);//change student age result = db. Database.executesqlcommand (@ "UPDATE ' student ' SET ' age ' = @ageWHERE ' name ' = @name;", P_age, P_name);
Asynchronously executes the SQL and returns the number of rows affected task<int> RESULT2 = db. Database.executesqlcommandasync ("DROP TABLE ' test ';");}

Note: If you need to create or delete the current database, there is also a. Create () and. Delete () methods, which do not accept parameters and return a BOOL value indicating success or failure.

3. Use the dbset<t>. SQLQuery ()

There is also one under each data entity collection dbset<t>. SQLQuery (), the function is the same as described above, but dbset<t> under. SQLQuery () can only return types that are contained in dbset<t>. But under the dbset<t>. SQLQuery () also causes the database context (Dbmodel) to track the status of the returned data while returning the data, which can be used if the returned data has been modified. SaveChanges () Saves the result directly back to the database. The result of the. Database.sqlquery () is not to be found.

using (var db = new Dbmodel ())//CREATE DATABASE context {//query called Lori's student information and modify her age student Result1 = Db.students.SqlQuery ("SELECT * from Studen T WHERE name = ' Lori '). FirstOrDefault (); result1.age = 13; Through the Entity collection. SQLQuery the data to be queried, the student RESULT2 = db that can be saved to the database after modification. Database.sqlquery<student> ("SELECT * FROM student WHERE name = ' mong Choi '"). FirstOrDefault (); result2.age = 21; Because it's using. Database.sqlquery query to, so the changes here will not be saved to the database//if desired. The data detected under Database.sqlquery can also be saved to the database student RESULT3 = db after modification. Database.sqlquery<student> ("SELECT * FROM student WHERE name = ' Xiaoming '"). FirstOrDefault (); result3.age = 36;db. Entry<student> (RESULT3). state = System.Data.Entity.EntityState.Modified; Notifies the data context, and this record has been modified
Db. SaveChanges ();}

4.BeginTransaction ()

Start a transaction on the underlying storage connection

            Transaction            using with (dbcontexttransaction dbtransaction = DeliverRepository.EFDb.Database.BeginTransaction ())            {                Try                {                    //Transactional Logic code                    //Transaction commit                    Dbtransaction.commit ();                }                catch (Exception ex)                {                    dbtransaction.rollback ();                    throw new Exception (ex. Message);                }            }

The basic usage of database class is so much, there is nothing missing welcome everyone to add!

PS: Welcome to scan the QR code below, join QQ Group

Jacky Source: https://www.cnblogs.com/ydcnblog/statement: This article copyright belongs to the author and the blog Park is shared, welcome reprint, but without the author's consent must retain this paragraph statement, and in the article page obvious location to the original link, otherwise reserves the right to pursue legal responsibility.

asp: Using database classes to execute SQL statements in the EF framework