mes-client/UAS_DXFORM/DBHandler.cs

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

namespace UAS_DXFORM
{
    class DBHandler
    {

        //SqlConnection数据库连接对象
        public SqlConnection localConnection = null;

        //构造函数中初始化连接对象，传入数据库服务名，数据库实例名，用户名和密码
        public DBHandler(string server, string dataBase, string id, string pwd)
        {
            String connectionString = "Data Source=" + server + ";Initial Catalog=" + dataBase
                                    + ";User ID=" + id + ";Password=" + pwd;
            this.localConnection = new SqlConnection(connectionString);
        }

        //创建对象后调用该方法判断是否能够成功连接
        public int checkValidate(ref string errMsg)
        {
            try
            {
                this.localConnection.Open();
            }
            catch (Exception ex)
            {
                errMsg = "[Connecting failed]" + ex.Message;
                return -1;
            }
            return 0;
        }

        //操作数据库前调用
        private void prepareConnect()
        {
            switch (this.localConnection.State)
            {
                case ConnectionState.Broken:
                    this.localConnection.Close(); //先正常关闭，释放资源
                    this.localConnection.Open();
                    break;
                case ConnectionState.Closed:
                    this.localConnection.Open();
                    break;
                case ConnectionState.Connecting:
                    break;
                case ConnectionState.Executing:
                    break;
                case ConnectionState.Fetching:
                    break;
                case ConnectionState.Open:
                    break;
                default:
                    break;
            }
        }

        //执行Update语句，返回更新的行数
        public int doUpdate(string sql, ref string errMsg)
        {
            prepareConnect();
            SqlCommand localCommand = new SqlCommand();
            localCommand.CommandText = sql;
            localCommand.Connection = this.localConnection;
            try
            {
                return localCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                errMsg = "[update error]" + ex.Message;
                return -1;
            }
        }

        //执行Select语句，返回DataTable结果集
        public void doSelect(string sql, ref DataTable dataTable, ref string errMsg)
        {
            prepareConnect();
            try
            {
                SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, this.localConnection);
                dataTable.Clear();
                dataAdapter.Fill(dataTable);
            }
            catch (Exception ex)
            {
                errMsg = "[select error]" + ex.Message;
            }
        }

    }
}