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, "");
}
