通用数据库操作辅助类DbHelper
时间：2010-09-22 来源：火地晋
有问题或者建议,请回复. 使用方式
DbHelper db;
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "SQLite数据文件(*.db3;*.db;*.sqlite)|*.db3;*.db;*.sqlite";
if (ofd.ShowDialog() == DialogResult.OK)
{
txtDataSource.Text = ofd.FileName;
db = new DbHelper("data source=" + txtDataSource.Text, DbProvider.Sqlite);
}
db.ReExNum(txtSql.Text);
接口
using System.Data;
using System.Collections.Generic;
using System.Data.Common;
/// <summary>
/// 该类用于对数据库进行操作
/// Design by 火地晋
/// </summary>
namespace System.Data
{
public interface IDbObject
{
/// <summary>
/// 定义一个DataReader的列表，已备检索
/// </summary>
List<DbDataReader> DataReaderList { get; set; }
/// <summary>
///
/// </summary>
DbConnection Connection { get; set; }
string ConnectionString { get; set; }
DbProvider DbProviderType { get; set; }
/// <summary>
/// 返回执行操作成功的数目，使用注意，如果是存储过程，必须在存储过程后加上：select @@ROWCOUNT
/// </summary>
/// <param name="strSql">存储过程名</param>
/// <param name="parameters">参数组</param>
/// <returns></returns>
int ReExNum(string strSql, params DbParameter[] parameters);
/// <summary>
/// 返回结果的存储过程
/// </summary>
/// <param name="strSql">任何SQL语句</param>
/// <param name="parameters">参数值</param>
/// <returns></returns>
DbDataReader ReSelectdr(string strSql, params DbParameter[] parameters);
/// <summary>
/// 返回dateSet
/// </summary>
/// <param name="strSql"></param>
/// <param name="parameters"></param>
/// <param name="tableName"></param>
/// <returns></returns>
DataSet ReSelectds(string strSql, string tableName, params DbParameter[] parameters);
DataTable ReSelectdtb(string strSql, params DbParameter[] parameters);
/// <summary>
/// 通過存儲過程及自定義參數組查詢返回SqlDataAdapter對象
/// </summary>
DbDataAdapter ReSelectdat(string strSql, params DbParameter[] parameters);
void ExSQL(string strSql, params DbParameter[] parameters);
/// <summary>
/// 執行SQL查詢語句,返回記錄條數
/// </summary>
/// <param name="strSql">Select語句(在select语句中,使用Count(*)函数)</param>
/// <returns>返回查詢到之記錄條數</returns>
int ReSelectNum(string strSql, params DbParameter[] parameters);
/// <summary>
/// 使用SqlDataAdapter返回指定范围的数据
/// </summary>
/// <param name="strSql">存储过程名</param>
/// <param name="parameters">参数名</param>
/// <param name="start">起始行</param>
/// <param name="maxRecord">记录数</param>
/// <param name="tableName">表名</param>
/// <returns></returns>
DataSet ReSelectds(string strSql, DbParameter[] parameters, int start, int maxRecord, string tableName);
/// <summary>
/// 返回执行操作成功的数目，不关闭连接
/// </summary>
/// <param name="strSql">执行的查询语句或存储过程</param>
/// <param name="parameters">参数组</param>
/// <returns></returns>
int ReExNumNoClose(string strSql, params DbParameter[] parameters);
/// <summary>
/// 返回执行操作成功的数目，不关闭连接，并返回出现的错误信息。
/// </summary>
/// <param name="strSql">存储过程名</param>
/// <param name="parameters">参数组</param>
/// <returns></returns>
int ReExNumNoClose(string strSql, out string error, params DbParameter[] parameters);
/// <summary>
/// 返回执行操作成功的数目，并返回发生的错误信息
/// </summary>
/// <param name="strSql">存储过程名</param>
/// <param name="parameters">参数组</param>
/// <returns></returns>
int ReExNum(string strSql, out string error, params DbParameter[] parameters);
/// <summary>
/// 返回执行操作结果的信息，如果返回为空则表示没错误，否则返回错误的信息。
/// </summary>
/// <param name="strSql">存储过程名</param>
/// <param name="parameters">参数组</param>
/// <returns></returns>
string ReExStr(string strSql, params DbParameter[] parameters);
/// <summary>
/// 如果数据库连接已关闭,则打开
/// </summary>
/// <returns></returns>
bool OpenConnection();
/// <summary>
/// 关闭数据库连接
/// </summary>
void CloseConnection();
}
public enum DbProvider
{
Sql=0,
Sqlite=1,
OleDb=2,
Oracle=3,
MySql=4
}
}
类
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
namespace System.Data
{
public class DbHelper : IDbObject
{
#region 属性
public List<DbDataReader> DataReaderList { get; set; }
public DbConnection Connection { get; set; }
public string ConnectionString { get; set; }
public DbProvider DbProviderType { get; set; }
private DbProviderFactory dbFactory;
#endregion
#region 构造函数
public DbHelper()
{
DbHelperInstance(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"], DbProvider.Sql);
}
public DbHelper(string connectionString)
{
DbHelperInstance(connectionString, DbProvider.Sql);
}
public DbHelper(DbProvider dbProviderType)
{
DbHelperInstance(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"], dbProviderType);
}
public DbHelper(string connectionString, DbProvider dbProviderType)
{
DbHelperInstance(connectionString, dbProviderType);
}
/// <summary>
/// 初始化
/// </summary>
/// <param name="connectionString"></param>
/// <param name="dbProviderType"></param>
public void DbHelperInstance(string connectionString, DbProvider dbProviderType)
{
this.DbProviderType = dbProviderType;
this.ConnectionString = connectionString;
DataReaderList = new List<DbDataReader>();
CreateFactory();
this.Connection = this.dbFactory.CreateConnection();
this.Connection.ConnectionString = this.ConnectionString;
}
#endregion
/// <summary>
/// 创建数据操作工厂
/// </summary>
private void CreateFactory()
{
switch (DbProviderType)
{
case DbProvider.Sql:
this.dbFactory = System.Data.SqlClient.SqlClientFactory.Instance;
break;
case DbProvider.Sqlite:
this.dbFactory = System.Data.SQLite.SQLiteFactory.Instance;
break;
case DbProvider.OleDb:
this.dbFactory = System.Data.OleDb.OleDbFactory.Instance;
break;
case DbProvider.Oracle:
this.dbFactory = System.Data.OracleClient.OracleClientFactory.Instance;
break;
case DbProvider.MySql:
this.dbFactory = MySql.Data.MySqlClient.MySqlClientFactory.Instance;
break;
}
}
/// <summary>
/// 创建操作对象
/// </summary>
/// <param name="procNameOrExText">如果包含@,则采用CommandType.Text</param>
/// <param name="parameters"></param>
/// <returns></returns>
private DbCommand BuilderQueryCommand(string procNameOrExText, params DbParameter[] parameters)
{
if (parameters == null || parameters.Length == 0)
{
DbCommand command = this.dbFactory.CreateCommand();
command.CommandText = procNameOrExText;
command.Connection = this.Connection;
return command;
}
if (procNameOrExText.IndexOf('@') > 0)//存储过程
{
return BuilderQueryCommandText(procNameOrExText, parameters);
}
else
{
return BuilderQueryCommandStorPro(procNameOrExText, parameters);
}
}
/// <summary>
/// 根据存储过程名称和参数生成对应的SQL命令对象
/// </summary>
/// <param name="strSql">存储过程名或者</param>
/// <param name="parameters">存储过程参数</param>
/// <returns></returns>
private DbCommand BuilderQueryCommandStorPro(string strSql, params DbParameter[] parameters)
{
DbCommand command = this.dbFactory.CreateCommand();
command.CommandText = strSql;
command.CommandType = CommandType.StoredProcedure;
command.Connection = this.Connection;
if (parameters != null)
{
foreach (DbParameter p in parameters)
{
command.Parameters.Add(p);
}
}
return command;
}
private DbCommand BuilderQueryCommandText(string strSql, params DbParameter[] parameters)
{
DbCommand command = this.dbFactory.CreateCommand();
command.CommandText = strSql;
command.Connection = this.Connection;
if (parameters != null)
{
foreach (DbParameter p in parameters)
{
command.Parameters.Add(p);
}
}
return command;
}
public DbParameter CreateDbParameter(string parameterName)
{
return CreateDbParameter(parameterName, DBNull.Value, DbType.Object, 0, ParameterDirection.Input);
}
public DbParameter CreateDbParameter(string parameterName, object value)
{
return CreateDbParameter(parameterName, value, DbType.Object, 0, ParameterDirection.Input);
}
public DbParameter CreateDbParameter(string parameterName, object value, DbType dbType)
{
return CreateDbParameter(parameterName,value,dbType,0,ParameterDirection.Input);
}
public DbParameter CreateDbParameter(string parameterName, object value, DbType dbType, int size)
{
return CreateDbParameter(parameterName,value,dbType,size,ParameterDirection.Input);
}
public DbParameter CreateDbParameter(string parameterName, object value, DbType dbType, int size, ParameterDirection parameterDirection)
{
DbParameter pat = this.dbFactory.CreateParameter();
pat.ParameterName = parameterName;
pat.Value = value;
pat.DbType = dbType;
pat.Size = size;
pat.Direction = parameterDirection;
return pat;
}
/// <summary>
/// 返回执行操作成功的数目，使用注意，如果是存储过程，必须在存储过程后加上：select @@ROWCOUNT
/// </summary>
/// <param name="strSql">存储过程名</param>
/// <param name="parameters">参数组</param>
/// <returns></returns>
public int ReExNum(string strSql, params DbParameter[] parameters)
{
int effect = 0;
if (!OpenConnection()) return -1;
DbTransaction trans = Connection.BeginTransaction();
try
{
DbCommand cmd = BuilderQueryCommand(strSql, parameters);
cmd.Transaction = trans;
//根据是否为存储过程来执行不同的处理
if (cmd.CommandType == CommandType.StoredProcedure)
{
object result = cmd.ExecuteScalar();
effect = result == null ? -1 : Convert.ToInt16(result);
}
else
{
effect = cmd.ExecuteNonQuery();
}
trans.Commit();
return effect;
}
catch
{
trans.Rollback();
Connection.Close();
return -1;
}
finally
{
Connection.Close();
}
}
/// <summary>
/// 返回结果的存储过程
/// </summary>
/// <param name="strSql">任何SQL语句</param>
/// <param name="parameters">参数值</param>
/// <returns></returns>
public DbDataReader ReSelectdr(string strSql, params DbParameter[] parameters)
{
try
{
DbDataReader reader;
if (!OpenConnection()) return null;
DbCommand cmd = BuilderQueryCommand(strSql, parameters);
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);//在dr关闭之后,就不需要进行cnn的关闭操作了
DataReaderList.Add(reader);//添加进dr列表,已备检索
return reader;
}
catch
{
return null;
}
}
/// <summary>
/// 返回dateSet
/// </summary>
/// <param name="strSql"></param>
/// <param name="parameters"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public DataSet ReSelectds(string strSql, string tableName, params DbParameter[] parameters)
{
try
{
DataSet ds = new DataSet();
if (!OpenConnection()) return null;
DbDataAdapter myDa = this.dbFactory.CreateDataAdapter();
myDa.SelectCommand = BuilderQueryCommand(strSql, parameters);
myDa.Fill(ds, tableName);
return ds;
}
catch
{
return null;
}
finally
{
Connection.Close();
}
}
public DataTable ReSelectdtb(string strSql, params DbParameter[] parameters)
{
try
{
DataTable dt = new DataTable();
if (!OpenConnection()) return null;
DbDataAdapter myDa = this.dbFactory.CreateDataAdapter();
myDa.SelectCommand = BuilderQueryCommand(strSql, parameters);
myDa.Fill(dt);
return dt;
}
catch
{
return null;
}
finally
{
Connection.Close();
}
}
/// <summary>
/// 通過存儲過程及自定義參數組查詢返回SqlDataAdapter對象
/// </summary>
public DbDataAdapter ReSelectdat(string strSql, params DbParameter[] parameters)
{
if (!OpenConnection()) return null;
try
{
DbCommand cmd = BuilderQueryCommand(strSql, parameters);
DbDataAdapter myDa = this.dbFactory.CreateDataAdapter();
myDa.SelectCommand = cmd;
return myDa;
}
catch
{
Connection.Close();
return null;
}
}
public void ExSQL(string strSql, params DbParameter[] 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();
}
}
/// <summary>
/// 執行SQL查詢語句,返回記錄條數
/// </summary>
/// <param name="strSql">Select語句(在select语句中,使用Count(*)函数)</param>
/// <returns>返回查詢到之記錄條數</returns>
public int ReSelectNum(string strSql, params DbParameter[] parameters)
{
int effect = 0;
try
{
DbDataReader dr = ReSelectdr(strSql, parameters);
if (dr.Read())
{
effect = Convert.ToInt32(dr.GetValue(0));
}
return effect;
}
catch
{
return effect;
}
}
/// <summary>
/// 使用SqlDataAdapter返回指定范围的数据
/// </summary>
/// <param name="strSql">存储过程名</param>
/// <param name="parameters">参数名</param>
/// <param name="start">起始行</param>
/// <param name="maxRecord">记录数</param>
/// <param name="tableName">表名</param>
/// <returns></returns>
public DataSet ReSelectds(string strSql, DbParameter[] parameters, int start, int maxRecord, string tableName)
{
try
{
DataSet ds = new DataSet();
OpenConnection();
DbDataAdapter myDa = this.dbFactory.CreateDataAdapter();
myDa.SelectCommand = BuilderQueryCommand(strSql, parameters);
myDa.Fill(ds, start, maxRecord, tableName);
return ds;
}
catch
{
Connection.Close();
return null;
}
finally
{
Connection.Close();
}
}
/// <summary>
/// 返回执行操作成功的数目，不关闭连接
/// </summary>
/// <param name="strSql">执行的查询语句或存储过程</param>
/// <param name="parameters">参数组</param>
/// <returns></returns>
public int ReExNumNoClose(string strSql, params DbParameter[] parameters)
{
int effect = 0;
if (!OpenConnection()) return -1;
DbTransaction trans = Connection.BeginTransaction();
try
{
DbCommand cmd = BuilderQueryCommand(strSql, parameters);
cmd.Transaction = trans;
//根据是否为存储过程来执行不同的处理
if (cmd.CommandType == CommandType.StoredProcedure)
{
object result = cmd.ExecuteScalar();
effect = result == null ? -1 : Convert.ToInt16(result);
}
else
{
effect = cmd.ExecuteNonQuery();
}
trans.Commit();
return effect;
}
catch
{
trans.Rollback();
return effect;
}
}
/// <summary>
/// 返回执行操作成功的数目，不关闭连接，并返回出现的错误信息。
/// </summary>
/// <param name="strSql">存储过程名</param>
/// <param name="parameters">参数组</param>
/// <returns></returns>
public int ReExNumNoClose(string strSql, out string error, params DbParameter[] parameters)
{
int effect = 0;
error = "";
if (!OpenConnection()) return -1;
DbTransaction trans = Connection.BeginTransaction();
try
{
DbCommand cmd = BuilderQueryCommand(strSql, parameters);
cmd.Transaction = trans;
if (cmd.CommandType == CommandType.StoredProcedure)
{
object result = cmd.ExecuteScalar();
effect = result == null ? -1 : Convert.ToInt16(result);
}
else
{
effect = cmd.ExecuteNonQuery();
}
effect = cmd.ExecuteNonQuery();
trans.Commit();
return effect;
}
catch (Exception ex)
{
trans.Rollback();
error = ex.Message;
return effect;
}
}
/// <summary>
/// 返回执行操作成功的数目，并返回发生的错误信息
/// </summary>
/// <param name="strSql">存储过程名</param>
/// <param name="parameters">参数组</param>
/// <returns></returns>
public int ReExNum(string strSql, out string error, params DbParameter[] parameters)
{
int effect = 0;
error = "";
if (!OpenConnection()) return -1;
DbTransaction trans = Connection.BeginTransaction();
try
{
DbCommand cmd = BuilderQueryCommand(strSql, parameters);
cmd.Transaction = trans;
if (cmd.CommandType == CommandType.StoredProcedure)
{
object result = cmd.ExecuteScalar();
effect = result == null ? -1 : Convert.ToInt16(result);
}
else
{
effect = cmd.ExecuteNonQuery();
}
trans.Commit();
return effect;
}
catch (Exception ex)
{
trans.Rollback();
error = ex.Message;
return effect;
}
finally
{
Connection.Close();
}
}
/// <summary>
/// 返回执行操作结果的信息，如果返回为空则表示没错误，否则返回错误的信息。
/// </summary>
/// <param name="strSql">存储过程名</param>
/// <param name="parameters">参数组</param>
/// <returns></returns>
public string ReExStr(string strSql, params DbParameter[] parameters)
{
string error = string.Empty;
int effect = 0;
if (!OpenConnection()) return null;
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();
}
return error;
}
/// <summary>
/// 如果数据库连接已关闭,则打开
/// </summary>
/// <returns></returns>
public bool OpenConnection()
{
if (Connection.State == ConnectionState.Closed)
{
try
{
Connection.Open();
}
catch
{
return false;
}
}
return true;
}
public void CloseConnection()
{
Connection.Close();
}
}
}
相关阅读 更多 +
