sqlhelp封装类

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace DBUtitle
{
    public class SqlHelp
    {
        public static string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ToString();
        public static SqlConnection conn;//声明连接对象
        public static SqlCommand cmd;//声明命令对象
        #region 执行不带参数的SQL的语句
        /// <summary>
        /// 执行不带参数的SQL的语句
        /// </summary>
        /// <param name="str">SQL的语句</param>
        /// <returns>返回int型</returns>
        public static int ExecuteSql(string str)
        {
            using (conn = new SqlConnection(strConn))
            {
                using (cmd = new SqlCommand(str, conn))
                {
                    conn.Open();
                    try
                    {
                        return cmd.ExecuteNonQuery();
                    }
                    catch (SqlException er)
                    {
                        throw er;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }
        /// <summary>
        /// 执行不带参数的SQL的语句
        /// </summary>
        /// <param name="str">SQL的语句</param>
        /// <returns>返回SqlDataReader型</returns>
        public static SqlDataReader ExecuteReader2(string str)
        {
            conn = new SqlConnection(strConn);
            cmd = new SqlCommand(str, conn);
            conn.Open();
            try
            {
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);//执行之后关闭连接
                //dr.Read();
                return dr;
            }
            catch (SqlException er)
            {
                throw er;
            }
        }
        /// <summary>
        /// 执行不带参数的SQL的语句
        /// </summary>
        /// <param name="str">SQL的语句</param>
        /// <returns>返回DataSet型</returns>
        public static DataSet ExecuteDataSet(string str)
        {
            using (conn = new SqlConnection(strConn))
            {
                using (cmd = new SqlCommand(str, conn))
                {
                    conn.Open();
                    SqlDataAdapter ad = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    try
                    {
                        ad.Fill(ds);
                        return ds;
                    }
                    catch (SqlException er)
                    {
                        throw er;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }
        #endregion
        #region 执行带参数的SQL的语句
        /// <summary>
        /// 执行带参数的SQL的语句
        /// </summary>
        /// <param name="str">SQL的语句</param>
        /// <returns>返回int型</returns>
        public static int ExecuteSql(string str, SqlParameter[] sp)
        {
            using (conn = new SqlConnection(strConn))
            {
                using (cmd = new SqlCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;
                    cmd.CommandText = str;
                    //添加参数
                    if (sp != null)
                    {
                        foreach (SqlParameter p in sp)
                        {
                            cmd.Parameters.Add(p);
                        }
                    }
                    try
                    {
                        int intI = cmd.ExecuteNonQuery();
                        return intI;
                    }
                    catch (SqlException er)
                    {
                        throw er;
                    }
                    finally
                    {
                        cmd.Parameters.Clear();//清空参数
                        conn.Close();
                    }
                }
            }
        }
        /// <summary>
        /// 执行带参数的SQL的语句
        /// </summary>
        /// <param name="str">SQL的语句</param>
        /// <returns>返回SqlDataReader型</returns>
        public static SqlDataReader ExecuteReader2(string str, SqlParameter[] sp)
        {
            conn = new SqlConnection(strConn);
            cmd = new SqlCommand();
            conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = str;
            //添加参数
            if (sp != null)
            {
                foreach (SqlParameter p in sp)
                {
                    cmd.Parameters.Add(p);
                }
            }
            try
            {
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);//执行之后关闭连接
                return dr;
            }
            catch (SqlException er)
            {
                throw er;
            }
            finally
            {
                cmd.Parameters.Clear();//清空参数
            }
        }
        /// <summary>
        /// 执行带参数的SQL的语句
        /// </summary>
        /// <param name="str">SQL的语句</param>
        /// <returns>返回DataSet型</returns>
        public static DataSet ExecuteDataSet(string str, SqlParameter[] sp)
        {
            using (conn = new SqlConnection(strConn))
            {
                using (cmd = new SqlCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;
                    cmd.CommandText = str;
                    //添加参数
                    if (sp != null)
                    {
                        foreach (SqlParameter p in sp)
                        {
                            cmd.Parameters.Add(p);
                        }
                    }
                    SqlDataAdapter ad = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    try
                    {
                        ad.Fill(ds);
                        return ds;
                    }
                    catch (SqlException er)
                    {
                        throw er;
                    }
                    finally
                    {
                        cmd.Parameters.Clear();//清空参数
                        conn.Close();
                    }
                }
            }
        }
        #endregion
        #region 执行不带参数的存储过程
        /// <summary>
        /// 执行不带参数的存储过程
        /// </summary>
        /// <param name="str">存储过程名</param>
        /// <returns>返回int型</returns>
        public static int ExecuteProc(string strproc)
        {
            using (conn = new SqlConnection(strConn))
            {
                using (cmd = new SqlCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;
                    cmd.CommandText = strproc;
                    cmd.CommandType = CommandType.StoredProcedure;
                    try
                    {
                        return cmd.ExecuteNonQuery();
                    }
                    catch (SqlException er)
                    {
                        throw er;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }
        /// <summary>
        /// 执行不带参数的存储过程
        /// </summary>
        /// <param name="str">存储过程名</param>
        /// <returns>返回SqlDataReader型</returns>
        public static SqlDataReader ExecuteProcReader(string strproc)
        {
            conn = new SqlConnection(strConn);
            cmd = new SqlCommand();
            conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = strproc;
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);//执行之后关闭连接
                return dr;
            }
            catch (SqlException er)
            {
                throw er;
            }
            finally
            {
            }

        }
        /// <summary>
        /// 执行不带参数的存储过程
        /// </summary>
        /// <param name="str">存储过程名</param>
        /// <returns>返回DataSet型</returns>
        public static DataSet ExecuteProcDataSet(string strproc)
        {
            using (conn = new SqlConnection(strConn))
            {
                using (cmd = new SqlCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;
                    cmd.CommandText = strproc;
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataAdapter ad = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    try
                    {
                        ad.Fill(ds);
                        return ds;
                    }
                    catch (SqlException er)
                    {
                        throw er;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }
        #endregion
        #region 执行带参数的存储过程
        /// <summary>
        /// 执行带参数的存储过程
        /// </summary>
        /// <param name="str">存储过程名</param>
        /// <returns>返回int型</returns>
        public static int ExecuteProc(string strproc, SqlParameter[] sp)
        {
            using (conn = new SqlConnection(strConn))
            {
                using (cmd = new SqlCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;
                    cmd.CommandText = strproc;
                    cmd.CommandType = CommandType.StoredProcedure;
                    //添加参数
                    if (sp != null)
                    {
                        foreach (SqlParameter p in sp)
                        {
                            cmd.Parameters.Add(p);
                        }
                    }
                    try
                    {
                        return cmd.ExecuteNonQuery();
                    }
                    catch (SqlException er)
                    {
                        throw er;
                    }
                    finally
                    {
                        cmd.Parameters.Clear();//清空参数
                        conn.Close();
                    }
                }
            }
        }
        /// <summary>
        /// 执行带参数的存储过程
        /// </summary>
        /// <param name="str">存储过程名</param>
        /// <returns>返回SqlDataReader型</returns>
        public static SqlDataReader ExecuteProcReader(string strproc, SqlParameter[] sp)
        {
            conn = new SqlConnection(strConn);
            cmd = new SqlCommand();
            conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = strproc;
            cmd.CommandType = CommandType.StoredProcedure;
            //添加参数
            if (sp != null)
            {
                foreach (SqlParameter p in sp)
                {
                    cmd.Parameters.Add(p);
                }
            }
            try
            {
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);//执行之后关闭连接
                return dr;
            }
            catch (SqlException er)
            {
                throw er;
            }
            finally
            {
                cmd.Parameters.Clear();//清空参数
            }

        }
        /// <summary>
        /// 执行带参数的存储过程
        /// </summary>
        /// <param name="str">存储过程名</param>
        /// <returns>返回DataSet型</returns>
        public static DataSet ExecuteProcDataSet(string strproc, SqlParameter[] sp)
        {
            using (conn = new SqlConnection(strConn))
            {
                using (cmd = new SqlCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;
                    cmd.CommandText = strproc;
                    cmd.CommandType = CommandType.StoredProcedure;
                    //添加参数
                    if (sp != null)
                    {
                        foreach (SqlParameter p in sp)
                        {
                            cmd.Parameters.Add(p);
                        }
                    }
                    SqlDataAdapter ad = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    try
                    {
                        ad.Fill(ds);
                        return ds;
                    }
                    catch (SqlException er)
                    {
                        throw er;
                    }
                    finally
                    {
                        cmd.Parameters.Clear();//清空参数
                        conn.Close();
                    }
                }
            }
        }
        /// <summary>
        /// 执行带参数的存储过程
        /// </summary>
        /// <param name="str">存储过程名</param>
        /// <returns>返回string型</returns>
        public static string Execute(string strproc, SqlParameter[] sp)
        {
            using (conn = new SqlConnection(strConn))
            {
                using (cmd = new SqlCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;
                    cmd.CommandText = strproc;
                    cmd.CommandType = CommandType.StoredProcedure;
                    //添加参数
                    if (sp != null)
                    {
                        foreach (SqlParameter p in sp)
                        {
                            cmd.Parameters.Add(p);
                        }
                    }
                    try
                    {
                        cmd.ExecuteNonQuery();
                        return cmd.Parameters["@str"].Value.ToString();
                    }
                    catch (SqlException er)
                    {
                        throw er;
                    }
                    finally
                    {
                        cmd.Parameters.Clear();//清空参数
                        conn.Close();
                    }
                }
            }
        }
        #endregion
    }
}