SqlHelper

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Reflection;
using System.Runtime.Serialization.Formatters.Binary;
using System.Xml;


namespace TestLogin
{


    /// <summary>
    /// 数据库访问抽象基类  (基于MS-SQLServer2005或以上版本)
    /// 此类为抽象类 abstract ，不允许实例化，在应用时直接调用即可
    /// Autho       ：zhouzhilong
    /// version     ：1.2
    /// LastEditTime：2010-10-09
    /// </summary>
    public abstract class SqlHelper
    {
        //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.        
        public static readonly string LocalSqlServer = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString();
        /// <summary>
        ///执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string connectionString, string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw new Exception(e.Message);
                    }
                }
            }
        }
    }
}






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


namespace TSMS.Common
{
    /// <summary>
    /// 负责对于ADO.NET的封装。
    /// </summary>
    public class SqlHelper : AbstractSqlHelper
    {
        /// <summary>
        /// 执行非查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdtype"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public override bool ExecuteNonQurey(string sql, CommandType cmdtype, params SqlParameter[] parameters)
        {
            SqlCommand cmd = GetCommand(sql, cmdtype, parameters);
            int reuslt =cmd.ExecuteNonQuery();
            CloseConnection();
            return reuslt > 0 ? true : false;
        }
        /// <summary>
        /// 获取数据表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdtype"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public override DataTable GetDataTable(string sql, CommandType cmdtype, params SqlParameter[] parameters)
        {
            SqlCommand cmd = GetCommand(sql, cmdtype, parameters);     
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataSet dataset = new DataSet();
            sda.Fill(dataset);
            return dataset.Tables[0];              
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdtype"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public override object GetSingleData(string sql, CommandType cmdtype, params SqlParameter[] parameters)
        {
            SqlCommand cmd = GetCommand(sql, cmdtype,parameters);
            object o = cmd.ExecuteScalar();
            CloseConnection();
            return o;
        }
        /// <summary>
        /// 获取数据流
        /// </summary>
        /// <param name="sql"></param>      
        public override SqlDataReader GetDataReader(string sql,CommandType cmdtype, params SqlParameter[] parameters)
        {  
            SqlCommand cmd = GetCommand(sql,cmdtype, parameters);
            SqlDataReader sdr = cmd.ExecuteReader();
            CloseConnection();
            return sdr;
        }
        /// <summary>
        ///获取缓存中数据表
        /// </summary>
        public override DataSet GetDataSet(string sql, CommandType cmdtype, params SqlParameter[] parameters)
        {
           
            SqlCommand cmd = GetCommand(sql,cmdtype ,parameters);          
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataSet dataset = new DataSet();
            sda.Fill(dataset);
            return dataset;  
        }
        public override DataSet GetDataSet(string sql, CommandType cmdtype, List<SqlParameter> list)
        {
            SqlCommand cmd = GetCommand(sql, cmdtype,list);
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            return ds;
        }
    }
}