public int GetCount(string expr, List <ParamItem> param)
{
int count = 0;
IDbCommand command = null;
try
{
if (prepareCommand(ref expr, param, out command))
{
command.CommandTimeout = 120;
object o = command.ExecuteScalar();
if (o != null && !System.DBNull.Equals(o, null))
{
count = Convert.ToInt32(o);
}
}
}
catch (Exception ex)
{
throw new Exception("Executing SQL statement (" + expr + ") failed. Reason: " + ex.Message);
}
finally
{
DataBasePool.ReleaseConnection(mUsePoolName, command.Connection);
if (null != command)
{
command.Parameters.Clear();
command.Dispose();
command = null;
}
}
return(count);
}
public DataTable ExecProcedure(string procName, List <ParamItem> param, out Hashtable outAl)
{
validChecked(procName, param);
DataSet ds = new DataSet();
DataTable dt = new DataTable();
IDbCommand command = null;
IDataParameter[] dataParameters = initParameters(param);
outAl = new Hashtable();
//IDataReader reader = null;
IDataAdapter adapter = null;
IDbConnection conn = mPool.GetConnection(mUsePoolName);
try
{
if (null != conn && ConnectionState.Open == conn.State)
{
command = initCommand();
command.Connection = conn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = procName;
command.CommandTimeout = 120;
for (int i = 0; i < dataParameters.Length; i++)
{
command.Parameters.Add(dataParameters[i]);
if (dataParameters[i].Direction == ParameterDirection.InputOutput || dataParameters[i].Direction == ParameterDirection.Output)
{
outAl[dataParameters[i].SourceColumn.Replace("@", "")] = (dataParameters[i]);
}
}
adapter = initDataAdapter(command);
adapter.Fill(ds);
for (int i = 0; i < dataParameters.Length; i++)
{
if (dataParameters[i].Direction == ParameterDirection.InputOutput || dataParameters[i].Direction == ParameterDirection.Output)
{
outAl[dataParameters[i].SourceColumn.Replace("@", "")] = ((IDataParameter)outAl[dataParameters[i].SourceColumn.Replace("@", "")]).Value;
}
}
return(ds.Tables.Count > 0 ? ds.Tables[0] : null);
}
}
catch (Exception ex1)
{
throw new Exception("Executing procedure (" + procName + ") failed. Reason: " + ex1.Message);
}
finally
{
DataBasePool.ReleaseConnection(mUsePoolName, conn);
if (null != command)
{
command.Parameters.Clear();
command.Dispose();
command = null;
}
}
return(null);
}
/// <summary>
///
/// </summary>
/// <param name="sqlStr"></param>
/// <param name="param"></param>
/// <returns></returns>
public string MysqlReadBlob(string sqlStr, List <ParamItem> param)
{
validChecked(sqlStr, param);
IDbCommand command = null;
IDataParameter[] dataParameters = initParameters(param);
MySqlDataReader reader = null;
byte[] buffer = null;
StringBuilder content = null;
try
{
if (prepareCommand(ref sqlStr, param, out command))
{
content = new StringBuilder();
reader = (MySqlDataReader)command.ExecuteReader();
using (reader)
{
if (reader.HasRows)
{
reader.Read();
long len = reader.GetBytes(0, 0, null, 0, 0);
buffer = new byte[len];
len = reader.GetBytes(0, 0, buffer, 0, (int)len);
return(Encoding.UTF8.GetString(buffer));
}
}
}
}
catch (Exception ex1)
{
throw new Exception("Executing SQL statement (" + sqlStr + ") failed. Reason: " + ex1.Message);
}
finally
{
if (null != reader)
{
reader.Close();
reader.Dispose();
reader = null;
}
DataBasePool.ReleaseConnection(mUsePoolName, command.Connection);
if (null != command)
{
command.Parameters.Clear();
command.Dispose();
command = null;
}
}
return(null != content?content.ToString() : null);
}
public int ExecuteStatement(string expr, List <ParamItem> param)
{
int result = -1;
IDbCommand command = null;
IDbTransaction tran = null;
try
{
if (prepareCommand(ref expr, param, out command))
{
try
{
tran = command.Connection.BeginTransaction();
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
}
result = command.ExecuteNonQuery();
}
}
catch (Exception ex1)
{
throw new Exception("Executing SQL statement (" + expr + ") failed. Reason: " + ex1.Message);
}
finally
{
try
{
if (null != tran)
{
tran.Commit();
tran.Dispose();
tran = null;
}
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
}
DataBasePool.ReleaseConnection(mUsePoolName, command.Connection);
if (null != command)
{
command.Parameters.Clear();
command.Dispose();
command = null;
}
}
return(result);
}
/// <summary>
/// 获取数据文法，返回数据表对象
/// </summary>
/// <returns>
/// 返回的数据对象.
/// </returns>
/// <param name='expr'>
/// Expr Sql 表达式.
/// </param>
/// <param name='param'>
/// Parameter 参数列表.
/// </param>
public DataTable GetTable(string expr, List <ParamItem> param)
{
DataTable data = null;
IDbCommand command = null;
IDataReader reader = null;
try
{
if (prepareCommand(ref expr, param, out command))
{
command.CommandTimeout = 120;
reader = command.ExecuteReader();
if (null != reader)
{
data = new DataTable();
}
for (int i = 0; i < reader.FieldCount; i++)
{
data.Columns.Add(reader.GetName(i));
}
object o = null;
int count = reader.FieldCount;
while (reader.Read())
{
DataRow row = data.NewRow();
row.BeginEdit();
for (int i = 0; i < count; i++)
{
o = reader.GetValue(i);
row[i] = null != o?o.ToString().Replace(@"\0", "").Replace("\0", "").Replace("�", "") : null;
}
row.EndEdit();
data.Rows.Add(row);
}
}
else
{
throw new Exception("prepareCommand 发生异常，返回失败。");
}
}
//catch (OracleException ex1) {
// throw new Exception ("Executing SQL statement (" + expr + ") failed(OracleException). Reason: " + ex1.Message);
//}
catch (Exception ex2)
{
throw new Exception("Executing SQL statement (" + expr + ") failed(Exception). Reason: " + ex2.StackTrace);
}
finally
{
DataBasePool.ReleaseConnection(mUsePoolName, command.Connection);
if (null != reader)
{
reader.Close();
reader.Dispose();
reader = null;
}
if (null != command)
{
command.Parameters.Clear();
command.Dispose();
command = null;
}
}
return(data);
}
public void ReleaseConnection(IDbConnection conn)
{
DataBasePool.ReleaseConnection(mUsePoolName, conn);
}
public DataTable ExecProcedureByDataReader(string procName, List <ParamItem> param, out ArrayList outAl)
{
validChecked(procName, param);
DataSet ds = new DataSet();
DataTable dt = new DataTable();
IDbCommand command = null;
IDataParameter[] dataParameters = initParameters(param);
outAl = new ArrayList();
IDataReader reader = null;
IDbConnection conn = mPool.GetConnection(mUsePoolName);
try
{
if (null != conn && ConnectionState.Open == conn.State)
{
command = initCommand();
command.Connection = conn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = procName;
for (int i = 0; i < dataParameters.Length; i++)
{
command.Parameters.Add(dataParameters[i]);
if (dataParameters[i].Direction == ParameterDirection.InputOutput || dataParameters[i].Direction == ParameterDirection.Output)
{
outAl.Add(dataParameters[i]);
}
}
command.CommandTimeout = 120;
reader = command.ExecuteReader();
for (int i = 0; i < outAl.Count; i++)
{
outAl[i] = ((IDataParameter)outAl[i]).Value;
}
object o = null;
int count = reader.FieldCount;
for (int i = 0; i < count; i++)
{
dt.Columns.Add(reader.GetName(i));
}
while (reader.Read())
{
DataRow dr = dt.NewRow();
for (int i = 0; i < count; i++)
{
o = reader.GetValue(i);
dr[i] = (null != o ? o.ToString().Replace(@"\0", "").Replace("\0", "").Replace("�", "") : null);
}
dt.Rows.Add(dr);
}
return(dt);
}
}
catch (Exception ex1)
{
throw new Exception("Executing procedure (" + procName + ") failed. Reason: " + ex1.Message);
}
finally
{
if (null != reader)
{
reader.Close();
reader.Dispose();
reader = null;
}
DataBasePool.ReleaseConnection(mUsePoolName, conn);
if (null != command)
{
command.Parameters.Clear();
command.Dispose();
command = null;
}
}
return(null);
}
public ArrayList ExecProcedure(string procName, List <ParamItem> param)
{
validChecked(procName, param);
ArrayList l1 = new ArrayList();
IDbCommand command = null;
IDataParameter[] dataParameters = initParameters(param);
IDataReader reader = null;
IDbConnection conn = mPool.GetConnection(mUsePoolName);
if (null == conn)
{
//写异常
return(null);
}
try
{
if (null != conn && ConnectionState.Open == conn.State)
{
command = initCommand();
command.Connection = conn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = procName;
for (int i = 0; i < dataParameters.Length; i++)
{
command.Parameters.Add(dataParameters[i]);
}
command.CommandTimeout = 120;
reader = command.ExecuteReader();
object o = null;
int count = reader.FieldCount;
if (reader.Read())
{
for (int i = 0; i < count; i++)
{
o = reader.GetValue(i);
l1.Add(null != o ? o.ToString().Replace(@"\0", "").Replace("\0", "").Replace("�", "") : null);
}
}
}
}
catch (Exception ex1)
{
throw new Exception("Executing procedure (" + procName + ") failed. Reason: " + ex1.Message);
}
finally
{
if (null != reader)
{
reader.Close();
reader.Dispose();
reader = null;
}
DataBasePool.ReleaseConnection(mUsePoolName, conn);
if (null != command)
{
command.Parameters.Clear();
command.Dispose();
command = null;
}
}
return(l1);
}