本人新手,没什么开发经验,下面代码是网上资料整理所得,经测试验证没问题,仅供参考
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Data; using System.Configuration; namespace DbTest { public class DbHelper { private readonly string SQLCONNECTSTR = "server=172.16.125.103;uid=sa;pwd=sa;database=CDDB;Timeout=10"; private SqlConnection dbConnection; //创建打开dbConnection对象 public void OpenConnection() { if (dbConnection == null) { dbConnection = new SqlConnection(SQLCONNECTSTR); } if (dbConnection.State == ConnectionState.Closed) { try { dbConnection.Open(); } catch (Exception ex) { dbConnection.Close(); throw ex; } } } //释放dbConnection对象 public void CloseConnection() { if (dbConnection != null) { if (dbConnection.State == ConnectionState.Open) { dbConnection.Close(); dbConnection.Dispose(); dbConnection = null; } } } // //创建cmd,注意dbconnection在该函数中创建,但没有在这函数中释放。 //在正确的面向对象设计方法中,对象应该是谁创建,谁就应该负责释放。按这个观点,这个过程有些不安全!!!! private SqlCommand CreateCommand(string ProName) { OpenConnection(); SqlCommand cmd = new SqlCommand(ProName, dbConnection); return cmd; } /// <summary> /// 返回受影响行数。 /// 创建cmd,并执行相应的操作。 然后释放cmd! /// 该函数是执行cmd没有返回值,且没有参数的方法。 /// </summary> /// <param name="ProName"></param> public int getExecuteNonQuery(string ProName) { SqlCommand cmd = CreateCommand(ProName); int k = 0; try { k = cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { CloseConnection(); cmd.Dispose(); } return k; } /// <summary> /// 返回查询结果第一行第一列 /// 创建cmd,并执行相应的操作。 然后释放cmd! /// 该函数是执行cmd带有返回值,但没有参数的方法。 /// </summary> /// <param name="ProName"></param> public object getExecuteScalar(string ProName) { object result; SqlCommand cmd = CreateCommand(ProName); try { result = cmd.ExecuteScalar(); } catch (Exception e) { throw e; } finally { cmd.Parameters.Clear(); CloseConnection(); } return result; } /// <summary> /// 返回DataTable对象 /// </summary> /// <param name="cmd"></param> /// <returns></returns> public DataTable getDataTable(string ProName) { SqlCommand cmd = CreateCommand(ProName); DataTable dt = new DataTable(); try { SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); adapter.Fill(ds); dt = ds.Tables[0]; } catch (Exception ex) { throw ex; } finally { CloseConnection(); cmd.Dispose(); } return dt; } /// <summary> /// 返回DataTable对象 /// 用法示例: /// DbHelper db = new DbHelper(); /// dataGridView1.DataSource=db.getDataTable( "select * from users"); /// </summary> /// <param name="ProName"></param> /// <returns></returns> public DataSet getDataSet(string ProName) { SqlCommand cmd = CreateCommand(ProName); DataSet ds = new DataSet(); try { SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(ds); } catch (Exception ex) { throw ex; } finally { CloseConnection(); cmd.Dispose(); } return ds; } } }