Asp.net技巧：数据库操作类(C#)

Asp.net技巧：数据库操作类(C#)

 

　　ASP.NET中一般都是使用SQL Server作为后台数据库。一般的ASP.NET数据库操作示例程序都是使用单独的数据访问，就是说每个页面都写连接到数据库，存取数据，关闭数据库的代码。这种方式带来了一些弊端，一个就是如果你的数据库改变了，你必须一个页面一个页面的去更改数据库连接代码。第二个弊端就是代码冗余，很多代码都是重复的，不必要的。因此，我试图通过一种一致的数据库操作类来实现ASP.NET的数据访问,在我之前的文章.net数据库操作类(C#) 中已经简单总结了对数据库的操作，现在.net数据库操作类(C#) 的基础上进行了如下改进：

1、添加了事务处理机制；
2、添加了SQL参数化操作；
3、添加了对存储过程的操作；
4、添加了对SQL参数和存储过程的综合操作；

现把.net数据库操作类代码公布出来，如有问题，欢迎大家指正！

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.IO;
using System.Globalization;

namespace VoteInfo
{
public class DataAccess
{
定义#region 定义
protected SqlCommand Comm;
protected SqlDataAdapter Adap;
protected SqlConnection Conn; //SQL连接
private string _connectString; //连接串
private string _commandString; //SQL命令
private Hashtable _dict, _result, _mapTable;
private DataSet _ds; //返回结果数据集
private DataRow _recordSet; //纪录集
private string _tableName; //表名
private int _recordCount; //纪录集的行数
private bool _eOF; //结果集是否为空，是否已经到了结尾
private string DB;
private string _deleteOP;
private string _path;
private StreamWriter SWCreate, SWApp;
private string _errorMessage;
private bool _writeLog;
#endregion

属性集#region 属性集
/** <summary>
/// 出错信息
/// </summary>
///
public string ErrorMessage
{
get { return this._errorMessage; }
set { this._errorMessage = value; }
}

/**//**/
/** <summary>
/// 设置或者取得删除的操作者
/// </summary>
public string DeleteOP
{
get { return this._deleteOP; }
set { this._deleteOP = value; }
}

/**//**/
/** <summary>
/// 取得是否溢出
/// </summary>
public bool EOF
{
get { return this._eOF; }
set { this._eOF = value; }
}

/**//**/
/** <summary>
/// 取得执行语句后得出的纪录条数
/// </summary>
public int RecordCount
{
get { return this._recordCount; }
set { this._recordCount = value; }
}

/**//**/
/** <summary>
/// 数据库中的表名 http://www.dwww.cn
/// </summary>
public string TableName
{
get { return this._tableName; }
set { this._tableName = value; }
}

/**//**/
/** <summary>
/// 返回的记录集
/// </summary>
public DataRow RecordSet
{
get { return this._recordSet; }
set { this._recordSet = value; }
}

/**//**/
/** <summary>
/// 返回的数据集
/// </summary>
public DataSet DS
{
get { return this._ds; }
set { this._ds = value; }
}

/**//**/
/** <summary>
/// 字段和控件的映射表
/// </summary>
public Hashtable MapTable
{
get { return this._mapTable; }
set { this._mapTable = value; }
}

/**//**/
/** <summary>
/// 修改数据时，作为修改结果
/// </summary>
public Hashtable Result
{
get { return this._result; }
set { this._result = value; }
}

/**//**/
/** <summary>
/// 保存数据用的字段和值对应的哈希表，修改数据时用作条件
/// </summary>
public Hashtable Dict
{
get { return this._dict; }
set { this._dict = value; }
}

/**//**/
/** <summary>
/// 查询语句
/// </summary>
public string CommandString
{
get { return this._commandString; }
set { this._commandString = value; }
}

/**//**/
/** <summary>
/// 连接串
/// </summary>
public string ConnectString
{
get { return this._connectString; }
set { this._connectString = value; }
}

#endregion

DataAccess的构造函数#region DataAccess的构造函数
/** <summary>
/// 空构造函数
/// </summary>
public DataAccess()
{
ConnectString = System.Configuration.ConfigurationSettings.AppSettings["DBConn"];
Conn = new System.Data.SqlClient.SqlConnection(ConnectString);
if (System.Configuration.ConfigurationSettings.AppSettings["WriteLog"] == "true")
{
_writeLog = true;
}
else
{
_writeLog = false;
}

}

~DataAccess()
{
}

/**//**/
/** <summary>
/// DataAccess的构造函数
/// </summary>
/// <param name="DB1">要访问的数据库名，Web.config里设置的连接字符串对应的key</param>
/// <param name="TableName1">要访问的数据表名</param>
public DataAccess(string DB1, string TableName1)
{
this.ErrorMessage = "";
DB = DB1;
TableName = TableName1;
try
{
ConnectString = System.Configuration.ConfigurationSettings.AppSettings[DB1];
if (System.Configuration.ConfigurationSettings.AppSettings["WriteLog"] == "true")
{
_writeLog = true;
}
else
{
_writeLog = false;
}
Conn = new System.Data.SqlClient.SqlConnection(ConnectString);
Dict = new Hashtable();
Result = new Hashtable();
MapTable = new Hashtable();
DS = new DataSet();

// IS_Open = false;
_path = "C://WebDebug.log";
if (_writeLog)
{
if (!File.Exists(_path))
{
using (SWCreate = File.CreateText(_path))
{
SWCreate.WriteLine(" ");
SWCreate.Close();
}
}
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine(" ");
}
}
}
catch (Exception e)
{
this.ErrorMessage = e.ToString();
}
}

/**//**/
/** <summary>
/// DataAccess的构造函数
/// </summary>
/// <param name="CST">数据库的连接字符串</param>
/// <param name="TableName1">要访问的数据表名</param>
/// <param name="flag">是否初始化</param>
public DataAccess(string CST, string TableName1, bool flag)
{
if (flag == true)
{
this.ErrorMessage = "";
TableName = TableName1;
try
{
if (System.Configuration.ConfigurationSettings.AppSettings["WriteLog"] == "true")
{
_writeLog = true;
}
else
{
_writeLog = false;
}
ConnectString = CST;
Conn = new System.Data.SqlClient.SqlConnection(ConnectString);
Dict = new Hashtable();
Result = new Hashtable();
MapTable = new Hashtable();
DS = new DataSet();

_path = "C://WebDebug.log";
if (_writeLog)
{
if (!File.Exists(_path))
{
using (SWCreate = File.CreateText(_path))
{
SWCreate.WriteLine(" ");
SWCreate.Close();
}
}
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine(" ");
}
}
}
catch (Exception e)
{
this.ErrorMessage = e.ToString();
}
}
}
#endregion

ExecuteNonQuery#region ExecuteNonQuery
/** <summary>
/// 执行无返回结果的SQL http://dwww.cn
/// </summary>
/// <param name="strSQL"></param>
public void ExecuteNonQuery(string strSQL)
{
Comm = new SqlCommand();
SqlTransaction Trans;
Conn.Open();
Trans = Conn.BeginTransaction();
Comm.CommandText = strSQL;
Comm.Connection = Conn;
Comm.Transaction = Trans;
Comm.CommandTimeout = 60;

try
{
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "] CommandString = " + strSQL);
SWApp.Close();
}
}

Comm.ExecuteNonQuery();
Trans.Commit();
this.Conn.Close();
}
catch (Exception e)
{
Trans.Rollback();
this.Conn.Close();
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "] Error Message ： " + e.ToString());
}
}
this.ErrorMessage = e.ToString();
throw new Exception(e.ToString());
}
}

/** <summary>
/// 执行无返回结果的SQL
/// </summary>
/// <param name="param">参数集合</param>
/// <param name="strSQL"></param>
public void ExecuteNonQuery(string strSQL, ICollection param)
{
Comm = new SqlCommand();
SqlTransaction Trans;
Conn.Open();
Trans = Conn.BeginTransaction();
Comm.CommandText = strSQL;
Comm.Connection = Conn;
Comm.Transaction = Trans;
Comm.CommandTimeout = 60;

try
{
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "] CommandString = " + strSQL);
SWApp.Close();
}
}

if (param != null)
{
foreach (ParamInfo p in param)
{
IDbDataParameter pa = Comm.CreateParameter();
pa.ParameterName = p.Name;
pa.Value = p.Value;

//处理大文本
if (pa is System.Data.SqlClient.SqlParameter && pa.Value != null && pa.Value.ToString().Length >= 4000)
{
System.Data.SqlClient.SqlParameter p1 = pa as System.Data.SqlClient.SqlParameter;
p1.SqlDbType = System.Data.SqlDbType.NText;
Comm.Parameters.Add(p1);
}
else
{
Comm.Parameters.Add(pa);
}
}
}

Comm.ExecuteNonQuery();
FillParameterValue(Comm.Parameters, param);
Trans.Commit();
this.Conn.Close();
}
catch (Exception e)
{
Trans.Rollback();
this.Conn.Close();
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "] Error Message ： " + e.ToString());
}
}
this.ErrorMessage = e.ToString();
throw new Exception(e.ToString());
}
}
#endregion

ExecuteScalar#region ExecuteScalar
/** <summary>
/// 返回查询结果的第一行第一列的值
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public object ExecuteScalar(string strSQL)
{
SqlTransaction Trans;
Comm = new SqlCommand();
Conn.Open();
Trans = Conn.BeginTransaction();
Comm.CommandText = strSQL;
Comm.Connection = Conn;
Comm.Transaction =Trans ;
Comm.CommandTimeout = 60;
try
{
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "] CommandString = " + strSQL);
SWApp.Close();
}
}

object objResutl = Comm.ExecuteScalar();
Trans.Commit();
this.Conn.Close();
return objResutl;
}
catch (Exception e)
{
Trans.Rollback();
this.Conn.Close();
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "] Error Message ： " + e.ToString());
}
}
this.ErrorMessage = e.ToString();
throw new Exception(e.ToString());
}
}

/** <summary>
/// 返回查询结果的第一行第一列的值
/// </summary>
/// <param name="strSQL"></param>
/// <param name="param">参数集合</param>
/// <returns></returns>
public object ExecuteScalar(string strSQL,ICollection param)
{
SqlTransaction Trans;
Comm = new SqlCommand();
Conn.Open();
Trans = Conn.BeginTransaction();
Comm.CommandText = strSQL;
Comm.Connection = Conn;
Comm.Transaction =Trans ;
Comm.CommandTimeout = 60;
try
{
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "] CommandString = " + strSQL);
SWApp.Close();
}
}
if ( param != null )
{
foreach ( ParamInfo p in param )
{
IDbDataParameter pa = Comm.CreateParameter();
pa.ParameterName = p.Name;
pa.Value = p.Value;
pa.Direction = p.Direction;

Comm.Parameters.Add(pa);
}
}

object objResutl = Comm.ExecuteScalar();
FillParameterValue(Comm.Parameters,param);
Trans.Commit();
this.Conn.Close();
return objResutl;

}
catch (Exception e)
{
Trans.Rollback();
this.Conn.Close();
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "] Error Message ： " + e.ToString());
}
}
this.ErrorMessage = e.ToString();
throw new Exception(e.ToString());
}
}
#endregion

ExecuteDataSet#region ExecuteDataSet
/** <summary>
/// 执行SQL语句并返回DataTable对象
/// </summary>
public DataSet ExecuteDataSet(string strSQL)
{
SqlTransaction Trans;
Comm = new SqlCommand();
Conn.Open();
Trans = Conn.BeginTransaction();
Comm.CommandText = strSQL;
Comm.Connection = Conn;
Comm.Transaction =Trans ;
Comm.CommandTimeout = 60;
DataSet ds = new DataSet();
try
{
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Begin Get DataSet.");
SWApp.WriteLine("CommandString = " + strSQL);
SWApp.Close();
}
}

try
{
IDataReader dr = Comm.ExecuteReader();
do
{
DataTable dt = new DataTable();
dt.Locale = CultureInfo.CurrentCulture;

DataColumn col = null;
DataRowCollection rows = dr.GetSchemaTable().Rows;
foreach(DataRow row in rows)
{
col = new DataColumn();
col.ColumnName = row["ColumnName"] == null ? null : row["ColumnName"].ToString();
col.Unique = Convert.ToBoolean(row["IsUnique"]);
col.AllowDBNull = Convert.ToBoolean(row["AllowDBNull"]);
col.ReadOnly = Convert.ToBoolean(row["IsReadOnly"]);
col.DataType = row["DataType"] as Type;

dt.Columns.Add(col);
}

while (dr.Read())
{
DataRow row = dt.NewRow();

foreach(DataColumn c in dt.Columns)
{
row[c] = dr[c.ColumnName];
}

dt.Rows.Add(row);
}

ds.Tables.Add(dt);
}
while (dr.NextResult());
dr.Close();
Trans.Commit();
}
catch (Exception e)
{
Trans.Rollback();
this.ErrorMessage = e.ToString();
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Error Message： " + e.ToString());
}
}
throw new Exception(e.ToString());
}

if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> End of Getting DataSet.");
SWApp.Close();
}
}
}
finally
{
Conn.Close();
}
return ds;
}

/** <summary>
/// 执行SQL语句并返回DataTable对象
/// <param name="strSQL">SQL语句</param>
/// <param name="param">参数集合</param>
/// </summary>
public DataSet ExecuteDataSet(string strSQL, ICollection param)
{
SqlTransaction Trans;
Comm = new SqlCommand();
Conn.Open();
Trans = Conn.BeginTransaction();
Comm.CommandText = strSQL;
Comm.Connection = Conn;
Comm.Transaction =Trans ;
Comm.CommandTimeout = 60;
DataSet ds = new DataSet();
try
{
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Begin Get DataSet.");
SWApp.WriteLine("CommandString = " + strSQL);
SWApp.Close();
}
}

try
{
if ( param != null )
{
foreach ( ParamInfo p in param )
{
IDbDataParameter pa = Comm.CreateParameter();
pa.ParameterName = p.Name;
pa.Value = p.Value;
pa.Direction = p.Direction;

Comm.Parameters.Add(pa);
}
}
IDataReader dr = Comm.ExecuteReader();

do
{
DataTable dt = new DataTable();
dt.Locale = CultureInfo.CurrentCulture;

DataColumn col = null;
DataRowCollection rows = dr.GetSchemaTable().Rows;
foreach(DataRow row in rows)
{
col = new DataColumn();
col.ColumnName = row["ColumnName"] == null ? null : row["ColumnName"].ToString();
col.Unique = Convert.ToBoolean(row["IsUnique"]);
col.AllowDBNull = Convert.ToBoolean(row["AllowDBNull"]);
col.ReadOnly = Convert.ToBoolean(row["IsReadOnly"]);
col.DataType = row["DataType"] as Type;

dt.Columns.Add(col);
}

while (dr.Read())
{
DataRow row = dt.NewRow();

foreach(DataColumn c in dt.Columns)
{
row[c] = dr[c.ColumnName];
}

dt.Rows.Add(row);
}

ds.Tables.Add(dt);
}
while (dr.NextResult());
dr.Close();
Trans.Commit();
}
catch (Exception e)
{
Trans.Rollback();
this.ErrorMessage = e.ToString();
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Error Message： " + e.ToString());
}
}
throw new Exception(e.ToString());
}

if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> End of Getting DataSet.");
SWApp.Close();
}
}
}
finally
{
Conn.Close();
}
return ds;
}
#endregion

ExecuteDataTable#region ExecuteDataTable
/** <summary>
/// 执行SQL语句并返回DataTable对象
/// </summary>
public DataTable ExecuteDataTable(string strSQL)
{
return ExecuteDataSet(strSQL).Tables[0];
}

/** <summary>
/// 执行SQL语句并返回DataTable对象
/// <param name="strSQL">SQL语句</param>
/// <param name="param">参数集合</param>
/// </summary>
public DataTable ExecuteDataTable(string strSQL, ICollection param)
{
return ExecuteDataSet(strSQL,param).Tables[0];
}
#endregion

ExecuteDataReader#region ExecuteDataReader
/** <summary>
/// <param name="strSQL">SQL语句</param>
/// </summary>
public IDataReader ExecuteDataReader(string strSQL)
{
SqlTransaction Trans;
Comm = new SqlCommand();
Conn.Open();
Trans = Conn.BeginTransaction();
Comm.CommandText = strSQL;
Comm.Connection = Conn;
Comm.Transaction = Trans;
Comm.CommandTimeout = 60;
IDataReader dr ;
try
{
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Begin Get DataReader.");
SWApp.WriteLine("CommandString = " + strSQL);
SWApp.Close();
}
}

try
{
dr=Comm.ExecuteReader();
Trans.Commit();
}
catch (Exception e)
{
Trans.Rollback();
this.ErrorMessage = e.ToString();
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Error Message： " + e.ToString());
}
}
throw new Exception(e.ToString());
}

if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> End of Getting DataReader.");
SWApp.Close();
}
}
}
finally
{
Conn.Close();
}
return dr;
}

/** <summary>
///
/// <param name="strSQL">SQL语句</param>
/// <param name="param">参数集合</param>
/// </summary>
public IDataReader ExecuteDataReader(string strSQL, ICollection param)
{
SqlTransaction Trans;
Comm = new SqlCommand();
Conn.Open();
Trans = Conn.BeginTransaction();
Comm.CommandText = strSQL;
Comm.Connection = Conn;
Comm.Transaction = Trans;
Comm.CommandTimeout = 60;
IDataReader dr;
try
{
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Begin Get DataReader.");
SWApp.WriteLine("CommandString = " + strSQL);
SWApp.Close();
}
}

try
{
if (param != null)
{
foreach (ParamInfo p in param)
{
IDbDataParameter pa = Comm.CreateParameter();
pa.ParameterName = p.Name;
pa.Value = p.Value;
pa.Direction = p.Direction;

Comm.Parameters.Add(pa);
}
}
dr = Comm.ExecuteReader();
Trans.Commit();
}
catch (Exception e)
{
Trans.Rollback();
this.ErrorMessage = e.ToString();
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Error Message： " + e.ToString());
}
}
throw new Exception(e.ToString());
}

if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> End of Getting DataReader.");
SWApp.Close();
}
}
}
finally
{
Conn.Close();
}
return dr;
}
#endregion

ExecuteProcNonQuery#region ExecuteProcNonQuery
/** <summary>
/// 执行无返回结果的存储过程
/// </summary>
/// <param name="procName">存储过程名</param>
public void ExecuteProcNonQuery(string procName)
{
Comm = new SqlCommand();
SqlTransaction Trans;
Conn.Open();
Trans = Conn.BeginTransaction();
Comm.CommandType = CommandType.StoredProcedure;
Comm.CommandText = procName;
Comm.Connection = Conn;
Comm.Transaction = Trans;
Comm.CommandTimeout = 60;

try
{
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "] StoredProcedureName = " + procName);
SWApp.Close();
}
}
Comm.ExecuteNonQuery();
Trans.Commit();
this.Conn.Close();
}
catch (Exception e)
{
Trans.Rollback();
this.Conn.Close();
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "] Error Message ： " + e.ToString());
}
}
this.ErrorMessage = e.ToString();
throw new Exception(e.ToString());
}
}

/** <summary>
/// 执行无返回结果的存储过程
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="param">参数集合</param>
public void ExecuteProcNonQuery(string procName, ICollection param)
{
Comm = new SqlCommand();
SqlTransaction Trans;
Conn.Open();
Trans = Conn.BeginTransaction();
Comm.CommandType = CommandType.StoredProcedure;
Comm.CommandText = procName;
Comm.Connection = Conn;
Comm.Transaction = Trans;
Comm.CommandTimeout = 60;

try
{
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "] StoredProcedureName = " + procName);
SWApp.Close();
}
}

if (param != null)
{
foreach (ParamInfo p in param)
{
IDbDataParameter pa = Comm.CreateParameter();
pa.ParameterName = p.Name;
pa.Value = p.Value;

//处理大文本
if (pa is System.Data.SqlClient.SqlParameter && pa.Value != null && pa.Value.ToString().Length >= 4000)
{
System.Data.SqlClient.SqlParameter p1 = pa as System.Data.SqlClient.SqlParameter;
p1.SqlDbType = System.Data.SqlDbType.NText;
Comm.Parameters.Add(p1);
}
else
{
Comm.Parameters.Add(pa);
}
}
}

Comm.ExecuteNonQuery();
FillParameterValue(Comm.Parameters, param);
Trans.Commit();
this.Conn.Close();
}
catch (Exception e)
{
Trans.Rollback();
this.Conn.Close();
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "] Error Message ： " + e.ToString());
}
}
this.ErrorMessage = e.ToString();
throw new Exception(e.ToString());
}
}
#endregion

ExecuteProcScalar#region ExecuteProcScalar
/** <summary>
/// 执行存储过程并返回第一行第一列的值
/// </summary>
/// <param name="procName">存储过程名</param>
/// <returns></returns>
public object ExecuteProcScalar(string procName)
{
SqlTransaction Trans;
Comm = new SqlCommand();
Conn.Open();
Trans = Conn.BeginTransaction();
Comm.CommandType = CommandType.StoredProcedure;
Comm.CommandText = procName;
Comm.Connection = Conn;
Comm.Transaction = Trans;
Comm.CommandTimeout = 60;
try
{
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "] StoredProcedureName = " + procName);
SWApp.Close();
}
}

object objResutl = Comm.ExecuteScalar();
Trans.Commit();
this.Conn.Close();
return objResutl;
}
catch (Exception e)
{
Trans.Rollback();
this.Conn.Close();
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "] Error Message ： " + e.ToString());
}
}
this.ErrorMessage = e.ToString();
throw new Exception(e.ToString());
}
}

/** <summary>
/// 执行存储过程并返回第一行第一列的值
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="param">参数集合</param>
/// <returns></returns>
public object ExecuteProcScalar(string procName, ICollection param)
{
SqlTransaction Trans;
Comm = new SqlCommand();
Conn.Open();
Trans = Conn.BeginTransaction();
Comm.CommandType = CommandType.StoredProcedure;
Comm.CommandText = procName;
Comm.Connection = Conn;
Comm.Transaction = Trans;
Comm.CommandTimeout = 60;
try
{
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "] StoredProcedureName = " + procName);
SWApp.Close();
}
}
if (param != null)
{
foreach (ParamInfo p in param)
{
IDbDataParameter pa = Comm.CreateParameter();
pa.ParameterName = p.Name;
pa.Value = p.Value;
pa.Direction = p.Direction;

Comm.Parameters.Add(pa);
}
}

object objResutl = Comm.ExecuteScalar();
FillParameterValue(Comm.Parameters, param);
Trans.Commit();
this.Conn.Close();
return objResutl;

}
catch (Exception e)
{
Trans.Rollback();
this.Conn.Close();
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "] Error Message ： " + e.ToString());
}
}
this.ErrorMessage = e.ToString();
throw new Exception(e.ToString());
}
}
#endregion

ExecuteProcDataSet#region ExecuteProcDataSet
/** <summary>
///执行存储过程并返回DataSet对象
/// </summary>
/// <param name="procName">存储过程名</param>
/// <returns>返回DataSet对象</returns>
public DataSet ExecuteProcDataSet(string procName)
{
SqlTransaction Trans;
Comm = new SqlCommand();
Conn.Open();
Trans = Conn.BeginTransaction();
Comm.CommandType = CommandType.StoredProcedure;
Comm.CommandText = procName;
Comm.Connection = Conn;
Comm.Transaction = Trans;
Comm.CommandTimeout = 60;
DataSet ds = new DataSet();
try
{
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Begin Get DataSet.");
SWApp.WriteLine("StoredProcedureName = " + procName);
SWApp.Close();
}
}

try
{
IDataReader dr = Comm.ExecuteReader();
do
{
DataTable dt = new DataTable();
dt.Locale = CultureInfo.CurrentCulture;

DataColumn col = null;
DataRowCollection rows = dr.GetSchemaTable().Rows;
foreach (DataRow row in rows)
{
col = new DataColumn();
col.ColumnName = row["ColumnName"] == null ? null : row["ColumnName"].ToString();
col.Unique = Convert.ToBoolean(row["IsUnique"]);
col.AllowDBNull = Convert.ToBoolean(row["AllowDBNull"]);
col.ReadOnly = Convert.ToBoolean(row["IsReadOnly"]);
col.DataType = row["DataType"] as Type;

dt.Columns.Add(col);
}

while (dr.Read())
{
DataRow row = dt.NewRow();

foreach (DataColumn c in dt.Columns)
{
row[c] = dr[c.ColumnName];
}

dt.Rows.Add(row);
}

ds.Tables.Add(dt);
}
while (dr.NextResult());
dr.Close();
Trans.Commit();
}
catch (Exception e)
{
Trans.Rollback();
this.ErrorMessage = e.ToString();
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Error Message： " + e.ToString());
}
}
throw new Exception(e.ToString());
}

if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> End of Getting DataSet.");
SWApp.Close();
}
}
}
finally
{
Conn.Close();
}
return ds;
}

/** <summary>
/// 执行存储过程并返回DataSet对象
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="param">参数集合</param>
/// <returns>返回DataSet对象</returns>
public DataSet ExecuteProcDataSet(string procName, ICollection param)
{
SqlTransaction Trans;
Comm = new SqlCommand();
Conn.Open();
Trans = Conn.BeginTransaction();
Comm.CommandType = CommandType.StoredProcedure;
Comm.CommandText = procName;
Comm.Connection = Conn;
Comm.Transaction = Trans;
Comm.CommandTimeout = 60;
DataSet ds = new DataSet();
try
{
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Begin Get DataSet.");
SWApp.WriteLine("StoredProcedureName = " + procName);
SWApp.Close();
}
}

try
{
if (param != null)
{
foreach (ParamInfo p in param)
{
IDbDataParameter pa = Comm.CreateParameter();
pa.ParameterName = p.Name;
pa.Value = p.Value;
pa.Direction = p.Direction;

Comm.Parameters.Add(pa);
}
}
IDataReader dr = Comm.ExecuteReader();

do
{,
DataTable dt = new DataTable();
dt.Locale = CultureInfo.CurrentCulture;

DataColumn col = null;
DataRowCollection rows = dr.GetSchemaTable().Rows;
foreach (DataRow row in rows)
{
col = new DataColumn();
col.ColumnName = row["ColumnName"] == null ? null : row["ColumnName"].ToString();
col.Unique = Convert.ToBoolean(row["IsUnique"]);
col.AllowDBNull = Convert.ToBoolean(row["AllowDBNull"]);
col.ReadOnly = Convert.ToBoolean(row["IsReadOnly"]);
col.DataType = row["DataType"] as Type;

dt.Columns.Add(col);
}

while (dr.Read())
{
DataRow row = dt.NewRow();

foreach (DataColumn c in dt.Columns)
{
row[c] = dr[c.ColumnName];
}

dt.Rows.Add(row);
}

ds.Tables.Add(dt);
}
while (dr.NextResult());
dr.Close();
Trans.Commit();
}
catch (Exception e)
{
Trans.Rollback();
this.ErrorMessage = e.ToString();
if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Error Message： " + e.ToString());
}
}
throw new Exception(e.ToString());
}

if (_writeLog)
{
using (SWApp = File.AppendText(_path))
{
SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> End of Getting DataSet.");
SWApp.Close();
}
}
}
finally
{
Conn.Close();
}
return ds;
}
#endregion

ExecuteProcDataTable#region ExecuteProcDataTable
/** <summary>
/// 执行存储过程并返回DataTable对象
/// </summary>
/// <param name="procName">存储过程名</param>
/// <returns>返回DataTable对象</returns>
public DataTable ExecuteProcDataTable(string procName)
{
return ExecuteProcDataSet(procName).Tables[0];
}

/** <summary>
/// 执行存储过程并返回DataTable对象
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="param">参数集合</param>
/// <returns>返回DataTable对象</returns>
public DataTable ExecuteProcDataTable(string procName, ICollection param)
{
return ExecuteProcDataSet(procName, param).Tables[0];
}
#endregion

FillParameterValue#region FillParameterValue
/** <summary>
/// 填充输出型参数和返回值型参数
/// </summary>
/// <param name="OutPutParameters">SQL命令执行后的参数集合</param>
/// <param name="param">SQL命令执行前的参数集合</param>
void FillParameterValue(System.Data.IDataParameterCollection OutPutParameters, ICollection param)
{
if (OutPutParameters == null || param == null) return;

ArrayList procParam = new ArrayList();
foreach (IDbDataParameter sqlParameter in OutPutParameters) //记录经过存储过程运算后的参数值
{
foreach (ParamInfo p in param)
{
if (p.Name == sqlParameter.ParameterName)
{
procParam.Add(new ParamInfo(p.Name, sqlParameter.Value, p.Direction, p.Size));
}
}
}

ArrayList procOutParam = param as ArrayList;
procOutParam.Clear();
foreach (ParamInfo p in procParam) //填充参数值
{
procOutParam.Add(new ParamInfo(p.Name, p.Value, p.Direction, p.Size));
}
}
#endregion
}
}