sqlHelper_DataBase

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

namespace RPMO.DAL
{
    public class DataBases
    {
        //获取数据库连接字符串
        static string str = System.Configuration.ConfigurationManager.ConnectionStrings["SharePoint_RPMO_SiteConnectionString"].ToString();
        //static string str = "Data Source=10.12.17.32;Initial Catalog=issuetracker;uid=sa;pwd=hisoft;";
        //创建连接字符串对象

        SqlConnection con = new SqlConnection(str);
        //创建命令对象
        SqlCommand cmd = null;
        //创建连接对象
        SqlDataAdapter da = null;
        DataSet ds = null;
        //创建一个表对象
        DataTable dt = null;
        //SqlParameter[] values = null;
        //string sql = null;

        #region 具体数据查询的方法

        /// <summary>
        /// 调用方法查询一个或多个表的信息，并返回DataSet类型值的方法
        /// </summary>
        /// <param name="sql">sql语句或存储过程名</param>
        /// <param name="re">是否是存储过程</param>
        /// <param name="values">参数</param>
        /// <returns>返回DataSet</returns>
        public DataSet GetDataSet(string sql, bool re, params SqlParameter[] values)
        {
            ds = new DataSet();
            da = new SqlDataAdapter(sql, con);

            if (re)
            {
                //定义命令类型
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
            }//if (re)//判断命令类型

            if (values != null)
            {
                //添加参数
                da.SelectCommand.Parameters.AddRange(values);
            }//if (values != null)//判断参数是否存在

            try
            {
                da.Fill(ds);

            }//try
            catch (SqlException ex)
            {
                //写错误日志

            }//try catch(SqlException ex)捕获数据库异常
            finally
            {

                if (con.State == ConnectionState.Open)
                {
                    //关闭连接
                    con.Close();
                }//if (con.State == ConnectionState.Open)关闭连接

            }//try catch(SqlException ex) finally

            return ds;

        }//public DataSet GetDataSet(string sql, bool re, params SqlParameter[] values)

        /// <summary>
        /// 调用方法查询单个表的信息，并返回DataTable类型值的方法
        /// </summary>
        /// <param name="sql">sql语句或存储过程名</param>
        /// <param name="re">是否是存储过程</param>
        /// <param name="values">参数</param>
        /// <returns>返回DataTable</returns>
        public DataTable GetDataTable(string sql, bool re, params SqlParameter[] values)
        {
            dt = new DataTable();
            da = new SqlDataAdapter(sql, con);

            if (re)
            {
                //定义命令类型
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
            }//if (re)

            if (values != null)
            {
                //添加参数
                da.SelectCommand.Parameters.AddRange(values);
            }//if (values != null)

            try
            {
                da.Fill(dt);
            }//try
            catch (SqlException ex)
            {
                //写错误日志

            }//try catch(SqlException ex)
            finally
            {

                if (con.State == ConnectionState.Open)
                {
                    //关闭连接
                    con.Close();
                }//if (con.State == ConnectionState.Open)

            }//try catch(SqlException ex) finally

            return dt;

        }//public DataTable GetDataTable(string sql, bool re, params SqlParameter[] values)

        /// <summary>
        /// 调用方法查询单个值，返回object类型值的方法
        /// </summary>
        /// <param name="sql">sql语句或存储过程名</param>
        /// <param name="re">是否是存储过程</param>
        /// <param name="values">参数</param>
        /// <returns>返回object</returns>
        public object GetValue(string sql, bool re, params SqlParameter[] values)
        {
            object strReturn = null;
            try
            {
                cmd = new SqlCommand(sql, con);
                if (re)
                {
                    //定义命令类型
                    cmd.CommandType = CommandType.StoredProcedure;
                }//if (re)

                if (values != null)
                {
                    //添加参数
                    cmd.Parameters.AddRange(values);
                }//if (values != null)

                if (con.State == ConnectionState.Closed)
                {
                    //打开连接
                    con.Open();
                }//if(con.State == ConnectionState.Closed)
                //读取一行一列的数据
                strReturn = cmd.ExecuteScalar();
                //如果返回值是数据库空值类型
                if (strReturn == DBNull.Value)
                {
                    strReturn = null;
                }
            }//try
            catch (SqlException ex)
            {
                //写错误日志

            }//try catch(SqlException ex)
            finally
            {

                if (con.State == ConnectionState.Open)
                {
                    //关闭连接
                    con.Close();
                }//if (con.State == ConnectionState.Open)

            }//try catch(SqlException ex) finally

            return strReturn;

        }//public object GetValue(string sql, bool re, params SqlParameter[] values)

        /// <summary>
        /// 调用方法更新数据库，并返回受影响行数
        /// </summary>
        /// <param name="sql">sql语句或存储过程名</param>
        /// <param name="re">是否是存储过程</param>
        /// <param name="values">参数</param>
        /// <returns>返回受影响行数</returns>
        public int ProExecuteCommand(string sql, bool re, params SqlParameter[] values)
        {
            int count = 0;
            cmd = new SqlCommand(sql, con);

            if (re)
            {
                //定义命令类型
                cmd.CommandType = CommandType.StoredProcedure;
            }//if(re)

            if (values != null)
            {
                //向命令对象的参数集合里添加参数
                cmd.Parameters.AddRange(values);
            }//if(values != null)

            try
            {

                if (con.State == ConnectionState.Closed)
                {
                    //打开连接
                    con.Open();
                }//if(con.State == ConnectionState.Closed)

                count = cmd.ExecuteNonQuery();//  执行命令，返回受影响行数
            }//try
            catch (SqlException ex)
            {
                //写错误日志

            }//try catch(SqlException ex)
            finally
            {

                if (con.State == ConnectionState.Open)
                {
                    //关闭连接
                    con.Close();
                }//if (con.State == ConnectionState.Open)

            }//try catch(SqlException ex) finally

            return count;

        }//public int ProExecuteCommand(string sql, bool re, params SqlParameter[] values)

        #endregion
    }
}