DBHelper类

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

namespace Office.DAL
{
    public class DBHelper
    {
        private readonly static string connectionString = ConfigurationManager.ConnectionStrings["Office"].ConnectionString;
        /// <summary>
        /// 执行增、删、改命令
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数列表</param>
        public static void ExecuteCommand(string sql, params SqlParameter[] param)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand comm = new SqlCommand();
                PrepareCommand(comm, conn, sql, param);
                comm.ExecuteNonQuery();
                comm.Parameters.Clear();
            }
        }
        /// <summary>
        /// 读取数据
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数列表</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader GetReader(string sql, params SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand comm = new SqlCommand();
            PrepareCommand(comm, conn, sql, param);
            SqlDataReader da = comm.ExecuteReader(CommandBehavior.CloseConnection);
            return da;
        }
        /// <summary>
        /// 读取数据第一行第一列
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数列表</param>
        /// <returns>object</returns>
        public static object GetScalar(string sql, params SqlParameter[] param)
        {
            object obj = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand comm = new SqlCommand();
                PrepareCommand(comm, conn, sql, param);
                obj = comm.ExecuteScalar();
                comm.Parameters.Clear();
            }
            return obj;
        }
        /// <summary>
        /// 读取数据
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数列表</param>
        /// <returns>DataTable</returns>
        public static DataTable GetDataTable(string sql, params SqlParameter[] param)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                if (param != null)
                    da.SelectCommand.Parameters.AddRange(param);
                da.Fill(dt);
            }
            return dt;
        }
        /// <summary>
        /// 预编译
        /// </summary>
        private static void PrepareCommand(SqlCommand comm, SqlConnection conn, string sql, SqlParameter[] param)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            comm.Connection = conn;
            comm.CommandText = sql;

            if (param != null)
            {
                foreach (SqlParameter parm in param)
                    comm.Parameters.Add(parm);
            }
        }
    }
}