微软通用的DBHelper

最新推荐文章于 2013-10-10 09:11:36 发布

最新推荐文章于 2013-10-10 09:11:36 发布 · 150 阅读

文章标签：

本文介绍了一个用于简化数据库操作的助手类库，包括执行SQL语句、存储过程调用及参数处理等功能，并提供了使用示例，如事务处理、DataReader应用等。

 
   view plain 
  
 usingSystem; 
  
 usingSystem.Configuration; 
  
 usingSystem.Data; 
  
 usingSystem.Data.Common; 
  
 usingSystem.Data.OleDb; 
  
 usingSystem.Data.SqlClient; 
  
 namespaceLelog.Data 
  
 { 
  
 ///<summary> 
  
 ///数据库助手类 
  
 ///</summary> 
  
 publicclassDbHelper 
  
 { 
  
 privatestaticstringdbProviderName=ConfigurationSettings.AppSettings["DbProviderName"]; 
  
 privatestaticstringdbConnectionString=ConfigurationManager.AppSettings["TaxInvoiceConn"]; 
  
 privateDbConnectionconnection; 
  
 publicDbHelper() 
  
 { 
  
 this.connection=CreateConnection(DbHelper.dbConnectionString); 
  
 } 
  
 publicDbHelper(stringconnectionString) 
  
 { 
  
 this.connection=CreateConnection(connectionString); 
  
 } 
  
 publicstaticDbConnectionCreateConnection() 
  
 { 
  
 DbProviderFactorydbfactory=DbProviderFactories.GetFactory(DbHelper.dbProviderName); 
  
 DbConnectiondbconn=dbfactory.CreateConnection(); 
  
 dbconn.ConnectionString=DbHelper.dbConnectionString; 
  
 returndbconn; 
  
 } 
  
 publicstaticDbConnectionCreateConnection(stringconnectionString) 
  
 { 
  
 DbProviderFactorydbfactory=DbProviderFactories.GetFactory(DbHelper.dbProviderName); 
  
 DbConnectiondbconn=dbfactory.CreateConnection(); 
  
 dbconn.ConnectionString=connectionString; 
  
 returndbconn; 
  
 } 
  
 publicDbCommandGetStoredProcCommond(stringstoredProcedure) 
  
 { 
  
 DbCommanddbCommand=connection.CreateCommand(); 
  
 dbCommand.CommandText=storedProcedure; 
  
 dbCommand.CommandType=CommandType.StoredProcedure; 
  
 returndbCommand; 
  
 } 
  
 publicDbCommandGetSqlStringCommond(stringsqlQuery) 
  
 { 
  
 DbCommanddbCommand=connection.CreateCommand(); 
  
 dbCommand.CommandText=sqlQuery; 
  
 dbCommand.CommandType=CommandType.Text; 
  
 returndbCommand; 
  
 } 
  
 #region增加参数 
  
 publicvoidAddParameterCollection(DbCommandcmd,DbParameterCollectiondbParameterCollection) 
  
 { 
  
 foreach(DbParameterdbParameterindbParameterCollection) 
  
 { 
  
 cmd.Parameters.Add(dbParameter); 
  
 } 
  
 } 
  
 publicvoidAddOutParameter(DbCommandcmd,stringparameterName,DbTypedbType,intsize) 
  
 { 
  
 DbParameterdbParameter=cmd.CreateParameter(); 
  
 dbParameter.DbType=dbType; 
  
 dbParameter.ParameterName=parameterName; 
  
 dbParameter.Size=size; 
  
 dbParameter.Direction=ParameterDirection.Output; 
  
 cmd.Parameters.Add(dbParameter); 
  
 } 
  
 publicvoidAddInParameter(DbCommandcmd,stringparameterName,DbTypedbType,objectvalue) 
  
 { 
  
 DbParameterdbParameter=cmd.CreateParameter(); 
  
 dbParameter.DbType=dbType; 
  
 dbParameter.ParameterName=parameterName; 
  
 dbParameter.Value=value; 
  
 dbParameter.Direction=ParameterDirection.Input; 
  
 cmd.Parameters.Add(dbParameter); 
  
 } 
  
 publicvoidAddReturnParameter(DbCommandcmd,stringparameterName,DbTypedbType) 
  
 { 
  
 DbParameterdbParameter=cmd.CreateParameter(); 
  
 dbParameter.DbType=dbType; 
  
 dbParameter.ParameterName=parameterName; 
  
 dbParameter.Direction=ParameterDirection.ReturnValue; 
  
 cmd.Parameters.Add(dbParameter); 
  
 } 
  
 publicDbParameterGetParameter(DbCommandcmd,stringparameterName) 
  
 { 
  
 returncmd.Parameters[parameterName]; 
  
 } 
  
 #endregion 
  
 #region执行 
  
 publicDataSetExecuteDataSet(DbCommandcmd) 
  
 { 
  
 DbProviderFactorydbfactory=DbProviderFactories.GetFactory(DbHelper.dbProviderName); 
  
 DbDataAdapterdbDataAdapter=dbfactory.CreateDataAdapter(); 
  
 dbDataAdapter.SelectCommand=cmd; 
  
 DataSetds=newDataSet(); 
  
 dbDataAdapter.Fill(ds); 
  
 returnds; 
  
 } 
  
 publicDataTableExecuteDataTable(DbCommandcmd) 
  
 { 
  
 DbProviderFactorydbfactory=DbProviderFactories.GetFactory(DbHelper.dbProviderName); 
  
 DbDataAdapterdbDataAdapter=dbfactory.CreateDataAdapter(); 
  
 dbDataAdapter.SelectCommand=cmd; 
  
 DataTabledataTable=newDataTable(); 
  
 dbDataAdapter.Fill(dataTable); 
  
 returndataTable; 
  
 } 
  
 publicDbDataReaderExecuteReader(DbCommandcmd) 
  
 { 
  
 cmd.Connection.Open(); 
  
 DbDataReaderreader=cmd.ExecuteReader(CommandBehavior.CloseConnection); 
  
 returnreader; 
  
 } 
  
 publicintExecuteNonQuery(DbCommandcmd) 
  
 { 
  
 cmd.Connection.Open(); 
  
 intret=cmd.ExecuteNonQuery(); 
  
 cmd.Connection.Close(); 
  
 returnret; 
  
 } 
  
 publicobjectExecuteScalar(DbCommandcmd) 
  
 { 
  
 cmd.Connection.Open(); 
  
 objectret=cmd.ExecuteScalar(); 
  
 cmd.Connection.Close(); 
  
 returnret; 
  
 } 
  
 #endregion 
  
 #region执行事务 
  
 publicDataSetExecuteDataSet(DbCommandcmd,Transt) 
  
 { 
  
 cmd.Connection=t.DbConnection; 
  
 cmd.Transaction=t.DbTrans; 
  
 DbProviderFactorydbfactory=DbProviderFactories.GetFactory(DbHelper.dbProviderName); 
  
 DbDataAdapterdbDataAdapter=dbfactory.CreateDataAdapter(); 
  
 dbDataAdapter.SelectCommand=cmd; 
  
 DataSetds=newDataSet(); 
  
 dbDataAdapter.Fill(ds); 
  
 returnds; 
  
 } 
  
 publicDataTableExecuteDataTable(DbCommandcmd,Transt) 
  
 { 
  
 cmd.Connection=t.DbConnection; 
  
 cmd.Transaction=t.DbTrans; 
  
 DbProviderFactorydbfactory=DbProviderFactories.GetFactory(DbHelper.dbProviderName); 
  
 DbDataAdapterdbDataAdapter=dbfactory.CreateDataAdapter(); 
  
 dbDataAdapter.SelectCommand=cmd; 
  
 DataTabledataTable=newDataTable(); 
  
 dbDataAdapter.Fill(dataTable); 
  
 returndataTable; 
  
 } 
  
 publicDbDataReaderExecuteReader(DbCommandcmd,Transt) 
  
 { 
  
 cmd.Connection.Close(); 
  
 cmd.Connection=t.DbConnection; 
  
 cmd.Transaction=t.DbTrans; 
  
 DbDataReaderreader=cmd.ExecuteReader(); 
  
 DataTabledt=newDataTable(); 
  
 returnreader; 
  
 } 
  
 publicintExecuteNonQuery(DbCommandcmd,Transt) 
  
 { 
  
 cmd.Connection.Close(); 
  
 cmd.Connection=t.DbConnection; 
  
 cmd.Transaction=t.DbTrans; 
  
 intret=cmd.ExecuteNonQuery(); 
  
 returnret; 
  
 } 
  
 publicobjectExecuteScalar(DbCommandcmd,Transt) 
  
 { 
  
 cmd.Connection.Close(); 
  
 cmd.Connection=t.DbConnection; 
  
 cmd.Transaction=t.DbTrans; 
  
 objectret=cmd.ExecuteScalar(); 
  
 returnret; 
  
 } 
  
 #endregion 
  
 } 
  
 publicclassTrans:IDisposable 
  
 { 
  
 privateDbConnectionconn; 
  
 privateDbTransactiondbTrans; 
  
 publicDbConnectionDbConnection 
  
 { 
  
 get{returnthis.conn;} 
  
 } 
  
 publicDbTransactionDbTrans 
  
 { 
  
 get{returnthis.dbTrans;} 
  
 } 
  
 publicTrans() 
  
 { 
  
 conn=DbHelper.CreateConnection(); 
  
 conn.Open(); 
  
 dbTrans=conn.BeginTransaction(); 
  
 } 
  
 publicTrans(stringconnectionString) 
  
 { 
  
 conn=DbHelper.CreateConnection(connectionString); 
  
 conn.Open(); 
  
 dbTrans=conn.BeginTransaction(); 
  
 } 
  
 publicvoidCommit() 
  
 { 
  
 dbTrans.Commit(); 
  
 this.Colse(); 
  
 } 
  
 publicvoidRollBack() 
  
 { 
  
 dbTrans.Rollback(); 
  
 this.Colse(); 
  
 } 
  
 publicvoidDispose() 
  
 { 
  
 this.Colse(); 
  
 } 
  
 publicvoidColse() 
  
 { 
  
 if(conn.State==System.Data.ConnectionState.Open) 
  
 { 
  
 conn.Close(); 
  
 } 
  
 } 
  
 } 
  
 }

2、直接执行sql语句

 
   view plain 
  
 DbHelperdb=newDbHelper(); 
  
 DbCommandcmd=db.GetSqlStringCommond("insertt1(id)values(‘?haha‘?)"); 
  
 db.ExecuteNonQuery(cmd);

3、执行存储过程

 
   view plain 
  
 DbHelperdb=newDbHelper(); 
  
 DbCommandcmd=db.GetStoredProcCommond("t1_insert"); 
  
 db.AddInParameter(cmd,"@id",DbType.String,"heihei"); 
  
 db.ExecuteNonQuery(cmd);

4、返回DataSet

 
   view plain 
  
 DbHelperdb=newDbHelper(); 
  
 DbCommandcmd=db.GetSqlStringCommond("select*fromt1"); 
  
 DataSetds=db.ExecuteDataSet(cmd);

5、返回DataTable

 
   view plain 
  
 DbHelperdb=newDbHelper(); 
  
 DbCommandcmd=db.GetSqlStringCommond("t1_findall"); 
  
 DataTabledt=db.ExecuteDataTable(cmd);

6、输入参数/输出参数/返回值的使用(比较重要哦)

 
   view plain 
  
 DbHelperdb=newDbHelper(); 
  
 DbCommandcmd=db.GetStoredProcCommond("t2_insert"); 
  
 db.AddInParameter(cmd,"@timeticks",DbType.Int64,DateTime.Now.Ticks); 
  
 db.AddOutParameter(cmd,"@outString",DbType.String,20); 
  
 db.AddReturnParameter(cmd,"@returnValue",DbType.Int32); 
  
 db.ExecuteNonQuery(cmd); 
  
 strings=db.GetParameter(cmd,"@outString").Valueasstring; 
  
 intr=Convert.ToInt32(db.GetParameter(cmd,"@returnValue").Value);

7、DataReader使用

 
   view plain 
  
 DbHelperdb=newDbHelper(); 
  
 DbCommandcmd=db.GetStoredProcCommond("t2_insert"); 
  
 db.AddInParameter(cmd,"@timeticks",DbType.Int64,DateTime.Now.Ticks); 
  
 db.AddOutParameter(cmd,"@outString",DbType.String,20); 
  
 db.AddReturnParameter(cmd,"@returnValue",DbType.Int32); 
  
 using(DbDataReaderreader=db.ExecuteReader(cmd)) 
  
 { 
  
 dt.Load(reader); 
  
 } 
  
 strings=db.GetParameter(cmd,"@outString").Valueasstring; 
  
 intr=Convert.ToInt32(db.GetParameter(cmd,"@returnValue").Value);

8、事务的使用.(项目中需要将基本的数据库操作组合成一个完整的业务流时,代码级的事务是必不可少的哦)

以上我们好像没有指定数据库连接字符串,大家如果看下DbHelper的代码,就知道要使用它必须在config中配置两个参数,如下:

 
   view plain 
  
 pubicvoidDoBusiness() 
  
 { 
  
 using(Transt=newTrans()) 
  
 { 
  
 try 
  
 { 
  
 D1(t); 
  
 thrownewException(); 
  
 D2(t); 
  
 t.Commit(); 
  
 } 
  
 catch 
  
 { 
  
 t.RollBack(); 
  
 } 
  
 } 
  
 } 
  
 publicvoidD1(Transt) 
  
 { 
  
 DbHelperdb=newDbHelper(); 
  
 DbCommandcmd=db.GetStoredProcCommond("table_insert"); 
  
 db.AddInParameter(cmd,"@timeticks",DbType.Int64,DateTime.Now.Ticks); 
  
 db.AddOutParameter(cmd,"@outString",DbType.String,20); 
  
 db.AddReturnParameter(cmd,"@returnValue",DbType.Int32); 
  
 if(t==null)db.ExecuteNonQuery(cmd); 
  
 elsedb.ExecuteNonQuery(cmd,t); 
  
 strings=db.GetParameter(cmd,"@outString").Valueasstring; 
  
 intr=Convert.ToInt32(db.GetParameter(cmd,"@returnValue").Value); 
  
 } 
  
 publicvoidD2(Transt) 
  
 { 
  
 DbHelperdb=newDbHelper(); 
  
 DbCommandcmd=db.GetSqlStringCommond("inserttable(id)values(@id)"); 
  
 if(t==null)db.ExecuteNonQuery(cmd); 
  
 elsedb.ExecuteNonQuery(cmd,t); 
  
 }