clsdb.cs

Vs2005C#图书管理系统初学的看看有好处的

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Data;
using System.Drawing;
namespace DB
{
    class ClsSQL
    {
        string strService = "";      //定义服务器名称
        string strDataBase = "";//   定义数据库名称
        string strUser = "";//  定义登陆用户名
        string strPassword = "";  //定义登陆用户密码
        string strConnect = "";
        int strType = 2;  //定义连接类型
        public  SqlConnection SQLConnect = new SqlConnection();// 定义连接对象
        SqlCommand SQLCmd = new SqlCommand();     //'定义数据库操作命令
        SqlDataAdapter SQLDA = new SqlDataAdapter();  //定义数据库操作适配器
        DataSet SQLDS = new DataSet(); //'定义数据集
        public ClsSQL()
        { }
        public ClsSQL(string strS, string strD, string strU, string strP, int StrT)
        {
            strService = strS;
            strDataBase = strD;
            strUser = strU;
            strPassword = strP;
            strType = StrT;

        }

        public string ServiveName
        {
            get { return strService; }
            set { strService = value; }

        }
        public string DataBaseName
        {
            get { return strDataBase; }
            set { strDataBase = value; }

        }
        public string UserName
        {
            get { return strUser; }
            set { strUser = value; }

        }
        public string Password
        {
            get { return strPassword; }
            set { strPassword = value; }

        }
        public int Type
        {
            get { return strType; }
            set { strType = value; }

        }
        public Boolean TestConnect() //判断数据库连接是否畅通
        {
            try
            {
                switch (strType)
                {
                    case 0://'远程连接
                        strConnect = "Data Source=" + strService + ",1433;Network Library=DBMSSOCN;Initial Catalog=" + strDataBase + ";User Id=" + strUser + ";password=" + strPassword + ";";
                        break;
                    case 1://'本地连接
                        strConnect = "workstation id=" + strService + ";packet size=4096;integrated security=SSPI;data source=" + strService + ";persist security info=False;initial catalog=" + strDataBase;
                        break;

                    case 2://信用连接
                        strConnect = "Server=" + strService + ";Database=" + strDataBase + ";User ID=" + strUser + ";Password=" + strPassword + ";Trusted_Connection=False";
                        break;
                    case 3:// '本地连接
                        strConnect = "Data Source=" + strService + ";User ID=" + strUser + ";Pwd=" + strPassword + ";Initial Catalog=" + strDataBase;
                        break;


                    default:
                        strConnect = "workstation id=" + strService + ";packet size=4096;integrated security=SSPI;data source=" + strService + ";persist security info=False;initial catalog=" + strDataBase;
                        break;
                }
                SQLConnect.ConnectionString = strConnect;
                SQLCmd.Connection = SQLConnect;
                SQLConnect.Open();

                if (SQLConnect.State == ConnectionState.Open)
                    return true;
                else
                    return false;

            }
            catch
            {
                MessageBox.Show("连接失败！");
                return false;
            }
            finally
            {
                SQLConnect.Close();
            }

        }
        public int SQLCommand(string strcmd)//数据库的插入、删除、修改操作
        {
            try
            {
                if (TestConnect() == true)
                {
                    SQLCmd.CommandText = strcmd;
                    SQLConnect.Open();
                    return SQLCmd.ExecuteNonQuery();
                }
                else
                {
                    MessageBox.Show("45632");
                    return 0;
                }
            }
            catch (Exception ex)
            {
                //表示出现异常的原因，用ex变量来捕获出现异常的原因
                MessageBox.Show(ex.ToString()+"当前数据已经存在！");
                return -1;
            }
            finally
            { SQLConnect.Close(); }
        }
        public void DBToDG(String strcmd, DataGridView dg, string strTable) //'将数据库中的数据带到数据表格
        {
            
            try
            {
                if (TestConnect() == true)
                {
                    SQLCmd.CommandText = strcmd;
                    SQLDA.SelectCommand = SQLCmd;
                    SQLConnect.Open();
                    SQLDS.Clear();
                    SQLDA.SelectCommand.ExecuteNonQuery();
                    SQLDA.Fill(SQLDS, strTable);
                    dg.DataSource = SQLDS.Tables[strTable];
                    

                }
                else
                    MessageBox.Show("数据库连接失败！");
            }
            catch { }
            finally
            { SQLConnect.Close(); }
        }
        public DataSet DBToDS(string strcmd, string strTableName) //'将数据库中的数据带到数据集
        {
            DataSet tmpDS = new DataSet();
            try
            {
                if (TestConnect() == true)
                {
                    SQLCmd.CommandText = strcmd;
                    SQLDA.SelectCommand = SQLCmd;
                    SQLConnect.Open();
                    tmpDS.Clear();
                    SQLDA.SelectCommand.ExecuteNonQuery();
                    SQLDA.Fill(tmpDS, strTableName);
                    return tmpDS;
                }
                else
                    MessageBox.Show("数据库连接失败！");
                return null;
            }
            //(Exception ex)
            catch
            {
                return null;
            }
            finally { SQLConnect.Close(); }
        }



    }
}