.NET的SqlHelper应用代码

2018-06-18 04:21:48来源：未知阅读 ()

首先需要引用命名空间 ,同时也需要右击'引用' --> '添加引用' --> '程序集' --> '框架' --> 'System.Configuration',SqlHelper属于三层中的DAL层：

　　using System.Data;

　　using System.Data.SqlClient;

　　using System.Configuration;

 public class SqlHelper
    {
        private static string conStr = ConfigurationManager.ConnectionStrings["conStr"].ToString();

        public static SqlConnection Open()      //存储过程
        {
            SqlConnection con = new SqlConnection(conStr);
            con.Open();
            return con;
        }


        /// <summary>
        /// Insert update delete 
        /// </summary>
        /// <param name="commandText">SQL</param>
        /// <param name="type">命令类型</param>
        /// <param name="pars">参数列表</param>
        /// <returns></returns>
        public static int ExecuteNoQuery(string commandText, CommandType type, params SqlParameter[] pars)
        {
            int result = 0;
            using (SqlConnection con = new SqlConnection(conStr))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(commandText, con);
                cmd.CommandType = type;
                if (pars != null)
                {
                    cmd.Parameters.AddRange(pars);

                }
                result = cmd.ExecuteNonQuery();

            }
            return result;
        }
        /// <summary>
        /// select 
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="type"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string commandText, CommandType type, params SqlParameter[] pars)
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            using (SqlConnection con = new SqlConnection(conStr))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(commandText, con);
                cmd.CommandType = type;
                if (pars != null)
                {
                    cmd.Parameters.AddRange(pars);
                }
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(ds);
                dt = ds.Tables[0];
            }
            return dt;
        }
        private static SqlConnection con = null;
        public static void CloseCon()
        {
            if (con != null && con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
        /// <summary>
        /// select
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="type"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string commandText, CommandType type, SqlParameter[] pars)
        {

            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                con = new SqlConnection(conStr);
                con.Open();
                cmd = new SqlCommand(commandText, con);
                if (pars != null)
                {
                    cmd.Parameters.AddRange(pars);
                }
                reader = cmd.ExecuteReader();

            }
            catch (SqlException ex)
            {

            }
            return reader;


        }
        public static object ExecScalre(string commandText, CommandType type, SqlParameter[] pars)
        {
            object obj = null;
            using (SqlConnection con = new SqlConnection(conStr))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(commandText, con);
                cmd.CommandType = type;
                if (pars != null)
                {
                    cmd.Parameters.AddRange(pars);

                }
                obj = cmd.ExecuteScalar();

            }
            return obj;
        }

Web.config 代码如下:

<configuration>
  <system.web>
    <compilation debug="true" targetFramework="4.5" />
    <httpRuntime targetFramework="4.5" />
  </system.web>
  <connectionStrings>
    <add name="conStr" connectionString="Data Source=LS--20161126TNE;Initial catalog=TestOne;User Id = sa ;Pwd=as123123"></add>
  </connectionStrings>
</configuration>