CShare_Common/Helper/SqlServer/SqlHelper.cs

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

namespace Common.Helper.SqlServer
{
    public class SqlHelper
    {
        public static readonly string ConnectString = "";
        private SqlConnection _con;
        private SqlCommand _cmd;

        private SqlDataAdapter _sda;
        private SqlDataReader _sdr;
        private DataSet _ds;
        private DataView _dv;

        public static SqlConnection GetConnection()//定义成静态的，很重要！
        {
            return new SqlConnection(ConnectString);
        }
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void OpenDataBase()
        {
            _con = new SqlConnection(ConnectString);
            _con.Open();
        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void CloseDataBase()
        {

            _con.Close();
            _con.Dispose();

        }

        /// <summary>
        /// 返回DataSet数据集
        /// </summary>
        /// <param name="sqlStr">数据库查询字符串</param>
        /// <param name="tableName"></param>
        /// <returns>DataSet</returns>
        public DataSet GetDs(string sqlStr, string tableName)
        {
            OpenDataBase();
            _sda = new SqlDataAdapter(sqlStr, _con);
            _ds = new DataSet();
            _sda.Fill(_ds, tableName);
            CloseDataBase();
            return _ds;
        }
        public DataView GetDv(string sqlStr)
        {
            OpenDataBase();
            _sda = new SqlDataAdapter(sqlStr, _con);
            _ds = new DataSet();
            _sda.Fill(_ds);
            _dv = _ds.Tables[0].DefaultView;
            CloseDataBase();
            return _dv;

        }
        /// <summary>
        /// 返回DataReader对象
        /// </summary>
        /// <param name="sqlString">查询字符串</param>
        /// <returns>返回值</returns>
        public SqlDataReader GetDataReader(string sqlString)
        {
            OpenDataBase();
            _cmd = new SqlCommand(sqlString, _con);
            _sdr = _cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

            return _sdr;


        }
        public DataSet DataSet(string sql)
        {
            SqlConnection con = new SqlConnection(ConnectString);
            SqlCommand cmd = new SqlCommand(sql, con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = null;
            try
            {
                con.Open();
                ds = new DataSet();
                da.Fill(ds);

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return ds;
        }

        public static DataTable DataTable(string sql)
        {
            var con = new SqlConnection(ConnectString);
            var cmd = new SqlCommand(sql, con);
            var da = new SqlDataAdapter(cmd);
            DataTable ds = null;
            try
            {
                con.Open();
                ds = new DataTable();
                da.Fill(ds);

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return ds;
        }

        public static DataTable DataTable(string sql,string connectString)
        {
            var con = new SqlConnection(connectString);
            var cmd = new SqlCommand(sql, con);
            var da = new SqlDataAdapter(cmd);
            DataTable ds = null;
            try
            {
                con.Open();
                ds = new DataTable();
                da.Fill(ds);

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return ds;
        }

        /// <summary>
        /// 可以传入连接字符串 返回T类型对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="connectString"></param>
        /// <returns></returns>
        public static List<T> Entity<T>(string sql,string connectString)
        {
            return ConvertDataTableToEntity<T>(DataTable(sql,connectString));
        }
        /// <summary>
        /// 根据sql语句返回 T 类型对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>

        public static List<T> Entity<T>(string sql)
        {
            return ConvertDataTableToEntity<T>(DataTable(sql));
        }
        /// <summary>
        /// 根据sql语句直接返回泛型对象
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static List<ExpandoObject> Entity(string sql)
        {
            return ConvertDataTableToEntity(DataTable(sql));
        }
        /// <summary>
        /// 执行Sql语句方法没有返回值
        /// </summary>
        /// <param name="sqlStr">传入的查询参数</param>
        public void RunSql(string sqlStr)
        {
            OpenDataBase();
            _cmd = new SqlCommand(sqlStr, _con);
            _cmd.ExecuteNonQuery();
            CloseDataBase();
        }
        /// <summary>
        /// 返回查询结果的首行首列
        /// </summary>
        /// <param name="sqlStr">查询字符串</param>
        /// <returns>返回结果</returns>
        public string ReturnSql(string sqlStr)
        {
            OpenDataBase();
            string returnSql;
            try
            {
                _cmd = new SqlCommand(sqlStr, _con);
                returnSql = _cmd.ExecuteScalar().ToString();
            }
            catch(Exception ex)
            {
                throw ex;
            }

            CloseDataBase();
            return returnSql;
        }

        /// <summary>
        /// 将DataTable转成 T 实体对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static List<T> ConvertDataTableToEntity<T>(DataTable dt)
        {
            return (from DataRow row in dt.Rows select GetItem<T>(row)).ToList();
        }
        /// <summary>
        /// 组装实体的方法
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dr"></param>
        /// <returns></returns>
        private static T GetItem<T>(DataRow dr)
        {
            var temp = typeof(T);
            var obj = Activator.CreateInstance<T>();

            foreach (DataColumn column in dr.Table.Columns)
            {
                foreach (var pro in temp.GetProperties())
                {
                    if (string.Equals(pro.Name, column.ColumnName.Replace("_", string.Empty), StringComparison.CurrentCultureIgnoreCase))
                    {
                        if (dr[column.ColumnName] is DBNull)
                        {
                            pro.SetValue(obj, default, null);
                            continue;
                        }
                        pro.SetValue(obj, dr[column.ColumnName], null);
                        if (dr[column.ColumnName] is string)
                            pro.SetValue(obj, Convert.ToString(dr[column.ColumnName]).Trim(), null);
                    }
                    else
                        continue;
                }
            }
            return obj;
        }

        /// <summary>
        /// 将DataTable转成 泛型实体对象
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static List<ExpandoObject> ConvertDataTableToEntity(DataTable dt)
        {
            return (from DataRow row in dt.Rows select GetItem(row)).ToList();
        }

        /// <summary>
        /// 不需要定义实体，直接返回泛型实体
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>

        public static ExpandoObject GetItem(DataRow dr)
        {
            dynamic dynamicEntity = new ExpandoObject();
            foreach (DataColumn column in dr.Table.Columns)
            {
                (dynamicEntity as IDictionary<string, object>).Add(column.ColumnName, (dr[column.ColumnName] is string)? dr[column.ColumnName].ToString().Trim(): dr[column.ColumnName]);
            }

            return dynamicEntity;
        }
    }
}