kjh-m-55128/Services/BaseServices.cs

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

using Common;
using Interface;
using Models;
using System.Data.SqlClient;
using System.Linq.Expressions;

namespace Services
{
    /// <summary>
    /// 基础服务
    /// </summary>
    public class BaseServices : BaseInterface
    {
        /// <summary>
        /// 装箱单个数据对象
        /// </summary>
        /// <typeparam name="T">装箱对象</typeparam>
        /// <param name="dr">装箱数据行</param>
        /// <returns></returns>
        protected T LoadData<T>(DataRow dr)
        {
            if (dr == null) return default(T);
            var t = typeof(T);
            var obj = Activator.CreateInstance(t);
            var properts = t.GetProperties();
            foreach (var pi in properts)
            {
                if (!dr.Table.Columns.Contains(pi.Name)) continue;
                pi.SetValue(obj, CommonHelper.ChangeType(dr[pi.Name], pi.PropertyType), null);
            }
            return (T)obj;
        }

        /// <summary>
        /// 装箱列表数据对象
        /// </summary>
        /// <typeparam name="T">装箱对象</typeparam>
        /// <param name="dt">装箱数据来源表格</param>
        /// <returns></returns>
        protected List<T> LoadDataList<T>(DataTable dt)
        {
            List<T> result = new List<T>();
            var t = typeof(T);
            var properts = t.GetProperties();
            object obj;
            foreach (DataRow dr in dt.Rows)
            {
                obj = Activator.CreateInstance(t);
                foreach (var pi in properts)
                {
                    if (!dt.Columns.Contains(pi.Name)) continue;
                    pi.SetValue(obj, CommonHelper.ChangeType(dr[pi.Name], pi.PropertyType), null);
                }
                result.Add((T)obj);
            }
            return result;
        }
        protected List<string> Loadstringist(DataTable dt)
        {
            List<string> result = new List<string>();

            foreach (DataRow dr in dt.Rows)
            {
                result.Add(dr[0].ToString());
            }
            return result;
        }

        public Boolean Add<T>(T obj)
        {
            if (obj == null)
            {
                return false;
            }
            string tablename = EnumHelper.GetZXTableName<T>();
            var tbnameary = tablename.Split('.');
            List<string> fildlist = new List<string>();
            List<string> paramsql = new List<string>();
            List<SqlParameter> paramlist = new List<SqlParameter>();
            foreach (PropertyInfo pi in obj.GetType().GetProperties())
            {

                object value1 = pi.GetValue(obj, null);
                var attribute = pi.GetCustomAttributes(typeof(IsInsert), false).FirstOrDefault();
                if (attribute != null)
                {
                    if (((IsInsert)attribute).FildName)
                    {
                        fildlist.Add(pi.Name);
                        paramsql.Add("@" + pi.Name);
                        paramlist.Add(new SqlParameter("@" + pi.Name, value1));
                    }
                }
            }
            string insertsql = string.Format(AddItemSql, tbnameary[1], string.Join(",", fildlist), string.Join(",", paramsql));
            var conn = SqlHelper.GetConnection(tbnameary[0]);
            var result = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, insertsql, paramlist.ToArray());
            return result > 0;
        }

        public bool CBAdd(string sql, SqlParameter[] para)
        {
            var conn = SqlHelper.GetConnection("CBDataBase");
            var result = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, para);
            return result > 0;
        }

        public Boolean DeleteItemBykey<T>(object key)
        {
            string tablename = EnumHelper.GetZXTableName<T>();
            var tbnameary = tablename.Split('.');
            List<SqlParameter> paramlist = new List<SqlParameter>();
            var deleteSql = string.Empty;
            foreach (PropertyInfo pi in typeof(T).GetProperties())
            {
                var attribute = pi.GetCustomAttributes(typeof(Key), false).FirstOrDefault();
                if (attribute != null)
                {
                    if (((Key)attribute).KEY)
                    {
                        paramlist.Add(new SqlParameter("@" + pi.Name, key));
                        deleteSql = string.Format(DeleteItemSql, tbnameary[1], pi.Name, "@" + pi.Name);
                    }
                }
            }
            var conn = SqlHelper.GetConnection(tbnameary[0]);
            var result = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, deleteSql, paramlist.ToArray());
            return result > 0;
        }


        public T QueryItembyKey<T>(object key)
        {
            string tablename = EnumHelper.GetZXTableName<T>();
            var tbnameary = tablename.Split('.');
            List<SqlParameter> paramlist = new List<SqlParameter>();
            var deleteSql = string.Empty;
            foreach (PropertyInfo pi in typeof(T).GetProperties())
            {
                var attribute = pi.GetCustomAttributes(typeof(Key), false).FirstOrDefault();
                if (attribute != null)
                {
                    if (((Key)attribute).KEY)
                    {
                        paramlist.Add(new SqlParameter("@" + pi.Name, key));
                        deleteSql = string.Format(QueryItemSql, tbnameary[1], pi.Name, "@" + pi.Name);
                    }
                }
            }
            var conn = SqlHelper.GetConnection(tbnameary[0]);
            var ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, deleteSql, paramlist.ToArray());
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                var result = LoadData<T>(ds.Tables[0].Rows[0]);
                return result;
            }
            else
            {
                return default(T);
            }
        }


        public bool Update<T>(T data)
        {
            if (data == null)
            {
                return false;
            }
            string tablename = EnumHelper.GetZXTableName<T>();
            var tbnameary = tablename.Split('.');
            List<string> paramsql = new List<string>();
            var key = new object();
            string keyname = string.Empty;
            List<SqlParameter> paramlist = new List<SqlParameter>();
            foreach (PropertyInfo pi in data.GetType().GetProperties())
            {

                object value1 = pi.GetValue(data, null);
                var attribute = pi.GetCustomAttributes(typeof(IsInsert), false).FirstOrDefault();
                if (attribute != null)
                {
                    var attributekey = pi.GetCustomAttributes(typeof(Key), false).FirstOrDefault();
                    if (attributekey != null)
                    {
                        if (((Key)attributekey).KEY)
                        {
                            key = value1;
                            keyname = pi.Name;
                        }
                    }

                    if (((IsInsert)attribute).FildName)
                    {
                        paramsql.Add(string.Format(" {0} = @{0}", pi.Name));
                        paramlist.Add(new SqlParameter("@" + pi.Name, value1));
                    }
                }
            }
            string insertsql = string.Format(UpdateItemsql, tbnameary[1], string.Join(" ,", paramsql), keyname, key.GetType() == typeof(int) ? key : "'" + key + "'");
            var conn = SqlHelper.GetConnection(tbnameary[0]);
            var result = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, insertsql, paramlist.ToArray());
            return result > 0;
        }

        public List<T> GetList<T>(int page, int rows, string order, List<EExpression> expression, bool isDesc = true)
        {
            int startindex = (page - 1) * rows;
            int endindex = startindex + rows + 1;
            string tablename = EnumHelper.GetZXTableName<T>();
            var tbnameary = tablename.Split('.');
            List<string> fildlist = new List<string>();
            List<string> expressionlist = new List<string>();
            string keyname = string.Empty;
            foreach (PropertyInfo pi in typeof(T).GetProperties())
            {

                fildlist.Add(pi.Name);
                var attributekey = pi.GetCustomAttributes(typeof(Key), false).FirstOrDefault();
                if (attributekey != null)
                {
                    if (((Key)attributekey).KEY)
                    {
                        keyname = pi.Name;
                    }
                }
            }
            for (int i = 0; i < expression.Count; i++)
            {
                expressionlist.Add(@" AND " + expression[i].GetSql());
            }
            //"AND OpenCode1>3 AND IsChecked=1"
            string pageListSql = string.Format(QueryListPageSql, string.Join(",", fildlist), order == null ? keyname : order, isDesc ? "desc" : "asc", tbnameary[1], string.Join("", expressionlist), startindex, endindex);
            var conn = SqlHelper.GetConnection(tbnameary[0]);
            var ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, pageListSql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                var result = LoadDataList<T>(ds.Tables[0]);
                return result;
            }
            else
            {
                return new List<T>();
            }
        }
        public List<T> GetList<T>(string order, List<EExpression> expression, bool isDesc = true)
        {
            string tablename = EnumHelper.GetZXTableName<T>();
            var tbnameary = tablename.Split('.');
            string keyname = string.Empty;
            List<string> expressionlist = new List<string>();
            foreach (PropertyInfo pi in typeof(T).GetProperties())
            {
                var attributekey = pi.GetCustomAttributes(typeof(Key), false).FirstOrDefault();
                if (attributekey != null)
                {
                    if (((Key)attributekey).KEY)
                    {
                        keyname = pi.Name;
                    }
                }
            }
            for (int i = 0; i < expression.Count; i++)
            {
                expressionlist.Add(@" AND " + expression[i].GetSql());
            }
            string listSql = string.Format(QueryListSql, tbnameary[1], string.Join("", expressionlist), order == null ? keyname : order, isDesc ? "desc" : "asc");
            var conn = SqlHelper.GetConnection(tbnameary[0]);
            var ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, listSql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                var result = LoadDataList<T>(ds.Tables[0]);
                return result;
            }
            else
            {
                return new List<T>();
            }
        }


        public int GetCount<T>()
        {
            string tablename = EnumHelper.GetZXTableName<T>();
            var tbnameary = tablename.Split('.');
            string insertsql = string.Format(SqlCount, tbnameary[1]);
            var conn = SqlHelper.GetConnection(tbnameary[0]);
            var result = SqlHelper.ExecuteScalar(conn, CommandType.Text, insertsql);
            return (int)result;
        }
        public int GetPageListCount<T>(List<EExpression> expression)
        {
            string tablename = EnumHelper.GetZXTableName<T>();
            var tbnameary = tablename.Split('.');
            string keyname = string.Empty;
            List<string> expressionlist = new List<string>();
            foreach (PropertyInfo pi in typeof(T).GetProperties())
            {
                var attributekey = pi.GetCustomAttributes(typeof(Key), false).FirstOrDefault();
                if (attributekey != null)
                {
                    if (((Key)attributekey).KEY)
                    {
                        keyname = pi.Name;
                    }
                }
            }
            for (int i = 0; i < expression.Count; i++)
            {
                expressionlist.Add(@" AND " + expression[i].GetSql());
            }
            string listSql = string.Format(PageListCount, keyname, tbnameary[1], string.Join("", expressionlist));
            var conn = SqlHelper.GetConnection(tbnameary[0]);
            var ds = SqlHelper.ExecuteScalar(conn, CommandType.Text, listSql);
            return (int)ds;
        }

        private static string AddItemSql = @"INSERT INTO {0} ({1}) VALUES ({2})";

        private static string DeleteItemSql = @"DELETE FROM {0} WHERE {1} = {2}";
        private static string QueryItemSql = @"SELECT * FROM {0} WHERE {1} = {2}";
        private static string UpdateItemsql = @"UPDATE {0} SET {1} WHERE {2} = {3}";
        private static string QueryListPageSql = @"SELECT {0} FROM
                                (SELECT ROW_NUMBER()OVER(ORDER BY {1} {2})ROWNUMBER, * FROM {3} WHERE 1=1 {4} )A
                                 WHERE ROWNUMBER>{5} AND ROWNUMBER<{6}";
        private static string QueryListSql = @"SELECT * FROM   {0} WHERE   1=1  {1} ORDER BY {2} {3}";

        private static string SqlCount = @"SELECT COUNT(*)
                                                FROM {0}";

        private static string PageListCount = @"SELECT Count({0}) FROM  {1} WHERE 1=1 {2}";



    }
}