C#之连接数据库（二）之封装sql方法

在上篇文章中说过，如果每次和数据库交互都写一次连接字符串、打开和关闭代码冗余度太高，而且将连接字符串暴露在代码中也不太安全。现在就来看如何将对数据库的操作分类封装成方法，并将数据库的连接字符串写在配置文件中。
将连接字符串写在配置文件里
1、连接字符串写在web.config或app.config的<app.Setting>中：

<appSettings>
<add key="ConnectionString"value="server=服务器名\实例名;database=库名;uid=sa;pwd=123456;Pooling=true;" />
</appSettings>

获取连接字符串：

string connectStr = ConfigurationManager.AppSettings["ConnectionString"];

或

string connectStr = ConfigurationSettings.AppSettings["ConnectionString"];

2、连接字符串写在web.config或app.config的<?connectionStrings >中：

<connectionStrings>
         		<add name="ConnectionString"connectionString="server=服务器名\实例名;database=库名;uid=sa;pwd=123456;Pooling=true;" providerName="System.Data.SqlClient"/>
</connectionStrings>

获取连接字符串：

string connectStr= ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

封装操作数据库的方法

 public class SqlCon
    {
        // 数据库访问和增、删、改数据操作
        private  string PriConnectionString = string.Empty;
        private static SqlCon _instance = null;//单例
        public SqlCon()
        {
        		//获取连接字符串
            try
            {
                PriConnectionString = ConfigurationManager.ConnectionStrings["数据库名"].ToString();
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("未配置{0}库:{1}", "数据库名", ex.Message));
            }
        }
        /// <summary>
        /// 单例模式
        /// </summary>
        /// <returns></returns>
        public static SqlCon GetInstance()
        {
            if (_instance == null)
            {
                _instance = new SqlCon();
            }
            return _instance;
        }

        #region 执行查询，返回要查询的结果集 DataTable

        /// <summary>
        /// 执行查询，返回要查询的结果集
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public DataTable ExecuteTable(string par_SqlStr)
        {
            return ExecuteTable(par_SqlStr, CommandType.Text, null);
        }


        /// <summary>
        /// 创建一个DataTable对象
        /// </summary>
        /// <param name="M_str_par_SqlStr">SQL语句</param>
        /// <param name="M_str_table">表名</param>
        /// <returns>返回DataSet对象</returns>
        /// <summary>
        /// 执行查询，返回要查询的结果集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="par_CommandType">SQL语句的类型（SQL文本命令或存储过程）</param>
        /// <returns></returns>
        public DataTable ExecuteTable(string par_SqlStr, CommandType par_CommandType = CommandType.Text)
        {
            return ExecuteTable(par_SqlStr, par_CommandType, null);
        }

        /// <summary>
        /// 执行查询，返回要查询的结果集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="par_CommandType">SQL语句的类型（SQL文本命令或存储过程）</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        public DataTable ExecuteTable(string sql, CommandType par_CommandType, params System.Data.SqlClient.SqlParameter[] pParameters)
        {
            // 保存要查询的结果
            DataTable table = new DataTable("dt");
            //SqlCommand command;
            using (SqlConnection Connection = new SqlConnection(PriConnectionString))
            {
                //command = new SqlCommand(sql, Connection);
                using (SqlCommand command = new SqlCommand(sql, Connection))
                {
                    try
                    {

                        if (Connection.State != ConnectionState.Open)
                            Connection.Open();
                        // 设置SQL语句的类型
                        command.CommandType = par_CommandType;
                        command.CommandTimeout = 36000;
                        // 如果传入了参数，则添加到命令对象中
                        if (pParameters != null)
                        {
                            for (int i = command.Parameters.Count - 1; i >= 0; i--)
                            {
                                command.Parameters.RemoveAt(i);
                            }
                            command.Parameters.AddRange(pParameters);
                        }

                        // 创建一个适配器对象，用以将结果填充到DataTable中
                        SqlDataAdapter adapter = new SqlDataAdapter(command);

                        adapter.Fill(table);

                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        throw new Exception(E.Message);
                    }
                    finally
                    {
                        command.Parameters.Clear();
                        command.Dispose();
                        Connection.Close();
                    }
                }
            }
            return table;


        }
        #endregion

        #region 执行查询，返回第一行第一列的值
        /// <summary>
        /// 执行查询，返回第一行第一列的值
        /// </summary>
        /// <param name="sql">存储过程名</param>
        /// <returns>