ThroughoutCMS技术共享系列（1）：轻量级的数据库访问类（初级水平）

本文链接：https://blog.csdn.net/jjkcom/article/details/5193228

网上和书籍上看到很多的数据库访问类，众说纷纭，概念很多，理念也很多，有的侧重于语法的解释，有的侧重于概念和技巧的探讨，有的强调工程结构的优化，有的面向oo设计原则，有的则专注于高性能的改进，本文是ThroughoutCMS技术系列共享文章，目的在于依托一个现实可用的完整产品应用（场景），阐述技术是如何融入到具体项目中的，顺藤摸瓜，感受技术是如何伴随着业务而成长的。在些列的其他文章中，我们会针对应用发生了变化而改进，大家一起讨论吧，剖析一下，看看哪里好，哪里不好。

数据访问类通常是一个系统最为核心的部分，下列代码是ThroughoutCMS最初版本应用的数据访问类，代码较为简单，采用c#，基本在微软block-DBHelper的基础上进行了包装和功能补充，没有采用多线程（线程池）、会话和缓存技术，更多的体现的是.net1.1开发的过程中“面向过程+面向对象”两种开发痕迹，应用面也比较窄，仅仅适合于"MS SQL Server"的数据库应用，适合于webform和winform开发，虽然说是初级水平，但是却成功应用于多个项目（包括数个百万级项目），拿来就用，省却了很多开发人员的工作，而后来数据库访问类也不断的改进，这是后话了，闲话少说，还是把代码贴出来吧。

如果你正在做一个小型的应用，直接拷贝，修改一下命名空间即可。如果技术培训，本文也是一个好的素材，讲点很多呢，不论是正面的还是反面的，我当初就是用它作为培训的材料。
using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Collections;

namespace CMS.Lib
{
/// <summary>
/// 数据库访问类，定义了一系列数据访问的方法接口，屏蔽了数据访问的底层
/// 并且封装了数据访问的通用函数，使用的时候更加强大和灵活。
/// <description>数据处理基类,调用方式：DataAccess.DataSet((string)sqlstr);
/// 或者DataAccess.DataSet((string)sqlstr,ref DataSet ds); </description>
/// </summary>
public class DB
{
#region "成员变量"
//以下是采用了webconfig进行读取连接串
static string ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["DBConnectionString"];
//以下是采用了直接赋值的方式
//static string ConnectionString = "workstation id=jjk;packet size=4096;user id=sa;data source=jjk;persist security info=False;initial catalog=CGS_UUM_DB";
#endregion

#region "构造函数"

/// <summary>
/// 没有参数的构造函数
/// </summary>
public DB() {}
/// <summary>
/// 带有数据库连接串的构造函数
/// </summary>
/// <param name="DBConn">数据库连接字符串</param>
public DB(string DBConn){ConnectionString=DBConn;}

#endregion

/// <summary>
/// 得到当前的数据服务器的连接串
/// </summary>
/// <returns>
/// 数据库联结字符串
/// </returns>
public static string GetCMSDBConn()
{
    return ConnectionString;
}
/// <summary>
/// 检测当前的CMS数据库连接是否正确
/// </summary>
/// <returns>
/// true:正确
/// false:错误
/// </returns>
public static bool CheckCMSDBConn()
{
   bool ret=false;
   SqlConnection conn=new SqlConnection(ConnectionString);
   try
   {
    conn.Open();
    conn.Close();
    conn.Dispose();
    ret=true;
   }
   catch(Exception e)
   {
    //throw new Exception(e.Message);
   }
   finally
   {}
   return ret;
}
/// 执行一段SQL语句
/// </summary>
/// <param name="sSql">SQL语句</param>
/// <returns>操作成功与否。
/// true,成功；
/// false,失败
/// </returns>
public static bool ExecuteSingleLineSql(string sSql)
{
   bool ret=false;
   SqlCommand comm=new SqlCommand();
   SqlConnection conn=new SqlConnection(ConnectionString);
   try
   {
    conn.Open();
    comm.Connection =conn;
    comm.CommandType =CommandType.Text ;
    comm.CommandText =sSql;
    comm.ExecuteNonQuery();
    ret=true;
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    if(conn.State == ConnectionState.Open)
     conn.Close();
    conn.Dispose();
    comm.Dispose();
   }
   return ret;
}

/// <summary>
/// 查询数据库，返回数据集
/// 由于多数数据操作直接返回数据表，因此实际上不推荐使用本函数
/// </summary>
/// <param name="sSQL">标准的查询SQL语句</param>
/// <returns>DataSet，其中存放查询结果集</returns>
public static DataSet SelectDataToDataset(string sSQL)
{
   string Sql = sSQL;
   SqlConnection conn=new SqlConnection(ConnectionString);
   // 创建DataSet对象
   DataSet data = new DataSet();
   // 创建DataAdapter对象并初始化
   SqlDataAdapter adapter;
   try
   {
    conn.Open();
    adapter = new SqlDataAdapter(Sql, conn);
    // 填充数据到DataSet
    adapter.Fill(data);
    //释放资源
    adapter.Dispose();

   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    if(conn.State == ConnectionState.Open)
     conn.Close();
    conn.Dispose();
   }
   return data;
}

/// <summary>
/// 执行存储过程并返回状态值
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="coll">SqlParameter集合</param>
/// <returns>成果返回存储过程返回值，失败返回-1字符串</returns>
public static string ExecutePorcedure(string procName,SqlParameter[] coll)
{
   string ret;
//   SqlConnection thisConnection;
//   // 创建SqlConnection对象
//   thisConnection = new SqlConnection(ConnectionString);
   SqlConnection conn=new SqlConnection(ConnectionString);
   SqlCommand comm=new SqlCommand();
   try
   {
    conn.Open();
    comm.Connection=conn;
    comm.Parameters.Clear();
    comm.CommandType=CommandType.StoredProcedure ;
    comm.CommandText =procName;
    for(int i=0;i<coll.Length;i++)
    {
     comm.Parameters .Add(coll[i]);
    }
    SqlParameter RetVal = comm.Parameters.Add("RetVal", SqlDbType.Int);
    RetVal.Direction = ParameterDirection.ReturnValue;//获取返回值

    comm.ExecuteNonQuery();
    ret=RetVal.Value.ToString();
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    comm.Parameters.Clear();
    if(conn.State == ConnectionState.Open)
     conn.Close();
    conn.Dispose();
    comm.Dispose();
   }
   return ret;
}

/// <summary>
/// 执行存储过程并返回结果集合
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="coll">SqlParameter集合</param>
/// <returns>成果返回存储过程返回查询结果</returns>
public static DataSet ExecutePorcedureToDataSet(string procName,SqlParameter[] coll)
{
   DataSet ret = new DataSet();
   // 创建SqlConnection对象
   SqlConnection conn=new SqlConnection(ConnectionString);
   SqlCommand comm=new SqlCommand();
   try
   {
    conn.Open();
    comm.Connection=conn;
    comm.Parameters.Clear();
    comm.CommandType=CommandType.StoredProcedure ;
    comm.CommandText =procName;
    for(int i=0;i<coll.Length;i++)
    {
     comm.Parameters .Add(coll[i]);
    }
    SqlDataAdapter da = new SqlDataAdapter(comm) ;
    da.Fill(ret);
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    comm.Parameters.Clear();
    if(conn.State == ConnectionState.Open)
     conn.Close();
    conn.Dispose();
    comm.Dispose();
   }
   return ret;
}

/// <summary>
/// 在一个事务中需要传入comm执行存储过程并返回结果集合
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="coll">SqlParameter集合</param>
/// <returns>成果返回存储过程返回查询结果</returns>
public static string ExecuteTranPorcedure(string procName,SqlParameter[] coll,SqlCommand comm)
{
   string ret;
   //   SqlConnection thisConnection;
   //   // 创建SqlConnection对象
   //   thisConnection = new SqlConnection(ConnectionString);
//   SqlConnection conn=new SqlConnection(ConnectionString);
//   SqlCommand comm=com;
   try
   {
    comm.Parameters.Clear();
    comm.CommandType=CommandType.StoredProcedure ;
    comm.CommandText =procName;
    for(int i=0;i<coll.Length;i++)
    {
     comm.Parameters .Add(coll[i]);
    }
    SqlParameter RetVal = comm.Parameters.Add("RetVal", SqlDbType.Int);
    RetVal.Direction = ParameterDirection.ReturnValue;//获取返回值

    comm.ExecuteNonQuery();
    ret=RetVal.Value.ToString();
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   return ret;
}

/// <summary>
/// 执行SQLCommand并返回结果集合
/// </summary>
/// <param name="sSql">SQL语句</param>
/// <param name="coll">SqlParameter集合</param>
/// <returns>成果返回存储过程返回查询结果</returns>
public static DataSet ExecuteSqlCommandToDataSet(string sSql,SqlParameter[] coll)
{
DataSet ret = new DataSet();
// // 创建SqlConnection对象

   SqlConnection conn=new SqlConnection(ConnectionString);
   SqlCommand comm=new SqlCommand();
   try
   {
    conn.Open();
    comm.Connection=conn;
    comm.Parameters.Clear();
    comm.CommandType=CommandType.Text ;
    comm.CommandText =sSql;
    for(int i=0;i<coll.Length;i++)
    {
     comm.Parameters .Add(coll[i]);
    }
    SqlDataAdapter da = new SqlDataAdapter(comm) ;
    da.Fill(ret);
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    comm.Parameters.Clear();
    if(conn.State == ConnectionState.Open)
     conn.Close();
    conn.Dispose();
    comm.Dispose();
   }
   return ret;
}

/// <summary>
/// 执行SQLCommand并返回结果集合
/// </summary>
/// <param name="sSQL">执行Sql语句</param>
/// <returns>数据表</returns>
public static DataTable SelectDataToDataTable(string sSQL)
{
   string Sql = sSQL;
   DataTable dt=new DataTable();
       // 创建SqlConnection对象
   SqlConnection conn=new SqlConnection(ConnectionString);
   // 创建DataAdapter对象并初始化
   SqlDataAdapter adapter=new SqlDataAdapter();
   try
   {
    conn.Open();
    adapter = new SqlDataAdapter(Sql, conn);
    //填充数据到DataSet
    adapter.Fill(dt);

   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    //释放资源
    adapter.Dispose();
    if(conn.State == ConnectionState.Open)
     conn.Close();
    conn.Dispose();
   }
   return dt;
}

/// <summary>
/// 执行存储过程并返回数据表
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="coll">SqlParameter集合</param>
/// <returns>成果返回存储过程返回查询结果</returns>
public static DataTable ExecutePorcedureToDataTable(string procName,SqlParameter[] coll)
{
   DataTable dt=new DataTable();
   // 创建SqlConnection对象
   SqlConnection conn=new SqlConnection(ConnectionString);
   SqlDataAdapter adapter;
   SqlCommand comm=new SqlCommand();
   try
   {
    conn.Open();
    comm.Connection=conn;
    comm.Parameters.Clear();
    comm.CommandType=CommandType.StoredProcedure ;
    comm.CommandText =procName;
    for(int i=0;i<coll.Length;i++)
    {
     comm.Parameters .Add(coll[i]);
    }
    adapter = new SqlDataAdapter(comm) ;
    adapter.Fill(dt);
    adapter.Dispose();
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    comm.Parameters.Clear();
    if(conn.State == ConnectionState.Open)
     conn.Close();
    conn.Dispose();
    comm.Dispose();
   }
   return dt;
}

/// <summary>
/// 执行SQLCommand并返回数据表
/// </summary>
/// <param name="sSql">SQL语句</param>
/// <param name="coll">SqlParameter集合</param>
/// <returns>成果返回存储过程返回查询结果</returns>
public static DataTable ExecuteSqlCommandToDataTable(string sSql,SqlParameter[] coll)
{
   DataTable dt=new DataTable();
    // 创建SqlConnection对象
   SqlConnection conn=new SqlConnection(ConnectionString);
   SqlCommand comm=new SqlCommand();
   try
   {
    conn.Open();
    comm.Connection=conn;
    comm.Parameters.Clear();
    comm.CommandType=CommandType.Text ;
    comm.CommandText =sSql;
    for(int i=0;i<coll.Length;i++)
    {
     comm.Parameters .Add(coll[i]);
    }
    SqlDataAdapter da = new SqlDataAdapter(comm) ;
       da.Fill(dt);
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    comm.Parameters.Clear();
    if(conn.State == ConnectionState.Open)
     conn.Close();
    conn.Dispose();
    comm.Dispose();
   }
   return dt;
}

/// <summary>
/// 执行SQLCommand并返回第一行第一列的值
/// </summary>
/// <param name="sSql"></param>
/// <returns>object,需要拆箱操作</returns>
public static string ExecuteSqlToSingleValue(string sSql)
{
   string ret="";
   SqlConnection conn=new SqlConnection(ConnectionString);
   SqlCommand comm=new SqlCommand();
   try
   {
    conn.Open();
    comm.Connection=conn;
    comm.CommandType =CommandType.Text ;
    comm.CommandText =sSql;

    ret=comm.ExecuteScalar().ToString();
   }
   catch(Exception e)
   {
    //throw new Exception(e.Message);
   }
   finally
   {
    if(conn.State == ConnectionState.Open)
     conn.Close();
    conn.Dispose();
    comm.Dispose();
   }
   return ret;
}

/// <summary>
/// 执行SQLCommand
/// </summary>
/// <param name="sSql">SQL语句</param>
/// <param name="coll">SqlParameter集合</param>
/// <returns>成果返回成功与否</returns>
public static bool ExecuteSqlCommand(string sSql,SqlParameter[] coll)
{
   bool ret=false;
    // 创建SqlConnection对象;
   SqlConnection conn=new SqlConnection(ConnectionString);
   SqlCommand comm=new SqlCommand();
   try
   {
    conn.Open();
    comm.Connection=conn;
    comm.Parameters.Clear();
    comm.CommandType=CommandType.Text ;
    comm.CommandText =sSql;
    for(int i=0;i<coll.Length;i++)
    {
     comm.Parameters .Add(coll[i]);
    }
    comm.ExecuteNonQuery();
    ret=true;
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    comm.Parameters.Clear();
    if(conn.State == ConnectionState.Open)
     conn.Close();
    conn.Dispose();
    comm.Dispose();
   }
   return ret;
}

/// <summary>
/// 执行SQLCommand
/// </summary>
/// <param name="sSql">SQL语句</param>
/// <param name="coll">SqlParameter集合</param>
/// <returns>成果返回数据集的第一行</returns>
   public static string ExecuteSqlCommandToSingleValue(string sSql,SqlParameter[] coll)
{
   string ret="";
    // 创建SqlConnection对象
   SqlConnection conn=new SqlConnection(ConnectionString);
   SqlCommand comm=new SqlCommand();
   try
   {
    conn.Open();
    comm.Connection=conn;
    comm.Parameters.Clear();
    comm.CommandType=CommandType.Text ;
    comm.CommandText =sSql;
    for(int i=0;i<coll.Length;i++)
    {
     comm.Parameters .Add(coll[i]);
    }
    ret=comm.ExecuteScalar().ToString();
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    comm.Parameters.Clear();
    if(conn.State == ConnectionState.Open)
     conn.Close();
    conn.Dispose();
    comm.Dispose();
   }
   return ret;
}

/// <summary>
/// 执行事务、传入SQL语句字符串数组
/// </summary>
/// <param name="strTrans">SQL语句数组</param>
/// <returns>成果返回成功与否</returns>
public static bool ExecuteTransAction(string[] strTrans)
{
   bool ret=false;
   // 创建SqlConnection对象;
   SqlConnection conn=new SqlConnection(ConnectionString);
   SqlCommand comm=new SqlCommand();
   try
   {
    conn.Open();
    comm.Connection=conn;
    comm.Transaction = conn.BeginTransaction();

    foreach (string str in strTrans)
    {
     comm.CommandText = str;
     comm.ExecuteNonQuery();
    }
    comm.Transaction.Commit();

    ret=true;
   }
   catch(Exception e)
   {
    comm.Transaction.Rollback();
    throw new Exception(e.Message);
   }
   finally
   {
    if(conn.State == ConnectionState.Open)
     conn.Close();
    conn.Dispose();
    comm.Dispose();
   }
   return ret;
}

/// <summary>
/// 执行多条SQL语句，实现数据库事务。sam addded
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(ArrayList SQLStringList)
{
   using (SqlConnection conn = new SqlConnection(ConnectionString))
   {
    conn.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection=conn;
    SqlTransaction tx=conn.BeginTransaction();
    cmd.Transaction=tx;
    try
    {
     for(int n=0;n<SQLStringList.Count;n++)
     {
      string strsql=SQLStringList[n].ToString();
      if (strsql.Trim().Length>1)
      {
       cmd.CommandText=strsql;
       cmd.ExecuteNonQuery();
      }
     }
     tx.Commit();
     conn.Close();//sam
    }
    catch(System.Data.SqlClient.SqlException E)
    {
     tx.Rollback();
     conn.Close();//sam
     throw new Exception(E.Message);
    }
   }
}

/// <summary>
/// 执行一段SQL语句
/// </summary>
/// <param name="sSql">SQL语句</param>
/// <returns>操作成功与否。
/// true,成功；
/// false,失败
/// </returns>
public static bool ExecuteSingleLineSql(string sSql,string connectionString)
{
   bool ret=false;
   SqlCommand comm=new SqlCommand();
   SqlConnection conn=new SqlConnection(connectionString);
   try
   {
    conn.Open();
    comm.Connection =conn;
    comm.CommandType =CommandType.Text ;
    comm.CommandText =sSql;
    comm.ExecuteNonQuery();
    ret=true;
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    if(conn.State == ConnectionState.Open)
     conn.Close();
    conn.Dispose();
    comm.Dispose();
   }
   return ret;
}

/// <summary>
/// 查询数据库，返回数据集
/// 由于多数数据操作直接返回数据表，因此实际上不推荐使用本函数
/// </summary>
/// <param name="sSQL">标准的查询SQL语句</param>
/// <returns>DataSet，其中存放查询结果集</returns>
public static DataSet SelectDataToDataset(string sSQL,string connectionString)
{
   string Sql = sSQL;
   SqlConnection conn=new SqlConnection(connectionString);
   // 创建DataSet对象
   DataSet data = new DataSet();
   // 创建DataAdapter对象并初始化
   SqlDataAdapter adapter;
   try
   {
    conn.Open();
    adapter = new SqlDataAdapter(Sql, conn);
    // 填充数据到DataSet
    adapter.Fill(data);
    //释放资源
    adapter.Dispose();

   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    if(conn.State == ConnectionState.Open)
     conn.Close();
    conn.Dispose();
   }
   return data;
}

/// <summary>

/// 执行存储过程并返回状态值
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="coll">SqlParameter集合</param>
/// <returns>成果返回存储过程返回值，失败返回-1字符串</returns>
public static string ExecutePorcedure(string procName,SqlParameter[] coll,string connectionString)
{
   string ret;
   //   SqlConnection thisConnection;
   //   // 创建SqlConnection对象
   //   thisConnection = new SqlConnection(connectionString);
   SqlConnection conn=new SqlConnection(connectionString);
   SqlCommand comm=new SqlCommand();
   try
   {
    conn.Open();
    comm.Connection=conn;
    comm.Parameters.Clear();
    comm.CommandType=CommandType.StoredProcedure ;
    comm.CommandText =procName;
    for(int i=0;i<coll.Length;i++)
    {
     comm.Parameters .Add(coll[i]);
    }
    SqlParameter RetVal = comm.Parameters.Add("RetVal", SqlDbType.Int);
    RetVal.Direction = ParameterDirection.ReturnValue;//获取返回值

/// <summary>

/// 执行存储过程并返回结果集合
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="coll">SqlParameter集合</param>
/// <returns>成果返回存储过程返回查询结果</returns>
public static DataSet ExecutePorcedureToDataSet(string procName,SqlParameter[] coll,string connectionString)
{
   DataSet ret = new DataSet();
   // 创建SqlConnection对象
   SqlConnection conn=new SqlConnection(connectionString);
   SqlCommand comm=new SqlCommand();
   try
   {
    conn.Open();
    comm.Connection=conn;
    comm.Parameters.Clear();
    comm.CommandType=CommandType.StoredProcedure ;
    comm.CommandText =procName;
    for(int i=0;i<coll.Length;i++)
    {
     comm.Parameters .Add(coll[i]);
    }
    SqlDataAdapter da = new SqlDataAdapter(comm) ;
    da.Fill(ret);
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    comm.Parameters.Clear();
    if(conn.State == ConnectionState.Open)
     conn.Close();
    conn.Dispose();
    comm.Dispose();
   }
   return ret;
}

/// <summary>

/// 在一个事务中需要传入comm执行存储过程并返回结果集合
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="coll">SqlParameter集合</param>
/// <returns>成果返回存储过程返回查询结果</returns>
public static string ExecuteTranPorcedure(string procName,SqlParameter[] coll,SqlCommand comm,string connectionString)
{
   string ret;
   //   SqlConnection thisConnection;
   //   // 创建SqlConnection对象
   //   thisConnection = new SqlConnection(ConnectionString);
   //   SqlConnection conn=new SqlConnection(ConnectionString);
   //   SqlCommand comm=com;
   try
   {
    comm.Parameters.Clear();
    comm.CommandType=CommandType.StoredProcedure ;
    comm.CommandText =procName;
    for(int i=0;i<coll.Length;i++)
    {
     comm.Parameters .Add(coll[i]);
    }
    SqlParameter RetVal = comm.Parameters.Add("RetVal", SqlDbType.Int);
    RetVal.Direction = ParameterDirection.ReturnValue;//获取返回值

    comm.ExecuteNonQuery();
    ret=RetVal.Value.ToString();
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   return ret;
}

/// <summary>
/// 执行SQLCommand并返回结果集合
/// </summary>
/// <param name="sSql">SQL语句</param>
/// <param name="coll">SqlParameter集合</param>
/// <returns>成果返回存储过程返回查询结果</returns>
public static DataSet ExecuteSqlCommandToDataSet(string sSql,SqlParameter[] coll,string connectionString)
{
DataSet ret = new DataSet();
// // 创建SqlConnection对象

   SqlConnection conn=new SqlConnection(connectionString);
   SqlCommand comm=new SqlCommand();
   try
   {
    conn.Open();
    comm.Connection=conn;
    comm.Parameters.Clear();
    comm.CommandType=CommandType.Text ;
    comm.CommandText =sSql;
    for(int i=0;i<coll.Length;i++)
    {
     comm.Parameters .Add(coll[i]);
    }
    SqlDataAdapter da = new SqlDataAdapter(comm) ;
    da.Fill(ret);
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    comm.Parameters.Clear();
    if(conn.State == ConnectionState.Open)
     conn.Close();
    conn.Dispose();
    comm.Dispose();
   }
   return ret;
}

/// <summary>
/// 执行SQLCommand并返回结果集合
/// </summary>
/// <param name="sSQL">执行Sql语句</param>
/// <returns>数据表</returns>
public static DataTable SelectDataToDataTable(string sSQL,string connectionString)
{
   string Sql = sSQL;
   DataTable dt=new DataTable();
   // 创建SqlConnection对象
   SqlConnection conn=new SqlConnection(connectionString);
   // 创建DataAdapter对象并初始化
   SqlDataAdapter adapter=new SqlDataAdapter();
   try
   {
    conn.Open();
    adapter = new SqlDataAdapter(Sql, conn);
    //填充数据到DataSet
    adapter.Fill(dt);

/// <summary>
/// 执行存储过程并返回数据表
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="coll">SqlParameter集合</param>
/// <returns>成果返回存储过程返回查询结果</returns>
public static DataTable ExecutePorcedureToDataTable(string procName,SqlParameter[] coll,string connectionString)
{
   DataTable dt=new DataTable();
   // 创建SqlConnection对象
   SqlConnection conn=new SqlConnection(connectionString);
   SqlDataAdapter adapter;
   SqlCommand comm=new SqlCommand();
   try
   {
    conn.Open();
    comm.Connection=conn;
    comm.Parameters.Clear();
    comm.CommandType=CommandType.StoredProcedure ;
    comm.CommandText =procName;
    for(int i=0;i<coll.Length;i++)
    {
     comm.Parameters .Add(coll[i]);
    }
    adapter = new SqlDataAdapter(comm) ;
    adapter.Fill(dt);
    adapter.Dispose();
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    comm.Parameters.Clear();
    if(conn.State == ConnectionState.Open)
     conn.Close();
    conn.Dispose();
    comm.Dispose();
   }
   return dt;
}

/// <summary>
/// 执行SQLCommand并返回数据表
/// </summary>
/// <param name="sSql">SQL语句</param>
/// <param name="coll">SqlParameter集合</param>
/// <returns>成果返回存储过程返回查询结果</returns>
public static DataTable ExecuteSqlCommandToDataTable(string sSql,SqlParameter[] coll,string connectionString)
{
   DataTable dt=new DataTable();
   // 创建SqlConnection对象
   SqlConnection conn=new SqlConnection(connectionString);
   SqlCommand comm=new SqlCommand();
   try
   {
    conn.Open();
    comm.Connection=conn;
    comm.Parameters.Clear();
    comm.CommandType=CommandType.Text ;
    comm.CommandText =sSql;
    for(int i=0;i<coll.Length;i++)
    {
     comm.Parameters .Add(coll[i]);
    }
    SqlDataAdapter da = new SqlDataAdapter(comm) ;
    da.Fill(dt);
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    comm.Parameters.Clear();
    if(conn.State == ConnectionState.Open)
     conn.Close();
    conn.Dispose();
    comm.Dispose();
   }
   return dt;
}

/// <summary>
/// 执行SQLCommand并返回第一行第一列的值
/// </summary>
/// <param name="sSql"></param>
/// <returns>object,需要拆箱操作</returns>
public static string ExecuteSqlToSingleValue(string sSql,string connectionString)
{
   string ret="";
   SqlConnection conn=new SqlConnection(connectionString);
   SqlCommand comm=new SqlCommand();
   try
   {
    conn.Open();
    comm.Connection=conn;
    comm.CommandType =CommandType.Text ;
    comm.CommandText =sSql;

    ret=comm.ExecuteScalar().ToString();
   }
   catch(Exception e)
   {
    //throw new Exception(e.Message);
   }
   finally
   {
    if(conn.State == ConnectionState.Open)
     conn.Close();
    conn.Dispose();
    comm.Dispose();
   }
   return ret;
}

/// <summary>
/// 执行SQLCommand
/// </summary>
/// <param name="sSql">SQL语句</param>
/// <param name="coll">SqlParameter集合</param>
/// <returns>成果返回成功与否</returns>
public static bool ExecuteSqlCommand(string sSql,SqlParameter[] coll,string connectionString)
{
   bool ret=false;
   // 创建SqlConnection对象;
   SqlConnection conn=new SqlConnection(connectionString);
   SqlCommand comm=new SqlCommand();
   try
   {
    conn.Open();
    comm.Connection=conn;
    comm.Parameters.Clear();
    comm.CommandType=CommandType.Text ;
    comm.CommandText =sSql;
    for(int i=0;i<coll.Length;i++)
    {
     comm.Parameters .Add(coll[i]);
    }
    comm.ExecuteNonQuery();
    ret=true;
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    comm.Parameters.Clear();
    if(conn.State == ConnectionState.Open)
     conn.Close();
    conn.Dispose();
    comm.Dispose();
   }
   return ret;
}

/// <summary>
/// 执行SQLCommand
/// </summary>
/// <param name="sSql">SQL语句</param>
/// <param name="coll">SqlParameter集合</param>
/// <returns>成果返回数据集的第一行</returns>
public static string ExecuteSqlCommandToSingleValue(string sSql,SqlParameter[] coll,string connectionString)
{
   string ret="";
   // 创建SqlConnection对象
   SqlConnection conn=new SqlConnection(connectionString);
   SqlCommand comm=new SqlCommand();
   try
   {
    conn.Open();
    comm.Connection=conn;
    comm.Parameters.Clear();
    comm.CommandType=CommandType.Text ;
    comm.CommandText =sSql;
    for(int i=0;i<coll.Length;i++)
    {
     comm.Parameters .Add(coll[i]);
    }
    ret=comm.ExecuteScalar().ToString();
   }
   catch(Exception e)
   {
    throw new Exception(e.Message);
   }
   finally
   {
    comm.Parameters.Clear();
    if(conn.State == ConnectionState.Open)
     conn.Close();
    conn.Dispose();
    comm.Dispose();
   }
   return ret;
}

/// <summary>
/// 执行事务、传入SQL语句字符串数组
/// </summary>
/// <param name="strTrans">SQL语句数组</param>
/// <returns>成果返回成功与否</returns>
public static bool ExecuteTransAction(string[] strTrans,string connectionString)
{
   bool ret=false;
   // 创建SqlConnection对象;
   SqlConnection conn=new SqlConnection(connectionString);
   SqlCommand comm=new SqlCommand();
   try
   {
    conn.Open();
    comm.Connection=conn;
    comm.Transaction = conn.BeginTransaction();

    foreach (string str in strTrans)
    {
     comm.CommandText = str;
     comm.ExecuteNonQuery();
    }
    comm.Transaction.Commit();

/// <summary>
/// 执行多条SQL语句，实现数据库事务。sam addded
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(ArrayList SQLStringList,string connectionString)
{
   using (SqlConnection conn = new SqlConnection(connectionString))
   {
    conn.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection=conn;
    SqlTransaction tx=conn.BeginTransaction();
    cmd.Transaction=tx;
    try
    {
     for(int n=0;n<SQLStringList.Count;n++)
     {
      string strsql=SQLStringList[n].ToString();
      if (strsql.Trim().Length>1)
      {
       cmd.CommandText=strsql;
       cmd.ExecuteNonQuery();
      }
     }
     tx.Commit();
     conn.Close();//sam
    }
    catch(System.Data.SqlClient.SqlException E)
    {
     tx.Rollback();
     conn.Close();//sam
     throw new Exception(E.Message);
    }
   }
}

}
}