/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程的名称</param>
/// <param name="dataSet">返回DataSet对象</param>
public void RunProc(string procName, ref DataSet dataSet)
{
if (dataSet == null)
{
dataSet = new DataSet();
}
///创建SqlDataAdapter
SqlDataAdapter da = CreateProcDataAdapter(procName, null);
try
{
///读取数据
da.Fill(dataSet);
}
catch (Exception ex)
{
///记录错误日志
SystemError.CreateErrorLog(ex.Message);
}
finally
{
///关闭数据库的连接
Close();
}
}
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <param name="prams">SQL语句所需参数</param>
/// <param name="dataSet">返回DataSet对象</param>
public void RunSQL(string cmdText, SqlParameter[] prams, ref DataSet dataSet)
{
if (dataSet == null)
{
dataSet = new DataSet();
}
///创建SqlDataAdapter
SqlDataAdapter da = CreateProcDataAdapter(cmdText, prams);
try
{
///读取数据
da.Fill(dataSet);
}
catch (Exception ex)
{
///记录错误日志
SystemError.CreateErrorLog(ex.Message);
}
finally
{
///关闭数据库的连接
Close();
}
}
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <param name="prams">SQL语句所需参数</param>
/// <param name="dataReader">返回DataReader对象</param>
public void RunSQL(string cmdText, SqlParameter[] prams, out SqlDataReader dataReader)
{
///创建Command
SqlCommand cmd = CreateSQLCommand(cmdText, prams);
try
{
///读取数据
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
dataReader = null;
///记录错误日志
SystemError.CreateErrorLog(ex.Message);
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程的名称</param>
/// <param name="dataReader">返回存储过程返回值</param>
public void RunProc(string procName, out SqlDataReader dataReader)
{
///创建Command
SqlCommand cmd = CreateProcCommand(procName, null);
try
{
///读取数据
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
dataReader = null;
///记录错误日志
SystemError.CreateErrorLog(ex.Message);
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程的名称</param>
/// <returns>返回存储过程返回值</returns>
public int RunProc(string procName)
{
SqlCommand cmd = CreateProcCommand(procName, null);
try
{
///执行存储过程
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
///记录错误日志
SystemError.CreateErrorLog(ex.Message);
}
finally
{
///关闭数据库的连接
Close();
}
///返回存储过程的参数值
return((int)cmd.Parameters[RETURNVALUE].Value);
}
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <param name="prams">SQL语句所需参数</param>
/// <returns>返回值</returns>
public int RunSQL(string cmdText, SqlParameter[] prams)
{
SqlCommand cmd = CreateSQLCommand(cmdText, prams);
try
{
///执行存储过程
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
///记录错误日志
SystemError.CreateErrorLog(ex.Message);
}
finally
{
///关闭数据库的连接
Close();
}
///返回存储过程的参数值
return((int)cmd.Parameters[RETURNVALUE].Value);
}
/// <summary>
/// 将DataReader转为DataTable
/// </summary>
/// <param name="DataReader">DataReader</param>
public static DataTable ConvertDataReaderToDataTable(SqlDataReader dataReader)
{
///定义DataTable
DataTable datatable = new DataTable();
try
{ ///动态添加表的数据列
for (int i = 0; i < dataReader.FieldCount; i++)
{
DataColumn myDataColumn = new DataColumn();
myDataColumn.DataType = dataReader.GetFieldType(i);
myDataColumn.ColumnName = dataReader.GetName(i);
datatable.Columns.Add(myDataColumn);
}
///添加表的数据
while (dataReader.Read())
{
DataRow myDataRow = datatable.NewRow();
for (int i = 0; i < dataReader.FieldCount; i++)
{
myDataRow[i] = dataReader[i].ToString();
}
datatable.Rows.Add(myDataRow);
myDataRow = null;
}
///关闭数据读取器
dataReader.Close();
return(datatable);
}
catch (Exception ex)
{
///抛出类型转换错误
SystemError.CreateErrorLog(ex.Message);
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// 打开数据库连接.
/// </summary>
private void Open()
{
// 打开数据库连接
if (myConnection == null)
{
myConnection = new SqlConnection(WebConfigurationManager.ConnectionStrings["Connection String"].ConnectionString);
}
if (myConnection.State == ConnectionState.Closed)
{
try
{
///打开数据库连接
myConnection.Open();
}
catch (Exception ex)
{
SystemError.CreateErrorLog(ex.Message);
}
finally
{
///关闭已经打开的数据库连接
}
}
}
/// <summary>
/// 打开数据库连接.
/// </summary>
private void Open()
{
// 打开数据库连接
if (myConnection == null)
{
myConnection = new SqlConnection(ConfigurationManager.AppSettings["SQLCONNECTIONSTRING"].ToString());
}
if (myConnection.State == ConnectionState.Closed)
{
try
{
///打开数据库连接
myConnection.Open();
}
catch (Exception ex)
{
SystemError.CreateErrorLog(ex.Message);
}
finally
{
///关闭已经打开的数据库连接
}
}
}