public void ComplexTypeReader()
        {
            var user = new TestUser
            {
                Contact = new Contact {
                    Address = new Address {
                        City = "Tallinn", Country = "Estonia"
                    }, PhoneNumber = "1234567"
                },
                FirstName = "Max",
                LastName  = "Lego",
                Id        = Guid.NewGuid()
            };
            var emptyUser = new TestUser();

            using (var ctx = new TestBaseContext())
            {
                using (var reader = new MappedDataReader <TestUser>(new[] { user, emptyUser }, GetDummyProvider(ctx)))
                {
                    Assert.AreEqual(11, reader.FieldCount);

                    while (reader.Read())
                    {
                        for (int i = 0; i < reader.FieldCount; ++i)
                        {
                            Console.WriteLine("{0}: {1}", i, reader.GetValue(i));
                        }
                    }
                }
            }
        }
        public void Performance()
        {
            var sw  = new Stopwatch();
            var swv = new Stopwatch();

            using (var ctx = new TestBaseContext())
            {
                ctx.Database.Initialize(false);
                sw.Restart();
                using (var reader = new MappedDataReader <Page>(CreatePages(1000000), GetDummyProvider(ctx)))
                {
                    Console.WriteLine("Construct {0}ms", sw.Elapsed.TotalMilliseconds);
                    while (reader.Read())
                    {
                        foreach (var col in reader.Cols)
                        {
                            swv.Start();
                            var value = reader.GetValue(col.Key);
                            swv.Stop();
                        }
                    }
                }
                sw.Stop();
                Console.WriteLine("Elapsed {0}ms. Getting values took {1}ms", sw.Elapsed.TotalMilliseconds, swv.Elapsed.TotalMilliseconds);
            }
        }
        public void ComplexTypeReader()
        {
            var user = new TestUser
            {
                Contact = new Contact { Address = new Address { City = "Tallinn", Country = "Estonia"}, PhoneNumber = "1234567"},
                FirstName = "Max",
                LastName = "Lego",
                Id = Guid.NewGuid()
            };
            var emptyUser = new TestUser();

            using (var ctx = new TestContext())
            {
                var tableMapping = ctx.Db<TestUser>();

                var tableMappings = new Dictionary<Type, IEntityMap>
                {
                    {typeof (TestUser), tableMapping}
                };
                using (var reader = new MappedDataReader<TestUser>(new[] { user, emptyUser }, tableMappings))
                {
                    Assert.AreEqual(9, reader.FieldCount);
                    
                    while (reader.Read())
                    {
                        for (int i = 0; i < reader.FieldCount; ++i)
                        {
                            Console.WriteLine("{0}: {1}", i, reader.GetValue(i));
                        }
                    }
                }
            }
        }
示例#4
0
        public void ComplexTypeReader()
        {
            var user = new TestUser
            {
                Contact = new Contact {
                    Address = new Address {
                        City = "Tallinn", Country = "Estonia"
                    }, PhoneNumber = "1234567"
                },
                FirstName = "Max",
                LastName  = "Lego",
                Id        = Guid.NewGuid()
            };
            var emptyUser = new TestUser();

            using (var ctx = new TestContext())
            {
                var tableMapping = ctx.Db <TestUser>();

                var tableMappings = new Dictionary <Type, IEntityMap>
                {
                    { typeof(TestUser), tableMapping }
                };
                using (var reader = new MappedDataReader <TestUser>(new[] { user, emptyUser }, tableMappings))
                {
                    Assert.AreEqual(9, reader.FieldCount);

                    while (reader.Read())
                    {
                        for (int i = 0; i < reader.FieldCount; ++i)
                        {
                            Console.WriteLine("{0}: {1}", i, reader.GetValue(i));
                        }
                    }
                }
            }
        }
        private void Run <T>(IEnumerable <T> entities, MySqlConnection connection, MySqlTransaction transaction)
        {
            bool keepIdentity = (BulkCopyOptions.KeepIdentity & Options.BulkCopyOptions) > 0;
            bool keepNulls    = (BulkCopyOptions.KeepNulls & Options.BulkCopyOptions) > 0;

            using (var reader = new MappedDataReader <T>(entities, this))
            {
                var tableEngine = GetTableEngine(connection.Database, reader.TableName, connection);

                var columns = reader.Cols
                              .Where(x => !x.Value.Computed && (!x.Value.IsIdentity || keepIdentity))
                              .ToArray();

                // INSERT INTO [TableName] (column list)
                var insert = new StringBuilder()
                             .Append($" INSERT INTO {reader.TableName} ")
                             .Append("(")
                             .Append(string.Join(",", columns.Select(col => col.Value.ColumnName)))
                             .Append(")")
                             .Append(" VALUES")
                             .ToString();

                int  i          = 0;
                long rowsCopied = 0;
                var  rows       = new List <string>();
                while (reader.Read())
                {
                    var values = new List <string>();
                    foreach (var col in columns)
                    {
                        var value = reader.GetValue(col.Key);
                        var type  = col.Value.Type;

                        AddParameter(type, value, values);
                    }

                    rows.Add("(" + string.Join(",", values) + ")");

                    i++;

                    if (i == Options.BatchSize || i == Options.NotifyAfter)
                    {
                        using (var cmd = CreateCommand(CreateInsertBatchText(insert, rows, tableEngine), connection, transaction))
                            cmd.ExecuteNonQueryAsync();

                        if (Options.Callback != null)
                        {
                            int batches = Options.BatchSize / Options.NotifyAfter;

                            rowsCopied += i;
                            Options.Callback(this, new RowsCopiedEventArgs(rowsCopied));
                        }

                        i = 0;
                        rows.Clear();
                    }
                }

                if (rows.Any())
                {
                    using (var cmd = CreateCommand(CreateInsertBatchText(insert, rows, tableEngine), connection, transaction))
                        cmd.ExecuteNonQuery();
                }
            }
        }
示例#6
0
        private void Run <T>(IEnumerable <T> entities, SqlCeConnection connection, SqlCeTransaction transaction)
        {
            bool runIdentityScripts;
            bool keepIdentity = runIdentityScripts = (SqlBulkCopyOptions.KeepIdentity & Options.SqlBulkCopyOptions) > 0;
            var  keepNulls    = (SqlBulkCopyOptions.KeepNulls & Options.SqlBulkCopyOptions) > 0;

            using (var reader = new MappedDataReader <T>(entities, this))
            {
                var identityCols = reader.Cols.Values.Where(x => x.IsIdentity).ToArray();
                if (identityCols.Length != 1 || !IsValidIdentityType(identityCols[0].Type))
                {
                    runIdentityScripts = false;
                }

                if (keepIdentity && runIdentityScripts)
                {
                    SetIdentityInsert(connection, transaction, reader.TableName, true);
                }

                var colInfos = ColInfos(connection, reader)
                               .Values
                               .Where(x => !x.IsIdentity || keepIdentity)
                               .ToArray();

                using (var cmd = CreateCommand(reader.TableName, connection, transaction))
                {
                    cmd.CommandType = CommandType.TableDirect;
                    using (var rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable))
                    {
                        var  rec        = rs.CreateRecord();
                        int  i          = 0;
                        long rowsCopied = 0;
                        while (reader.Read())
                        {
                            foreach (var colInfo in colInfos)
                            {
                                var value = reader.GetValue(colInfo.ReaderKey);
                                if (value == null && keepNulls)
                                {
                                    rec.SetValue(colInfo.OrdinalPosition, DBNull.Value);
                                }
                                else
                                {
                                    rec.SetValue(colInfo.OrdinalPosition, value);
                                }
                            }
                            rs.Insert(rec);

                            ++i;
                            if (i == Options.NotifyAfter && Options.Callback != null)
                            {
                                rowsCopied += i;
                                Options.Callback(this, new SqlRowsCopiedEventArgs(rowsCopied));
                                i = 0;
                            }
                        }
                    }
                }

                if (keepIdentity && runIdentityScripts)
                {
                    SetIdentityInsert(connection, transaction, reader.TableName, false);
                }
            }
        }
        public override void Run <T>(IEnumerable <T> entities, MySqlTransaction transaction, BulkInsertOptions options)
        {
            var keepIdentity = (SqlBulkCopyOptions.KeepIdentity & options.SqlBulkCopyOptions) > 0;

            using (var reader = new MappedDataReader <T>(entities, this))
            {
                var csvPath = AppDomain.CurrentDomain.BaseDirectory + System.Guid.NewGuid().ToString() + ".csv";

                Dictionary <int, IPropertyMap> propertyMaps = reader.Cols
                                                              .Where(x => !x.Value.IsIdentity || keepIdentity)
                                                              .ToDictionary(x => x.Key, x => x.Value);

                var csv = new StringBuilder();

                while (reader.Read())
                {
                    foreach (var kvp in propertyMaps)
                    {
                        var value = reader.GetValue(kvp.Key);
                        if (value == null)
                        {
                            csv
                            .Append("null")
                            .Append(FieldTerminator);
                        }
                        else
                        {
                            // todo - escape "'"
                            csv
                            .AppendFormat("{0}", value)
                            .Append(FieldTerminator);
                        }
                    }
                    csv.Append(LineTerminator);
                }

                // todo - save csv
                File.WriteAllText(csvPath, csv.ToString());

                // upload csv
                var mySqlBulkLoader = new MySqlBulkLoader(transaction.Connection);
                //mySqlBulkLoader.TableName = string.Format("[{0}].[{1}]", reader.SchemaName, reader.TableName);
                mySqlBulkLoader.TableName       = reader.TableName;
                mySqlBulkLoader.FieldTerminator = FieldTerminator;
                mySqlBulkLoader.LineTerminator  = LineTerminator;
                mySqlBulkLoader.FileName        = csvPath;

                foreach (var kvp in propertyMaps)
                {
                    mySqlBulkLoader.Columns.Add(kvp.Value.ColumnName);
                }

                int count = mySqlBulkLoader.Load();

                try
                {
                    File.Delete(csvPath);
                }
                catch (Exception exception)
                {
                }
            }
        }
        private void Run<T>(IEnumerable<T> entities, MySqlConnection connection, MySqlTransaction transaction)
        {
            if (!entities.Any())
                return;

            bool keepIdentity = (BulkCopyOptions.KeepIdentity & Options.BulkCopyOptions) > 0;
            bool keepNulls = (BulkCopyOptions.KeepNulls & Options.BulkCopyOptions) > 0;

            using (var reader = new MappedDataReader<T>(entities, this))
            {
                var tableEngine = GetTableEngine(connection.Database, reader.TableName, connection);

                var keyColumns = reader.Cols
                    .Where(x => !x.Value.Computed && x.Value.IsPk)
                    .ToArray();
                var columns = reader.Cols
                    .Where(x => !x.Value.Computed && !x.Value.IsPk && (!x.Value.IsIdentity || keepIdentity))
                    .ToArray();

                // UPDATE [TableName] (column list)
                var updateHeader = $"UPDATE `{reader.TableName}` SET ";
                var updateBuilder = new StringBuilder();

                int i = 0;
                long rowsCopied = 0;
                while (reader.Read())
                {
                    updateBuilder.Append(updateHeader);
                    foreach (var col in columns)
                    {
                        var value = reader.GetValue(col.Key);
                        var type = col.Value.Type;
                        updateBuilder.AppendFormat("{0}={1},", col.Value.ColumnName, AddParameter(type, value));
                    }
                    updateBuilder.Remove(updateBuilder.Length - 1, 1);//remove last ,
                    updateBuilder.Append(" WHERE ");
                    foreach(var col in keyColumns)
                    {
                        var value = reader.GetValue(col.Key);
                        var type = col.Value.Type;
                        updateBuilder.AppendFormat("{0}={1} AND ", col.Value.ColumnName, AddParameter(type, value));
                    }
                    updateBuilder.Remove(updateBuilder.Length - 5, 5);//remove last AND
                    updateBuilder.Append(";");

                    i++;

                    if (i == Options.BatchSize || i == Options.NotifyAfter)
                    {
                        using (var cmd = CreateCommand(CreateUpdateBatchText(updateBuilder, tableEngine), connection, transaction))
                            cmd.ExecuteNonQueryAsync();

                        if (Options.Callback != null)
                        {
                            int batches = Options.BatchSize / Options.NotifyAfter;

                            rowsCopied += i;
                            Options.Callback(this, new RowsCopiedEventArgs(rowsCopied));
                        }
                        i = 0;
                    }
                }

                if (i>0)
                {
                    using (var cmd = CreateCommand(CreateUpdateBatchText(updateBuilder, tableEngine), connection, transaction))
                        cmd.ExecuteNonQuery();
                }
            }
        }