Simple SqlHelper and simple SqlHelper

Simple SqlHelper and simple SqlHelper

Currently, many SQlHelper textbooks on the Internet do not close connections and release resources. This slows down the website response during project development and leads to a crash.

The SQlHelper I introduced now uses Using to automatically release resources, so we do not need to manually release resources.

1 using System; 2 using System. collections. generic; 3 using System. configuration; 4 using System. data; 5 using System. data. sqlClient; 6 using System. linq; 7 using System. text; 8 using System. threading. tasks; 9 10 namespace DAL 11 {12 public static class SqlHelper 13 {14 // defines a connection string 15 // readonly variable, which can only be copied during initialization, and assign values in the constructor. The value 16 private static readonly string conStr = ConfigurationMana can only be read elsewhere. Ger. connectionStrings [""]. connectionString; 17 /// <summary> 18 /// ExecuteNonQuery 19 /// </summary> 20 /// <param name = "SQL"> statement to be executed/param> 21 /// <param name = "type"> specifies the type (stored procedure or statement) </param> 22 // <param name = "pms"> parameter </param> 23 // <returns> </returns> 24 public static int ExecuteNonQuery (string SQL, commandType type, params SqlParameter [] pms) 25 {26 using (SqlConnection con = new SqlConnection (ConStr) 27 {28 using (SqlCommand cmd = new SqlCommand (SQL, con) 29 {30 // determine whether the SQL statement or stored procedure is passed in cmd. commandType = type; 32 if (pms! = Null) 33 {34 cmd. parameters. addRange (pms); 35} 36 con. open (); 37 return cmd. executeNonQuery (); 38} 39} 40} 41 // <summary> 42 // a single value 43 // </summary> 44 /// <param name = "SQL"> </param> 45 // <param name = "type"> </param> 46 // <param name = "pms"> </param> 47 /// <returns> </returns> 48 public static object ExecuteScalar (string SQL, commandType type, params SqlParameter [] pms) 49 {50 using (SqlConnection con = new SqlConnection (conStr) 51 {52 using (SqlCommand cmd = new SqlCommand (SQL, con) 53 {54 cmd. CommandType = type; 55 if (pms! = Null) 56 {57 cmd. parameters. addRange (pms); 58} 59 con. open (); 60 return cmd. executeScalar (); 61} 62} 63} 64 public static SqlDataReader ExecuteReader (string SQL, CommandType type, params SqlParameter [] pms) 65 {66 // using is not used here because the reader object cannot close the connection. When using a reader object, the connection must be enabled. 67 SqlConnection con = new SqlConnection (conStr); 68 using (SqlCommand cmd = new SqlCommand (SQL, con) 69 {70 cmd. CommandType = type; 71 if (pms! = Null) 72 {73 cmd. parameters. addRange (pms); 74} 75 try 76 {77 con. open (); 78 // use CommandBehavior. closeConnection indicates that after sqlDatareader is used in the future, the associated Connection object will be closed while reader is closed. 79 return cmd. executeReader (CommandBehavior. closeConnection); 80} 81 // Exception execution 82 catch (Exception) 83 {84 con. close (); 85 con. dispose (); 86 throw; 87} 88} 89} 90 public static DataTable ExcuteDataTable (string SQL, CommandType type, params SqlParameter [] pms) 91 {92 DataTable dt = new DataTable (); 93 using (SqlDataAdapter adapter = new SqlDataAdapter (SQL, conStr) 94 {95 adapter. selectCommand. CommandType = type; 96 if (pms! = Null) 97 {98 adapter. SelectCommand. Parameters. AddRange (pms); 99} 100 adapter. Fill (dt); 101} 102 return dt; 103} 104} 105}