?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;

/// <summary>
/// Class1 的摘要說明
/// </summary>
/// 
namespace SMS
{
    public class OPDataBase
    {
        public OPDataBase()
        {
            //
            // TODO: 在此處添加構造函數(shù)邏輯
            //
        }
       
       
# region 一:返回對象方法集;
        //返回一個SqlDataReader對象；
        public SqlDataReader getDataReader(string sqlstr)
        {
            //SqlConnection conn = new SqlConnection(getConnection());
            //conn.Open();
            SqlConnection conn = sqlConnection();
            conn.Open();

            SqlCommand cmd = new SqlCommand(sqlstr, conn);
            SqlDataReader dr;
            dr = cmd.ExecuteReader();

            return dr;

        }
        //返回一個連接SqlConnection對象，來創(chuàng)建連接。
        public SqlConnection sqlConnection()
        {

            SqlConnection conn = new SqlConnection(getConnection());

            return conn;



        }
        //返回DataSet對象的方法,需要SQL語句
        public DataSet getDataset(string sql)
        {

            SqlConnection conn = sqlConnection();
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet(); ;
            da.Fill(ds);
            return ds;



        }
        //返回連接字符串；
        public string getConnection()
        {
            string conn = ConfigurationSettings.AppSettings["dsn"];
            return conn;


        }
#endregion
#region   二:綁定 GridView 控件的方法集;
        
        //數(shù)據(jù)梆定到GridView上的方法，需要輸入：查詢語句str，GridView對象，主鍵字鍛，三個參數(shù)。
        public void BinData(string str, GridView gridView, string datakeynames)
        {
            SqlConnection conn = sqlConnection();
            conn.Open();

            SqlDataAdapter da = new SqlDataAdapter(str, conn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            gridView.DataSource = ds;
            gridView.DataKeyNames = new string[] { datakeynames };
            gridView.DataBind();



        }
        //數(shù)據(jù)梆定到GridView上的方法，需要輸入：查詢語句str，GridView對象，兩個參數(shù)。
        public void BinData(string str, GridView gridView)
        {
            SqlConnection conn = sqlConnection();
            conn.Open();

            SqlDataAdapter da = new SqlDataAdapter(str, conn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            gridView.DataSource = ds;
            gridView.DataBind();
        }
        public void BinData(string str, GridView gridView,Label lable)
        {
            SqlConnection conn = sqlConnection();
            conn.Open();

            SqlDataAdapter da = new SqlDataAdapter(str, conn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            try
            {
                gridView.DataSource = ds;
                gridView.DataBind();
            }
            catch (SqlException ex)
            {
                lable.Text = ex.Message.ToString();
            }
            finally
            {
                conn.Close();
                da.Dispose();
            }
        }   
       
       


        //改變時間格式并綁定到 GridView 控件,Cells為數(shù)組, 用來填寫要改變時間格式的GridView 控件的列號,
        //注意的是, GridView 控件列號是從0開始的!
        public void BinData(string str, GridView gridView, string field, int[] Cells)
        {
            SqlConnection conn = sqlConnection();
            conn.Open();

            SqlDataAdapter da = new SqlDataAdapter(str, conn);
            DataSet ds = new DataSet();
            da.Fill(ds, "Table1");
            gridView.DataSource = ds;
            gridView.DataBind();
       for (int i = 0; i <= gridView.Rows.Count - 1; i++)
            {
                DataRowView drv = ds.Tables["Table1"].DefaultView[i];
      
                DateTime dt = Convert.ToDateTime(drv[field]);
               foreach (int a in Cells)
                {
                  gridView.Rows[i].Cells[a].Text = dt.ToLongDateString();
                  
                }

                
            }
                   
               
                




        }

       
        #endregion
#region  三:執(zhí)行SqlCommand命令方法集;
        //刪除，需要刪除的語句。
        public void DeletData(string sql)
        {


            SqlConnection conn = new SqlConnection(getConnection());
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                cmd.ExecuteNonQuery();

            }
            catch { }

        }
       
        //SqlCommand命令，需要SQL語句和 LableID控件顯示異常!
        public void BindCommand(string sql, Label lable1)
        {
            SqlConnection conn = new SqlConnection(getConnection());
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                cmd.ExecuteNonQuery();
                lable1.Text = "執(zhí)行成功!";
            }
            catch (SqlException ex)
            {
                lable1.Text = ex.Message.ToString();
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }

        }
        //SqlCommand命令，需要SQL語句
        public void BindCommand(string sql)
        {
            SqlConnection conn = new SqlConnection(getConnection());
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch
            {

            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }


        }
# endregion
      
      
    }

}