SqlHelper 源码

/******************************************************    
 *FileName:  SqlHelper
 *Copyright  (c)  2011-七期提高班
 *Writer: 七期提高班 李守宏
 *Create Date: 2011/6/12 15:24:28
 
 *Rewriter:
 *Rewrite Date:
 *Impact:
 *Main Content（Function Name、parameters、returns）
******************************************************/

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

namespace SqlHelper
{
    /// <summary>
    /// 数据库助手类
    /// </summary>
    public class SqlHelper
    {
        SqlCommand cmd = null;
        SqlConnection con = null;
        SqlDataReader dr = null;
        /// <summary>
        /// 获取数据库连接字符串
        /// </summary>
        /// <returns></returns>
        public SqlConnection GetCon()
        {
            string conStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
            //string conStr = "Data Source=LSH;Initial Catalog=LibraryMis;User ID=sa;Password=123456;";
            con = new SqlConnection();
            con.ConnectionString = conStr;
            //打开数据库
            if (ConnectionState.Closed == con.State)
            {
                con.Open();
            }
            return con;
        }
        /// <summary>
        /// 执行数据库 增 删 该 方法
        /// </summary>
        /// <param name="sqlStr">数据库字符串</param>
        /// <param name="Para">参数数组</param>
        /// <param name="commandType">执行类型</param>
        /// <returns></returns>
        public Boolean ExecuteNonQuery(string sqlStr, SqlParameter[] Para, CommandType commandType)
        {
            cmd = new SqlCommand(sqlStr, GetCon());
            cmd.CommandType = commandType;

            //添加参数
            if (null != Para)
            {
                cmd.Parameters.AddRange(Para);
            }
            //执行语句
            try
            {
                return Convert.ToBoolean(cmd.ExecuteNonQuery());
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                //关闭数据库
                if (null != con)
                {
                    con.Close();
                }
            }
        }
        /// <summary>
        /// 执行数据库查询操作
        /// </summary>
        /// <param name="sqlStr">数据库字符串</param>
        /// <param name="Para">数组</param>
        /// <param name="commandType">执行类型</param>
        /// <returns></returns>
        public DataTable ExecuteQuery(string sqlStr, SqlParameter[] Para, CommandType commandType)
        {
            cmd = new SqlCommand(sqlStr, GetCon());
            cmd.CommandType = commandType;
            DataTable dt = new DataTable();

            //添加参数
            if (null != Para)
            {
                cmd.Parameters.AddRange(Para);
            }
            //执行语句
            try
            {
                dr = cmd.ExecuteReader();
                dt.Load(dr);
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                //关闭数据库
                if (null != con)
                {
                    con.Close();
                }
            }
            return dt;
        }
        /// <summary>
        /// 执行数据库查询 返回一个值
        /// </summary>
        /// <param name="sqlStr">sql语句或者存储过程名</param>
        /// <param name="Para">参数数组</param>
        /// <param name="CommandType">执行类型,sql语句或者存储过程</param>
        /// <returns></returns>
        public string  ExecuteScalar(string sqlStr, SqlParameter[] Para, CommandType CommandType)
        {
            cmd = new SqlCommand(sqlStr, GetCon());
            cmd.CommandType = CommandType;
            //添加参数
            if (Para != null)
            {
                cmd.Parameters.AddRange(Para);
            }

            //执行语句
            try
            {
                return Convert.ToString (cmd.ExecuteScalar());
            }
            catch (Exception)
            {
                return "没有记录";
            }
        }
    }
}