在昨天那篇数据库封装类里还不支持存储过程，今天我加上了存储过程的支持。希望对大家有点用
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
namespace DataOperation
{
/// <summary>
/// MsSql操作类
/// </summary>
sealed public class DataMsSql
{
private SqlConnection dataConnection = null;
#region 构造函数
/// <summary>
/// 无参构造函数
/// </summary>
public DataMsSql()
{
dataConnection = new SqlConnection();
}
/// <summary>
/// 有参构造函数
/// </summary>
/// <param name="connectionString">数据库联接串</param>
public DataMsSql(string connectionString)
{
dataConnection = new SqlConnection(connectionString);
}
#endregion
#region 属性
/// <summary>
/// 设置或获取SqlConnection类型数据库联接dataConnection的值
/// </summary>
public SqlConnection DataConnection
{
get
{
if (dataConnection == null)
dataConnection = new SqlConnection();
return dataConnection;
}
set
{
dataConnection = value;
}
}
/// <summary>
/// 获取数据库联接的状态
/// </summary>
public ConnectionState SqlState
{
get
{
return dataConnection.State;
}
}
#endregion
#region 状态操作
/// <summary>
/// 打开数据库联接
/// </summary>
public void Open()
{
if (dataConnection.State != ConnectionState.Open)
dataConnection.Open();
}
/// <summary>
/// 关闭数据库联接
/// </summary>
public void Close()
{
if (dataConnection.State != ConnectionState.Closed)
dataConnection.Close();
}
#endregion
#region 一般数据语句操作
/// <summary>
/// 获取检索出来首行首列的值
/// </summary>
/// <param name="sqlStr">sql查询串</param>
/// <returns>结果</returns>
public object ExecuteScalar(string sqlStr)
{
if (SqlState == ConnectionState.Closed)
this.Open();
SqlCommand cmd = new SqlCommand(sqlStr, dataConnection);
return cmd.ExecuteScalar();
}
/// <summary>
///检索数据以SqlDataReader形式返检索结果
/// </summary>
/// <param name="sqlStr">sql查询串</param>
/// <returns>SqlDataReader数据集</returns>
public SqlDataReader GetDataReader(string sqlStr)
{
if (SqlState == ConnectionState.Closed)
this.Open();
SqlCommand cmd = new SqlCommand(sqlStr, dataConnection);
return cmd.ExecuteReader();
}
/// <summary>
/// 检索数据以DataTable形式返检索结果
/// </summary>
/// <param name="sqlStr">sql查询串</param>
/// <returns>DataTale数据集</returns>
public DataTable GetDataTable(string sqlStr)
{
DataTable dt = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(sqlStr, dataConnection);
adapter.Fill(dt);
return dt;
}
/// <summary>
/// 执行出检索以外的其它数据操作返回影响条数
/// </summary>
/// <param name="sqlStr">sql操作语句</param>
/// <returns> 操作影响条数</returns>
public int ExecuteSql(String sqlStr)
{
if (SqlState == ConnectionState.Closed)
this.Open();
SqlCommand cmd = new SqlCommand(sqlStr, DataConnection);
return cmd.ExecuteNonQuery();
}
#endregion
#region 存储过程操作
/// <summary>
/// 执行存储过程获得DataTable数据
/// </summary>
/// <param name="storedProcedureName">存储过程名称</param>
/// <returns>DataTable形式的结果集</returns>
public DataTable StoredProcGetDataTable(string storedProcedureName)
{
return StoredProcGetDataTable(null, storedProcedureName);
}
/// <summary>
/// 执行存储过程获得DataTable数据
/// </summary>
/// <param name="sqlParameters">存储过程参数ArrayList类型的SqlParameter集合</param>
/// <param name="storedProcedureName">存储过程名称</param>
/// <returns>DataTable形式的结果集</returns>
public DataTable StoredProcGetDataTable(ArrayList sqlParameters, string storedProcedureName)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = dataConnection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcedureName;
if(sqlParameters != null && sqlParameters.Count != 0)
foreach(object parameter in sqlParameters)
{
cmd.Parameters.Add((SqlParameter)parameter);
}
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
return dt;
}
/// <summary>
/// 执行存储过程返回影响条数
/// </summary>
/// <param name="storedProcedureName">存储过程名称</param>
/// <returns>影响条数</returns>
public int StoredProcExecute(string storedProcedureName)
{
return StoredProcExecute(null, storedProcedureName);
}
/// <summary>
/// 执行存储过程返回影响条数
/// </summary>
/// <param name="sqlParameters">存储过程参数ArrayList类型的SqlParameter集合</param>
/// <param name="storedProcedureName">存储过程名称</param>
/// <returns>影响条数</returns>
public int StoredProcExecute(ArrayList sqlParameters, string storedProcedureName)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = dataConnection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcedureName;
if (sqlParameters != null && sqlParameters.Count != 0)
foreach (object parameter in sqlParameters)
{
cmd.Parameters.Add((SqlParameter)parameter);
}
return cmd.ExecuteNonQuery();
}
/// <summary>
/// 执行存储过程获得数据并以SqlDataReader返回结果
/// </summary>
/// <param name="storedProcedureName">存储过程名称</param>
/// <returns>SqlDataReader形式的结果集</returns>
public SqlDataReader StoredProcGetDataReader(string storedProcedureName)
{
return StoredProcGetDataReader(null, storedProcedureName);
}
/// <summary>
/// 执行存储过程获得数据并以SqlDataReader返回结果
/// </summary>
/// <param name="sqlParameters">存储过程参数ArrayList类型的SqlParameter集合</param>
/// <param name="storedProcedureName">存储过程名称</param>
/// <returns>SqlDataReader形式的结果集</returns>
public SqlDataReader StoredProcGetDataReader(ArrayList sqlParameters, string storedProcedureName)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = dataConnection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcedureName;
if (sqlParameters != null && sqlParameters.Count != 0)
foreach (object parameter in sqlParameters)
{
cmd.Parameters.Add((SqlParameter)parameter);
}
return cmd.ExecuteReader();
}
/// <summary>
/// 执行存储过程获得数据并返回首行首列
/// </summary>
/// <param name="storedProcedureName">存储过程名称</param>
/// <returns>返回首行首列</returns>
public object StoredProcExecuteScalar(string storedProcedureName)
{
return StoredProcExecuteScalar(null, storedProcedureName);
}
/// <summary>
/// 执行存储过程获得数据并返回首行首列
/// </summary>
/// <param name="sqlParameters">存储过程参数ArrayList类型的SqlParameter集合</param>
/// <param name="storedProcedureName">存储过程名称</param>
/// <returns>返回首行首列</returns>
public object StoredProcExecuteScalar(ArrayList sqlParameters, string storedProcedureName)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = dataConnection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcedureName;
if (sqlParameters != null && sqlParameters.Count != 0)
foreach (object parameter in sqlParameters)
{
cmd.Parameters.Add((SqlParameter)parameter);
}
return cmd.ExecuteScalar();
}
#endregion
#region 事务操作
/// <summary>
/// 开启事务
/// </summary>
public void BeginTransaction()
{
ExecuteSql("begin transaction;");
}
/// <summary>
/// 提交事务
/// </summary>
public void Commit()
{
ExecuteSql("commit;");
}
/// <summary>
/// 回滚事务
/// </summary>
public void Rollback()
{
ExecuteSql("rollback;");
}
#endregion
}
}
