1 class DBHandler { 2  3     //SqlConnection数据库连接对象 4     private SqlConnection localConnection = null; 5  6     //构造函数中初始化连接对象，传入数据库服务名，数据库实例名，用户名和密码 7     public DBHandler(string server, string dataBase, string id, string pwd) {  8         String connectionString = "Data Source=" + server + ";Initial Catalog=" + dataBase 9                                 + ";User ID=" + id + ";Password=" + pwd;10         this.localConnection = new SqlConnection(connectionString);11     }12 13     //创建对象后调用该方法判断是否能够成功连接14     public int checkValidate(ref string errMsg) {15         try {16             this.localConnection.Open();17         } catch (Exception ex) {18             errMsg = "[Connecting failed]" + ex.Message;19             return -1;20         }21         return 0;22     }23 24     //操作数据库前调用25     private void prepareConnect() {26         switch (this.localConnection.State) {27             case ConnectionState.Broken:28                 this.localConnection.Close(); //先正常关闭，释放资源29                 this.localConnection.Open();30                 break;31             case ConnectionState.Closed:32                 this.localConnection.Open();33                 break;34             case ConnectionState.Connecting:35                 break;36             case ConnectionState.Executing:37                 break;38             case ConnectionState.Fetching:39                 break;40             case ConnectionState.Open:41                 break;42             default:43                 break;44         }45     }46 47     //执行Update语句，返回更新的行数48     public int doUpdate(string sql, ref string errMsg) {49         prepareConnect();50         SqlCommand localCommand = new SqlCommand();51         localCommand.CommandText = sql;52         localCommand.Connection = this.localConnection;53         try {54             return localCommand.ExecuteNonQuery();55         } catch (Exception ex) {56             errMsg = "[update error]" + ex.Message;57             return -1;58         }59     }60 61     //执行Select语句，返回DataTable结果集62     public void doSelect(string sql, ref DataTable dataTable, ref string errMsg) {63         prepareConnect();            64         try {65             SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, this.localConnection);66             dataTable.Clear();67             dataAdapter.Fill(dataTable);68         } catch (Exception ex) {69             errMsg = "[select error]" + ex.Message;70         }71     }72     73 }