public static DataTable GetDataTable(DbConnection conn, CommandType cmdType, string cmdText,
params DbParameter[] commandParameters)
{
if (conn == null)
{
throw new ArgumentNullException("conn");
}
if (string.IsNullOrEmpty(cmdText))
{
throw new ArgumentNullException("cmdText");
}
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
var cmd = DBFactory.NewCommand(conn);
cmd.CommandType = cmdType;
cmd.CommandText = cmdText;
if (commandParameters != null)
{
foreach (var param in commandParameters)
{
cmd.Parameters.Add(param);
}
}
var da = DBFactory.NewDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds); //填充数据
cmd.Parameters.Clear();
conn.Close();
if (ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
else
{
return(null);
}
}
catch (Exception ex)
{
EventLogger.Log("SqlHelper.GetDataSet 【" + cmdText + "】", ex);
RefreshSqlConnection();
return(null);
}
}
public static string ExecuteNonQuerys(DbConnection conn, EmDbType dbType, List <string> sqlList)
{
if (conn == null)
{
throw new ArgumentNullException("conn");
}
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
var cmd = DBFactory.NewCommand(dbType, conn);
using (var trans = conn.BeginTransaction())
{
cmd.Transaction = trans;
try
{
foreach (string sql in sqlList)
{
if (string.IsNullOrWhiteSpace(sql))
{
continue;
}
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception ex)
{
EventLogger.Log("SqlHelper.ExecuteNonQuerys SQL【" + string.Join(";", sqlList.ToArray()) + "】 ", ex);
trans.Rollback(); //回滚
OpenConnection(dbType, conn); //重新连接
return(ex.Message);
}
}
conn.Close();
return("OK");
}
catch (Exception ex)
{
EventLogger.Log("SqlHelper.ExecuteNonQuery 错误.", ex);
OpenConnection(dbType, conn); //重新连接
return(ex.Message);
}
}
public static string GetFirstValue(DbConnection conn, CommandType cmdType, string cmdText,
params DbParameter[] commandParameters)
{
if (conn == null)
{
throw new ArgumentNullException("conn");
}
if (string.IsNullOrEmpty(cmdText))
{
throw new ArgumentNullException("cmdText");
}
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
var cmd = DBFactory.NewCommand(conn);
cmd.CommandType = cmdType;
cmd.CommandText = cmdText;
if (commandParameters != null)
{
foreach (var param in commandParameters)
{
cmd.Parameters.Add(param);
}
}
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
conn.Close();
return(val?.ToString());
}
catch (Exception ex)
{
EventLogger.Log("SqlHelper.GetFirstValue 【" + cmdText + "】", ex);
RefreshSqlConnection();
return(null);
}
}
/// <summary>
///
/// </summary>
/// <param name="conn"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="commandParameters"></param>
/// <returns>OK；错误信息</returns>
public static string ExecuteNonQuery(DbConnection conn, CommandType cmdType, string cmdText,
params DbParameter[] commandParameters)
{
if (conn == null)
{
throw new ArgumentNullException("conn");
}
if (string.IsNullOrEmpty(cmdText))
{
throw new ArgumentNullException("cmdText");
}
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
var cmd = DBFactory.NewCommand(conn);
cmd.CommandType = cmdType;
cmd.CommandText = cmdText;
if (commandParameters != null)
{
foreach (var param in commandParameters)
{
cmd.Parameters.Add(param);
}
}
cmd.ExecuteNonQuery(); //执行
cmd.Parameters.Clear();
conn.Close();
return("OK");
}
catch (Exception ex)
{
EventLogger.Log($"SqlServerHelper.ExecuteNonQuery SQL[{cmdText}] 错误.", ex);
RefreshSqlConnection();
return(ex.Message);
}
}
public static DbDataReader ExecuterReader(string connectionString, CommandType cmdType, string cmdText,
params DbParameter[] commandParameters)
{
if (string.IsNullOrEmpty(connectionString))
{
throw new ArgumentNullException("connectionString");
}
if (string.IsNullOrEmpty(cmdText))
{
throw new ArgumentNullException("cmdText");
}
try
{
var conn = DBFactory.NewConnection(connectionString);
conn.Open();
var cmd = DBFactory.NewCommand(conn);
cmd.CommandType = cmdType;
cmd.CommandText = cmdText;
if (commandParameters != null)
{
foreach (var param in commandParameters)
{
cmd.Parameters.Add(param);
}
}
var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return(dr);
}
catch (Exception ex)
{
EventLogger.Log("SqlHelper.ExecuterReader 【" + cmdText + "】", ex);
RefreshSqlConnection();
return(null);
}
}
