using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Model;

namespace DAL
{
    public class SqlHelper
    {
        static string connctionString = ConfigurationManager.ConnectionStrings["SqlString"].ToString();
        //增删该查使用的方法
        public static void ExcuteNonQuery(string sql)
        {
            //创建连接
            SqlConnection myConn = new SqlConnection(connctionString);
            //打开连接
            myConn.Open();
            //创建SqlCommand
            SqlCommand myComm = new SqlCommand(sql, myConn);
            //执行方法
            myComm.ExecuteNonQuery();
            //释放
            myComm.Dispose();
            //关闭连接
            myConn.Close();
        }

        //带参数的增删该查使用此方法
        public static void ExcuteNonQuery(string sql,SqlParameter [] sqlp)
        {
            //创建连接
            SqlConnection myConn = new SqlConnection(connctionString);
            //打开连接
            myConn.Open();
            //创建SqlCommand
            SqlCommand myComm = new SqlCommand(sql, myConn);
            myComm.Parameters.AddRange(sqlp);
            //执行方法
            myComm.ExecuteNonQuery();
            //释放
            myComm.Dispose();
            //关闭连接
            myConn.Close();
        }

        //查询使用的方法
        public static DataSet GetDataSet(string sql)
        {
            //创建连接
            SqlConnection myConn = new SqlConnection(connctionString);
            //打开连接
            myConn.Open();
            //创建SqlCommand
            SqlCommand myComm = new SqlCommand(sql, myConn);
            //创建SqlDataAdapter
            SqlDataAdapter sqlDa = new SqlDataAdapter(myComm);
            //创建DataSet
            DataSet myDs = new DataSet();
            //填充DataSet 
            sqlDa.Fill(myDs);
            //释放
            myComm.Dispose();
            //关闭连接
            myConn.Close();
            //返回结果
            return myDs;
        }
        //大参数的查询使用的方法
        public static DataSet GetDataSet(string sql,SqlParameter [] sqlp)
        {
            //创建连接
            SqlConnection myConn = new SqlConnection(connctionString);
            //打开连接
            myConn.Open();
            //创建SqlCommand
            SqlCommand myComm = new SqlCommand(sql, myConn);
            myComm.Parameters.AddRange(sqlp);
            //创建SqlDataAdapter
            SqlDataAdapter sqlDa = new SqlDataAdapter(myComm);
            //创建DataSet
            DataSet myDs = new DataSet();
            //填充DataSet 
            sqlDa.Fill(myDs);
            //释放
            myComm.Dispose();
            //关闭连接
            myConn.Close();
            //返回结果
            return myDs;
        }

       //获得DataReader 
        public static SqlDataReader GetDataReader(string sql)
        {
            //创建连接
            SqlConnection myConn = new SqlConnection(connctionString);
            //打开连接
            myConn.Open();
            //创建SqlCommand
            SqlCommand myComm = new SqlCommand(sql, myConn);
            //创建SqlDataReader
            SqlDataReader dr = myComm.ExecuteReader();
            return dr;
        }
    }
}