- 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();
- }
- }
- }
- }
来源: http://www.bubuko.com/infodetail-2156988.html