using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
using System.IO;
|
using System.Data;
|
using System.Data.SqlClient;
|
|
namespace DAL
|
{
|
public class SQLHelpers
|
{
|
|
/***连接数据库****/
|
public static string connectionString = "User ID=HX_User;Password=lc@841022;Data Source=.;Initial Catalog=HX_APSsys_WDR";
|
// PublicFunc publicFunc = new PublicFunc();
|
#region 执行简单SQL语句
|
public static SqlDataReader GetReader(string sql)
|
{
|
SqlConnection conn = new SqlConnection(connectionString);
|
SqlCommand cmd = new SqlCommand(sql, conn);
|
try
|
{
|
conn.Open();
|
SqlDataReader objReader =
|
cmd.ExecuteReader(CommandBehavior.CloseConnection);
|
return objReader;
|
}
|
catch (Exception ex)
|
{
|
conn.Close();
|
throw ex;
|
}
|
}
|
public static SqlDataReader GetReader(string sql, SqlParameter[] param)
|
{
|
SqlConnection conn = new SqlConnection(connectionString);
|
SqlCommand cmd = new SqlCommand(sql, conn);
|
try
|
{
|
conn.Open();
|
cmd.Parameters.AddRange(param);
|
SqlDataReader objReader =
|
cmd.ExecuteReader(CommandBehavior.CloseConnection);
|
return objReader;
|
}
|
catch (Exception ex)
|
{
|
conn.Close();
|
throw ex;
|
}
|
}
|
/// <summary>
|
/// 执行SQL语句，返回影响的记录数
|
/// </summary>
|
/// <param name="SQLString">SQL语句</param>
|
/// <returns>影响的记录数</returns>
|
public static int ExecuteSql(string SQLString)
|
{
|
using (SqlConnection connection = new SqlConnection(connectionString))
|
{
|
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
|
{
|
try
|
{
|
connection.Open();
|
int rows = cmd.ExecuteNonQuery();
|
return rows;
|
}
|
catch (System.Data.SqlClient.SqlException e)
|
{
|
|
connection.Close();
|
throw e;
|
|
}
|
}
|
}
|
}
|
/// <summary>
|
/// 查询单一结果
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <returns></returns>
|
public static object GetSingleResult(string sql)
|
{
|
SqlConnection conn = new SqlConnection(connectionString);
|
SqlCommand cmd = new SqlCommand(sql, conn);
|
try
|
{
|
conn.Open();
|
return cmd.ExecuteScalar();
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
public static object GetSingleResult(string sql, SqlParameter[] param)
|
{
|
SqlConnection conn = new SqlConnection(connectionString);
|
SqlCommand comm = new SqlCommand(sql, conn);
|
try
|
{
|
conn.Open();
|
comm.Parameters.AddRange(param);
|
return comm.ExecuteScalar();
|
}
|
catch (Exception ex)
|
{
|
WriteLog(ex.Message);
|
throw new Exception("在调用public static object GetSingleResult(string sql,SqlParameter[] param)方法时出错，错误信息" + ex.Message);
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
/// <summary>
|
/// 执行多结果查询（select）
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <returns></returns>
|
|
public static int ExecuteSqlByTime(string SQLString, int Times)
|
{
|
using (SqlConnection connection = new SqlConnection(connectionString))
|
{
|
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
|
{
|
try
|
{
|
connection.Open();
|
cmd.CommandTimeout = Times;
|
int rows = cmd.ExecuteNonQuery();
|
return rows;
|
}
|
catch (System.Data.SqlClient.SqlException e)
|
{
|
|
|
connection.Close();
|
throw e;
|
}
|
}
|
}
|
}
|
|
|
public static int ExecuteSqlTran(List<String> SQLStringList)
|
{
|
using (SqlConnection conn = new SqlConnection(connectionString))
|
{
|
conn.Open();
|
SqlCommand cmd = new SqlCommand();
|
cmd.Connection = conn;
|
SqlTransaction 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
|
{
|
|
tx.Rollback();
|
return 0;
|
}
|
}
|
}
|
/// <summary>
|
/// 执行带一个存储过程参数的的SQL语句。
|
/// </summary>
|
/// <param name="SQLString">SQL语句</param>
|
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
|
/// <returns>影响的记录数</returns>
|
public static int ExecuteSql(string SQLString, string content)
|
{
|
using (SqlConnection connection = new SqlConnection(connectionString))
|
{
|
SqlCommand cmd = new SqlCommand(SQLString, connection);
|
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
|
myParameter.Value = content;
|
cmd.Parameters.Add(myParameter);
|
try
|
{
|
connection.Open();
|
int rows = cmd.ExecuteNonQuery();
|
return rows;
|
}
|
catch (System.Data.SqlClient.SqlException e)
|
{
|
|
throw e;
|
}
|
finally
|
{
|
|
cmd.Dispose();
|
connection.Close();
|
}
|
}
|
}
|
#region 执行带参数的SQL语句
|
|
/// <summary>
|
/// 执行SQL语句，返回影响的记录数
|
/// </summary>
|
/// <param name="SQLString">SQL语句</param>
|
/// <returns>影响的记录数</returns>
|
public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
|
{
|
using (SqlConnection connection = new SqlConnection(connectionString))
|
{
|
using (SqlCommand cmd = new SqlCommand())
|
{
|
try
|
{
|
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
|
int rows = cmd.ExecuteNonQuery();
|
cmd.Parameters.Clear();
|
return rows;
|
}
|
catch (System.Data.SqlClient.SqlException e)
|
{
|
// WriteLog(e.Message);
|
throw e;
|
}
|
}
|
}
|
}
|
#endregion
|
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
|
{
|
if (conn.State != ConnectionState.Open)
|
conn.Open();
|
cmd.Connection = conn;
|
cmd.CommandText = cmdText;
|
if (trans != null)
|
cmd.Transaction = trans;
|
cmd.CommandType = CommandType.Text;//cmdType;
|
if (cmdParms != null)
|
{
|
|
|
foreach (SqlParameter parameter in cmdParms)
|
{
|
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
|
(parameter.Value == null))
|
{
|
parameter.Value = DBNull.Value;
|
}
|
cmd.Parameters.Add(parameter);
|
}
|
}
|
}
|
|
/// <summary>
|
/// 执行带一个存储过程参数的的SQL语句。
|
/// </summary>
|
/// <param name="SQLString">SQL语句</param>
|
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
|
/// <returns>影响的记录数</returns>
|
public static object ExecuteSqlGet(string SQLString, string content)
|
{
|
using (SqlConnection connection = new SqlConnection(connectionString))
|
{
|
SqlCommand cmd = new SqlCommand(SQLString, connection);
|
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.VarChar);
|
myParameter.Value = content;
|
cmd.Parameters.Add(myParameter);
|
try
|
{
|
connection.Open();
|
object obj = cmd.ExecuteScalar();
|
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
|
{
|
return null;
|
}
|
else
|
{
|
return obj;
|
}
|
}
|
catch (System.Data.SqlClient.SqlException e)
|
{
|
|
throw e;
|
}
|
finally
|
{
|
cmd.Dispose();
|
connection.Close();
|
}
|
}
|
}
|
/// <summary>
|
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
|
/// </summary>
|
/// <param name="strSQL">SQL语句</param>
|
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
|
/// <returns>影响的记录数</returns>
|
/// <summary>
|
/// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
|
/// </summary>
|
/// <param name="strSQL">查询语句</param>
|
/// <returns>SqlDataReader</returns>
|
public static SqlDataReader ExecuteReader(string strSQL)
|
{
|
SqlConnection connection = new SqlConnection(connectionString);
|
SqlCommand cmd = new SqlCommand(strSQL, connection);
|
try
|
{
|
connection.Open();
|
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
|
|
return myReader;
|
}
|
catch (System.Data.SqlClient.SqlException e)
|
{
|
|
throw e;
|
}
|
|
}
|
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
|
{
|
using (SqlConnection connection = new SqlConnection(connectionString))
|
{
|
SqlCommand cmd = new SqlCommand(strSQL, connection);
|
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
|
myParameter.Value = fs;
|
cmd.Parameters.Add(myParameter);
|
try
|
{
|
connection.Open();
|
int rows = cmd.ExecuteNonQuery();
|
return rows;
|
}
|
catch (System.Data.SqlClient.SqlException e)
|
{
|
|
throw e;
|
}
|
finally
|
{
|
cmd.Dispose();
|
connection.Close();
|
}
|
}
|
}
|
#endregion
|
|
/// <summary>
|
/// 执行查询语句，返回DataSet
|
/// </summary>
|
/// <param name="SQLString">查询语句</param>
|
/// <returns>DataSet</returns>
|
public static DataSet Query(string SQLString)
|
{
|
using (SqlConnection connection = new SqlConnection(connectionString))
|
{
|
DataSet ds = new DataSet();
|
try
|
{
|
connection.Open();
|
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
|
command.Fill(ds, "ds");
|
}
|
catch (System.Data.SqlClient.SqlException ex)
|
{
|
|
throw new Exception(ex.Message);
|
}
|
return ds;
|
}
|
}
|
/// <summary>
|
/// 执行返回结果集的操作
|
/// </summary>
|
/// <returns></returns>
|
public static SqlDataReader GetReaderByProcedure(string storeProduceName, SqlParameter[] param)
|
{
|
SqlConnection conn = new SqlConnection(connectionString);
|
SqlCommand comm = new SqlCommand();
|
comm.Connection = conn;
|
comm.CommandType = CommandType.StoredProcedure;
|
comm.CommandText = storeProduceName;
|
try
|
{
|
conn.Open();
|
if (param != null)
|
{
|
comm.Parameters.AddRange(param);
|
}
|
return comm.ExecuteReader(CommandBehavior.CloseConnection);
|
}
|
catch (Exception ex)
|
{
|
///将错误信息写入日志
|
//WriteLog("执行GetReaderByProcedure(string storeProduceName, SqlParameter[] param)时发生错误,错误信息" + ex);
|
throw ex;
|
}
|
}
|
/// <summary>
|
/// 执行增删改方法（insert、update、delete类型的SQL语句）
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <returns></returns>
|
public static int Update(string sql)
|
{
|
SqlConnection conn = new SqlConnection(connectionString);
|
SqlCommand cmd = new SqlCommand(sql, conn);
|
try
|
{
|
conn.Open();
|
return cmd.ExecuteNonQuery();
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
|
/// <summary>
|
/// 执行带参数的SQL语句或存储过程
|
/// </summary>
|
/// <param name="sqlOrProcedure">sql语句或存储过程名称</param>
|
/// <param name="param">参数</param>
|
/// <param name="isProcedure">是否为存储过程</param>
|
/// <returns></returns>
|
public static int Update(string sqlOrProcedure, SqlParameter[] param, bool isProcedure)
|
{
|
SqlConnection conn = new SqlConnection(connectionString);
|
SqlCommand cmd = new SqlCommand(sqlOrProcedure, conn);
|
if (isProcedure)
|
{
|
cmd.CommandType = CommandType.StoredProcedure;
|
}
|
try
|
{
|
conn.Open();
|
cmd.Parameters.AddRange(param);
|
return cmd.ExecuteNonQuery();
|
}
|
catch (Exception ex)
|
{
|
//WriteErrLog("执行 public static int Update(string sqlOrProcedure,SqlParameter[] param,bool isProcedure)方法时出错,错误信息" + ex.Message);
|
throw ex;
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
/// <summary>
|
/// 执行存储过程
|
/// </summary>
|
/// <param name="storedProcName">存储过程名</param>
|
/// <param name="parameters">存储过程参数</param>
|
/// <param name="tableName">DataSet结果中的表名</param>
|
/// <returns>DataSet</returns>
|
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
|
{
|
using (SqlConnection connection = new SqlConnection(connectionString))
|
{
|
DataSet dataSet = new DataSet();
|
connection.Open();
|
SqlDataAdapter sqlDA = new SqlDataAdapter();
|
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
|
sqlDA.Fill(dataSet, tableName);
|
connection.Close();
|
return dataSet;
|
}
|
}
|
/// <summary>
|
/// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
|
/// </summary>
|
/// <param name="connection">数据库连接</param>
|
/// <param name="storedProcName">存储过程名</param>
|
/// <param name="parameters">存储过程参数</param>
|
/// <returns>SqlCommand</returns>
|
private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
|
{
|
SqlCommand command = new SqlCommand(storedProcName, connection);
|
command.CommandType = CommandType.StoredProcedure;
|
foreach (SqlParameter parameter in parameters)
|
{
|
if (parameter != null)
|
{
|
// 检查未分配值的输出参数,将其分配以DBNull.Value.
|
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
|
(parameter.Value == null))
|
{
|
parameter.Value = DBNull.Value;
|
}
|
command.Parameters.Add(parameter);
|
}
|
}
|
|
return command;
|
}
|
//用存储过程插入数据
|
|
public static DataSet GetDataSetByProcedure(string spName, SqlParameter[] param)
|
{
|
SqlConnection conn = new SqlConnection(connectionString);
|
SqlCommand cmd = new SqlCommand(spName, conn);
|
cmd.CommandType = CommandType.StoredProcedure;
|
try
|
{
|
conn.Open();
|
cmd.Parameters.AddRange(param);
|
SqlDataAdapter sda = new SqlDataAdapter();
|
sda.SelectCommand = cmd;
|
DataSet ds = new DataSet();
|
sda.Fill(ds, "ds");
|
cmd.Parameters.Clear();
|
return ds;
|
|
}
|
catch (Exception ex)
|
{
|
//将错误信息写入系统日志
|
string info = "调用GetReaderByProcedure(string spName, SqlParameter[] param)方法时发生错误：" + ex.Message;
|
WriteLog(ex.Message);
|
throw new Exception(info);
|
}
|
}
|
public static SqlDataReader MyReader(string strSQL)
|
{
|
SqlConnection connection = new SqlConnection(connectionString);
|
SqlCommand cmd = new SqlCommand(strSQL, connection);
|
try
|
{
|
connection.Open();
|
SqlDataReader myReader = cmd.ExecuteReader();
|
return myReader;
|
}
|
catch (System.Data.SqlClient.SqlException e)
|
{
|
throw new Exception(e.Message);
|
}
|
finally
|
{
|
cmd.Dispose();
|
//connection.Close();
|
}
|
|
}
|
public static int ExecuteSql1(string SQLString, params SqlParameter[] myParameter)
|
{
|
using (SqlConnection connection = new SqlConnection(connectionString))
|
{
|
SqlCommand cmd = new SqlCommand(SQLString, connection);
|
cmd.CommandType = CommandType.StoredProcedure;
|
cmd.Parameters.AddRange(myParameter);
|
|
try
|
{
|
connection.Open();
|
int rows = cmd.ExecuteNonQuery();
|
return rows;
|
}
|
catch (System.Data.SqlClient.SqlException e)
|
{
|
WriteLog(e.Message);
|
throw e;
|
}
|
finally
|
{
|
|
cmd.Dispose();
|
connection.Close();
|
}
|
}
|
}
|
/// <summary>
|
/// 使用事务提交带参数的SQL语句
|
/// </summary>
|
/// <param name="mainSql">主SQL语句</param>
|
/// <param name="mainparam">主SQL参数</param>
|
/// <param name="detailSql">明细SQL</param>
|
/// <param name="detailparam">明细SQL语句对应的参数数组集合</param>
|
/// <returns>返回事务是否成功</returns>
|
public static bool UpdateByTran(string mainSql, SqlParameter[] mainparam, string detailSql, List<SqlParameter[]> detailparam)
|
{
|
SqlConnection conn = new SqlConnection(connectionString);
|
SqlCommand comm = new SqlCommand();
|
comm.Connection = conn;
|
try
|
{
|
conn.Open();
|
comm.Transaction = conn.BeginTransaction();//开启事务
|
if (mainSql != null && mainSql.Length != 0)
|
{
|
comm.CommandText = mainSql;
|
if (mainparam != null)
|
comm.Parameters.AddRange(mainparam);
|
comm.ExecuteNonQuery();
|
}
|
if (detailparam != null)
|
{
|
foreach (SqlParameter[] param in detailparam)
|
{
|
comm.CommandText = detailSql;
|
comm.Parameters.Clear();//清空参数
|
comm.Parameters.AddRange(param);
|
comm.ExecuteNonQuery();
|
}
|
}
|
comm.Transaction.Commit();//提交事务
|
return true;
|
}
|
catch (Exception ex)
|
{
|
if (comm.Transaction != null)
|
{
|
comm.Transaction.Rollback();//回滚事务
|
}
|
WriteLog(ex.Message);
|
throw new Exception("在调用 public static bool UpdateByTran(string mainSql,SqlParameter[] mainparam,string detailSql,List<SqlParameter[]> detailparam)方法时出错，错误信息" + ex.Message);
|
}
|
finally
|
{
|
if (comm.Transaction != null)
|
{
|
comm.Transaction = null;
|
}
|
}
|
}
|
#region SQL错误日志记录
|
/// <summary>
|
/// SQL错误日志记录
|
/// </summary>
|
public static void WriteLog(string loginfo)
|
{
|
FileStream fs = new FileStream("SQLHelper.Log", FileMode.Append);
|
StreamWriter sw = new StreamWriter(fs);
|
string Log = GetDBSqlDateTime() + " 错误信息: " + loginfo;
|
sw.WriteLine(Log);
|
sw.Close();
|
fs.Close();
|
}
|
#endregion
|
/// <summary>
|
/// 获取服务器时间
|
/// </summary>
|
/// <returns></returns>
|
public static DateTime GetDBSqlDateTime()
|
{
|
return Convert.ToDateTime(GetSingleResult("select getdate()"));
|
}
|
}
|
}
|
