一个封装数据库的类-Connection MS SQL DB & Execute SQL

创建一个ExecuteSQL类,编译成一个DLL文件.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ExecuteSQL
{
    public class ExecuteSQL
    {
        private string strDataSource = "";
        private string strInitialCatalog = "";
        private string strUserID = "";
        private string strPwd = "";

        private string m_strConnString;
        private SqlConnection m_connConnection;

        public ExecuteSQL(string strMyDataSource, string strMyInitialCatalog, string strMyUserID, string strMyPwd)
        {
            strDataSource = strMyDataSource;
            strInitialCatalog = strMyInitialCatalog;
            strUserID = strMyUserID;
            strPwd = strMyPwd;
        }
        // public void Open(string strDataSource, string strInitialCatalog, string strUserID, string strPwd)

        private void Open() 
        {
            try
            {
                m_strConnString = "Data Source=" + strDataSource + ";Initial Catalog=" + strInitialCatalog + ";User ID=" + strUserID + ";Password=" + strPwd;
                m_connConnection = new SqlConnection(m_strConnString);
                m_connConnection.Open();    //---Open Database Connection
            }
            catch (SqlException e)
            {
                throw e;
            } 
        }

        public void Close()
        {
            try
            {
                //m_connConnection.Close();
                if (m_connConnection.State != ConnectionState.Closed)
                {
                    m_connConnection.Close();   //---Close Database Connection
                }
            }
            catch (SqlException e)
            {
                throw e;
            } 

        }

        public SqlDataReader GetDataReader(string strCommandString)
        {
            try
            {
                Open();
                SqlCommand cmdCommand = new SqlCommand(strCommandString, m_connConnection);
                cmdCommand.CommandTimeout = 300;
                SqlDataReader drDataReader = cmdCommand.ExecuteReader();
                //notice: drDataReader没有close
                Close();
                return drDataReader;
            }
            catch (SqlException e)
            {
                throw e;
            } 

        }
        public int Execute(string strCommandString)
        {
            try
            {
                Open();
                SqlCommand cmdCommand = new SqlCommand(strCommandString, m_connConnection);
                cmdCommand.CommandTimeout = 300;
                int nAffected = cmdCommand.ExecuteNonQuery();
                Close();
                return nAffected;
            }
            catch (SqlException e)
            {
                throw e;
            } 
        }


        public DataSet GetDataSet(string strCommandString, string strTableName)
        {
            try
            {
                Open();
                SqlCommand cmdCommand = new SqlCommand(strCommandString, m_connConnection);
                cmdCommand.CommandTimeout = 300;
                SqlDataAdapter adAdapter = new SqlDataAdapter();
                adAdapter.SelectCommand = new SqlCommand(strCommandString, m_connConnection);

                DataSet dsDataSet = new DataSet();

                adAdapter.Fill(dsDataSet, strTableName);
                Close();
                return dsDataSet;
            }
            catch (SqlException e)
            {
                throw e;
            } 

        }

        public DataTable GetDataTable(string strCommandString)
        {
            try
            {
                Open();
                SqlCommand cmdCommand = new SqlCommand(strCommandString, m_connConnection);
                cmdCommand.CommandTimeout = 300;
                SqlDataAdapter adAdapter = new SqlDataAdapter();
                adAdapter.SelectCommand = new SqlCommand(strCommandString, m_connConnection);

                DataSet dsDataSet = new DataSet();

                adAdapter.Fill(dsDataSet);
                Close();
                return dsDataSet.Tables[0];
                Close();
            }
            catch (SqlException e)
            {
                throw e;
            } 

        }

        public DataView GetDataView(string strCommandString, string strTableName)
        {
            try
            {
                Open();
                DataSet dsDataSet;
                dsDataSet = this.GetDataSet(strCommandString, strTableName);
                Close();
                if (dsDataSet != null)
                {
                    return new DataView(dsDataSet.Tables[strTableName]);
                }
                else
                {
                    return null;
                }
            }
            catch (SqlException e)
            {
                throw e;
            } 
        }
    }
}

 

窗体调用时代码如下, 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using Microsoft.Win32;
//引入ExecuteSQL命名空间
using ExecuteSQL;

namespace TV_ShowBoard
{
    public partial class frmShowOne : Form
    {
        private string strSQL = "";
        ExecuteSQL.ExecuteSQL CESQL = new ExecuteSQL.ExecuteSQL(Parameter.pstrDBIP, Parameter.pstrDBName, Parameter.pstrDBUID, Parameter.pstrDBPWD);

        public frmShowOne()
        {
            InitializeComponent();
        }

        private void frmShowOne_Load(object sender, EventArgs e)
        {
            this.WindowState = FormWindowState.Maximized;
            Refresh();
        }

        private void Refresh()
        {
            lblLine.Text = Parameter.pstrLine;
            strSQL = "Exec usp_TVShowBoard_Monitor '" + Parameter.pstrLine + "','" + Parameter.pstrSBType + "'";
            DataTable dt = CESQL.GetDataTable(strSQL);

            if (dt.Rows.Count != 0)
            {
                //
            }
        }
    }
}