Example #1
0
        public override bool BulkCopy <TEntity>(IEnumerable <TEntity> list, IBulkMapper bulkMapper)
        {
            try
            {
                CreateConnection();
                SqlConnection connection = Connection as SqlConnection;
                if (connection == null)
                {
                    throw new NullReferenceException(nameof(Connection));
                }

                SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)
                {
                    DestinationTableName = bulkMapper.DstTableName[0]
                };

                foreach (string map in bulkMapper.MapList)
                {
                    string[] cols = map.Split(',');
                    bulkCopy.ColumnMappings.Add(cols[0], cols[1]);
                }

                using (var er = new EntityReader <TEntity>(list))
                {
                    Stopwatch sw = new Stopwatch();
                    sw.Start();
                    bulkCopy.WriteToServer(er);
                    sw.Stop();
                    bulkCopy.Close();

                    Trace.WriteLine($"bulkCopy took {sw.ElapsedMilliseconds} ms to execute");
                }
            }
            catch (Exception e)
            {
                LastError.Code    = e.HResult;
                LastError.Message = e.Message;
                return(false);
            }

            return(true);
        }
Example #2
0
 public virtual bool BulkUpdate <TEntity>(IEnumerable <TEntity> list, IBulkMapper bulkMapper) where TEntity : class, new()
 {
     throw new NotImplementedException();
 }
Example #3
0
        /*
         * Example 1
         *  Merges the DailySales table into GlobalSales:
         *
         *  merge into GlobalSales
         *      (Item_number, Description, Quantity)as G
         *  using DailySales as D
         *  ON D.Item_number = G.Item_number
         *  when not matched then
         *      insert (Item_number, Description, Quantity )
         *          values (D.Item_number, D.Description, D.Quantity)
         *  when matched then
         *      update set G.Quantity = G.Quantity + D.Quantity
         *
         * Example 2
         * Uses a derived table as the source table with dynamic parameter markers:
         *
         * merge into GlobalSales (Item_number, Description, Quantity)as G
         * using select (?, ?, ?) as D (Item_number, Description, Quantity)
         * ON D.Item_number = G.Item_number
         * when not matched then
         *  insert (Item_number, Description, Quantity )
         *        values (D.Item_number, D.Description, D.Quantity)
         * when matched then
         *  update set G.Quantity = G.Quantity + D.Quantity
         */
        //-----------------------------------------------------------------------------------------
        public override bool BulkUpdate <TEntity>(IEnumerable <TEntity> list, IBulkMapper bulkMapper)
        {
            Stopwatch sw = new Stopwatch();

            try
            {
                if (bulkMapper.SqlCreateTmpTable.IsNotNullOrEmpty())
                {
                    CreateCommand(bulkMapper.SqlCreateTmpTable);
                    Cmd.ExecuteNonQuery();
                }

                // this is the connection for the bulkupdate
                SqlConnection connection = Connection as SqlConnection;
                if (connection == null)
                {
                    throw new NullReferenceException(nameof(connection));
                }

                SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)
                {
                    DestinationTableName = $"#tmp_{bulkMapper.TmpTableName}"
                };
                foreach (string colPair in bulkMapper.MapList)
                {
                    string[] fields = colPair.Split(',');
                    if (fields.Length == 2)
                    {
                        bulkCopy.ColumnMappings.Add(fields[0], fields[0]);
                    }
                }
#if false
                using (var reader = ObjectReader.Create(list))
                {
                    // write records to temp table.
                    sw.Restart();
                    bulkCopy.WriteToServer(reader);
                    sw.Stop();
                    bulkCopy.Close();
                    Trace.WriteLine($"BulkUpdate bulkCopy.WriteToServer took {sw.ElapsedMilliseconds} ms to execute");

                    // merge temp table into specified tables, one at a time.
                    foreach (var sqlMerge in bulkMapper.SqlMerge)
                    {
                        var resultList = ExecuteQuery <TEntity>($"select * from #tmp_{bulkMapper.TmpTableName}");
                        if (resultList != null && resultList.Any())
                        {
                            if (sqlMerge.IsNotNullOrEmpty())
                            {
                                sw.Restart();
                                CreateCommand(sqlMerge);
                                Cmd.ExecuteNonQuery();
                                sw.Stop();
                                Trace.WriteLine($"BulkUpdate Merge statement took {sw.ElapsedMilliseconds} ms to execute");
                            }
                        }
                    }
                }
#else
                using (var reader = new EntityReader <TEntity>(list))
                {
                    // write records to temp table.
                    sw.Restart();
                    bulkCopy.WriteToServer(reader);
                    sw.Stop();
                    bulkCopy.Close();
                    Trace.WriteLine($"BulkUpdate bulkCopy.WriteToServer took {sw.ElapsedMilliseconds} ms to execute");

                    // merge temp table into specified tables, one at a time.
                    foreach (var sqlMerge in bulkMapper.SqlMerge)
                    {
                        var resultList = ExecuteQuery <TEntity>($"select top 1 * from #tmp_{bulkMapper.TmpTableName}");
                        if (resultList != null && resultList.Any())
                        {
                            if (sqlMerge.IsNotNullOrEmpty())
                            {
                                CreateCommand(sqlMerge);
                                sw.Restart();
                                Cmd.ExecuteNonQuery();
                                sw.Stop();
                                Trace.WriteLine($"BulkUpdate Merge statement took {sw.ElapsedMilliseconds} ms to execute");
                            }
                        }
                    }
                }
#endif
            }
            catch (Exception e)
            {
                LastError.Code    = e.HResult;
                LastError.Message = e.Message;
                Trace.WriteLine($"Error Message: {e.Message}");
                return(false);
            }

            return(true);
        }
Example #4
0
 public override bool BulkCopy <TEntity>(IEnumerable <TEntity> list, IBulkMapper bulkMapper)
 {
     return(base.BulkCopy(list, bulkMapper));
 }