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