欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

SQLhelper帮助类

程序员文章站 2022-06-02 23:46:11
...
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Utility
{
    public class SQLHelper
    {
        #region 通用方法
        // 数据连接池 
        private SqlConnection con;
        private string _message;
        string _Message = string.Empty;
        public string Message
        {
            get { return _message; }
            set { _message = value; }
        }
        private CommandType _SQLCommandType = CommandType.Text;
        /// <summary>
        /// 命令类型
        /// </summary>
        public CommandType SQLCommandType
        {
            get { return this._SQLCommandType; }
            set { this._SQLCommandType = value; }
        }
        /// <summary>
        /// 返回数据库连接字符串
        /// </summary>
        /// <returns></returns>
        public static String GetSqlConnection()
        {
            string connString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStrPool"].ConnectionString;
            return connString;
        }
        #endregion

        #region 执行sql字符串
        /// <summary>
        /// 执行不带参数的SQL语句
        /// </summary>
        /// <param name="StrSql"></param>
        /// <returns></returns>
        public static int ExecuteSql(String StrSql)
        {
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = StrSql;
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                return 1;
            }
        }

        /// <summary>
        /// cyj 2012 7 4
        /// 验证数据是否存在
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public bool ValiExist(string sql, SqlParameter[] param)
        {
            String ConnStr = GetSqlConnection();
            using (SqlConnection con = new SqlConnection(ConnStr))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(sql, con);
                if (param != null)
                    cmd.Parameters.AddRange(param);
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    if (dr.Read())
                    {
                        con.Close();
                        return true;
                    }
                    else
                    {
                        con.Close();
                        return false;
                    }

                }
            }
        }

        /// <summary>
        /// 执行不带参数的SQL语句(替换上面的方法。暂时保留上面的方法,是为了与旧系统能兼容) 
        /// 作者:Duke
        /// 时间:2012.2.6
        /// 改写目的:程序出错时,不崩溃,并能给出友好的提示信息
        /// 改写方法:
        /// 1、取消静态方法(不方便出错信息等属性读取)
        /// 2、加入try结构
        /// 3、封闭类的消息属性 
        /// </summary>
        /// <param name="StrSql"></param>
        /// <returns></returns>
        public int ExecuteSqlNoPa(String StrSql)
        {
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = StrSql;
                try
                {
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    this.Message = ex.Message;
                    return 0;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 执行带参数的SQL语句
        /// 作者:Duke
        /// 时间:2012.2.2
        /// 改写目的:程序出错时,不崩溃,并能给出友好的提示信息
        /// 改写方法:
        /// 1、取消静态方法(不方便出错信息等属性读取)
        /// 2、加入try结构
        /// 3、封闭类的消息属性
        /// </summary>
        /// <param name="StrSql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public  int ExecuteSql(String StrSql, SqlParameter[] param)
        {
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = StrSql;
                cmd.Parameters.AddRange(param);
                try
                {
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                   
                    this.Message = ex.Message;
                    return 0;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 返回DataReader
        /// </summary>
        /// <param name="StrSql"></param>
        /// <returns></returns> 
        public static SqlDataReader ExecuteReader(String StrSql)
        {
            String ConnStr = GetSqlConnection();
            SqlConnection conn = new SqlConnection(ConnStr);//返回DataReader时,是不可以用using()的 
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = StrSql;
                conn.Open();
                return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection 
            }
            catch //(Exception ex) 
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 获取单个值
        /// </summary>
        /// <param name="selectSQL"></param>
        /// <returns></returns>
        public object ExecScalar(string selectSQL)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = GetSqlConnection();
            SqlCommand command = new SqlCommand(selectSQL, conn);
            try
            {
                conn.Open();
                return command.ExecuteScalar();
            }
            catch (SqlException ex)
            {
                Message = ex.Message;
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 执行SQL语句并返回数据表
        /// </summary>
        /// <param name="StrSql"></param>
        /// <returns></returns>
        public static DataTable ExecuteDt(String StrSql)
        {
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlDataAdapter da = new SqlDataAdapter(StrSql, conn);
                DataTable dt = new DataTable();
                conn.Open();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
        }

        /// <summary>
        /// 执行SQL语句并返回DataSet
        /// </summary>
        /// <param name="StrSql"></param>
        /// <returns></returns>
        public static DataSet ExecuteDs(String StrSql)
        {
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlDataAdapter da = new SqlDataAdapter(StrSql, conn);
                DataSet ds = new DataSet();
                conn.Open();
                da.SelectCommand.CommandTimeout = 180;
                da.Fill(ds);
                conn.Close();
                return ds;
            }
        }

        /// <summary>
        /// 获取结果集(含参数)
        /// </summary>
        /// <param name="selectSQL">Select语句或者存储过程名称</param>
        /// <param name="paramList">参数列表</param>
        /// <returns></returns>
        public DataTable Select(string selectSQL, System.Data.SqlClient.SqlParameter[] paramList)
        {

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = GetSqlConnection();

            SqlCommand command = new SqlCommand(selectSQL);
            command.CommandType = this.SQLCommandType;
            command.Parameters.AddRange(paramList);
            command.Connection = conn;

            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataTable ds = new DataTable();
            try
            {
                conn.Open();
                adapter.Fill(ds);
            }
            catch (SqlException ex)
            {
                Message = ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return ds;
        }

        #endregion

        #region 操作存储过程
        /// <summary>
        /// 调用存储过程(有参数)
        /// </summary>
        /// <param name="name"></param>
        /// <param name="paramList"></param>
        /// <returns>-1:失败</returns>
        public int CallProcedure(string name, System.Data.Common.DbParameter[] paramList)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = GetSqlConnection();

            SqlCommand command = new SqlCommand(name);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddRange(paramList);
            command.Connection = conn;
            try
            {
                conn.Open();
                return command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                Message = ex.Message;
                return -1;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 调用存储过程(无参数)
        /// </summary>
        /// <param name="name"></param>
        /// <param name="paramList"></param>
        /// <returns>-1:失败</returns>
        public int CallProcedure(string name)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = GetSqlConnection();

            SqlCommand command = new SqlCommand(name);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Connection = conn;
            try
            {
                conn.Open();
                return command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                this._Message = ex.Message;
                return -1;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 运行存储过程(已重载) 
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <returns></returns>
        public int RunProc(string procName)
        {
            SqlCommand cmd = CreateCommand(procName, null);
            cmd.ExecuteNonQuery();
            this.Close();
            return (int)cmd.Parameters["ReturnValue"].Value;
        }

        /// <summary>
        /// 运行存储过程(已重载)
        /// </summary>
        /// <param name="procName">存储过程的名字</param>
        /// <param name="prams">存储过程的输入参数列表</param>
        /// <returns>存储过程的返回值</returns>
        public int RunProc(string procName, SqlParameter[] prams)
        {
            SqlCommand cmd = CreateCommand(procName, prams);

            cmd.ExecuteNonQuery();
            this.Close();
            return (int)cmd.Parameters[0].Value;
        }

        /// <summary>
        /// 运行存储过程(已重载)
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="dataReader">结果集</param>
        public void RunProc(string procName, out SqlDataReader dataReader)
        {
            SqlCommand cmd = CreateCommand(procName, null);
            dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 运行存储过程(已重载)
        /// </summary>
        /// <param name="procName">存储过程的名字</param>
        /// <param name="prams">存储过程的输入参数列表</param>
        /// <param name="dataReader">结果集</param>
        public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)
        {
            SqlCommand cmd = CreateCommand(procName, prams);
            dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 创建Command对象用于访问存储过程
        /// </summary>
        /// <param name="procName">存储过程的名字</param>
        /// <param name="prams">存储过程的输入参数列表</param>
        /// <returns>Command对象</returns>
        private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
        {
            // 确定连接是打开的 
            Open();
            //command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) ); 
            SqlCommand cmd = new SqlCommand(procName, con);
            cmd.CommandType = CommandType.StoredProcedure;
            // 添加存储过程的输入参数列表 
            if (prams != null)
            {
                foreach (SqlParameter parameter in prams) cmd.Parameters.Add(parameter);
            }
            // 返回Command对象 
            return cmd;
        }

        /// <summary>
        /// 创建输入参数
        /// </summary>
        /// <param name="ParamName">参数名</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <param name="Value">参数值</param>
        /// <returns>新参数对象</returns>
        public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
        }

        /// <summary>
        /// 创建输出参数
        /// </summary>
        /// <param name="ParamName">参数名</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <returns>新参数对象</returns>
        public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
        }
        /// <summary>
        /// 创建存储过程参数
        /// </summary>
        /// <param name="ParamName">参数名</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <param name="Direction">参数的方向(输入/输出)</param>
        /// <param name="Value">参数值</param>
        /// <returns>新参数对象</returns>
        public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
        {
            SqlParameter param;
            if (Size > 0)
            {
                param = new SqlParameter(ParamName, DbType, Size);
            }
            else
            {
                param = new SqlParameter(ParamName, DbType);
            }
            param.Direction = Direction;
            if (!(Direction == ParameterDirection.Output && Value == null))
            {
                param.Value = Value;
            }
            return param;
        }
        #endregion

        #region 数据库连接和关闭

        public void OpenAdd()
        {
            Open();
        }
        /// <summary>
        /// 打开连接池
        /// </summary>
        private void Open()
        {
            // 打开连接池 
            if (con == null)
            {
                //这里不仅需要using System.Configuration;还要在引用目录里添加 
                con = new SqlConnection(GetSqlConnection());
                con.Open();
            }
        }

        /// <summary>
        /// 关闭连接池
        /// </summary>
        public void Close()
        {
            if (con != null)
                con.Close();
        }

        /// <summary>
        /// 释放连接池
        /// </summary>
        public void Dispose()
        {
            // 确定连接已关闭 
            if (con != null)
            {
                con.Dispose();
                con = null;
            }
        }

        #endregion
    }
}
相关标签: c# SQLhelper