using System;

 using System.Collections.Generic;

 using System.Linq;

 using System.Text;

 using System.Threading.Tasks;

 using System.Data.SqlClient;

 using System.Data;

 namespace MySchool.DAL

 {

     public class DBHelper

     {

         //private const string conn = "Data Source=.;Initial Catalog=MySchool;User ID=GaoFei;Password=111111";

         private const string conn = "Data Source=.;Initial Catalog=MySchool;User ID=sa;Password=sa";

         private static SqlConnection connection = new SqlConnection(conn);

         /// <summary>

         /// 读取数据

         /// </summary>

         /// <returns></returns>

         public static SqlDataReader Reader(string sql)

         {

             try

             {

                 connection.Open();

                 SqlCommand comm = new SqlCommand(sql, connection);

                 return comm.ExecuteReader(CommandBehavior.CloseConnection);

             }

             catch (Exception ex)

             {

                 throw;

             }

         }

         /// <summary>

         /// 增删改数据

         /// </summary>

         /// <param name="sql"></param>

         /// <returns></returns>

         public static int ExecuteNonQuery(string sql)

         {

             try

             {

                 connection.Open();   //打开数据库连接

                 SqlCommand comm = new SqlCommand(sql, connection);

                 return comm.ExecuteNonQuery();

             }

             catch (Exception ex)

             {

                 throw;

             }

             finally

             {

                 connection.Close();

             }

         }

         /// <summary>

         /// 返回单个值

         /// </summary>

         /// <param name="sql"></param>

         /// <returns></returns>

         public static object ExecuteScalar(string sql)

         {

             try

             {

                 connection.Open();   //打开数据库连接

                 SqlCommand comm = new SqlCommand(sql, connection);

                 return comm.ExecuteScalar();

             }

             catch (Exception)

             {

                 throw;

             }

             finally

             {

                 connection.Close();

             }

         }

         /// <summary>

         /// 返回数据集

         /// </summary>

         /// <param name="sql"></param>

         /// <param name="tableName"></param>

         /// <returns></returns>

         public static DataSet Fill(string sql, string tableName)

         {

             try

             {

                 connection.Open();  //打开连接

                 //创建数据适配器对象

                 SqlDataAdapter sda = new SqlDataAdapter(sql, connection);

                 //创建数据集

                 DataSet ds = new DataSet();

                 sda.Fill(ds, tableName); //填充数据集

                 return ds;

             }

             catch (Exception ex)

             {

                 throw;

                 //将异常引发出现

                 //  throw new Exception(e.Message);

             }

             finally

             {

                 connection.Close();

             }

         }

     }

 }