dbclass.cs

贸易有限公司项目管理系统(C#.NET2003),供学习参考！

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.Web.Configuration;
using System.Web.SessionState;

/// <summary>
/// DBClass 的摘要说明
/// </summary>
public class DBClass
{
    public static SqlConnection myConnection;
    public static SqlCommand myCommand;
    public static  DataSet ds;
    public static DataTable dt;
    public static DataRow dr;
    public static SqlDataAdapter myAdapter;
    public static SqlDataAdapter myAdapter1;
    public static SqlDataReader myReader;
    protected static HttpResponse Response;
    protected static HttpSessionState Session;

	public DBClass()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
	}
    /*
     * 打开数据库的连接用力判断数据库是否处于关闭或打开状态
     * 采用webconfig的连接机制。 
     * */
    #region 打开数据库操作
    public static void Open()
    {
      string strConnection;

        if (myConnection == null)
        {
            //strConnection = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
            myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"].ToString());
            myConnection.Open();
        }
        else
        {
            if (myConnection.State == ConnectionState.Closed)
            {
                if (myConnection.ConnectionString == "" || myConnection.ConnectionString == null)
                {
                    strConnection = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                    myConnection.ConnectionString = strConnection;
                }
                myConnection.Open();
            }
        }
    }
    #endregion 关闭数据库操作

    /*
		 * 关闭数据库的连接状态,清除dataset中的数据
		 */
    #region 关闭数据库
    public static  void Close()
    {
        if (ds != null)
        {
            ds.Clear();
            ds.Dispose();
        }
        if (myConnection.State == ConnectionState.Open)
        {
            try
            {
                myAdapter.Dispose();
            }
            catch (Exception)
            {

            }
            myConnection.Close();
            myConnection.Dispose();
        }
    }
    /*
     *关闭数据连接，清除数据适配器中的数据，取消填充数据
     * 
     */
    public static void CloseConnection()
    {
        if (myConnection.State == ConnectionState.Open)
        {
            try
            {
                myAdapter.Dispose();
            }
            catch (Exception)
            {

            }
            myConnection.Close();
            myConnection.Dispose();
        }
    }
    #endregion
    # region 释放资源
    public static  void Dispose()
    {
        if (ds != null)
        {
            ds.Dispose();
        }
        if (myAdapter != null)
        {
            myAdapter.Dispose();
        }
        if (myAdapter1 != null)
        {
            myAdapter.Dispose();
        }
        if (myCommand != null)
        {
            myCommand.Dispose();
        }
        if (myReader != null)
        {
            if (myReader.IsClosed)
            {
                myReader.Close();
            }
        }
    }
    #endregion 

    //sqlconnetion的方法用于数据的连接
    public static SqlConnection getconn()
    {
        SqlConnection conn;
        string connections = System.Configuration.ConfigurationSettings.AppSettings["Connectionstring"];
        conn = new SqlConnection(connections);
        return conn;
    }
    //对数据库执行sql操作
    public static SqlCommand getCommand()
    {
        SqlCommand myCmd = new SqlCommand();
        return myCmd;
    }

    public static SqlCommand getCommand(string strArg)
    {
        SqlCommand myCmd = new SqlCommand(strArg);
        return myCmd;
    }

    public static SqlCommand getCommand(string strArg, SqlConnection connArg)
    {
        Open();
        SqlCommand myCmd = new SqlCommand(strArg, connArg);
        return myCmd;
    }
    //执行 Transact-SQL INSERT、DELELE、UPDATE 及 SET 语句等命令。
    public static  void ExeSql(string str_Sql)
    {
        if (myConnection.State == ConnectionState.Closed)
        {
            Open();
        }
        myCommand = new SqlCommand(str_Sql, myConnection);
        myCommand.ExecuteNonQuery();
        myCommand.Dispose();
        CloseConnection();
    }
    //读取数据
    public static void Fill(string str_Sql)
    {
        myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        if (myConnection.State == ConnectionState.Closed)
        {
            Open();
        }
        myAdapter = new SqlDataAdapter(str_Sql, myConnection);
        ds = new DataSet();
        myAdapter.Fill(ds);
    }

    public static void FillAdd(string tabname, string str_Sql)
    {
        myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        if (myConnection.State == ConnectionState.Closed)
        {
            Open();
        }
        myAdapter = new SqlDataAdapter(str_Sql, myConnection);
        if (ds == null)
            ds = new DataSet();
        myAdapter.Fill(ds, tabname);

    }

    //dataset的数据绑定
    public static int GetRowCount(string str_Sql)
    {
        int intCount;
        Fill(str_Sql);
        if (ds.Tables.Count < 1)
        {
            Close();
            intCount = 0;
        }
        else
        {
            intCount = ds.Tables[0].Rows.Count;
        }
        return intCount;

    }

    public static void GetRowRecord(string str_Sql)
    {
        Fill(str_Sql);
        if (ds.Tables.Count > 0)
        {
            if (ds.Tables[0].Rows.Count > 0)
            {
                dr = ds.Tables[0].Rows[0];
            }
        }
        myConnection.Close();
        myConnection.Dispose();
    }

    /*
 * 服务器控件数据的绑定
 * */
    public static void BindDataList(string sql, DataList mydatalist)
    {
        Fill(sql);
        mydatalist.DataSource = ds.Tables[0].DefaultView;
        mydatalist.DataBind();
    }
    public static void BindRepeater(string sql, Repeater myrepeater)
    {
        Fill(sql);
        myrepeater.DataSource = ds.Tables[0].DefaultView;
        myrepeater.DataBind();
    }
    //datagrid 的数据库多种绑定
    /// <summary>
    /// 获取DataGrid被选中的项索引若没有选中，返回－1
    /// </summary>
    /// <param name="DG">要操作的DataGrid</param>
    /// <returns>选中的项索引，没有选中返回－1</returns>
    public static int SelectedIndex(DataGrid DG)
    {
        if (DG.SelectedIndex == -1)
        {
            return -1;
        }
        else
            return DG.SelectedIndex;
    }
    /// <summary>
    /// 获取DataGrid被选中的项的DataKeys值，若没有选中，提示
    /// 若DataGrid没有设置DataKeyField，提示 
    /// </summary>
    /// <param name="DG">要操作的DataGrid</param>