using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data; using System.Data.SqlClient; namespace Reservation.DAL { public class SqlHelper { /// <summary> /// 自定义数据库连接字符串 /// </summary> public static string ConnString = @"Data Source=.;Initial Catalog=DB_Date_Plan; User ID=sa;Pwd=111111"; /// <summary> /// 应用程序下获取连接字符串 /// </summary> //public static string ConnString //{ // get { // return System.Configuration.ConfigurationManager.ConnectionStrings["connstring"].ToString(); // } //} /// <summary> /// 执行一个Sql语句返回受影响的行数 /// </summary> /// <param name="sql">insert,update,delete或相关的存储过程</param> /// <param name="type">命令类型：SQL语句还是存储过程</param> /// <param name="pars">SQL语句所需要的参数</param> public static int ExcuteSQLReturnInt(string sql, CommandType type, params SqlParameter[] pars) { //定义连接对象 SqlConnection conn = new SqlConnection(ConnString); //判断连接对象的状态，并且打开 if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken) { conn.Open(); } try { //实例化命令对象 SqlCommand cmd = new SqlCommand(sql, conn); //判断CommandType类型是否是SQL语句还是存储过程 cmd.CommandType = type; if (pars != null && pars.Length > 0) { foreach (SqlParameter p in pars) { cmd.Parameters.Add(p); } } //调用方法执行SQL语句或存储过程 int count = cmd.ExecuteNonQuery(); return count; } catch (Exception ex) { return 0; } finally { //记得要关闭连接 conn.Close(); } } /// <summary> /// 执行一个Sql语句或存储过程，返回一条记录，sqldataReader /// </summary> /// <param name="sql">select语句，或相关的存储过程</param> /// <param name="type">指定命令类型，sql语句还是存储过程，默认的是sql语句</param> /// <param name="pars">参数的集合</param> /// <returns></returns> public static SqlDataReader ExcuteSqlReturnReader(string sql, CommandType type, SqlParameter[] pars) { SqlConnection conn = new SqlConnection(ConnString); if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken) { conn.Open(); } SqlCommand cmd = new SqlCommand(sql, conn); if (pars != null && pars.Length > 0) { foreach (SqlParameter p in pars) { cmd.Parameters.Add(p); } } cmd.CommandType = type; //当reader.close(),也就是关闭了datareader时，CommandBehavior.CloseConnection 也会关闭连接！ SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return reader; } /// <summary> /// 执行一个sql语句或存储过程,不带参数的!返回一天记录 sqldataReader /// </summary> /// <param name="sql"></param> /// <param name="type"></param> /// <returns></returns> public static SqlDataReader ExcuteSqlReturnReader(string sql, CommandType type) { SqlConnection conn = new SqlConnection(ConnString); if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken) { conn.Open(); } SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = type; //当reader.close(),也就是关闭了datareader时，CommandBehavior.CloseConnection 也会关闭连接！ SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return reader; } /// <summary> /// 执行一个Sql语句或存储过程，返回一个数据集，dataset /// </summary> /// <param name="sql">select语句或相关的存储过程</param> /// <param name="type">命令类型</param> /// <param name="pars">命令类型</param> /// <returns>DataSet</returns> public static DataSet SelectSqlReturnDataset(string sql, CommandType type, SqlParameter[] pars) { SqlConnection conn = new SqlConnection(ConnString); //数据集识别器对象SqlDataAdapter 会自动打开数据库链接！ SqlDataAdapter sda = new SqlDataAdapter(sql, conn); if (pars != null && pars.Length > 0) { foreach (SqlParameter p in pars) { sda.SelectCommand.Parameters.Add(p); } } sda.SelectCommand.CommandType = type; DataSet ds = new DataSet(); sda.Fill(ds); return ds; } /// <summary> /// 执行一个sql语句返回一个数据表对象 /// </summary> /// <param name="sql">select</param> /// <param name="type">命令类型</param> /// <param name="pars">参数集合</param> /// <returns>DataTable</returns> public static DataTable SelectSqlReturnDataTable(string sql, CommandType type, SqlParameter[] pars) { SqlConnection conn = new SqlConnection(ConnString); SqlDataAdapter sda = new SqlDataAdapter(sql, conn); if (pars != null && pars.Length > 0) { foreach (SqlParameter p in pars) { sda.SelectCommand.Parameters.Add(p); } } sda.SelectCommand.CommandType = type; DataTable dt = new DataTable(); sda.Fill(dt); return dt; } //******************************************************************* //SelectSqlReturnDataTable方法的重载，不传参数的情况下 /// <summary> /// 执行一个sql语句返回一个数据表对象,不传参数 /// </summary> /// <param name="sql">select</param> /// <param name="type">命令类型</param> /// <returns></returns> public static DataTable SelectSqlReturnDataTable(string sql, CommandType type) { SqlConnection conn = new SqlConnection(ConnString); SqlDataAdapter sda = new SqlDataAdapter(sql, conn); sda.SelectCommand.CommandType = type; DataTable dt = new DataTable(); sda.Fill(dt); return dt; } /// <summary> /// 执行一个sql语句或相关的存储过程，返回一个值 /// </summary> /// <param name="sql">select count(*) from tablename where ....</param> /// <param name="type">命令类型</param> /// <param name="pars">所需的参数</param> /// <returns>object</returns> public static object selectSqlReturnObject(string sql, CommandType type, SqlParameter[] pars) { SqlConnection conn = new SqlConnection(ConnString); if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken) { conn.Open(); } try { SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = type; if (pars != null && pars.Length > 0) { foreach (SqlParameter p in pars) { cmd.Parameters.Add(p); } } object obj = cmd.ExecuteScalar(); return obj; } catch (Exception ex) { return null; } finally { conn.Close(); } } /// <summary> /// 执行一个sql语句或相关的存储过程，返回一个值，不传参数 /// </summary> /// <param name="sql"></param> /// <param name="type"></param> /// <returns></returns> public static object selectSqlReturnObject(string sql, CommandType type) { SqlConnection conn = new SqlConnection(ConnString); if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken) { conn.Open(); } try { SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = type; object obj = cmd.ExecuteScalar(); return obj; } catch (Exception ex) { return null; } finally { conn.Close(); } } } }