/// <summary>
/// 计算查询结果数
/// </summary>
/// <param name="eaconn"></param>
/// <param name="cmdText"></param>
/// <param name="parameters"></param>
/// <param name="pageInfo"></param>
/// <returns></returns>
public long Count(IEAConnection eaconn, string cmdText, IDictionary<string, object> parameters)
{
using (IDbConnection conn = eaconn.Open())
using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
{
AddParameters(eaconn.DatabaseType, cmd, parameters);
cmd.CommandText = string.Format("select count(*) from ({0})T ", cmd.CommandText);
return cmd.ExecuteScalar().ToLong();
}
}
public void CreateTable(IEAConnection eaconn, string tableName, string cmdText)
{
string sqlStr = string.Empty;
if (eaconn.DatabaseType != DatabaseType.Oracle)
{
sqlStr = string.Format("SELECT top 1 table_name as [Name] FROM INFORMATION_SCHEMA.TABLES where table_name=@table_name");
}
else
{
tableName = tableName.ToUpper();
sqlStr = string.Format("select table_name as Name from user_tables where table_name=:table_name");
}
IDictionary<string, object> parameters = new Dictionary<string, object>();
parameters.Add("table_name", tableName);
if (ExecuteScalar(eaconn, sqlStr, parameters) == null)
{
ExecuteNonQuery(eaconn, cmdText);
}
}
public DataSet ExecuteDataSet(IEAConnection eaconn, string cmdText, IDictionary<string, object> parameters)
{
using (IDbConnection conn = eaconn.Open())
using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
{
AddParameters(eaconn.DatabaseType, cmd, parameters);
return new ADODataAdapter().FillDataSet(cmd);
}
}
public void Insert(IEAConnection eaconn, DataTable dt, IDictionary<string, string> mappings)
{
if (eaconn.DatabaseType != DatabaseType.Oracle)
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(eaconn.ConnectionString))
{
foreach (KeyValuePair<string, string> item in mappings)
{
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(item.Key, item.Value));
}
bulkCopy.DestinationTableName = dt.TableName;
bulkCopy.WriteToServer(dt);
}
return;
}
if (!string.IsNullOrEmpty(dt.TableName))
ExecuteNonQuery(eaconn, string.Format("alter table {0} NOLOGGING", dt.TableName.ToUpper()));
foreach (DataRow dr in dt.Rows)
{
IDictionary<string, object> parameters = mappings.Values.ToDictionary(field => field, field => dr[field]);
ExecuteNonQuery(eaconn, @"INSERT INTO DD_BatchValue
(ID
,BatchDataID
,Value
,CreateTime
,Creator)
VALUES
(:ID,
:BatchDataID,
:Value,
to_date(:CreateTime, 'yyyy-mm-dd hh24:mi:ss'),
:Creator)", parameters);
}
if (!string.IsNullOrEmpty(dt.TableName))
ExecuteNonQuery(eaconn, string.Format("alter table {0} LOGGING", dt.TableName.ToUpper()));
}
public object ExecuteScalarWithTrans(IEAConnection eaconn, string cmdText)
{
object value = null;
using (IDbConnection conn = eaconn.Open())
{
using (System.Data.IDbTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted))
{
using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
{
try
{
cmd.Transaction = trans;
value = cmd.ExecuteScalar();
trans.Commit();
}
catch (Exception ex)
{
GlobalLogger.Error<DataContext>(string.Format("执行sql={0}出错", cmdText), ex);
trans.Rollback();
throw;
}
}
}
}
return value.ToSafeString();
}
public DataTable ExecuteTop1(IEAConnection eaconn, string cmdText)
{
if (eaconn.DatabaseType == DatabaseType.Oracle)
{
return ExecuteDataTable(eaconn, string.Format("select * from ({0}) T where rownum=1", cmdText));
}
return ExecuteDataTable(eaconn, string.Format("select top 1 * from ({0}) T ", cmdText));
}
public object ExecuteScalar(IEAConnection eaconn, string cmdText, IDictionary<string, object> parameters)
{
using (IDbConnection conn = eaconn.Open())
using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
{
AddParameters(eaconn.DatabaseType, cmd, parameters);
return cmd.ExecuteScalar();
}
}
public object ExecuteScalar(IEAConnection eaconn, string cmdText)
{
using (IDbConnection conn = eaconn.Open())
using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
{
return cmd.ExecuteScalar();
}
}
public DataTable ExecuteProcDataTable(IEAConnection eaconn, string procName, IDictionary<string, object> parameters)
{
DataSet ds = ExecuteProcDataSet(eaconn, procName, parameters);
if (ds == null || ds.Tables.Count == 0)
return null;
return ds.Tables[0];
}
public object ExecuteProcScalar(IEAConnection eaconn, string procName, IDictionary<string, object> parameters)
{
using (IDbConnection conn = eaconn.Open())
using (IDbCommand cmd = PrepareCommand(procName, conn, CommandType.StoredProcedure))
{
AddParameters(eaconn.DatabaseType, cmd, parameters);
return cmd.ExecuteScalar();
}
}
public DataSet ExecuteProcDataSet(IEAConnection eaconn, string procName, IDictionary<string, object> parameters)
{
using (IDbConnection conn = eaconn.Open())
{
using (IDbCommand cmd = PrepareCommand(procName, conn, CommandType.StoredProcedure))
{
AddParameters(eaconn.DatabaseType, cmd, parameters);
return new ADODataAdapter().FillDataSet(cmd);
}
}
}
public void ExecuteNonQuery(IEAConnection eaconn, string cmdText)
{
using (IDbConnection conn = eaconn.Open())
using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
{
cmd.ExecuteNonQuery();
}
}
public DataTable ExecuteDataTable(IEAConnection eaconn, string cmdText)
{
DataSet ds = ExecuteDataSet(eaconn, cmdText);
if (ds == null || ds.Tables.Count == 0)
return null;
return ds.Tables[0];
}
/// <summary>
/// 执行sql命令，返回DataTable
/// </summary>
/// <param name="cmdText">sql命令</param>
/// <param name="parameters">条件参数</param>
/// <param name="pageInfo">分页信息</param>
/// <returns></returns>
public DataTable ExecuteDataTable(IEAConnection eaconn, string cmdText, IDictionary<string, object> parameters, string orderBy, PageInfo pageInfo)
{
pageInfo.ItemCount = Count(eaconn, cmdText, parameters);
if (!string.IsNullOrEmpty(orderBy) && !orderBy.Replace(" ", "").ToLower().StartsWith("orderby"))
{
orderBy = string.Format("order by {0} ", orderBy);
}
using (IDbConnection conn = eaconn.Open())
using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
{
//AddParameters(eaconn.DatabaseType, cmd, parameters);
//cmd.CommandText = string.Format("select count(*) from ({0})T ", cmd.CommandText);
AddParameters(eaconn.DatabaseType, cmd, parameters);
string paramChar = DataUtil.GetParamChar(eaconn.DatabaseType);
parameters.SafeAdd(paramChar + "PageIndex", pageInfo.PageIndex);
parameters.SafeAdd(paramChar + "PageSize", pageInfo.PageSize);
if (eaconn.DatabaseType == DatabaseType.MySQL)
{
cmd.CommandText = string.Format("{0} {1} limit {2}, {3} ", cmd.CommandText, orderBy, (pageInfo.PageIndex - 1) * pageInfo.PageSize, pageInfo.PageSize);
}
else if (eaconn.DatabaseType == DatabaseType.Oracle)
{
cmd.CommandText = string.Format("SELECT * FROM(SELECT ROWNUM RN,T.* FROM ({0} {2})T WHERE ROWNUM <= {1}PageIndex*{1}PageSize) WHERE RN >=({1}PageIndex-1)*{1}PageSize+1", cmd.CommandText, paramChar, orderBy);
}
else
{
cmd.CommandText = string.Format("select Top ({0}PageSize) * from (select ROW_NUMBER() OVER( {2} ) AS row_number, * from ({1}) P ) T Where row_number>({0}PageIndex-1)*{0}PageSize", paramChar, cmd.CommandText, string.IsNullOrEmpty(orderBy) ? "ORDER BY CURRENT_TIMESTAMP" : orderBy);
}
AddParameter(cmd, "PageIndex", pageInfo.PageIndex);
AddParameter(cmd, "PageSize", pageInfo.PageSize);
DataSet ds = new ADODataAdapter().FillDataSet(cmd);
return ds != null && ds.Tables.Count > 0 ? ds.Tables[0] : null;
}
}
public DataSet ExecuteDataSet(IEAConnection eaconn, string cmdText)
{
using (IDbConnection conn = eaconn.Open())
using (IDbCommand cmd = PrepareCommand(cmdText, conn, CommandType.Text))
{
DataSet ds = new DataSet();
return new ADODataAdapter().FillDataSet(cmd);
}
}
