using System;
|
using System.Collections;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.IO;
|
|
namespace Common
|
{
|
/// <summary>
|
/// 用于SQL Server数据库访问的帮助类
|
/// </summary>
|
public class SQLHelperClass
|
{
|
private string _connectionString = "";
|
|
public string connectionString
|
{
|
get { return _connectionString; }
|
set { _connectionString =value; }
|
}
|
|
private SqlConnection _connection4Tran;
|
private SqlTransaction _transaction;
|
private readonly Queue<SqlTask> _transactionTaskList;
|
|
/// <summary>
|
/// 创建一个新的SQLHelper并指定连接字符串
|
/// </summary>
|
public SQLHelperClass()
|
{
|
_transactionTaskList = new Queue<SqlTask>();
|
}
|
|
///// <summary>
|
///// 创建一个新的SQLHelper并指定连接信息
|
///// </summary>
|
///// <param name="server"></param>
|
///// <param name="userID"></param>
|
///// <param name="password"></param>
|
///// <param name="database"></param>
|
// SQLHelper(string server, string userID, string password, string database)
|
//{
|
// _transactionTaskList = new Queue<SqlTask>();
|
// if (database == string.Empty)
|
// {
|
// _connectionString = "Data Source=" + server + ";User ID=" + userID + ";Password=" + password;
|
// }
|
// else
|
// {
|
// _connectionString = "Data Source=" + server + ";User ID=" + userID + ";Password=" + password + ";Initial Catalog=" + database;
|
// }
|
|
//}
|
|
/// <summary>
|
/// 测试连接
|
/// </summary>
|
/// <returns></returns>
|
public bool TestConnection()
|
{
|
try
|
{
|
using (var connection = new SqlConnection(_connectionString))
|
{
|
connection.Open();
|
connection.Close();
|
return true;
|
}
|
}
|
catch (Exception)
|
{
|
return false;
|
}
|
|
}
|
|
/// <summary>
|
/// ExecuteNonQuery操作，对数据库进行 增、删、改 操作(1）
|
/// </summary>
|
/// <param name="sql">要执行的SQL语句 </param>
|
/// <returns> </returns>
|
public int ExecuteNonQuery(string sql)
|
{
|
return ExecuteNonQuery(sql, CommandType.Text, null);
|
}
|
/// <summary>
|
/// ExecuteNonQuery操作，对数据库进行 增、删、改 操作（2）
|
/// </summary>
|
/// <param name="sql">要执行的SQL语句 </param>
|
/// <param name="commandType">要执行的查询类型（存储过程、SQL文本） </param>
|
/// <returns> </returns>
|
public int ExecuteNonQuery(string sql, CommandType commandType)
|
{
|
return ExecuteNonQuery(sql, commandType, null);
|
}
|
/// <summary>
|
/// ExecuteNonQuery操作，对数据库进行 增、删、改 操作（3）
|
/// </summary>
|
/// <param name="sql">要执行的SQL语句 </param>
|
/// <param name="commandType">要执行的查询类型（存储过程、SQL文本） </param>
|
/// <param name="parameters">参数数组 </param>
|
/// <returns> </returns>
|
public int ExecuteNonQuery(string sql, CommandType commandType, SqlParameter[] parameters)
|
{
|
return ExecuteNonQuery(sql, commandType, parameters, false);
|
}
|
|
/// <summary>
|
/// ExecuteNonQuery操作，对数据库进行 增、删、改 操作(4）
|
/// </summary>
|
/// <param name="sql">要执行的SQL语句</param>
|
/// <param name="commandType">要执行的查询类型（存储过程、SQL文本）</param>
|
/// <param name="parameters">参数数组</param>
|
/// <param name="joinTransaction">是否加入事务处理；如果为true，前提是必须使用BeginTransaction开始了一个事务，如果为false,不使用事务。</param>
|
/// <returns></returns>
|
public int ExecuteNonQuery(string sql, CommandType commandType, SqlParameter[] parameters, bool joinTransaction)
|
{
|
int count = 0;
|
if (joinTransaction)
|
{
|
if (_transaction == null || _connection4Tran == null)
|
{
|
throw new Exception("事务未初始化！");
|
}
|
//using (var command = new SqlCommand(sql, _connection4Tran))
|
//{
|
// command.CommandType = commandType;
|
// if (parameters != null)
|
// {
|
// foreach (SqlParameter parameter in parameters)
|
// {
|
// command.Parameters.Add(parameter);
|
// }
|
// }
|
// command.Transaction = _transaction;
|
// count = command.ExecuteNonQuery();
|
//}
|
_transactionTaskList.Enqueue(new SqlTask(sql, commandType, parameters));
|
}
|
else
|
{
|
using (var connection = new SqlConnection(_connectionString))
|
{
|
using (var command = new SqlCommand(sql, connection))
|
{
|
command.CommandType = commandType;
|
if (parameters != null)
|
{
|
foreach (SqlParameter parameter in parameters)
|
{
|
command.Parameters.Add(parameter);
|
}
|
}
|
connection.Open();
|
count = command.ExecuteNonQuery();
|
}
|
}
|
}
|
return count;
|
}
|
|
/// <summary>
|
/// SqlDataAdapter的Fill方法执行一个查询，并返回一个DataSet类型结果（1）
|
/// </summary>
|
/// <param name="sql">要执行的SQL语句 </param>
|
/// <returns> </returns>
|
public DataSet ExecuteDataSet(string sql)
|
{
|
return ExecuteDataSet(sql, CommandType.Text, null);
|
}
|
/// <summary>
|
/// SqlDataAdapter的Fill方法执行一个查询，并返回一个DataSet类型结果（2）
|
/// </summary>
|
/// <param name="sql">要执行的SQL语句 </param>
|
/// <param name="commandType">要执行的查询类型（存储过程、SQL文本） </param>
|
/// <returns> </returns>
|
public DataSet ExecuteDataSet(string sql, CommandType commandType)
|
{
|
return ExecuteDataSet(sql, commandType, null);
|
}
|
/// <summary>
|
/// SqlDataAdapter的Fill方法执行一个查询，并返回一个DataSet类型结果（3）
|
/// </summary>
|
/// <param name="sql">要执行的SQL语句 </param>
|
/// <param name="commandType">要执行的查询类型（存储过程、SQL文本） </param>
|
/// <param name="parameters">参数数组 </param>
|
/// <returns> </returns>
|
public DataSet ExecuteDataSet(string sql, CommandType commandType, SqlParameter[] parameters)
|
{
|
var ds = new DataSet();
|
using (var connection = new SqlConnection(_connectionString))
|
{
|
using (var command = new SqlCommand(sql, connection))
|
{
|
command.CommandType = commandType;
|
if (parameters != null)
|
{
|
foreach (SqlParameter parameter in parameters)
|
{
|
command.Parameters.Add(parameter);
|
}
|
}
|
var adapter = new SqlDataAdapter(command);
|
adapter.Fill(ds);
|
command.Parameters.Clear();//将使用完之后的Command命令的Parameters集合清空
|
}
|
}
|
return ds;
|
}
|
/// <summary>
|
/// SqlDataAdapter的Fill方法执行一个查询，并返回一个DataTable类型结果（1）
|
/// </summary>
|
/// <param name="sql">要执行的SQL语句 </param>
|
/// <returns> </returns>
|
public DataTable ExecuteDataTable(string sql)
|
{
|
return ExecuteDataTable(sql, CommandType.Text, null);
|
}
|
/// <summary>
|
/// SqlDataAdapter的Fill方法执行一个查询，并返回一个DataTable类型结果（2）
|
/// </summary>
|
/// <param name="sql">要执行的SQL语句 </param>
|
/// <param name="commandType">要执行的查询类型（存储过程、SQL文本） </param>
|
/// <returns> </returns>
|
public DataTable ExecuteDataTable(string sql, CommandType commandType)
|
{
|
return ExecuteDataTable(sql, commandType, null);
|
}
|
/// <summary>
|
/// SqlDataAdapter的Fill方法执行一个查询，并返回一个DataTable类型结果（3）
|
/// </summary>
|
/// <param name="sql">要执行的SQL语句 </param>
|
/// <param name="commandType">要执行的查询类型（存储过程、SQL文本） </param>
|
/// <param name="parameters">参数数组 </param>
|
/// <returns> </returns>
|
public DataTable ExecuteDataTable(string sql, CommandType commandType, SqlParameter[] parameters)
|
{
|
var data = new DataTable();
|
using (var connection = new SqlConnection(_connectionString))
|
{
|
using (var command = new SqlCommand(sql, connection))
|
{
|
command.CommandType = commandType;
|
if (parameters != null)
|
{
|
foreach (SqlParameter parameter in parameters)
|
{
|
command.Parameters.Add(parameter);
|
}
|
}
|
var adapter = new SqlDataAdapter(command);
|
adapter.Fill(data);
|
command.Parameters.Clear();//将使用完之后的Command命令的Parameters集合清空
|
}
|
}
|
return data;
|
}
|
/// <summary>
|
/// ExecuteReader执行一查询，返回一SqlDataReader对象实例（1）
|
/// </summary>
|
/// <param name="sql">要执行的SQL语句 </param>
|
/// <returns> </returns>
|
public SqlDataReader ExecuteReader(string sql)
|
{
|
return ExecuteReader(sql, CommandType.Text, null);
|
}
|
/// <summary>
|
/// ExecuteReader执行一查询，返回一SqlDataReader对象实例（2）
|
/// </summary>
|
/// <param name="sql">要执行的SQL语句 </param>
|
/// <param name="commandType">要执行的查询类型（存储过程、SQL文本） </param>
|
/// <returns> </returns>
|
public SqlDataReader ExecuteReader(string sql, CommandType commandType)
|
{
|
return ExecuteReader(sql, commandType, null);
|
}
|
/// <summary>
|
/// ExecuteReader执行一查询，返回一SqlDataReader对象实例（3）
|
/// </summary>
|
/// <param name="sql">要执行的SQL语句 </param>
|
/// <param name="commandType">要执行的查询类型（存储过程、SQL文本） </param>
|
/// <param name="parameters">参数数组 </param>
|
/// <returns> </returns>
|
public SqlDataReader ExecuteReader(string sql, CommandType commandType, SqlParameter[] parameters)
|
{
|
var connection = new SqlConnection(_connectionString);
|
var command = new SqlCommand(sql, connection) { CommandType = commandType };
|
if (parameters != null)
|
{
|
foreach (SqlParameter parameter in parameters)
|
{
|
command.Parameters.Add(parameter);
|
}
|
}
|
connection.Open();
|
return command.ExecuteReader(CommandBehavior.CloseConnection);
|
}
|
/// <summary>
|
/// ExecuteScalar执行一查询，返回查询结果的第一行第一列（1）
|
/// </summary>
|
/// <param name="sql">要执行的SQL语句 </param>
|
/// <returns> </returns>
|
public Object ExecuteScalar(string sql)
|
{
|
return ExecuteScalar(sql, CommandType.Text, null);
|
}
|
/// <summary>
|
/// ExecuteScalar执行一查询，返回查询结果的第一行第一列（2）
|
/// </summary>
|
/// <param name="sql">要执行的SQL语句 </param>
|
/// <param name="commandType">要执行的查询类型（存储过程、SQL文本） </param>
|
/// <returns> </returns>
|
public Object ExecuteScalar(string sql, CommandType commandType)
|
{
|
return ExecuteScalar(sql, commandType, null);
|
}
|
|
/// <summary>
|
/// ExecuteScalar执行一查询，返回查询结果的第一行第一列（3）
|
/// </summary>
|
/// <param name="sql">要执行的SQL语句 </param>
|
/// <param name="commandType">要执行的查询类型（存储过程、SQL文本） </param>
|
/// <param name="parameters">参数数组</param>
|
/// <returns> </returns>
|
public Object ExecuteScalar(string sql, CommandType commandType, SqlParameter[] parameters)
|
{
|
object result;
|
using (var connection = new SqlConnection(_connectionString))
|
{
|
using (var command = new SqlCommand(sql, connection))
|
{
|
command.CommandType = commandType;
|
if (parameters != null)
|
{
|
foreach (SqlParameter parameter in parameters)
|
{
|
command.Parameters.Add(parameter);
|
}
|
}
|
connection.Open();
|
result = command.ExecuteScalar();
|
command.Parameters.Clear();//将使用完之后的Command命令的Parameters集合清空
|
}
|
}
|
return result;
|
}
|
/// <summary>
|
/// 返回当前连接的数据库中所有由用户创建的数据表
|
/// </summary>
|
/// <returns> </returns>
|
public DataTable GetTables()
|
{
|
DataTable data;
|
using (var connection = new SqlConnection(_connectionString))
|
{
|
connection.Open();
|
data = connection.GetSchema("Tables");
|
}
|
return data;
|
}
|
|
/// <summary>
|
/// 返回当前连接的数据库列表
|
/// </summary>
|
/// <returns> </returns>
|
public DataTable GetDatabases()
|
{
|
DataTable data;
|
using (var connection = new SqlConnection(_connectionString))
|
{
|
connection.Open();
|
data = connection.GetSchema("Databases");
|
}
|
return data;
|
}
|
|
/// <summary>
|
/// 获取SQL Server服务器列表
|
/// </summary>
|
/// <returns></returns>
|
public ArrayList GetServerList()
|
{
|
var arrayList = new ArrayList();
|
foreach (DataRow dr in System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources().Rows)
|
{
|
arrayList.Add(dr[0].ToString());
|
}
|
return arrayList;
|
}
|
|
/// <summary>
|
/// 开始一个事务处理
|
/// <para/>
|
/// <para>调用格式样例：</para>
|
/// <para/>
|
/// <para><code>SQLHelper.BeginTransaction</code></para>
|
/// <para><code>SQLHelper.ExecuteNoneQuery(command1)</code></para>
|
/// <para><code>SQLHelper.ExecuteNoneQuery(command2)</code></para>
|
/// <para><code>SQLHelper.ExecuteNoneQuery(...)</code></para>
|
/// <para><code>SQLHelper.CommitTransaction</code></para>
|
/// <para/>
|
/// <para>在事务提交之前，可随时使用方法CancelTransaction取消事务;</para>
|
/// <para>如果事务在提交的过程中发生错误，将自动回滚事务。</para>
|
/// </summary>
|
/// <param name="isolationLevel">事务隔离级别</param>
|
public void BeginTransaction(IsolationLevel isolationLevel)
|
{
|
if ((_transaction != null) || (_connection4Tran != null))
|
{
|
throw new Exception("要开始一个新的事务，请先完成当前事务!");
|
}
|
_connection4Tran = new SqlConnection(_connectionString);
|
_connection4Tran.Open();
|
_transaction = _connection4Tran.BeginTransaction(isolationLevel);
|
_transactionTaskList.Clear();
|
}
|
|
/// <summary>
|
/// 提交事务
|
/// </summary>
|
public void CommitTransaction()
|
{
|
try
|
{
|
if (_transactionTaskList.Count > 0)
|
{
|
foreach (SqlTask sqlTask in _transactionTaskList)
|
{
|
using (var command = new SqlCommand(sqlTask.Text, _connection4Tran))
|
{
|
command.CommandType = sqlTask.CommandType;
|
if (sqlTask.Parameters != null)
|
{
|
foreach (SqlParameter parameter in sqlTask.Parameters)
|
{
|
command.Parameters.Add(parameter);
|
}
|
}
|
command.Transaction = _transaction;
|
command.ExecuteNonQuery();
|
}
|
}
|
}
|
_transaction.Commit();
|
}
|
catch (Exception)
|
{
|
_transaction.Rollback();
|
throw;
|
}
|
finally
|
{
|
if (_transaction != null)
|
{
|
_transaction.Dispose();
|
}
|
if (_connection4Tran != null)
|
{
|
_connection4Tran.Close();
|
}
|
if (_connection4Tran != null)
|
{
|
_connection4Tran.Dispose();
|
}
|
_transaction = null;
|
_connection4Tran = null;
|
_transactionTaskList.Clear();
|
}
|
}
|
|
/// <summary>
|
/// 通过存储过程返回结果集方法
|
/// </summary>
|
/// <param name="procedureName">存储过程名称</param>
|
/// <param name="param">参数组</param>
|
/// <returns>返回SqlDataReader数据结果集</returns>
|
public SqlDataReader GetReaderByProcedure(string procedureName, SqlParameter[] param)
|
{
|
SqlConnection conn = new SqlConnection(_connectionString);
|
SqlCommand cmd = new SqlCommand();
|
try
|
{
|
conn.Open();
|
cmd.Connection = conn;
|
cmd.CommandType = CommandType.StoredProcedure;
|
cmd.CommandText = procedureName;
|
cmd.Parameters.AddRange(param);
|
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
|
}
|
catch (Exception ex)
|
{
|
WritterError("执行 GetReaderByProcedure(string procedureName,SqlParameter[] param)出现异常:", ex.Message);
|
conn.Close();
|
throw;
|
}
|
}
|
|
/// <summary>
|
/// 通过存储过程返回一个DataSet数据集
|
/// </summary>
|
/// <param name="procedureName">存储过程名称</param>
|
/// <param name="param">存储过程所需参数</param>
|
/// <returns>DataSet数据集</returns>
|
public DataSet GetDataSetByProcedure(string procedureName, SqlParameter[] param)
|
{
|
SqlConnection conn = new SqlConnection(_connectionString);
|
SqlCommand cmd = new SqlCommand(procedureName, conn);
|
SqlDataAdapter da = new SqlDataAdapter(cmd);
|
try
|
{
|
conn.Open();
|
cmd.CommandType = CommandType.StoredProcedure;
|
cmd.Parameters.AddRange(param);
|
DataSet ds = new DataSet();
|
da.Fill(ds);
|
return ds;
|
|
}
|
catch (Exception ex)
|
{
|
//WritterError("执行GetTableByProcedure(string procedureName,SqlParameter[] param)出现异常:", ex.Message);
|
throw ex;
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
|
#region 错误信息写入日志
|
public void WritterError(string errorObjName, string exMessage)
|
{
|
FileStream fs = new FileStream("libraryError.log", FileMode.Append);
|
StreamWriter sw = new StreamWriter(fs);
|
sw.WriteLine(string.Format("{0}{1}", errorObjName, exMessage + GetServerTime()));
|
sw.Close();
|
fs.Close();
|
}
|
#endregion
|
|
#region 获取服务器时间
|
public DateTime GetServerTime()
|
{
|
return Convert.ToDateTime(ExecuteScalar("select getdate()"));
|
}
|
|
#endregion
|
|
/// <summary>
|
/// 取消事务
|
/// </summary>
|
public void CancelTransaction()
|
{
|
if (_transaction != null)
|
{
|
_transaction.Dispose();
|
}
|
if (_connection4Tran != null)
|
{
|
_connection4Tran.Close();
|
}
|
if (_connection4Tran != null)
|
{
|
_connection4Tran.Dispose();
|
}
|
_transaction = null;
|
_connection4Tran = null;
|
}
|
|
#region ICloneable 成员
|
|
//public object Clone()
|
//{
|
// return (new SQLHelper(_connectionString));
|
//}
|
|
#endregion
|
|
#region IDisposable 成员
|
|
//public void Dispose()
|
//{
|
// _connection4Tran = null;
|
// _transaction = null;
|
//}
|
|
#endregion
|
|
public class SqlTask
|
{
|
public string Text { get; private set; }
|
public CommandType CommandType { get; private set; }
|
public SqlParameter[] Parameters { get; private set; }
|
|
public SqlTask(string text, CommandType commandType, SqlParameter[] paras)
|
{
|
Text = text;
|
CommandType = commandType;
|
Parameters = paras;
|
}
|
}
|
}
|
}
|
