/// <summary>
/// 根据数据库实例获取数据库类型枚举
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public static DBMSType GetDBMSType(CommonDB db)
{
if (db != null)
{
if (db is Access)
{
return(DBMSType.Access);
}
if (db is SqlServer)
{
return(DBMSType.SqlServer);
}
if (db is Oracle)
{
return(DBMSType.Oracle);
}
if (db is OleDb)
{
return(DBMSType.UNKNOWN);
}
if (db is Odbc)
{
return(DBMSType.UNKNOWN);
}
}
return(DBMSType.UNKNOWN);
}
/// <summary>
/// 主体查询预备执行操作，这里会构造命令日志信息
/// </summary>
/// <param name="db">当前查询连接对象</param>
/// <param name="SQL">当前主体要执行的查询命令</param>
/// <param name="commandType">命令类型</param>
/// <param name="parameters">命令参数</param>
/// <returns>总是返回成功</returns>
public bool OnExecuting(CommonDB db, ref string SQL, CommandType commandType, IDataParameter[] parameters)
{
if (this.enable)
{
this.currDb = db;
db.BeginTransaction();
//需要真实反映执行的语句顺序，CommandID的赋值推迟到执行后
//logEntity.CommandID = CommonUtil.NewSequenceGUID();
//logEntity.ExecuteTime = DateTime.Now;
//使用 PrepairSQL 方法处理
//logEntity.CommandText = SQL;
logEntity.CommandType = commandType;
logEntity.LogFlag = 0;
//logEntity.ParameterInfo = DbParameterSerialize.Serialize(parameters);
logEntity.PrepairSQL(SQL, DbParameterSerialize.Serialize(parameters));
if (db.ContextObject != null)
{
if (db.ContextObject is OQL)
{
logEntity.CommandName = ((OQL)db.ContextObject).currEntity.GetTableName();
}
else if (db.ContextObject is EntityBase)
{
logEntity.CommandName = ((EntityBase)db.ContextObject).GetTableName();
}
else
{
logEntity.CommandName = "";
}
}
}
return(true);
}
/// <summary>
/// 主体查询预备执行操作，这里会构造命令日志信息
/// </summary>
/// <param name="db">当前查询连接对象</param>
/// <param name="SQL">当前主体要执行的查询命令</param>
/// <param name="commandType">命令类型</param>
/// <param name="parameters">命令参数</param>
/// <returns>总是返回成功</returns>
public bool OnExecuting(CommonDB db, ref string SQL, CommandType commandType, IDataParameter[] parameters)
{
if (this.enable)
{
this.currDb = db;
db.BeginTransaction();
logEntity.CommandID = CommonUtil.NewSequenceGUID();
logEntity.CommandText = SQL;
logEntity.CommandType = commandType;
logEntity.LogFlag = 0;
logEntity.ParameterInfo = DbParameterSerialize.Serialize(parameters);
logEntity.ExecuteTime = DateTime.Now;
}
return(true);
}
/// <summary>
/// 根据数据库实例获取数据库类型枚举
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public static DBMSType GetDBMSType(CommonDB db)
{
if (db != null)
{
//if (db is Access)
// return DBMSType.Access;
if (db is SqlServer)
{
return(DBMSType.SqlServer);
}
//if (db is Oracle)
// return DBMSType.Oracle;
//if (db is OleDb)
// return DBMSType.UNKNOWN;
//if (db is Odbc)
// return DBMSType.UNKNOWN;
}
return(DBMSType.UNKNOWN);
}
public bool OnExecuting(CommonDB db, ref string SQL, CommandType commandType, IDataParameter[] parameters)
{
this.CurrCommandLog.ReSet();
return(true);
}
// /// <summary>
// /// 获取数据适配器实例
// /// </summary>
// /// <returns>数据适配器</returns>
// protected override IDbDataAdapter GetDataAdapter(IDbCommand command)
// {
// return null;
// }
// /// <summary>
// /// 获取一个新参数对象
// /// </summary>
// /// <returns>特定于数据源的参数对象</returns>
// public override IDataParameter GetParameter()
// {
// return null;
// }
/// <summary>
/// 创建公共数据访问类的实例
/// </summary>
/// <param name="providerAssembly">提供这程序集名称</param>
/// <param name="providerType">提供者类型</param>
/// <returns></returns>
public static AdoHelper CreateHelper(string providerAssembly, string providerType)
{
return((AdoHelper)CommonDB.CreateInstance(providerAssembly, providerType));
}
public DataParameterFormat(CommonDB db)
{
this.DB = db;
paras = new List <IDataParameter>();
paraIndex = 0;
}
/// <summary>
/// 更新数据集，带数据访问对象
/// </summary>
/// <param name="ds">数据集</param>
/// <param name="DB">数据访问对象</param>
/// <returns></returns>
public int UpdateDataSet(DataSet ds, CommonDB DB)
{
int count = 0;
foreach (DataTable dt in ds.Tables)
{
if (dt.PrimaryKey.Length > 0)
{
count += UpdateDataTable(dt, GetSqlUpdate(dt), DB);
_msg = "已经更新记录" + count + "条";
}
else
{
count += UpdateDataTable(dt, GetSqlInsert(dt), DB);
_msg = "已经插入记录" + count + "条";
}// end if
}//end for
return count;
}//end function
/// <summary>
/// 更新数据表，带数据访问对象
/// </summary>
/// <param name="dt"></param>
/// <param name="SQL"></param>
/// <param name="DB"></param>
/// <returns></returns>
private int UpdateDataTable(DataTable dt, string SQL, CommonDB DB)
{
string ParaChar = GetDBParaChar(DB);
SQL = SQL.Replace("@@", ParaChar);
int count = 0;
foreach (DataRow dr in dt.Rows)
{
IDataParameter[] paras = new IDataParameter[dt.Columns.Count];
for (int i = 0; i < dt.Columns.Count; i++)
{
paras[i] = DB.GetParameter(ParaChar + dt.Columns[i].ColumnName, dr[i]);
}
count += DB.ExecuteNonQuery(SQL, CommandType.Text, paras);
if (DB.ErrorMessage != "")
throw new Exception(DB.ErrorMessage);
}
return count;
}
/// <summary>
/// 自动保存数据表中的数据到数据库
/// <remarks>更新时间：2011.11.16</remarks>
/// </summary>
/// <param name="dt"></param>
/// <param name="insertSQL"></param>
/// <param name="updateSQL"></param>
/// <param name="DB"></param>
/// <returns></returns>
private static int SaveDataTable(DataTable dt, string insertSQL, string updateSQL, CommonDB DB)
{
//CommonDB DB = MyDB.GetDBHelper();
string ParaChar = GetDBParaChar(DB);
insertSQL = insertSQL.Replace("@@", ParaChar);
updateSQL = updateSQL.Replace("@@", ParaChar);
int count = 0;
DB.BeginTransaction();
try
{
foreach (DataRow dr in dt.Rows)
{
IDataParameter[] paras = new IDataParameter[dt.Columns.Count];
for (int i = 0; i < dt.Columns.Count; i++)
{
paras[i] = DB.GetParameter(ParaChar + dt.Columns[i].ColumnName, dr[i]);
}
//先更新，如果没有记录受影响再次尝试执行插入
int tempCount = DB.ExecuteNonQuery(updateSQL, CommandType.Text, paras);
if (tempCount <= 0)
tempCount = DB.ExecuteNonQuery(insertSQL, CommandType.Text, paras);
count += tempCount;
if (DB.ErrorMessage != "")
throw new Exception(DB.ErrorMessage);
}
DB.Commit();
}
catch (Exception ex)
{
DB.Rollback();
throw ex;
}
return count;
}
/// <summary>
/// 获取特定数据库参数字符
/// </summary>
/// <param name="DB">数据库类型</param>
/// <returns></returns>
private static string GetDBParaChar(CommonDB DB)
{
return DB is Oracle ? ":" : "@";
}
/// <summary>
/// 更新数据集中的字段到数据源中
/// </summary>
/// <param name="sDs">源数据集</param>
/// <param name="tableName">要更新的表</param>
/// <param name="fieldName">要更新的字段</param>
/// <param name="fieldValue">字段的值</param>
/// <param name="pkName">主键名称</param>
/// <param name="DB">数据访问对象</param>
/// <returns></returns>
public int UpdateField(DataSet sDs, string tableName, string fieldName, object fieldValue, string pkName, CommonDB DB)
{
DataSet ds = sDs.Copy();
DataTable dt = ds.Tables[tableName];
fieldName = fieldName.ToUpper();
pkName = pkName.ToUpper();
for (int i = 0; i < dt.Columns.Count; i++)
{
string colName = dt.Columns[i].ColumnName.ToUpper();
if (colName == fieldName || colName == pkName)
continue;
dt.Columns.Remove(colName);
i = 0;//集合元素位置可能已经迁移，所以需要重新从头开始查找
}
dt.PrimaryKey = new DataColumn[] { dt.Columns[pkName] };
foreach (DataRow dr in dt.Rows)
{
dr[fieldName] = fieldValue;
}
int updCount = UpdateDataSet(ds, DB);
return updCount;
}
/// <summary>
/// 根据主键信息从数据源查询数据表到数据集中
/// </summary>
/// <param name="fields">字段列表</param>
/// <param name="tableName">数据源中的表名称</param>
/// <param name="pkNames">主键名称数组</param>
/// <param name="pkValues">主键值数组，必须和主键名对应</param>
/// <param name="DB">数据访问对象</param>
/// <returns></returns>
public DataSet SelectDataSet(string fields, string tableName, string[] pkNames, object[] pkValues, CommonDB DB)
{
string ParaChar = GetDBParaChar(DB);
string sqlSelect = "SELECT " + fields + " FROM " + tableName + " WHERE 1=1 ";
IDataParameter[] paras = new IDataParameter[pkNames.Length];
for (int i = 0; i < pkNames.Length; i++)
{
sqlSelect += " And " + pkNames[i] + "=" + ParaChar + pkNames[i];
paras[i] = DB.GetParameter(ParaChar + pkNames[i], pkValues[i]);
}
DataSet ds = DB.ExecuteDataSet(sqlSelect, CommandType.Text, paras);
ds.Tables[0].TableName = tableName;
return ds;
}
}//end function
/// <summary>
/// 根据数据集中在指定的表中，根据表中的指定列的值在数据源中删除数据,带数据访问对象
/// </summary>
/// <param name="ds">数据集</param>
/// <param name="tableName">表名称</param>
/// <param name="columnName">列名</param>
/// <param name="DB">数据访问对象</param>
/// <returns></returns>
public int DeleteDataSet(DataSet ds, string tableName, string columnName, CommonDB DB)
{
DataTable dt = ds.Tables[tableName];
string ParaChar = GetDBParaChar(DB);
int count = 0;
string sqlDelete = "DELETE FROM " + tableName + " WHERE " + columnName + "=" + ParaChar + columnName;
foreach (DataRow dr in dt.Rows)
{
IDataParameter[] paras = { DB.GetParameter(ParaChar + columnName, dr[columnName]) };
count += DB.ExecuteNonQuery(sqlDelete, CommandType.Text, paras);
if (DB.ErrorMessage != "")
throw new Exception(DB.ErrorMessage);
if (count >= dt.Rows.Count) break;
}
return count;
}
public EntityCommand(EntityBase entity, CommonDB db)
{
this.currEntity = entity;
this.currDb = db;
}
/// <summary>
/// 根据数据库实例获取数据库类型枚举
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public static DBMSType GetDBMSType(CommonDB db)
{
if (db != null)
{
if (db is Access)
return DBMSType.Access;
if (db is SqlServer)
return DBMSType.SqlServer;
if (db is Oracle)
return DBMSType.Oracle;
if (db is OleDb)
return DBMSType.UNKNOWN;
if (db is Odbc)
return DBMSType.UNKNOWN;
}
return DBMSType.UNKNOWN;
}
}//end function
/// <summary>
/// 自动将数据集中的数据更新或者插入到数据库
/// <remarks>更新时间：2011.11.16</remarks>
/// </summary>
/// <param name="ds"></param>
/// <param name="DB"></param>
/// <returns></returns>
public static int SaveDataSet(DataSet ds, CommonDB DB)
{
int count = 0;
foreach (DataTable dt in ds.Tables)
{
string insertSql = GetSqlInsert(dt);
string updateSql = GetSqlUpdate(dt);
count += SaveDataTable(dt, insertSql, updateSql, DB);
}//end for
return count;
}
