分享一个C#数据库操作辅助类
1 /// <summary> 2 /// 数据库操作辅助类 3 /// </summary> 4 public partial class SQLHelper 5 { 6 /// <summary> 7 /// 执行对象 8 /// </summary> 9 private static DbCommand dbCommand { get; set; } 10 11 /// <summary> 12 /// 事物状态 13 /// </summary> 14 public static int TransactionState { get; set; } 15 /// <summary> 16 /// 事物对象 17 /// </summary> 18 private static DbTransaction dbTransaction { get; set; } 19 20 /// <summary> 21 /// 连接对象 22 /// </summary> 23 private static DbConnection dbConnection { get; set; } 24 25 /// <summary> 26 /// 抽象数据工厂 27 /// </summary> 28 private static DbProviderFactory providerFactory; 29 30 /// <summary> 31 /// 数据库连接字符串 32 /// </summary> 33 public static string ConnectionString { get; set; } 34 35 /// <summary> 36 /// ProviderType类型 37 /// </summary> 38 public static ProviderType DBProviderType { get; set; } 39 40 /// <summary> 41 /// 构造函数 42 /// </summary> 43 static SQLHelper() 44 { 45 //获取数据库连接字符串 46 ConnectionString = ConfigHelper.ConnectionString; 47 //获取数据库类型 48 DBProviderType = ProviderFactory.GetProviderType(ConfigHelper.ProviderTypeName); 49 //获取providerFactory对象 50 providerFactory = ProviderFactory.GetDbProviderFactory(DBProviderType); 51 if (providerFactory == null) 52 { 53 throw new ArgumentException("Can't load DBProviderFactory for given value of ProviderType"); 54 } 55 dbConnection = providerFactory.CreateConnection(); 56 dbConnection.ConnectionString = ConnectionString; 57 } 58 59 #region 事务 60 /// <summary> 61 /// 开始事务 62 /// </summary> 63 public static void BeginTransaction() 64 { 65 if (dbConnection != null) 66 { 67 dbConnection.Open(); 68 dbTransaction = dbConnection.BeginTransaction(); 69 TransactionState = 1; 70 } 71 } 72 73 /// <summary> 74 /// 提交事务 75 /// </summary> 76 public static void Commit() 77 { 78 if (dbConnection != null && dbTransaction != null) 79 { 80 dbTransaction.Commit(); 81 dbTransaction = null; 82 dbConnection.Close(); 83 TransactionState = 0; 84 } 85 } 86 87 /// <summary> 88 /// 回滚事务 89 /// </summary> 90 public static void Rollback() 91 { 92 if (dbConnection != null && dbTransaction != null) 93 { 94 dbTransaction.Rollback(); 95 dbTransaction = null; 96 dbConnection.Close(); 97 TransactionState = 0; 98 } 99 } 100 101 #endregion 102 103 #region ExecuteNonQuery 104 105 /// <summary> 106 /// 执行增删改操作,返回受影响的行数。 107 /// </summary> 108 /// <param name="commandText">要执行的增删改的SQL语句</param> 109 /// <param name="parameters">执行增删改语句所需要的参数</param> 110 /// <returns></returns> 111 public static int ExecuteNonQuery(string commandText, params DbParameter[] parameters) 112 { 113 return DBExecuteNonQuery(CommandType.Text, commandText, parameters); 114 } 115 116 /// <summary> 117 /// 执行增删改操作,返回受影响的行数。 118 /// </summary> 119 /// <param name="commandType">执行的SQL语句的类型</param> 120 /// <param name="commandText">要执行的增删改的SQL语句</param> 121 /// <param name="parameters">执行增删改语句所需要的参数</param> 122 /// <returns></returns> 123 public static int ExecuteNonQuery(CommandType commandType, string commandText, params DbParameter[] parameters) 124 { 125 return DBExecuteNonQuery(commandType, commandText, parameters); 126 } 127 128 /// <summary> 129 /// 对数据库执行增删改操作,返回受影响的行数。 130 /// </summary> 131 /// <param name="commandType">执行的SQL语句的类型</param> 132 /// <param name="commandText">要执行的增删改的SQL语句</param> 133 /// <param name="trans">事物对象</param> 134 /// <param name="parameters">执行增删改语句所需要的参数</param> 135 /// <returns></returns> 136 private static int DBExecuteNonQuery(CommandType commandType, string commandText, DbParameter[] parameters) 137 { 138 try 139 { 140 using (dbCommand = CreateDbCommand(commandType, commandText, parameters)) 141 { 142 int ExecuteRows = dbCommand.ExecuteNonQuery(); 143 dbCommand.Parameters.Clear(); 144 CloseConnection(dbCommand); 145 return ExecuteRows; 146 } 147 } 148 catch (Exception ex) 149 { 150 new ExceptionHelper("SQLHELPRE_ExecuteNonQuery", ex); 151 TransactionState = 9; 152 return 0; 153 } 154 } 155 156 #endregion 157 158 #region ExecuteReader 159 160 /// <summary> 161 /// 执行查询语句,返回DataReader对象 162 /// </summary> 163 /// <param name="commandText">需要执行的查询SQL语句</param> 164 /// <param name="parameters">执行SQL查询语句所需要的参数</param> 165 /// <returns></returns> 166 public static DbDataReader ExecuteReader(string commandText, params DbParameter[] parameters) 167 { 168 return DBExecuteReader(CommandType.Text, commandText, parameters); 169 } 170 171 /// <summary> 172 /// 执行查询语句,返回DbDataReader对象 173 /// </summary> 174 /// <param name="commandType">执行的SQL语句的类型</param> 175 /// <param name="commandText">需要执行的查询SQL语句</param> 176 /// <param name="parameters">执行SQL查询语句所需要的参数</param> 177 /// <returns></returns> 178 public static DbDataReader ExecuteReader(CommandType commandType, string commandText, params DbParameter[] parameters) 179 { 180 return DBExecuteReader(commandType, commandText, parameters); 181 } 182 183 /// <summary> 184 /// 执行查询语句,返回DbDataReader对象 185 /// </summary> 186 /// <param name="commandType">执行的SQL语句的类型</param> 187 /// <param name="commandText">需要执行的查询SQL语句</param> 188 /// <param name="parameters">执行SQL查询语句所需要的参数</param> 189 /// <param name="trans">事物对象</param> 190 /// <returns></returns> 191 private static DbDataReader DBExecuteReader(CommandType commandType, string commandText, DbParameter[] parameters) 192 { 193 DbCommand command = CreateDbCommand(commandType, commandText, parameters); 194 try 195 { 196 DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); 197 command.Parameters.Clear(); 198 return reader; 199 } 200 catch (Exception ex) 201 { 202 command.Parameters.Clear(); 203 CloseConnection(command); 204 new ExceptionHelper("SQLHELPRE_ExecuteReader", ex); 205 return null; 206 } 207 } 208 209 #endregion 210 211 #region ExecuteScalar 212 213 /// <summary> 214 /// 执行查询语句,返回查询结果的第一行第一列 215 /// </summary> 216 /// <param name="commandText">要执行的SQL查询语句</param> 217 /// <param name="parameters">执行SQL查询语句所需要的参数</param> 218 /// <returns></returns> 219 public static object ExecuteScalar(string commandText, params DbParameter[] parameters) 220 { 221 return DBExecuteScalar(CommandType.Text, commandText, parameters); 222 } 223 224 /// <summary> 225 /// 执行查询语句,返回查询结果的第一行第一列 226 /// </summary> 227 /// <param name="commandType">执行的SQL语句的类型</param> 228 /// <param name="commandText">要执行的SQL查询语句</param> 229 /// <param name="parameters">执行SQL查询语句所需要的参数</param> 230 /// <returns></returns> 231 public static object ExecuteScalar(CommandType commandType, string commandText, params DbParameter[] parameters) 232 { 233 return DBExecuteScalar(commandType, commandText, parameters); 234 } 235 236 /// <summary> 237 /// 执行查询语句,返回查询结果的第一行第一列 238 /// </summary> 239 /// <param name="commandType">执行的SQL语句的类型</param> 240 /// <param name="commandText">要执行的SQL查询语句</param> 241 /// <param name="trans">事物对象</param> 242 /// <param name="parameters">执行SQL查询语句所需要的参数</param> 243 /// <returns></returns> 244 private static object DBExecuteScalar(CommandType commandType, string commandText, DbParameter[] parameters) 245 { 246 try 247 { 248 using (DbCommand command = CreateDbCommand(commandType, commandText, parameters)) 249 { 250 object result = command.ExecuteScalar(); 251 command.Parameters.Clear(); 252 CloseConnection(command); 253 return result; 254 } 255 } 256 catch (Exception ex) 257 { 258 new ExceptionHelper("SQLHELPRE_ExecuteScalar", ex); 259 return null; 260 } 261 } 262 263 #endregion 264 265 #region ExecuteDataTable 266 267 /// <summary> 268 /// 执行查询语句,返回查询结果的DataTable 269 /// </summary> 270 /// <param name="commandText">要执行的SQL查询语句</param> 271 /// <param name="parameters">执行SQL查询语句所需要的参数</param> 272 /// <returns></returns> 273 public static DataTable ExecuteDataTable(string commandText, params DbParameter[] parameters) 274 { 275 return DBExecuteDataTable(CommandType.Text, commandText, parameters); 276 } 277 278 /// <summary> 279 /// 执行查询语句,返回查询结果的DataTable 280 /// </summary> 281 /// <param name="commandType">执行的SQL语句的类型</param> 282 /// <param name="commandText">要执行的SQL查询语句</param> 283 /// <param name="parameters">执行SQL查询语句所需要的参数</param> 284 /// <returns></returns> 285 public static DataTable ExecuteDataTable(CommandType commandType, string commandText, params DbParameter[] parameters) 286 { 287 return DBExecuteDataTable(commandType, commandText, parameters); 288 } 289 290 /// <summary> 291 /// 执行查询语句,返回查询结果的DataTable 292 /// </summary> 293 /// <param name="commandType">执行的SQL语句的类型</param> 294 /// <param name="commandText">要执行的SQL查询语句</param> 295 /// <param name="trans">事物对象</param> 296 /// <param name="parameters">执行SQL查询语句所需要的参数</param> 297 /// <returns></returns> 298 private static DataTable DBExecuteDataTable(CommandType commandType, string commandText, DbParameter[] parameters) 299 { 300 try 301 { 302 using (DbCommand command = CreateDbCommand(commandType, commandText, parameters)) 303 { 304 using (DbDataAdapter adapter = providerFactory.CreateDataAdapter()) 305 { 306 adapter.SelectCommand = command; 307 DataTable datatab = new DataTable(); 308 adapter.Fill(datatab); 309 command.Parameters.Clear(); 310 CloseConnection(command); 311 return datatab; 312 } 313 } 314 } 315 catch (Exception ex) 316 { 317 new ExceptionHelper("SQLHELPRE_ExecuteDataTable", ex); 318 return null; 319 } 320 } 321 322 323 #endregion 324 325 #region CreateDBParameter 326 /// <summary> 327 /// 创建DbParameter参数 328 /// </summary> 329 /// <param name="name">参数名</param> 330 /// <param name="value">参数值</param> 331 /// <returns></returns> 332 public static DbParameter CreateDbParameter(string parameterName, object parameterValue) 333 { 334 return CreateDbParameter(parameterName, ParameterDirection.Input, parameterValue); 335 } 336 337 /// <summary> 338 /// 创建DbParameter参数 339 /// </summary> 340 /// <param name="name">参数名</param> 341 /// <param name="parameterDirection">参数类型</param> 342 /// <param name="value">参数值</param> 343 /// <returns></returns> 344 public static DbParameter CreateDbParameter(string parameterName, ParameterDirection parameterDirection, object parameterValue) 345 { 346 DbParameter parameter = providerFactory.CreateParameter(); 347 parameter.ParameterName = parameterName; 348 parameter.Value = parameterValue; 349 parameter.Direction = parameterDirection; 350 return parameter; 351 } 352 353 #endregion 354 355 /// <summary> 356 /// 关闭连接 357 /// </summary> 358 private static void CloseConnection(DbCommand command = null) 359 { 360 if (command != null) 361 { 362 if (dbTransaction == null) 363 { 364 command.Connection.Close(); 365 } 366 } 367 } 368 369 /// <summary> 370 /// 创建一个DbCommand对象 371 /// </summary> 372 /// <param name="sql">要执行的查询语句</param> 373 /// <param name="parameters">执行SQL查询语句所需要的参数</param> 374 /// <param name="commandType">执行的SQL语句的类型</param> 375 /// <returns></returns> 376 private static DbCommand CreateDbCommand(CommandType commandType, string commandText, params DbParameter[] parameters) 377 { 378 DbCommand command = dbConnection.CreateCommand(); 379 if (dbConnection == null) 380 { 381 dbConnection = providerFactory.CreateConnection(); 382 dbConnection.ConnectionString = ConnectionString; 383 } 384 command.Connection = dbConnection; 385 switch (dbConnection.State) 386 { 387 case ConnectionState.Closed: 388 dbConnection.Open(); 389 break; 390 case ConnectionState.Broken: 391 dbConnection.Close(); 392 dbConnection.Open(); 393 break; 394 } 395 command.CommandText = commandText; 396 command.CommandType = commandType; 397 if (dbTransaction != null) 398 { 399 command.Transaction = dbTransaction; 400 } 401 if (parameters != null) 402 { 403 command.Parameters.AddRange(parameters); 404 } 405 return command; 406 } 407 408 }