commandinfo mysql dbhelper_通用数据库操作辅助类DbHelper

使用方式

DbHelper db;

OpenFileDialog ofd=newOpenFileDialog();

ofd.Filter="SQLite数据文件(*.db3;*.db;*.sqlite)|*.db3;*.db;*.sqlite";if(ofd.ShowDialog()==DialogResult.OK)

{

txtDataSource.Text=ofd.FileName;

db=newDbHelper("data source="+txtDataSource.Text, DbProvider.Sqlite);

}

db.ReExNum(txtSql.Text);

接口usingSystem.Data;usingSystem.Collections.Generic;usingSystem.Data.Common;//该类用于对数据库进行操作///Design by 火地晋///namespaceSystem.Data

{publicinterfaceIDbObject

{//定义一个DataReader的列表，已备检索///ListDataReaderList {get;set; }/DbConnection Connection {get;set; }stringConnectionString {get;set; }

DbProvider DbProviderType {get;set; }//返回执行操作成功的数目，使用注意，如果是存储过程，必须在存储过程后加上：select @@ROWCOUNT//存储过程名///参数组///intReExNum(stringstrSql,paramsDbParameter[] parameters);//返回结果的存储过程//任何SQL语句///参数值///DbDataReader ReSelectdr(stringstrSql,paramsDbParameter[] parameters);//返回dateSet///DataSet ReSelectds(stringstrSql,stringtableName,paramsDbParameter[] parameters);

DataTable ReSelectdtb(stringstrSql,paramsDbParameter[] parameters);//通過存儲過程及自定義參數組查詢返回SqlDataAdapter對象///DbDataAdapter ReSelectdat(stringstrSql,paramsDbParameter[] parameters);voidExSQL(stringstrSql,paramsDbParameter[] parameters);//執行SQL查詢語句,返回記錄條數//Select語句(在select语句中,使用Count(*)函数)///返回查詢到之記錄條數intReSelectNum(stringstrSql,paramsDbParameter[] parameters);//使用SqlDataAdapter返回指定范围的数据//存储过程名///参数名///起始行///记录数///表名///DataSet ReSelectds(stringstrSql, DbParameter[] parameters,intstart,intmaxRecord,stringtableName);//返回执行操作成功的数目，不关闭连接//执行的查询语句或存储过程///参数组///intReExNumNoClose(stringstrSql,paramsDbParameter[] parameters);//返回执行操作成功的数目，不关闭连接，并返回出现的错误信息。//存储过程名///参数组///intReExNumNoClose(stringstrSql,outstringerror,paramsDbParameter[] parameters);//返回执行操作成功的数目，并返回发生的错误信息//存储过程名///参数组///intReExNum(stringstrSql,outstringerror,paramsDbParameter[] parameters);//返回执行操作结果的信息，如果返回为空则表示没错误，否则返回错误的信息。//存储过程名///参数组///stringReExStr(stringstrSql,paramsDbParameter[] parameters);//如果数据库连接已关闭,则打开//boolOpenConnection();//关闭数据库连接///voidCloseConnection();

}publicenumDbProvider

{

Sql=0,

Sqlite=1,

OleDb=2,

Oracle=3,

MySql=4}

}

类usingSystem;usingSystem.Collections.Generic;usingSystem.Text;usingSystem.Data.Common;namespaceSystem.Data

{publicclassDbHelper : IDbObject

{#region属性publicListDataReaderList {get;set; }publicDbConnection Connection {get;set; }publicstringConnectionString {get;set; }publicDbProvider DbProviderType {get;set; }privateDbProviderFactory dbFactory;#endregion#region构造函数publicDbHelper()

{

DbHelperInstance(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"], DbProvider.Sql);

}publicDbHelper(stringconnectionString)

{

DbHelperInstance(connectionString, DbProvider.Sql);

}publicDbHelper(DbProvider dbProviderType)

{

DbHelperInstance(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"], dbProviderType);

}publicDbHelper(stringconnectionString, DbProvider dbProviderType)

{

DbHelperInstance(connectionString, dbProviderType);

}//初始化/publicvoidDbHelperInstance(stringconnectionString, DbProvider dbProviderType)

{this.DbProviderType=dbProviderType;this.ConnectionString=connectionString;

DataReaderList=newList();

CreateFactory();this.Connection=this.dbFactory.CreateConnection();this.Connection.ConnectionString=this.ConnectionString;

}#endregion//创建数据操作工厂///privatevoidCreateFactory()

{switch(DbProviderType)

{caseDbProvider.Sql:this.dbFactory=System.Data.SqlClient.SqlClientFactory.Instance;break;caseDbProvider.Sqlite:this.dbFactory=System.Data.SQLite.SQLiteFactory.Instance;break;caseDbProvider.OleDb:this.dbFactory=System.Data.OleDb.OleDbFactory.Instance;break;caseDbProvider.Oracle:this.dbFactory=System.Data.OracleClient.OracleClientFactory.Instance;break;caseDbProvider.MySql:this.dbFactory=MySql.Data.MySqlClient.MySqlClientFactory.Instance;break;

}

}//创建操作对象//如果包含@,则采用CommandType.Text//privateDbCommand BuilderQueryCommand(stringprocNameOrExText,paramsDbParameter[] parameters)

{if(parameters==null||parameters.Length==0)

{

DbCommand command=this.dbFactory.CreateCommand();

command.CommandText=procNameOrExText;

command.Connection=this.Connection;returncommand;

}if(procNameOrExText.IndexOf('@')>0)//存储过程{returnBuilderQueryCommandText(procNameOrExText, parameters);

}else{returnBuilderQueryCommandStorPro(procNameOrExText, parameters);

}

}//根据存储过程名称和参数生成对应的SQL命令对象//存储过程名或者///存储过程参数///privateDbCommand BuilderQueryCommandStorPro(stringstrSql,paramsDbParameter[] parameters)

{

DbCommand command=this.dbFactory.CreateCommand();

command.CommandText=strSql;

command.CommandType=CommandType.StoredProcedure;

command.Connection=this.Connection;if(parameters!=null)

{foreach(DbParameter pinparameters)

{

command.Parameters.Add(p);

}

}returncommand;

}privateDbCommand BuilderQueryCommandText(stringstrSql,paramsDbParameter[] parameters)

{

DbCommand command=this.dbFactory.CreateCommand();

command.CommandText=strSql;

command.Connection=this.Connection;if(parameters!=null)

{foreach(DbParameter pinparameters)

{

command.Parameters.Add(p);

}

}returncommand;

}publicDbParameter CreateDbParameter(stringparameterName)

{returnCreateDbParameter(parameterName, DBNull.Value, DbType.Object,0, ParameterDirection.Input);

}publicDbParameter CreateDbParameter(stringparameterName,objectvalue)

{returnCreateDbParameter(parameterName, value, DbType.Object,0, ParameterDirection.Input);

}publicDbParameter CreateDbParameter(stringparameterName,objectvalue, DbType dbType)

{returnCreateDbParameter(parameterName,value,dbType,0,ParameterDirection.Input);

}publicDbParameter CreateDbParameter(stringparameterName,objectvalue, DbType dbType,intsize)

{returnCreateDbParameter(parameterName,value,dbType,size,ParameterDirection.Input);

}publicDbParameter CreateDbParameter(stringparameterName,objectvalue, DbType dbType,intsize, ParameterDirection parameterDirection)

{

DbParameter pat=this.dbFactory.CreateParameter();

pat.ParameterName=parameterName;

pat.Value=value;

pat.DbType=dbType;

pat.Size=size;

pat.Direction=parameterDirection;returnpat;

}//返回执行操作成功的数目，使用注意，如果是存储过程，必须在存储过程后加上：select @@ROWCOUNT//存储过程名///参数组///publicintReExNum(stringstrSql,paramsDbParameter[] parameters)

{inteffect=0;if(!OpenConnection())return-1;

DbTransaction trans=Connection.BeginTransaction();try{

DbCommand cmd=BuilderQueryCommand(strSql, parameters);

cmd.Transaction=trans;//根据是否为存储过程来执行不同的处理if(cmd.CommandType==CommandType.StoredProcedure)

{objectresult=cmd.ExecuteScalar();

effect=result==null?-1: Convert.ToInt16(result);

}else{

effect=cmd.ExecuteNonQuery();

}

trans.Commit();returneffect;

}catch{

trans.Rollback();

Connection.Close();return-1;

}finally{

Connection.Close();

}

}//返回结果的存储过程//任何SQL语句///参数值///publicDbDataReader ReSelectdr(stringstrSql,paramsDbParameter[] parameters)

{try{

DbDataReader reader;if(!OpenConnection())returnnull;

DbCommand cmd=BuilderQueryCommand(strSql, parameters);

reader=cmd.ExecuteReader(CommandBehavior.CloseConnection);//在dr关闭之后,就不需要进行cnn的关闭操作了DataReaderList.Add(reader);//添加进dr列表,已备检索returnreader;

}catch{returnnull;

}

}//返回dateSet///publicDataSet ReSelectds(stringstrSql,stringtableName,paramsDbParameter[] parameters)

{try{

DataSet ds=newDataSet();if(!OpenConnection())returnnull;

DbDataAdapter myDa=this.dbFactory.CreateDataAdapter();

myDa.SelectCommand=BuilderQueryCommand(strSql, parameters);

myDa.Fill(ds, tableName);returnds;

}catch{returnnull;

}finally{

Connection.Close();

}

}publicDataTable ReSelectdtb(stringstrSql,paramsDbParameter[] parameters)

{try{

DataTable dt=newDataTable();if(!OpenConnection())returnnull;

DbDataAdapter myDa=this.dbFactory.CreateDataAdapter();

myDa.SelectCommand=BuilderQueryCommand(strSql, parameters);

myDa.Fill(dt);returndt;

}catch{returnnull;

}finally{

Connection.Close();

}

}//通過存儲過程及自定義參數組查詢返回SqlDataAdapter對象///publicDbDataAdapter ReSelectdat(stringstrSql,paramsDbParameter[] parameters)

{if(!OpenConnection())returnnull;try{

DbCommand cmd=BuilderQueryCommand(strSql, parameters);

DbDataAdapter myDa=this.dbFactory.CreateDataAdapter();

myDa.SelectCommand=cmd;returnmyDa;

}catch{

Connection.Close();returnnull;

}

}publicvoidExSQL(stringstrSql,paramsDbParameter[] parameters)

{if(!OpenConnection())return;

DbTransaction trans=Connection.BeginTransaction();try{

DbCommand cmd=BuilderQueryCommand(strSql, parameters);

cmd.Transaction=trans;

cmd.ExecuteNonQuery();

trans.Commit();

}catch{

trans.Rollback();

Connection.Close();return;

}finally{

Connection.Close();

}

}//執行SQL查詢語句,返回記錄條數//Select語句(在select语句中,使用Count(*)函数)///返回查詢到之記錄條數publicintReSelectNum(stringstrSql,paramsDbParameter[] parameters)

{inteffect=0;try{

DbDataReader dr=ReSelectdr(strSql, parameters);if(dr.Read())

{

effect=Convert.ToInt32(dr.GetValue(0));

}returneffect;

}catch{returneffect;

}

}//使用SqlDataAdapter返回指定范围的数据//存储过程名///参数名///起始行///记录数///表名///publicDataSet ReSelectds(stringstrSql, DbParameter[] parameters,intstart,intmaxRecord,stringtableName)

{try{

DataSet ds=newDataSet();

OpenConnection();

DbDataAdapter myDa=this.dbFactory.CreateDataAdapter();

myDa.SelectCommand=BuilderQueryCommand(strSql, parameters);

myDa.Fill(ds, start, maxRecord, tableName);returnds;

}catch{

Connection.Close();returnnull;

}finally{

Connection.Close();

}

}//返回执行操作成功的数目，不关闭连接//执行的查询语句或存储过程///参数组///publicintReExNumNoClose(stringstrSql,paramsDbParameter[] parameters)

{inteffect=0;if(!OpenConnection())return-1;

DbTransaction trans=Connection.BeginTransaction();try{

DbCommand cmd=BuilderQueryCommand(strSql, parameters);

cmd.Transaction=trans;//根据是否为存储过程来执行不同的处理if(cmd.CommandType==CommandType.StoredProcedure)

{objectresult=cmd.ExecuteScalar();

effect=result==null?-1: Convert.ToInt16(result);

}else{

effect=cmd.ExecuteNonQuery();

}

trans.Commit();returneffect;

}catch{

trans.Rollback();returneffect;

}

}//返回执行操作成功的数目，不关闭连接，并返回出现的错误信息。//存储过程名///参数组///publicintReExNumNoClose(stringstrSql,outstringerror,paramsDbParameter[] parameters)

{inteffect=0;

error="";if(!OpenConnection())return-1;

DbTransaction trans=Connection.BeginTransaction();try{

DbCommand cmd=BuilderQueryCommand(strSql, parameters);

cmd.Transaction=trans;if(cmd.CommandType==CommandType.StoredProcedure)

{objectresult=cmd.ExecuteScalar();

effect=result==null?-1: Convert.ToInt16(result);

}else{

effect=cmd.ExecuteNonQuery();

}

effect=cmd.ExecuteNonQuery();

trans.Commit();returneffect;

}catch(Exception ex)

{

trans.Rollback();

error=ex.Message;returneffect;

}

}//返回执行操作成功的数目，并返回发生的错误信息//存储过程名///参数组///publicintReExNum(stringstrSql,outstringerror,paramsDbParameter[] parameters)

{inteffect=0;

error="";if(!OpenConnection())return-1;

DbTransaction trans=Connection.BeginTransaction();try{

DbCommand cmd=BuilderQueryCommand(strSql, parameters);

cmd.Transaction=trans;if(cmd.CommandType==CommandType.StoredProcedure)

{objectresult=cmd.ExecuteScalar();

effect=result==null?-1: Convert.ToInt16(result);

}else{

effect=cmd.ExecuteNonQuery();

}

trans.Commit();returneffect;

}catch(Exception ex)

{

trans.Rollback();

error=ex.Message;returneffect;

}finally{

Connection.Close();

}

}//返回执行操作结果的信息，如果返回为空则表示没错误，否则返回错误的信息。//存储过程名///参数组///publicstringReExStr(stringstrSql,paramsDbParameter[] parameters)

{stringerror=string.Empty;inteffect=0;if(!OpenConnection())returnnull;

DbTransaction trans=Connection.BeginTransaction();try{

DbCommand cmd=BuilderQueryCommand(strSql, parameters);

cmd.Transaction=trans;

effect=cmd.ExecuteNonQuery();

trans.Commit();if(effect==0)

{

error="操作成功记录数为0，请检查意外的错误。"+"sql语句："+strSql;

}else{

error="";

}

}catch(Exception ex)

{

trans.Rollback();

error="sql语句："+strSql+"错误信息："+ex.Message;

}finally{

Connection.Close();

}returnerror;

}//如果数据库连接已关闭,则打开//publicboolOpenConnection()

{if(Connection.State==ConnectionState.Closed)

{try{

Connection.Open();

}catch{returnfalse;

}

}returntrue;

}publicvoidCloseConnection()

{

Connection.Close();

}

}

}