SqlHelper

一．知识点：

 SqlHelper主要是用于简化重复的去写那些数据库连接（SqlConnection）,SqlCommand,SqlDataReader等等。SqlHelper 封装过后通常是只需要给方法传入一些参数如数据库连接字符串，SQL参数,存储过程等，就可以访问数据库了。

在 SqlHelper 类中实现的方法包括：

        ExecuteNonQuery。此方法用于执行(有参数或无参数的)不返回任何行或值的命令。这些命令通常用于执行数据库(增\删\改)更新，但也可用于返回存储过程的输出参数。

       ExecuteReader。此方法用于返回 SqlDataReader 对象，该对象包含由某一命令返回的结果集。

       ExecuteDataset。此方法返回 DataSet 对象，该对象包含由某一命令返回的结果集。

 

二.思维导图

 

 

 

 

三．示例:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Configuration;  

using System.Data.SqlClient;

using System.Data;   <span style="white-space:pre">  </span>    

namespace lesson02

{

    public static class SqlHelper

    {

        Private static readonly string constr = ConfigurationManager.ConnectionStrings["connectionStr"].ConnectionString;

  

 

       // static string constr = @"server = (local)\MYSQL; database = itcast;uid = sa;pwd = 123456;";

        public static object ExecuteScalar(string sql,params SqlParameter[] pms)        {

            using (SqlConnection con = new SqlConnection(constr)){

                using (SqlCommand cmd = new SqlCommand(sql, con))

                {

                    if (pms != null)

                    {

                        cmd.Parameters.AddRange(pms);

                    }

                    con.Open();

                    return cmd.ExecuteScalar();

                }

            }

        }

 

        public static int ExecuteNonQuery(string sql, params SqlParameter[] pms)        {

            using (SqlConnection con = new SqlConnection(constr))

            {

                using (SqlCommand cmd = new SqlCommand(sql, con))

                {

                    if (pms != null)

                    {

                        cmd.Parameters.AddRange(pms);

                    }

                    con.Open();

                    return cmd.ExecuteNonQuery();

                }

            }

        }

 

        public static SqlDataReader ExecuteReader(string sql,params SqlParameter [] pms)        {

            SqlConnection con = new SqlConnection(constr);

            using (SqlCommand cmd = new SqlCommand(sql,con))

            {

                if (pms != null)

                {

                    cmd.Parameters.AddRange(pms);

                }

                try

                {

                    con.Open();

                    return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

                }

                catch (Exception)

                {

                    con.Close();

                    con.Dispose();

                    throw;

                }

            }

        }

 

        public static DataTable ExecuteDataTable(string sql, params SqlParameter[] pms)        {

            DataTable dt = new DataTable();

            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, constr))

            {

                if (pms != null)

                {

                    adapter.SelectCommand.Parameters.AddRange(pms);

                }

                adapter.Fill(dt);

            }

            return dt;

}

}