/// <summary>
/// 获取SQL数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="domains"></param>
/// <param name="cols"></param>
/// <returns></returns>
public static Dictionary <string, object[]> GetData <T>(IList <T> domains, Expression <Func <T, object> >[] cols) where T : class
{
Dictionary <string, object[]> columnRowData = new Dictionary <string, object[]>();
foreach (var expression in cols)
{
object[] objs = domains.Select(expression.Compile()).ToArray();
var type = expression.GetRightMostMember().Type;
for (int j = 0; j < objs.Length; j++)
{
if (objs[j] == null)
{
#if DEBUG
throw new ArgumentNullException(expression.PropertyName(), "批量更新包含空值");
#endif
//objs[j] = (object)DBNull.Value;
objs[j] = DbTool.GetDefaultValue(type);
}
}
var key = expression.PropertyName();
if (!columnRowData.ContainsKey(key))
{
columnRowData.Add(key, objs);
}
}
return(columnRowData);
}
public object ExecuteScalar(OracleCommand cmd)
{
object ret = null;
try
{
log.Debug(DbTool.GetLogSql(cmd.CommandText, cmd.Parameters));
if (null == Transaction.Current)
{
cmd.Connection.Open();
}
ret = cmd.ExecuteScalar();
}
catch (System.Exception ex)
{
ret = null;
log.Error("ExecuteScalar", ex);
throw ex;
}
finally
{
if (null == Transaction.Current && cmd.Connection != null && cmd.Connection.State != ConnectionState.Closed)
{
cmd.Connection.Close();
cmd.Connection.Dispose();
}
}
return(ret);
}
public DataTable ExecuteDataTable(OracleCommand cmd)
{
DataTable dataTable = new DataTable();
try
{
log.Debug(DbTool.GetLogSql(cmd.CommandText, cmd.Parameters));
if (null == Transaction.Current)
{
cmd.Connection.Open();
}
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(dataTable);
}
catch (System.Exception ex)
{
dataTable = null;
log.Error("ExecuteDataTable", ex);
throw ex;
}
finally
{
if (null == Transaction.Current && cmd.Connection != null && cmd.Connection.State != ConnectionState.Closed)
{
cmd.Connection.Close();
cmd.Connection.Dispose();
}
}
return(dataTable);
}
/// <summary>
/// 谨慎使用 Reader记得关闭
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
private OracleDataReader ExecuteReader(OracleCommand cmd)
{
log.Debug(DbTool.GetLogSql(cmd.CommandText, cmd.Parameters));
if (null == Transaction.Current)
{
cmd.Connection.Open();
return(cmd.ExecuteReader(CommandBehavior.CloseConnection));
}
return(cmd.ExecuteReader());
}
public void AddInOutParameter(OracleCommand cmd, string parameterName, object value)
{
OracleParameter dbParameter = cmd.CreateParameter();
dbParameter.OracleDbType = DbTool.GetOracleDbType(value);
dbParameter.ParameterName = parameterName;
dbParameter.Value = DbTool.GetParamValue(value);
dbParameter.Direction = ParameterDirection.InputOutput;
cmd.Parameters.Add(dbParameter);
}
public void AddBatchInParameter(OracleCommand cmd, string parameterName, object[] value)
{
if (value.Length > 0)
{
// 绑定批处理的行数
cmd.ArrayBindCount = value.Length; // 很重要
cmd.CommandTimeout = 600; // 10分钟
OracleParameter dbParameter = new OracleParameter();
dbParameter.ParameterName = parameterName;
//dbParameter.OracleDbType = DbTool.GetOracleDbType(value);
dbParameter.OracleDbTypeEx = DbTool.GetOracleDbType(value);
dbParameter.Value = DbTool.GetParamValue(value);
dbParameter.Direction = ParameterDirection.Input;
cmd.Parameters.Add(dbParameter);
}
}
/// <summary>
/// 转换字段可区分大小写
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="cmd"></param>
/// <returns></returns>
public List <T> ExecuteList <T>(OracleCommand cmd) where T : class
{
//return Cis.BaseModel.DeserializeObject.ConvertTo<T>(ExecuteDataTable(cmd)) as List<T>;
List <T> list = null;
OracleDataReader reader = null;
try
{
log.Debug(DbTool.GetLogSql(cmd.CommandText, cmd.Parameters));
if (null == Transaction.Current)
{
cmd.Connection.Open();
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
else
{
reader = cmd.ExecuteReader();
}
var columns = DbTool.GetColumns(reader);
list = new List <T>();
while (reader.Read())
{
//TODO ??
// list.Add(Cis.BaseModel.DeserializeObject.CreateItem<T>(reader, columns));
}
}
catch (System.Exception ex)
{
log.Error("ExecuteList", ex);
list = null;
throw ex;
}
finally
{
if (reader != null)
{
reader.Close();
reader.Dispose();
}
if (null == Transaction.Current && cmd.Connection != null && cmd.Connection.State != ConnectionState.Closed)
{
cmd.Connection.Close();
cmd.Connection.Dispose();
}
}
return(list);
}
public void AddBatchInParameters <T>(OracleCommand cmd, IList <T> domains, params Expression <Func <T, object> >[] cols) where T : class
{
Dictionary <string, object[]> columnRowData = DbTool.GetData(domains, cols);
if (columnRowData.Count > 0)
{
string[] dbColumns = columnRowData.Keys.ToArray();
// 绑定批处理的行数
cmd.ArrayBindCount = columnRowData.Values.First().Length; // 很重要
cmd.CommandTimeout = 600; // 10分钟
foreach (string colName in dbColumns)
{
this.AddBatchInParameter(cmd, colName, columnRowData[colName]);
}
}
}
public int ExecuteNonQuery(OracleCommand cmd)
{
int ret = -1;
try
{
if (cmd.ArrayBindCount > 0)
{
#if DEBUG
DbTool.LogOperateDone(cmd);
#endif
DbTool.WriteLog(Level.Debug, cmd);
}
else
{
log.Debug(DbTool.GetLogSql(cmd.CommandText, cmd.Parameters));
}
//执行批处理
if (null == Transaction.Current)
{
cmd.Connection.Open();
}
ret = cmd.ExecuteNonQuery();
}
catch (System.Exception ex)
{
log.Error("ExecuteNonQuery", ex);
throw ex;
}
finally
{
if (null == Transaction.Current && cmd.Connection != null && cmd.Connection.State != ConnectionState.Closed)
{
cmd.Connection.Close();
cmd.Connection.Dispose();
}
}
return(ret);
}
public T ExecuteModel <T>(OracleCommand cmd) where T : class
{
T t = default(T);
OracleDataReader reader = null;
try
{
log.Debug(DbTool.GetLogSql(cmd.CommandText, cmd.Parameters));
if (null == Transaction.Current)
{
cmd.Connection.Open();
}
reader = cmd.ExecuteReader();
if (reader.Read())
{
//TODO 观察一下是否有问题
// t = Cis.BaseModel.DeserializeObject.CreateItem<T>(reader);
}
}
catch (System.Exception ex)
{
log.Error("ExecuteModel", ex);
throw ex;
}
finally
{
if (reader != null)
{
reader.Close();
reader.Dispose();
}
if (null == Transaction.Current && cmd.Connection != null && cmd.Connection.State != ConnectionState.Closed)
{
cmd.Connection.Close();
cmd.Connection.Dispose();
}
}
return(t);
}