using System;
using System.Data.SqlClient; using System.Configuration; using System.Data; /********************************* * 类名:DBHelper * 功能描述:提供基本数据访问功能 * ******************************/ namespace B2CSYS.DAL { public static class DBHelper { //数据库连接属性 private static SqlConnection connection; public static SqlConnection Connection { get { string connectionString = ConfigurationManager.ConnectionStrings["B2CConnectionString"].ConnectionString; if (connection == null) { connection = new SqlConnection(connectionString); connection.Open(); } else if (connection.State == System.Data.ConnectionState.Closed) { connection.Open(); } else if (connection.State == System.Data.ConnectionState.Broken) { connection.Close(); connection.Dispose(); connection.Open(); } return connection; } } /// <summary> /// 执行带参存储过程 /// </summary> public static void ExecProc(string strProName, SqlParameter[] para) { //connection.Open(); try { SqlCommand sqlcmd = connection.CreateCommand(); sqlcmd.CommandText = strProName; sqlcmd.CommandType = CommandType.StoredProcedure; foreach (SqlParameter paras in para) { sqlcmd.Parameters.Add(paras); } sqlcmd.ExecuteNonQuery(); } finally { connection.Close(); } } /// <summary> /// 根据sql语句执行非查询操作 /// </summary> public static bool ExecNoQuery(string sql) { //connection.Open(); try { SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = sql; return true; } catch { return false; } //Connection.Close(); } /// <summary> /// 执行无参SQL语句 /// </summary> public static int ExecuteCommand(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); int result = cmd.ExecuteNonQuery(); return result; } /// <summary> /// 执行带参SQL语句 /// </summary> public static int ExecuteCommand(string sql, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); return cmd.ExecuteNonQuery(); } /// <summary> /// 执行无参SQL语句,并返回执行记录数 /// </summary> public static int GetScalar(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); int result = Convert.ToInt32(cmd.ExecuteScalar()); return result; } /// <summary> /// 执行有参SQL语句,并返回执行记录数 /// </summary> public static int GetScalar(string sql, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); int result = Convert.ToInt32(cmd.ExecuteScalar()); return result; } /// <summary> /// 执行无参SQL语句,并返回SqlDataReader /// </summary> public static SqlDataReader GetReader(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); SqlDataReader reader = cmd.ExecuteReader(); return reader; } /// <summary> /// 执行有参SQL语句,并返回SqlDataReader /// </summary> public static SqlDataReader GetReader(string sql, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); SqlDataReader reader = cmd.ExecuteReader(); return reader; } public static DataTable GetDataSet(string safeSql) { DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(safeSql, Connection); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); return ds.Tables[0]; } public static DataTable GetDataSet(string sql, params SqlParameter[] values) { DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); return ds.Tables[0]; } /// <summary> /// 根据sql语句获得一个单值字符串 /// </summary> public static string ReturnStringScalar(string sql) { SqlCommand cmd = new SqlCommand(sql, Connection); try { string result = cmd.ExecuteScalar().ToString(); return result; } catch (Exception e) { Console.WriteLine(e.Message); return "0"; } connection.Close(); } /// <summary> /// 执行事务处理SQL /// </summary> public static bool ExecTSQL(string[] sqls) { connection.Open(); SqlTransaction trans = connection.BeginTransaction(IsolationLevel.ReadCommitted); try { for (int i = 0; i < sqls.Length; i++) { if (sqls[i] == "" || sqls[i] == null) { continue; } SqlCommand cmd = connection.CreateCommand(); cmd.Transaction = trans; cmd.CommandText = sqls[i]; cmd.ExecuteNonQuery(); } trans.Commit(); return true; } catch { trans.Rollback(); return false; } finally { trans = null; connection.Close(); } } } } |
|