public async Task <IActionResult> Get2() { var bulkCopy = new SqlBulkCopy(_connection); var dataObject = initializeData(); var properties = TypeDescriptor.GetProperties(typeof(WeatherForecast)); bulkCopy.DestinationTableName = "TBL_NAME"; foreach (PropertyDescriptor item in properties) { var columnSource = item.Name; var columnDestination = item.Name; bulkCopy.ColumnMappings.Add(columnSource, columnDestination); } _connection.Open(); using (var dataReader = new ObjectDataReader <WeatherForecast> (dataObject)) { bulkCopy.WriteToServer(dataReader); } bulkCopy.Close(); _connection.Close(); var results = await _connection.QueryAsync <WeatherForecast> ("SELECT TOP 100 * FROM TBL_NAME"); Console.WriteLine(results.Count()); return(Ok(results)); }
private void setAll <TData>(IEnumerable <TData> objects, SqlBulkCopyOptions copyOptions, TableNameResolver nameResolver) { var connection = GetDefaultConnection(); var bulkCopy = new SqlBulkCopy(connection, copyOptions, null); bulkCopy.SetupBulkCopyByDataType <TData>(nameResolver); bool keepClosed = connection.State == ConnectionState.Closed; try { if (connection.State == ConnectionState.Closed) { connection.Open(); } using (var dataReader = new ObjectDataReader <TData>(objects)) { bulkCopy.WriteToServer(dataReader); } } catch (Exception ex) { throw new Exception("SetAll < " + typeof(TData) + " >", ex); } finally { if (keepClosed) { connection.Close(); } } }
public void SqlTVPSimple1() { using var csvText = GetData(); // Gets a TextReader over a large-ish CSV dataset var conn = GetConnection(); conn.Open(); var data = Enumerable .Range(0, 10) .Select(i => new { Id = i, Name = "name " + i }); var dataReader = ObjectDataReader.Create(data); dataReader.AddColumn("Id", r => r.Id); dataReader.AddColumn("Name", r => r.Name); using var cmd = conn.CreateCommand(); cmd.CommandText = "InsertSimple1"; cmd.CommandType = CommandType.StoredProcedure; var param = new SqlParameter() { ParameterName = "data", SqlDbType = SqlDbType.Structured }; var paramData = dataReader; param.Value = paramData; cmd.Parameters.Add(param); cmd.ExecuteNonQuery(); }
public void InsertTenThousandAlphabets1() { using (var entities = new Entities()) { var insertTime = new Stopwatch(); var alphabets = Enumerable.Range(0, 10000).Select(i => new Alphabet { A = "A", B = "B", C = "C", D = "D", E = "E", F = "F", G = "G", H = "H", I = "I", J = "J", K = "K", L = "L", M = "M", N = "N", O = "O", P = "P", Q = "Q", R = "R", S = "S", T = "T", U = "U", V = "V", W = "W", X = "X", Y = "Y", Z = "Z" }).ToList(); insertTime.Start(); var sqlBulkCopy = new SqlBulkCopy(entities.SqlConnection) { DestinationTableName = "Alphabets", ColumnMappings = { { "A", "A" }, { "B", "B" }, { "C", "C" }, { "D", "D" }, { "E", "E" }, { "F", "F" }, { "G", "G" }, { "H", "H" }, { "I", "I" }, { "J", "J" }, { "K", "K" }, { "L", "L" }, { "M", "M" }, { "N", "N" }, { "O", "O" }, { "P", "P" }, { "Q", "Q" }, { "R", "R" }, { "S", "S" }, { "T", "T" }, { "U", "U" }, { "V", "V" }, { "W", "W" }, { "X", "X" }, { "Y", "Y" }, { "Z", "Z" } } }; using (var dataReader = new ObjectDataReader <Alphabet>(alphabets)) { sqlBulkCopy.WriteToServer(dataReader); } sqlBulkCopy.Close(); insertTime.Stop(); double actualInsertTimePerAlphabet = (double)insertTime.ElapsedMilliseconds / alphabets.Count(); Debug.WriteLine("Insert Time Milliseconds Per Row: " + actualInsertTimePerAlphabet); } }
public void InsertTenThousandAlphabets1() { using (var entities = new Entities()) { var insertTime = new Stopwatch(); var alphabets = Enumerable.Range(0, 10000).Select(i => new Alphabet { A = "A", B = "B", C = "C", D = "D", E = "E", F = "F", G = "G", H = "H", I = "I", J = "J", K = "K", L = "L", M = "M", N = "N", O = "O", P = "P", Q = "Q", R = "R", S = "S", T = "T", U = "U", V = "V", W = "W", X = "X", Y = "Y", Z = "Z" }).ToList(); insertTime.Start(); var sqlBulkCopy = new SqlBulkCopy(entities.SqlConnection) { DestinationTableName = "Alphabets", ColumnMappings = {{"A", "A"}, {"B", "B"}, {"C", "C"}, {"D", "D"}, {"E", "E"}, {"F", "F"}, {"G", "G"}, {"H", "H"}, {"I", "I"}, {"J", "J"}, {"K", "K"}, {"L", "L"}, {"M", "M"}, {"N", "N"}, {"O", "O"}, {"P", "P"}, {"Q", "Q"}, {"R", "R"}, {"S", "S"}, {"T", "T"}, {"U", "U"}, {"V", "V"}, {"W", "W"}, {"X", "X"}, {"Y", "Y"}, {"Z", "Z"}} }; using (var dataReader = new ObjectDataReader<Alphabet>(alphabets)) { sqlBulkCopy.WriteToServer(dataReader); } sqlBulkCopy.Close(); insertTime.Stop(); double actualInsertTimePerAlphabet = (double)insertTime.ElapsedMilliseconds / alphabets.Count(); Debug.WriteLine("Insert Time Milliseconds Per Row: " + actualInsertTimePerAlphabet); } }
public override void BulkSaveInStaging(IEnumerable <T> entities) { var sqlBulkCopy = new SqlBulkCopy(OpenAndGetSqlConnection(), SqlBulkCopyOptions.Default, null); sqlBulkCopy.DestinationTableName = SqlStagingTableName; sqlBulkCopy.BulkCopyTimeout = 300; sqlBulkCopy.BatchSize = entities.Count(); foreach (var element in PropertiesToBulkLoad) { sqlBulkCopy.ColumnMappings.Add(element.Name, element.GetColumnName(base.StoreObject)); } sqlBulkCopy.ColumnMappings.Add(TempColumnNumOrderName, TempColumnNumOrderName); var dataReader = new ObjectDataReader(entities, new ObjectDataReaderConfig { EfProperties = PropertiesToBulkLoad, Types = EntityTypes, Context = Context, TempColumnNumOrderName = TempColumnNumOrderName }); // sqlBulkCopy.SqlRowsCopied += (sender, eventArgs) => // { // Console.WriteLine("Wrote " + eventArgs.RowsCopied + " records."); // }; sqlBulkCopy.WriteToServer(dataReader); }
void TestAllParameterCombinations(IEnumerable <Car> cars) { foreach (var nt in Enum.GetValues(typeof(NullConversion))) { var reader = new ObjectDataReader <Car>(cars, NullConversion.ToDBNull); DoMainChecks <Car>(reader); } }
public static DbDataReader GetBinaryData() { var items = GetTestBinary(); var reader = ObjectDataReader.Create(items); reader.AddColumn("Id", d => d.Id); reader.AddColumn("Data", d => d.Data); return(reader); }
public void as_nullable_gives_nullable_object() { var reader = new ObjectDataReader <Car>(GetNullableCars(), NullConversion.None); reader.Read(); Assert.IsNull(reader["NullableIntField"]); Assert.IsNull(reader["NullableReadOnlyIntField"]); Assert.IsNull(reader["ReadOnlyStringField"]); Assert.IsNull(reader["NullableEnumField"]); }
public void as_db_null_converts_nulls_to_db_null() { var reader = new ObjectDataReader <Car>(GetNullableCars(), NullConversion.ToDBNull); reader.Read(); Assert.True(Convert.IsDBNull(reader["NullableIntField"])); Assert.True(Convert.IsDBNull(reader["NullableReadOnlyIntField"])); Assert.True(Convert.IsDBNull(reader["ReadOnlyStringField"])); Assert.True(Convert.IsDBNull(reader["NullableEnumField"])); }
public async Task CopyAsync(SqlConnection connection) { using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, _transaction)) { using (var dataReader = new ObjectDataReader <T>(_enumerable.GetEnumerator())) { ConfigureBulkCopy(sqlBulkCopy); await sqlBulkCopy.WriteToServerAsync(dataReader, _cancellationToken).ConfigureAwait(false); } } }
private static void InsertDataUsingSqlBulkCopy(IEnumerable<ContactInfo> people, SqlConnection connection) { var bulkCopy = new SqlBulkCopy(connection); bulkCopy.DestinationTableName = "Person"; // bulkCopy.ColumnMappings.Add("Name", "Name")lkCopy.ColumnMappings.Add("DateOfBirth", "DateOfBirth"); SqlBulkCopyColumnMapping mapping = new SqlBulkCopyColumnMapping(); using (var dataReader = new ObjectDataReader<ContactInfo>(people)) { bulkCopy.WriteToServer(dataReader); } }
void IncludesFieldsFromPrefixedObject <T>(ObjectDataReader <T> reader, string prefix) { var columns = reader.GetColumns(); CollectionAssert.Contains(columns, prefix + "IntField"); CollectionAssert.Contains(columns, prefix + "NullableIntField"); CollectionAssert.Contains(columns, prefix + "ReadOnlyIntField"); CollectionAssert.Contains(columns, prefix + "NullableReadOnlyIntField"); CollectionAssert.Contains(columns, prefix + "ReadOnlyStringField"); CollectionAssert.Contains(columns, prefix + "StringProperty"); CollectionAssert.Contains(columns, prefix + "EnumField"); CollectionAssert.Contains(columns, prefix + "NullableEnumField"); }
private IEnumerable <object> ExecuteBulkCopy(IEnumerable <TInput> input, SqlConnection conn) { var reader = new ObjectDataReader <TInput>(input); using (var bcp = new SqlBulkCopy(conn)) { bcp.DestinationTableName = _destinationTable; bcp.BulkCopyTimeout = (int)Math.Ceiling(Timeout.TotalSeconds); SetupMappings(bcp); bcp.Insert(reader); } return(Enumerable.Empty <object>()); }
void CanReadAllRowsAndColumns <T>(ObjectDataReader <T> reader) { var columns = reader.GetColumns(); while (reader.Read()) { for (int i = 0; i < columns.Count(); i++) { object o1, o2; Assert.DoesNotThrow(() => o1 = reader[i]); Assert.DoesNotThrow(() => o2 = reader[columns[i]]); } } }
public static DbDataReader GetTestDataReader(int recordCount = DefaultRecordCount, int valueCount = DefaultDataValueCount) { var items = GetTestObjects(recordCount, valueCount); return (ObjectDataReader .Build <TestRecord>() .AddColumn("Id", i => i.Id) .AddColumn("Name", i => i.Name) .AddColumn("Date", i => i.Date) .AddColumn("IsActive", i => i.IsActive) .Repeat((b, i) => b.AddColumn("Data" + i, r => r.DataSet[i]), valueCount) .Build(items)); }
public override void Close() { try { if (this.bulkCopyTask != null && !(this.bulkCopyTask.IsCanceled || this.bulkCopyTask.IsCompleted || this.bulkCopyTask.IsFaulted)) { this.Rollback(); } } finally { if (this.dataReader != null) { this.dataReader.Dispose(); this.dataReader = null; } if (this.bulkCopyTask != null) { this.bulkCopyTask.Dispose(); this.bulkCopyTask = null; } if (this.BulkCopy != null) { ((IDisposable)this.BulkCopy).Dispose(); this.BulkCopy = null; } if (this.sqlTransaction != null) { this.sqlTransaction.Dispose(); this.sqlTransaction = null; } if (this.sqlConnection != null) { this.sqlConnection.Dispose(); this.sqlConnection = null; } if (this.cancellationTokenSource != null) { this.cancellationTokenSource.Dispose(); this.cancellationTokenSource = null; } } }
private static async Task RunStaticDatasetDemoAsync(SqlConnection connection, int count, CancellationToken cancellationToken) { using (var bulkCopy = new SqlBulkCopy(connection)) { bulkCopy.DestinationTableName = "Contacts"; bulkCopy.BatchSize = 1000; bulkCopy.BulkCopyTimeout = (int)TimeSpan.FromMinutes(10).TotalSeconds; bulkCopy.ColumnMappings.Add("Id", "Id"); bulkCopy.ColumnMappings.Add("FirstName", "FirstName"); bulkCopy.ColumnMappings.Add("LastName", "LastName"); bulkCopy.ColumnMappings.Add("BirthDate", "BirthDate"); using (var reader = new ObjectDataReader <Contact>(new RandomDataGenerator().GetContacts(count))) await bulkCopy.WriteToServerAsync(reader, cancellationToken); } }
public static DbDataReader GetTestDataReader(int recordCount = DefaultRecordCount, int valueCount = DefaultDataValueCount) { var items = GetTestObjects(recordCount, valueCount); var reader = ObjectDataReader.Create(items); reader.AddColumn("Id", d => d.Id); reader.AddColumn("Name", d => d.Name); reader.AddColumn("Date", d => d.Date); reader.AddColumn("IsActive", d => d.IsActive); for (int i = 0; i < valueCount; i++) { var idx = 0; reader.AddColumn("Value" + i, d => d.DataSet[idx]); } return(reader); }
//Add list of users into table private static void InsertDataUsingSqlBulkCopy(IEnumerable <UserVO> studentUsers, SqlConnection connection) { var bulkCopy = new SqlBulkCopy(connection); bulkCopy.DestinationTableName = "IlmpUser"; bulkCopy.ColumnMappings.Add("UserName", "UserName"); bulkCopy.ColumnMappings.Add("StudentId", "StudentId"); bulkCopy.ColumnMappings.Add("StaffId", "StaffId"); bulkCopy.ColumnMappings.Add("Password", "Password"); bulkCopy.ColumnMappings.Add("EmailId", "EmailId"); bulkCopy.ColumnMappings.Add("HashSalt", "HashSalt"); bulkCopy.ColumnMappings.Add("FirstLogin", "FirstLogin"); bulkCopy.ColumnMappings.Add("Active", "Active"); bulkCopy.ColumnMappings.Add("Role", "Role"); using (var dataReader = new ObjectDataReader <UserVO>(studentUsers)) { bulkCopy.WriteToServer(dataReader); } }
public async Task BulkInsertBulkCopy <EntitySimple>(List <EntitySimple> list, IList <Tuple <string, string> > mappings, string destinationTable, int batchSize) { using (var objReader = new ObjectDataReader <EntitySimple>(list)) using (var connection = new SqlConnection(@"Server=LedgerLocalWINPROD01\LedgerLocalPRODDB16;Database=LedgerLocal-Main-Prod;User Id=LedgerLocaluserprod;Password=LedgerLocal132@;")) { connection.Open(); using (var bulkCopy = new SqlBulkCopy(connection)) { bulkCopy.BatchSize = batchSize; foreach (var m1 in mappings) { bulkCopy.ColumnMappings.Add(m1.Item1, m1.Item2); } bulkCopy.DestinationTableName = destinationTable; await bulkCopy.WriteToServerAsync(objReader); } } }
protected virtual void Initialize(Type recordType) { this.cancellationTokenSource = new CancellationTokenSource(); this.sqlConnection = this.CreateSqlConnection(); this.sqlTransaction = this.CreateSqlTransaction(this.sqlConnection); this.BulkCopy = this.CreateBulkCopy(this.sqlConnection, this.sqlTransaction); this.dataReader = new ObjectDataReader(recordType, this.cancellationTokenSource.Token); this.bulkCopyTask = Task.Run(() => { try { this.BulkCopy.WriteToServer(this.dataReader); } catch { this.cancellationTokenSource.Cancel(); throw; } }); }
public override void BulkSaveInStaging(IEnumerable <T> sources) { var sqlBulkCopy = new SqlBulkCopy(OpenAndGetSqlConnection(), SqlBulkCopyOptions.Default, null); sqlBulkCopy.DestinationTableName = SqlStagingTableName; sqlBulkCopy.BatchSize = sources.Count(); foreach (var element in PropertiesToBulkLoad) { sqlBulkCopy.ColumnMappings.Add(base.PropertyGetters[element.Name].Name, element.GetColumnName(base.StoreObject)); } var dataReader = new ObjectDataReader(sources, new ObjectDataReaderConfig { EfProperties = PropertiesToBulkLoad, Types = new[] { base.BaseType }, Context = Context, TempColumnNumOrderName = null }); sqlBulkCopy.WriteToServer(dataReader); }
void DoMainChecks <T>(ObjectDataReader <T> reader) { IncludesFieldsFromMainObject(reader); CanReadAllRowsAndColumns(reader); }
void IncludesFieldsFromMainObject <T>(ObjectDataReader <T> reader) { IncludesFieldsFromPrefixedObject(reader, ""); }