public static void Main()
{
List <Contato> contatos = new List <Contato>();
string line;
int counter = 0;
string[] columns;
string[] rows;
var file = new StreamReader(@"{caminho do arquivo .csv}");
while ((line = file.ReadLine()) != null)
{
Console.WriteLine(line);
if (counter == 0)
{
columns = line.Split(";");
}
else
{
Contato contato = new Contato();
rows = line.Split(";");
contato.Id = Convert.ToInt32(rows[0]);
contato.FName = rows[1];
contato.LName = rows[2];
contato.Title = rows[3];
contato.Email = rows[4];
contato.Mobile = rows[5];
contato.Address1 = rows[6];
contato.Address2 = rows[7];
contato.City = rows[8];
contato.Zip = Convert.ToInt32(rows[9]);
contato.State = rows[10];
contato.CountryId = rows[11];
contato.Lang = rows[12];
contato.Birthdate = Convert.ToDateTime(rows[13]);
contatos.Add(contato);
}
counter++;
}
var connection = new SqlConnection(@"{connection string}");
connection.Open();
var bulk = new BulkOperation <Contato>(connection);
bulk.DestinationTableName = "Contatos";
bulk.ColumnInputExpression = c => new { c.FName, c.LName, c.Title, c.Email, c.Mobile, c.Address1, c.Address2, c.City, c.Zip, c.State, c.CountryId, c.Lang, c.Birthdate };
bulk.ColumnOutputExpression = c => c.Id;
bulk.ColumnPrimaryKeyExpression = c => c.Id;
bulk.BulkInsert(contatos);
//bulk.BulkUpdate(contatos);
}
public void BulkInsert(IEnumerable <TEntity> items)
{
CheckForObjectAlreadyDisposedOrNot(typeof(EntityFrameworkCodeFirstCommand <TEntity, TId>).FullName);
ContractUtility.Requires <ArgumentNullException>(items.IsNotNull(), "items instance cannot be null");
ContractUtility.Requires <ArgumentOutOfRangeException>(items.IsNotEmpty(), "items count should be greater than 0");
DbConnection connection = _dbContext.Database.Connection;
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var bulkOperation = new BulkOperation <TEntity>(connection);
ApplyAuditInfoRules();
bulkOperation.BulkInsert(items);
}
private static void BulkWriteToDB(DataTable dataTable, string tableName)
{
try
{
MySqlConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (var bulk = new BulkOperation(conn))
{
bulk.DestinationTableName = tableName;
bulk.BulkInsert(dataTable);
}
conn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
/*
* public int InsertDatatable(DataTable _dataTable)
* {
* lock(DBlock)
* {
* string sqlString = string.Format("SELECT * FROM {0} WHERE FALSE", _dataTable.TableName);
*
* using (MySqlConnection connection = establishConnection())
* {
* using (MySqlCommand mySqlCommand = new MySqlCommand(sqlString, connection))
* {
* connection.Open();
* MySqlTransaction mySqlTransaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
* try
* {
* int count = 0;
* MySqlDataAdapter dataAdapter = new MySqlDataAdapter();
* dataAdapter.SelectCommand = new MySqlCommand(sqlString, connection);
* MySqlCommandBuilder builder = new MySqlCommandBuilder(dataAdapter);
* builder.ConflictOption = ConflictOption.OverwriteChanges;
* builder.SetAllValues = true;
* count = dataAdapter.Update(_dataTable);
* mySqlTransaction.Commit();
* _dataTable.AcceptChanges();
* dataAdapter.Dispose();
* builder.Dispose();
*
* return count;
* }
* catch(Exception ex)
* {
* mySqlTransaction.Rollback();
* Console.WriteLine(ex.Message);
*
* return 0;
* }
* }
* }
* //以下是sqlBulkCopy 應用於SQL但不能用在MySQL
* using (var sqlBulkCopy = new SqlBulkCopy(connectionString(), SqlBulkCopyOptions.UseInternalTransaction))
* {
* //設定批次量及逾時
* sqlBulkCopy.BatchSize = 1000;
* sqlBulkCopy.BulkCopyTimeout = 60;
* //sqlBulkCopy.NotifyAfter = 10000;
* //sqlBulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
*
* //資料庫內的資料表名
* sqlBulkCopy.DestinationTableName = "plc";
*
* sqlBulkCopy.ColumnMappings.Add("StationID", "StationID");
* sqlBulkCopy.ColumnMappings.Add("DateTime", "DateTime");
* sqlBulkCopy.ColumnMappings.Add("StationState", "StationState");
* sqlBulkCopy.ColumnMappings.Add("Temperature", "Temperature");
* sqlBulkCopy.ColumnMappings.Add("Pa", "Pa");
* sqlBulkCopy.ColumnMappings.Add("ValueType", "ValueType");
* sqlBulkCopy.ColumnMappings.Add("Value", "Value");
* sqlBulkCopy.ColumnMappings.Add("PositionState", "PositionState");
* sqlBulkCopy.ColumnMappings.Add("X", "X");
* sqlBulkCopy.ColumnMappings.Add("Y", "Y");
* sqlBulkCopy.ColumnMappings.Add("Z", "Z");
* sqlBulkCopy.ColumnMappings.Add("A", "A");
* sqlBulkCopy.ColumnMappings.Add("B", "B");
* sqlBulkCopy.ColumnMappings.Add("C", "C");
*
* //開始寫入
* try
* {
* sqlBulkCopy.WriteToServer(_dataTable);
* }
* catch(Exception ex)
* {
* Console.WriteLine(ex.Message);
* }
* }
* connection.Dispose();
*/
//將Datatable可以直接insert進Database
//對於中斷點可以再處理得更好 目前的test寫成了10秒內會輸入30萬筆左右的測試資料進資料庫
public void InsertTable(DataTable _datatable)
{
lock (DBlock)
{
//需要每個月初定期下載更新版本來延長license
MySqlConnection connection = establishConnection();
connection.Open();
DataTable dt = new DataTable("plc");
dt = _datatable;
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM plc", connection);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
da.Fill(dt);
var bulk = new BulkOperation(connection);
bulk.DestinationTableName = "plc";
bulk.BulkInsert(dt);
connection.Close();
}
}
/// <summary>Executes this object.</summary>
public void Execute()
{
if (IsExecuted)
{
return;
}
if (DataSource == null)
{
IsExecuted = true;
return;
}
var bulkOperation = new BulkOperation
{
Connection = (DbConnection)Connection
};
var mapperKey = "";
var enumerableDataSource = DataSource as IEnumerable <object>;
if (enumerableDataSource != null)
{
var first = enumerableDataSource.FirstOrDefault();
if (first == null)
{
IsExecuted = true;
return;
}
var enumerableFirst = first as IEnumerable <object>;
if (enumerableFirst != null)
{
// List<List<Entity>>
var list = enumerableDataSource.Where(x => x != null).Cast <IEnumerable <object> >().SelectMany(x => x).ToList();
var firstElement = list[0];
// GET mapper key
mapperKey = DapperPlusManager.GetFullMapperKey(firstElement.GetType(), Key);
// Convert DataSource to List<Entity>
var castMethod = typeof(Enumerable).GetMethod("Cast");
castMethod = castMethod.MakeGenericMethod(firstElement.GetType());
var toListMethod = typeof(Enumerable).GetMethod("ToList");
toListMethod = toListMethod.MakeGenericMethod(firstElement.GetType());
var obj = castMethod.Invoke(null, new[] { list });
obj = toListMethod.Invoke(null, new[] { obj });
DataSource = obj;
}
else if (DataSource.GetType().GetGenericArguments()[0] == typeof(object))
{
// List<object> => List<Entity>
var type = first.GetType();
// GET mapper key
mapperKey = DapperPlusManager.GetFullMapperKey(type, Key);
// Convert DataSource to List<Entity>
var castMethod = typeof(Enumerable).GetMethod("Cast");
castMethod = castMethod.MakeGenericMethod(type);
var toListMethod = typeof(Enumerable).GetMethod("ToList");
toListMethod = toListMethod.MakeGenericMethod(type);
var obj = castMethod.Invoke(null, new[] { DataSource });
obj = toListMethod.Invoke(null, new[] { obj });
DataSource = obj;
}
else
{
// List<Entity>
// GET mapper key
mapperKey = DapperPlusManager.GetFullMapperKey(first.GetType(), Key);
}
}
else
{
// item
// GET mapper key
mapperKey = DapperPlusManager.GetFullMapperKey(DataSource.GetType(), Key);
// CREATE list
var list = new List <object> {
DataSource
};
// Convert List<object> to List<Entity>
var castMethod = typeof(Enumerable).GetMethod("Cast");
castMethod = castMethod.MakeGenericMethod(DataSource.GetType());
var toListMethod = typeof(Enumerable).GetMethod("ToList");
toListMethod = toListMethod.MakeGenericMethod(DataSource.GetType());
var obj = castMethod.Invoke(null, new[] { list });
obj = toListMethod.Invoke(null, new[] { obj });
DataSource = obj;
}
DapperPlusEntityMapper config;
if (!DapperPlusManager.MapperCache.TryGetValue(mapperKey, out config))
{
var elementType = DataSource.GetType().GetElementType();
var constructor = typeof(DapperPlusEntityMapper <>).MakeGenericType(elementType).GetConstructor(new Type[0]);
config = (DapperPlusEntityMapper)constructor.Invoke(new object[0]);
}
bulkOperation.DataSource = DataSource;
bulkOperation.AllowDuplicateKeys = true;
bulkOperation.CaseSensitive = CaseSensitiveType.DestinationInsensitive;
if (Kind == DapperPlusActionKind.Insert)
{
ApplyConfig(bulkOperation, config._configInsert);
bulkOperation.BulkInsert();
}
else if (Kind == DapperPlusActionKind.Update)
{
ApplyConfig(bulkOperation, config._configInsert);
bulkOperation.BulkUpdate();
}
else if (Kind == DapperPlusActionKind.Delete)
{
ApplyConfig(bulkOperation, config._configInsert);
bulkOperation.BulkDelete();
}
else if (Kind == DapperPlusActionKind.Merge)
{
ApplyConfig(bulkOperation, config._configInsert);
bulkOperation.BulkMerge();
}
IsExecuted = true;
}
/// <summary>Executes this object.</summary>
public void Execute()
{
if (IsExecuted) return;
if (DataSource == null)
{
IsExecuted = true;
return;
}
var bulkOperation = new BulkOperation
{
Connection = (DbConnection) Connection
};
var mapperKey = "";
var enumerableDataSource = DataSource as IEnumerable<object>;
if (enumerableDataSource != null)
{
var first = enumerableDataSource.FirstOrDefault();
if (first == null)
{
IsExecuted = true;
return;
}
var enumerableFirst = first as IEnumerable<object>;
if (enumerableFirst != null)
{
// List<List<Entity>>
var list = enumerableDataSource.Where(x => x != null).Cast<IEnumerable<object>>().SelectMany(x => x).ToList();
var firstElement = list[0];
// GET mapper key
mapperKey = DapperPlusManager.GetFullMapperKey(firstElement.GetType(), Key);
// Convert DataSource to List<Entity>
var castMethod = typeof (Enumerable).GetMethod("Cast");
castMethod = castMethod.MakeGenericMethod(firstElement.GetType());
var toListMethod = typeof (Enumerable).GetMethod("ToList");
toListMethod = toListMethod.MakeGenericMethod(firstElement.GetType());
var obj = castMethod.Invoke(null, new[] {list});
obj = toListMethod.Invoke(null, new[] {obj});
DataSource = obj;
}
else if (DataSource.GetType().GetGenericArguments()[0] == typeof (object))
{
// List<object> => List<Entity>
var type = first.GetType();
// GET mapper key
mapperKey = DapperPlusManager.GetFullMapperKey(type, Key);
// Convert DataSource to List<Entity>
var castMethod = typeof (Enumerable).GetMethod("Cast");
castMethod = castMethod.MakeGenericMethod(type);
var toListMethod = typeof (Enumerable).GetMethod("ToList");
toListMethod = toListMethod.MakeGenericMethod(type);
var obj = castMethod.Invoke(null, new[] {DataSource});
obj = toListMethod.Invoke(null, new[] {obj});
DataSource = obj;
}
else
{
// List<Entity>
// GET mapper key
mapperKey = DapperPlusManager.GetFullMapperKey(first.GetType(), Key);
}
}
else
{
// item
// GET mapper key
mapperKey = DapperPlusManager.GetFullMapperKey(DataSource.GetType(), Key);
// CREATE list
var list = new List<object> {DataSource};
// Convert List<object> to List<Entity>
var castMethod = typeof (Enumerable).GetMethod("Cast");
castMethod = castMethod.MakeGenericMethod(DataSource.GetType());
var toListMethod = typeof (Enumerable).GetMethod("ToList");
toListMethod = toListMethod.MakeGenericMethod(DataSource.GetType());
var obj = castMethod.Invoke(null, new[] {list});
obj = toListMethod.Invoke(null, new[] {obj});
DataSource = obj;
}
DapperPlusEntityMapper config;
if (!DapperPlusManager.MapperCache.TryGetValue(mapperKey, out config))
{
var elementType = DataSource.GetType().GetElementType();
var constructor = typeof (DapperPlusEntityMapper<>).MakeGenericType(elementType).GetConstructor(new Type[0]);
config = (DapperPlusEntityMapper) constructor.Invoke(new object[0]);
}
bulkOperation.DataSource = DataSource;
bulkOperation.AllowDuplicateKeys = true;
bulkOperation.CaseSensitive = CaseSensitiveType.DestinationInsensitive;
if (Kind == DapperPlusActionKind.Insert)
{
ApplyConfig(bulkOperation, config._configInsert);
bulkOperation.BulkInsert();
}
else if (Kind == DapperPlusActionKind.Update)
{
ApplyConfig(bulkOperation, config._configInsert);
bulkOperation.BulkUpdate();
}
else if (Kind == DapperPlusActionKind.Delete)
{
ApplyConfig(bulkOperation, config._configInsert);
bulkOperation.BulkDelete();
}
else if (Kind == DapperPlusActionKind.Merge)
{
ApplyConfig(bulkOperation, config._configInsert);
bulkOperation.BulkMerge();
}
IsExecuted = true;
}
//Called upon board creation, saves squares to DB
public bool SaveBoard(Model sudoku)
{
DataTable gridDataTable = new DataTable("grid_square");
gridDataTable.Columns.Add(new DataColumn("gridID", typeof(string)));
gridDataTable.Columns.Add(new DataColumn("columnNum", typeof(string)));
gridDataTable.Columns.Add(new DataColumn("rowNum", typeof(string)));
gridDataTable.Columns.Add(new DataColumn("entryNum", typeof(string)));
DataTable blankDataTable = new DataTable("start_empty");
blankDataTable.Columns.Add(new DataColumn("gridID", typeof(string)));
blankDataTable.Columns.Add(new DataColumn("columnNum", typeof(string)));
blankDataTable.Columns.Add(new DataColumn("rowNum", typeof(string)));
for (int cIndex = 0; cIndex < sudoku.SolutionBoard.GetLength(0); cIndex++)
{
for (int rIndex = 0; rIndex < sudoku.SolutionBoard.GetLength(1); rIndex++)
{
//Add each square to the grid data table
gridDataTable.Rows.Add(new string[]
{ sudoku.GameId.ToString(), cIndex.ToString(), rIndex.ToString(), sudoku.SolutionBoard[cIndex, rIndex].Substring(0, 1) });
//If the square is blank in the play board, add it to the blank data table
if (sudoku.PlayBoard[cIndex, rIndex] == "--")
{
blankDataTable.Rows.Add(new string[]
{ sudoku.GameId.ToString(), cIndex.ToString(), rIndex.ToString() });
}
}
}
using (MySqlConnection connection = new MySqlConnection(Helper.ConnectionVal("SudokuCloudDB")))
{
connection.Open();
connection.Query($"INSERT INTO full_grid (gridID, playerID, difficulty, hintsRemaining) VALUES ({sudoku.GameId}, 1, {sudoku.Difficulty}, {sudoku.Hints})");
var bulkGrid = new BulkOperation(connection);
bulkGrid.BulkInsert(gridDataTable);
var bulkBlank = new BulkOperation(connection);
bulkBlank.BulkInsert(blankDataTable);
connection.Close();
}
//Iterate over solution board and call SaveSquare on each square
//for (int cIndex = 0; cIndex < sudoku.SolutionBoard.GetLength(0); cIndex++)
//{
// for (int rIndex = 0; rIndex < sudoku.SolutionBoard.GetLength(1); rIndex++)
// {
// try
// {
// int entry = int.Parse(sudoku.SolutionBoard[cIndex, rIndex].Substring(0, 1));
// if (!SaveSquare(sudoku, false, cIndex, rIndex, entry))
// {
// return false;
// }
// //Check this location in the play board. If it's blank
// if (sudoku.PlayBoard[cIndex, rIndex] == "--")
// {
// SaveSquare(sudoku, true, cIndex, rIndex, 0);
// }
// }
// catch (FormatException)
// {
// Console.WriteLine("ERROR: Fault saving grid entry. " + cIndex + "-" + rIndex + " is not a number.");
// return false;
// }
// }
//}
return(true);
}
/// <summary>Executes this object.</summary>
public void Execute()
{
if (IsExecuted)
{
return;
}
if (DataSource == null)
{
IsExecuted = true;
return;
}
var bulkOperation = new BulkOperation
{
Connection = (DbConnection)Connection
};
var mapperKey = "";
var enumerableDataSource = DataSource as IEnumerable <object>;
if (enumerableDataSource != null)
{
var first = enumerableDataSource.FirstOrDefault();
if (first == null)
{
IsExecuted = true;
return;
}
var enumerableFirst = first as IEnumerable <object>;
if (enumerableFirst != null)
{
// List<List<Entity>>
var list = enumerableDataSource.Where(x => x != null).Cast <IEnumerable <object> >().SelectMany(x => x).ToList();
var firstElement = list[0];
// GET mapper key
mapperKey = DapperPlusManager.GetFullMapperKey(firstElement.GetType(), Key);
// Convert DataSource to List<Entity>
var castMethod = typeof(Enumerable).GetMethod("Cast");
castMethod = castMethod.MakeGenericMethod(firstElement.GetType());
var toListMethod = typeof(Enumerable).GetMethod("ToList");
toListMethod = toListMethod.MakeGenericMethod(firstElement.GetType());
var obj = castMethod.Invoke(null, new[] { list });
obj = toListMethod.Invoke(null, new[] { obj });
DataSource = obj;
}
else if (DataSource.GetType().GetGenericArguments()[0] == typeof(object))
{
// List<object> => List<Entity>
var type = first.GetType();
// GET mapper key
mapperKey = DapperPlusManager.GetFullMapperKey(type, Key);
// Convert DataSource to List<Entity>
var castMethod = typeof(Enumerable).GetMethod("Cast");
castMethod = castMethod.MakeGenericMethod(type);
var toListMethod = typeof(Enumerable).GetMethod("ToList");
toListMethod = toListMethod.MakeGenericMethod(type);
var obj = castMethod.Invoke(null, new[] { DataSource });
obj = toListMethod.Invoke(null, new[] { obj });
DataSource = obj;
}
else
{
// List<Entity>
// GET mapper key
mapperKey = DapperPlusManager.GetFullMapperKey(first.GetType(), Key);
}
}
else
{
// item
// GET mapper key
mapperKey = DapperPlusManager.GetFullMapperKey(DataSource.GetType(), Key);
// CREATE list
var list = new List <object> {
DataSource
};
// Convert List<object> to List<Entity>
var castMethod = typeof(Enumerable).GetMethod("Cast");
castMethod = castMethod.MakeGenericMethod(DataSource.GetType());
var toListMethod = typeof(Enumerable).GetMethod("ToList");
toListMethod = toListMethod.MakeGenericMethod(DataSource.GetType());
var obj = castMethod.Invoke(null, new[] { list });
obj = toListMethod.Invoke(null, new[] { obj });
DataSource = obj;
}
DapperPlusEntityMapper config;
if (!DapperPlusManager.MapperCache.TryGetValue(mapperKey, out config))
{
// CHECK for Entity Framework Proxy Type
if (mapperKey.StartsWith("zzz_proxy;"))
{
var mapperProxy = new List <DapperPlusEntityMapper>();
// Try to find if one mapping could correspond
foreach (var keyValue in DapperPlusManager.MapperCache)
{
var key = keyValue.Key;
var prefix = string.IsNullOrEmpty(Key) ? "zzz_null" : Key;
// MUST start with the same suffix
if (!key.StartsWith(prefix))
{
continue;
}
var suffix = key.Split('.').Last().Split('+').Last();
var mapperSuffix = mapperKey.Split(';').Last();
if (suffix.Length < 20)
{
// MUST BE Equal
if (suffix != mapperSuffix)
{
continue;
}
}
else
{
// MUST START with same name but only one!
if (!suffix.StartsWith(mapperSuffix))
{
continue;
}
}
mapperProxy.Add(keyValue.Value);
}
if (mapperProxy.Count == 1)
{
config = mapperProxy[0];
}
}
if (config == null)
{
if (DapperPlusManager.ThrowErrorIfNotMapped)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("Mapping Not Found!");
sb.AppendLine("Current MapperKey: " + mapperKey);
sb.AppendLine("Possible Mapping:");
foreach (var keyValue in DapperPlusManager.MapperCache)
{
sb.AppendLine(" - " + keyValue.Key);
}
throw new Exception(sb.ToString());
}
else
{
var type = DataSource.GetType();
var elementType = type.GetGenericArguments()[0];
var constructor = typeof(DapperPlusEntityMapper <>).MakeGenericType(elementType).GetConstructor(new Type[0]);
config = (DapperPlusEntityMapper)constructor.Invoke(new object[0]);
}
}
}
bulkOperation._isDapperPlus = true;
bulkOperation.DataSource = DataSource;
bulkOperation.AllowDuplicateKeys = true;
bulkOperation.CaseSensitive = CaseSensitiveType.DestinationInsensitive;
if (Kind == DapperPlusActionKind.Insert)
{
ApplyConfig(bulkOperation, config._configInsert);
bulkOperation.BulkInsert();
}
else if (Kind == DapperPlusActionKind.Update)
{
ApplyConfig(bulkOperation, config._configInsert);
bulkOperation.BulkUpdate();
}
else if (Kind == DapperPlusActionKind.Delete)
{
ApplyConfig(bulkOperation, config._configInsert);
bulkOperation.BulkDelete();
}
else if (Kind == DapperPlusActionKind.Merge)
{
ApplyConfig(bulkOperation, config._configInsert);
bulkOperation.BulkMerge();
}
IsExecuted = true;
}
