using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlClient;

using System.Data;

using System.Reflection;

using System.Configuration;

using Cp.Processing;

namespace Cp.DAL

{

    /// <summary>

    /// DBhelper 类

    /// </summary>

    public class DBHelper

    {

        public static string connString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;



        ///// <summary>

        ///// 工具生成的

        ///// </summary>

        ///// <returns></returns>

        //public static SqlConnection CreateConn()

        //{

        //    return new SqlConnection(connString);

        //}



        /// <summary>

        /// 打开数据库连接

        /// </summary>

        /// <param name="conn"></param>

        public static void OpenConn(SqlConnection conn)

        {

            if (conn != null)

            {

                if (conn.State == ConnectionState.Closed)

                {

                    conn.Open();

                }

            }

        }



        /// <summary>

        /// 关闭数据库连接

        /// </summary>

        /// <param name="conn"></param>

        public static void CloseConn(SqlConnection conn)

        {

            if (conn != null)

            {

                if (conn.State == ConnectionState.Open)

                {

                    conn.Close();

                }

            }



        }

        private static SqlConnection connection;

        /// <summary>

        /// 获取数据库连接(Connection)

        /// </summary>

        public static SqlConnection Connection

        {

            get

            {

                string connectionString = connString;

                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>

        /// 执行 T-SQL 语句，并返回所受影响的行数-----AAA

        /// </summary>

        /// <param name="safeSql">Sql语句</param>

        /// <returns>返回受影响的行数</returns>

        public static int ExecuteCommand(string safeSql)

        {



            SqlConnection conn = Connection;

            try

            {

                SqlCommand cmd = new SqlCommand(safeSql, conn);

                //等待时间

                cmd.CommandTimeout = 30;

                int num = cmd.ExecuteNonQuery();

                CloseConn(conn); //关闭数据库连接

                return num;

            }

            catch (Exception e)

            {

                CloseConn(conn);

                return 0;

            }

        }

        /// <summary>

        /// 返回 SqlDataReader 的对象

        /// </summary>

        /// <param name="safeSql"></param>

        /// <returns></returns>

        public static SqlDataReader ExecuteReader(string safeSql)

        {

            SqlConnection conn = Connection;

            try

            {

                SqlCommand cmd = new SqlCommand(safeSql.Trim(), conn);

                //等待时间

                cmd.CommandTimeout = 30;

                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                return reader;

            }

            catch (Exception e)

            {

                CloseConn(conn);

                return null;

            }

        }

        /// <summary>

        /// 增删改

        /// </summary>

        /// <param name="cmdText"></param>

        /// <param name="paramers"></param>

        /// <returns></returns>

        public static Object Execute(string cmdText, params SqlParameter[] paramers)

        {

            SqlConnection conn = Connection;

            try

            {

                SqlCommand cmd = CreateCMD(cmdText, paramers, conn);

                //等待时间

                cmd.CommandTimeout = 30;

                int num = cmd.ExecuteNonQuery();

                CloseConn(conn); //关闭数据库连接

                return num;

            }

            catch (Exception e)

            {

                CloseConn(conn);

                return 0;

            }

        }

        /// <summary>

        /// 查询 的sql 或者 存储过程

        /// </summary>

        /// <param name="cmdText"></param>

        /// <param name="paramers"></param>

        /// <returns></returns>

        public static Object M_Execute(string cmdText, params SqlParameter[] paramers)

        {

            SqlConnection conn = Connection;

            try

            {

                SqlCommand cmd = CreateCMD(cmdText, paramers, conn);

                //等待时间

                cmd.CommandTimeout = 30;

                return cmd.ExecuteReader(CommandBehavior.CloseConnection);

            }

            catch (Exception e)

            {

                CloseConn(conn);

                return new object();

            }



        }

        //上面的调用

        private static SqlCommand CreateCMD(string cmdText, SqlParameter[] paramers, SqlConnection conn)

        {

            SqlCommand cmd = new SqlCommand(cmdText, conn);

            if (paramers != null)

                cmd.Parameters.AddRange(paramers);

            OpenConn(conn);

            return cmd;

        }

    }

}

using (SqlDataReader sdr = (SqlDataReader)DBHelper.M_Execute(cmdText))

            {

                while (sdr.Read())

                {

                    UserType userType = new UserType();

                    userType.id = (Convert.ToInt32(sdr[0]));

                    userType.name = (Convert.ToString(sdr[1]));

                    userTypes.Add(userType);

                    ........

                }

                sdr.Dispose();

            }