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