using System; using System.Collections.Generic; using System.Linq; using System.Text;
using System.Data.SqlClient; using System.Data; using System.Windows.Forms; using WindowsFormsApplication1.DBTools;//提供数据库连接
namespace liuxw_MPS.DBTools { /// <summary> /// 数据库操作公共类。 /// 改进:结合SqlParamsTool类,可比较好的支持动态SQL语句操作 /// </summary> class NewDBOperate {
private SqlConnection conn=null;//数据库连接对象 private SqlTransaction trans=null;//事务 /// <summary> /// 构造方法中建立数据库连接 /// </summary> public NewDBOperate() { conn=DBConnection.getConnection(); } /// <summary> /// 打开数据库连接 /// </summary> public void OpenConnection() { if(conn.State!=ConnectionState.Open) { conn.Open(); } } /// <summary> /// 关闭数据库连接 /// </summary> public void CloseConnection() { if(conn.State!=ConnectionState.Closed) { conn.Close(); } } /// <summary> /// 执行需要返回DataSet的sql语句; /// </summary> /// <param name="sql"></param> /// <param name="sp"></param> /// <returns></returns> public DataSet GetDataSet(string sql, SqlParameter[] sp) { SqlDataAdapter sda = new SqlDataAdapter(sql, conn); SqlCommand cmd = sda.SelectCommand; if (sp != null)//sql语句有动态参数,设置参数 {
//>>>方式1.设置参数 //foreach(SqlParameter param in sp) //{ // sda.SelectCommand.Parameters.Add(param);
//} //>>>方式2.设置参数 sda.SelectCommand.Parameters.AddRange(sp);//参数sp不能为null值 } sda.SelectCommand.Transaction = trans;//事务. DataSet ds = new DataSet(); sda.Fill(ds); return ds; } /// <summary> /// 执行需要返回DataTable表的sql语句; /// </summary> /// <param name="sql"></param> /// <param name="sp"></param> /// <returns></returns> public DataTable GetTable(string sql, SqlParameter[] sp) { return this.GetDataSet(sql,sp).Tables[0]; } /// <summary> /// 执行需要返回SqlDataReader对象的sql语句; /// </summary> /// <param name="sql"></param> /// <param name="sp"></param> /// <returns></returns> public SqlDataReader ExecuteReader(string sql, SqlParameter[] sp) { SqlCommand cmd = new SqlCommand(sql, conn); if (sp != null) { cmd.Parameters.AddRange(sp);//设置sql参数;参数sp不能为null值 } cmd.Transaction = trans;//使用事务;进行数据库事物处理必须对cmd的Transaction赋值,默认为null值 return cmd.ExecuteReader(); } /// <summary> /// 返回查询所返回的结果集中第一行的第一列。忽略其他列或行。 ///可用于用户名登录验证。 /// </summary> /// <returns>返回单个值</returns> public object ExecuteScalar(string sql,SqlParameter[] sp) { object returnValue = 0; SqlCommand cmd = new SqlCommand(sql, conn); if (sp != null) { cmd.Parameters.AddRange(sp); } cmd.Transaction = trans;//使用事务 return cmd.ExecuteScalar();
} /// <summary> /// 绑定DataGridView; /// </summary> /// <param name="dgv"></param> /// <param name="sql"></param> /// <param name="sp"></param> public void BindDataGridView(DataGridView dgv, string sql, SqlParameter[] sp) { dgv.DataSource = this.GetTable(sql,sp).DefaultView;//设置数据源 } /// <summary> /// 绑定下拉列表,参数“控件”,“表名”,“列” /// </summary> /// <param name="cb"></param> /// <param name="sql"></param> /// <param name="sp"></param> public void BindComboBox(ComboBox cb,string tableName,int column ) { SqlDataReader reader = this.ExecuteReader("select * from "+tableName,null); while (reader.Read()) { cb.Items.Add(reader[column].ToString()); } reader.Close(); } /// <summary> /// 操作数据(增删改); /// </summary> /// <param name="sql"></param> /// <param name="sp"></param> /// <returns>返回受影响的行数</returns> public int OperateData(string sql,SqlParameter[] sp) { SqlCommand cmd = new SqlCommand(sql, conn); cmd.Transaction = trans;//事务 if (sp != null)//sql参数不为空 { cmd.Parameters.AddRange(sp);//设置参数;参数sp不能为null值 } int i = cmd.ExecuteNonQuery();//获取操作受影响的行数 return i; }
//>>>事务: /// <summary> /// 开启数据库事务 /// </summary> public void BeginTransaction() { trans = conn.BeginTransaction(); } /// <summary> /// 提交事务 /// </summary> public void Commit() { trans.Commit(); } /// <summary> /// 回滚事务 /// </summary> public void RollBack() { trans.Rollback(); } /// <summary> /// 销毁事务 /// </summary> public void disposeTransaction() { if (trans != null) { trans.Dispose(); trans = null; } }
} }