通用DBHelper类（1）

来源：互联网发布：物流仿真模拟软件编辑：程序博客网时间：2024/05/18 02:32

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

namespace PubsManager.DAL
{
    public class DBHelper
    {
        private static readonly string connectionString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;

        public static SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] paras)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                PreparedCommand(cmd, conn, cmdText, paras);
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return dr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        public static int ExecuteNonQuery(string cmdText,params SqlParameter [] paras)
        {
            using(SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PreparedCommand(cmd,conn,cmdText,paras);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        public static object ExecuteScalar(string cmdText,params SqlParameter [] paras)
        {
            using(SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PreparedCommand(cmd,conn,cmdText,paras);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();

                return val;
            }
        }

        public static void PreparedCommand(SqlCommand cmd, SqlConnection conn, string cmdText, SqlParameter[] paras)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

cmd.Connection = conn;
cmd.CommandText = cmdText;

            if (paras != null)
            {
                foreach (var p in paras)
                {
                    cmd.Parameters.Add(p);
                }
            }
        }

}
}