SqlBulkCopy批量插入

        public DBResult BatchInsert<T>(string tableName, List<T> entities) where T : class
        {
            if(entities == null || string.IsNullOrEmpty(tableName))
            {
                return DBResult.ExecuteErr;
            }

            DataTable dt = new DataTable();

            DBResult dbRst = CreateTable(tableName, ref dt);

            if(dbRst != DBResult.Success)
            {
                return DBResult.ExecuteErr;
            }

            dbRst = ListToDataTable<T>(entities, ref dt);

            if (dbRst != DBResult.Success)
            {
                return DBResult.ExecuteErr;
            }

            return BatchInsertData<T>(tableName, dt);
        }

        public DBResult BatchInsertData<T>(string tableName, DataTable dtData) where T : class
        {
            ConnectionManager manager = GetConnection();
            if (manager == null)
            {
                return DBResult.ExecuteErr;
            }

            SqlTransaction MyTrans = null;
            SqlBulkCopy BulkCopyElectron = null;
            try
            {
                manager.OpenDb();
                SqlConnection conn = manager.MyConnection;
                MyTrans = manager.MyConnection.BeginTransaction();

                if (MyTrans == null)
                {
                    return DBResult.NullObject;
                }

                if (dtData.Rows.Count > 0)
                {
                    //批量增加数据
                    BulkCopyElectron = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, MyTrans);
                    BulkCopyElectron.DestinationTableName = tableName;

                    //提交到数据库
                    BulkCopyElectron.WriteToServer(dtData);
                }

                MyTrans.Commit();
                return DBResult.Success;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);

                if (MyTrans != null)
                {
                    MyTrans.Rollback();
                }
                return DBResult.ExecuteErr;
            }
            finally
            {
                if (BulkCopyElectron != null)
                {
                    BulkCopyElectron.Close();
                }
                ReleaseConnection(ref manager);
            }
        }

        public DBResult CreateTable(string tableName, ref DataTable dt)
        {
            if (dt == null)
            {
                return DBResult.ExecuteErr;
            }

            ConnectionManager manager = GetConnection();

            try
            {
                manager.OpenDb();
                SqlCommand cmd = manager.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = string.Format("SELECT * FROM {0} WHERE 1 <> 1", tableName);
                dt = GetTable(cmd);

                if (dt == null || dt.Columns.Count == 0)
                {
                    return DBResult.ExecuteErr;
                }

                return DBResult.Success;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
                return DBResult.ExecuteErr;
            }
            finally
            {
                ReleaseConnection(ref manager);
            }
        }

        public DBResult ListToDataTable<T>(List<T> entities, ref DataTable dt)
        {
            //检查实体集合不能为空
            if (entities == null || entities.Count == 0)
            {
                return DBResult.ExecuteErr;
            }

            try
            {
                DataColumnCollection dc = dt.Columns;

                //取出第一个实体的所有Propertie
                Type entityType = entities[0].GetType();
                System.Reflection.PropertyInfo[] entityProperties = entityType.GetProperties();

                //将所有entity添加到DataTable中
                foreach (T entity in entities)
                {
                    DataRow dr = dt.NewRow();

                    for (int i = 0; i < entityProperties.Length; i++)
                    {
                        if (dc.Contains(entityProperties[i].Name))
                        {
                            dr[entityProperties[i].Name] = entityProperties[i].GetValue(entity, null);
                        }
                    }
                    dt.Rows.Add(dr);
                }

                return DBResult.Success;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
                return DBResult.ExecuteErr;
            }
        }

  

posted @ 2021-11-11 16:40  我要找到我的全世界  阅读(40)  评论(0编辑  收藏  举报