//版权所有 OSLEAGUE 2002-2003
//作者：Blood
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using OSLeagueForumXP.Components;
namespace OSLeagueForumXP.Common
{
///
/// 数据库操作通用类
///
public class DataBase : IDisposable
{
///
/// 数据库连接源
///
private SqlConnection SqlConn;
///
/// 运行SQL Server储存过程
///
/// 储存过程名称
///
public int RunProcedure(string ProcedureName)
{
SqlCommand Sqlcmd = CreateCommand(ProcedureName, null);
Sqlcmd.ExecuteNonQuery();
this.Close();
return (int)Sqlcmd.Parameters["ReturnValue"].Value;
}
///
/// 运行SQL Server储存过程
///
/// 储存过程名称
/// 储存过程参数
///
public int RunProcedure(string ProcedureName, SqlParameter[] SqlPrams)
{
SqlCommand Sqlcmd = CreateCommand(ProcedureName, SqlPrams);
Sqlcmd.ExecuteNonQuery();
this.Close();
return (int)Sqlcmd.Parameters["ReturnValue"].Value;
}
///
/// 运行SQL Server储存过程
///
/// 储存过程名称
/// 以SqlDataReader返回储存过程结果
public void RunProcedure(string ProcedureName, out SqlDataReader SqlReader)
{
SqlCommand Sqlcmd = CreateCommand(ProcedureName, null);
SqlReader = Sqlcmd.ExecuteReader(CommandBehavior.CloseConnection);
}
///
/// 运行SQL Server储存过程
///
/// 储存过程名称
/// 储存过程参数
/// 以SqlDataReader返回储存过程结果
public void RunProcedure(string ProcedureName, SqlParameter[] SqlPrams, out SqlDataReader SqlReader)
{
SqlCommand Sqlcmd = CreateCommand(ProcedureName, SqlPrams);
SqlReader = Sqlcmd.ExecuteReader(CommandBehavior.CloseConnection);
}
///
/// 运行Sql Server储存过程
///
/// 储存过程名称
/// 数据表
///
public DataSet RunProcedure(string ProcedureName, string Table)
{
DataSet SqlDS = new DataSet();
SqlDataAdapter SqlDA = CreateDataAdapter(ProcedureName,null);
SqlDA.Fill(SqlDS,Table);
return(SqlDS);
}
///
/// 运行SQL Server储存过程
///
/// 储存过程名称
/// Sql参数
/// 数据表
///
public DataSet RunProcedure(string ProcedureName, SqlParameter[] SqlPrams,string Table)
{
DataSet SqlDS = new DataSet();
SqlDataAdapter SqlDA = CreateDataAdapter(ProcedureName,SqlPrams);
SqlDA.Fill(SqlDS,Table);
return(SqlDS);
}
///
/// 运行Sql Server储存过程
///
/// 储存过程名称
/// 数据表
/// 开始记录
/// 最大记录数
///
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);
}
///
/// 运行SQL Server储存过程
///
/// 储存过程名称
/// Sql参数
/// 数据表
/// 开始记录
/// 最大记录数
///
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);
}
///
/// 创建SqlDataAdapter对象
///
/// 储存过程
/// Sql参数
/// 类型
///
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;
}
///
/// 创建一个SqlCommand对象来调用储存过程
///
/// 储存过程名称
/// 储存过程参数
///
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;
}
///
/// 打开数据库连接
///
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();
}
///
/// 关闭数据库连接
///
public void Close()
{
if (SqlConn != null)
SqlConn.Close();
}
///
/// 释放资源
///
public void Dispose()
{
// 确定以关闭数据库连接
if (SqlConn != null)
{
SqlConn.Dispose();
SqlConn = null;
}
}
///
/// 创建输入参数
///
/// 参数名称
/// 参数类型
/// 参数大小
/// 参数值
///
public SqlParameter MakeInputParameter(string ParameterName, SqlDbType DbType, int Size, object Value)
{
return MakeParameter(ParameterName, DbType, Size, ParameterDirection.Input, Value);
}
///
/// 创建输出参数
///
/// 参数名称
/// 参数类型
/// 参数大小
///
public SqlParameter MakeOutParameter(string ParameterName, SqlDbType DbType, int Size)
{
return MakeParameter(ParameterName, DbType, Size, ParameterDirection.Output, null);
}
///
/// 创建储存过程参数
///
/// 参数名称
/// 参数类型
/// 参数大小
/// 参数方法
/// 参数值
///
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;
}
}
}