.NET连接数据库的通用类

来源：互联网发布：胎儿b超数据看男女编辑：程序博客网时间：2024/05/23 23:47

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
/********************************
* 类名：DBHelper
* 功能描述：连接数据库
* ******************************/
namespace HotelManager.DAL
{
    public static class DBHelper
    {
        #region private Members 从配置文件中读取数据库连接字符串
        /// <summary>
        /// 返回Connection对象
        /// </summary>
        private static SqlConnection connection;

        public static SqlConnection Connection
        {
            get
            {
                string connString = ConfigurationManager.ConnectionStrings["HotelManagerConnString"].ConnectionString;

                if (connection == null)
                {
                    connection = new SqlConnection(connString);
                    connection.Open();
                }
                else if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                return connection;
            }
        }

        public static string dboOwner=ConfigurationManager.ConnectionStrings["DataBaseOwner"].ConnectionString;

        #endregion

        #region ExecuteCommand 执行带参数的SQL命令
        /// <summary>
        /// 执行带参数的SQL命令
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteCommand(string sql,params SqlParameter[] values)
        {
            SqlCommand command = new SqlCommand(dboOwner + sql, Connection);
            command.CommandType = CommandType.StoredProcedure;
            if (values != null)
              command.Parameters.AddRange(values);

            int result = command.ExecuteNonQuery();
            return result;
        }
        #endregion

        #region GetReader 执行带参数的SQL命令,返回SqlDataReader对象
        /// <summary>
        /// 执行SQL命令,返回SqlDataReader对象
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string sql,params SqlParameter[] values)
        {
            SqlCommand command = new SqlCommand(dboOwner + sql, Connection);
            command.CommandType = CommandType.StoredProcedure;
            if (values != null)
              command.Parameters.AddRange(values);

            SqlDataReader reader = command.ExecuteReader();
            return reader;
        }
        #endregion

        #region GetScalar 执行带参数的SQL命令,返回结果中的第一行第一列
        /// <summary>
        /// 执行带参数的SQL命令,返回结果中的第一行第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int GetScalar(string sql,params SqlParameter[] values)
        {
            SqlCommand command = new SqlCommand(dboOwner + sql, Connection);
            command.CommandType = CommandType.StoredProcedure;
            if (values != null)
              command.Parameters.AddRange(values);

            int result = command.ExecuteNonQuery();
            return result;
        }
        #endregion

        #region GetDataSet 执行带参数的SQL命令,返回DataTable对象
        /// <summary>
        /// 执行SQL命令,返回DataTable对象
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable GetDataSet(string sql,params SqlParameter[] values)
        {
            DataSet ds = new DataSet();
            SqlCommand command = new SqlCommand(dboOwner + sql, Connection);
            command.CommandType = CommandType.StoredProcedure;

if (values != null)
command.Parameters.AddRange(values);

            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds);
            return ds.Tables[0];
        }
        #endregion
    }
}