database.cs

网开商城系统网开商城系统网开商城系统网开商城系统

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Security.Cryptography;
using System.Collections.Generic;
/// <summary>
/// Database 的摘要说明
/// </summary>
//该源码首发自www.51aspx.com(５１aｓｐｘ．ｃｏｍ)

public class Database:IDisposable
{
    private readonly string RETURNVALUE = "RETURNVALUE";

    #region 保护变量 数据库连接,数据库连接串
    /// <summary>
    /// 保护变量，数据库连接。
    /// </summary>
    protected SqlConnection cn;

    /// <summary>
    /// 保护变量，数据库连接串。
    /// </summary>
    protected String ConnectionString;
    #endregion
    #region  构造函数数据库连接串
    /// <summary>
    /// 构造函数。
    /// </summary>
    /// <param name="DatabaseConnectionString">数据库连接串</param>
    public Database()
    {
        ConnectionString = "server=(local);database=shop;uid=sa;password=sa";
    }
    #endregion
    #region 析构函数，释放非托管资源
    /// <summary>
    /// 析构函数，释放非托管资源
    /// </summary>
    ~Database()
    {
        try
        {
            if (cn != null)
                cn.Close();
        }
        catch { }
        try
        {
            Dispose();
        }
        catch { }
    }
    #endregion
    #region 保护方法，打开数据库连接
    /// <summary>
    /// 保护方法，打开数据库连接。
    /// </summary>
    protected void Open()
    {
        if (cn == null)
        {
            cn = new SqlConnection(ConnectionString);
        }
        if (cn.State.Equals(ConnectionState.Closed))
        {
            cn.Open();
        }
    }
    #endregion
    #region 公有方法，关闭数据库连接
    /// <summary>
    /// 公有方法，关闭数据库连接。
    /// </summary>
    public void Close()
    {
        if (cn != null)
            cn.Close();
    }
    #endregion
    #region 公有方法，释放资源
    /// <summary>
    /// 公有方法，释放资源。
    /// </summary>
    public void Dispose()
    {
        // 确保连接被关闭
        if (cn != null)
        {
            cn.Dispose();
            cn = null;
        }
    }
    #endregion
    #region 共有方法创建SqlDataAdapter
    public SqlDataAdapter CreateProcDataAdapter(string proname, SqlParameter[] param, int IsType)
    {
        Open();
        SqlDataAdapter da = new SqlDataAdapter(proname, cn);
        if (IsType == 1)
        {
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
        }
        else
        {
            da.SelectCommand.CommandType = CommandType.Text;
        }
        if (param != null)
        {
            foreach (SqlParameter parameter in param)
            {
                da.SelectCommand.Parameters.Add(parameter);
            }
        }

        ///添加返回参数ReturnValue
        da.SelectCommand.Parameters.Add(
            new SqlParameter(RETURNVALUE, SqlDbType.Int, 4, ParameterDirection.ReturnValue,
            false, 0, 0, string.Empty, DataRowVersion.Default, null));
        return da;
    }
    #endregion 
    #region 共有方法创建Sqlcommand对象
    /// <summary>
    /// 私有方法创建Sqlcommand对象
    /// </summary>
    /// <param name="proname">Sql语句或存储过程的名字</param>
    /// <param name="param">Sql参数</param>
    /// <param name="IsType">执行类型1为存储过程0为sql语句</param>
    /// <returns>Sqlcommand对象</returns>
    public SqlCommand CreateProCommand(string proname, SqlParameter[] param, int IsType)
    {
        Open();
        SqlCommand cmd = new SqlCommand(proname, cn);
        if (IsType == 1)
        {
            cmd.CommandType = CommandType.StoredProcedure;
        }
        else
        {
            cmd.CommandType = CommandType.Text;
        }

        if (param != null)
        {
            foreach (SqlParameter s in param)
            {
                cmd.Parameters.Add(s);
            }
        }
        ///添加返回参数ReturnValue
        cmd.Parameters.Add(
            new SqlParameter(RETURNVALUE, SqlDbType.Int, 4, ParameterDirection.ReturnValue,
            false, 0, 0, string.Empty, DataRowVersion.Default, null));

        ///返回创建的SqlCommand对象
        return cmd;
       

    }
    #endregion
    #region 公有方法，执行Sql存储过程或语句
    /// <summary>
    /// 公有方法，执行Sql存储过程。
    /// </summary>
    /// <param name="proname">Sql语句或存储过程的名字</param>
    /// <param name="param">Sql参数</param>
    /// <param name="IsType">执行类型1为存储过程0为sql语句</param>
    /// <returns>增加返回新增的id删除和修改返回影响的行数大于-1</returns>
    public int execute(string proname, SqlParameter[] param, int IsType)
    {
        Open();
        SqlCommand cmd = CreateProCommand(proname,param,IsType);
        try
        {
            cmd.ExecuteNonQuery();
            Close();
            
        }
        catch (SqlException ex)
        {
            throw ex;

        }
      return (int)cmd.Parameters[RETURNVALUE].Value;

    }
    #endregion
    #region 共有方法获取数据返回DataSet对象
    /// <summary>
    /// 公有方法，执行Sql存储过程返回DataSet对象
    /// </summary>
    /// <param name="proname">Sql语句或存储过程的名字</param>
    /// <param name="param">Sql参数</param>
    /// <param name="Srctable">映射的表名</param>
    /// <param name="IsType">执行类型1为存储过程0为sql语句</param>
 

    public void GetSet(string proname, SqlParameter[] param,ref DataSet ds,string srctable, int IsType)
    {
        Open();
        SqlDataAdapter da = CreateProcDataAdapter(proname, param, IsType);
        if (IsType == 1)
        {
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
        }
        else
        {
            da.SelectCommand.CommandType = CommandType.Text;
        }

        try 
        {
         da.Fill(ds,srctable);
         Close();
        }
        catch (SqlException ex)
        {
            throw ex;

        }

    }
    #endregion
    #region 公有方法，执行Sql存储过程或语句返回SqlDataReader对象
    /// <summary>
    /// 公有方法，执行Sql存储过程返回SqlDataReader对象
    /// </summary>
    /// <param name="proname">Sql语句或存储过程的名字</param>
    /// <param name="param">Sql参数</param>
    /// <param name="IsType">执行类型1为存储过程0为sql语句</param>
    /// <returns>SqlDataReader对象</returns>



    //返回SqlDataReader
    public  SqlDataReader GetReader(string proname, SqlParameter[] param, int IsType)
    {
        Open();
        SqlCommand cmd = CreateProCommand(proname,param,IsType);
        SqlDataReader dr = null;
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        return dr;

    }
    #endregion
    #region 公有方法，执行Sql存储过程或语句返回个整数值.用到对象ExecuteScalar
    /// <summary>
    /// 公有方法，执行Sql存储过程返回第一行第一列的值
    /// </summary>
    /// <param name="proname">Sql存储过程</param>
    /// <param name="param">Sql参数</param>
    /// <param name="IsType">执行类型1为存储过程0为sql语句</param>
    /// <returns>返回第一行第一列的值</returns>

    public int GetScalar(string proname, SqlParameter[] param, int IsType)
    {
        Open();
        SqlCommand cmd = new SqlCommand(proname, cn);
        if (IsType == 1)
        {
            cmd.CommandType = CommandType.StoredProcedure;
        }
        else
        {
            cmd.CommandType = CommandType.Text;
        }
        if (param != null)
        {
            foreach (SqlParameter s in param)
            {
                cmd.Parameters.Add(s);
            }
        }
        int result = Convert.ToInt32(cmd.ExecuteScalar());
        Close();
        return result;

    }
    #endregion
    #region 公有方法，执行Sql存储过程或语句返回DataTable对象
    /// <summary>
    /// 公有方法，执行Sql存储过程返回SqlDataReader对象
    /// </summary>
    /// <param name="proname">Sql存储过程</param>
    /// <param name="param">Sql参数</param>
    /// <param name="IsType">执行类型1为存储过程0为sql语句</param>
    /// <returns>DataTable对象</returns>
    public DataTable GetTable(string proname, SqlParameter[] param, string table, int IsType)
    {
        Open();
        SqlCommand cmd = new SqlCommand(proname, cn);
        if (IsType == 1)
        {
            cmd.CommandType = CommandType.StoredProcedure;
        }
        else
        {
            cmd.CommandType = CommandType.Text;
        }
        if (param != null)
        {
            foreach (SqlParameter s in param)
            {
                cmd.Parameters.Add(s);