Getting started with SqlSugar (1): SqlSugar ORM introduction and ORM object creation, sqlsugarorm

Getting started with SqlSugar (1): SqlSugar ORM introduction and ORM object creation, sqlsugarorm
Preface

. The Net circle has been around for several years. From the first SqlHelper to Microsoft's Enterprise Library, to EF, to a third-party ORM framework, it has gone through an undefinable process, sqlHelper and the Enterprise Library are not mentioned much. EF is powerful, ..... (10 thousand words are omitted here). Finally, I found several domestic ORM and used it. Finally, I stayed at SqlSugar. compared with other ORM, SqlSugar has a relatively late start, but has the most powerful functions. At least this is the way I use it. high Performance and powerful Lambda support (Lambda supports more than Dos. there are more ORM types, but Dos is not devalued here. ORM), multi-database support (currently supports SQL Server & Oracle

& MySql & Sqlite), supported. net Core and so on. (currently, it seems to be supported. only a handful of. Net Core ORM, and SqlSugar is one of them). You can find other ones by yourself. in addition, SqlSugar is updated and maintained in a timely manner. For more information, visit the official website.

For more questions, contact the QQ group. QQ Group No.: 225982985

No more nonsense.

Maxcompute is suitable for beginners to look at the DEMO Environment Introduction.

This series of blogs only use the following environments:

IDE: VS2017

JS environment: Vue 2.x

Database: SQL Server 2016

. Net environment:. Net Core 2.0

Website hosting server: IIS

Operating System: Windows 10 Enterprise Edition

This series of blogs will not introduce Core too much, but will only pass through it. Core technology needs to be further documented and learned.

 

Let's create a new project first. I have already created a new project. The approximate directory structure is as follows (more and more directory structures will be created later, and the code will be downloaded below the article, which is in the ascending order)

It is relatively simple. I have removed the Web and logic layers and the data layer. I think it is not necessary to use the ORM Data Layer. Do you have to choose one?

After the project is created, install SqlSugar in SqlSugarDemo. Busines and install it through NuGet,

If your project is. Net, not. Net Core, install the first one.

 

1. We create a new SugarBase class in the logic layer to provide the ORM database access object.

The Code is as follows (here is personal encapsulation, limited technology, for reference only ):

Using System; using System. data; namespace SqlSugarDemo. busines. base {using SqlSugar; // <summary> // Sugar ORM parent class, encapsulate some basic operations /// </summary> public static class SugarBase {// <summary> // database link string /// </summary> public static string DB_ConnectionString {get; set ;}//< summary> /// obtain the connection object of the orm database (only operate on the database once, otherwise the database will be connected and closed multiple times) /// the default timeout value is 30 seconds /// the default value is SQL Server database // The database link is automatically closed by default. Do not use this attribute for multiple database operations, this may cause performance problems. // to customize the settings, use the GetIntance () method or directly use the Exec method, delegate/// </summary> public static SqlSugarClient DB {get {return InitDB (30, DbType. sqlServer, true) ;}/// <summary> // obtain SqlSugarClient (use this method. manually release resources by default, such as using (var db = SugarBase. getIntance () {your code}. If you set the isAutoCloseConnection parameter to true, you do not need to manually release it. The database will be released once each operation, which may affect performance. Please use it on your own) /// </summary> /// <param name = "commandTimeOut"> waiting time-out period. The default value is 30 seconds (unit: seconds) </param> /// <param name = "dbType"> database type, the default value is SQL Server </param> /// <param name = "isAutoCloseConnection"> whether to automatically close the database connection. The default value is no. If it is set to true, it will immediately close the database after each operation. If the database is operated multiple times in a method, it is recommended to keep it false, otherwise, it may cause performance problems </param> /// <returns> </returns> public static SqlSugarClient GetIntance (int commandTimeOut = 30, DbType dbType = DbType. sqlServer, bool isAutoCloseConnection = false) {return SugarBase. initDB (commandTimeOut, dbType, isAutoCloseConnection );} /// <summary> /// initialize the ORM connection object /// </summary> /// <param name = "commandTimeOut"> wait for the timeout time, the default value is 30 seconds (unit: seconds) </param> // <param name = "dbType"> database type, the default value is SQL Server </param> /// <param name = "isAutoCloseConnection"> whether to automatically close the database connection. The default value is no. If it is set to true, it will immediately close the database after each operation. If the database is operated multiple times in a method, it is recommended to keep it false, otherwise, it may cause performance problems </param> private static SqlSugarClient InitDB (int commandTimeOut = 30, DbType dbType = DbType. sqlServer, bool isAutoCloseConnection = false) {var db = new SqlSugarClient (new ConnectionConfig () {ConnectionString = SugarBase. DB_ConnectionString, DbType = dbType, InitKeyType = InitKeyType. attribute, IsAutoCloseConnection = isAutoCloseConnection}); db. ado. commandTimeOut = commandTimeOut; return db ;} /// <summary> /// perform database operations /// </summary> /// <typeparam name = "Result"> return value type generic </typeparam> // /<param name = "func"> method DeleGate </param> // <param name = "commandTimeOut"> timeout, the Unit is seconds. The default value is 30 seconds. </param> /// <param name = "dbType"> database type, the default value is SQL Server </param> // <returns> generic return value </returns> public static Result Exec <Result> (Func <SqlSugarClient, Result> func, int commandTimeOut = 30, DbType dbType = DbType. sqlServer) {if (func = null) throw new Exception ("delegate is null, transaction processing meaningless"); using (var db = InitDB (commandTimeOut, dbType )) {try {return func (db);} catch (Exception ex) {throw ex;} finally {db. dispose ();}}} /// <summary> /// perform database operations with transaction processing /// </summary> /// <typeparam name = "Result"> return value type generic </ typeparam> // <param name = "func"> method DeleGate </param> /// <param name = "commandTimeOut"> timeout, the Unit is seconds. The default value is 30 seconds. </param> /// <param name = "dbType"> database type, the default value is SQL Server </param> // <returns> generic return value </returns> public static Result ExecTran <Result> (Func <SqlSugarClient, Result> func, int commandTimeOut = 30, DbType dbType = DbType. sqlServer) {if (func = null) throw new Exception ("delegate is null, transaction processing meaningless"); using (var db = InitDB (commandTimeOut, dbType )) {try {db. ado. beginTran (IsolationLevel. unspecified); var result = func (db); db. ado. commitTran (); return result;} catch (Exception ex) {db. ado. rollbackTran (); throw ex;} finally {db. dispose ();}}}}}

SugarBase

DB_ConnectionString: the connection string of the database. You need to assign values during MVC initialization. In other ways, I am lazy and assign values directly to the Startup class of MVC. the Startup code is as follows:

Using Microsoft. aspNetCore. builder; using Microsoft. aspNetCore. hosting; using Microsoft. extensions. configuration; using Microsoft. extensions. dependencyInjection; using SqlSugarDemo. busines. base; namespace SqlSugarDemo. web {public class Startup {readonly private IConfiguration _ Configuration; public Startup (IConfiguration configuration Configuration) {this. _ Configuration = configuration;} public void ConfigureServi Ces (IServiceCollection services) {services. addMvc (); SugarBase. DB_ConnectionString = this. _ Configuration. getConnectionString ("Sugar"); // assign a value to the database connection string} public void Configure (IApplicationBuilder app, IHostingEnvironment env) {app. useStaticFiles (); app. useMvc (routes => {routes. mapRoute (name: "default", template: "{controller = Home}/{action = Index}/{id ?} "); Routes. MapRoute (name:" default1 ", template:" {controller = Home}/{action = Index}/{id ?} ");});}}}

Startup

The database configuration information is written under the appsettings. json file on the web. MVC loads the json configuration to the memory by default. The JSON file structure is as follows:

{  "Logging": {    "IncludeScopes": false,    "LogLevel": {      "Default": "Warning"    }  },  "ConnectionStrings": {    "Sugar": "server=127.0.0.1;database=SqlSugarDemo;uid=sa;pwd=123456;"  }}

View Code

In this step, the provision of the orm db object is basically complete. As you can see, my DB provides N methods for the object. The following Code demonstrates how to use it.

2. directly use the DB attribute to operate the database (this article does not use the Lambda syntax, and uses SQL to describe the database objects. The next article details how to use the object Layer and Lambda syntax)

Add a HomeManager class to the logic layer and import the namespace. Because VS2017 is used, you can directly import static classes.

Using static SqlSugarDemo. Busines. Base. SugarBase;

In this way, you can directly use DB instead of SugarBase. DB.

Add method: Use the DB attribute for query. You can download the code below the database structure.

Compile the DBTest method to test the database attributes.

public bool DBTest()        {            return DB.Ado.ExecuteCommand("insert into TB_User values(@Name,@Gender,@Phone,@CreateDate)", new List<SugarParameter>            {                new SugarParameter("@Name", "Dos.Orm"),                new SugarParameter("@Gender", false),                new SugarParameter("@Phone", "18888888888"),                new SugarParameter("@CreateDate", DateTime.Now)            }) > 0;        }

DBTest

I used the Lazy class for calling in the control. The call code is as follows:

/// <Summary> /// homepage /// </summary> /// <returns> </returns> [HttpGet] public IActionResult Index () {var result = this. _ HomeManager. value. DBTest (); ViewBag. result = result; return View ();}

Index

We run the program. The interface shows "True". The execution is successful, and the database has a piece of data.

The above example briefly introduces how to operate a database using the DB attribute. It is still very simple to use, and you don't have to consider whether to release it or not, because SqlSugar will automatically process it. If you don't want to finish using it, release immediately. Our SugarBase provides a method. The following describes how to use the method of not releasing automatically.

Compile the NotAutoDisposeTest method on the logic layer. The Code is as follows:

/// <Summary> /// not automatically released /// </summary> /// <returns> </returns> public bool NotAutoDisposeTest () {using (var db = GetIntance () {var id = db. ado. getInt ("Select ID from TB_User where Name = @ Name", new SugarParameter ("@ Name", "SqlSugar"); var result = db. ado. executeCommand ("update TB_User set Name = @ Name where ID = @ ID", new SugarParameter ("@ Name", "SqlSugar_SqlSugar"), new SugarParameter ("@ ID ", id); return result> 0 ;}}

NotAutoDisposeTest

When we run the program, True is returned and the execution is successful.

In this method, we use the GetIntance method in the SugarBase class to provide the database access object. The GetIntance method has three parameters, which can be set to the default value for the timeout time, database type, and automatic release, I did not write

The GetIntance method must use using. Otherwise, the database link will not be released after use.

The GetIntance method is used when you operate the database multiple times in a method, instead of using the DB attribute. The DB attribute is released after each operation, it will have a certain performance impact. The specific impact is not tested.

The GetIntance method has a confirmation, that is, it must be written to using every time it is used. This is optimized. The Exec <T> method is provided in SugarBase, saving you the trouble of writing using every time.

Compile the ExecTest method on the logic layer. The Code is as follows:

/// <Summary> /// Exec method /// </summary> /// <returns> </returns> public bool ExecTest () {return Exec (db => {var id = db. ado. getInt ("Select ID from TB_User where Name = @ Name", new SugarParameter ("@ Name", "SqlSugar_SqlSugar"); var result = db. ado. executeCommand ("update TB_User set Name = @ Name where ID = @ ID", new SugarParameter ("@ Name", "SqlSugar_Sugar"), new SugarParameter ("@ ID ", id); return result> 0 ;});}

ExecTest

It can be seen that the Exec method requires a delegate, which provides the SqlSugarClient parameter to operate the database, run the program, return true, and run successfully.

When I operate the database multiple times, it saves the trouble of writing using and is not very different from GetIntance.

 

SugarBase also provides a simple encapsulation of transaction operations. The usage is the same as that of Exec, and the transaction will be automatically processed in it. here we will not compile the DEMO.

The transaction processing method is ExecTran. If an exception occurs, the transaction will be automatically rolled back and the transaction will be committed incorrectly.

Note that you need to write your own rollback code to manually roll back the transaction.

If modification is performed, the number of affected rows returned is 0, indicating that the execution failed. At this time, we need to roll back the transaction and not continue the execution.

                if (result<=0)                {                    db.Ado.RollbackTran();                    return false;                }

ExecTran uses the same method as Exec.

 

At this point, the SqlSugar introduction is basically complete. The SugarBase class is a simple Encapsulation by the blogger and is suitable for most scenarios. You can use the SugarBase type provided by me to obtain database operation objects, quick Start

Basically, I have learned about how to create an ORM object. Next I will write SqlSugar to add a database.

 

Project File Download

Password: 3mk7