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 >

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

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?

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

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);
myconn.Open();
//Related database operations
myconn.Close();

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 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

db.Queryable<Student>().Take(10).ToList()
//select top 10 * from Student

　　

Get minimum value

db.Queryable<Order>().Min(it=>it.Id);//synchronization
db.Queryable<Order>().MinAsync(it=>it.Id);//asynchronous
//You can also use the function SqlFunc.AggregateMin

　　

Sum

db.Queryable<Order>().Sum(it=>it.Id);//synchronization
db.Queryable<Order>().SumAsync(it=>it.Id);//asynchronous
//You can also use the function sqlfunc.aggregateum