/// <summary>
/// 根据存储过程获取DataTable
/// </summary>
/// <param name="ProcName"></param>
/// <param name="paras"></param>
/// <returns></returns>
public DataTable GetTableByProc(string ProcName, CmdParameter[] paras)
{
OracleParameter[] pas = CFunctions.ConvertToOraParameter(paras);
DataTable dt = new DataTable();;
using (OracleConnection conn = new OracleConnection(strConn))
{
using (OracleDataAdapter da = new OracleDataAdapter(ProcName, conn))
try
{
da.SelectCommand.CommandType = CommandType.StoredProcedure;
if (pas != null)
{
da.SelectCommand.Parameters.AddRange(pas);
}
//da.SelectCommand.Parameters.Add(new OracleParameter("PSQL",OracleType.VarChar,500));
//da.SelectCommand.Parameters["PSQL"].Value = " 1=1 ";
//da.SelectCommand.Parameters["PSQL"].Direction = ParameterDirection.Input;
//da.SelectCommand.Parameters.Add(new OracleParameter("v_cur", OracleType.Cursor));
//da.SelectCommand.Parameters["v_cur"].Direction = ParameterDirection.Output;
da.Fill(dt);
dt.TableName = ProcName;
}
catch (Exception ex)
{
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
throw (ex);
}
}
return(dt);
}
public OracleLob getOdb(OracleConnection conn, OracleTransaction tx, object imgBytes)
{
try
{
byte[] imgByte = (byte[])imgBytes;
OracleCommand cmd = conn.CreateCommand();
cmd.Transaction = tx;
//这里是关键，他定义了一个命令对象的t-sql语句，通过dmbs_lob来创建一个临时对象，这个对象的类型为blob，并存放在变量xx中，然后将xx的值付给外传参数tmpblob
cmd.CommandText = "declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;";
//构造外传参数对象，并加入到命令对象的参数集合中
cmd.Parameters.Add(new OracleParameter("tempblob", OracleType.Blob)).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
//构造OracleLob对象，他的值为tmpblob外传参数的值
OracleLob tempLob = (OracleLob)cmd.Parameters[0].Value;
//指定tempLob的访问模式，并开始操作二进制数据
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
//将二进制流byte数组集合写入到tmpLob里
tempLob.Write(imgByte, 0, imgByte.Length);
tempLob.EndBatch();
return(tempLob);
}
catch (Exception ex)
{
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
return(null);
}
}
/// <summary>
/// 将自定义的CmdParameter类型转为OracleParameter类型
/// </summary>
/// <param name="prams"></param>
/// <returns></returns>
public static OracleParameter[] ConvertToOraParameter(CmdParameter[] prams)
{
if (prams == null)
{
return(null);
}
OracleParameter[] sqlPrams = new OracleParameter[prams.Length];
try
{
for (int i = 0; i < prams.Length; i++)
{
OracleParameter sqlPram = new OracleParameter(prams[i].ParameterName, prams[i].Value);
sqlPrams[i] = sqlPram;
if (prams[i].size != 0)
{
sqlPrams[i].Size = prams[i].size;
}
sqlPrams[i].OracleType = prams[i].oracle_type;
sqlPrams[i].Direction = prams[i].paramDirection;
// sqlPrams[i].Direction = (ParameterDirection)Enum.Parse(typeof(ParameterDirection), prams[i].Direction.ToString(), true);// ParameterDirection.Output;
}
}
catch (Exception ex)
{
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
return(sqlPrams);
}
/// <summary>
/// 往数据库中批量插入数据
/// </summary>
/// <param name="sourceDt">数据源表</param>
/// <param name="targetTable">服务器上目标表</param>
public void BulkToDB(DataTable sourceDt, string targetTable)
{
SqlConnection conn = new SqlConnection(strConn);
SqlBulkCopy bulkCopy = new SqlBulkCopy(conn); //用其它源的数据有效批量加载sql server表中
bulkCopy.DestinationTableName = targetTable; //服务器上目标表的名称
bulkCopy.BatchSize = sourceDt.Rows.Count; //每一批次中的行数
try
{
conn.Open();
if (sourceDt != null && sourceDt.Rows.Count != 0)
{
bulkCopy.WriteToServer(sourceDt); //将提供的数据源中的所有行复制到目标表中
}
}
catch (Exception ex)
{
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
throw ex;
}
finally
{
conn.Close();
if (bulkCopy != null)
{
bulkCopy.Close();
}
}
}
public DataSet GetDataSetByProc(string ProcName, CmdParameter[] parms)
{
SqlParameter[] pas = CFunctions.ConvertToSqlParameter(parms);
DataSet dt = new DataSet();;
using (SqlConnection conn = new SqlConnection(strConn))
{
using (SqlDataAdapter da = new SqlDataAdapter(ProcName, conn))
try
{
da.SelectCommand.CommandType = CommandType.StoredProcedure;
if (pas != null)
{
da.SelectCommand.Parameters.AddRange(pas);
}
da.Fill(dt);
//command.Parameters["ReturnValue"].Value;
}
catch (Exception ex)
{
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name + ":" + ProcName, ex);
throw (ex);
}
}
return(dt);
}
/// <summary>
/// 执行SQL语句，返回第一行，第一列
/// </summary>
/// <param name="strSQL">要执行的SQL语句</param>
/// <param name="paras">参数列表，没有参数填入null</param>
/// <returns>返回影响行数</returns>
public int ExcuteScalarSQL(string strSQL, SqlParameter[] paras, CommandType cmdType)
{
int i = 0;
using (SqlConnection conn = new SqlConnection(strConn))
{
using (SqlCommand cmd = new SqlCommand(strSQL, conn))
{
try
{
cmd.CommandType = cmdType;
if (paras != null)
{
cmd.Parameters.AddRange(paras);
}
conn.Open();
i = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
}
catch (Exception ex)
{
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex); throw (ex);
}
}
}
return(i);
}
/// <summary>
/// 执行SQL语句，返回首行首列
/// </summary>
/// <param name="strSQL">要执行的SQL语句</param>
/// <param name="paras">参数列表，没有参数填入null</param>
/// <returns>返回的首行首列</returns>
public object GetObject(string strSQL, SqlParameter[] paras, CommandType cmdtype)
{
object o = null;
using (SqlConnection conn = new SqlConnection(strConn))
{
using (SqlCommand cmd = new SqlCommand(strSQL, conn))
try
{
cmd.CommandType = cmdtype;
if (paras != null)
{
cmd.Parameters.AddRange(paras);
}
conn.Open();
o = cmd.ExecuteScalar();
conn.Close();
}
catch (Exception ex)
{
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
throw (ex);
}
}
return(o);
}
public DataTable GetTableByProc(string ProcName, SqlParameter[] pas)
{
DataTable dt = new DataTable();;
using (SqlConnection conn = new SqlConnection(strConn))
{
using (SqlDataAdapter da = new SqlDataAdapter(ProcName, conn))
try
{
da.SelectCommand.CommandType = CommandType.StoredProcedure;
if (pas != null)
{
da.SelectCommand.Parameters.AddRange(pas);
}
dt.TableName = ProcName;
da.Fill(dt);
da.SelectCommand.Parameters.Clear();
}
catch (Exception ex)
{
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
throw (ex);
}
}
return(dt);
}
/// <summary>
/// 执行查询，返回DataSet对象
/// </summary>
/// <param name="strSQL">sql语句</param>
/// <param name="pas">参数数组</param>
/// <param name="cmdtype">Command类型</param>
/// <returns>DataSet对象</returns>
public DataSet GetDataSet(string strSQL, SqlParameter[] pas, CommandType cmdtype)
{
DataSet dt = new DataSet();;
using (SqlConnection conn = new SqlConnection(strConn))
{
using (SqlDataAdapter da = new SqlDataAdapter(strSQL, conn))
try
{
da.SelectCommand.CommandType = cmdtype;
if (pas != null)
{
da.SelectCommand.Parameters.AddRange(pas);
}
da.Fill(dt);
//command.Parameters["ReturnValue"].Value;
}
catch (Exception ex) {
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
throw (ex);
}
}
return(dt);
}
public static MySqlParameter[] ConvertToMySqlParameter(CmdParameter[] prams)
{
if (prams == null)
{
return(null);
}
MySqlParameter[] sqlPrams = new MySqlParameter[prams.Length];
try
{
for (int i = 0; i < prams.Length; i++)
{
MySqlParameter sqlPram = new MySqlParameter(prams[i].ParameterName, prams[i].Value);
sqlPrams[i] = sqlPram;
if (prams[i].size != 0)
{
sqlPrams[i].Size = prams[i].size;
}
sqlPrams[i].MySqlDbType = prams[i].mySql_Type;
sqlPrams[i].Direction = prams[i].paramDirection;
}
}
catch (Exception ex)
{
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
return(sqlPrams);
}
/// <summary>
/// 执行非查询存储过程和SQL语句
/// 增、删、改
/// </summary>
/// <param name="strSQL">要执行的SQL语句</param>
/// <param name="paras">参数列表，没有参数填入null</param>
/// <param name="cmdType">Command类型</param>
/// <returns>返回影响行数</returns>
public int ExcuteSQL(string strSQL, SqlParameter[] paras, CommandType cmdType)
{
int i = 0;
using (SqlConnection conn = new SqlConnection(strConn))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
using (SqlCommand cmd = new SqlCommand(strSQL, conn))
try
{
cmd.CommandType = cmdType;
if (paras != null)
{
cmd.Parameters.AddRange(paras);
}
cmd.Transaction = trans;
// conn.Open();
i = cmd.ExecuteNonQuery();
trans.Commit();
conn.Close();
}
catch (Exception ex)
{
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
trans.Rollback();
throw (ex);
}
}
}
return(i);
}
public int ExecuteSqlTran(List <string> SQLStringList)
{
using (SqlConnection conn = new SqlConnection(strConn))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
//循环
foreach (string sql in SQLStringList)
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
int val = cmd.ExecuteNonQuery();
// cmd.Parameters.Clear();
}
trans.Commit();
return(1);
}
catch (Exception ex)
{
trans.Rollback();
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
return(0);
//throw;
}
}
}
}
/// <summary>
/// 根据存储过程获取DataTable，参数的修改会被返回
/// </summary>
/// <param name="ProcName"></param>
/// <param name="paras"></param>
/// <returns></returns>
public DataTable GetTableByProc(string ProcName, ref CmdParameter[] paras)
{
OracleParameter[] pas = CFunctions.ConvertToOraParameter(paras);
DataTable dt = new DataTable();;
using (OracleConnection conn = new OracleConnection(strConn))
{
using (OracleDataAdapter da = new OracleDataAdapter(ProcName, conn))
try
{
da.SelectCommand.CommandType = CommandType.StoredProcedure;
if (pas != null)
{
da.SelectCommand.Parameters.AddRange(pas);
}
da.Fill(dt);
dt.TableName = ProcName;
paras = CFunctions.RecoverParameter(pas);
}
catch (Exception ex)
{
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
throw (ex);
}
}
return(dt);
}
/// <summary>
/// 根据存储过程获取DataTable，参数的修改会被返回
/// </summary>
/// <param name="ProcName"></param>
/// <param name="parms"></param>
/// <returns></returns>
public DataTable GetTableByProc(string ProcName, ref CmdParameter[] parms)
{
SqlParameter[] pas = CFunctions.ConvertToSqlParameter(parms);
DataTable dt = new DataTable();;
using (SqlConnection conn = new SqlConnection(strConn))
{
using (SqlDataAdapter da = new SqlDataAdapter(ProcName, conn))
{
try
{
da.SelectCommand.CommandType = CommandType.StoredProcedure;
if (pas != null)
{
da.SelectCommand.Parameters.AddRange(pas);
}
dt.TableName = ProcName;
da.Fill(dt);
da.SelectCommand.Parameters.Clear();
}
catch (Exception ex)
{
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name + ":" + ProcName, ex);
throw (ex);
}
try
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = ProcName;
cmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter sp in pas)
{
cmd.Parameters.Add(sp);
}
cmd.ExecuteNonQuery();
for (int i = 0; i < pas.Length; i++)
{
pas[i] = cmd.Parameters[i];
}
parms = CFunctions.RecoverParameter(pas);
}
catch (Exception ex) {
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
// throw (ex);
}
}
}
return(dt);
}
/// <summary>
/// 执行存储过程，带参数,用于增删改
/// </summary>
/// <param name="ProcName">存储过程名称</param>
/// <param name="pars">参数集合</param>
/// <returns></returns>
public int ExcuteProc(string ProcName, CmdParameter[] paras)
{
OracleParameter[] pars = CFunctions.ConvertToOraParameter(paras);
//CFunctions.HandleBlobParam( ref pars);
using (OracleConnection connection = new OracleConnection(strConn))
{
int result;
try
{
connection.Open();
OracleTransaction tx = connection.BeginTransaction();
OracleCommand command = new OracleCommand(ProcName, connection);
command.Transaction = tx;
command.CommandType = CommandType.StoredProcedure;
foreach (OracleParameter parameter in pars)
{
if (parameter.OracleType == OracleType.Blob && parameter.Value != null && !string.IsNullOrEmpty(parameter.Value.ToString()))
{
parameter.Value = getOdb(connection, tx, parameter.Value);
}
command.Parameters.Add(parameter);
}
//添加一个名为ReturnValue的Output参数，用于返回值
command.Parameters.Add(new OracleParameter("ReturnValue", OracleType.Int32, 4, ParameterDirection.Output,
false, 0, 0, string.Empty, DataRowVersion.Default, null));
//command = BuildIntCommand(connection, ProcName, pars);
command.ExecuteNonQuery();
tx.Commit();
result = (int)command.Parameters["ReturnValue"].Value;
}
catch (Exception ex)
{
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
throw ex;
}
//Connection.Close();
return(result);
}
}
// <summary>
/// 执行查询语句，返回DataTable ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
/// </summary>
/// <param name="strSQL">查询语句</param>
public DataTable getDataBySql(string strSQL)
{
DataTable dt = new DataTable();;
using (OracleConnection conn = new OracleConnection(strConn))
{
using (OracleDataAdapter da = new OracleDataAdapter(strSQL, conn))
try
{
da.SelectCommand.CommandType = CommandType.Text;
da.Fill(dt);
dt.TableName = "tabname";
}
catch (Exception ex)
{
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
throw (ex);
}
}
return(dt);
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="ProcName">存储过程名称</param>
/// <returns></returns>
public int ExcuteProc(string ProcName)
{
using (OracleConnection connection = new OracleConnection(strConn))
{
int result = 0;
try
{
connection.Open();
OracleCommand command = BuildIntCommand(connection, ProcName, null);
command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
}
catch (Exception ex)
{
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
throw ex;
}
//Connection.Close();
return(result);
}
}
public int ExecuteSqlTran(List <String> SQLStringList)
{
using (OracleConnection conn = new OracleConnection(strConn))
{
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (string sql in SQLStringList)
{
if (!String.IsNullOrEmpty(sql))
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
return(1);
}
catch (System.Data.OracleClient.OracleException ex)
{
tx.Rollback();
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
throw ex;
return(0);
//throw new Exception(E.Message);
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
//return 0;
}
}
}
/// <summary>
/// 将SqlParameter类型转换为CmdParameter
/// </summary>
/// <param name="prams"></param>
/// <returns></returns>
private CmdParameter[] RecoverOraParameter(OracleParameter[] prams)
{
if (prams == null)
{
return(null);
}
CmdParameter[] sqlPrams = new CmdParameter[prams.Length];
try
{
for (int i = 0; i < prams.Length; i++)
{
CmdParameter sqlPram = new CmdParameter(prams[i].ParameterName, prams[i].Value);
sqlPrams[i] = sqlPram;
//sqlPrams[i].Direction = prams[i].Direction.ToString();// ParameterDirection.Output;
}
}
catch (Exception ex)
{
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
return(sqlPrams);
}
public DataSet GetDataSetByProc(string ProcName, CmdParameter[] paras)
{
OracleParameter[] pas = CFunctions.ConvertToOraParameter(paras);
using (OracleConnection connection = new OracleConnection(strConn))
{
DataSet dataSet = new DataSet();
try
{
connection.Open();
OracleDataAdapter sqlDA = new OracleDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, ProcName, pas);
sqlDA.Fill(dataSet, ProcName);
connection.Close();
}
catch (Exception ex)
{
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
throw (ex);
}
return(dataSet);
}
}
/// <summary>
/// 将自定义的CmdParameter类型转为SqlParameter类型
/// </summary>
/// <param name="prams"></param>
/// <returns></returns>
public static SqlParameter[] ConvertToSqlParameter(CmdParameter[] prams)
{
if (prams == null)
{
return(null);
}
SqlParameter[] sqlPrams = new SqlParameter[prams.Length];
try
{
for (int i = 0; i < prams.Length; i++)
{
SqlParameter sqlPram = new SqlParameter(prams[i].ParameterName, prams[i].Value);
sqlPrams[i] = sqlPram;
sqlPrams[i].SqlDbType = prams[i].sql_Type;
sqlPrams[i].Direction = prams[i].paramDirection;; //;(ParameterDirection)Enum.Parse(typeof(ParameterDirection), prams[i].Direction.ToString(), true);// ParameterDirection.Output;
}
}
catch (Exception ex)
{
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
return(sqlPrams);
}
public static CmdParameter[] RecoverParameter(object[] prams)
{
if (prams == null)
{
return(null);
}
CmdParameter[] sqlPrams = new CmdParameter[prams.Length];
try
{
for (int i = 0; i < prams.Length; i++)
{
if (prams[i] is SqlParameter)
{
CmdParameter sqlPram = new CmdParameter((prams[i] as SqlParameter).ParameterName, (prams[i] as SqlParameter).Value);
sqlPrams[i] = sqlPram;
}
else if (prams[i] is OracleParameter)
{
CmdParameter sqlPram = new CmdParameter((prams[i] as OracleParameter).ParameterName, (prams[i] as OracleParameter).Value);
sqlPrams[i] = sqlPram;
}
else if (prams[i] is MySqlParameter)
{
CmdParameter sqlPram = new CmdParameter((prams[i] as MySqlParameter).ParameterName, (prams[i] as MySqlParameter).Value);
sqlPrams[i] = sqlPram;
}
//sqlPrams[i].Direction = prams[i].Direction.ToString();// ParameterDirection.Output;
}
}
catch (Exception ex)
{
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
return(sqlPrams);
}