SQLHelper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Web;

namespace CommonTools
{
public class SQLHelper : IDisposable
{
#region * 字段与属性 *

private string connectionString = string.Empty;
private SqlConnection conn;
private SqlCommand cmd;

/// <summary>
/// 数据库连接字符串
/// </summary>
public string ConnectionString
{
get { return this.connectionString; }
}

/// <summary>
/// 获取基于打开状态的数据连接
/// </summary>
public SqlConnection Conn
{
get
{
if (this.conn == null)
this.conn = new SqlConnection(this.ConnectionString);
else if (this.conn.State == System.Data.ConnectionState.Closed)
this.conn.Open();
else if (this.conn.State == System.Data.ConnectionState.Broken)
{
this.conn.Close();
this.conn.Open();
}
return this.conn;
}
}

/// <summary>
/// 获取已赋值Connection值的Command
/// </summary>
public SqlCommand Cmd
{
get
{
if (this.cmd == null)
this.cmd = new SqlCommand();

this.cmd.Connection = this.Conn;
return this.cmd;
}
}

#endregion

#region * ConnectionString *

public SQLHelper()
{
//ConfigurationManager: 提供对客户端应用程序配置文件的访问,无法继承类
this.connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
}
public SQLHelper(string connectionString)
{
this.connectionString = connectionString;
}

#endregion

#region * 使用公共方法 *

/// <summary>
/// 1. 释放资源
/// </summary>
public void Dispose()
{
if (this.conn != null)
{
if (this.conn.State == ConnectionState.Open)
{
this.conn.Close();
}
this.conn.Dispose();
}
if (this.cmd != null)
{
this.cmd.Dispose();
}
}

/// <summary>
/// 2. 执行查询返回结果集的第一行第一列
/// </summary>
/// <param name="commandText">SQL字符串或存储过程的名称</param>
/// <param name="commandType"></param>
/// <param name="parms">参数集合，若没有参数则传递null</param>
/// <returns></returns>
public object ExecuteScalar(string commandText, CommandType commandType, params SqlParameter[] parms)
{
object result = null;

this.Cmd.CommandText = commandText;
this.Cmd.CommandType = commandType;

if (parms != null && parms.Length > 0)
{
this.Cmd.Parameters.AddRange(parms);
}

try
{
result = this.Cmd.ExecuteScalar();
}
catch (SqlException)
{
throw;
}
catch (Exception)
{
throw;
}
finally
{
if (this.Cmd.Parameters != null)
this.Cmd.Parameters.Clear();
}
return result;
}

/// <summary>
/// 3. 对数据库执行操作，返回受影响的行数
/// </summary>
/// <param name="commandText">SQL字符串或存储过程的名称</param>
/// <param name="commandType"></param>
/// <param name="parms">参数集合，如果没有参数，默认传null</param>
/// <returns></returns>
public int ExecuteNonQuery(string commandText, CommandType commandType, params SqlParameter[] parms)
{
int result = 0;

this.Cmd.CommandText = commandText;
this.Cmd.CommandType = commandType;

if (parms != null && parms.Length > 0)
{
this.Cmd.Parameters.AddRange(parms);
}
try
{
result = this.Cmd.ExecuteNonQuery();
}
catch (SqlException)
{
throw;
}
catch (Exception)
{
throw;
}
finally
{
if (this.Cmd.Parameters != null)
this.Cmd.Parameters.Clear();
}
return result;
}

/// <summary>
/// 4. 执行Sql语句，返回符合条件的数据集
/// </summary>
/// <param name="commandText">SQL字符串或存储过程的名称</param>
/// <param name="commandType">类型</param>
/// <param name="parms">参数集合，如果没有参数，默认传null</param>
/// <returns></returns>
public DataSet GetData(string commandText, CommandType commandType, params SqlParameter[] parms)
{
this.Cmd.CommandText = commandText;
this.Cmd.CommandType = commandType;

SqlDataAdapter da = new SqlDataAdapter(this.Cmd);
DataSet ds = new DataSet();

if (parms != null && parms.Length > 0)
{
this.Cmd.Parameters.AddRange(parms);
}
try
{
da.Fill(ds);
}
catch (Exception)
{
throw;
}
finally
{
if (this.Cmd.Parameters != null)
this.Cmd.Parameters.Clear();
}
return ds;

}

/// <summary>
/// 5. 对数据库执行事务操作，返回受影响的行数
/// </summary>
/// <param name="commandType">命令类型</param>
/// <param name="sqlList">以sql字符串为键，以参数数组为值的sql命令集合</param>
/// <returns></returns>
public int ExecuteNonQuery(CommandType commandType, params KeyValuePair<string, SqlParameter[]>[] sqlList)
{
int result = 0;

if (sqlList == null || sqlList.Length < 1)
{
return result;
}

this.Cmd.CommandType = commandType;
SqlTransaction transaction = this.Conn.BeginTransaction("deleteLeaveWord");
this.Cmd.Transaction = transaction;

try
{
foreach (KeyValuePair<string, SqlParameter[]> item in sqlList)
{
this.Cmd.CommandText = item.Key;
if (item.Value != null && item.Value.Length > 0)
{
this.Cmd.Parameters.AddRange(item.Value);
}
result = this.Cmd.ExecuteNonQuery();
if (result <= 0)
{
transaction.Rollback();
}
}
transaction.Commit();//提交事务
}
catch (SqlException)
{
transaction.Rollback();//从挂起状态回滚事务
throw;
}
catch (Exception)
{
transaction.Rollback();
throw;
}
finally
{
if (this.Cmd.Parameters != null)
this.Cmd.Parameters.Clear();
}
return result;
}

/// <summary>
/// 6. 对数据库执行查询，返回DataReader对象，获取完返回的DataReader对象要进行关闭与释放
/// </summary>
/// <param name="commandText">SQL语句或存储过程的名称</param>
/// <param name="commandType"></param>
/// <param name="parms">参数集合，如果没有参数则传null</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string commandText, CommandType commandType, params SqlParameter[] parms)
{
SqlDataReader sdr = null;

this.Cmd.CommandText = commandText;
this.Cmd.CommandType = commandType;

if (parms != null && parms.Length > 0)
{
this.Cmd.Parameters.AddRange(parms);
}

try
{
sdr = this.Cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (SqlException)
{
throw;
}
catch (Exception)
{
throw;
}
finally
{
if (this.Cmd.Parameters != null)
this.Cmd.Parameters.Clear();
}
return sdr;
}

#endregion
}
}