SqlHelper and mysqlsqlhelper for MySql

SqlHelper and mysqlsqlhelper for MySql

SqlHelper for MySql

1 // <summary> 2 3 // Title: MySqlHelper 4 // Author: WinterT 5 // Date: 08:12:54 6 // Description: 7 // ExecuteNonQuery 8 // ExecuteScalar 9 // ExecuteReader 10 /// ExecuteTable 11 /// </summary> 12 public static class MySqlHelper 13 {14 // <summary> 15 // return the connection 16 specified in the configuration file /// </summary> 17 // <returns> the connection specified in the configuration file </returns> 18 private static MySqlConnection GetConnection () 19 {20 string connString = ConfigurationManager. connectionStrings [1]. connectionString; 21 return new MySqlConnection (connString ); 22} 23 # region ExecuteNonQuery 24 // <summary> 25 // Execute SQL statement 26 /// </summary> 27 /// <param name = "SQL"> SQL statement </param> 28 // <returns> affected rows </returns> 29 public static int ExecuteNonQuery (string SQL) 30 {31 using (MySqlConnection conn = GetConnection () 32 {33 using (My SqlCommand cmd = conn. createCommand () 34 {35 cmd. commandText = SQL; 36 return cmd. executeNonQuery (); 37} 38} 39} 40 // <summary> 41 // Based on the given connection, execute the SQL statement 42 with parameters /// </summary> 43 /// <param name = "conn"> connect and make sure the connection is enabled before use. </Param> 44 // <param name = "SQL"> SQL statement with parameters </param> 45 // <param name = "paras"> parameter </ param> 46 // <returns> affected rows </returns> 47 public static int ExecuteNonQuery 48 (MySqlConnection conn, string SQL, params MySqlParameter [] paras) 49 {50 using (MySqlCommand cmd = conn. createCommand () 51 {52 cmd. commandText = SQL; 53 cmd. parameters. addRange (paras); 54 return cmd. executeNonQuery (); 55} 56} 57 // <summary> 58 // run the SQL statement 59 with parameters // </summary> 60 // <param name = "SQL"> SQL statement with parameters </param> 61 // <param name = "paras"> parameter </param> 62 // <returns> affected rows </returns> 63 public static int ExecuteNonQuery 64 (string SQL, params MySqlParameter [] paras) 65 {66 using (MySqlConnection conn = GetConnection () 67 {68 return ExecuteNonQuery (conn, SQL, paras ); 69} 70} 71 # endregion 72 # region ExecuteScalar 73 // <summary> 74 // execute the SQL statement, return the first 75 /// </summary> 76 // <param name = "SQL"> SQL statement </param> 77 /// <returns> first column of a row </returns> 78 public static Object ExecuteScalar (string SQL) 79 {80 using (MySqlConnection conn = GetConnection () 81 {82 using (MySqlCommand cmd = conn. createCommand () 83 {84 cmd. commandText = SQL; 85 return cmd. executeScalar (); 86} 87} 88} 89 // <summary> 90 // execute an SQL statement with parameters based on the Connection object, return 91 /// </summary> 92 // <param name = "conn"> connection </param> 93 /// <param name =" SQL "> SQL statement </param> 94 // <param name =" paras "> parameter </param> 95 /// <returns> returns the first column of the First row </param> /returns> 96 public static object ExecuteScalar 97 (MySqlConnection conn, string SQL, MySqlParameter [] paras) 98 {99 using (MySqlCommand cmd = conn. createCommand () 100 {101 cmd. commandText = SQL; 102 cmd. parameters. addRange (paras); 103 return cmd. executeScalar (); 104} 105} 106 // <summary> 107 // execute an SQL statement with parameters, the first column of the First row is 108 /// </summary> 109 /// <param name = "SQL"> SQL statement </param> 110 /// <param name = "paras"> parameter </param> 111 // <returns> returns the first column of the First row </returns> 112 public static object ExecuteScalar113 (string SQL, mySqlParameter [] paras) 114 {115 using (MySqlConnection conn = GetConnection () 116 {117 return ExecuteScalar (conn, SQL, paras ); 118} 119} 120 # endregion121 # region ExecuteReader122 // <summary> 123 // execute the SQL statement, returns a MySqlDataReader124 /// </summary> 125 /// <param name = "SQL"> SQL statement </param> 126 /// <returns> MySqlDataReader object </ returns> 127 public static MySqlDataReader ExecuteReader (string SQL) 128 {129 MySqlConnection conn = GetConnection (); 130 using (MySqlCommand cmd = conn. createCommand () 131 {132 cmd. commandText = SQL; 133 conn. open (); 134 return cmd. executeReader (CommandBehavior. closeConnection); 135} 136} 137 // <summary> 138 // execute an SQL statement with parameters based on the specified connection, returns a Reader object 139 /// </summary> 140 /// <param name = "conn"> connection </param> 141 /// <param name = "SQL"> SQL statement </param> 142 // <param name = "paras"> parameter </param> 143 // <returns> A MySqlDataReader object </returns> 144 public static MySqlDataReader ExecuteReader145 (MySqlConnection conn, string SQL, params MySqlParameter [] paras) 146 {147 using (MySqlCommand cmd = conn. createCommand () 148 {149 cmd. commandText = SQL; 150 cmd. parameters. addRange (paras); 151 conn. open (); 152 return cmd. executeReader (CommandBehavior. closeConnection); 153} 154} 155 // <summary> 156 // execute an SQL statement with parameters, returns a Reader object 157 /// </summary> 158 /// <param name = "SQL"> SQL statement </param> 159 /// <param name = "paras "> parameter </param> 160 // <returns> A MySqlDataReader object </returns> 161 public static MySqlDataReader ExecuteReader162 (string SQL, params MySqlParameter [] paras) 163 {164 MySqlConnection conn = GetConnection (); 165 using (MySqlCommand cmd = conn. createCommand () 166 {167 return ExecuteReader (conn, SQL, paras ); 168} 169} 170 # endregion171 # region ExecuteTable172 // <summary> 173 // execute the SQL statement, returns a ableable174 /// </summary> 175 /// <param name = "SQL"> SQL statement </param> 176 /// <returns> DataTable </returns> 177 public static DataTable ExecuteTable (string SQL) 178 {179 using (MySqlConnection conn = GetConnection () 180 {181 using (MySqlCommand cmd = conn. createCommand () 182 {183 cmd. commandText = SQL; 184 using (MySqlDataReader reader = cmd. executeReader () 185 {186 DataTable table = new DataTable (); 187 table. load (reader); 188 return table; 189} 190} 191} 192} 193 // <summary> 194 // execute an SQL statement with parameters based on the connection, returns a ableable195 /// </summary> 196 /// <param name = "conn"> connection, remember that the connection has been enabled </param> 197 // <param name = "SQL"> SQL statement </param> 198 // <param name = "paras"> parameter </param> 199 // <returns> DataTable </returns> 200 public static DataTable ExecuteTable201 (MySqlConnection conn, string SQL, params MySqlParameter [] paras) 202 {203 using (MySqlCommand cmd = conn. createCommand () 204 {205 cmd. commandText = SQL; 206 cmd. parameters. addRange (paras); 207 using (MySqlDataReader reader = cmd. executeReader () 208 {209 DataTable table = new DataTable (); 210 table. load (reader); 211 return table; 212} 213} 214} 215 // <summary> 216 // run the SQL statement with parameters 217 // </summary> 218 // <param name =" SQL "> SQL statement </param> 219 // <param name =" paras "> parameter </param> 220 // <returns> DataTable </returns> 221 public static DataTable ExecuteTable222 (string SQL, params MySqlParameter [] paras) 223 {224 using (MySqlConnection conn = GetConnection () 225 {226 return ExecuteTable (conn, SQL, paras); 227} 228} 229 230 # endregion231}