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