public void Add_HelperOverloads_ItemsAddedAsExpected()
{
SqlBulkCopyColumnMappingCollection collection = CreateCollection();
SqlBulkCopyColumnMapping item;
item = collection.Add(3, 4);
Assert.NotNull(item);
Assert.Equal(3, item.SourceOrdinal);
Assert.Equal(4, item.DestinationOrdinal);
item = collection.Add(5, "destination");
Assert.NotNull(item);
Assert.Equal(5, item.SourceOrdinal);
Assert.Equal("destination", item.DestinationColumn);
item = collection.Add("source", 6);
Assert.NotNull(item);
Assert.Equal("source", item.SourceColumn);
Assert.Equal(6, item.DestinationOrdinal);
item = collection.Add("src", "dest");
Assert.NotNull(item);
Assert.Equal("src", item.SourceColumn);
Assert.Equal("dest", item.DestinationColumn);
}
public void Methods_NullParameterPassed_ThrowsArgumentNullException()
{
SqlBulkCopyColumnMappingCollection collection = CreateCollection();
collection.Add(new SqlBulkCopyColumnMapping());
Assert.Throws <ArgumentNullException>(() => collection.CopyTo(null, 0));
// Passing null to the public Add method should really throw ArgumentNullException
// (which would be consistent with the explicit implementation of IList.Add), but
// the full framework does not check for null in the public Add method. Instead it
// accesses the parameter without first checking for null, resulting in
// NullReferenceExpcetion being thrown.
Assert.Throws <NullReferenceException>(() => collection.Add(null));
// Passing null to the public Insert and Remove methods should really throw
// ArgumentNullException (which would be consistent with the explicit
// implementations of IList.Insert and IList.Remove), but the full framework
// does not check for null in these methods.
collection.Insert(0, null);
collection.Remove(null);
IList list = collection;
Assert.Throws <ArgumentNullException>(() => list[0] = null);
Assert.Throws <ArgumentNullException>(() => list.Add(null));
Assert.Throws <ArgumentNullException>(() => list.CopyTo(null, 0));
Assert.Throws <ArgumentNullException>(() => list.Insert(0, null));
Assert.Throws <ArgumentNullException>(() => list.Remove(null));
}
private static async Task TestAsync(string srcConstr, string dstConstr, string dstTable, SemaphoreSlim outputSemaphore)
{
DataSet dataset;
SqlDataAdapter adapter;
DataTable datatable;
using (SqlConnection dstConn = new SqlConnection(dstConstr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
Helpers.TryExecute(dstCmd, "create table " + dstTable + " (col1 int, col2 nvarchar(20), col3 nvarchar(10), col4 datetime)");
using (SqlConnection srcConn = new SqlConnection(srcConstr))
using (SqlCommand srcCmd = new SqlCommand("select * from employees", srcConn))
{
srcConn.Open();
dataset = new DataSet("MyDataSet");
adapter = new SqlDataAdapter(srcCmd);
adapter.Fill(dataset);
datatable = dataset.Tables[0];
datatable.Rows[0].BeginEdit();
datatable.Rows[0][0] = 333;
datatable.Rows[0].EndEdit();
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn))
{
bulkcopy.DestinationTableName = dstTable;
bulkcopy.BatchSize = 7;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add(0, "col1");
ColumnMappings.Add(1, "col2");
ColumnMappings.Add(2, "col3");
bulkcopy.WriteToServer(datatable, DataRowState.Unchanged);
datatable.Rows.GetEnumerator().Reset();
await bulkcopy.WriteToServerAsync(datatable, DataRowState.Modified);
datatable.Rows.GetEnumerator().Reset();
await bulkcopy.WriteToServerAsync(datatable, DataRowState.Deleted);
bulkcopy.Close();
}
await outputSemaphore.WaitAsync();
Helpers.VerifyResults(dstConn, dstTable, 4, 18);
}
}
finally
{
Helpers.TryExecute(dstCmd, "drop table " + dstTable);
}
}
}
public static void Test(string srcConstr, string dstConstr, string dstTable)
{
DataSet dataset;
SqlDataAdapter adapter;
DataTable datatable;
DataRow[] rows;
using (SqlConnection dstConn = new SqlConnection(dstConstr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
Helpers.TryExecute(dstCmd, "create table " + dstTable + " (orderid int, customerid nchar(5), rdate datetime, freight money, shipname nvarchar(40))");
using (SqlConnection srcConn = new SqlConnection(srcConstr))
using (SqlCommand srcCmd = new SqlCommand("select top 100 * from orders", srcConn))
{
srcConn.Open();
dataset = new DataSet("MyDataSet");
adapter = new SqlDataAdapter(srcCmd);
adapter.Fill(dataset);
datatable = dataset.Tables[0];
rows = new DataRow[datatable.Rows.Count];
for (int i = 0; i < rows.Length; i++)
{
rows[i] = datatable.Rows[i];
}
}
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn))
{
bulkcopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnRowCopied);
bulkcopy.DestinationTableName = dstTable;
bulkcopy.NotifyAfter = 50;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add(0, "orderid");
ColumnMappings.Add(1, "customerid");
ColumnMappings.Add(4, "rdate");
ColumnMappings.Add(7, "freight");
ColumnMappings.Add(8, "shipname");
bulkcopy.WriteToServer(rows);
bulkcopy.SqlRowsCopied -= new SqlRowsCopiedEventHandler(OnRowCopied);
}
Helpers.VerifyResults(dstConn, dstTable, 5, 100);
}
finally
{
Helpers.TryExecute(dstCmd, "drop table " + dstTable);
}
}
}
private static async Task TestAsync(string srcConstr, string dstConstr, string dstTable, SemaphoreSlim outputSemaphore)
{
DataSet dataset;
SqlDataAdapter adapter;
DataTable datatable;
DataRow[] rows;
using (SqlConnection dstConn = new SqlConnection(dstConstr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
Helpers.TryExecute(dstCmd, "create table " + dstTable + " (col1 int, col2 nvarchar(20), col3 nvarchar(10), col4 datetime)");
using (SqlConnection srcConn = new SqlConnection(srcConstr))
using (SqlCommand srcCmd = new SqlCommand("select * from employees", srcConn))
{
srcConn.Open();
dataset = new DataSet("MyDataSet");
adapter = new SqlDataAdapter(srcCmd);
adapter.Fill(dataset);
datatable = dataset.Tables[0];
rows = new DataRow[datatable.Rows.Count];
for (int i = 0; i < rows.Length; i++)
{
rows[i] = datatable.Rows[i];
}
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn))
{
bulkcopy.DestinationTableName = dstTable;
bulkcopy.BatchSize = 4;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add(0, "col1");
ColumnMappings.Add(2, "col3");
await bulkcopy.WriteToServerAsync(rows);
bulkcopy.Close();
}
await outputSemaphore.WaitAsync();
Helpers.VerifyResults(dstConn, dstTable, 4, 9);
}
}
finally
{
Helpers.TryExecute(dstCmd, "drop table " + dstTable);
}
}
}
public static void Test(string constr, string dstTable)
{
string srctable = "[" + dstTable + " src]";
dstTable = "[" + dstTable + "]";
string[] epilogue =
{
"create table " + srctable + "([col 1] int primary key, [col 2] text)",
"insert into " + srctable + " values (33, 'Michael')",
"create table " + dstTable + "([col 1] int primary key, [col 2] text)",
};
string[] prologue =
{
"drop table " + srctable,
"drop table " + dstTable,
};
using (SqlConnection dstConn = new SqlConnection(constr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
Helpers.ProcessCommandBatch(typeof(SqlConnection), constr, epilogue);
using (SqlConnection srcConn = new SqlConnection(constr))
using (SqlCommand srcCmd = new SqlCommand(string.Format("select * from {0} ", srctable), srcConn))
{
srcConn.Open();
using (DbDataReader reader = srcCmd.ExecuteReader())
{
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn))
{
bulkcopy.DestinationTableName = dstTable;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add("[col 1]", "col 1");
ColumnMappings.Add("col 2", "[col 2]");
bulkcopy.WriteToServer(reader);
DataTestUtility.AssertEqualsWithDescription(bulkcopy.RowsCopied, 1, "Unexpected number of rows.");
}
Helpers.VerifyResults(dstConn, dstTable, 2, 1);
}
}
}
finally
{
Helpers.ProcessCommandBatch(typeof(SqlConnection), constr, prologue);
}
}
}
public static void Test(string constr, string srctable, string dstTable)
{
using (SqlConnection dstConn = new SqlConnection(constr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
// create the source table
Helpers.TryExecute(dstCmd, "create table " + srctable + " (col1 int , col2 int, col3 text)");
Helpers.TryExecute(dstCmd, "insert into " + srctable + " values (33, 498, 'Michael')");
Helpers.TryExecute(dstCmd, "insert into " + srctable + " values (34, 499, 'Astrid')");
Helpers.TryExecute(dstCmd, "insert into " + srctable + " values (65, 500, 'alles Käse')");
Helpers.TryExecute(dstCmd, "create table " + dstTable + " (col1 int primary key, col2 int CONSTRAINT CK_" + dstTable + " CHECK (col2 < 500), col3 text)");
using (SqlConnection srcConn = new SqlConnection(constr))
using (SqlCommand srcCmd = new SqlCommand("select * from " + srctable, srcConn))
{
srcConn.Open();
using (DbDataReader reader = srcCmd.ExecuteReader())
{
try
{
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn, SqlBulkCopyOptions.CheckConstraints, null))
{
bulkcopy.DestinationTableName = dstTable;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add("col1", "col1");
ColumnMappings.Add("col2", "col2");
ColumnMappings.Add("col3", "col3");
bulkcopy.WriteToServer(reader);
}
}
catch (SqlException sqlEx)
{
// Error 547 == The %ls statement conflicted with the %ls constraint "%.*ls".
DataTestUtility.AssertEqualsWithDescription(547, sqlEx.Number, "Unexpected error number.");
}
}
}
}
finally
{
Helpers.TryExecute(dstCmd, "drop table " + dstTable);
Helpers.TryExecute(dstCmd, "drop table " + srctable);
}
}
}
internal static void InnerTest(SqlRowsCopiedEventHandler eventHandler)
{
bool hitException = false;
try
{
if (null == _tx || null == _tx.Connection)
{
_tx = _dstConn.BeginTransaction();
_dstcmd.Transaction = _tx;
_dstcmd.ExecuteNonQuery();
}
SqlBulkCopy bulkcopy;
using (bulkcopy = new SqlBulkCopy(_dstConn, SqlBulkCopyOptions.Default, _tx))
{
bulkcopy.SqlRowsCopied += eventHandler;
bulkcopy.DestinationTableName = _dstTable;
bulkcopy.NotifyAfter = 10;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add(0, "orderid");
ColumnMappings.Add(1, "customerid");
ColumnMappings.Add(4, "rdate");
ColumnMappings.Add(7, "freight");
ColumnMappings.Add(8, "shipname");
bulkcopy.WriteToServer(_dataTable);
bulkcopy.SqlRowsCopied -= eventHandler;
_tx.Commit();
}
}
catch (Exception e)
{
while (null != e.InnerException)
{
e = e.InnerException;
}
Assert.True(e is InvalidOperationException, "Unexpected exception type: " + e.GetType());
Assert.True(e.Message.Contains(expectedErrorMsg), string.Format("Incorrect error message. Expected: {0}. Actual: {1}.", expectedErrorMsg, e.Message));
_tx.Dispose();
hitException = true;
}
Assert.True(hitException, "Did not encounter expected exception.");
}
public int Load()
{
if (_dataTable == null || _dataTable.Rows.Count < 1) // nothing to load
{
return(0);
}
using (SqlBulkCopy bc = new SqlBulkCopy(_context.Connection, SqlBulkCopyOptions.Default, null))
{
bc.BatchSize = _dataTable.Rows.Count;
bc.DestinationTableName = _context.TableSchema.TableName;
bc.BulkCopyTimeout = 0;
SqlBulkCopyColumnMappingCollection colMappings = bc.ColumnMappings;
int columnCount = _context.TableSchema.ColumnNameList.Count;
for (int colIndex = 0; colIndex < columnCount; colIndex++)
{
if (_context.TableSchema.ReadOnlyColumnIndexes.BinarySearch(colIndex) > -1)
{
continue;
}
colMappings.Add(colIndex, colIndex);
}
bc.WriteToServer(_dataTable);
bc.Close();
}
return(_dataTable.Rows.Count);
}
/// <summary>
/// 实体字段映射数据库列
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="mappings"></param>
private void GenerateColumnMappings <TBulk>(SqlBulkCopyColumnMappingCollection mappings) where TBulk : class
{
var properties = typeof(TBulk).GetProperties();
foreach (var property in properties)
{
if (property.GetCustomAttributes(typeof(KeyAttribute), true).Any())
{
mappings.Add(new SqlBulkCopyColumnMapping(property.Name, typeof(T).Name + property.Name));
}
else
{
mappings.Add(new SqlBulkCopyColumnMapping(property.Name, property.Name));
}
}
}
/// <summary>
/// 数据表映射
/// </summary>
/// <param name="columnMapping"></param>
/// <param name="bigDataTable"></param>
private static void Mapping(SqlBulkCopyColumnMappingCollection columnMapping, DataTable bigDataTable)
{
foreach (DataColumn propertyInfo in bigDataTable.Columns)
{
columnMapping.Add(propertyInfo.ColumnName, propertyInfo.ColumnName);
}
}
/// <summary>
/// モデルのプロパティと宛先項目のマッピングを行います。
/// </summary>
/// <param name="columnMappings">マッピングコレクション。</param>
public void SetColumnMappings(SqlBulkCopyColumnMappingCollection columnMappings)
{
foreach (var property in this.Properties)
{
columnMappings.Add(property.Name, GetColumnName(property));
}
}
private void GenerateColumnMappings <T, TKey>(SqlBulkCopyColumnMappingCollection mappings)
where T : BaseModel <TKey>
{
var properties = typeof(T).GetProperties();
foreach (var property in properties)
{
if (property.GetCustomAttributes <KeyAttribute>().Any())
{
mappings.Add(new SqlBulkCopyColumnMapping(property.Name, typeof(T).Name + property.Name));
}
else
{
mappings.Add(new SqlBulkCopyColumnMapping(property.Name, property.Name));
}
}
}
public static void AddRange(this SqlBulkCopyColumnMappingCollection columnMappings,
IEnumerable <SqlBulkCopyColumnMapping> mappings)
{
foreach (var mapping in mappings)
{
columnMappings.Add(mapping);
}
}
public static void Test(string srcConstr, string dstConstr, string dstTable)
{
using (SqlConnection dstConn = new SqlConnection(dstConstr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
Helpers.Execute(dstCmd, "create table " + dstTable + " (orderid int, customerid nchar(5), rdate datetime, freight money, shipname nvarchar(40))");
using (SqlConnection srcConn = new SqlConnection(srcConstr))
using (SqlCommand srcCmd = new SqlCommand("select top 100 * from orders", srcConn))
{
srcConn.Open();
using (DbDataReader reader = srcCmd.ExecuteReader())
using (bulkcopy = new SqlBulkCopy(dstConn, SqlBulkCopyOptions.UseInternalTransaction, null))
{
bulkcopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnRowCopied);
bulkcopy.DestinationTableName = dstTable;
bulkcopy.NotifyAfter = 50;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add("OrderID", "orderid");
ColumnMappings.Add("CustomerID", "customerid");
ColumnMappings.Add("RequiredDate", "rdate");
ColumnMappings.Add("Freight", "freight");
ColumnMappings.Add("ShipName", "shipname");
bulkcopy.NotifyAfter = 3;
DataTestUtility.AssertThrowsWrapper <OperationAbortedException>(() => bulkcopy.WriteToServer(reader));
bulkcopy.SqlRowsCopied -= new SqlRowsCopiedEventHandler(OnRowCopied);
bulkcopy.Close();
}
}
}
finally
{
Helpers.Execute(dstCmd, "drop table " + dstTable);
}
}
}
public static void Test(string srcConstr, string dstConstr, string dstTable)
{
using (SqlConnection dstConn = new SqlConnection(dstConstr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
Helpers.TryExecute(dstCmd, "create table " + dstTable + " (col1 int, col2 nvarchar(20), col3 nvarchar(10), col4 datetime)");
using (SqlConnection srcConn = new SqlConnection(srcConstr))
using (SqlCommand srcCmd = new SqlCommand("select * from employees", srcConn))
{
srcConn.Open();
using (DbDataReader reader = srcCmd.ExecuteReader())
{
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn))
{
bulkcopy.DestinationTableName = dstTable;
bulkcopy.BatchSize = 6;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add("EmployeeID", "col1");
ColumnMappings.Add("BirthDate", "col4");
ColumnMappings.Add("FirstName", "col2");
ColumnMappings.Add("LastName", "col3");
bulkcopy.WriteToServer(reader);
}
Helpers.VerifyResults(dstConn, dstTable, 4, 9);
}
}
}
finally
{
Helpers.TryExecute(dstCmd, "drop table " + dstTable);
}
}
}
public static void Test(string srcConstr, string dstConstr, string targetTable)
{
using (SqlConnection dstConn = new SqlConnection(dstConstr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
using (SqlConnection srcConn = new SqlConnection(srcConstr))
using (SqlCommand srcCmd = new SqlCommand("select top 5 EmployeeID, LastName, FirstName from employees", srcConn))
{
srcConn.Open();
using (DbDataReader reader = srcCmd.ExecuteReader())
{
bool hitException = false;
try
{
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn))
{
bulkcopy.DestinationTableName = targetTable;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add("EmployeeID", "col1");
ColumnMappings.Add("LastName", "col2");
ColumnMappings.Add("FirstName", "col3");
bulkcopy.WriteToServer(reader);
bulkcopy.Close();
}
}
catch (InvalidOperationException ioe)
{
string expectedErrorMsg = string.Format(SystemDataResourceManager.Instance.SQL_BulkLoadInvalidDestinationTable, targetTable);
Assert.True(ioe.Message.Contains(expectedErrorMsg), "Unexpected error message: " + ioe.Message);
hitException = true;
}
Assert.True(hitException, "Did not get any exceptions!");
}
}
}
}
public void GetEnumerator_ModifiedCollectionDuringEnumeration_ThrowsInvalidOperationException()
{
SqlBulkCopyColumnMappingCollection collection = CreateCollection();
IEnumerator e = collection.GetEnumerator();
collection.Add(0, 0);
// Collection changed.
Assert.Throws <InvalidOperationException>(() => e.MoveNext());
Assert.Throws <InvalidOperationException>(() => e.Reset());
}
public void Add_AddItems_ItemsAddedAsEpected()
{
SqlBulkCopyColumnMappingCollection collection = CreateCollection();
var item1 = new SqlBulkCopyColumnMapping(0, 0);
Assert.Same(item1, collection.Add(item1));
Assert.Same(item1, collection[0]);
var item2 = new SqlBulkCopyColumnMapping(1, 1);
Assert.Same(item2, collection.Add(item2));
Assert.Same(item2, collection[1]);
IList list = CreateCollection();
int index = list.Add(item1);
Assert.Equal(0, index);
Assert.Same(item1, list[0]);
index = list.Add(item2);
Assert.Equal(1, index);
Assert.Same(item2, list[1]);
}
/// <summary>
/// 设置SqlBulkCopy中的ColumnMappings
/// </summary>
/// <param name="columnMappings">SqlBulkCopyColumnMappingCollection对象</param>
/// <param name="metaDataTable">MetaDataTable对象</param>
/// <param name="columnNames">目标表列名集合</param>
private void SetColumnMappings(SqlBulkCopyColumnMappingCollection columnMappings, MetaDataTable metaDataTable, params string[] columnNames)
{
if (columnNames == null || columnNames.Length == 0)
{
foreach (var kp in metaDataTable.Columns)
{
columnMappings.Add(kp.Key, kp.Value.Name);
}
return;
}
foreach (var columnName in columnNames)
{
string colName = columnName.Trim().ToLower();
if (!metaDataTable.Columns.ContainsKey(colName))
{
continue;
}
columnMappings.Add(colName, metaDataTable.Columns[colName].Name);
}
}
public void Add_InvalidItems_ThrowsInvalidOperationException()
{
SqlBulkCopyColumnMappingCollection collection = CreateCollection();
Assert.Throws <InvalidOperationException>(() => collection.Add(new SqlBulkCopyColumnMapping {
SourceColumn = null
}));
Assert.Throws <InvalidOperationException>(() => collection.Add(new SqlBulkCopyColumnMapping {
DestinationColumn = null
}));
// The explicitly implemented IList.Add should really throw InvalidOperationException to match the public
// Add method behavior, but does not throw in the full framework.
IList list = CreateCollection();
Assert.Equal(0, list.Add(new SqlBulkCopyColumnMapping {
SourceColumn = null
}));
Assert.Equal(1, list.Add(new SqlBulkCopyColumnMapping {
DestinationColumn = null
}));
}
public static void Test(string srcConstr, string dstConstr, string dstTable)
{
using (SqlConnection dstConn = new SqlConnection(dstConstr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
Helpers.TryExecute(dstCmd, "create table " + dstTable + " (col1 int, col2 nvarchar(20), col3 nvarchar(10))");
using (SqlConnection srcConn = new SqlConnection(srcConstr))
using (SqlCommand srcCmd = new SqlCommand("select top 5 * from employees", srcConn))
{
srcConn.Open();
using (DbDataReader reader = srcCmd.ExecuteReader())
{
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn))
{
bulkcopy.DestinationTableName = dstTable;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add("EmployeeID", "col1");
ColumnMappings.Add("LastName", "col2");
ColumnMappings.Add("FirstName", "col3");
bulkcopy.WriteToServer(reader);
DataTestUtility.AssertEqualsWithDescription(bulkcopy.RowsCopied, 5, "Unexpected number of rows.");
}
Helpers.VerifyResults(dstConn, dstTable, 3, 5);
}
}
}
finally
{
Helpers.TryExecute(dstCmd, "drop table " + dstTable);
}
}
}
private static SqlBulkCopyColumnMappingCollection CreateCollection(params SqlBulkCopyColumnMapping[] mappings)
{
Debug.Assert(mappings != null);
SqlBulkCopyColumnMappingCollection collection = CreateCollection();
foreach (SqlBulkCopyColumnMapping mapping in mappings)
{
Debug.Assert(mapping != null);
collection.Add(mapping);
}
return(collection);
}
public StrictTypeMappingInfoProvider(Type type, SqlBulkCopyColumnMappingCollection mappingsCollection)
{
_itemProperties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
if (_itemProperties == null || _itemProperties.Any() == false)
{
throw new InvalidOperationException("Cannot collect properties from object");
}
MappingsCollection = mappingsCollection;
for (var i = 0; i < _itemProperties.Length; i++)
{
MappingsCollection.Add(new SqlBulkCopyColumnMapping(i, _itemProperties[i].Name));
}
}
public static void Test(string srcConstr, string dstConstr, string dstTable)
{
using (SqlConnection dstConn = new SqlConnection(dstConstr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
Helpers.TryExecute(dstCmd, "create table " + dstTable + " (col1 int, col3 nvarchar(10))");
using (SqlConnection srcConn = new SqlConnection(srcConstr))
using (SqlCommand srcCmd = new SqlCommand("select top 5 EmployeeID, LastName, FirstName from employees", srcConn))
{
srcConn.Open();
using (DbDataReader reader = srcCmd.ExecuteReader())
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn))
{
bulkcopy.DestinationTableName = dstTable;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add("EmployeeID", "col1");
ColumnMappings.Add("LastName", "col2"); // this column does not exist
ColumnMappings.Add("FirstName", "col3");
string errorMsg = SystemDataResourceManager.Instance.SQL_BulkLoadNonMatchingColumnMapping;
DataTestUtility.AssertThrowsWrapper <InvalidOperationException>(() => bulkcopy.WriteToServer(reader), exceptionMessage: errorMsg);
}
}
}
finally
{
Helpers.TryExecute(dstCmd, "drop table " + dstTable);
}
}
}
public static void Test(string srcconstr, string dstconstr, string srctable, string dsttable)
{
using SqlConnection destConn = new(dstconstr);
destConn.Open();
using SqlCommand dstcmd = destConn.CreateCommand();
Helpers.TryExecute(dstcmd, "create table " + srctable + " (col1 int, col2 text, col3 text)");
Helpers.TryExecute(dstcmd, "insert into " + srctable + "(col1, col3) values (1, 'Michael')");
Helpers.TryExecute(dstcmd, "insert into " + srctable + "(col1, col2, col3) values (2, 'Quark', 'Astrid')");
Helpers.TryExecute(dstcmd, "insert into " + srctable + "(col1, col2) values (66, 'K�se');");
Helpers.TryExecute(dstcmd, "create table " + dsttable + " (col1 int identity(1,1), col2 text default 'Jogurt', col3 text)");
using SqlConnection sourceConn = new(srcconstr);
sourceConn.Open();
using SqlCommand srccmd = new("select * from " + srctable, sourceConn);
using IDataReader reader = srccmd.ExecuteReader();
try
{
using SqlBulkCopy bulkcopy = new(destConn, SqlBulkCopyOptions.KeepNulls, null);
bulkcopy.DestinationTableName = dsttable;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add("col1", "col1");
ColumnMappings.Add("col2", "col2");
ColumnMappings.Add("col3", "col3");
bulkcopy.WriteToServer(reader);
Helpers.VerifyResults(destConn, dsttable, 3, 3);
}
finally
{
Helpers.TryDropTable(dstconstr, srctable);
Helpers.TryDropTable(dstconstr, dsttable);
}
}
// public void Load(DataTable data) {
// var tableName = "TestTable";
// var databaseName = "TestDB";
// var serverName = ".\\sqlexpress";
// var connStr = $"Data Source={serverName}; Database={databaseName}; Integrated Security=SSPI;";
//
//
// using (var conn = new SqlConnection(connStr)) {
// conn.Open();
// using (var bulk = new SqlBulkCopy(conn)) {
// bulk.DestinationTableName = tableName;
// AddtMappings( bulk.ColumnMappings);
// bulk.WriteToServer(data);
// }
// }
// }
private void AddtMapping(SqlBulkCopyColumnMappingCollection map, dynamic mapPair)
{
var source = mapPair.source.ToString();
var dest = mapPair.destination.ToString();
if (string.IsNullOrEmpty(source))
{
throw new ArgumentNullException("loader mapping is invalid because source is missing ");
}
if (string.IsNullOrEmpty(dest))
{
throw new ArgumentNullException("loader mapping is invalid because destination is missing");
}
map.Add(source, dest);
}
public ExpandoObjectMappingInfoProvider(
IDictionary <string, object> properties,
SqlBulkCopyColumnMappingCollection mappingsCollection)
{
if (properties == null || properties.Count == 0)
{
throw new InvalidOperationException("Cannot collect properties from object");
}
MappingsCollection = mappingsCollection;
_propertiesKeyIndices = new Dictionary <int, string>();
foreach (var property in properties)
{
var ordinal = _propertiesKeyIndices.Count;
_propertiesKeyIndices.Add(ordinal, property.Key);
MappingsCollection.Add(new SqlBulkCopyColumnMapping(ordinal, property.Key));
}
}
/// <summary>
/// 设置DataTable到数据库中表的映射
/// </summary>
/// <param name="mappings"></param>
private void SetMappings(SqlBulkCopyColumnMappingCollection mappings)
{
var cmd = this._db.CreateCommand();
cmd.CommandText = string.Format("select top 1 * from [{0}]", this._table.Name);
var dbNames = new List <string>();
using (var reader = cmd.ExecuteReader())
{
for (int i = 0, c = reader.FieldCount; i < c; i++)
{
var name = reader.GetName(i);
dbNames.Add(name);
}
}
foreach (var column in this._table.Columns)
{
var correspondingDbName = dbNames.First(c => string.Compare(c, column.Name, true) == 0);
mappings.Add(column.Name, correspondingDbName);
}
}
public IColumnsMappingConfigurator <TEntity> WithMapping <TProperty>(
Expression <Func <TEntity, TProperty> > member,
string tableColumnName = null)
{
if (!(member.Body is MemberExpression memberExpression))
{
throw new InvalidOperationException("Member was configured uncorrectly");
}
var propertyInfo = memberExpression.Member as PropertyInfo;
if (propertyInfo == null || propertyInfo.CanRead == false)
{
throw new InvalidOperationException("Member was configured uncorrectly");
}
_itemProperties.Add(propertyInfo);
_propertiesIndicesByNames[propertyInfo.Name] = _itemProperties.Count - 1;
_mappingsCollection.Add(new SqlBulkCopyColumnMapping(_itemProperties.Count - 1, tableColumnName ?? propertyInfo.Name));
return(this);
}