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 Remove_BehavesAsExpected() { var item1 = new SqlBulkCopyColumnMapping(0, 0); var item2 = new SqlBulkCopyColumnMapping(1, 1); SqlBulkCopyColumnMappingCollection collection = CreateCollection(item1, item2); collection.Remove(item1); Assert.Equal(1, collection.Count); Assert.Same(item2, collection[0]); collection.Remove(item2); Assert.Equal(0, collection.Count); // The explicit implementation of IList.Remove throws ArgumentException if // the item isn't in the collection, but the public Remove method does not // throw in the full framework. collection.Remove(item2); collection.Remove(new SqlBulkCopyColumnMapping(2, 2)); IList list = CreateCollection(item1, item2); list.Remove(item1); Assert.Equal(1, list.Count); Assert.Same(item2, list[0]); list.Remove(item2); Assert.Equal(0, list.Count); Assert.Throws <ArgumentException>(() => list.Remove(item2)); Assert.Throws <ArgumentException>(() => list.Remove(new SqlBulkCopyColumnMapping(2, 2))); Assert.Throws <ArgumentException>(() => list.Remove("bogus")); }
public ManualConfiguredStrictTypePropertyInfoProvider(SqlBulkCopyColumnMappingCollection mappingsCollection) { _mappingsCollection = mappingsCollection; _itemProperties = new List <PropertyInfo>(); _propertiesIndicesByNames = new Dictionary <string, int>(); }
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> /// <param name="columnMappings">マッピングコレクション。</param> public void SetColumnMappings(SqlBulkCopyColumnMappingCollection columnMappings) { foreach (var property in this.Properties) { columnMappings.Add(property.Name, GetColumnName(property)); } }
/// <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); } }
public void RemoveAt_BehavesAsExpected() { var item1 = new SqlBulkCopyColumnMapping(0, 0); var item2 = new SqlBulkCopyColumnMapping(1, 1); var item3 = new SqlBulkCopyColumnMapping(2, 2); SqlBulkCopyColumnMappingCollection collection = CreateCollection(item1, item2, item3); collection.RemoveAt(0); Assert.Equal(2, collection.Count); Assert.Same(item2, collection[0]); Assert.Same(item3, collection[1]); collection.RemoveAt(1); Assert.Equal(1, collection.Count); Assert.Same(item2, collection[0]); collection.RemoveAt(0); Assert.Equal(0, collection.Count); IList list = CreateCollection(item1, item2, item3); list.RemoveAt(0); Assert.Equal(2, list.Count); Assert.Same(item2, list[0]); Assert.Same(item3, list[1]); list.RemoveAt(1); Assert.Equal(1, list.Count); Assert.Same(item2, list[0]); list.RemoveAt(0); Assert.Equal(0, list.Count); }
/// <summary> /// Check that every column specified in the mappings collection exists /// in the wrapped reader. /// </summary> void ValidateBulkCopySourceColumnMappings(SqlBulkCopyColumnMappingCollection mappings) { foreach (SqlBulkCopyColumnMapping mapping in mappings) { var sc = new SqlName(mapping.SourceColumn); if (!String.IsNullOrEmpty(sc.Name)) { if (wrappedReader.GetOrdinal(sc.Name) == -1) { string bestFit = wrappedReader.GetColumns().SingleOrDefault(c => c.Equals(sc.Name, StringComparison.OrdinalIgnoreCase)); if (bestFit == null) { throw new SchemaException("Source column " + mapping.SourceColumn + " does not exist in source."); } else { throw new SchemaException ( "Source column " + mapping.SourceColumn + " does not exist in source." + " Column name mappings are case specific and best found match is " + bestFit + "." ); } } } else { if (mapping.SourceOrdinal < 0 || mapping.SourceOrdinal >= wrappedReader.FieldCount) { throw new SchemaException("No column exists at index " + mapping.SourceOrdinal + " in source."); } } } }
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)); }
/// <summary> /// Constructor for BulkCopySettings class /// </summary> /// <param name="DestinationTable">Table into which the bulk copy should be done</param> /// <param name="FieldCount">Number of columns that will be copied into the destination table</param> /// <param name="DataMap"> /// Delegate that maps fields of the generic type of the QueryParallelizer instance to ordinal positions /// </param> /// <param name="OutputRowsToExternalEnumerator"> /// If true, the external enumerator will output the same rows that are processed by the bulk copy component. /// If false, the rows will only be output via the bulk component, and the external enumerator will output /// an empty result. /// </param> /// <param name="ConnectionString"> /// Connection string for the destination table. If left null, the loopback connection provider will be used /// </param> /// <param name="CopyOptions"> /// SqlBulkCopyOptions enumeration, used to specify table locking, whether to keep NULLs, and other options /// </param> /// <param name="BatchSize">Number of rows to transfer per batch. Use 0 for unlimited.</param> /// <param name="ColumnMap"> /// Mapping between the DataMap ordinals and the ordinals of columns in the actual destination table. To be /// used when the column order in the base table is different from that specified by the DataMap, or when there /// are fewer columns in the destination table than those that will be populated by the bulk copy operation. /// Can be left null if not needed. /// </param> public BulkCopySettings( string destinationTable, int fieldCount, BulkCopyMappingDelegate dataMap, bool outputRowsToExternalEnumerator, int numThreads, string connectionString, SqlBulkCopyOptions copyOptions, int batchSize, SqlBulkCopyColumnMapping[] columnMap) : this (destinationTable, fieldCount, dataMap, outputRowsToExternalEnumerator, numThreads, connectionString) { this.CopyOptions = copyOptions; this.BatchSize = batchSize; //create an instance of SqlBulkCopy to grab the ColumnMappingCollection SqlBulkCopy bc = new SqlBulkCopy(""); this.ColumnMap = bc.ColumnMappings; if (columnMap != null) { foreach (SqlBulkCopyColumnMapping cm in columnMap) { this.ColumnMap.Add(cm); } } }
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); } } }
public static void AddRange(this SqlBulkCopyColumnMappingCollection columnMappings, IEnumerable <SqlBulkCopyColumnMapping> mappings) { foreach (var mapping in mappings) { columnMappings.Add(mapping); } }
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 void GetEnumerator_NoItems_EmptyEnumerator() { SqlBulkCopyColumnMappingCollection collection = CreateCollection(); IEnumerator e = collection.GetEnumerator(); Assert.Throws <InvalidOperationException>(() => e.Current); Assert.False(e.MoveNext()); Assert.Throws <InvalidOperationException>(() => e.Current); }
void ValidateBulkCopyDestinationColumnMappings ( SqlBulkCopyColumnMappingCollection mappings, Dictionary <string, int> columnLookup, DataRow[] columns ) { foreach (SqlBulkCopyColumnMapping mapping in mappings) { var destColumn = new SqlName(mapping.DestinationColumn); if (!String.IsNullOrEmpty(destColumn.Name)) { if (!columnLookup.ContainsKey(destColumn.Name)) { // If we can't find an exact match by case, try for a case-insensitive match. string bestFit = columnLookup.Keys.SingleOrDefault(c => c.Equals(destColumn.Name, StringComparison.OrdinalIgnoreCase)); if (bestFit == null) { throw new SchemaException ( "Destination column " + mapping.DestinationColumn + " does not exist in destination table " + targetTableName + " in database " + targetDatabaseName + " on server " + targetServerName + "." ); } else { throw new SchemaException ( "Destination column " + mapping.DestinationColumn + " does not exist in destination table " + targetTableName + " in database " + targetDatabaseName + " on server " + targetServerName + ". Column name mappings are case specific and best found match is " + bestFit + "." ); } } } else { if (mapping.DestinationOrdinal < 0 || mapping.DestinationOrdinal >= columns.Length) { throw new SchemaException ( "No column exists at index " + mapping.DestinationOrdinal + " in destination table " + targetTableName + " in database " + targetDatabaseName + " on server " + targetServerName + "." ); } } } }
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); } } }
void CreateLookupFromColumnMappings ( SqlBulkCopyColumnMappingCollection mappings, DataTable schemaTableOfDestinationTable ) { // create lookup dest column definition by source index foreach (SqlBulkCopyColumnMapping mapping in mappings) { int sourceIndex = -1; var sourceColumn = new SqlName(mapping.SourceColumn); if (!String.IsNullOrEmpty(sourceColumn.Name)) { sourceIndex = wrappedReader.GetOrdinal(sourceColumn.Name); } else { sourceIndex = mapping.SourceOrdinal; } DataRow destColumnDef = null; var destColumn = new SqlName(mapping.DestinationColumn); if (!String.IsNullOrEmpty(destColumn.Name)) { destColumnDef = schemaTableOfDestinationTable.Rows.Cast <DataRow>(). Single(c => (string)c["ColumnName"] == destColumn.Name); //foreach (DataRow column in schemaTableOfDestinationTable.Rows) //{ // if ((string)column["ColumnName"] == destColumn.Name) // { // destColumnDef = column; // break; // } //} } else { destColumnDef = schemaTableOfDestinationTable.Rows.Cast <DataRow>(). Single(c => (int)c["ColumnOrdinal"] == mapping.DestinationOrdinal); //foreach (DataRow column in schemaTableOfDestinationTable.Rows) //{ // if ((int)column["ColumnOrdinal"] == mapping.DestinationOrdinal) // { // destColumnDef = column; // break; // } //} } lookup[sourceIndex] = destColumnDef; } }
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 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 EmployeeID, LastName, FirstName from employees", srcConn)) { srcConn.Open(); using (DbDataReader reader = srcCmd.ExecuteReader()) { SqlTransaction myTrans = dstConn.BeginTransaction(); using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn, SqlBulkCopyOptions.Default, myTrans)) { bulkcopy.DestinationTableName = dstTable; SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings; try { bulkcopy.WriteToServer(reader); SqlCommand myCmd = dstConn.CreateCommand(); myCmd.CommandText = "select * from " + dstTable; myCmd.Transaction = myTrans; using (DbDataReader reader1 = myCmd.ExecuteReader()) { Assert.True(reader1.HasRows, "Expected reader to have rows."); } } finally { myTrans.Rollback(); } } Helpers.CheckTableRows(dstConn, dstTable, false); } } } finally { Helpers.TryExecute(dstCmd, "drop table " + dstTable); } } }
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); } } }
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)); } }
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 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); }
/// <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)); } } }
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 void Insert_BehavesAsExpected() { var item1 = new SqlBulkCopyColumnMapping(0, 0); var item2 = new SqlBulkCopyColumnMapping(1, 1); var item3 = new SqlBulkCopyColumnMapping(2, 2); SqlBulkCopyColumnMappingCollection collection = CreateCollection(); collection.Insert(0, item3); collection.Insert(0, item2); collection.Insert(0, item1); Assert.Equal(3, collection.Count); Assert.Same(item1, collection[0]); Assert.Same(item2, collection[1]); Assert.Same(item3, collection[2]); }
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))"); 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 (SqlConnection conn3 = new SqlConnection(srcConstr)) { conn3.Open(); // Start a local transaction on the wrong connection. SqlTransaction myTrans = conn3.BeginTransaction(); using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn, SqlBulkCopyOptions.Default, myTrans)) { SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings; bulkcopy.DestinationTableName = dstTable; string exceptionMsg = SystemDataResourceManager.Instance.ADP_TransactionConnectionMismatch; DataTestUtility.AssertThrowsWrapper <InvalidOperationException>(() => bulkcopy.WriteToServer(reader), exceptionMessage: exceptionMsg); SqlCommand myCmd = dstConn.CreateCommand(); myCmd.CommandText = "select * from " + dstTable; myCmd.Transaction = myTrans; DataTestUtility.AssertThrowsWrapper <InvalidOperationException>(() => myCmd.ExecuteReader(), exceptionMessage: exceptionMsg); } } } } finally { Helpers.TryExecute(dstCmd, "drop table " + dstTable); } } }