///// <summary>
///// 返回DataTable,参数带有状态码的存储过程 oracle的存储过程参数必须定义输出参数rcode和dt游标类型，且名字不能改
///// </summary>
///// <param name="cmdText"></param>
///// <param name="rcode">返回码</param>
///// <param name="parameters"></param>
///// <returns></returns>
//public static DataTable ExecuteDataTableByProcSQL(string cmdText, ref int rcode, params MySqlParameter[] parameters)
//{
// DataTable dt = new DataTable();
// using (MySqlConnection conn = new MySqlConnection(connstr))
// {
// using (MySqlCommand cmd = new MySqlCommand(cmdText, conn))
// {
// try
// {
// conn.Open();
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.AddRange(parameters);
// cmd.Parameters.Add(new MySqlParameter("rcode", MySqlDbType.Int32));
// cmd.Parameters["rcode"].Direction = ParameterDirection.Output;
// cmd.Parameters.Add(new MySqlParameter("dt", MySqlDbType.c));
// cmd.Parameters["dt"].Direction = ParameterDirection.Output;
// //这里没有@符号：区别SqlServer的写法,Sql中返回结果集可以直接使用select语句,
// //而Orcale中返回结果集要有游标
// MySqlDataAdapter da = new MySqlDataAdapter(cmd);
// da.Fill(dt);
// rcode = Convert.ToInt32(cmd.Parameters["rcode"].Value);
// }
// catch (MySqlException ex)
// {
// log.WriteLog(ex);
// return null;
// }
// }
// }
// return dt;
//}
/// <summary>
/// 调用存储过程返回DataTable,输出参数为dt的游标类型
/// </summary>
/// <param name="cmdText"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static DataTable ExecuteDataTableByProcSQL(string cmdText, params MySqlParameter[] parameters)
{
DataTable dt = new DataTable();
using (MySqlConnection conn = new MySqlConnection(connstr))
{
using (MySqlCommand cmd = new MySqlCommand(cmdText, conn))
{
try
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
if (!parameters.IsNullEmpty())
{
cmd.Parameters.AddRange(parameters);
}
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
da.Fill(dt);
}
catch (MySqlException ex)
{
DbLog.WriteLog(ex);
return(null);
}
}
}
return(dt);
}
/// <summary>
/// 执行存储过程返回一系列字符 输出参数必须定义为o_resStr
/// </summary>
/// <param name="cmdText"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static string ExecuteStringByProcSQL(string cmdText, params MySqlParameter[] parameters)
{
string str = "";
using (MySqlConnection conn = new MySqlConnection(connstr))
{
using (MySqlCommand cmd = new MySqlCommand(cmdText, conn))
{
try
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(parameters);
cmd.Parameters.Add(new MySqlParameter("o_resStr", MySqlDbType.VarChar, 4000)); //这里需要设置长度
cmd.Parameters["o_resStr"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery(); //执行成功返回默认影响行数为1
str = Convert.ToString(cmd.Parameters["o_resStr"].Value);
}
catch (MySqlException ex)
{
DbLog.WriteLog(ex);
return(ex.Message.ToString());
}
}
}
return(str);
}
// connectionString="Server=localhost;Database=drivetop_base; User=otnp80;Password=123;Use Procedure Bodies=false;Charset=utf8;Allow Zero Datetime=True; Pooling=True; Max Pool Size=50; "
#region 基本操作
/// <summary>
/// 查询返回DataTable
/// </summary>
/// <param name="cmdText"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string cmdText, params MySqlParameter[] parameters)
{
using (MySqlConnection conn = new MySqlConnection(connstr))
{
try
{
using (MySqlCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(parameters);
using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
adapter.Fill(dt);
cmd.Parameters.Clear();
return(dt);
}
}
}
catch (MySqlException e)
{
DbLog.WriteLog(e);
return(null);
}
}
}
/// <summary>
/// 批量执行多条SQL语句，实现数据库事务,不好用 没考虑特殊字符。
/// </summary>
/// <param name="SQLStringList">List集合中是多条SQL语句</param>
public static int ExecuteSqlTran(List <String> SQLStringList)
{
using (MySqlConnection conn = new MySqlConnection(connstr))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
MySqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n];
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
return(count);
}
catch (MySqlException e)
{
DbLog.WriteLog(e);
tx.Rollback();
return(0);
}
}
}
/// <summary>
/// 返回首行首列--与ExecuteScalar一样
/// </summary>
/// <param name="SQLString"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static object GetSingle(string SQLString, params MySqlParameter[] parameters)
{
using (MySqlConnection conn = new MySqlConnection(connstr))
{
using (MySqlCommand cmd = new MySqlCommand(SQLString, conn))
{
try
{
conn.Open();
cmd.Parameters.AddRange(parameters);
object obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, DBNull.Value)))
{
return(null);
}
else
{
return(obj);
}
}
catch (Exception e)
{
conn.Close();
DbLog.WriteLog(e);
return(null);
}
}
}
}
/// <summary>
/// //批量执行sql，实现事务 返回>=1 成功
/// </summary>
/// <param name="sqls">多条sql</param>
/// <param name="param">sql参数化</param>
/// <returns></returns>
public static int BatchExecuteBySql(object[] sqls, object[] param)
{
int num = 0;
using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int i = 0; i < sqls.Length; i++)
{
StringBuilder builder = (StringBuilder)sqls[i];
if (builder != null)
{
cmd.CommandText = builder.ToString();
if (param != null)
{
SqlParameter[] paramArray = (SqlParameter[])param[i];
cmd.Parameters.AddRange(paramArray);
}
num += cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (Exception e)
{
num = 0;
DbLog.WriteLog(e);
tx.Rollback();
}
finally
{
conn.Close();
conn.Dispose();
tx.Dispose();
}
}
return(num);
}
public static MySqlDataReader ExecuteDataReader(string cmdText, params MySqlParameter[] parameters)//这里先不用using 调用转换时使用了using
{
try
{
MySqlConnection conn = new MySqlConnection(connstr);
MySqlCommand cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(parameters);
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return(dr);
}
catch (MySqlException ex)
{
DbLog.WriteLog(ex);
return(null);
}
}
/// <summary>
/// 返回影响的行数
/// </summary>
/// <param name="cmdText"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string cmdText, params MySqlParameter[] parameters)
{
using (MySqlConnection conn = new MySqlConnection(connstr))
{
using (MySqlCommand cmd = conn.CreateCommand())
{
try
{
conn.Open();
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(parameters);
int result = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return(result);
}
catch (MySqlException e)
{
DbLog.WriteLog(e);
return(-1);
}
}
}
}