- 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语句
- DbHelperdb=newDbHelper();
- DbCommandcmd=db.GetSqlStringCommond("insertt1(id)values(‘?haha‘?)");
- db.ExecuteNonQuery(cmd);
3、执行存储过程
- DbHelperdb=newDbHelper();
- DbCommandcmd=db.GetStoredProcCommond("t1_insert");
- db.AddInParameter(cmd,"@id",DbType.String,"heihei");
- db.ExecuteNonQuery(cmd);
4、返回DataSet
- DbHelperdb=newDbHelper();
- DbCommandcmd=db.GetSqlStringCommond("select*fromt1");
- DataSetds=db.ExecuteDataSet(cmd);
5、返回DataTable
- DbHelperdb=newDbHelper();
- DbCommandcmd=db.GetSqlStringCommond("t1_findall");
- DataTabledt=db.ExecuteDataTable(cmd);
6、输入参数/输出参数/返回值的使用(比较重要哦)
- 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使用
- 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中配置两个参数,如下:
- 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);
- }