数据库访问的整体设计如下:
为了支持批量SQL和事务,添加了以下两个枚举:ExcuteMode和ExcuteOption,分别用于支持批量、事务和自动关闭连接。
下面的类表示了一个要执行的SQL:
一、IDB的定义如下
二、DB抽象类实现了IDB中的方法,SqlDB、OleDB以及OdbcDB只需要继承它即可。
三、SqlDB
构造中允许引用一个已有连接,其目的是多个对象共享一个连接,在事务执行模式下可以保证对象的关联性好.
OleDB和OdbcDB类似,不再赘述。
四、一些工具,用于方便构造常用SQL语句和DBParameter等。定义如下,具体代码从略。
其中构造DbParameter需要使用具体的DB实现类以决定生成哪一种参数.
一、IDB的定义如下
public interface IDB : IDisposable
...{
Properties#region Properties
/**//// <summary>
/// 获取或设置连接对象
/// </summary>
DbConnection Connection ...{ get;set;}
/**//// <summary>
/// 获取或设置连接字符串
/// </summary>
string ConnectionString ...{ get;set;}
/**//// <summary>
/// 获取或设置命令对象
/// </summary>
DbCommand Command ...{ get; }
/**//// <summary>
/// 获取数据库连接状态
/// </summary>
ConnectionState ConnectionState ...{ get;}
/**//// <summary>
/// 获取或设置默认的执行模式
/// </summary>
ExcuteMode ExcuteMode ...{ get;set;}
/**//// <summary>
/// 获取或设置默认的执行选项
/// </summary>
ExcuteOption ExcuteOption ...{ get;set;}
/**//// <summary>
/// 获取或设置等待执行SQL,当Flush被调用后,将被删除。
/// </summary>
List<SqlStatement> BatchedSQL ...{ get;set;}
/**//// <summary>
/// 获取或设置事务中的SQL,当Flush被调用后,将被删除。
/// </summary>
List<SqlStatement> TransactionSQL ...{ get;set;}
/**//// <summary>
/// 获取命令执行信息集合
/// </summary>
List<string> ExcuteInfo ...{ get;}
/**//// <summary>
/// 获取当前命令的执行信息
/// </summary>
string CurrentExcuteInfo ...{ get;}
#endregion
Methods#region Methods
/**//// <summary>
/// 打开连接
/// </summary>
bool Open();
/**//// <summary>
/// 关闭连接
/// </summary>
bool Close();
Excute#region Excute
/**//// <summary>
/// 使用ExcuteMode属性指定的方式执行SQL,该方法不会自动关闭连接
/// </summary>
/// <param name="sql">要被执行的SQL</param>
/// <returns>受影响的行数</returns>
int Excute(string sql, DbParameter[] param);
/**//// <summary>
/// 使用给定的选项执行SQL
/// </summary>
/// <param name="sql">要被执行的SQL</param>
/// <param name="option"></param>
/// <returns>受影响的行数</returns>
int Excute(string sql, DbParameter[] param, ExcuteOption option);
/**//// <summary>
/// 使用给定的方式执行SQL
/// </summary>
/// <param name="sql"></param>
/// <param name="mode"></param>
/// <returns></returns>
int Excute(string sql, DbParameter[] param, ExcuteMode mode);
/**//// <summary>
/// 使用给定的方式、选项执行SQL。
/// </summary>
/// <param name="sql"></param>
/// <param name="mode"></param>
/// <param name="option"></param>
/// <returns></returns>
int Excute(string sql, DbParameter[] param, ExcuteMode mode, ExcuteOption option);
/**//// <summary>
/// 使用给定的方式、选项执行SQL。
/// </summary>
/// <param name="sql"></param>
/// <param name="mode"></param>
/// <param name="option"></param>
/// <returns></returns>
int Excute(SqlStatement sql, ExcuteMode mode, ExcuteOption option);
/**//// <summary>
/// 使用给定的方式、选项执行一批SQL。无论ExcuteMode指定何值,这些SQL都会立即执行。
/// </summary>
/// <param name="sql"></param>
/// <param name="mode"></param>
/// <param name="option"></param>
/// <returns></returns>
int ExcuteImmediately(SqlStatement[] sql, ExcuteMode mode, ExcuteOption option);
/**//// <summary>
/// 执行所有的被指定为BatchMode或TransationMode的SQL.
/// </summary>
void Flush();
/**//// <summary>
/// 执行指定模式的SQL.
/// </summary>
/// <returns>是否无误执行</returns>
bool Flush(ExcuteMode Option);
#endregion
Get data#region Get data
/**//// <summary>
/// 获取数据集
/// </summary>
/// <param name="sql">用于查询的SQL</param>
/// <param name="param">SQL的参数,可以为NULL</param>
/// <returns>数据集</returns>
DataSet GetDataSet(string sql, DbParameter[] param);
/**//// <summary>
/// 获取DataTable
/// </summary>
/// <param name="sql">用于查询的SQL</param>
/// <param name="param">SQL的参数,可以为NULL</param>
/// <returns>DataTable</returns>
DataTable GetDataTable(string sql, DbParameter[] param);
/**//// <summary>
/// 获取分页查询数据
/// </summary>
/// <param name="tableName">表或视图名称</param>
/// <param name="IDCol">主键</param>
/// <param name="columns">选择列</param>
/// <param name="condition">查询条件</param>
/// <param name="orderColumns">排序</param>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">页大小</param>
/// <param name="totalRecords">返回符合条件的记录数</param>
/// <returns>指定页的数据</returns>
DataTable GetDataTable(string tableName, string IDCol, string columns, string condition, string orderColumns, int pageIndex, int pageSize, out long totalRecords);
/**//// <summary>
/// 取得查询的第一行数据
/// </summary>
/// <param name="sql">用于查询的SQL</param>
/// <param name="param">SQL的参数,可以为NULL</param>
/// <returns>DataRow</returns>
DataRow GetFirstRow(string sql, DbParameter[] param);
#endregion
/**//// <summary>
/// 获取当前操作对象的ID
/// </summary>
long CurrentID ...{ get;}
#endregion
}
...{
Properties#region Properties
/**//// <summary>
/// 获取或设置连接对象
/// </summary>
DbConnection Connection ...{ get;set;}
/**//// <summary>
/// 获取或设置连接字符串
/// </summary>
string ConnectionString ...{ get;set;}
/**//// <summary>
/// 获取或设置命令对象
/// </summary>
DbCommand Command ...{ get; }
/**//// <summary>
/// 获取数据库连接状态
/// </summary>
ConnectionState ConnectionState ...{ get;}
/**//// <summary>
/// 获取或设置默认的执行模式
/// </summary>
ExcuteMode ExcuteMode ...{ get;set;}
/**//// <summary>
/// 获取或设置默认的执行选项
/// </summary>
ExcuteOption ExcuteOption ...{ get;set;}
/**//// <summary>
/// 获取或设置等待执行SQL,当Flush被调用后,将被删除。
/// </summary>
List<SqlStatement> BatchedSQL ...{ get;set;}
/**//// <summary>
/// 获取或设置事务中的SQL,当Flush被调用后,将被删除。
/// </summary>
List<SqlStatement> TransactionSQL ...{ get;set;}
/**//// <summary>
/// 获取命令执行信息集合
/// </summary>
List<string> ExcuteInfo ...{ get;}
/**//// <summary>
/// 获取当前命令的执行信息
/// </summary>
string CurrentExcuteInfo ...{ get;}
#endregion
Methods#region Methods
/**//// <summary>
/// 打开连接
/// </summary>
bool Open();
/**//// <summary>
/// 关闭连接
/// </summary>
bool Close();
Excute#region Excute
/**//// <summary>
/// 使用ExcuteMode属性指定的方式执行SQL,该方法不会自动关闭连接
/// </summary>
/// <param name="sql">要被执行的SQL</param>
/// <returns>受影响的行数</returns>
int Excute(string sql, DbParameter[] param);
/**//// <summary>
/// 使用给定的选项执行SQL
/// </summary>
/// <param name="sql">要被执行的SQL</param>
/// <param name="option"></param>
/// <returns>受影响的行数</returns>
int Excute(string sql, DbParameter[] param, ExcuteOption option);
/**//// <summary>
/// 使用给定的方式执行SQL
/// </summary>
/// <param name="sql"></param>
/// <param name="mode"></param>
/// <returns></returns>
int Excute(string sql, DbParameter[] param, ExcuteMode mode);
/**//// <summary>
/// 使用给定的方式、选项执行SQL。
/// </summary>
/// <param name="sql"></param>
/// <param name="mode"></param>
/// <param name="option"></param>
/// <returns></returns>
int Excute(string sql, DbParameter[] param, ExcuteMode mode, ExcuteOption option);
/**//// <summary>
/// 使用给定的方式、选项执行SQL。
/// </summary>
/// <param name="sql"></param>
/// <param name="mode"></param>
/// <param name="option"></param>
/// <returns></returns>
int Excute(SqlStatement sql, ExcuteMode mode, ExcuteOption option);
/**//// <summary>
/// 使用给定的方式、选项执行一批SQL。无论ExcuteMode指定何值,这些SQL都会立即执行。
/// </summary>
/// <param name="sql"></param>
/// <param name="mode"></param>
/// <param name="option"></param>
/// <returns></returns>
int ExcuteImmediately(SqlStatement[] sql, ExcuteMode mode, ExcuteOption option);
/**//// <summary>
/// 执行所有的被指定为BatchMode或TransationMode的SQL.
/// </summary>
void Flush();
/**//// <summary>
/// 执行指定模式的SQL.
/// </summary>
/// <returns>是否无误执行</returns>
bool Flush(ExcuteMode Option);
#endregion
Get data#region Get data
/**//// <summary>
/// 获取数据集
/// </summary>
/// <param name="sql">用于查询的SQL</param>
/// <param name="param">SQL的参数,可以为NULL</param>
/// <returns>数据集</returns>
DataSet GetDataSet(string sql, DbParameter[] param);
/**//// <summary>
/// 获取DataTable
/// </summary>
/// <param name="sql">用于查询的SQL</param>
/// <param name="param">SQL的参数,可以为NULL</param>
/// <returns>DataTable</returns>
DataTable GetDataTable(string sql, DbParameter[] param);
/**//// <summary>
/// 获取分页查询数据
/// </summary>
/// <param name="tableName">表或视图名称</param>
/// <param name="IDCol">主键</param>
/// <param name="columns">选择列</param>
/// <param name="condition">查询条件</param>
/// <param name="orderColumns">排序</param>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">页大小</param>
/// <param name="totalRecords">返回符合条件的记录数</param>
/// <returns>指定页的数据</returns>
DataTable GetDataTable(string tableName, string IDCol, string columns, string condition, string orderColumns, int pageIndex, int pageSize, out long totalRecords);
/**//// <summary>
/// 取得查询的第一行数据
/// </summary>
/// <param name="sql">用于查询的SQL</param>
/// <param name="param">SQL的参数,可以为NULL</param>
/// <returns>DataRow</returns>
DataRow GetFirstRow(string sql, DbParameter[] param);
#endregion
/**//// <summary>
/// 获取当前操作对象的ID
/// </summary>
long CurrentID ...{ get;}
#endregion
}
二、DB抽象类实现了IDB中的方法,SqlDB、OleDB以及OdbcDB只需要继承它即可。
/// <summary>
/// 提供数据库操作的方法
/// </summary>
public abstract class DB :IDB
{
Fields / Properties#region Fields / Properties
/**//// <summary>
/// 是否记录操作过程
/// </summary>
public static bool IsLog = false;
Connection,Command#region Connection,Command
/**//// <summary>
/// 数据库连接对象
/// </summary>
protected DbConnection _Connection;
/**//// <summary>
/// 获取或设置连接对象
/// </summary>
public virtual DbConnection Connection ...{ get ...{ return _Connection; } set ...{ _Connection = value; } }
/**//// <summary>
/// 获取数据库连接状态
/// </summary>
public ConnectionState ConnectionState ...{ get ...{ return _Connection.State; } }
/**//// <summary>
/// 获取或设置连接字符串
/// </summary>
public virtual string ConnectionString ...{ get ...{ return Connection.ConnectionString; } set ...{ Connection.ConnectionString = value; } }
/**//// <summary>
/// 命令对象
/// </summary>
protected DbCommand _Command;
/**//// <summary>
/// 获取或设置命令对象
/// </summary>
public virtual DbCommand Command ...{ get ...{ if (_Command == null) _Command = Connection.CreateCommand(); return _Command; } }
#endregion
ExcuteMode,ExcuteOption#region ExcuteMode,ExcuteOption
/**//// <summary>
/// 默认的执行模式
/// </summary>
protected ExcuteMode _ExcuteMode = ExcuteMode.Normal;
/**//// <summary>
/// 获取或设置默认的执行模式
/// </summary>
public ExcuteMode ExcuteMode ...{ get ...{ return _ExcuteMode; } set ...{ _ExcuteMode = value; } }
/**//// <summary>
/// 默认的执行选项
/// </summary>
protected ExcuteOption _ExcuteOption = ExcuteOption.NoSet;
/**//// <summary>
/// 获取或设置默认的执行选项
/// </summary>
public ExcuteOption ExcuteOption ...{ get ...{ return _ExcuteOption; } set ...{ _ExcuteOption = value; } }
#endregion
BatchedSQL,TransactionSQL#region BatchedSQL,TransactionSQL
/**//// <summary>
/// 等待执行SQL,当Flush被调用后,将被删除。
/// </summary>
private List<SqlStatement> _BatchedSQL;
/**//// <summary>
/// 获取或设置等待执行SQL,当Flush被调用后,将被删除。
/// </summary>
public List<SqlStatement> BatchedSQL
...{
get
...{
if (_BatchedSQL == null) _BatchedSQL = new List<SqlStatement>();
return _BatchedSQL;
}
set ...{ _BatchedSQL = value; }
}
/**//// <summary>
/// 事务中的SQL,当Flush被调用后,将被删除。
/// </summary>
private List<SqlStatement> _TransactionSQL;
/**//// <summary>
/// 获取或设置事务中的SQL,当Flush被调用后,将被删除。
/// </summary>
public List<SqlStatement> TransactionSQL
...{
get
...{
if (_TransactionSQL == null) _TransactionSQL = new List<SqlStatement>();
return _TransactionSQL;
}
set ...{ _TransactionSQL = value; }
}
#endregion
ExcuteInfo related#region ExcuteInfo related
/**//// <summary>
/// 执行信息集合
/// </summary>
private List<string> _ExcuteInfo;
/**//// <summary>
/// 获取命令执行信息集合
/// </summary>
public List<string> ExcuteInfo
...{
get
...{
if (_ExcuteInfo == null) _ExcuteInfo = new List<string>();
return _ExcuteInfo;
}
}
/**//// <summary>
/// 获取当前命令的执行信息
/// </summary>
public string CurrentExcuteInfo
...{
get
...{
int i = ExcuteInfo.Count;
return i > 0 ? _ExcuteInfo[i - 1] : "";
}
}
#endregion
CurrentID#region CurrentID
/**//// <summary>
/// 获取当前操作对象的ID
/// </summary>
public long CurrentID
...{
get
...{
try ...{ return Data.To<long>(GetFirstRow("SELECT @@IDENTITY;", null)[0]); }
catch ...{ return 0; }
}
}
#endregion
#endregion
Methods#region Methods
Open,Close,Dispose#region Open,Close,Dispose
/**//// <summary>
/// 打开连接
/// </summary>
public bool Open()
...{
try ...{ Connection.Open(); }
catch (Exception ex) ...{ if(IsLog) ExcuteInfo.Add("Open:" + ex.Message); }
return Connection.State == ConnectionState.Open;
}
/**//// <summary>
/// 关闭连接
/// </summary>
public bool Close()
...{
try ...{ Connection.Close(); }
catch (Exception ex) ...{ if(IsLog) ExcuteInfo.Add("Open:" + ex.Message); }
return Connection.State == ConnectionState.Closed;
}
/**//// <summary>
/// 销毁并回收资源
/// </summary>
public void Dispose()
...{
Close();
_Command = null;
Connection = null;
_ExcuteInfo = null;
BatchedSQL = null;
TransactionSQL = null;
GC.Collect();
}
#endregion
Excute with params#region Excute with params
/**//// <summary>
/// 使用ExcuteMode属性指定的方式执行SQL,该方法不会自动关闭连接
/// </summary>
/// <param name="sql">要被执行的SQL</param>
/// <returns>受影响的行数</returns>
public int Excute(string sql, DbParameter[] param) ...{ return Excute(sql, param, this.ExcuteMode, this.ExcuteOption); }
/**//// <summary>
/// 使用给定的选项执行SQL
/// </summary>
/// <param name="sql">要被执行的SQL</param>
/// <param name="option"></param>
/// <returns>受影响的行数</returns>
public int Excute(string sql, DbParameter[] param, ExcuteOption option) ...{ return Excute(sql, param, this.ExcuteMode, option); }
/**//// <summary>
/// 使用给定的方式执行SQL
/// </summary>
/// <param name="sql"></param>
/// <param name="mode"></param>
/// <returns></returns>
public int Excute(string sql, DbParameter[] param, ExcuteMode mode) ...{ return Excute(sql, param, mode, this.ExcuteOption); }
/**//// <summary>
/// 使用给定的方式、选项执行SQL。
/// </summary>
/// <param name="sql"></param>
/// <param name="mode"></param>
/// <param name="option"></param>
/// <returns></returns>
public int Excute(string sql, DbParameter[] param, ExcuteMode mode, ExcuteOption option)
...{
return Excute(new SqlStatement(sql,param),mode,option);
}
/**//// <summary>
/// 使用给定的方式、选项执行SQL。
/// </summary>
/// <param name="sql"></param>
/// <param name="mode"></param>
/// <param name="option"></param>
/// <returns></returns>
public int Excute(SqlStatement sql, ExcuteMode mode, ExcuteOption option)
...{
int i = 0;
if (mode == ExcuteMode.Normal)
i = ExcuteImmediately(new SqlStatement[] ...{ sql }, mode, option);
else if (mode == ExcuteMode.BatchMode)
...{
BatchedSQL.Add(sql);
if (IsLog) ExcuteInfo.Add("BatchedSQL added:" + sql.SQL);
}
else
...{
TransactionSQL.Add(sql);
if (IsLog) ExcuteInfo.Add("TransactionSQL added:" + sql.SQL);
}
return i;
}
/**//// <summary>
/// 使用给定的方式、选项执行一批SQL。无论ExcuteMode指定何值,这些SQL都会立即执行。
/// </summary>
/// <param name="sqls"></param>
/// <param name="mode"></param>
/// <param name="option"></param>
/// <returns>当输入sql的数目为1时,返回受影响数目;数目>1时,0代表无错误执行,1表示出错.</returns>
public virtual int ExcuteImmediately(SqlStatement[] sqls, ExcuteMode mode, ExcuteOption option)
...{
int result = 1;
Open();
if (mode == ExcuteMode.TransactionMode)
Command.Transaction = Connection.BeginTransaction();
for (int i = 0; i < sqls.Length; i++)
...{
try
...{
Command.CommandText = sqls[i].SQL;
AddDBParameter(sqls[i].DbParameters.ToArray());
result = Command.ExecuteNonQuery();
}
catch (Exception ex) ...{ if (IsLog) ExcuteInfo.Add("Excute error:" + sqls[i] + " " + ex.Message); throw ex; }
}
try
...{
if (mode == ExcuteMode.TransactionMode)
Command.Transaction.Commit();
if (sqls.Length > 1) result = 0;
}
catch ...{ Command.Transaction.Rollback(); result = 1; }
if (option == ExcuteOption.AutoClose) Close();
return result;
}
/**//// <summary>
/// 将DbParameter添加到Command中
/// </summary>
/// <param name="param"></param>
protected void AddDBParameter(DbParameter[] param)
...{
if (param == null) return;
foreach (DbParameter p in param)
Command.Parameters.Add(p);
}
#endregion
Get data#region Get data
/**//// <summary>
/// 获取数据集
/// </summary>
/// <param name="sql">用于查询的SQL</param>
/// <param name="param">SQL的参数,可以为NULL</param>
/// <returns>数据集</returns>
public DataSet GetDataSet(string sql, DbParameter[] param)
...{
DataSet ds = new DataSet();
DataAdapter dap = DBHelper.GetDataAdapter(sql,this);
DbCommand comm = DBHelper.GetInnerCommand(dap);
if (param != null)
foreach (DbParameter p in param)
comm.Parameters.Add(p);
Open();
dap.Fill(ds);
if (ExcuteOption == ExcuteOption.AutoClose) Close();
return ds;
}
/**//// <summary>
/// 获取DataTable
/// </summary>
/// <param name="sql">用于查询的SQL</param>
/// <param name="param">SQL的参数,可以为NULL</param>
/// <returns>DataTable</returns>
public DataTable GetDataTable(string sql, DbParameter[] param)
...{
return GetDataSet(sql, param).Tables[0];
}
/**//// <summary>
/// 获取分页查询数据
/// </summary>
/// <param name="tableName">表或视图名称</param>
/// <param name="IDCol">主键</param>
/// <param name="columns">选择列</param>
/// <param name="condition">查询条件</param>
/// <param name="orderColumns">排序</param>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">页大小</param>
/// <param name="totalRecords">返回符合条件的记录数</param>
/// <returns>指定页的数据</returns>
public DataTable GetDataTable(string tableName, string IDCol, string columns, string condition, string orderColumns, int pageIndex, int pageSize, out long totalRecords)
...{
if (string.IsNullOrEmpty(columns)) columns = "*";
if (pageIndex <= 0) pageIndex = 1;
if (string.IsNullOrEmpty(condition.Trim())) condition = "1=1";
if (orderColumns.ToUpper().IndexOf(IDCol.ToUpper()) < 0)
Data.Connect(ref orderColumns, IDCol + " asc",",");
string sqlGetTotal = string.Format("SELECT @Total=COUNT(*) FROM(SELECT 0 S FROM {0} WHERE {1}) T;", tableName, condition);
string sqlExceptedID = string.Format("SELECT TOP {0} {1} FROM {2} WHERE {3} ORDER BY {4}", pageSize * (pageIndex - 1), IDCol, tableName, condition, orderColumns);
string sql = string.Format("SELECT TOP {0} {1} FROM {2} WHERE {3} AND {4} NOT IN ({5}) ORDER BY {6};", pageSize, columns, tableName, condition, IDCol, sqlExceptedID, orderColumns);
List<DbParameter> param = new List<DbParameter>();
param.Add(DBHelper.ParseOutParameter(this, "@Total"));
DataTable dt = GetDataTable(sqlGetTotal + sql, param.ToArray());
totalRecords = Data.To<long>(param[0].Value);
return dt;
}
/**//// <summary>
/// 取得查询的第一行数据
/// </summary>
/// <param name="sql">用于查询的SQL</param>
/// <param name="param">SQL的参数,可以为NULL</param>
/// <returns>DataRow</returns>
public DataRow GetFirstRow(string sql, DbParameter[] param)
...{
DataRowCollection rows = GetDataTable(sql, param).Rows;
if (rows == null || rows.Count <= 0) return null;
return rows[0];
}
#endregion
Flush#region Flush
/**//// <summary>
/// 使用ExcuteOption属性指定的选项执行所有的被指定为BatchMode或TransationMode的SQL.
/// </summary>
public void Flush()
...{
Flush(ExcuteMode.BatchMode);
Flush(ExcuteMode.BatchMode);
}
/**//// <summary>
/// 执行指定模式的SQL.
/// </summary>
public bool Flush(ExcuteMode mode)
...{
List<SqlStatement> sql = null;
int i = 0;
switch (mode)
...{
case ExcuteMode.TransactionMode: sql = TransactionSQL; break;
case ExcuteMode.BatchMode: sql = BatchedSQL;break;
}
if (sql.Count > 0) i = ExcuteImmediately(sql.ToArray(), mode, this.ExcuteOption);
if(IsLog) ExcuteInfo.Add(string.Format("Flush {0}:{1} statements.", mode, sql.Count));
sql.Clear();
return sql.Count != 1 ? i != 1 : i > 0;
}
#endregion
#endregion
}/**/...
/// 提供数据库操作的方法
/// </summary>
public abstract class DB :IDB
{
Fields / Properties#region Fields / Properties
/**//// <summary>
/// 是否记录操作过程
/// </summary>
public static bool IsLog = false;
Connection,Command#region Connection,Command
/**//// <summary>
/// 数据库连接对象
/// </summary>
protected DbConnection _Connection;
/**//// <summary>
/// 获取或设置连接对象
/// </summary>
public virtual DbConnection Connection ...{ get ...{ return _Connection; } set ...{ _Connection = value; } }
/**//// <summary>
/// 获取数据库连接状态
/// </summary>
public ConnectionState ConnectionState ...{ get ...{ return _Connection.State; } }
/**//// <summary>
/// 获取或设置连接字符串
/// </summary>
public virtual string ConnectionString ...{ get ...{ return Connection.ConnectionString; } set ...{ Connection.ConnectionString = value; } }
/**//// <summary>
/// 命令对象
/// </summary>
protected DbCommand _Command;
/**//// <summary>
/// 获取或设置命令对象
/// </summary>
public virtual DbCommand Command ...{ get ...{ if (_Command == null) _Command = Connection.CreateCommand(); return _Command; } }
#endregion
ExcuteMode,ExcuteOption#region ExcuteMode,ExcuteOption
/**//// <summary>
/// 默认的执行模式
/// </summary>
protected ExcuteMode _ExcuteMode = ExcuteMode.Normal;
/**//// <summary>
/// 获取或设置默认的执行模式
/// </summary>
public ExcuteMode ExcuteMode ...{ get ...{ return _ExcuteMode; } set ...{ _ExcuteMode = value; } }
/**//// <summary>
/// 默认的执行选项
/// </summary>
protected ExcuteOption _ExcuteOption = ExcuteOption.NoSet;
/**//// <summary>
/// 获取或设置默认的执行选项
/// </summary>
public ExcuteOption ExcuteOption ...{ get ...{ return _ExcuteOption; } set ...{ _ExcuteOption = value; } }
#endregion
BatchedSQL,TransactionSQL#region BatchedSQL,TransactionSQL
/**//// <summary>
/// 等待执行SQL,当Flush被调用后,将被删除。
/// </summary>
private List<SqlStatement> _BatchedSQL;
/**//// <summary>
/// 获取或设置等待执行SQL,当Flush被调用后,将被删除。
/// </summary>
public List<SqlStatement> BatchedSQL
...{
get
...{
if (_BatchedSQL == null) _BatchedSQL = new List<SqlStatement>();
return _BatchedSQL;
}
set ...{ _BatchedSQL = value; }
}
/**//// <summary>
/// 事务中的SQL,当Flush被调用后,将被删除。
/// </summary>
private List<SqlStatement> _TransactionSQL;
/**//// <summary>
/// 获取或设置事务中的SQL,当Flush被调用后,将被删除。
/// </summary>
public List<SqlStatement> TransactionSQL
...{
get
...{
if (_TransactionSQL == null) _TransactionSQL = new List<SqlStatement>();
return _TransactionSQL;
}
set ...{ _TransactionSQL = value; }
}
#endregion
ExcuteInfo related#region ExcuteInfo related
/**//// <summary>
/// 执行信息集合
/// </summary>
private List<string> _ExcuteInfo;
/**//// <summary>
/// 获取命令执行信息集合
/// </summary>
public List<string> ExcuteInfo
...{
get
...{
if (_ExcuteInfo == null) _ExcuteInfo = new List<string>();
return _ExcuteInfo;
}
}
/**//// <summary>
/// 获取当前命令的执行信息
/// </summary>
public string CurrentExcuteInfo
...{
get
...{
int i = ExcuteInfo.Count;
return i > 0 ? _ExcuteInfo[i - 1] : "";
}
}
#endregion
CurrentID#region CurrentID
/**//// <summary>
/// 获取当前操作对象的ID
/// </summary>
public long CurrentID
...{
get
...{
try ...{ return Data.To<long>(GetFirstRow("SELECT @@IDENTITY;", null)[0]); }
catch ...{ return 0; }
}
}
#endregion
#endregion
Methods#region Methods
Open,Close,Dispose#region Open,Close,Dispose
/**//// <summary>
/// 打开连接
/// </summary>
public bool Open()
...{
try ...{ Connection.Open(); }
catch (Exception ex) ...{ if(IsLog) ExcuteInfo.Add("Open:" + ex.Message); }
return Connection.State == ConnectionState.Open;
}
/**//// <summary>
/// 关闭连接
/// </summary>
public bool Close()
...{
try ...{ Connection.Close(); }
catch (Exception ex) ...{ if(IsLog) ExcuteInfo.Add("Open:" + ex.Message); }
return Connection.State == ConnectionState.Closed;
}
/**//// <summary>
/// 销毁并回收资源
/// </summary>
public void Dispose()
...{
Close();
_Command = null;
Connection = null;
_ExcuteInfo = null;
BatchedSQL = null;
TransactionSQL = null;
GC.Collect();
}
#endregion
Excute with params#region Excute with params
/**//// <summary>
/// 使用ExcuteMode属性指定的方式执行SQL,该方法不会自动关闭连接
/// </summary>
/// <param name="sql">要被执行的SQL</param>
/// <returns>受影响的行数</returns>
public int Excute(string sql, DbParameter[] param) ...{ return Excute(sql, param, this.ExcuteMode, this.ExcuteOption); }
/**//// <summary>
/// 使用给定的选项执行SQL
/// </summary>
/// <param name="sql">要被执行的SQL</param>
/// <param name="option"></param>
/// <returns>受影响的行数</returns>
public int Excute(string sql, DbParameter[] param, ExcuteOption option) ...{ return Excute(sql, param, this.ExcuteMode, option); }
/**//// <summary>
/// 使用给定的方式执行SQL
/// </summary>
/// <param name="sql"></param>
/// <param name="mode"></param>
/// <returns></returns>
public int Excute(string sql, DbParameter[] param, ExcuteMode mode) ...{ return Excute(sql, param, mode, this.ExcuteOption); }
/**//// <summary>
/// 使用给定的方式、选项执行SQL。
/// </summary>
/// <param name="sql"></param>
/// <param name="mode"></param>
/// <param name="option"></param>
/// <returns></returns>
public int Excute(string sql, DbParameter[] param, ExcuteMode mode, ExcuteOption option)
...{
return Excute(new SqlStatement(sql,param),mode,option);
}
/**//// <summary>
/// 使用给定的方式、选项执行SQL。
/// </summary>
/// <param name="sql"></param>
/// <param name="mode"></param>
/// <param name="option"></param>
/// <returns></returns>
public int Excute(SqlStatement sql, ExcuteMode mode, ExcuteOption option)
...{
int i = 0;
if (mode == ExcuteMode.Normal)
i = ExcuteImmediately(new SqlStatement[] ...{ sql }, mode, option);
else if (mode == ExcuteMode.BatchMode)
...{
BatchedSQL.Add(sql);
if (IsLog) ExcuteInfo.Add("BatchedSQL added:" + sql.SQL);
}
else
...{
TransactionSQL.Add(sql);
if (IsLog) ExcuteInfo.Add("TransactionSQL added:" + sql.SQL);
}
return i;
}
/**//// <summary>
/// 使用给定的方式、选项执行一批SQL。无论ExcuteMode指定何值,这些SQL都会立即执行。
/// </summary>
/// <param name="sqls"></param>
/// <param name="mode"></param>
/// <param name="option"></param>
/// <returns>当输入sql的数目为1时,返回受影响数目;数目>1时,0代表无错误执行,1表示出错.</returns>
public virtual int ExcuteImmediately(SqlStatement[] sqls, ExcuteMode mode, ExcuteOption option)
...{
int result = 1;
Open();
if (mode == ExcuteMode.TransactionMode)
Command.Transaction = Connection.BeginTransaction();
for (int i = 0; i < sqls.Length; i++)
...{
try
...{
Command.CommandText = sqls[i].SQL;
AddDBParameter(sqls[i].DbParameters.ToArray());
result = Command.ExecuteNonQuery();
}
catch (Exception ex) ...{ if (IsLog) ExcuteInfo.Add("Excute error:" + sqls[i] + " " + ex.Message); throw ex; }
}
try
...{
if (mode == ExcuteMode.TransactionMode)
Command.Transaction.Commit();
if (sqls.Length > 1) result = 0;
}
catch ...{ Command.Transaction.Rollback(); result = 1; }
if (option == ExcuteOption.AutoClose) Close();
return result;
}
/**//// <summary>
/// 将DbParameter添加到Command中
/// </summary>
/// <param name="param"></param>
protected void AddDBParameter(DbParameter[] param)
...{
if (param == null) return;
foreach (DbParameter p in param)
Command.Parameters.Add(p);
}
#endregion
Get data#region Get data
/**//// <summary>
/// 获取数据集
/// </summary>
/// <param name="sql">用于查询的SQL</param>
/// <param name="param">SQL的参数,可以为NULL</param>
/// <returns>数据集</returns>
public DataSet GetDataSet(string sql, DbParameter[] param)
...{
DataSet ds = new DataSet();
DataAdapter dap = DBHelper.GetDataAdapter(sql,this);
DbCommand comm = DBHelper.GetInnerCommand(dap);
if (param != null)
foreach (DbParameter p in param)
comm.Parameters.Add(p);
Open();
dap.Fill(ds);
if (ExcuteOption == ExcuteOption.AutoClose) Close();
return ds;
}
/**//// <summary>
/// 获取DataTable
/// </summary>
/// <param name="sql">用于查询的SQL</param>
/// <param name="param">SQL的参数,可以为NULL</param>
/// <returns>DataTable</returns>
public DataTable GetDataTable(string sql, DbParameter[] param)
...{
return GetDataSet(sql, param).Tables[0];
}
/**//// <summary>
/// 获取分页查询数据
/// </summary>
/// <param name="tableName">表或视图名称</param>
/// <param name="IDCol">主键</param>
/// <param name="columns">选择列</param>
/// <param name="condition">查询条件</param>
/// <param name="orderColumns">排序</param>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">页大小</param>
/// <param name="totalRecords">返回符合条件的记录数</param>
/// <returns>指定页的数据</returns>
public DataTable GetDataTable(string tableName, string IDCol, string columns, string condition, string orderColumns, int pageIndex, int pageSize, out long totalRecords)
...{
if (string.IsNullOrEmpty(columns)) columns = "*";
if (pageIndex <= 0) pageIndex = 1;
if (string.IsNullOrEmpty(condition.Trim())) condition = "1=1";
if (orderColumns.ToUpper().IndexOf(IDCol.ToUpper()) < 0)
Data.Connect(ref orderColumns, IDCol + " asc",",");
string sqlGetTotal = string.Format("SELECT @Total=COUNT(*) FROM(SELECT 0 S FROM {0} WHERE {1}) T;", tableName, condition);
string sqlExceptedID = string.Format("SELECT TOP {0} {1} FROM {2} WHERE {3} ORDER BY {4}", pageSize * (pageIndex - 1), IDCol, tableName, condition, orderColumns);
string sql = string.Format("SELECT TOP {0} {1} FROM {2} WHERE {3} AND {4} NOT IN ({5}) ORDER BY {6};", pageSize, columns, tableName, condition, IDCol, sqlExceptedID, orderColumns);
List<DbParameter> param = new List<DbParameter>();
param.Add(DBHelper.ParseOutParameter(this, "@Total"));
DataTable dt = GetDataTable(sqlGetTotal + sql, param.ToArray());
totalRecords = Data.To<long>(param[0].Value);
return dt;
}
/**//// <summary>
/// 取得查询的第一行数据
/// </summary>
/// <param name="sql">用于查询的SQL</param>
/// <param name="param">SQL的参数,可以为NULL</param>
/// <returns>DataRow</returns>
public DataRow GetFirstRow(string sql, DbParameter[] param)
...{
DataRowCollection rows = GetDataTable(sql, param).Rows;
if (rows == null || rows.Count <= 0) return null;
return rows[0];
}
#endregion
Flush#region Flush
/**//// <summary>
/// 使用ExcuteOption属性指定的选项执行所有的被指定为BatchMode或TransationMode的SQL.
/// </summary>
public void Flush()
...{
Flush(ExcuteMode.BatchMode);
Flush(ExcuteMode.BatchMode);
}
/**//// <summary>
/// 执行指定模式的SQL.
/// </summary>
public bool Flush(ExcuteMode mode)
...{
List<SqlStatement> sql = null;
int i = 0;
switch (mode)
...{
case ExcuteMode.TransactionMode: sql = TransactionSQL; break;
case ExcuteMode.BatchMode: sql = BatchedSQL;break;
}
if (sql.Count > 0) i = ExcuteImmediately(sql.ToArray(), mode, this.ExcuteOption);
if(IsLog) ExcuteInfo.Add(string.Format("Flush {0}:{1} statements.", mode, sql.Count));
sql.Clear();
return sql.Count != 1 ? i != 1 : i > 0;
}
#endregion
#endregion
}/**/...
/**//// <summary>
/// 执行SQL SERVER的数据库操作等
/// </summary>
public class SqlDB : DB
...{
Properties#region Properties
/**//// <summary>
/// 获取或设置SqlConnection
/// </summary>
public override DbConnection Connection
...{
get ...{ if (_Connection == null) _Connection = new SqlConnection(); return _Connection; }
set ...{ _Connection = value; }
}
#endregion
Constructors#region Constructors
/**//// <summary>
/// 空构造
/// </summary>
public SqlDB() ...{ }
/**//// <summary>
/// 使用连接字符串构造
/// </summary>
/// <param name="ConnectionString"></param>
public SqlDB(string ConnectionString) ...{ _Connection = new SqlConnection(ConnectionString); }
/**//// <summary>
/// 使用一个现有连接构造
/// </summary>
/// <param name="Connection">一个现有连接构造</param>
public SqlDB(ref SqlConnection Connection) ...{ _Connection = Connection; }
#endregion
}
/// 执行SQL SERVER的数据库操作等
/// </summary>
public class SqlDB : DB
...{
Properties#region Properties
/**//// <summary>
/// 获取或设置SqlConnection
/// </summary>
public override DbConnection Connection
...{
get ...{ if (_Connection == null) _Connection = new SqlConnection(); return _Connection; }
set ...{ _Connection = value; }
}
#endregion
Constructors#region Constructors
/**//// <summary>
/// 空构造
/// </summary>
public SqlDB() ...{ }
/**//// <summary>
/// 使用连接字符串构造
/// </summary>
/// <param name="ConnectionString"></param>
public SqlDB(string ConnectionString) ...{ _Connection = new SqlConnection(ConnectionString); }
/**//// <summary>
/// 使用一个现有连接构造
/// </summary>
/// <param name="Connection">一个现有连接构造</param>
public SqlDB(ref SqlConnection Connection) ...{ _Connection = Connection; }
#endregion
}
OleDB和OdbcDB类似,不再赘述。
四、一些工具,用于方便构造常用SQL语句和DBParameter等。定义如下,具体代码从略。
其中构造DbParameter需要使用具体的DB实现类以决定生成哪一种参数.