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

namespace MyCommunityDAL
{
    public static class GetConnection
    {
        private static SqlConnection _connection;

        /// <summary>
        /// 获取数据库联接桥
        /// </summary>
        public static SqlConnection connection {
            get {
                string connectionString = "Data Source=.;Initial Catalog=mycommunity;Integrated Security=True";
                if (_connection == null) {
                    _connection = new SqlConnection(connectionString);
                    _connection.Open();
                } if (_connection.State == ConnectionState.Closed) {
                    _connection.Open();
                } if (_connection.State == ConnectionState.Broken) {
                    _connection.Close();
                    _connection.Open();
                }
                return _connection;
            }
        }

        /// <summary>
        /// 参数化sql语句，执行非查询
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public static int NotExecute(string strSql, SqlParameter[] pars) {
            SqlCommand comm = new SqlCommand(strSql,connection);
            comm.Parameters.AddRange(pars);
            int row = comm.ExecuteNonQuery();
            return row;
        }

        /// <summary>
        /// 参数化sql语句，执行查询，返回一个DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string strSql, SqlParameter[] pars) {
            SqlCommand comm = new SqlCommand(strSql, connection);
            DataTable dt = new DataTable();
            comm.Parameters.AddRange(pars);
            SqlDataAdapter sda = new SqlDataAdapter(comm);
            sda.Fill(dt);
            return dt;
        }

        /// <summary>
        /// 参数化sql语句，执行查询，返回一个DataReader
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="prams"></param>
        /// <returns></returns>
        public static SqlDataReader GetDataReader(string strSql, SqlParameter[] prams)
        {
            SqlCommand comm = new SqlCommand(strSql, connection);
            comm.Parameters.AddRange(prams);
            return comm.ExecuteReader();
        }

       /// <summary>
        /// 执行有参数的存储过程的方法,返回一个DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public static DataTable GetDataTableByProc(string storeName, SqlParameter[] pars)
        {
            SqlCommand comm = new SqlCommand(storeName, Connection);
            DataTable dt = new DataTable();
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddRange(pars);
            SqlDataAdapter sda = new SqlDataAdapter(comm);
            sda.Fill(dt);
            return dt;
        }
       

        // 执行无参数的存储过程的方法,返回类型为DataTable
        public static DataTable GetDataTableByProc(string storeName)
        {
            SqlCommand comm = new SqlCommand(storeName, Connection);
            DataTable dt = new DataTable();
            comm.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter sda = new SqlDataAdapter(comm);
            sda.Fill(dt);
            return dt;
        }

    }
}