.net数据库操作类2

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using OSLeagueForumXP.Components;

namespace OSLeagueForumXP.Common
{
/// <summary>
/// 数据库操作通用类
/// </summary>
public class DataBase : IDisposable
{
/// <summary>
/// 数据库连接源
/// </summary>
private SqlConnection SqlConn;

/// <summary>
/// 运行SQL Server储存过程
/// </summary>
/// <param name="ProcedureName">储存过程名称</param>
/// <returns>储存过程返回的值</returns>
public int RunProcedure(string ProcedureName)
{
SqlCommand Sqlcmd = CreateCommand(ProcedureName, null);
Sqlcmd.ExecuteNonQuery();
this.Close();
return (int)Sqlcmd.Parameters["ReturnValue"].Value;
}

/// <summary>
/// 运行SQL Server储存过程
/// </summary>
/// <param name="ProcedureName">储存过程名称</param>
/// <param name="SqlPrams">储存过程参数</param>
/// <returns>储存过程返回值</returns>
public int RunProcedure(string ProcedureName, SqlParameter[] SqlPrams)
{
SqlCommand Sqlcmd = CreateCommand(ProcedureName, SqlPrams);
Sqlcmd.ExecuteNonQuery();
this.Close();
return (int)Sqlcmd.Parameters["ReturnValue"].Value;
}

/// <summary>
/// 运行SQL Server储存过程
/// </summary>
/// <param name="ProcedureName">储存过程名称</param>
/// <param name="SqlReader">以SqlDataReader返回储存过程结果</param>
public void RunProcedure(string ProcedureName, out SqlDataReader SqlReader)
{
SqlCommand Sqlcmd = CreateCommand(ProcedureName, null);
SqlReader = Sqlcmd.ExecuteReader(CommandBehavior.CloseConnection);
}

/// <summary>
/// 运行SQL Server储存过程
/// </summary>
/// <param name="ProcedureName">储存过程名称</param>
/// <param name="SqlPrams">储存过程参数</param>
/// <param name="SqlReader">以SqlDataReader返回储存过程结果</param>
public void RunProcedure(string ProcedureName, SqlParameter[] SqlPrams, out SqlDataReader SqlReader)
{
SqlCommand Sqlcmd = CreateCommand(ProcedureName, SqlPrams);
SqlReader = Sqlcmd.ExecuteReader(CommandBehavior.CloseConnection);
}

/// <summary>
/// 运行Sql Server储存过程
/// </summary>
/// <param name="ProcedureName">储存过程名称</param>
/// <param name="Table">数据表</param>
/// <returns>DataSet对象</returns>
public DataSet RunProcedure(string ProcedureName, string Table)
{
DataSet SqlDS = new DataSet();
SqlDataAdapter SqlDA = CreateDataAdapter(ProcedureName,null);
SqlDA.Fill(SqlDS,Table);
return(SqlDS);
}

/// <summary>
/// 运行SQL Server储存过程
/// </summary>
/// <param name="ProcedureName">储存过程名称</param>
/// <param name="SqlPrams">Sql参数</param>
/// <param name="Table">数据表</param>
/// <returns>DataSet对象</returns>
public DataSet RunProcedure(string ProcedureName, SqlParameter[] SqlPrams,string Table)
{
DataSet SqlDS = new DataSet();
SqlDataAdapter SqlDA = CreateDataAdapter(ProcedureName,SqlPrams);
SqlDA.Fill(SqlDS,Table);
return(SqlDS);
}

/// <summary>
/// 运行Sql Server储存过程
/// </summary>
/// <param name="ProcedureName">储存过程名称</param>
/// <param name="Table">数据表</param>
/// <param name="StartRecord">开始记录</param>
/// <param name="MaxRecord">最大记录数</param>
/// <returns>DataSet对象</returns>
public DataSet RunProcedure(string ProcedureName, string Table,int StartRecord,int MaxRecord)
{
DataSet SqlDS = new DataSet();
SqlDataAdapter SqlDA = CreateDataAdapter(ProcedureName,null);
SqlDA.Fill(SqlDS,StartRecord,MaxRecord,Table);
return(SqlDS);
}

/// <summary>
/// 运行SQL Server储存过程
/// </summary>
/// <param name="ProcedureName">储存过程名称</param>
/// <param name="SqlPrams">Sql参数</param>
/// <param name="Table">数据表</param>
/// <param name="StartRecord">开始记录</param>
/// <param name="MaxRecord">最大记录数</param>
/// <returns>DataSet对象</returns>
public DataSet RunProcedure(string ProcedureName, SqlParameter[] SqlPrams,string Table,int StartRecord,int MaxRecord)
{
DataSet SqlDS = new DataSet();
SqlDataAdapter SqlDA = CreateDataAdapter(ProcedureName,SqlPrams);
SqlDA.Fill(SqlDS,StartRecord,MaxRecord,Table);
return(SqlDS);
}

/// <summary>
/// 创建SqlDataAdapter对象
/// </summary>
/// <param name="ProcedureName">储存过程</param>
/// <param name="SqlPrams">Sql参数</param>
/// <param name="Type">类型</param>
/// <returns>SqlDataAdapter对象</returns>
private SqlDataAdapter CreateDataAdapter(string ProcedureName, SqlParameter[] SqlPrams)
{
//打开数据库连接
Open();

SqlDataAdapter SqlDA = new SqlDataAdapter(ProcedureName, SqlConn);

SqlDA.SelectCommand.CommandType = CommandType.StoredProcedure;
// 添加储存过程参数
if (SqlPrams != null)
{
foreach (SqlParameter Sqlparameter in SqlPrams)
{
SqlDA.SelectCommand.Parameters.Add(Sqlparameter);
}
}

// 返回参数
SqlDA.SelectCommand.Parameters.Add(
new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null));

return SqlDA;
}

/// <summary>
/// 创建一个SqlCommand对象来调用储存过程
/// </summary>
/// <param name="ProcedureName">储存过程名称</param>
/// <param name="SqlPrams">储存过程参数</param>
/// <returns>SqlCommand对象</returns>
private SqlCommand CreateCommand(string ProcedureName, SqlParameter[] SqlPrams)
{
//打开数据库连接
Open();

SqlCommand Sqlcmd = new SqlCommand(ProcedureName, SqlConn);
Sqlcmd.CommandType = CommandType.StoredProcedure;

// 添加储存过程参数
if (SqlPrams != null)
{
foreach (SqlParameter Sqlparameter in SqlPrams)
{
Sqlcmd.Parameters.Add(Sqlparameter);
}
}

// 返回参数
Sqlcmd.Parameters.Add(
new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null));

return Sqlcmd;
}

/// <summary>
/// 打开数据库连接
/// </summary>
public void Open()
{
INI clsini = new INI();
string strGetKey = clsini.Read("Common","SqlConn","",HttpContext.Current.Server.MapPath("../config.aspx"));
// 如果没有打开数据库，则打开数据库连接
SqlConn = new SqlConnection(strGetKey);
SqlConn.Open();
}

/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close()
{
if (SqlConn != null)
SqlConn.Close();
}

/// <summary>
/// 释放资源
/// </summary>
public void Dispose()
{
// 确定以关闭数据库连接
if (SqlConn != null)
{
SqlConn.Dispose();
SqlConn = null;
}
}

/// <summary>
/// 创建输入参数
/// </summary>
/// <param name="ParameterName">参数名称</param>
/// <param name="DbType">参数类型</param>
/// <param name="Size">参数大小</param>
/// <param name="Value">参数值</param>
/// <returns>新的参数</returns>
public SqlParameter MakeInputParameter(string ParameterName, SqlDbType DbType, int Size, object Value)
{
return MakeParameter(ParameterName, DbType, Size, ParameterDirection.Input, Value);
}

/// <summary>
/// 创建输出参数
/// </summary>
/// <param name="ParameterName">参数名称</param>
/// <param name="DbType">参数类型</param>
/// <param name="Size">参数大小</param>
/// <returns>新的参数</returns>
public SqlParameter MakeOutParameter(string ParameterName, SqlDbType DbType, int Size)
{
return MakeParameter(ParameterName, DbType, Size, ParameterDirection.Output, null);
}

/// <summary>
/// 创建储存过程参数
/// </summary>
/// <param name="ParameterName">参数名称</param>
/// <param name="DbType">参数类型</param>
/// <param name="Size">参数大小</param>
/// <param name="Direction">参数方法</param>
/// <param name="Value">参数值</param>
/// <returns>新的参数</returns>
public SqlParameter MakeParameter(string ParameterName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
SqlParameter SqlParam;

if(Size > 0)
{
SqlParam = new SqlParameter(ParameterName, DbType, Size);
}
else
{
SqlParam = new SqlParameter(ParameterName, DbType);
}

SqlParam.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
{
SqlParam.Value = Value;
}

return SqlParam;
}
}
}