新学习用的DBHelper类

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


namespace MyCode
{
    public static class MyDBHelper
    {
        //从配置文件中得到连接
        public static readonly string MyConnection = ConfigurationManager.ConnectionStrings["MyConnection"].ToString();
        public static readonly string dbo = ConfigurationManager.ConnectionStrings["dbo"].ToString();


        /// <summary>
        /// 得到连接
        /// </summary>
        private static SqlConnection connection;
        public static SqlConnection Connection
        {
            get
            {
                string connectionString = MyConnection;
                if (connection == null)
                {
                    connection = new SqlConnection(connectionString);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)  //判断连接是否中断
                {
                    connection.Close();
                    connection.Open();
                }
                return connection;
            }
        }

        /// <summary>
        /// 关闭所有连接
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="reader"></param>
        public static void closeAll(SqlConnection conn, SqlDataReader reader)
        {
            if (conn.State == System.Data.ConnectionState.Open)
            {
                conn.Close();
            } if (reader.IsClosed)
            {
                reader.Close();
            }
        }

        /// <summary>
        /// 执行增、改、查语句，返回个数
        /// </summary>
        /// <param name="safeSql"></param>
        /// <returns></returns>
        public static int ExecuteCommand(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            int result = cmd.ExecuteNonQuery();

            return result;
        }

        /// <summary>
        /// 执行带多个参数的增删改的 存储过程
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static int ExecuteCommand(string sql, SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.CommandType = CommandType.StoredProcedure;  //我新加
            cmd.Parameters.AddRange(values);

            return cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 执行带一个参数的增删改的 存储过程
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static int ExecuteCommand(string sql, SqlParameter value)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.Add(value);
            int result = cmd.ExecuteNonQuery();

            return result;
        }


        public static int ExecuteScalar(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            int result = (int)cmd.ExecuteScalar();
            return result;
        }

        public static int ExecuteScalar(string sql, SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            int result = (int)cmd.ExecuteScalar();

            return result;
        }

        public static int ExecuteScalar(string sql, SqlParameter value)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.Add(value);
            int result = (int)cmd.ExecuteScalar();

            return result;
        }

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="safeSql"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }

        /// <summary>
        /// 执行带一个参数的查询 存储过程
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string sql, SqlParameter value)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.Add(value);
            SqlDataReader reader = cmd.ExecuteReader();

            return reader;
        }

        /// <summary>
        /// 执行带多个参数的查询 存储过程
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string sql, SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SqlDataReader reader = cmd.ExecuteReader();

            return reader;
        }

        /// <summary>
        /// 执行查询，得到数据集
        /// </summary>
        /// <param name="safeSql"></param>
        /// <returns></returns>
        public static DataTable GetDataSet(string safeSql)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);

            return ds.Tables[0];
        }
    }
}

 

<connectionStrings>
    <add name="MyConnection" connectionString="Data Source=MRYY\SQLEXPRESS;Initial Catalog=tt;Persist Security Info=True;User ID=sa;Password=sa" providerName="System.Data.SqlClient"/>
    <add name="dbo" connectionString=".dbo"/>
</connectionStrings>