网上和书籍上看到很多的数据库访问类,众说纷纭,概念很多,理念也很多,有的侧重于语法的解释,有的侧重于概念和技巧的探讨,有的强调工程结构的优化,有的面向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;//获取返回值
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,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);
}
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,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();
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,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);
}
}
}
}
}