mysql 数据库操作通用类_整理了一个多数据库通用操作类

usingSystem;usingSystem.Collections.Generic;usingSystem.Web;usingSystem.Configuration;usingSystem.Data.Common;usingSystem.Data;usingSystem.Data.SqlClient;usingSystem.Collections;public enumMyDbType

{

MSSQL,

ORACLE,

MYSQL,

ACCESS

}public classDataBase

{private DbConnection cnn;//抽象类型

private DbCommand cmd;//抽象类型

privateDbProviderFactory provider;private stringproviderName;private stringconnectionString;public DataBase() : this(MyDbType.MSSQL)

{

}publicDataBase(MyDbType dbType)

{

providerName=ConfigurationManager.ConnectionStrings[dbType.ToString()].ProviderName;

provider=DbProviderFactories.GetFactory(providerName);

cnn=provider.CreateConnection();

cnn.ConnectionString=ConfigurationManager.ConnectionStrings[dbType.ToString()].ConnectionString;

cmd=provider.CreateCommand();

cmd.Connection=cnn;

}#region 执行不带参数的SQL语句

///

///执行SQL语句，返回影响的记录数///

public int ExecuteSQL(stringsql)

{return ExecuteSQL(sql, null);

}///

///执行多条SQL语句，实现数据库事务。///

public int ExecuteSqlTran(ListsqlList)

{int count = -1;

cnn.Open();

DbTransaction tx=cnn.BeginTransaction();try{

cmd.Transaction=tx;for (int n = 0; n < sqlList.Count; n++)

{string strsql =sqlList[n].ToString();if (strsql.Trim().Length > 1)

{

cmd.CommandText=strsql;

count=cmd.ExecuteNonQuery();

}

}

tx.Commit();

}catch(SqlException e)

{

tx.Rollback();

cnn.Close();throw newException(e.Message);

}returncount;

}///

///执行一条计算查询结果语句，返回查询结果(object)。///

public int ExecuteScalar(stringsql)

{return ExecuteScalar(sql, null);

}///

///执行查询语句，返回DataSet///

public DataSet GetDataSet(stringsql)

{return GetDataSet(sql, null);

}///

///执行查询语句，返回DataSet///

public DataTable GetDataTable(stringsql)

{return GetDataSet(sql).Tables[0];

}///

///执行查询语句，返回DataReader(使用该方法切记要手工关闭DataReader和连接)///

public DbDataReader ExecuteReader(stringsql)

{return ExecuteReader(sql, null);

}#endregion

#region 执行带参数的SQL语句

///

///执行SQL语句，返回影响的记录数///

public int ExecuteSQL(string sql, paramsDbParameter[] cmdParms)

{try{

CreateCommand(sql, cmdParms);int rows =cmd.ExecuteNonQuery();

cmd.Parameters.Clear();returnrows;

}catch(SqlException e)

{

cnn.Close();throw newException(e.Message);

}

}///

///执行多条SQL语句，实现数据库事务。///

public intExecuteSqlTran(Hashtable sqlList)

{int count = -1;

cnn.Open();

DbTransaction tx=cnn.BeginTransaction();try{

cmd.Transaction=tx;foreach (DictionaryEntry myDE insqlList)

{string cmdText =myDE.Key.ToString();

DbParameter[] cmdParms=(DbParameter[])myDE.Value;

CreateCommand(cmdText, cmdParms);

count=cmd.ExecuteNonQuery();

}

tx.Commit();

}catch(SqlException e)

{

tx.Rollback();

cnn.Close();throw newException(e.Message);

}returncount;

}///

///执行一条计算查询结果语句，返回查询结果(object)。///

public int ExecuteScalar(string sql, paramsDbParameter[] cmdParms)

{try{

CreateCommand(sql, cmdParms);object o =cmd.ExecuteScalar();return int.Parse(o.ToString());

}catch(SqlException e)

{

cnn.Close();throw newException(e.Message);

}

}///

///执行查询语句，返回DataSet///

public DataSet GetDataSet(string sql, paramsDbParameter[] cmdParms)

{

DataSet ds= newDataSet();try{

CreateCommand(sql, cmdParms);

DbDataAdapter adapter=provider.CreateDataAdapter();

adapter.SelectCommand=cmd;

adapter.Fill(ds);

}catch(SqlException e)

{

cnn.Close();throw newException(e.Message);

}returnds;

}///

///执行查询语句，返回DataTable///

public DataTable GetDataTable(string sql, paramsDbParameter[] cmdParms)

{return GetDataSet(sql, cmdParms).Tables[0];

}///

///执行查询语句，返回DataReader(使用该方法切记要手工关闭DataReader和连接)///

public DbDataReader ExecuteReader(string sql, paramsDbParameter[] cmdParms)

{try{

CreateCommand(sql, cmdParms);

DbDataReader myReader=cmd.ExecuteReader();returnmyReader;

}catch(SqlException e)

{

cnn.Close();throw newException(e.Message);

}

}public DbParameter MakeParam(string ParamName, DbType DbType, Int32 Size, objectValue)

{

DbParameter Param=cmd.CreateParameter();

Param.ParameterName=ParamName;

Param.DbType=DbType;if(Size > 0)

Param.Size=Size;if (Value != null)

Param.Value=Value;returnParam;

}private DbCommand CreateCommand(stringcmdText, DbParameter[] Prams)

{returnCreateCommand(CommandType.Text, cmdText, Prams);

}private DbCommand CreateCommand(CommandType cmdType, stringcmdText, DbParameter[] Prams)

{if (cnn.State !=ConnectionState.Open)

cnn.Open();

cmd.CommandType=cmdType;

cmd.CommandText=cmdText;if (Prams != null)

{

cmd.Parameters.Clear();foreach (DbParameter Parameter inPrams)

cmd.Parameters.Add(Parameter);

}returncmd;

}public DataSet GetDataSetByProc(stringProcName, DbParameter[] Params)

{

cnn.Open();

DbCommand cmd=CreateCommand(CommandType.StoredProcedure, ProcName, Params);

DbDataAdapter adapter=provider.CreateDataAdapter();

adapter.SelectCommand=cmd;

DataSet ds= newDataSet();

adapter.Fill(ds);

cnn.Close();returnds;

}#endregion}