/// <summary>
/// ExecSQL
/// </summary>
/// <param name="cmd"></param>
/// <param name="sql"></param>
/// <param name="objParams"></param>
/// <returns></returns>
public int ExecSql(OracleCommand cmd, string sql, int step, params IDataParameter[] objParams)
{
int intAffectedRows = 0;
try
{
cmd.CommandText = GetParam(sql);
if (objParams != null)
{
for (int i = 0; i < objParams.Length; i++)
{
if (objParams[i].Value == null)
{
objParams[i].Value = System.DBNull.Value;
}
((OracleParameter)objParams[i]).ParameterName = (i + 1 + step * 10).ToString();
cmd.Parameters.Add((OracleParameter)objParams[i]);
}
}
SqlLog.OutPutParmLog(objParams);
if (cmd.Connection.State == ConnectionState.Closed)
{
cmd.Connection.Open();
}
intAffectedRows = cmd.ExecuteNonQuery();
}
catch (System.Exception objEx)
{
intAffectedRows = -1;
throw objEx;
}
return(intAffectedRows);
}
/// <summary>
/// 获取DataTable带参数
/// </summary>
/// <param name="conn"></param>
/// <param name="procName"></param>
/// <param name="dtRecord"></param>
/// <param name="objParams"></param>
/// <returns></returns>
public int GetDataTableFromProc(string conn, string procName, ref DataTable dtRecord, params IDataParameter[] objParams)
{
OracleCommand cmd = GetSqlCommand(conn, CommandType.StoredProcedure);
int intAffectedRows = 0;
try
{
cmd.CommandText = GetParam(procName);
if (objParams != null)
{
for (int i = 0; i < objParams.Length; i++)
{
if (objParams[i].Value == null)
{
objParams[i].Value = System.DBNull.Value;
}
else if (objParams[i].Direction == ParameterDirection.Output && objParams[i].DbType == DbType.String)
{
((OracleParameter)objParams[i]).Size = 1000;
}
cmd.Parameters.Add((OracleParameter)objParams[i]);
}
}
SqlLog.OutPutParmLog(objParams);
OracleDataAdapter Adapter = new OracleDataAdapter();
Adapter.SelectCommand = cmd;
dtRecord = new DataTable();
Adapter.Fill(dtRecord);
}
catch (System.Exception objEx)
{
intAffectedRows = -1;
throw objEx;
}
finally
{
if (cmd != null)
{
cmd.Connection.Close();
cmd.Dispose();
}
}
return(intAffectedRows);
}
/// <summary>
/// 执行SQL带参数
/// </summary>
/// <param name="conn"></param>
/// <param name="sql"></param>
/// <param name="objParams"></param>
/// <returns></returns>
public int ExecSql(string conn, string sql, int step, params IDataParameter[] objParams)
{
OracleCommand cmd = GetSqlCommand(conn, CommandType.Text);
int intAffectedRows = 0;
try
{
cmd.CommandText = GetParam(sql);
if (objParams != null)
{
for (int i = 0; i < objParams.Length; i++)
{
if (objParams[i].Value == null)
{
objParams[i].Value = System.DBNull.Value;
}
if (!string.IsNullOrEmpty(objParams[i].ParameterName) && objParams[i].ParameterName == "xmltype")
{
((OracleParameter)objParams[i]).OracleDbType = OracleDbType.XmlType;
}
((OracleParameter)objParams[i]).ParameterName = (i + 1 + step * 10).ToString();
cmd.Parameters.Add((OracleParameter)objParams[i]);
}
}
SqlLog.OutPutParmLog(objParams);
if (cmd.Connection.State == ConnectionState.Closed)
{
cmd.Connection.Open();
}
intAffectedRows = cmd.ExecuteNonQuery();
}
catch (System.Exception objEx)
{
intAffectedRows = -1;
throw objEx;
}
finally
{
if (cmd != null)
{
cmd.Connection.Close();
cmd.Dispose();
}
}
return(intAffectedRows);
}
/// <summary>
/// 执行存储过程带参数
/// </summary>
/// <param name="conn"></param>
/// <param name="procName"></param>
/// <param name="objParams"></param>
/// <returns></returns>
public int ExecProc(string conn, string procName, params IDataParameter[] objParm)
{
SqlCommand cmd = GetSqlCommand(conn, EnumExecType.ExecProc);
int intAffectedRows = 0;
try
{
cmd.CommandText = GetParm(procName);
if (objParm != null && objParm.Length > 0)
{
for (int i = 0; i < objParm.Length; i++)
{
if (objParm[i].Value == null)
{
objParm[i].Value = System.DBNull.Value;
}
else if (objParm[i].Direction == ParameterDirection.Output && objParm[i].DbType == DbType.String)
{
((SqlParameter)objParm[i]).Size = 1000;
}
cmd.Parameters.Add((SqlParameter)objParm[i]);
}
SqlLog.OutPutParmLog(objParm);
}
if (cmd.Connection.State == ConnectionState.Closed)
{
cmd.Connection.Open();
}
intAffectedRows = cmd.ExecuteNonQuery();
}
catch (System.Exception objEx)
{
intAffectedRows = -1;
throw objEx;
}
finally
{
if (cmd != null)
{
cmd.Connection.Close();
cmd.Dispose();
}
}
return(intAffectedRows);
}
/// <summary>
/// /// 获取DataTable带参数
/// </summary>
/// <param name="conn"></param>
/// <param name="procName"></param>
/// <param name="objParm"></param>
/// <returns></returns>
public DataTable GetDataTableFromProc(string conn, string procName, params IDataParameter[] objParm)
{
DataTable dtRecord = null;
SqlCommand cmd = GetSqlCommand(conn, EnumExecType.ExecProc);
try
{
cmd.CommandText = GetParm(procName);
if (objParm != null && objParm.Length > 0)
{
for (int i = 0; i < objParm.Length; i++)
{
if (objParm[i].Value == null)
{
objParm[i].Value = System.DBNull.Value;
}
else if (objParm[i].Direction == ParameterDirection.Output && objParm[i].DbType == DbType.String)
{
((SqlParameter)objParm[i]).Size = 1000;
}
cmd.Parameters.Add((SqlParameter)objParm[i]);
}
SqlLog.OutPutParmLog(objParm);
}
SqlDataAdapter Adapter = new SqlDataAdapter();
Adapter.SelectCommand = cmd;
dtRecord = new DataTable();
Adapter.Fill(dtRecord);
}
catch (System.Exception objEx)
{
dtRecord = null;
throw objEx;
}
finally
{
if (cmd != null)
{
cmd.Connection.Close();
cmd.Dispose();
}
}
return(dtRecord);
}
/// <summary>
/// 执行SQL带参数
/// </summary>
/// <param name="conn"></param>
/// <param name="sql"></param>
/// <param name="objParams"></param>
/// <returns></returns>
public int ExecSql(string conn, string sql, int step, params IDataParameter[] objParm)
{
SqlCommand cmd = GetSqlCommand(conn, EnumExecType.ExecSql);
int affectedRows = 0;
try
{
cmd.CommandText = GetParm(sql, step);
if (objParm != null && objParm.Length > 0)
{
for (int i = 0; i < objParm.Length; i++)
{
if (objParm[i].Value == null)
{
objParm[i].Value = System.DBNull.Value;
}
((SqlParameter)objParm[i]).ParameterName = (i + 1 + step * 10).ToString();
cmd.Parameters.Add((SqlParameter)objParm[i]);
}
SqlLog.OutPutParmLog(objParm);
}
if (cmd.Connection.State == ConnectionState.Closed)
{
cmd.Connection.Open();
}
affectedRows = cmd.ExecuteNonQuery();
}
catch (System.Exception objEx)
{
affectedRows = -1;
throw objEx;
}
finally
{
if (cmd != null)
{
cmd.Connection.Close();
cmd.Dispose();
}
}
return(affectedRows);
}
public int ExecProc(OracleCommand cmd, string procName, params IDataParameter[] objParams)
{
int intAffectedRows = 0;
try
{
cmd.CommandText = GetParam(procName);
if (objParams != null)
{
for (int i = 0; i < objParams.Length; i++)
{
if (objParams[i].Value == null)
{
objParams[i].Value = System.DBNull.Value;
}
else if (objParams[i].Direction == ParameterDirection.Output && objParams[i].DbType == DbType.String)
{
((OracleParameter)objParams[i]).Size = 1000;
}
cmd.Parameters.Add((OracleParameter)objParams[i]);
}
}
SqlLog.OutPutParmLog(objParams);
if (cmd.Connection.State == ConnectionState.Closed)
{
cmd.Connection.Open();
}
intAffectedRows = cmd.ExecuteNonQuery();
}
catch (System.Exception objEx)
{
intAffectedRows = -1;
throw objEx;
}
return(intAffectedRows);
}
/// <summary>
/// 获取DataTable带参数
/// </summary>
/// <param name="conn"></param>
/// <param name="sql"></param>
/// <param name="dtRecord"></param>
/// <param name="objParams"></param>
/// <returns></returns>
public DataTable GetDataTable(string conn, string sql, params IDataParameter[] objParm)
{
DataTable dtRecord = null;
SqlCommand cmd = GetSqlCommand(conn, EnumExecType.ExecSql);
try
{
//cmd.CommandText = "set transaction isolation level read uncommitted";
//cmd.ExecuteNonQuery();
cmd.CommandText = GetParm(sql);
if (objParm != null && objParm.Length > 0)
{
for (int i = 0; i < objParm.Length; i++)
{
if (objParm[i].Value == null)
{
objParm[i].Value = System.DBNull.Value;
}
((SqlParameter)objParm[i]).ParameterName = (i + 1).ToString();
cmd.Parameters.Add((SqlParameter)objParm[i]);
}
SqlLog.OutPutParmLog(objParm);
}
SqlDataReader sqlReader = cmd.ExecuteReader();
dtRecord = new DataTable();
dtRecord.Load(sqlReader);
sqlReader.Close();
//int fieldCount = sqlReader.FieldCount;
//for (int i = 0; i < fieldCount; i++)
//{
// if (dtRecord.Columns.IndexOf(sqlReader.GetName(i)) > 0)
// dtRecord.Columns.Add(sqlReader.GetName(i) + i.ToString(), sqlReader.GetFieldType(i));
// else
// dtRecord.Columns.Add(sqlReader.GetName(i), sqlReader.GetFieldType(i));
//}
//dtRecord.BeginLoadData();
//object[] objValues = new object[fieldCount];
//while (sqlReader.Read())
//{
// sqlReader.GetValues(objValues);
// dtRecord.LoadDataRow(objValues, true);
//}
//sqlReader.Close();
//dtRecord.EndLoadData();
//SqlDataAdapter Adapter = new SqlDataAdapter();
//Adapter.SelectCommand = cmd;
//dtRecord = new DataTable();
//Adapter.Fill(dtRecord);
}
catch (System.Exception objEx)
{
dtRecord = null;
throw objEx;
}
finally
{
if (cmd != null)
{
cmd.Connection.Close();
cmd.Dispose();
}
}
return(dtRecord);
}
/// <summary>
/// 获取DataTable带参数
/// </summary>
/// <param name="conn"></param>
/// <param name="sql"></param>
/// <param name="dtRecord"></param>
/// <param name="objParams"></param>
/// <returns></returns>
public int GetDataTable(string conn, string sql, ref DataTable dtRecord, params IDataParameter[] objParams)
{
OracleCommand cmd = GetSqlCommand(conn, CommandType.Text);
int intAffectedRows = 0;
try
{
cmd.CommandText = GetParam(sql);
if (objParams != null)
{
for (int i = 0; i < objParams.Length; i++)
{
if (objParams[i].Value == null)
{
objParams[i].Value = System.DBNull.Value;
}
((OracleParameter)objParams[i]).ParameterName = (i + 1).ToString();
cmd.Parameters.Add((OracleParameter)objParams[i]);
}
}
SqlLog.OutPutParmLog(objParams);
OracleDataReader oraReader = cmd.ExecuteReader();
dtRecord = new DataTable();
dtRecord.Load(oraReader);
oraReader.Close();
//int fieldCount = oraReader.FieldCount;
//for (int i = 0; i < fieldCount; i++)
//{
// if (dtRecord.Columns.IndexOf(oraReader.GetName(i)) > 0)
// dtRecord.Columns.Add(oraReader.GetName(i) + i.ToString(), oraReader.GetFieldType(i));
// else
// dtRecord.Columns.Add(oraReader.GetName(i), oraReader.GetFieldType(i));
//}
//dtRecord.BeginLoadData();
//object[] objValues = new object[fieldCount];
//while (oraReader.Read())
//{
// oraReader.GetValues(objValues);
// dtRecord.LoadDataRow(objValues, true);
//}
//oraReader.Close();
//dtRecord.EndLoadData();
//OracleDataAdapter Adapter = new OracleDataAdapter();
//Adapter.SelectCommand = cmd;
//dtRecord = new DataTable();
//Adapter.Fill(dtRecord);
for (int i = 0; i < dtRecord.Columns.Count; i++)
{
dtRecord.Columns[i].ColumnName = dtRecord.Columns[i].ColumnName.ToLower();
}
}
catch (System.Exception objEx)
{
intAffectedRows = -1;
weCare.Core.Utils.ExceptionLog.OutPutException(objEx);
throw objEx;
}
finally
{
if (cmd != null)
{
cmd.Connection.Close();
cmd.Dispose();
}
}
return(intAffectedRows);
}