FCS/FCS.Services/Service/DTOpenCodeServices.cs

using FCS.Common;
using FCS.Interface;
using FCS.Models;
using FCS.Models.Entity;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

namespace FCS.Services
{
    /// <summary>
    /// 数据服务
    /// </summary>
    public class DTOpenCodeServices : BaseServices, IDTOpenCode
    {
        #region   足球相关
        /// <summary>
        /// 添加区域表信息
        /// </summary>
        /// <param name="lottery"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddArea(FCSLottery lottery, F_Area model)
        {
            var TableName = EnumHelper.GetFCSLotteryTableName(lottery);
            var sqlString = string.Format(AddAreaSql, TableName);
            var param = new SqlParameter[]{
                new SqlParameter("@Name",model.Name),
                new SqlParameter("@ParentId",model.ParentId),
                new SqlParameter("@LogoImage",model.LogoImage),
                new SqlParameter("@SortCode",model.SortCode),
                new SqlParameter("@ID",model.Id),
                new SqlParameter ("@Remark",model.Remark),
                new SqlParameter ("@CreateDateTime",model.CreateDateTime),
            };
            var result = SqlHelper.ExecuteNonQuery(CommandType.Text, sqlString, param);
            return result > 0;
        }

        public F_EuropeanEvents GetAreaId(string Name)
        {
            var sqlString = string.Format(LastItemSql, Name);
            var ds = SqlHelper.ExecuteDataset(CommandType.Text, sqlString);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                var result = LoadData<F_EuropeanEvents>(ds.Tables[0].Rows[0]);
                return result;
            }
            return null;
        }

        /// <summary>
        /// 添加赛事数据
        /// </summary>
        /// <param name="lottery"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddEvents(FCSLottery lottery, F_Events model)
        {
            var TableName = EnumHelper.GetFCSLotteryTableName(lottery);
            var sqlString = string.Format(AddEventsSql, TableName);
            var param = new SqlParameter[]{
                new SqlParameter("@Name",model.Name),
                new SqlParameter("@AreaId",model.AreaId),
                new SqlParameter("@LogoImage",model.LogoImage),
                new SqlParameter("@ID",model.Id),
                new SqlParameter ("@Remark",model.Remark),
                new SqlParameter ("@EventType",model.EventType),
            };
            var result = SqlHelper.ExecuteNonQuery(CommandType.Text, sqlString, param);
            return result > 0;
        }

        /// <summary>
        /// 获取枚举list
        /// </summary>
        /// <returns></returns>
        public List<DataItemDetail> GetDataItem(DataItemDetailEnum dateItem)
        {
            var sqlString = string.Format(SelectDataItemDetailSql, dateItem.ToString());

            var ds = SqlHelper.ExecuteDataset(CommandType.Text, sqlString);
            if (ds != null && ds.Tables.Count > 0)
            {
                var result = LoadDataList<DataItemDetail>(ds.Tables[0]);
                return result;
            }
            return null;
        }

        /// <summary>
        /// 获取球队列表
        /// </summary>
        /// <returns></returns>
        public List<F_Team> GetTeamList()
        {
            var sqlString = string.Format(GetTeamListSql);

            var ds = SqlHelper.ExecuteDataset(CommandType.Text, sqlString);
            if (ds != null && ds.Tables.Count > 0)
            {
                var result = LoadDataList<F_Team>(ds.Tables[0]);
                return result;
            }
            return null;
        }

        /// <summary>
        /// 获取国家列表
        /// </summary>
        /// <returns></returns>
        public List<F_Area> GetContryList()
        {
            var sqlString = string.Format(GetContryListSql);

            var ds = SqlHelper.ExecuteDataset(CommandType.Text, sqlString);
            if (ds != null && ds.Tables.Count > 0)
            {
                var result = LoadDataList<F_Area>(ds.Tables[0]);
                return result;
            }
            return null;
        }

        /// <summary>
        /// 获取球员列表
        /// </summary>
        /// <returns></returns>
        public List<F_Players> GetPlayerList()
        {
            var sqlString = string.Format(GetPlayerListSql);

            var ds = SqlHelper.ExecuteDataset(CommandType.Text, sqlString);
            if (ds != null && ds.Tables.Count > 0)
            {
                var result = LoadDataList<F_Players>(ds.Tables[0]);
                return result;
            }
            return null;
        }

        /// <summary>
        /// 添加球队信息
        /// </summary>
        /// <param name="lottery"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddTeam(FCSLottery lottery, F_Team model)
        {
            var TableName = EnumHelper.GetFCSLotteryTableName(lottery);
            var sqlString = string.Format(AddTeamSql, TableName);
            var param = new SqlParameter[]{
                   new SqlParameter("@ID",model.Id),
                new SqlParameter("@Name",model.Name),
                new SqlParameter("@CityName",model.CityName),
                new SqlParameter("@Describe",model.Describe),
                new SqlParameter("@Venue",model.Venue),
                new SqlParameter ("@Coach",model.Coach),
                new SqlParameter ("@Address",model.Address),
                new SqlParameter ("@SetUpDateTime",model.SetUpDateTime),
                new SqlParameter ("@Advantage",model.Advantage),
                new SqlParameter ("@Style",model.Style),
                new SqlParameter ("@Weaknesses",model.Weaknesses),
                new SqlParameter ("@Website",model.Website),
                new SqlParameter ("@LogoImage",model.LogoImage),
                new SqlParameter ("@Remark",model.Remark),
                 new SqlParameter ("@EventId",model.EventId),
                 new SqlParameter ("@CreateDateTime",model.CreateDateTime),
            };
            var result = SqlHelper.ExecuteNonQuery(CommandType.Text, sqlString, param);
            return result > 0;
        }

        /// <summary>
        /// 添加球员信息
        /// </summary>
        /// <param name="lottery"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddPlayers(FCSLottery lottery, F_Players model)
        {
            var TableName = EnumHelper.GetFCSLotteryTableName(lottery);
            var sqlString = string.Format(AddPlayersSql, TableName);
            var param = new SqlParameter[]{
                new SqlParameter ("@ID",model.Id),
                new SqlParameter ("@ChineseName",model.ChineseName),
                new SqlParameter ("@EnglishName",model.EnglishName),
                new SqlParameter ("@Nationality",model.Nationality),
                new SqlParameter ("@Weight",model.Weight),
                new SqlParameter ("@Stature",model.Stature),
                new SqlParameter ("@Birthday",model.Birthday),
                new SqlParameter ("@GoodFoot",model.GoodFoot),
                new SqlParameter ("@Honor",model.Honor),
                new SqlParameter ("@Describe",model.Describe),
                new SqlParameter ("@LogoImage",model.LogoImage),
                new SqlParameter ("@FromAddress",model.FromAddress),
                new SqlParameter ("@Celebrity",model.Celebrity),
                new SqlParameter ("@Remark",model.Remark),
            };
            var result = SqlHelper.ExecuteNonQuery(CommandType.Text, sqlString, param);
            return result > 0;
        }

        /// <summary>
        /// 添加球队球员关系
        /// </summary>
        /// <param name="lottery"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddRelation(FCSLottery lottery, F_Relation model)
        {
            var TableName = EnumHelper.GetFCSLotteryTableName(lottery);
            var sqlString = string.Format(AddRelationsSql, TableName);
            var param = new SqlParameter[]{
                new SqlParameter ("@ID",model.Id),
                new SqlParameter ("@TeamId",model.TeamId),
                new SqlParameter ("@PlayerId",model.PlayerId),
                new SqlParameter ("@Position",model.Position),
                new SqlParameter ("@PoloShirt",model.PoloShirt),
            };
            var result = SqlHelper.ExecuteNonQuery(CommandType.Text, sqlString, param);
            return result > 0;
        }

        /// <summary>
        /// 添加公司信息
        /// </summary>
        /// <param name="lottery"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddBankerCompany(FCSLottery lottery, BankerCompany model)
        {
            var TableName = EnumHelper.GetFCSLotteryTableName(lottery);
            var sqlString = string.Format(AddBankerCompanysql, TableName);
            var param = new SqlParameter[]{
                   new SqlParameter("@Id",model.Id),
                new SqlParameter("@Name",model.Name),
                new SqlParameter("@CompanyType",model.CompanyType),
                new SqlParameter("@CreateDateTime",model.CreateDateTime),
                new SqlParameter("@Remark",model.Remark),
            };
            var result = SqlHelper.ExecuteNonQuery(CommandType.Text, sqlString, param);
            return result > 0;
        }
        /// <summary>
        /// 添加公司关系信息
        /// </summary>
        /// <param name="lottery"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddBankerCompanyRelation(FCSLottery lottery, BankerCompanyRelation model)
        {
            var TableName = EnumHelper.GetFCSLotteryTableName(lottery);
            var sqlString = string.Format(AddBankerCompanyRelationsql, TableName);
            var param = new SqlParameter[]{
                   new SqlParameter("@Id",model.Id),
                new SqlParameter("@BankerCompanyId",model.BankerCompanyId),
                new SqlParameter("@BankerCompanyType",model.BankerCompanyType),
                new SqlParameter("@CreateDateTime",model.CreateDateTime),
                new SqlParameter("@Remark",model.Remark),
            };
            var result = SqlHelper.ExecuteNonQuery(CommandType.Text, sqlString, param);
            return result > 0;
        }
        /// <summary>
        /// 添加固化分析信息
        /// </summary>
        /// <param name="lottery"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddCuringAnalysis(FCSLottery lottery, F_CuringAnalysis model)
        {
            var TableName = EnumHelper.GetFCSLotteryTableName(lottery);
            var sqlString = string.Format(AddCuringAnalysissql, TableName);
            var param = new SqlParameter[]{
                   new SqlParameter("@Id",model.Id),
                new SqlParameter("@ChangeDateTime",model.ChangeDateTime),
                new SqlParameter("@CreateDateTime",model.CreateDateTime),
                new SqlParameter("@CuringAnalysisType",model.CuringAnalysisType),
                new SqlParameter("@CuringType",model.CuringType),
                new SqlParameter("@GameId",model.GameId),
                new SqlParameter("@Init_Disc",model.Init_Disc),
                new SqlParameter("@Init_FOdd",model.Init_FOdd),
                new SqlParameter("@Init_POdd",model.Init_POdd),
                new SqlParameter("@Init_SOdd",model.Init_SOdd),
                new SqlParameter("@Kelly_FOdd",model.Kelly_FOdd),
                new SqlParameter("@Kelly_POdd",model.Kelly_POdd),
                new SqlParameter("@Kelly_SOdd",model.Kelly_SOdd),
                new SqlParameter("@Loss_Odd",model.Loss_Odd),
                new SqlParameter("@Newest_Disc",model.Newest_Disc),
                new SqlParameter("@Newest_FOdd",model.Newest_FOdd),
                new SqlParameter("@Newest_POdd",model.Newest_POdd),
                new SqlParameter("@Newest_SOdd",model.Newest_SOdd),
                new SqlParameter("@Probability_FOdd",model.Probability_FOdd),
                new SqlParameter("@Probability_POdd",model.Probability_POdd),
                new SqlParameter("@Probability_SOdd",model.Probability_SOdd),
                new SqlParameter("@Remark",model.Remark),
            };
            var result = SqlHelper.ExecuteNonQuery(CommandType.Text, sqlString, param);
            return result > 0;
        }
        /// <summary>
        /// 添加固化分析信息
        /// </summary>
        /// <param name="lottery"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddOddsCuring(FCSLottery lottery, F_OddsCuring model)
        {
            var TableName = EnumHelper.GetFCSLotteryTableName(lottery);
            var sqlString = string.Format("", TableName);
            var param = new SqlParameter[]{
                   new SqlParameter("@Id",model.Id),
                new SqlParameter("@ChangeDateTime",model.ChangeDateTime),
                new SqlParameter("@CreateDateTime",model.CreateDateTime),
                new SqlParameter("@CuringAnalysisType",model.BankerCompanyId),
                new SqlParameter("@CuringType",model.BankerCompanyName),
                new SqlParameter("@GameId",model.GameId),
                new SqlParameter("@Init_Disc",model.HomeTeamId),
                new SqlParameter("@Init_FOdd",model.HomeTeamName),
                new SqlParameter("@Init_POdd",model.Init_FOdd),
                new SqlParameter("@Init_SOdd",model.Init_POdd),
                new SqlParameter("@Kelly_FOdd",model.Init_SOdd),
                new SqlParameter("@Kelly_POdd",model.Kelly_FOdd),
                new SqlParameter("@Kelly_SOdd",model.Kelly_POdd),
                new SqlParameter("@Loss_Odd",model.Kelly_SOdd),
                new SqlParameter("@Newest_Disc",model.Loss_Odd),
                new SqlParameter("@Newest_FOdd",model.Newest_FOdd),
                new SqlParameter("@Newest_POdd",model.Newest_POdd),
                new SqlParameter("@Newest_SOdd",model.Newest_SOdd),
                new SqlParameter("@Probability_FOdd",model.Probability_FOdd),
                new SqlParameter("@Probability_POdd",model.Probability_POdd),
                new SqlParameter("@Probability_SOdd",model.Probability_SOdd),
                new SqlParameter("@Newest_SOdd",model.Remark),
                new SqlParameter("@Probability_FOdd",model.VisitingTeamId),
                new SqlParameter("@Probability_POdd",model.VisitingTeamName),
            };
            var result = SqlHelper.ExecuteNonQuery(CommandType.Text, sqlString, param);
            return result > 0;
        }
        

        public bool AddNews(NewsTypeEnum news, Base_News model)
        {
            var TableName = "Base_News";
            var sqlString = string.Format(AddNewsSql, TableName);
            var param = new SqlParameter[]{
                new SqlParameter ("@ID",model.Id),
                new SqlParameter ("@TypeId",model.TypeId),
                new SqlParameter ("@ParentId",model.ParentId),
                new SqlParameter ("@Category",model.Category),
                new SqlParameter ("@CategoryId",model.CategoryId),
                new SqlParameter ("@FullHead",model.FullHead),
                new SqlParameter ("@FullHeadColor",model.FullHeadColor),
                new SqlParameter ("@BriefHead",model.BriefHead),
                new SqlParameter ("@AuthorName",model.AuthorName),
                new SqlParameter ("@CompileName",model.CompileName),
                new SqlParameter ("@TagWord",model.TagWord),
                new SqlParameter ("@Keyword",model.Keyword),
                new SqlParameter ("@SourceName",model.SourceName),
                new SqlParameter ("@SourceAddress",model.SourceAddress),
                new SqlParameter ("@NewsContent",model.NewsContent),
                new SqlParameter ("@ReleaseTime",model.ReleaseTime),
                new SqlParameter ("@DeleteMark",model.DeleteMark),
                new SqlParameter ("@CreateDate",model.CreateDate),
                new SqlParameter ("@IsHot",model.IsHot),
                new SqlParameter ("@IsStick",model.IsStick),
                new SqlParameter ("@IsRecommend",model.IsRecommend),
                new SqlParameter ("@EnabledMark",model.EnabledMark),
            };
            var result = SqlHelper.ExecuteNonQuery(CommandType.Text, sqlString, param);
            return result > 0;
        }

        #endregion

        #region SQL语句

        /// <summary>
        /// 通过赛事id获取赛事名称
        /// </summary>
        private static string GetTeamListSql = @"select Id,Name from F_Team ";

        /// <summary>
        /// 获取所有的球员
        /// </summary>
        private static string GetPlayerListSql = @"select Id,ChineseName from F_Players ";

        /// <summary>
        /// 获取所有的球员
        /// </summary>
        private static string GetContryListSql = @"select Id,Name from F_Area  where ParentId is not null";

        /// <summary>
        /// 新增队员信息
        /// </summary>
        private static string AddPlayersSql = @"IF NOT EXISTS(SELECT TOP 1 1 FROM {0} WHERE ChineseName = @ChineseName)
                                                        BEGIN
	                                                        INSERT INTO {0}(Id,ChineseName,EnglishName,Nationality,Weight,Stature,Birthday,GoodFoot,Honor,Describe,LogoImage,FromAddress,Celebrity,Remark)
                                                            SELECT @Id,@ChineseName,@EnglishName,@Nationality,@Weight,@Stature,@Birthday,@GoodFoot,@Honor,@Describe,@LogoImage,@FromAddress,@Celebrity,@Remark
                                                        END";
        /// <summary>
        /// 新增球队球员关系
        /// </summary>
        private static string AddRelationsSql = @"IF NOT EXISTS(SELECT TOP 1 1 FROM {0} WHERE TeamId = @TeamId  and PlayerId = @PlayerId)
                                                        BEGIN
	                                                        INSERT INTO {0}(Id,TeamId,PlayerId,Position,PoloShirt)
                                                            SELECT @Id,@TeamId,@PlayerId,@Position,@PoloShirt
                                                        END";

        /// <summary>
        /// 添加区域表sql语句
        /// </summary>
        private static string AddTeamSql = @"IF NOT EXISTS(SELECT TOP 1 1 FROM {0} WHERE Name = @Name)
                                                        BEGIN
	                                                        INSERT INTO {0}(Name,Id,CityName,CreateDateTime,Describe,Venue,Coach,Address,SetUpDateTime,Advantage,Style,Weaknesses,Website,LogoImage,Remark,EventId)
                                                            SELECT @Name,@Id,@CityName,@CreateDateTime,@Describe,@Venue,@Coach,@Address,@SetUpDateTime,@Advantage,@Style,@Weaknesses,@Website,@LogoImage,@Remark,@EventId
                                                        END";
        /// <summary>
        /// 添加公司表sql语句
        /// </summary>
        private static string AddBankerCompanysql = @"IF NOT EXISTS(SELECT TOP 1 1 FROM {0} WHERE Name = @Name)
                                                        BEGIN
	                                                        INSERT INTO {0}(Name,Id,CompanyType,CreateDateTime,Remark)
                                                            SELECT @Name,@Id,@CompanyType,@CreateDateTime,@Remark
                                                        END";
        /// <summary>
        /// 添加公司关系表sql语句
        /// </summary>
        private static string AddBankerCompanyRelationsql = @"IF NOT EXISTS(SELECT TOP 1 1 FROM {0} WHERE Name = @Name)
                                                        BEGIN
	                                                        INSERT INTO {0}(BankerCompanyType,Id,BankerCompanyType,CreateDateTime,Remark)
                                                            SELECT @BankerCompanyType,@Id,@BankerCompanyType,@CreateDateTime,@Remark
                                                        END";
        /// <summary>
        /// 添加固化分析表sql语句
        /// </summary>
        private static string AddCuringAnalysissql = @"IF NOT EXISTS(SELECT TOP 1 1 FROM {0} WHERE Name = @Name)
                                                        BEGIN
	                                                        INSERT INTO {0}(Id,
ChangeDateTime,
CreateDateTime,
CuringAnalysisType,
CuringType,
GameId,
Init_Disc,
Init_FOdd,
Init_POdd,
Init_SOdd,
Kelly_FOdd,
Kelly_POdd,
Kelly_SOdd,
Loss_Odd,
Newest_Disc,
Newest_FOdd,
Newest_POdd,
Newest_SOdd,
Probability_FOdd,
Probability_POdd,
Probability_SOdd,
Remark)
                                                            SELECT @Id,
@ChangeDateTime,
@CreateDateTime,
@CuringAnalysisType,
@CuringType,
@GameId,
@Init_Disc,
@Init_FOdd,
@Init_POdd,
@Init_SOdd,
@Kelly_FOdd,
@Kelly_POdd,
@Kelly_SOdd,
@Loss_Odd,
@Newest_Disc,
@Newest_FOdd,
@Newest_POdd,
@Newest_SOdd,
@Probability_FOdd,
@Probability_POdd,
@Probability_SOdd,
@Remark
                                                        END";

        private static string AddAreaSql = @"IF NOT EXISTS(SELECT TOP 1 1 FROM {0} WHERE Name = @Name)
                                                        BEGIN
	                                                        INSERT INTO {0}(Name,Id,ParentId,CreateDateTime,LogoImage,SortCode,Remark)
                                                            SELECT @Name,@Id,@ParentId,@CreateDateTime,@LogoImage,@SortCode,@Remark
                                                        END";

        /// <summary>
        /// 添加各区域的赛事
        /// </summary>
        private static string AddEventsSql = @"IF NOT EXISTS(SELECT TOP 1 1 FROM {0} WHERE Name = @Name)
                                                        BEGIN
	                                                        INSERT INTO {0}(Name,Id,AreaId,CreateDateTime,Remark,LogoImage,EventType)
                                                            SELECT @Name,@Id,@AreaId,GETDATE(),@Remark,@LogoImage,@EventType
                                                        END";

        /// <summary>
        /// 获取区域表国家的GUiD
        /// </summary>
        private static string LastItemSql = @" SELECT TOP 1 * FROM[Berry].[dbo].[F_Area]     WHERE Name = '{0}'";



        private static string SelectDataItemDetailSql = @" select a.Id, a.ItemName as Name,a.ItemValue ,B.ItemName as parentName,b.Id as parentId ,b.ItemCode from Base_DataItemDetail as a left join Base_DataItem as b on  a.ItemId=b.Id WHERE b.ItemCode='{0}'";



     
        
        /// <summary>
        /// 新增英超新闻
        /// </summary>
        private static string AddNewsSql = @"IF NOT EXISTS(SELECT TOP 1 1 FROM {0} WHERE FullHead = @FullHead)
                                                        BEGIN
	                                                        INSERT INTO {0}(Id,TypeId,ParentId,Category,CategoryId,FullHead,FullHeadColor,BriefHead,AuthorName,CompileName,TagWord,Keyword,SourceName,SourceAddress,NewsContent,ReleaseTime,DeleteMark,CreateDate,IsHot,IsStick,IsRecommend,EnabledMark)
                                                            SELECT @Id,@TypeId,@ParentId,@Category,@CategoryId,@FullHead,@FullHeadColor,@BriefHead,@AuthorName,@CompileName,@TagWord,@Keyword,@SourceName,@SourceAddress,@NewsContent,@ReleaseTime,@DeleteMark,@CreateDate,@IsHot,@IsStick,@IsRecommend,@EnabledMark
                                                        END";


        #endregion


        #region 篮球相关

        /// <summary>
        /// 获取篮球球队
        /// </summary>
        /// <returns></returns>
        public List<B_Team> GetBasketTeamList()
        {
            var sqlString = string.Format(GetBasketTeamListSql);

            var ds = SqlHelper.ExecuteDataset(CommandType.Text, sqlString);
            if (ds != null && ds.Tables.Count > 0)
            {
                var result = LoadDataList<B_Team>(ds.Tables[0]);
                return result;
            }
            return null;
        }

        /// <summary>
        /// 获取篮球球员
        /// </summary>
        /// <returns></returns>
        public List<B_Players> GetBasketPalyerList()
        {
            var sqlString = string.Format(GetBasketPlayerListSql);

            var ds = SqlHelper.ExecuteDataset(CommandType.Text, sqlString);
            if (ds != null && ds.Tables.Count > 0)
            {
                var result = LoadDataList<B_Players>(ds.Tables[0]);
                return result;
            }
            return null;
        }
        #endregion

        #region 篮球sql

        /// <summary>
        /// 获取赛事
        /// </summary>
        private static string GetBasketTeamListSql = @"select Id,Name from B_Team ";

        /// <summary>
        /// 获取球员
        /// </summary>
        private static string GetBasketPlayerListSql = @"select Id,ChineseName from B_Players ";
        #endregion

        #region 公共方法
        /// <summary>
        /// 批量新增（SqlBulkCopy）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        public void SqlBulkCopyAdd<T>(IEnumerable<T> list)
        {
            if (list.Count() <= 0)
                return;
            DataTable dt = ListToDataTable<T>(list.ToList());
            SqlConnection conn = new SqlConnection(SqlHelper.GetConnSting());
            conn.Open();
            //声明SqlBulkCopy ,using释放非托管资源
            using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn))
            {
                //一次批量的插入的数据量
                // sqlBC.BatchSize = 1000;
                //超时之前操作完成所允许的秒数，如果超时则事务不会提交 ，数据将回滚，所有已复制的行都会从目标表中移除
                sqlBC.BulkCopyTimeout = 60;

                ////設定 NotifyAfter 属性，以便在每插入10000 条数据时，呼叫相应事件。  
                //sqlBC.NotifyAfter = 10000;
                //sqlBC.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);

                //设置要批量写入的表
                sqlBC.DestinationTableName = EntityAttributeHelper.GetEntityTable<T>();

                //自定义的datatable和数据库的字段进行对应
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    sqlBC.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);//dt字段名和数据库表的字段名
                }
                //批量写入
                sqlBC.WriteToServer(dt);
            }
            conn.Close();
        }
        /// <summary>
        /// 将泛类型集合List类转换成DataTable
        /// </summary>
        /// <param name="entitys">泛类型集合</param>
        /// <returns></returns>
        public DataTable ListToDataTable<T>(List<T> entitys)
        {
            //检查实体集合不能为空
            if (entitys == null || entitys.Count < 1)
            {
                throw new Exception("需转换的集合为空");
            }
            //取出第一个实体的所有Propertie
            Type entityType = entitys[0].GetType();
            PropertyInfo[] entityProperties = entityType.GetProperties();

            //生成DataTable的structure
            //生产代码中，应将生成的DataTable结构Cache起来，此处略
            DataTable dt = new DataTable();
            for (int i = 0; i < entityProperties.Length; i++)
            {
                //dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType);
                dt.Columns.Add(entityProperties[i].Name);
            }
            //将所有entity添加到DataTable中
            foreach (T entity in entitys)
            {
                try
                {
                    //检查所有的的实体都为同一类型
                    if (entity.GetType() != entityType)
                    {
                        throw new Exception("要转换的集合元素类型不一致");
                    }
                    object[] entityValues = new object[entityProperties.Length];
                    for (int i = 0; i < entityProperties.Length; i++)
                    {
                        entityValues[i] = entityProperties[i].GetValue(entity, null);
                    }
                    dt.Rows.Add(entityValues);
                }
                catch (Exception e)
                {

                    //throw;
                }

            }
            return dt;
        }

        /// <summary>
        /// DataTable转换List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataset"></param>
        /// <returns></returns>
        public List<T> DataSetToList<T>(DataSet dataset)
        {
            DataTable dataTable = dataset.Tables[0];
            // 返回值初始化 
            List<T> result = new List<T>();
            for (int j = 0; j < dataTable.Rows.Count; j++)
            {
                T _t = (T)Activator.CreateInstance(typeof(T));
                PropertyInfo[] propertys = _t.GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    for (int i = 0; i < dataTable.Columns.Count; i++)
                    {
                        // 属性与字段名称一致的进行赋值 
                        if (pi.Name.Equals(dataTable.Columns[i].ColumnName))
                        {
                            // 数据库NULL值单独处理 
                            if (dataTable.Rows[j][i] != DBNull.Value)
                                pi.SetValue(_t, dataTable.Rows[j][i], null);
                            else
                                pi.SetValue(_t, null, null);
                            break;
                        }
                    }
                }
                result.Add(_t);
            }
            return result;
        }
        #endregion


        #region 查询
        /// <summary>
        /// 单表查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public IEnumerable<T> Query<T>(string where = "", string json = "", string select = "a.*")
        {
            var sql = @"
SELECT {3} FROM {0} a
{2}
WHERE 1=1 {1}
".FormatMe(EntityAttributeHelper.GetEntityTable<T>(), where, json, select);
            var ds = SqlHelper.ExecuteDataset(CommandType.Text, sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                var result = DataSetToList<T>(ds);
                return result;
            }
            return new List<T>();
        }
        /// <summary>
        /// 单表查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public IEnumerable<U> Query<T, U>(string where = "", string json = "", string select = "a.*")
        {
            var sql = @"
SELECT {3} FROM {0} a
{2}
WHERE 1=1 {1}
".FormatMe(EntityAttributeHelper.GetEntityTable<T>(), where, json, select);
            var ds = SqlHelper.ExecuteDataset(CommandType.Text, sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                var result = DataSetToList<U>(ds);
                return result;
            }
            return new List<U>();
        }
        /// <summary>
        /// 单表查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public IEnumerable<T> Query1<T>(string where = "")
        {
            var sql = @"
SELECT * FROM {0} WHERE 1=1 {1}
".FormatMe(EntityAttributeHelper.GetEntityTable<T>(), where);
            var ds = SqlHelper.ExecuteDataset(CommandType.Text, sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                var result = DataSetToList<T>(ds);
                return result;
            }
            return null;
        }
        #endregion
        #region 删除
        public void Delete<T>(string where = "")
        {
            var sql = @" DELETE  {0} WHERE 1=1 {1} ".FormatMe(EntityAttributeHelper.GetEntityTable<T>(), where);
            var result = SqlHelper.ExecuteNonQuery(CommandType.Text, sql);
            Trace.WriteLine("删除数据的条数：" + result);
        }
        #endregion
        #region 更改
        public void Update<T>(string where = "")
        {
            var sql = @" Update {0} set IsObtain=1 WHERE 1=1 {1} ".FormatMe(EntityAttributeHelper.GetEntityTable<T>(), where);
            var result = SqlHelper.ExecuteNonQuery(CommandType.Text, sql);
            Trace.WriteLine("更改数据的条数：" + result);
        }
        #endregion

    }
}