数据库小工具整合版,三个DbHelper数据操作类

By admin in 4858.com on 2019年3月25日

本小说为原创内容,如需转发,请表明小编及出处,谢谢!

自个儿在二零零七年三月11日写的一篇小说“数据库小工具(C#)”中提到:

 

      其实,微软的公司库中有2个要命不利的数码操作类了.不过,不少专营商(起码小编赶上的多少个…),对一些”封装”了些什么的事物不太敢用,固然本人引进过微软的集团库框架了…可是还是要”评估”…一评即是多少个月…而且,一些商行部分根本就是裸ado.net开发,也许本身包裹的数据库操作类分外别扭,很倒霉用.
      那里小编给大家共享3个本身参考企业库中的数据操作组件编码风格写的数据库操作类,对接纳它的程序员来说,编码是很舒服滴(起码作者觉得很好撒).以下是代码,很简单的,没有做别的多余的包装,只是改变了ADO.NET的编码步骤,方便了切实开销数据库操作代码的程序员.


在编写制定有关数据库方面的C#次第时,日常索要驾驭数据库的表中各字段的以下新闻:
1.
用来OracleParameter(或SqlParameter,…)中的字段和总体性的数据库特定的数据类型。

实在,微软的公司库中有1个十三分正确的多寡操作类了.但是,不少商店(起码笔者遇见的多少个…),对有的”封装”了些什么的事物不太敢用,固然作者引进过微软的公司库框架了…但是还是要”评估”…一评便是多少个月…而且,一些供销合作社部分根本就是裸ado.net开发,或然本身包装的数据库操作类相当别扭,很不好用.
     
那里笔者给大家共享一个本神草考集团库中的数据操作组件编码风格写的数据库操作类,对应用它的程序员来说,编码是很舒服滴(起码笔者认为很好撒).以下是代码,相当粗略的,没有做别的多余的包裹,只是改变了ADO.NET的编码步骤,方便了实际开发数据库操作代码的程序员.

4858.com 1    using System;
4858.com 2    using System.Data;
4858.com 3    using System.Data.Common;
4858.com 4    using System.Configuration;
4858.com 5
4858.com 6    public class DbHelper
4858.com 74858.com 8    4858.com 9{
4858.com 10        private static string dbProviderName = ConfigurationManager.AppSettings[“DbHelperProvider”];
4858.com 11        private static string dbConnectionString = ConfigurationManager.AppSettings[4858.com,”DbHelperConnectionString”];
4858.com 12
4858.com 13        private DbConnection connection;
4858.com 14        public DbHelper()
4858.com 154858.com 16        4858.com 17{
4858.com 18            this.connection = CreateConnection(DbHelper.dbConnectionString);
4858.com 19        }
4858.com 20        public DbHelper(string connectionString)
4858.com 214858.com 22        4858.com 23{
4858.com 24            this.connection = CreateConnection(connectionString);
4858.com 25        }
4858.com 26        public static DbConnection CreateConnection()
4858.com 274858.com 28        4858.com 29{
4858.com 30            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
4858.com 31            DbConnection dbconn = dbfactory.CreateConnection();
4858.com 32            dbconn.ConnectionString = DbHelper.dbConnectionString;
4858.com 33            return dbconn;
4858.com 34        }
4858.com 35        public static DbConnection CreateConnection(string connectionString)
4858.com 364858.com 37        4858.com 38{
4858.com 39            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
4858.com 40            DbConnection dbconn = dbfactory.CreateConnection();
4858.com 41            dbconn.ConnectionString = connectionString;
4858.com 42            return dbconn;
4858.com 43        }
4858.com 44
4858.com 45        public DbCommand GetStoredProcCommond(string storedProcedure)
4858.com 464858.com 47        4858.com 48{
4858.com 49            DbCommand dbCommand = connection.CreateCommand();
4858.com 50            dbCommand.CommandText = storedProcedure;
4858.com 51            dbCommand.CommandType = CommandType.StoredProcedure;
4858.com 52            return dbCommand;
4858.com 53        }
4858.com 54        public DbCommand GetSqlStringCommond(string sqlQuery)
4858.com 554858.com 56        4858.com 57{
4858.com 58            DbCommand dbCommand = connection.CreateCommand();
4858.com 59            dbCommand.CommandText = sqlQuery;
4858.com 60            dbCommand.CommandType = CommandType.Text;
4858.com 61            return dbCommand;
4858.com 62        }
4858.com 63
4858.com 644858.com 65        增添参数#region 扩展参数
4858.com 66        public void AddParameterCollection(DbCommand cmd, DbParameterCollection dbParameterCollection)
4858.com 674858.com 68        4858.com 69{
4858.com 70            foreach (DbParameter dbParameter in dbParameterCollection)
4858.com 714858.com 72            4858.com 73{
4858.com 74                cmd.Parameters.Add(dbParameter);
4858.com 75            }
4858.com 76        }
4858.com 77        public void AddOutParameter(DbCommand cmd, string parameterName, DbType dbType, int size)
4858.com 784858.com 79        4858.com 80{
4858.com 81            DbParameter dbParameter = cmd.CreateParameter();
4858.com 82            dbParameter.DbType = dbType;
4858.com 83            dbParameter.ParameterName = parameterName;
4858.com 84            dbParameter.Size = size;
4858.com 85            dbParameter.Direction = ParameterDirection.Output;
4858.com 86            cmd.Parameters.Add(dbParameter);
4858.com 87        }
4858.com 88        public void AddInParameter(DbCommand cmd, string parameterName, DbType dbType, object value)
4858.com 894858.com 90        4858.com 91{
4858.com 92            DbParameter dbParameter = cmd.CreateParameter();
4858.com 93            dbParameter.DbType = dbType;
4858.com 94            dbParameter.ParameterName = parameterName;
4858.com 95            dbParameter.Value = value;
4858.com 96            dbParameter.Direction = ParameterDirection.Input;
4858.com 97            cmd.Parameters.Add(dbParameter);
4858.com 98        }
4858.com 99        public void AddReturnParameter(DbCommand cmd, string parameterName, DbType dbType)
4858.com 1004858.com 101        4858.com 102{
4858.com 103            DbParameter dbParameter = cmd.CreateParameter();
4858.com 104            dbParameter.DbType = dbType;
4858.com 105            dbParameter.ParameterName = parameterName;
4858.com 106            dbParameter.Direction = ParameterDirection.ReturnValue;
4858.com 107            cmd.Parameters.Add(dbParameter);
4858.com 108        }
4858.com 109        public DbParameter GetParameter(DbCommand cmd, string parameterName)
4858.com 1104858.com 111        4858.com 112{
4858.com 113            return cmd.Parameters[parameterName];
4858.com 114        }
4858.com 115
4858.com 116        #endregion
4858.com 117
4858.com 1184858.com 119        执行#region 执行
4858.com 120        public DataSet ExecuteDataSet(DbCommand cmd)
4858.com 1214858.com 122        4858.com 123{
4858.com 124            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
4858.com 125            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
4858.com 126            dbDataAdapter.SelectCommand = cmd;
4858.com 127            DataSet ds = new DataSet();
4858.com 128            dbDataAdapter.Fill(ds);
4858.com 129            return ds;
4858.com 130        }
4858.com 131
4858.com 132        public DataTable ExecuteDataTable(DbCommand cmd)
4858.com 1334858.com 134        4858.com 135{
4858.com 136            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
4858.com 137            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
4858.com 138            dbDataAdapter.SelectCommand = cmd;
4858.com 139            DataTable dataTable = new DataTable();
4858.com 140            dbDataAdapter.Fill(dataTable);
4858.com 141            return dataTable;
4858.com 142        }
4858.com 143
4858.com 144        public DbDataReader ExecuteReader(DbCommand cmd)
4858.com 1454858.com 146        4858.com 147{
4858.com 148            cmd.Connection.Open();
4858.com 149            DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);            
4858.com 150            return reader;
4858.com 151        }
4858.com 152        public int ExecuteNonQuery(DbCommand cmd)
4858.com 1534858.com 154        4858.com 155{
4858.com 156            cmd.Connection.Open();
4858.com 157            int ret = cmd.ExecuteNonQuery();
4858.com 158            cmd.Connection.Close();
4858.com 159            return ret;
4858.com 160        }
4858.com 161
4858.com 162        public object ExecuteScalar(DbCommand cmd)
4858.com 1634858.com 164        4858.com 165{
4858.com 166            cmd.Connection.Open();
4858.com 167            object ret = cmd.ExecuteScalar();
4858.com 168            cmd.Connection.Close();
4858.com 169            return ret;
4858.com 170        }
4858.com 171        #endregion        
4858.com 172
4858.com 1734858.com 174数据库小工具整合版,三个DbHelper数据操作类。        执行工作#region 执行工作
4858.com 175        public DataSet ExecuteDataSet(DbCommand cmd,Trans t)
4858.com 1764858.com 177        4858.com 178{
4858.com 179            cmd.Connection = t.DbConnection;
4858.com 180            cmd.Transaction = t.DbTrans;
4858.com 181            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
4858.com 182            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
4858.com 183            dbDataAdapter.SelectCommand = cmd;
4858.com 184            DataSet ds = new DataSet();
4858.com 185            dbDataAdapter.Fill(ds);
4858.com 186            return ds;
4858.com 187        }
4858.com 188
4858.com 189        public DataTable ExecuteDataTable(DbCommand cmd, Trans t)
4858.com 1904858.com 191        4858.com 192{
4858.com 193            cmd.Connection = t.DbConnection;
4858.com 194            cmd.Transaction = t.DbTrans;
4858.com 195            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
4858.com 196            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
4858.com 197            dbDataAdapter.SelectCommand = cmd;
4858.com 198            DataTable dataTable = new DataTable();
4858.com 199            dbDataAdapter.Fill(dataTable);
4858.com 200            return dataTable;
4858.com 201        }
4858.com 202
4858.com 203        public DbDataReader ExecuteReader(DbCommand cmd, Trans t)
4858.com 2044858.com 205        4858.com 206{
4858.com 207            cmd.Connection.Close();
4858.com 208            cmd.Connection = t.DbConnection;
4858.com 209            cmd.Transaction = t.DbTrans;            
4858.com 210            DbDataReader reader = cmd.ExecuteReader();
4858.com 211            DataTable dt = new DataTable();            
4858.com 212            return reader;
4858.com 213        }
4858.com 214        public int ExecuteNonQuery(DbCommand cmd, Trans t)
4858.com 2154858.com 216        4858.com 217{
4858.com 218            cmd.Connection.Close();
4858.com 219            cmd.Connection = t.DbConnection;
4858.com 220            cmd.Transaction = t.DbTrans;  
4858.com 221            int ret = cmd.ExecuteNonQuery();            
4858.com 222            return ret;
4858.com 223        }
4858.com 224
4858.com 225        public object ExecuteScalar(DbCommand cmd, Trans t)
4858.com 2264858.com 227        4858.com 228{
4858.com 229            cmd.Connection.Close();
4858.com 230            cmd.Connection = t.DbConnection;
4858.com 231            cmd.Transaction = t.DbTrans;  
4858.com 232            object ret = cmd.ExecuteScalar();            
4858.com 233            return ret;
4858.com 234        }
4858.com 235        #endregion
4858.com 236    }
4858.com 237
4858.com 238    public class Trans : IDisposable
4858.com 2394858.com 240    4858.com 241{
4858.com 242        private DbConnection conn;
4858.com 243        private DbTransaction dbTrans;
4858.com 244        public DbConnection DbConnection
4858.com 2454858.com 246        4858.com 247{
4858.com 2484858.com 249            get 4858.com 250{ return this.conn; }
4858.com 251        }
4858.com 252        public DbTransaction DbTrans
4858.com 2534858.com 254        4858.com 255{
4858.com 2564858.com 257            get 4858.com 258{ return this.dbTrans; }
4858.com 259        }
4858.com 260
4858.com 261        public Trans()
4858.com 2624858.com 263        4858.com 264{
4858.com 265            conn = DbHelper.CreateConnection();
4858.com 266            conn.Open();
4858.com 267            dbTrans = conn.BeginTransaction();
4858.com 268        }
4858.com 269        public Trans(string connectionString)
4858.com 2704858.com 271        4858.com 272{
4858.com 273            conn = DbHelper.CreateConnection(connectionString);
4858.com 274            conn.Open();
4858.com 275            dbTrans = conn.BeginTransaction();
4858.com 276        }
4858.com 277        public void Commit()
4858.com 2784858.com 279        4858.com 280{
4858.com 281            dbTrans.Commit();
4858.com 282            this.Colse();
4858.com 283        }
4858.com 284
4858.com 285        public void RollBack()
4858.com 2864858.com 287        4858.com 288{
4858.com 289            dbTrans.Rollback();
4858.com 290            this.Colse();
4858.com 291        }
4858.com 292
4858.com 293        public void Dispose()
4858.com 2944858.com 295        4858.com 296{
4858.com 297            this.Colse();
4858.com 298        }
4858.com 299
4858.com 300        public void Colse()
4858.com 3014858.com 302        4858.com 303{
4858.com 304            if (conn.State == System.Data.ConnectionState.Open)
4858.com 3054858.com 306            4858.com 307{
4858.com 308                conn.Close();
4858.com 309            }
4858.com 310        }
4858.com 311    }

 

  1. 其对应的.NET数据类型。

4858.com 312 using System;
4858.com 313 using System.Data;
4858.com 314 using
System.Data.Common;
4858.com 315 using
System.Configuration;
4858.com 316
4858.com 317 public class DbHelper
4858.com 3184858.com 319
4858.com 320{
4858.com 321 private static string
dbProviderName =
ConfigurationManager.AppSettings[“DbHelperProvider”];
4858.com 322 private static string
dbConnectionString =
ConfigurationManager.AppSettings[“DbHelperConnectionString”];
4858.com 323
4858.com 324 private DbConnection
connection;
4858.com 325 public DbHelper()
4858.com 3264858.com 327
4858.com 328{
4858.com 329 this.connection =
CreateConnection(DbHelper.dbConnectionString);
4858.com 330        }
4858.com 331 public DbHelper(string
connectionString)
4858.com 3324858.com 333
4858.com 334{
4858.com 335 this.connection =
CreateConnection(connectionString);
4858.com 336        }
4858.com 337 public static DbConnection
CreateConnection()
4858.com 3384858.com 339
4858.com 340{
4858.com 341           
DbProviderFactory dbfactory =
DbProviderFactories.GetFactory(DbHelper.dbProviderName);
4858.com 342            DbConnection
dbconn = dbfactory.CreateConnection();
4858.com 343           
dbconn.ConnectionString = DbHelper.dbConnectionString;
4858.com 344 return dbconn;
4858.com 345        }
4858.com 346 public static DbConnection
CreateConnection(string connectionString)
4858.com 3474858.com 348
4858.com 349{
4858.com 350           
DbProviderFactory dbfactory =
DbProviderFactories.GetFactory(DbHelper.dbProviderName);
4858.com 351            DbConnection
dbconn = dbfactory.CreateConnection();
4858.com 352           
dbconn.ConnectionString = connectionString;
4858.com 353 return dbconn;
4858.com 354        }
4858.com 355
4858.com 356 public DbCommand
GetStoredProcCommond(string storedProcedure)
4858.com 3574858.com 358
4858.com 359{
4858.com 360            DbCommand
dbCommand = connection.CreateCommand();
4858.com 361           
dbCommand.CommandText = storedProcedure;
4858.com 362           
dbCommand.CommandType = CommandType.StoredProcedure;
4858.com 363 return dbCommand;
4858.com 364        }
4858.com 365 public DbCommand
GetSqlStringCommond(string sqlQuery)
4858.com 3664858.com 367
4858.com 368{
4858.com 369            DbCommand
dbCommand = connection.CreateCommand();
4858.com 370           
dbCommand.CommandText = sqlQuery;
4858.com 371           
dbCommand.CommandType = CommandType.Text;
4858.com 372 return dbCommand;
4858.com 373        }
4858.com 374
4858.com 3754858.com 376
增添参数#region 扩张参数
4858.com 377 public void
AddParameterCollection(DbCommand cmd, DbParameterCollection
dbParameterCollection)
4858.com 3784858.com 379
4858.com 380{
4858.com 381 foreach (DbParameter
dbParameter in dbParameterCollection)
4858.com 3824858.com 383
4858.com 384{
4858.com 385               
cmd.Parameters.Add(dbParameter);
4858.com 386            }
4858.com 387        }
4858.com 388 public void
AddOutParameter(DbCommand cmd, string parameterName, DbType dbType, int
size)
4858.com 3894858.com 390
4858.com 391{
4858.com 392            DbParameter
dbParameter = cmd.CreateParameter();
4858.com 393           
dbParameter.DbType = dbType;
4858.com 394           
dbParameter.ParameterName = parameterName;
4858.com 395           
dbParameter.Size = size;
4858.com 396           
dbParameter.Direction = ParameterDirection.Output;
4858.com 397           
cmd.Parameters.Add(dbParameter);
4858.com 398        }
4858.com 399 public void
AddInParameter(DbCommand cmd, string parameterName, DbType dbType,
object value)
4858.com 4004858.com 401
4858.com 402{
4858.com 403            DbParameter
dbParameter = cmd.CreateParameter();
4858.com 404           
dbParameter.DbType = dbType;
4858.com 405           
dbParameter.ParameterName = parameterName;
4858.com 406           
dbParameter.Value = value;
4858.com 407           
dbParameter.Direction = ParameterDirection.Input;
4858.com 408           
cmd.Parameters.Add(dbParameter);
4858.com 409        }
4858.com 410 public void
AddReturnParameter(DbCommand cmd, string parameterName, DbType dbType)
4858.com 4114858.com 412
4858.com 413{
4858.com 414            DbParameter
dbParameter = cmd.CreateParameter();
4858.com 415           
dbParameter.DbType = dbType;
4858.com 416           
dbParameter.ParameterName = parameterName;
4858.com 417           
dbParameter.Direction = ParameterDirection.ReturnValue;
4858.com 418           
cmd.Parameters.Add(dbParameter);
4858.com 419        }
4858.com 420 public DbParameter
GetParameter(DbCommand cmd, string parameterName)
4858.com 4214858.com 422
4858.com 423{
4858.com 424 return
cmd.Parameters[parameterName];
4858.com 425        }
4858.com 426
4858.com 427 #endregion
4858.com 428
4858.com 4294858.com 430
执行#region 执行
4858.com 431 public DataSet
ExecuteDataSet(DbCommand cmd)
4858.com 4324858.com 433
4858.com 434{
4858.com 435           
DbProviderFactory dbfactory =
DbProviderFactories.GetFactory(DbHelper.dbProviderName);
4858.com 436            DbDataAdapter
dbDataAdapter = dbfactory.CreateDataAdapter();
4858.com 437           
dbDataAdapter.SelectCommand = cmd;
4858.com 438            DataSet ds =
new DataSet();
4858.com 439           
dbDataAdapter.Fill(ds);
4858.com 440 return ds;
4858.com 441        }
4858.com 442
4858.com 443 public DataTable
ExecuteDataTable(DbCommand cmd)
4858.com 4444858.com 445
4858.com 446{
4858.com 447           
DbProviderFactory dbfactory =
DbProviderFactories.GetFactory(DbHelper.dbProviderName);
4858.com 448            DbDataAdapter
dbDataAdapter = dbfactory.CreateDataAdapter();
4858.com 449           
dbDataAdapter.SelectCommand = cmd;
4858.com 450            DataTable
dataTable = new DataTable();
4858.com 451           
dbDataAdapter.Fill(dataTable);
4858.com 452 return dataTable;
4858.com 453        }
4858.com 454
4858.com 455 public DbDataReader
ExecuteReader(DbCommand cmd)
4858.com 4564858.com 457
4858.com 458{
4858.com 459           
cmd.Connection.Open();
4858.com 460            DbDataReader
reader =
cmd.ExecuteReader(CommandBehavior.CloseConnection);           
4858.com 461 return reader;
4858.com 462        }
4858.com 463 public int
ExecuteNonQuery(DbCommand cmd)
4858.com 4644858.com 465
4858.com 466{
4858.com 467           
cmd.Connection.Open();
4858.com 468 int ret =
cmd.ExecuteNonQuery();
4858.com 469           
cmd.Connection.Close();
4858.com 470 return ret;
4858.com 471        }
4858.com 472
4858.com 473 public object
ExecuteScalar(DbCommand cmd)
4858.com 4744858.com 475
4858.com 476{
4858.com 477           
cmd.Connection.Open();
4858.com 478 object ret =
cmd.ExecuteScalar();
4858.com 479           
cmd.Connection.Close();
4858.com 480 return ret;
4858.com 481        }
4858.com 482 #endregion
4858.com 483
4858.com 4844858.com 485
执行工作#region 执行工作
4858.com 486 public DataSet
ExecuteDataSet(DbCommand cmd,Trans t)
4858.com 4874858.com 488
4858.com 489{
4858.com 490            cmd.Connection
= t.DbConnection;
4858.com 491            cmd.Transaction
= t.DbTrans;
4858.com 492           
DbProviderFactory dbfactory =
DbProviderFactories.GetFactory(DbHelper.dbProviderName);
4858.com 493            DbDataAdapter
dbDataAdapter = dbfactory.CreateDataAdapter();
4858.com 494           
dbDataAdapter.SelectCommand = cmd;
4858.com 495            DataSet ds =
new DataSet();
4858.com 496           
dbDataAdapter.Fill(ds);
4858.com 497 return ds;
4858.com 498        }
4858.com 499
4858.com 500 public DataTable
ExecuteDataTable(DbCommand cmd, Trans t)
4858.com 5014858.com 502
4858.com 503{
4858.com 504            cmd.Connection
= t.DbConnection;
4858.com 505            cmd.Transaction
= t.DbTrans;
4858.com 506           
DbProviderFactory dbfactory =
DbProviderFactories.GetFactory(DbHelper.dbProviderName);
4858.com 507            DbDataAdapter
dbDataAdapter = dbfactory.CreateDataAdapter();
4858.com 508           
dbDataAdapter.SelectCommand = cmd;
4858.com 509            DataTable
dataTable = new DataTable();
4858.com 510           
dbDataAdapter.Fill(dataTable);
4858.com 511 return dataTable;
4858.com 512        }
4858.com 513
4858.com 514 public DbDataReader
ExecuteReader(DbCommand cmd, Trans t)
4858.com 5154858.com 516
4858.com 517{
4858.com 518           
cmd.Connection.Close();
4858.com 519            cmd.Connection
= t.DbConnection;
4858.com 520            cmd.Transaction
= t.DbTrans;           
4858.com 521            DbDataReader
reader = cmd.ExecuteReader();
4858.com 522            DataTable dt =
new DataTable();           
4858.com 523 return reader;
4858.com 524        }
4858.com 525 public int
ExecuteNonQuery(DbCommand cmd, Trans t)
4858.com 5264858.com 527
4858.com 528{
4858.com 529           
cmd.Connection.Close();
4858.com 530            cmd.Connection
= t.DbConnection;
4858.com 531            cmd.Transaction
= t.DbTrans; 
4858.com 532 int ret =
cmd.ExecuteNonQuery();           
4858.com 533 return ret;
4858.com 534        }
4858.com 535
4858.com 536 public object
ExecuteScalar(DbCommand cmd, Trans t)
4858.com 5374858.com 538
4858.com 539{
4858.com 540           
cmd.Connection.Close();
4858.com 541            cmd.Connection
= t.DbConnection;
4858.com 542            cmd.Transaction
= t.DbTrans; 
4858.com 543 object ret =
cmd.ExecuteScalar();           
4858.com 544 return ret;
4858.com 545        }
4858.com 546 #endregion
4858.com 547    }
4858.com 548
4858.com 549 public class Trans :
IDisposable
4858.com 5504858.com 551
4858.com 552{
4858.com 553 private DbConnection
conn;
4858.com 554 private DbTransaction
dbTrans;
4858.com 555 public DbConnection
DbConnection
4858.com 5564858.com 557
4858.com 558{
4858.com 5594858.com 560
get 4858.com 561{ return this.conn; }
4858.com 562        }
4858.com 563 public DbTransaction
DbTrans
4858.com 5644858.com 565
4858.com 566{
4858.com 5674858.com 568
get 4858.com 569{ return this.dbTrans;
}
4858.com 570        }
4858.com 571
4858.com 572 public Trans()
4858.com 5734858.com 574
4858.com 575{
4858.com 576            conn =
DbHelper.CreateConnection();
4858.com 577            conn.Open();
4858.com 578            dbTrans =
conn.BeginTransaction();
4858.com 579        }
4858.com 580 public Trans(string
connectionString)
4858.com 5814858.com 582
4858.com 583{
4858.com 584            conn =
DbHelper.CreateConnection(connectionString);
4858.com 585            conn.Open();
4858.com 586            dbTrans =
conn.BeginTransaction();
4858.com 587        }
4858.com 588 public void Commit()
4858.com 5894858.com 590
4858.com 591{
4858.com 592           
dbTrans.Commit();
4858.com 593 this.Colse();
4858.com 594        }
4858.com 595
4858.com 596 public void RollBack()
4858.com 5974858.com 598
4858.com 599{
4858.com 600           
dbTrans.Rollback();
4858.com 601 this.Colse();
4858.com 602        }
4858.com 603
4858.com 604 public void Dispose()
4858.com 6054858.com 606
4858.com 607{
4858.com 608 this.Colse();
4858.com 609        }
4858.com 610
4858.com 611 public void Colse()
4858.com 6124858.com 613
4858.com 614{
4858.com 615 if (conn.State ==
System.Data.ConnectionState.Open)
4858.com 6164858.com 617
4858.com 618{
4858.com 619               
conn.Close();
4858.com 620            }
4858.com 621        }
4858.com 622    }

那正是说怎么着运用它吧?下边笔者付诸一些中央的行使示例,基本能满意你超过1/2的数据库操作供给了.
1)间接执行sql语句

壹 、在System.Data.Common命名空间下,存在这么的四个类:

立即,对一一差别的数据库是个别完结那些“数据库小工具”的。

那就是说怎样行使它吗?上边小编付诸一些着力的行使示例,基本能满意你超越四分之一的数据库操作要求了.
1)直接执行sql语句

4858.com 623        DbHelper db = new DbHelper();
4858.com 624        DbCommand cmd = db.GetSqlStringCommond(“insert t1 (id)values(‘haha’)”);
4858.com 625        db.ExecuteNonQuery(cmd);

    //
    // 摘要:
    //     表示一组方法,这些方法用于创建提供程序对数据源类的实现的实例。
    public abstract class DbProviderFactory
    {
        //
        // 摘要:
        //     初始化 System.Data.Common.DbProviderFactory 类的新实例。
        protected DbProviderFactory();

        //
        // 摘要:
        //     指定特定的 System.Data.Common.DbProviderFactory 是否支持 System.Data.Common.DbDataSourceEnumerator
        //     类。
        //
        // 返回结果:
        //     如果 System.Data.Common.DbProviderFactory 的实例支持 System.Data.Common.DbDataSourceEnumerator
        //     类,则为 true;否则为 false。
        public virtual bool CanCreateDataSourceEnumerator { get; }

        //
        // 摘要:
        //     返回实现 System.Data.Common.DbCommand 类的提供程序的类的一个新实例。
        //
        // 返回结果:
        //     System.Data.Common.DbCommand 的新实例。
        public virtual DbCommand CreateCommand();
        //
        // 摘要:
        //     返回实现 System.Data.Common.DbCommandBuilder 类的提供程序的类的一个新实例。
        //
        // 返回结果:
        //     System.Data.Common.DbCommandBuilder 的新实例。
        public virtual DbCommandBuilder CreateCommandBuilder();
        //
        // 摘要:
        //     返回实现 System.Data.Common.DbConnection 类的提供程序的类的一个新实例。
        //
        // 返回结果:
        //     System.Data.Common.DbConnection 的新实例。
        public virtual DbConnection CreateConnection();
        //
        // 摘要:
        //     返回实现 System.Data.Common.DbConnectionStringBuilder 类的提供程序的类的一个新实例。
        //
        // 返回结果:
        //     System.Data.Common.DbConnectionStringBuilder 的新实例。
        public virtual DbConnectionStringBuilder CreateConnectionStringBuilder();
        //
        // 摘要:
        //     返回实现 System.Data.Common.DbDataAdapter 类的提供程序的类的一个新实例。
        //
        // 返回结果:
        //     System.Data.Common.DbDataAdapter 的新实例。
        public virtual DbDataAdapter CreateDataAdapter();
        //
        // 摘要:
        //     返回实现 System.Data.Common.DbDataSourceEnumerator 类的提供程序的类的一个新实例。
        //
        // 返回结果:
        //     System.Data.Common.DbDataSourceEnumerator 的新实例。
        public virtual DbDataSourceEnumerator CreateDataSourceEnumerator();
        //
        // 摘要:
        //     返回实现 System.Data.Common.DbParameter 类的提供程序的类的一个新实例。
        //
        // 返回结果:
        //     System.Data.Common.DbParameter 的新实例。
        public virtual DbParameter CreateParameter();
        //
        // 摘要:
        //     返回提供程序的类的新实例,该实例可实现提供程序的 System.Security.CodeAccessPermission 类的版本。
        //
        // 参数:
        //   state:
        //     System.Security.Permissions.PermissionState 值之一。
        //
        // 返回结果:
        //     指定 System.Security.Permissions.PermissionState 的 System.Security.CodeAccessPermission
        //     对象。
        public virtual CodeAccessPermission CreatePermission(PermissionState state);
    }

近期,让大家将这个独立的“小工具”整合在协同吧。

4858.com 626        DbHelper db = new
DbHelper();
4858.com 627        DbCommand cmd =
db.GetSqlStringCommond(“insert t1 (id)values(‘haha’)”);
4858.com 628       
db.ExecuteNonQuery(cmd);

2)执行存款和储蓄进度

我们能够看看,在此类中,有过多用以创设数据库相关对象的类型,如DbConnection,DbCommand,DbDataAdapter等。

4858.com 629

2)执行存款和储蓄进程

4858.com 630        DbHelper db = new DbHelper();
4858.com 631        DbCommand cmd = db.GetStoredProcCommond(“t1_insert”);
4858.com 632        db.AddInParameter(cmd, “@id”, DbType.String, “heihei”);
4858.com 633        db.ExecuteNonQuery(cmd);

而且,完成诸如SqlConnection、SqlCommand、SqlDataAdapter(那里用的是SQL
Server)的连串,都各自继承自DbConnection,DbCommand,DbDataAdapter,

从上海体育地方中得以看来 Odbc、奥莱Db、Oracle、SQL Server、SQL Server Mobile
Edition、SQL Server Compact 艾德ition、SQLite、MySql
等数据库都曾经构成在同一个先后中了。

4858.com 634        DbHelper db = new
DbHelper();
4858.com 635        DbCommand cmd =
db.GetStoredProcCommond(“t1_insert”);
4858.com 636       
db.AddInParameter(cmd, “@id”, DbType.String, “heihei”);
4858.com 637       
db.ExecuteNonQuery(cmd);

3)返回DataSet

故此,大家得以应用DbProviderFactory来成立大家想要的、可完结别的数据库的DbHelper。

4858.com 638

3)返回DataSet

4858.com 639        DbHelper db = new DbHelper();
4858.com 640        DbCommand cmd = db.GetSqlStringCommond(“select * from t1”);
4858.com 641        DataSet ds = db.ExecuteDataSet(cmd);

贰 、实现大旨的DbHelper帮忙类

上海体育场面呈现执行一条 SQL 语句后的结果。

4858.com 642        DbHelper db = new
DbHelper();
4858.com 643        DbCommand cmd =
db.GetSqlStringCommond(“select * from t1”);
4858.com 644        DataSet ds =
db.ExecuteDataSet(cmd);

4)返回DataTable

壹 、我们将DbHelper定义为抽象类,并在类中提供七个抽象可读属性,名称叫DbProviderFactory,重返类型为DbProviderFactory(注:名称与再次来到类型能够为同一,也足以不均等),

4858.com 645

4)返回DataTable

4858.com 646        DbHelper db = new DbHelper();
4858.com 647        DbCommand cmd = db.GetSqlStringCommond(“t1_findall”);
4858.com 648        DataTable dt = db.ExecuteDataTable(cmd);

贰 、我们运用在该抽象类完结的子类中重写DbProviderFactory方法,并在子类的构造函数中为该属性赋值,该值正是已经达成了实际数据库类型的DbProviderFactory。

上海图书馆彰显出数据库中的表的组织。

4858.com 649        DbHelper db = new
DbHelper();
4858.com 650        DbCommand cmd =
db.GetSqlStringCommond(“t1_findall”);
4858.com 651        DataTable dt =
db.ExecuteDataTable(cmd);

5)输入参数/输出参数/再次来到值的运用(相比较根本哦)

概念的代码参考如下:

好了,大家来看源程序吗:

5)输入参数/输出参数/重临值的运用(相比较主要哦)

4858.com 652        DbHelper db = new DbHelper();
4858.com 653        DbCommand cmd = db.GetStoredProcCommond(“t2_insert”);
4858.com 654        db.AddInParameter(cmd, “@timeticks”, DbType.Int64, DateTime.Now.Ticks);
4858.com 655        db.AddOutParameter(cmd, “@outString”, DbType.String, 20);
4858.com 656        db.AddReturnParameter(cmd, “@returnValue”, DbType.Int32);
4858.com 657
4858.com 658        db.ExecuteNonQuery(cmd);
4858.com 659
4858.com 660        string s = db.GetParameter(cmd, “@outString”).Value as string;//out parameter
4858.com 661        int r = Convert.ToInt32(db.GetParameter(cmd, “@returnValue”).Value);//return value
4858.com 662

    public abstract class DbHelper
    {
        public abstract DbProviderFactory DbProviderFactory { get; }
    }
using System;
using System.IO;
using System.Data;
using System.Data.Common;
using System.Windows.Forms;

namespace Skyiv.Ben.DbTools
{
  public partial class MainForm : Form
  {
    public MainForm()
    {
      InitializeComponent();
    }

    private void MainForm_Load(object sender, EventArgs e)
    {
      try
      {
        tbxDsn.Text = "Data Source=:memory:";
        tbxSql.Text = "select sqlite_version()";
        var table = DbProviderFactories.GetFactoryClasses();
        lbxDbProvider.ValueMember = "InvariantName";
        lbxDbProvider.DataSource = table;
        dgvMain.DataSource = table;
        lbxDbProvider.SelectedValue = "System.Data.SQLite";
      }
      catch (Exception ex)
      {
        tbxMessage.AppendText(Pub.GetMessage(ex));
      }
    }

    private void btnSubmit_Click(object sender, EventArgs e)
    {
      btnSubmit.Enabled = false;
      try
      {
        tbxMessage.Clear();
        var factory = DbProviderFactories.GetFactory(lbxDbProvider.SelectedValue.ToString());
        using (var conn = factory.CreateConnection())
        {
          conn.ConnectionString = tbxDsn.Text;
          conn.Open();
          tbxMessage.AppendLine("Client Version: [{0}]", Pub.GetClientVersion(Path.GetFileNameWithoutExtension(factory.ToString())));
          tbxMessage.AppendLine("Server Version: [{0}]", conn.ServerVersion);
          var sql = tbxSql.Text.Trim();
          if (sql.Length != 0)
          {
            var comm = conn.CreateCommand();
            comm.CommandText = sql;
            var isQuery = IsQuery(sql);
            var rows = int.MinValue;
            if (!isQuery) rows = comm.ExecuteNonQuery();
            else if (chkStruct.Checked) dgvMain.DataSource = RunQueryTableStruct(comm);
            else dgvMain.DataSource = RunQueryTableData(factory, comm);
            tbxMessage.AppendText("运行 SQL 语句完毕(" + (!isQuery ? "非查询" : (chkStruct.Checked ? "表结构" : "查询")) + ")");
            if (rows >= 0) tbxMessage.AppendText(",受影响的行数: " + rows.ToString("N0"));
          }
          else tbxMessage.AppendText("完成");
        }
      }
      catch (Exception ex)
      {
        tbxMessage.AppendText(Pub.GetMessage(ex));
      }
      btnSubmit.Enabled = true;
    }

    DataView RunQueryTableStruct(DbCommand comm)
    {
      using (var r = comm.ExecuteReader(CommandBehavior.KeyInfo))
      {
        return r.GetSchemaTable().DefaultView;
      }
    }

    DataView RunQueryTableData(DbProviderFactory factory, DbCommand comm)
    {
      var da = factory.CreateDataAdapter();
      da.SelectCommand = comm;
      var ds = new DataSet();
      da.Fill(ds);
      return ds.Tables[0].DefaultView;
    }

    bool IsQuery(string sql)
    {
      return sql.ToUpper().StartsWith("SELECT");
    }
  }
}

4858.com 663        DbHelper db = new
DbHelper();
4858.com 664        DbCommand cmd =
db.GetStoredProcCommond(“t2_insert”);
4858.com 665       
db.AddInParameter(cmd, “@timeticks”, DbType.Int64,
DateTime.Now.Ticks);
4858.com 666       
db.AddOutParameter(cmd, “@outString”, DbType.String, 20);
4858.com 667       
db.AddReturnParameter(cmd, “@returnValue”, DbType.Int32);
4858.com 668
4858.com 669       
db.ExecuteNonQuery(cmd);
4858.com 670
4858.com 671 string s =
db.GetParameter(cmd, “@outString”).Value as string;//out parameter
4858.com 672 int r =
Convert.ToInt32(db.GetParameter(cmd, “@returnValue”).Value);//return
value
4858.com 673

6)DataReader使用

叁 、我们为该抽象类编写八个构造函数,传进去的参数为总是字符串,并将其储存在可读的ConnectionString字段里,代码如下:

其一顺序首要接纳 System.Data.Common 命名空间中的类来拓展工作 。

6)DataReader使用

4858.com 674      DbHelper db = new DbHelper();
4858.com 675        DbCommand cmd = db.GetStoredProcCommond(“t2_insert”);
4858.com 676        db.AddInParameter(cmd, “@timeticks”, DbType.Int64, DateTime.Now.Ticks);
4858.com 677        db.AddOutParameter(cmd, “@outString”, DbType.String, 20);
4858.com 678        db.AddReturnParameter(cmd, “@returnValue”, DbType.Int32);
4858.com 679
4858.com 680        using (DbDataReader reader = db.ExecuteReader(cmd))
4858.com 6814858.com 682        4858.com 683{
4858.com 684            dt.Load(reader);
4858.com 685        }        
4858.com 686        string s = db.GetParameter(cmd, “@outString”).Value as string;//out parameter
4858.com 687        int r = Convert.ToInt32(db.GetParameter(cmd, “@returnValue”).Value);//return value
4858.com 688

    public abstract class DbHelper
    {
        public DbHelper(string connectionString)
        {
            ConnectionString = connectionString;
        }
        public string ConnectionString { get; }
        public abstract DbProviderFactory DbProviderFactory { get; }
    }

在 MainForm_Load 方法(第②6到32行)中选用 DbProviderFactories 的静态方法
GetFactoryClasses 得到在本机中达成 DbProviderFactories
的拥有已设置提供程序的音信(第三2行),然后再将其绑定到程序主界面包车型大巴 ListBox(第34行) 和 DataGridView (第叁5行) 控件上。那样,那七个控件就会联合浮动了。

4858.com 689      DbHelper db = new
DbHelper();
4858.com 690        DbCommand cmd =
db.GetStoredProcCommond(“t2_insert”);
4858.com 691       
db.AddInParameter(cmd, “@timeticks”, DbType.Int64,
DateTime.Now.Ticks);
4858.com 692       
db.AddOutParameter(cmd, “@outString”, DbType.String, 20);
4858.com 693       
db.AddReturnParameter(cmd, “@returnValue”, DbType.Int32);
4858.com 694
4858.com 695 using (DbDataReader reader
= db.ExecuteReader(cmd))
4858.com 6964858.com 697
4858.com 698{
4858.com 699           
dt.Load(reader);
4858.com 700        }
4858.com 701 string s =
db.GetParameter(cmd, “@outString”).Value as string;//out parameter
4858.com 702 int r =
Convert.ToInt32(db.GetParameter(cmd, “@returnValue”).Value);//return
value
4858.com 703

7)事务的使用.(项目中要求将基本的数据库操作组合成三个完整的事体流时,代码级的政工是必备的哦)

肆 、在DbHelper编写一些用于落到实处数据库相关操作的法门,那里就用到了DbProviderFactory类中的方法,以下措施仅供参考,具体请参见别的完整的DbHelp帮助类,

点击“执行”按钮,就会调用 btnSubmit_Click 方法(第③4到68行)。在该办法中:

7)事务的使用.(项目中须要将挑建邺的数据库操作组合成二个一体化的事务流时,代码级的事务是须要的啊)

4858.com 704    pubic void DoBusiness()
4858.com 7054858.com 706    4858.com 707{
4858.com 708        using (Trans t = new Trans())
4858.com 7094858.com 710        4858.com 711{
4858.com 712            try
4858.com 7134858.com 714            4858.com 715{
4858.com 716                D1(t);
4858.com 717                throw new Exception();//即使有那多少个,会回滚滴
4858.com 718                D2(t);
4858.com 719                t.Commit();
4858.com 720            }
4858.com 721            catch
4858.com 7224858.com 723            4858.com 724{
4858.com 725                t.RollBack();
4858.com 726            }
4858.com 727        }
4858.com 728    }
4858.com 729    public void D1(Trans t)
4858.com 7304858.com 731    4858.com 732{
4858.com 733        DbHelper db = new DbHelper();
4858.com 734        DbCommand cmd = db.GetStoredProcCommond(“t2_insert”);
4858.com 735        db.AddInParameter(cmd, “@timeticks”, DbType.Int64, DateTime.Now.Ticks);
4858.com 736        db.AddOutParameter(cmd, “@outString”, DbType.String, 20);
4858.com 737        db.AddReturnParameter(cmd, “@returnValue”, DbType.Int32);
4858.com 738
4858.com 739        if (t == null) db.ExecuteNonQuery(cmd);
4858.com 740        else db.ExecuteNonQuery(cmd,t);
4858.com 741
4858.com 742        string s = db.GetParameter(cmd, “@outString”).Value as string;//out parameter
4858.com 743        int r = Convert.ToInt32(db.GetParameter(cmd, “@returnValue”).Value);//return value
4858.com 744    }
4858.com 745    public void D2(Trans t)
4858.com 7464858.com 747    4858.com 748{
4858.com 749        DbHelper db = new DbHelper();
4858.com 750        DbCommand cmd = db.GetSqlStringCommond(“insert t1 (id)values(‘4858.com 751..’)”);        
4858.com 752        if (t == null) db.ExecuteNonQuery(cmd);
4858.com 753        else db.ExecuteNonQuery(cmd, t);
4858.com 754    }

DbHelper完整代码如下:

  • 利用 DbProviderFactories 类的静态方法 GetFactory 获得贰个DbProviderFactory (第60行)。
  • 行使 DbProviderFactory 类的 CreateConnection 方法创设叁个DbConnection (第陆1行)。
  • 使用 DbConnection 类的 Open 方法打开数据库连接(第⑥4行)。
  • 选用 DbConnection 类的 CreateCommand 方法创立3个 DbCommand
    (第⑥0行)。
  • 假设要执行的 SQL 语句不是 SELECT 语句,则调用 DbCommand 类的
    ExecuteNonQuery 方法执行该 SQL 语句(第陆4行)。
  • 要不,借使程序主界面中的“结构”复选框被选中,就调用
    RunQueryTableStruct 方法获得数据库中的表的组织(第肆5行)。
  • 要不,就调用 RunQueryTableData 方法获得数据库中的表的数目(第66行)。

4858.com 755    pubic void
DoBusiness()
4858.com 7564858.com 757
4858.com 758{
4858.com 759 using (Trans t = new
Trans())
4858.com 7604858.com 761
4858.com 762{
4858.com 763 try
4858.com 7644858.com 765
4858.com 766{
4858.com 767                D1(t);
4858.com 768 throw new
Exception();//若是有丰硕,会回滚滴
4858.com 769                D2(t);
4858.com 770               
t.Commit();
4858.com 771            }
4858.com 772 catch
4858.com 7734858.com 774
4858.com 775{
4858.com 776               
t.RollBack();
4858.com 777            }
4858.com 778        }
4858.com 779    }
4858.com 780 public void D1(Trans t)
4858.com 7814858.com 782
4858.com 783{
4858.com 784        DbHelper db = new
DbHelper();
4858.com 785        DbCommand cmd =
db.GetStoredProcCommond(“t2_insert”);
4858.com 786       
db.AddInParameter(cmd, “@timeticks”, DbType.Int64,
DateTime.Now.Ticks);
4858.com 787       
db.AddOutParameter(cmd, “@outString”, DbType.String, 20);
4858.com 788       
db.AddReturnParameter(cmd, “@returnValue”, DbType.Int32);
4858.com 789
4858.com 790 if (t == null)
db.ExecuteNonQuery(cmd);
4858.com 791 else
db.ExecuteNonQuery(cmd,t);
4858.com 792
4858.com 793 string s =
db.GetParameter(cmd, “@outString”).Value as string;//out parameter
4858.com 794 int r =
Convert.ToInt32(db.GetParameter(cmd, “@returnValue”).Value);//return
value
4858.com 795    }
4858.com 796 public void D2(Trans t)
4858.com 7974858.com 798
4858.com 799{
4858.com 800        DbHelper db = new
DbHelper();
4858.com 801        DbCommand cmd =
db.GetSqlStringCommond(“insert t1
(id)values(‘4858.com 802..’)”);       
4858.com 803 if (t == null)
db.ExecuteNonQuery(cmd);
4858.com 804 else
db.ExecuteNonQuery(cmd, t);
4858.com 805    }

上述大家好像从没点名数据库连接字符串,大家只要看下DbHelper的代码,就驾驭要动用它必须在config中布局八个参数,如下:

//帮助类的基类(抽象类)
    public abstract class DbHelper
    {
        public DbHelper(string connectionString)
        {
            ConnectionString = connectionString;
        }

        public abstract DbProviderFactory DbProviderFactory {  get; } 

        public string ConnectionString { get; }

        //以下实现的帮助类方法,仅供该例子使用,具体请参照其他完整的DbHelp帮助类
        private void ThrowExceptionIfLengthNotEqual(string[] sqls, params DbParameter[][] parameters)
        {
            if (parameters.GetLength(0) != 0 && sqls.Length != parameters.GetLength(0)) throw new ArgumentException($"一维数组{nameof(sqls)}的长度与二维数组{nameof(parameters)}长度的第一维长度不一致");
        }

        private T[] Execute<T>(string[] sqls, CommandType commandType = CommandType.Text, ExecuteMode executeMode = ExecuteMode.NonQuery, params DbParameter[][] parameters)
        {
            ThrowExceptionIfLengthNotEqual(sqls, parameters);
            if(executeMode == ExecuteMode.NonQuery && typeof(T) != typeof(int)) throw new InvalidCastException("使用NonQuery模式时,必须将类型T指定为int");
            using (DbConnection connection = DbProviderFactory.CreateConnection())
            using (DbCommand command = DbProviderFactory.CreateCommand())
            {
                connection.ConnectionString = ConnectionString;
                connection.Open();
                command.Connection = connection;
                command.CommandType = commandType;
                DbTransaction transaction = connection.BeginTransaction();
                command.Transaction = transaction;
                try
                {
                    List<T> resultList = new List<T>();
                    for (int i = 0; i < sqls.Length; i++)
                    {
                        command.CommandText = sqls[i];
                        if (parameters.GetLength(0) != 0)
                        {
                            command.Parameters.Clear();
                            command.Parameters.AddRange(parameters[i]);
                        }
                        object result = null;
                        switch (executeMode)
                        {
                            case ExecuteMode.NonQuery:
                                result = command.ExecuteNonQuery(); break;
                            case ExecuteMode.Scalar:
                                result = command.ExecuteScalar(); break;
                            default: throw new NotImplementedException();
                        }
                        resultList.Add((T)Convert.ChangeType(result, typeof(T)));
                    }
                    transaction.Commit();
                    return resultList.ToArray();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        public int ExecuteNonQuery(string sql, params DbParameter[] parameter) => ExecuteNonQuery(new string[] { sql }, new DbParameter[][] { parameter })[0];

        public int[] ExecuteNonQuery(string[] sqls, params DbParameter[][] parameters)=> Execute<int>(sqls, CommandType.Text, ExecuteMode.NonQuery,parameters);

        public int ExecuteNonQueryWithProc(string sql, params DbParameter[] parameter) => ExecuteNonQueryWithProc(new string[] { sql },  new DbParameter[][] { parameter })[0];

        public int[] ExecuteNonQueryWithProc(string[] sqls, params DbParameter[][] parameters) => Execute<int>(sqls, CommandType.StoredProcedure, ExecuteMode.NonQuery, parameters);

        public T ExecuteScalar<T>(string sql, params DbParameter[] parameter) => ExecuteNonQuery<T>(new string[] { sql }, new DbParameter[][] { parameter })[0];

        public T[] ExecuteNonQuery<T>(string[] sqls, params DbParameter[][] parameters) => Execute<T>(sqls, CommandType.Text,ExecuteMode.Scalar, parameters);

        public T ExecuteScalarWithProc<T>(string sql, params DbParameter[] parameter) => ExecuteNonQuery<T>(new string[] { sql }, new DbParameter[][] { parameter })[0];

        public T[] ExecuteNonQueryWithProc<T>(string[] sqls, params DbParameter[][] parameters) => Execute<T>(sqls, CommandType.StoredProcedure, ExecuteMode.Scalar, parameters);

        enum ExecuteMode
        {
            NonQuery,Scalar
        }

        private DataTable[] Fill(string[] selectSqls, CommandType commandType = CommandType.Text, params DbParameter[][] parameters)
        {
            ThrowExceptionIfLengthNotEqual(selectSqls, parameters);
            using (DbConnection connection = DbProviderFactory.CreateConnection())
            using (DbDataAdapter adapter = DbProviderFactory.CreateDataAdapter())
            using (DbCommand command = DbProviderFactory.CreateCommand())
            {
                connection.ConnectionString = ConnectionString;
                connection.Open();
                command.Connection = connection;
                command.CommandType = commandType;
                adapter.SelectCommand = command;
                List<DataTable> resultList = new List<DataTable>();
                for (int i = 0; i < selectSqls.Length; i++)
                {
                    command.CommandText = selectSqls[i];
                    if (parameters.GetLength(0) != 0)
                    {
                        command.Parameters.Clear();
                        command.Parameters.AddRange(parameters[i]);
                    }
                    DataTable table = new DataTable();
                    adapter.Fill(table);
                    resultList.Add(table);
                }
                return resultList.ToArray();
            }
        }

        public DataTable Fill(string selectSql, params DbParameter[] parameter) => Fill(new string[] { selectSql }, new DbParameter[][] { parameter })[0];

        public DataTable[] Fill(string[] selectSqls, params DbParameter[][] parameters) => Fill(selectSqls, CommandType.Text, parameters);

        public DataTable FillWithProc(string selectSql, params DbParameter[] parameter) => FillWithProc(new string[] { selectSql }, new DbParameter[][] { parameter })[0];

        public DataTable[] FillWithProc(string[] selectSqls, params DbParameter[][] parameters) => Fill(selectSqls, CommandType.StoredProcedure, parameters);
    }

第⑩0到76行的 RunQueryTableStruct 方法运用 DbCommand 类的 ExecuteReader
方法(使用 CommandBehavior.KeyInfo 参数)获得三个 DbDataReader
(第拾2行),然后调用 DbData里德r 类的 GetSchemaTable
方法来赢得数据库中的表的各列的元数据(第104行)。

上述大家好像从没点名数据库连接字符串,我们固然看下DbHelper的代码,就驾驭要利用它必须在config中安顿四个参数,如下:

4858.com 806    <appSettings>
4858.com 807        <add key=”DbHelperProvider” value=”System.Data.SqlClient”/>
4858.com 808        <add key=”DbHelperConnectionString” value=”Data Source=(local);Initial Catalog=DbHelperTest;Persist Security Info=True;User ID=sa;Password=sa”/>
4858.com 809    </appSettings>

叁 、落成具体的数据库帮忙类

第七8到85行的 RunQueryTableData 方法应用 DbProviderFactory 类的
CreateDataAdapter 方法创制二个 DbDataAdapter (第十0行),然后选取DbDataAdapter 类的 Fill 方法来填充 DataSet (第⑩3行),最后回来该 DataSet
的 Tables 属性中的第一个 DataTabe 的 DefaultView (第捌4行)。

4858.com 810 <appSettings>
4858.com 811 <add
key=”DbHelperProvider” value=”System.Data.SqlClient”/>
4858.com 812 <add
key=”DbHelperConnectionString” value=”Data Source=(local);Initial
Catalog=DbHelperTest;Persist Security Info=True;User
ID=sa;Password=sa”/>
4858.com 813 </appSettings>

实在,DbHelper供给的单独是四个字符串,你能够本人修改,作成加密什么的…

壹 、达成Sql
Server的扶持类,具体方法:只要重写DbHelper类的DbProviderFactory属性并在构造函数为其赋值即可,其余的数据库帮忙类亦是如此,

该程序中的静态类 Pub 类提供一些推推搡搡的静态方法:

其实,DbHelper必要的无非是七个字符串,你能够团结修改,作成加密什么的…
好了,就好像此,DbHelper的代码是分外不难和晶莹剔透的,只是在ado.net上做了少数小包装,改变了一下施用它的程序员的编码格局,去除掉一些比较”
物理级”的编制程序概念,如connection的open和close之类的,使程序员更注意于业务逻辑代码的编排,少死掉点脑细胞,此外,统一了数额操作层的数目操作代码的风格和格式,维护起来很有益的撒~~~
另:以上代码大家能够随心所欲行使,
不供给给自身版权费的啊,嘿嘿.假诺大家发现有怎么着BUG,只怕有更好的多少操作类的贯彻方式,请联系本身哦.

好了,就这么,DbHelper的代码是格外简单和透亮的,只是在ado.net上做了一些小包装,改变了弹指间接选举用它的程序员的编码格局,去除掉一些相比较”物理级”的编制程序概念,如connection的open和close之类的,使程序员更注意于事情逻辑代码的编辑,少死掉点脑细胞,其它,统一了数额操作层的多寡操作代码的品格和格式,维护起来很方便的撒~~~

代码如下:

using System;
using System.Text;
using System.Windows.Forms;
using System.Reflection;

namespace Skyiv.Ben.DbTools
{
  static class Pub
  {
    public static string GetMessage(Exception ex)
    {
      var sb = new StringBuilder();
      for (sb.Append("错误: "); ex != null; ex = ex.InnerException)
      {
        sb.AppendFormat("[{0}]: ", ex.GetType());
        sb.AppendLine(ex.Message);
      }
      return sb.ToString();
    }

    public static void AppendLine(this TextBoxBase tbx, string fmt, params object[] args)
    {
      tbx.AppendText(string.Format(fmt, args) + Environment.NewLine);
    }

    public static Version GetClientVersion(string name)
    {
      foreach (var a in AppDomain.CurrentDomain.GetAssemblies()) if (name == a.GetName().Name) return a.GetName().Version;
      return null;
    }
  }
}

另:以上代码大家能够肆意动用,
不必要给自家版权费的啦,嘿嘿.借使大家发现有哪些BUG,可能有更好的数目操作类的落到实处格局,请联系笔者哦.

    //用于Sql Server的帮助类
    public class SqlClientHelper : DbHelper
    {
        public SqlClientHelper(string connectionString) : base(connectionString)
        {
            this.DbProviderFactory = SqlClientFactory.Instance;
        }
        public override DbProviderFactory DbProviderFactory { get; }
    }

末段,完整的源程序可以到
页面下载。

贰 、参照以上,完毕SQLite辅助类如下:

也能够动用 hg clone 命令下载。

    //用于SQLite的帮助类
    public class SQLiteHelper : DbHelper
    {
        public SQLiteHelper(string connectionString) : base(connectionString)
        {
            DbProviderFactory = SQLiteFactory.Instance;
        }
        public override DbProviderFactory DbProviderFactory  { get; }
    }

关于 hg ,请参阅 Mercurial
备忘录。

③ 、别的数据库的助手类,正如以上所说的,只要重写DbHelper类的DbProviderFactory属性并在构造函数为其赋值即可。

四 、示例演示

应用前,必须引用了System.Data.SQLite,具体请参见一下作品:

使用C#始建SQLite控制台应用程序

编写的客户端代码,如下:

    class Program
    {
        //客户端调用
        static void Main(string[] args)
        {
            string fileName = "Test.db";
            if (File.Exists(fileName)) File.Delete(fileName);
            SQLiteConnection.CreateFile(fileName);
            SQLiteHelper helper = new SQLiteHelper($"Data Source = {fileName}");
            helper.ExecuteNonQuery("CREATE TABLE IF NOT EXISTS Info(ID integer PRIMARY KEY AUTOINCREMENT, Guid text)");
            List<string> sqlList = new List<string>();
            for (int i = 0; i < 1000; i++)
            {
                sqlList.Add($"INSERT INTO Info VALUES(null,'{Guid.NewGuid()}')");
            }
            helper.ExecuteNonQuery(sqlList.ToArray());
            DataTable table = helper.Fill("SELECT * FROM Info");
            table.Rows.Cast<DataRow>().ToList().ForEach(x => Console.WriteLine($"{x[0]}\t{x[1]}"));
            Console.ReadKey();
        }
    }

输出的结果如下:

4858.com 814

⑤ 、完整代码如下:

4858.com 8154858.com 816

using System;
using System.Linq;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.IO;

namespace ConsoleApp
{
    class Program
    {
        //客户端调用
        static void Main(string[] args)
        {
            string fileName = "Test.db";
            if (File.Exists(fileName)) File.Delete(fileName);
            SQLiteConnection.CreateFile(fileName);
            SQLiteHelper helper = new SQLiteHelper($"Data Source = {fileName}");
            helper.ExecuteNonQuery("CREATE TABLE IF NOT EXISTS Info(ID integer PRIMARY KEY AUTOINCREMENT, Guid text)");
            List<string> sqlList = new List<string>();
            for (int i = 0; i < 1000; i++)
            {
                sqlList.Add($"INSERT INTO Info VALUES(null,'{Guid.NewGuid()}')");
            }
            helper.ExecuteNonQuery(sqlList.ToArray());
            DataTable table = helper.Fill("SELECT * FROM Info");
            table.Rows.Cast<DataRow>().ToList().ForEach(x => Console.WriteLine($"{x[0]}\t{x[1]}"));
            Console.ReadKey();
        }
    }

    //用于Sql Server的帮助类
    public class SqlClientHelper : DbHelper
    {
        public SqlClientHelper(string connectionString) : base(connectionString)
        {
            this.DbProviderFactory = SqlClientFactory.Instance;
        }
        public override DbProviderFactory DbProviderFactory { get; }
    }

    //用于SQLite的帮助类
    public class SQLiteHelper : DbHelper
    {
        public SQLiteHelper(string connectionString) : base(connectionString)
        {
            DbProviderFactory = SQLiteFactory.Instance;
        }
        public override DbProviderFactory DbProviderFactory  { get; }
    }

    //--------------------------------------------------------------------------------
    //其他数据库的帮助类,只要重写DbHelper类的DbProviderFactory属性并在构造函数为其赋值即可
    //--------------------------------------------------------------------------------

    //帮助类的基类(抽象类)
    public abstract class DbHelper
    {
        public DbHelper(string connectionString)
        {
            ConnectionString = connectionString;
        }

        public abstract DbProviderFactory DbProviderFactory {  get; } 

        public string ConnectionString { get; }

        //以下实现的帮助类方法,仅供该例子使用,具体请参照其他完整的DbHelp帮助类
        private void ThrowExceptionIfLengthNotEqual(string[] sqls, params DbParameter[][] parameters)
        {
            if (parameters.GetLength(0) != 0 && sqls.Length != parameters.GetLength(0)) throw new ArgumentException($"一维数组{nameof(sqls)}的长度与二维数组{nameof(parameters)}长度的第一维长度不一致");
        }

        private T[] Execute<T>(string[] sqls, CommandType commandType = CommandType.Text, ExecuteMode executeMode = ExecuteMode.NonQuery, params DbParameter[][] parameters)
        {
            ThrowExceptionIfLengthNotEqual(sqls, parameters);
            if(executeMode == ExecuteMode.NonQuery && typeof(T) != typeof(int)) throw new InvalidCastException("使用NonQuery模式时,必须将类型T指定为int");
            using (DbConnection connection = DbProviderFactory.CreateConnection())
            using (DbCommand command = DbProviderFactory.CreateCommand())
            {
                connection.ConnectionString = ConnectionString;
                connection.Open();
                command.Connection = connection;
                command.CommandType = commandType;
                DbTransaction transaction = connection.BeginTransaction();
                command.Transaction = transaction;
                try
                {
                    List<T> resultList = new List<T>();
                    for (int i = 0; i < sqls.Length; i++)
                    {
                        command.CommandText = sqls[i];
                        if (parameters.GetLength(0) != 0)
                        {
                            command.Parameters.Clear();
                            command.Parameters.AddRange(parameters[i]);
                        }
                        object result = null;
                        switch (executeMode)
                        {
                            case ExecuteMode.NonQuery:
                                result = command.ExecuteNonQuery(); break;
                            case ExecuteMode.Scalar:
                                result = command.ExecuteScalar(); break;
                            default: throw new NotImplementedException();
                        }
                        resultList.Add((T)Convert.ChangeType(result, typeof(T)));
                    }
                    transaction.Commit();
                    return resultList.ToArray();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        public int ExecuteNonQuery(string sql, params DbParameter[] parameter) => ExecuteNonQuery(new string[] { sql }, new DbParameter[][] { parameter })[0];

        public int[] ExecuteNonQuery(string[] sqls, params DbParameter[][] parameters)=> Execute<int>(sqls, CommandType.Text, ExecuteMode.NonQuery,parameters);

        public int ExecuteNonQueryWithProc(string sql, params DbParameter[] parameter) => ExecuteNonQueryWithProc(new string[] { sql },  new DbParameter[][] { parameter })[0];

        public int[] ExecuteNonQueryWithProc(string[] sqls, params DbParameter[][] parameters) => Execute<int>(sqls, CommandType.StoredProcedure, ExecuteMode.NonQuery, parameters);

        public T ExecuteScalar<T>(string sql, params DbParameter[] parameter) => ExecuteNonQuery<T>(new string[] { sql }, new DbParameter[][] { parameter })[0];

        public T[] ExecuteNonQuery<T>(string[] sqls, params DbParameter[][] parameters) => Execute<T>(sqls, CommandType.Text,ExecuteMode.Scalar, parameters);

        public T ExecuteScalarWithProc<T>(string sql, params DbParameter[] parameter) => ExecuteNonQuery<T>(new string[] { sql }, new DbParameter[][] { parameter })[0];

        public T[] ExecuteNonQueryWithProc<T>(string[] sqls, params DbParameter[][] parameters) => Execute<T>(sqls, CommandType.StoredProcedure, ExecuteMode.Scalar, parameters);

        enum ExecuteMode
        {
            NonQuery,Scalar
        }

        private DataTable[] Fill(string[] selectSqls, CommandType commandType = CommandType.Text, params DbParameter[][] parameters)
        {
            ThrowExceptionIfLengthNotEqual(selectSqls, parameters);
            using (DbConnection connection = DbProviderFactory.CreateConnection())
            using (DbDataAdapter adapter = DbProviderFactory.CreateDataAdapter())
            using (DbCommand command = DbProviderFactory.CreateCommand())
            {
                connection.ConnectionString = ConnectionString;
                connection.Open();
                command.Connection = connection;
                command.CommandType = commandType;
                adapter.SelectCommand = command;
                List<DataTable> resultList = new List<DataTable>();
                for (int i = 0; i < selectSqls.Length; i++)
                {
                    command.CommandText = selectSqls[i];
                    if (parameters.GetLength(0) != 0)
                    {
                        command.Parameters.Clear();
                        command.Parameters.AddRange(parameters[i]);
                    }
                    DataTable table = new DataTable();
                    adapter.Fill(table);
                    resultList.Add(table);
                }
                return resultList.ToArray();
            }
        }

        public DataTable Fill(string selectSql, params DbParameter[] parameter) => Fill(new string[] { selectSql }, new DbParameter[][] { parameter })[0];

        public DataTable[] Fill(string[] selectSqls, params DbParameter[][] parameters) => Fill(selectSqls, CommandType.Text, parameters);

        public DataTable FillWithProc(string selectSql, params DbParameter[] parameter) => FillWithProc(new string[] { selectSql }, new DbParameter[][] { parameter })[0];

        public DataTable[] FillWithProc(string[] selectSqls, params DbParameter[][] parameters) => Fill(selectSqls, CommandType.StoredProcedure, parameters);
    }
}

View Code

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图
Copyright @ 2010-2019 美高梅手机版4858 版权所有