操作Sql数据库帮助类
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Data; namespace DateBase { public class Helper { /// <summary> /// 定义SqlConnection /// </summary> public static SqlConnection conn = null; /// <summary> /// 数据库连接 public static string str = ConfigurationManager.AppSettings["MySqlConncetion"]; /// </summary> public static string str = "Data Source=.;Initial Catalog=MyOffice;User ID=www_jy;password=*****************"; /// <summary> /// 事物回滚 /// </summary> public static SqlTransaction trans = null; /// <summary> /// 打开数据库连接 /// </summary> public static SqlConnection Connection { get { try { if (conn == null) { conn = new SqlConnection(str); conn.Open(); } else if (conn.State == ConnectionState.Closed) { conn.Open(); } else if (conn.State == ConnectionState.Broken) { conn.Close(); conn.Open(); } } catch (Exception e) { //抛出错误 } return conn; } } /// <summary> /// 打开事物 /// </summary> public static void OpenTrans() { try { trans = Connection.BeginTransaction(); } catch (Exception e) { //抛出异常 } } /// <summary> /// 事物提交 /// </summary> public static void CommitTrans() { trans.Commit(); } /// <summary> /// 事物回滚 /// </summary> public static void RollBackTrans() { trans.Rollback(); } /// <summary> /// 执行增、删、改 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static int GetExecute(string sql) { int num = 0; try { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Transaction = trans; num = cmd.ExecuteNonQuery(); } catch (Exception e) { //抛出异常 RollBackTrans(); } finally { //关闭数据库连接 conn.Close(); } return num; } /// <summary> /// 有参数执行增、删、改操作 /// </summary> /// <param name="sql"></param> /// <param name="pare"></param> /// <returns></returns> public static int GetExecute(string sql, SqlParameter[] pare) { int num = 0; try { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(pare); cmd.Transaction = trans; num = cmd.ExecuteNonQuery(); } catch (Exception e) { //抛出异常 RollBackTrans(); } finally { //关闭数据库连接 conn.Close(); } return num; } /// <summary> /// 查询条数 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static int GetExecuteScalar(string sql) { int num = 0; try { SqlCommand cmd = new SqlCommand(sql, Connection); num = Convert.ToInt32(cmd.ExecuteScalar()); } catch (Exception e) { //抛出异常 } finally { //关闭数据库连接 conn.Close(); } return num; } /// <summary> /// 条件查询 /// </summary> /// <param name="sql"></param> /// <param name="pare"></param> /// <returns></returns> public static int GetExecuteScalar(string sql, SqlParameter[] pare) { int num = 0; try { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(pare); num = Convert.ToInt32(cmd.ExecuteScalar()); } catch (Exception e) { //抛出异常 } finally { //关闭数据库连接 conn.Close(); } return num; } /// <summary> /// 查找数据库 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static DataSet GetDataSet(string sql) { DataSet ds = new DataSet(); try { SqlDataAdapter adapter = new SqlDataAdapter(sql, Connection); adapter.Fill(ds); } catch (Exception e) { //抛出异常 } finally { //关闭数据库连接 conn.Close(); } return ds; } /// <summary> /// 有参查找数据库 /// </summary> /// <param name="sql"></param> /// <param name="pare"></param> /// <returns></returns> public static DataSet GetDataSet(string sql, SqlParameter[] pare) { DataSet ds = new DataSet(); try { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(pare); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(ds); } catch (Exception e) { //抛出异常 } finally { //关闭数据库连接 conn.Close(); } return ds; } } }