}//Execute(string sql)
/// <summary>
/// 执行Sql语句
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="Conn">数据库连接对象</param>
/// <param name="param">参数</param>
/// <returns>返回受影响行数</returns>
static public int Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param)
{
if (Conn == null)
{
DBCoreHelper.ErrLog("DBCoreHelper.Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param):连接对象为空!");
return(0);
}
if (Conn.State == System.Data.ConnectionState.Closed)
{
Conn.Open();
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
cmd.CommandTimeout = 180;
cmd.CommandText = sql;
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
try
{
return(cmd.ExecuteNonQuery());
}
catch (Exception ex)
{
DBCoreHelper.ErrLog("DBCoreHelper.Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param):" + ex.Message + "/nsql=" + sql);
return(0);
}
}//Execute(string sql,System.Data.IDataParameter[] param)
}//ExecuteQueryToDataTable(string sql)
/// <summary>
/// 执行查询返回DataReader
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="Conn">连接对象</param>
/// <returns>成功时返回Reader对象，失败时返回null</returns>
static public System.Data.IDataReader ExecuteQuery(string sql, System.Data.Common.DbConnection Conn)
{
System.Data.IDataReader reader = null;
if (Conn == null)
{
return(null);
}
try
{
if (Conn.State == System.Data.ConnectionState.Closed)
{
Conn.Open();
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
cmd.CommandText = sql;
reader = cmd.ExecuteReader();
return(reader);
}
catch (Exception ex)
{
if (ex.Message.Contains("死锁"))
{
WriteLog(ex.Message + " 再做列锁循环！ExecuteQuery");
System.Threading.Thread.Sleep(200);
return(ExecuteQuery(sql, Conn));
}
else
{
DBCoreHelper.ErrLog("DBCoreHelper.ExecuteQuery(string sql, System.Data.Common.DbConnection Conn):" + ex.Message + "; \n sql =" + sql);
}
return(null);
}
}//ExecuteQuery(string sql)
/// <summary>
/// 查询数据库
/// </summary>
/// <param name="filters">过滤条件filter对象</param>
/// <returns>FilterQueryResult结果对象</returns>
internal virtual FilterQueryResult GetQueryResult(QueryPageFilter filters)
{
FilterQueryResult result = new FilterQueryResult();
string whereStr = GetWhereString(filters);
string orderBy = GetOrderByString(filters);
string groupByFields = GetGroupByFields(filters);
var fields = GetQueryFields(filters);
string sql = "select count(*) from " + filters.TableName + " where " + whereStr;
string strSql = string.Empty;
if (filters.IsPage == true)
{
var index = filters.PageSize * (filters.PageIndex - 1);
if (index < 0)
{
index = 0;
}
strSql = "select top " + filters.PageSize + fields + " from " + filters.TableName +
" where Id Not In ( select top " + index + " Id from " + filters.TableName + " where " + whereStr + orderBy + ") and ( " + whereStr + " ) " + orderBy;
if (CurrPrividerType == SqlPrividerType.Sqlite)
{
strSql = "select " + fields + " from " + filters.TableName +
" where Id Not In ( select Id from " + filters.TableName + " where " + whereStr + orderBy + " limit 0," + index + " ) and ( " + whereStr + " ) " + orderBy + " limit 0," + filters.PageSize;
}
}
else
{
var groupBy = string.Empty;
if (!string.IsNullOrEmpty(groupByFields))
{
fields = groupByFields;
groupBy = "group by " + groupByFields;
}
strSql = "select " + fields + " from " + filters.TableName + " where " + whereStr + groupBy + orderBy;
}
var conn = DBCoreHelper.OpenConnect(CurrConnectionString, DBHelperFactory.GetSqlPrividerTypeName(CurrPrividerType));
var tableCount = DBCoreHelper.ExecuteQueryToDataTable(sql, conn);
int count = int.Parse(tableCount.Rows[0][0].ToString());
if (filters.IsReturnCount == false)
{
var table = DBCoreHelper.ExecuteQueryToDataTable(strSql, conn);
result.Result = table;
}
result.TotalCount = count;
conn.Close();
return(result);
}
}//Execute(string sql)
/// <summary>
/// 执行Sql语句
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="Conn">数据库连接对象</param>
/// <returns>返回受影响行数</returns>
static public int ExecuteWithTrans(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbTransaction tran)
{
if (Conn == null)
{
DBCoreHelper.ErrLog("DBCoreHelper.ExecuteWithTrans(string sql, System.Data.Common.DbConnection Conn):连接对象为空!");
return(0);
}
if (Conn.State == System.Data.ConnectionState.Closed)
{
Conn.Open();
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
cmd.CommandTimeout = 180;
cmd.Transaction = tran;
cmd.CommandText = sql;
try
{
var count = cmd.ExecuteNonQuery();
cmd.Dispose();
return(count);
}
catch (Exception ex)
{
cmd.Dispose();
if (ex.Message.Contains("死锁"))
{
WriteLog(ex.Message + " 再做列锁循环！ExecuteWithTrans");
System.Threading.Thread.Sleep(200);
return(Execute(sql, Conn));
}
else
{
DBCoreHelper.ErrLog("DBCoreHelper.ExecuteWithTrans(string sql, System.Data.Common.DbConnection Conn):" + ex.Message + "/nsql=" + sql);
return(0);
}
}
}//Execute(string sql)
}//Execute(string sql,System.Data.IDataParameter[] param)
/// <summary>
/// 执行一个事务
/// </summary>
/// <param name="sqls">Sql语句组</param>
/// <returns>成功时返回true</returns>
static public bool ExecuteTrans(string[] sqls, System.Data.Common.DbConnection Conn)
{
System.Data.IDbTransaction myTrans;
if (Conn == null)
{
DBCoreHelper.ErrLog("DBCoreHelper.ExecuteTrans(string[] sqls):连接对象为空!");
return(false);
}
if (Conn.State == System.Data.ConnectionState.Closed)
{
Conn.Open();
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
cmd.CommandTimeout = 180;
myTrans = Conn.BeginTransaction();
cmd.Transaction = myTrans;
try
{
foreach (string sql in sqls)
{
if (sql != null)
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
myTrans.Commit();
}
catch (Exception ex)
{
myTrans.Rollback();
DBCoreHelper.ErrLog("DBCoreHelper.ExecuteTrans(string[] sqls):" + ex.Message);
return(false);
}
return(true);
}//Execute(string sql)