. NET ORM connection database and basic addition, deletion, modification and query

  1, Write in front

Because the. net course selected this semester will be tested on the computer, summarize the methods of. net operating SqlServer data. (because my direction is Java, I don't know much about. net, but the code written below has been tested successfully)

2, . net connection database

  Configure the database connection in the web.config file, and write the code in < configuration > < / configuration >

    <add key="connstring" value="user id=sa; password=sa123456; database=dataset; server=(local)" />

Where SA   Is the SqlServer database account, sa123456 is the database account password, and dataset bit is the database name.

Through the above code, it is not difficult to see that this is connected through the account of the SqlServer database. What if you need to connect through Windows authentication?

    <add key="connstring" value="Data Source=.;Initial Catalog=dataset;Integrated Security=True"/>

Where dataset is the database name.

Once configured, use the Connection object in the cs stage to connect

String constr = ConfigurationManager.AppSettings["connstring"].ToString();         
SqlConnection myconn = new SqlConnection(constr);

Obviously, the first sentence gets the string of information about the database we configured in web.config, and then new a SqlConnection object.

At this time, you may think that since you are getting the string in the configuration file, can you write it directly in the cs background file instead of getting it after configuring it in the configuration file? The answer is yes.

String constr = "Data Source=.;Initial Catalog=dataset;Integrated Security=True";
SqlConnection myconn = new SqlConnection(constr);

By carefully comparing the previously written configuration files, you can see the similarities between the two methods.

Now let's simply say what the Connection object is. In ADO.NET, the main purpose of the Connection object is to open and close the Connection to the database. Through this object, you can access and operate the database.

String constr = "Data Source=.;Initial Catalog=dataset;Integrated Security=True";
SqlConnection myconn = new SqlConnection(constr);
//Related database operations

Open is to open the database, and Close is to Close the database.

3, . NET ORM operation database

install   sqlsuagr ORM

  Create database object

//Create database object SqlSugarClient   

SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()


           ConnectionString = "Server=.xxxxx",//Connector string

           DbType = DbType.SqlServer, //Database type

           IsAutoCloseConnection = true //If it is not set to true, close it manually


Check all

List<Student> list=db.Queryable<Student>().ToList()

//select * from Student

  Query by criteria

db.Queryable<Student>().Where(it=>it.Id==1).ToList()//select * from Student where id=1

Multi condition query

db.Queryable<Student>().Where(it=>it.Id>10&&it.Name=="a").ToList()//Select * from student where id > 10 and name ='a'db. Queryable < student > (). Where (it = > it. ID > 10). Where (it = > it. Name = = "a"). Tolist() / / select * from student where id > 10 and name ='a '/ / if yes, or the relationship can be used||

Fuzzy query

db.Queryable<Student>().Where(it =>it.Name.Contains("jack")).ToList();//select  * from  Student where name like %jack%

Dynamic OR query

var exp= Expressionable.Create<Student>();

exp.OrIF(condition,it=>it.Id==1);//. OR IF is a condition

exp.Or(it =>it.Name.Contains("jack"));//Splice OR

var list=db.Queryable<Student>().Where(exp.ToExpression()).ToList();

Query by PK

single in susgar is equivalent to SingleOrDefault in EF

db.Queryable<Student>().InSingle(2) //Query SingleById by primary key

db.Queryable<Student>().Single(it=>it.Id==2) //Query by ID

//select * from Student where id=2

Check the first few items for the number of data lines


//select top 10 * from Student

Get minimum value



//You can also use the function SqlFunc.AggregateMin


db.Queryable<Order>().Sum(it=>it.Id);//Synchronize dB. Queryable < order > (). Sumasync (it = > it. ID)// Asynchronous / / you can also use the function sqlfunc.aggregateum

More usage:   SqlSugar ORM 5.X official website, documents and Tutorials - sqlsugar 5x -. Net fructose website

Tags: net

Posted on Mon, 08 Nov 2021 14:13:55 -0500 by Cronje