C#SqlHelper封装示例

    //封装一个sqlHelper

　　public static class SqlHelper
    {
        //定义一个连接字符串,readOnly修饰变量，只能在初始化的时候复制，以及在构造函数中赋值 
        private static readonly string conStr = ConfigurationManager.ConnectionStrings["mssqlserver"].ConnectionString;

        
        //1.执行增删改的方法
        public static int ExecuteNoQuery(string sql, params SqlParameter[] pms)//未知参数的表示
        {
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        //2. 执行查询，返回单个值的方法
        public static object ExecuteScalar(string sql, params SqlParameter[] pms)
        {
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    conn.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }

        //3. 执行查询，返回多行的方法，因为connection关闭会导致reader也关闭，因此不能使用using
        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms)
        {
            SqlConnection conn = new SqlConnection(conStr);
            
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                if (pms != null)
                {
                    cmd.Parameters.AddRange(pms);
                }
                try
                {
                    conn.Open();
                    return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                }
                catch 
                {
                    conn.Close();
                    conn.Dispose();
                    throw;
                }
                
            }

        }

  }