SqlHelper reconstruction, sqlhelper

SqlHelper reconstruction, sqlhelper

SqlHelper (click to View Details) was used during data center reconstruction. At that time, I didn't understand anything. After continuous use, I began to understand the meaning. Later I found that the original SqlHelper was a little tedious to write. Write twice for each operation to identify whether a parameter is included. This time, rebuild the first version to improve its shortcomings.

Variable Length Parameter params

First, let's take a look at the variable length parameter params. Here are two examples.

First

<span style="font-size:18px;"><span style="font-size:18px;">class Program    {        static void Main(string[] args)        {            int i = Sum(new int[]{2,87,51,5});            Console.WriteLine(i);            Console.ReadKey();        }        static int Sum(int[] sums)        {            int result = 0;            foreach (int i in sums )            {                result += i;                }            return result;        }    }</span></span>

The returned result is

Second:

<span style="font-size:18px;">class Program    {        static void Main(string[] args)        {            int i = Sum(2,5,5,5);            Console.WriteLine(i);            Console.ReadKey();        }        static int Sum(params int[] sums)        {            int result = 0;            foreach (int i in sums )            {                result += i;                }            return result;        }    }</span>

The returned result is:

In the first example, the Sum method does not use the params modifier during Declaration, so the array needs to be declared during the call. In the second example, the params modifier is used. During the call, you can directly enter the parameter. Now you can understand the role of params. Params packages all the subsequent parameters, which is equivalent to an array. If no parameter is written, it is equivalent to an array with a length of 0.

Note: When declaring a params array, the array must be at the end of the parameter, because it will pack all the parameters following it by default.

Similarly, you can use the params modifier in the SqlHelper method. If there is a parameter, enter the parameter. If there is no parameter, no input is required, which is equivalent to an array with a length of 0.

Use using ()

After the database is executed, You need to perform the conn. close () and cmd. Dispose () operations. In the previous SqlHelper, the two statements of code should be written. In fact, there are better ways to replace it. That is, the IDispose interface is implemented using () and using () methods. That is to say, if using () is used, it will automatically help us execute conn. close () and cmd. dispose () operation. Next, let's look at the reconstructed SqlHelper

<Span style = "font-size: 18px;"> <span style = "font-size: 18px;"> using System; using System. collections. generic; using System. linq; using System. text; using System. configuration; using System. data. sqlClient; using System. data; namespace DAL {public class SqlHelper {// app. config File inheritance: public static readonly string connstr = ConfigurationManager. connectionStrings ["connstr"]. connectionString; // <summary> // Execute Add, delete, modify, and delete SQL statements or stored procedure /// </summary> /// <param name = "plain text"> SQL statement or stored procedure name </param> /// <param name = "parameters"> parameter </param> // <returns> returns the number of affected rows </returns> public int ExecuteNonQuery (string parameter text, commandType parameter type, params SqlParameter [] parameters) // a variable-length parameter-params modifier is added before SqlParameter. </Span> {try {// create an SQL connection using (SqlConnection conn = new SqlConnection (connstr) {// open the connection conn. open (); // create SqlCommand and execute the SQL command using (SqlCommand cmd = conn. createCommand () {// SQL statement or stored procedure name cmd. commandText = plain text; // command type cmd. commandType = commantype; // Add the cmd parameter. parameters. addRange (parameters); // return the execution result return cmd. executeNonQuery () ;}} catch (Exception ex) {// throw new Exception (ex. toString ());}} /// <Summary> /// query the database /// </summary> /// <param name = "plain text"> SQL statement or stored procedure </ param> /// <param name = "partition type"> command type </param> /// <param name = "parameters"> parameter </param> /// <returns> return the queried table </returns> public DataTable ExecuteDataTable (string plain text, commandType parameter type, params SqlParameter [] parameters) // a variable-length parameter-params modifier is added before SqlParameter. {Try {// create an SQL connection using (SqlConnection conn = new SqlConnection (connstr) {// open the connection conn. open (); // create SqlCommand and execute the SQL command using (SqlCommand cmd = conn. createCommand () {// SQL statement or stored procedure name cmd. commandText = plain text; // command type cmd. commandType = commantype; // Add the cmd parameter. parameters. addRange (parameters); // defines the DataSet dataset DataSet = new DataSet (); // creates a data adapter and queries SqlDataAdapter adapter = new SqlDataAdapter (cmd); // fills the dataset adapter. fill (dataset); // obtain the first table return dataset in the dataset. tables [0] ;}} catch (Exception ex) {// throw new Exception (ex. toString () ;}}}</span> </span>

Summary:

In This refactoring, we learned the variable-length parameter params and deepened our understanding of the using () method. It seems pretty good. Of course, SqlHelper cannot be reconstructed. There are still many, and we will continue to learn to expand from ourselves in the future.