public System.Data.DataTable GetTableByProc(string ProcName, CmdParameter[] parms)
{
MySqlParameter[] pas = CFunctions.ConvertToMySqlParameter(parms);
DataTable dt = new DataTable();
using (MySqlConnection conn = new MySqlConnection(strConn))
{
using (MySqlDataAdapter da = new MySqlDataAdapter(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);
}
}
}
return(dt);
}
/// <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, MySqlParameter[] paras, CommandType cmdType)
{
int i = 0;
using (MySqlConnection conn = new MySqlConnection(strConn))
{
conn.Open();
using (MySqlTransaction trans = conn.BeginTransaction())
{
using (MySqlCommand cmd = new MySqlCommand(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 (MySqlConnection conn = new MySqlConnection(strConn))
{
conn.Open();
using (MySqlTransaction trans = conn.BeginTransaction())
{
MySqlCommand cmd = new MySqlCommand();
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;
}
}
}
}
public System.Data.DataTable GetTableByProc(string ProcName, ref CmdParameter[] parms)
{
MySqlParameter[] pas = CFunctions.ConvertToMySqlParameter(parms);
DataTable dt = new DataTable();
using (MySqlConnection conn = new MySqlConnection(strConn))
{
using (MySqlDataAdapter da = new MySqlDataAdapter(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();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = ProcName;
cmd.CommandType = CommandType.StoredProcedure;
foreach (MySqlParameter 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>
/// 返回一个CmdParameter类型的对象
/// </summary>
/// <param name="paraName"></param>
/// <param name="Value"></param>
/// <param name="paramtype"></param>
/// <param name="dbtype"></param>
/// <param name="size"></param>
/// <param name="paramDirection"></param>
/// <returns></returns>
public CmdParameter CmdParameter(string paraName, object Value, string paramtype, string dbtype, int size, ParameterDirection paramDirection)
{
try
{
CmdParameter cmp = new CmdParameter(paraName, Value, paramtype, dbtype, size, paramDirection);
return(cmp);
}
catch (Exception ex)
{
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
return(null);
}
/// <summary>
/// 创建CmdParameter类型的带参构造函数，因为WCF不支持带参数的构造函数，所以放在这里创建
/// </summary>
/// <param name="paraName"></param>
/// <param name="Value"></param>
/// <returns></returns>
public CmdParameter CmdParameter(string paraName, object Value)
{
CmdParameter cmp = new CmdParameter();
try
{
cmp.ParameterName = paraName;
cmp.Value = Value;
}
catch (Exception ex)
{
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
return(cmp);
}
/// <summary>
/// 将SqlParameter类型转换为CmdParameter
/// </summary>
/// <param name="prams"></param>
/// <returns></returns>
private CmdParameter[] RecoverParameter(SqlParameter[] 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;// ParameterDirection.Output;
}
}
catch (Exception ex)
{
CFunctions.HandleException(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
}
return(sqlPrams);
}
/// <summary>
/// 将自定义的CmdParameter类型转换为SqlParameter
/// </summary>
/// <param name="prams"></param>
/// <returns></returns>
private SqlParameter[] ConvertParameter(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].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);
}