SqlDataBase.cs

using System.Data;
using System.Data.SqlClient;
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;

/// <summary>
/// SqlDataBase 的摘要说明
/// </summary>
public class SqlDataBase
{
    public SqlDataBase()
    {
        //
        // TODO: 在此处添加构造函数逻辑
        //
    }
    private SqlConnection Cn;
    private SqlCommand Cm;
    private DataSet ds;
    private DataView dv;

    #region
    private void Open()
    {
        Cn = new SqlConnection(ConfigurationSettings.AppSettings["Dsn"]);
        Cn.Open();
    }
    #endregion

    #region 关闭连接
    private void Close()
    {
        if (Cn != null)
        {
            Cn.Close();
            Cn.Dispose();
        }
    }
    #endregion

    #region 执行简单的SQL语句
    public void NoreturnSql(string Sql)
    {
        Open();
        Cm = new SqlCommand(Sql, Cn);
        Cm.ExecuteNonQuery();
        Close();
    }
    #endregion

    #region 返回首列单行的SQL语句
    public string ReturnSQL(string Sql)
    {
        string Returnstring = "";
        try
        {
            Open();
            Cm = new SqlCommand(Sql, Cn);
            Returnstring = Cm.ExecuteScalar().ToString();
        }
        catch
        {
        }
        Close();
        return Returnstring;
    }
    #endregion

    #region 返回SQL语句的DateSet数据集
    public DataSet GetDS(string SQL)
    {
        Open();
        SqlDataAdapter myAdapter = new SqlDataAdapter(SQL, Cn);
        ds = new DataSet();
        myAdapter.Fill(ds);
        Close();
        return ds;
    }
    #endregion

    #region 返回一个DataView视图
    public DataView DV(string SQL)
    {
        ds = GetDS(SQL);
        dv = ds.Tables[0].DefaultView;
        return dv;
    }
    #endregion

    #region 返回一个DataTable对象
    public DataTable DT(string SQL)
    {
        return GetDS(SQL).Tables[0];
    }
    #endregion

    #region 返回一个含有两个DataTable的DataSet数据集
    public DataSet TwoDS(string SQL1, string SQL2)
    {
        DataSet myDS = new DataSet();
        Open();
        SqlDataAdapter myAD1 = new SqlDataAdapter(SQL1, Cn);
        myAD1.Fill(myDS, "Big");
        SqlDataAdapter myAD2 = new SqlDataAdapter(SQL2, Cn);
        myAD2.Fill(myDS, "Small");
        Close();
        return myDS;
    }
    #endregion

    #region
    public SqlDataReader GRead(string Sql)
    {
        Open();
        SqlCommand mycom = new SqlCommand(Sql, Cn);
        SqlDataReader Dr = mycom.ExecuteReader();
        return Dr;
    }
    #endregion

    #region 创建一个Command来执行存储过程
    private SqlCommand ReturnCM(string RunNM, SqlParameter[] PartNM)
    {
        Open();
        SqlCommand ThisCm = new SqlCommand(RunNM, Cn);
        ThisCm.CommandType = CommandType.StoredProcedure;

        //传入参数
        if (PartNM != null)
        {
            foreach (SqlParameter parmt in PartNM)
                ThisCm.Parameters.Add(parmt);
        }
        //加入返回参数
        ThisCm.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4,
            ParameterDirection.ReturnValue, false, 0, 0,
            string.Empty, DataRowVersion.Default, null));
        return ThisCm;
    }
    #endregion

    #region 返回带参数的存储过程
    public int RunInsert(string RunName, SqlParameter[] PartName)
    {
        SqlCommand MyCM = ReturnCM(RunName, PartName);
        MyCM.ExecuteNonQuery();
        this.Close();
        return (int)MyCM.Parameters["ReturnValue"].Value;
    }
    #endregion

    #region //传入输入的参数
    public SqlParameter IntParam(string ParamName, SqlDbType DbType, int Size, object Value)
    {
        return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
    }
    #endregion

    #region //传入返回值参数
    public SqlParameter OutParam(string ParamName, SqlDbType DbType, int Size)
    {
        return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
    }
    #endregion

    #region //生成存储过程参数
    private SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
    {
        SqlParameter param;

        if (Size > 0)
        {
            param = new SqlParameter(ParamName, DbType, Size);
        }
        else
        {
            param = new SqlParameter(ParamName, DbType);
        }
        param.Direction = Direction;
        if (!(Direction == ParameterDirection.Output && Value == null))
            param.Value = Value;
        return param;
    }
    #endregion
}