Simple database operation of ado.net (two) package SqlHelperl class

Simple database operation of ado.net (two) package SqlHelperl class

  Today I took the book last time, and then yesterday’s ado.net database operation related knowledge!

  Judging from the examples given in the previous article, you will definitely find that operating the database is actually quite troublesome. Even the simplest database operation statement must include defining the database connection string, creating the database connection object, and opening the database connection. , Define sql statement, instantiate operation database operation object, operate database, close database and so on. However, when we come into contact with the project, we will find that database operation is the most frequently used function in the project, so is there a simple way to use the database operation? Yes, the answer is of course, yes! How to do it? Encapsulate database operations into a class. This is the encapsulated SqlHelper class I want to record today, haha!

  Below, I will talk step by step, how to encapsulate it, haha!

The first step: add the SqlHelper class

  1. right-click in the project ==> Add ==> New Item ==> Class ==> Name it SqlHelper.cs ==> Click Add, as shown in the following figure:

After opening, we can start to write the SqlHeiperl class.

Step 2: Determine the parameters of the method in the class

When we write the SqlHelper class, we definitely want to write fixed things in the class, and then transfer those changes that require us to manually enter into parameters, so what content needs us to pass in through parameters What? According to the basic explanation of database operations in the previous article, we know that there should be such parameters:

Sql statement: According to the type of operation, the sql statement will definitely be different, insert\delete\update\select, these are different, so this sql statement must be passed in as a parameter;

Qualified conditions in SQL statements : When executing sql statements, we will have certain qualifications in most cases, such as "delete the row with id = xxx", "query whether a certain name exists", etc., then these The qualification must also be passed in.

  The above two parameters must be present, and there is another thing, it is not a parameter that needs to be passed in, but a value that needs to be configured by us, that is the database connection string conStr , in my first blog The configuration of this thing is also mentioned here, which is a parameter set to facilitate us to change the connected database. Under normal circumstances, we put "Data Source = .;Initial Catalog = database name; Integrated Security = true" this string of configuration information into a configuration file called App.config, and then get this information in the configuration file in SqlHelper , And then perform the operation to connect to the database just fine. The advantage of this is that we don't need to define the conStr string in each SqlHelper method, so we can use the packaged SqlHelper in any project and connect to any database.

The above are the determined parameters. Let's encapsulate SqlHelper specifically:

Step 3: Package SqlHelper

1.conStr configuration

(1) 1. open the App.config file, and then add the following code in the location shown in the figure below:

Code:

 <connectionStrings>
    <add name="mssqlserver" connectionString="Data Source = acer-pc; Initial Catalog = FirstDB; Integrated Security = true"/>
  </connectionStrings>
connectionString = write your own database connection information later

(2) Right-click the reference == "Add reference == "Assembly ==" check System.Configuration == "OK: as shown in the figure below

(3) Define the connection string in SqlHelper, the code is as follows:

 private static readonly string conStr = ConfigurationManager.ConnectionStrings["mssqlserver"].ConnectionString;

Next, you can start to write the database operation method. In the previous article, I talked about the three methods of operating the database. They are: ExcuteNonQuery\ExcuteScalar\ExcuteReader, then we should also have these three methods when writing SqlHelper, the following are respectively Introduce the preparation of three methods:

2.ExecuteNonQuery() method

Give the code first, and then explain it step by step:

 1 public static int ExecuteNonQurey(string sql,params SqlParameter[] pms)
 2 {
 3 using (SqlConnection con = new SqlConnection(conStr))
 4 {
 5 using (SqlCommand cmd = new SqlCommand(sql,con))
 6 {
 7 if (pms != null)
 8 {
 9 cmd.Parameters.AddRange(pms);//This function is used to add parameter values, and then passed to the stored procedure, which means that the required parameters are placed in the specified position of the sql statement
10}
11 con.Open();
12 return cmd.ExecuteNonQuery();
13}
14}
15}

First of all, let’s talk about the return value. Because ExecuteNonQuery returns plastic data, the return value must be of type int. Then, let’s talk about the parameters. The first one is a sql statement of string type (string), and the second is a parameter type ( params) array, let me talk about it, because I haven't seen this type when I was learning C language, and I haven't seen this type when I was learning php, so I'm a little confused:

params is a parameter array type. What does it mean? You can put various different types and different numbers of parameters into this array to pass parameters. This parameter must be a one-dimensional array. That’s what it means. I want to know more. Students about params can click this link: https://www.cnblogs.com/wangliu/p/3876672.html .

These are two parameters.

Then the following is to write the code step by step and go down, to the 7th line, we have to check the parameter array to see if it is empty, if it is empty, then just execute it without saying anything, if not If it is empty, you have to do one thing first, that is, put the required parameters in the specified position of the sql statement, and let it execute . For an explanation of the Parameters.AddRange() method, please see this blog : https://blog.csdn.net/wang13667539325/article/details/9672829 .

3.ExcuteScalar() method:

Again, let's go to the code first, and then explain it step by step:

 1 public static object ExecuteScalar(string sql,params SqlParameter[] pms)
 2 {
 3 using (SqlConnection con = new SqlConnection(conStr))
 4 {
 5 using (SqlCommand cmd = new SqlCommand(sql,con))
 6 {
 7 if (pms != null)
 8 {
 9 cmd.Parameters.AddRange(pms);
10}
11 con.Open();
12 return cmd.ExecuteScalar();
13}
14 
15}
16 
17}

It's a step-by-step explanation, but in fact there is nothing to explain. This is almost the same as the one above, haha!

4.Execute.Reader():

The third one is slightly different, look at the code first :

 1 public static SqlDataReader ExecuteReader(string sql,params SqlParameter[] pms)
 2 {
 3 SqlConnection con = new SqlConnection(conStr);
 4 using (SqlCommand cmd = new SqlCommand(sql,con))
 5 {
 6 if (pms != null)
 7 {
 8 cmd.Parameters.AddRange(pms);
 9 }
10 try
11 {
12 con.Open();
13 return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            //System.Data.CommandBehavior.CloseConnection This enumeration parameter indicates that after the SqlDataReader is used in the future, when the reader is closed, the associated Connection object will also be closed inside the SqlDataReader
14 
15}
16 catch {
17 con.Close();
18 con.Dispose();
19 throw;
20}       
21 }
22 }

The wording in this is not the same. There is no using when creating database connection objects. Why is this?

Remember that we said earlier that the object reader returned by this method is not a warehouse for storing things, it is just a link, then after we return this thing, we will use this thing to fetch the value from the database, but if we use using, The default is to close the database after using. At this time, the database is already closed. Even if we get the returned reader, we can’t use it to get the value, which is equivalent to the returned reader. No eggs are used. So, when connecting to the database here, we don't need to use it.

  Then you have to ask again, don't you care if the database is used up? Of course not, continue to look at the code behind, there is a try...catch..., there is a comment in the try, see it! what? It's too young to see it. I was so angry that I squirted old blood. Okay, then I will copy this sentence to the following:

The enumeration parameter System.Data.CommandBehavior.CloseConnection means that when the SqlDataReader is used up in the future, when the reader is closed, the associated Connection object will also be closed inside the SqlDataReader, also closed, and also closed!

Regarding the encapsulation of SqlHelper, I will stop here. There are some places in the article that I didn't understand when I wrote it, but I will understand it after I write it. Here is a reference to a csdn blog, which is also written to encapsulate SqlHelper. I am not verbose, but it is more comprehensive, and the link is attached: https://blog.csdn.net/langji1234/article/details/44724779 , if you need it, you can Poke in and take a look.

That’s it for today, haha, Caiji is going to learn something new this afternoon, haha!

My QQ: 3074596466

Reference: https://cloud.tencent.com/developer/article/1503488 ado.net's simple database operation (two) package SqlHelperl class-Cloud + Community-Tencent Cloud