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.Single(collection); Assert.Same(item2, collection[0]); collection.Remove(item2); Assert.Empty(collection); // 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); AssertExtensions.Throws <ArgumentException>(null, () => list.Remove(item2)); AssertExtensions.Throws <ArgumentException>(null, () => list.Remove(new SqlBulkCopyColumnMapping(2, 2))); AssertExtensions.Throws <ArgumentException>(null, () => list.Remove("bogus")); }
/// <summary> /// Creates a new column mapping, using column names to refer to source and destination columns. /// </summary> /// <param name="sourceColumn">The name of the source column within the data source.</param> /// <param name="destinationColumn">The name of the destination column within the destination table.</param> /// <returns> /// A fluent <see langword="interface" /> to a <see cref="SqlBulkCopy " /> operation. /// </returns> public IDataBulkCopy Mapping(string sourceColumn, string destinationColumn) { var map = new SqlBulkCopyColumnMapping(sourceColumn, destinationColumn); _mapping.Add(map); return(this); }
protected override async Task EmitBatchAsync(IEnumerable <ResponseEntry> events) { var list = events as IList <ResponseEntry> ?? events.ToList(); this.Fill(list); using (var connection = new SqlConnection(_options.ConnectionString)) { connection.Open(); using (var copy = new SqlBulkCopy(connection)) { copy.DestinationTableName = string.Format(_options.ResponsesTableName); foreach (var column in _eventsTable.Columns) { var columnName = ((DataColumn)column).ColumnName; var mapping = new SqlBulkCopyColumnMapping(columnName, columnName); copy.ColumnMappings.Add(mapping); } await copy.WriteToServerAsync(_eventsTable).ConfigureAwait(false); } } _eventsTable.Clear(); }
/// <summary> /// Creates a new column mapping, using column ordinals to refer to source and destination columns. /// </summary> /// <param name="sourceColumnOrdinal">The ordinal position of the source column within the data source.</param> /// <param name="destinationOrdinal">The ordinal position of the destination column within the destination table.</param> /// <returns> /// A fluent <see langword="interface" /> to a <see cref="SqlBulkCopy " /> operation. /// </returns> public IDataBulkCopy Mapping(int sourceColumnOrdinal, int destinationOrdinal) { var map = new SqlBulkCopyColumnMapping(sourceColumnOrdinal, destinationOrdinal); _mapping.Add(map); return(this); }
public static void SavePlaylistsToDatabase(Dictionary <Int64, SpotifyPlaylist> Playlists, WorkTableState oWorkTableState) { DataTable dtArtists = new DataTable(); dtArtists.Columns.Add(new DataColumn("PlaylistID", typeof(long))); dtArtists.Columns.Add(new DataColumn("Name", typeof(string))); dtArtists.Columns.Add(new DataColumn("SpotifyID", typeof(string))); dtArtists.Columns.Add(new DataColumn("PlaylistOwner", typeof(string))); foreach (KeyValuePair <long, SpotifyPlaylist> oKVP in Playlists) { DataRow drArtist = dtArtists.NewRow(); drArtist["PlaylistID"] = oKVP.Key; drArtist["Name"] = oKVP.Value.Name; drArtist["SpotifyID"] = oKVP.Value.SpotifyID; drArtist["PlaylistOwner"] = oKVP.Value.Owner; dtArtists.Rows.Add(drArtist); } SqlBulkCopyColumnMapping cmID = new SqlBulkCopyColumnMapping("PlaylistID", "PlaylistID"); SqlBulkCopyColumnMapping cmName = new SqlBulkCopyColumnMapping("Name", "Name"); SqlBulkCopyColumnMapping cmSpotifyID = new SqlBulkCopyColumnMapping("SpotifyID", "SpotifyID"); SqlBulkCopyColumnMapping cmPlaylistOwner = new SqlBulkCopyColumnMapping("PlaylistOwner", "PlaylistOwner"); RelationalDatabase.BulkInsert(dtArtists, oWorkTableState, cmID, cmName, cmSpotifyID, cmPlaylistOwner); RelationalDatabase.ExecuteNonQuery("AddSpotifyPlaylists", CommandType.StoredProcedure); }
public static void SaveStatesToDatabase(List <SpotifyState> SpotifyStates, WorkTableState oWorkTableState) { DataTable dtArtists = new DataTable(); dtArtists.Columns.Add(new DataColumn("UserID", typeof(long))); dtArtists.Columns.Add(new DataColumn("PlaylistID", typeof(long))); dtArtists.Columns.Add(new DataColumn("TrackID", typeof(long))); dtArtists.Columns.Add(new DataColumn("Position", typeof(long))); dtArtists.Columns.Add(new DataColumn("ArtistID", typeof(long))); foreach (SpotifyState oSpotifyState in SpotifyStates) { DataRow drArtist = dtArtists.NewRow(); drArtist["UserID"] = oSpotifyState.UserID; drArtist["PlaylistID"] = oSpotifyState.PlaylistID; drArtist["TrackID"] = oSpotifyState.TrackID; drArtist["Position"] = oSpotifyState.Position; drArtist["ArtistID"] = oSpotifyState.ArtistID; dtArtists.Rows.Add(drArtist); } SqlBulkCopyColumnMapping cmUserID = new SqlBulkCopyColumnMapping("UserID", "UserID"); SqlBulkCopyColumnMapping cmPlaylistID = new SqlBulkCopyColumnMapping("PlaylistID", "PlaylistID"); SqlBulkCopyColumnMapping cmTrackID = new SqlBulkCopyColumnMapping("TrackID", "TrackID"); SqlBulkCopyColumnMapping cmPosition = new SqlBulkCopyColumnMapping("Position", "Position"); SqlBulkCopyColumnMapping cmArtistID = new SqlBulkCopyColumnMapping("ArtistID", "ArtistID"); RelationalDatabase.BulkInsert(dtArtists, oWorkTableState, cmUserID, cmPlaylistID, cmTrackID, cmPosition, cmArtistID); RelationalDatabase.ExecuteNonQuery("UpsertSpotifyState", CommandType.StoredProcedure); }
public void LoadBulkCopy(string FileName) { DataTable dt = LoadDataTable(FileName); //Load database using (SqlConnection conn = new SqlConnection(ConnString)) { conn.Open(); SqlBulkCopy bc = new SqlBulkCopy(ConnString); bc.DestinationTableName = "DataTable"; SqlBulkCopyColumnMapping s1 = new SqlBulkCopyColumnMapping("S1", "S1"); SqlBulkCopyColumnMapping s2 = new SqlBulkCopyColumnMapping("S2", "S2"); SqlBulkCopyColumnMapping d1 = new SqlBulkCopyColumnMapping("Dt1", "Dt1"); SqlBulkCopyColumnMapping d2 = new SqlBulkCopyColumnMapping("Dt2", "Dt2"); SqlBulkCopyColumnMapping n1 = new SqlBulkCopyColumnMapping("N1", "N1"); SqlBulkCopyColumnMapping n2 = new SqlBulkCopyColumnMapping("N2", "N2"); bc.ColumnMappings.Add(s1); bc.ColumnMappings.Add(s2); bc.ColumnMappings.Add(d1); bc.ColumnMappings.Add(d2); bc.ColumnMappings.Add(n1); bc.ColumnMappings.Add(n2); bc.WriteToServer(dt); } }
/// <summary> /// Emit a batch of log events, running asynchronously. /// </summary> /// <param name="events">The events to emit.</param> /// <remarks> /// Override either <see cref="PeriodicBatchingSink.EmitBatch" /> or <see cref="PeriodicBatchingSink.EmitBatchAsync" /> /// , /// not both. /// </remarks> protected override async Task EmitBatchAsync(IEnumerable <LogEvent> events) { // Copy the events to the data table FillDataTable(events); try { using (var cn = new SqlConnection(_connectionString)) { await cn.OpenAsync(_token.Token).ConfigureAwait(false); using (var copy = new SqlBulkCopy(cn)) { copy.DestinationTableName = _tableName; foreach (var column in _eventsTable.Columns) { var columnName = ((DataColumn)column).ColumnName; var mapping = new SqlBulkCopyColumnMapping(columnName, columnName); copy.ColumnMappings.Add(mapping); } await copy.WriteToServerAsync(_eventsTable, _token.Token).ConfigureAwait(false); } } } catch (Exception ex) { SelfLog.WriteLine("Unable to write {0} log events to the database due to following error: {1}", events.Count(), ex.Message); } finally { // Processed the items, clear for the next run _eventsTable.Clear(); } }
public IEnumerable <SqlBulkCopyColumnMapping> GetColumnMappings() { var columnMappings = new List <SqlBulkCopyColumnMapping>(); var columns = this.RecordType != null ? this.DynamicRecordColumnProvider.GetFields(this.RecordType) : this.DynamicRecordColumnProvider.GetFields(); foreach (var column in columns) { SqlBulkCopyColumnMapping columnMapping = null; if (column.DestinationFieldOrdinal != null) { columnMapping = new SqlBulkCopyColumnMapping(column.Name, column.DestinationFieldOrdinal.Value); } else if (column.DestinationFieldName != null) { columnMapping = new SqlBulkCopyColumnMapping(column.Name, column.DestinationFieldName); } if (columnMapping != null) { columnMappings.Add(columnMapping); } } return(columnMappings); }
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.Single(collection); Assert.Same(item2, collection[0]); collection.RemoveAt(0); Assert.Empty(collection); 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); }
public long BulkInsert(DataTable Data, string TableName, int BatchSize, int NotifySize) { SqlConnection sqlCon = (SqlConnection)Database.GetDbConnection(); long insertedCount = 0; using (sqlCon) { try { sqlCon.Open(); var transaction = sqlCon.BeginTransaction(); using (SqlBulkCopy sqlCopy = new SqlBulkCopy(sqlCon)) { Data.Columns.Cast <DataColumn>().ToList().ForEach(f => { SqlBulkCopyColumnMapping bccm = new SqlBulkCopyColumnMapping(); bccm.DestinationColumn = f.ColumnName; bccm.SourceColumn = f.ColumnName; sqlCopy.ColumnMappings.Add(bccm); }); sqlCopy.NotifyAfter = NotifySize; sqlCopy.SqlRowsCopied += (sender, eventArgs) => insertedCount = eventArgs.RowsCopied; sqlCopy.DestinationTableName = TableName; sqlCopy.BatchSize = BatchSize; sqlCopy.WriteToServer(Data); } return(insertedCount); } catch (Exception ex) { throw; } } }
public static SqlBulkCopyColumnMapping AddColumnMapping(this SqlBulkCopy sbc, string sourceColumn, string targetColumn) { var map = new SqlBulkCopyColumnMapping(sourceColumn, targetColumn); sbc.ColumnMappings.Add(map); return(map); }
public static SqlBulkCopyColumnMapping AddColumnMapping(this SqlBulkCopy sbc, int sourceColumnOrdinal, int targetColumnOrdinal) { var map = new SqlBulkCopyColumnMapping(sourceColumnOrdinal, targetColumnOrdinal); sbc.ColumnMappings.Add(map); return(map); }
/// <summary> /// Note: this is necessary at least for the following reason: /// /// Without explicit mapping based on column names, SqlBulkCopy inserts based on column ordinals. /// So if the datatable has the following properties: /// ------------------------------------------- /// PartNumber (int) | PartDescription(string) /// ------------------------------------------- /// /// and the database table has the following properties: /// ------------------------------------------- /// PartDescription(string) | PartNumber (int) /// ------------------------------------------- /// /// then the bulk copy will fail because it will try to insert the part number data into the /// part description column. /// /// So the mapping below will instead perform bulk copies on the names of the CLR properties /// and the names of the columns in the database rather than on CLR and SQL column ordinals. /// /// </summary> /// <param name="sqlBulkCopy"></param> /// <param name="dataTable"></param> private static void SetColumnMappings(SqlBulkCopy sqlBulkCopy, DataTable dataTable) { foreach (DataColumn column in dataTable.Columns) { var columnMapping = new SqlBulkCopyColumnMapping(column.ColumnName, column.ColumnName); sqlBulkCopy.ColumnMappings.Add(columnMapping); } }
/// <summary> /// 获取数据 /// </summary> /// <returns></returns> public static SqlBulkCopyColumnMapping[] GetMapping() { SqlBulkCopyColumnMapping[] mapping = new SqlBulkCopyColumnMapping[4]; mapping[0] = new SqlBulkCopyColumnMapping("ID", "ID"); mapping[1] = new SqlBulkCopyColumnMapping("Name", "Name"); mapping[2] = new SqlBulkCopyColumnMapping("sex", "sex"); mapping[3] = new SqlBulkCopyColumnMapping("phone", "phone"); return(mapping); }
public void CopyTo_InvalidArrayType_Throws() { var item1 = new SqlBulkCopyColumnMapping(0, 0); var item2 = new SqlBulkCopyColumnMapping(1, 1); var item3 = new SqlBulkCopyColumnMapping(2, 2); ICollection collection = CreateCollection(item1, item2, item3); Assert.Throws <InvalidCastException>(() => collection.CopyTo(new int[collection.Count], 0)); Assert.Throws <InvalidCastException>(() => collection.CopyTo(new string[collection.Count], 0)); }
/// <summary> /// 批量实体写入数据库 ///<see cref="entities">实体类结构字段必须与数据库保持一致</see> /// </summary> /// <typeparam name="TEntity">s</typeparam> /// <param name="entities">实体集合</param> /// <param name="transation">是否开启事务</param> /// <param name="tableName">实际表名称</param> /// <returns></returns> public int InsertEntityItems <TEntity>(IEnumerable <TEntity> entities, string tableName = null, bool transation = false) where TEntity : class { try { DataTable dt = new DataTable(); var flag = false; if (transation) { BeginTransation = transation; } if (tableName == null) { var entity = entities.FirstOrDefault(); tableName = GetTableNameType(entity.GetType()); } using (SqlBulkCopy bluk = new SqlBulkCopy(_sqlConnection, SqlBulkCopyOptions.Default, _transaction)) { bluk.DestinationTableName = tableName; foreach (var entity in entities) { var property = entity.GetType().GetProperties(); if (!flag) { foreach (var perty in property) { dt.Columns.Add(perty.Name); var sqlqMapper = new SqlBulkCopyColumnMapping(perty.Name, perty.Name); bluk.ColumnMappings.Add(sqlqMapper); } flag = true; } DataRow row = dt.NewRow(); foreach (var perty in property) { row[perty.Name] = perty.GetValue(entity, null); } dt.Rows.Add(row); } bluk.WriteToServer(dt); if (_transaction != null && transation) { _transaction.Commit(); } } return(1); } catch (Exception ex) { logger.Error($"批量写入SqlServer数据库异常:{ex.Message}"); return(0); } }
public List <SqlBulkCopyColumnMapping> GetMappingList(DataTable dt) { List <SqlBulkCopyColumnMapping> mappingList = new List <SqlBulkCopyColumnMapping>(); foreach (DataColumn column in dt.Columns) { SqlBulkCopyColumnMapping mapping = new SqlBulkCopyColumnMapping(column.ColumnName, column.ColumnName); mappingList.Add(mapping); } return(mappingList); }
public ActionResult PostFile() { string tableName; var file = Request.Form.Files[0]; System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance); using (Stream stream = file.OpenReadStream()) { IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); var result = excelReader.AsDataSet(new ExcelDataSetConfiguration() { ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true } }); Dictionary <string, List <string> > dic = new Dictionary <string, List <string> > (); DataTable firstTable = result.Tables[0]; foreach (DataColumn col in firstTable.Columns) { DataTable table = getmaster("Name", col.ColumnName); tableName = table.Rows[0]["TableName"].ToString(); if (!dic.ContainsKey(tableName)) { List <string> list = new List <string> (); list.Add(col.ColumnName); dic.Add(tableName, list); } else { List <string> list1 = new List <string> (); dic.TryGetValue(tableName, out list1); list1.Add(col.ColumnName); } } foreach (var item in dic) { List <string> de = item.Value; var bulkCopy = new SqlBulkCopy(connectionstr); bulkCopy.DestinationTableName = item.Key; foreach (string col in de) { DataTable table = getmaster("Name", col); string last = table.Rows[0]["ColumnName"].ToString(); SqlBulkCopyColumnMapping mapID = new SqlBulkCopyColumnMapping(col, last); bulkCopy.ColumnMappings.Add(mapID); } bulkCopy.WriteToServer(firstTable); } return(Ok()); } }
private void save_Click(object sender, EventArgs e) { string connectionString = GetConnectionString(); using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); // Perform an initial count on the destination table. SqlCommand commandRowCount = new SqlCommand( "SELECT COUNT(*) FROM " + "dbo.tblProduct;", connection); long countStart = System.Convert.ToInt32(commandRowCount.ExecuteScalar()); //MessageBox.Show("Starting row count = {0}" + countStart); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)) { bulkCopy.DestinationTableName = "dbo.tblProduct"; try { // Write from the source to the destination. SqlBulkCopyColumnMapping productId = new SqlBulkCopyColumnMapping("productId", "productId"); bulkCopy.ColumnMappings.Add(productId); SqlBulkCopyColumnMapping productName = new SqlBulkCopyColumnMapping("productName", "productName"); bulkCopy.ColumnMappings.Add(productName); SqlBulkCopyColumnMapping productPrice = new SqlBulkCopyColumnMapping("productPrice", "productPrice"); bulkCopy.ColumnMappings.Add(productPrice); SqlBulkCopyColumnMapping categoryId = new SqlBulkCopyColumnMapping("categoryId", "categoryId"); bulkCopy.ColumnMappings.Add(categoryId); SqlBulkCopyColumnMapping openingstock = new SqlBulkCopyColumnMapping("openingstock", "openingstock"); bulkCopy.ColumnMappings.Add(openingstock); SqlBulkCopyColumnMapping productImage = new SqlBulkCopyColumnMapping("productImage", "productImage"); bulkCopy.ColumnMappings.Add(productImage); bulkCopy.WriteToServer(csvData); MessageBox.Show("Saved"); } catch (Exception ex) { MessageBox.Show(ex.Message); } } long countEnd = System.Convert.ToInt32(commandRowCount.ExecuteScalar()); //Console.WriteLine("Ending row count = {0}", countEnd); MessageBox.Show("{0} rows were added." + (countEnd - countStart)); } }
private StringBuilder WriteOutputToDatabaseSql(List <Row> rows) { StringBuilder sb = new StringBuilder(); using (SqlConnection sourceConnection = new SqlConnection(ConnectionStringKey.Value)) { DateTime StartTime = DateTime.Now; ExtensionMethods.TraceInformation("Converting to DataTable - " + StartTime.ToString()); DataTable outDataTable = ToDataTable(rows); sb = outDataTable.ToCsvStringBuilder(); DateTime EndTime = DateTime.Now; ExtensionMethods.TraceInformation("Converted - {0}. Time taken: {1}", EndTime, (EndTime - StartTime).ToString()); sourceConnection.Open(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConnectionStringKey.Value)) { bulkCopy.BulkCopyTimeout = 15 * 60; //15 minutes bulkCopy.DestinationTableName = DatabaseMap.TargetTableName; foreach (ColumnMapInfo columnMapInfo in DatabaseMap) { if ((!string.IsNullOrEmpty(columnMapInfo.InputColumn)) && (columnMapInfo.InputColumn != ColumnMapInfo.DatabaseDefault)) { SqlBulkCopyColumnMapping mapInfo = new SqlBulkCopyColumnMapping(columnMapInfo.InputColumn, columnMapInfo.OutputColumn); bulkCopy.ColumnMappings.Add(mapInfo); } } try { bulkCopy.WriteToServer(outDataTable); } catch (Exception ex) { IsErrored = true; //we are not throwing as we need sb to return as it may useful for debugging ExtensionMethods.TraceError(ex.ToString()); _Job.TraceError(GetBulkCopyFailedData(outDataTable, bulkCopy.ColumnMappings)); } finally { if (outDataTable != null) { outDataTable.Dispose(); } } } } return(sb); }
private static void InsertDataUsingSqlBulkCopy(IEnumerable <ContactInfo> people, SqlConnection connection) { var bulkCopy = new SqlBulkCopy(connection); bulkCopy.DestinationTableName = "Person"; // bulkCopy.ColumnMappings.Add("Name", "Name")lkCopy.ColumnMappings.Add("DateOfBirth", "DateOfBirth"); SqlBulkCopyColumnMapping mapping = new SqlBulkCopyColumnMapping(); using (var dataReader = new ObjectDataReader <ContactInfo>(people)) { bulkCopy.WriteToServer(dataReader); } }
/* * public void BulkInsertXml(int bufferSize) { * * bufferSize = 5; * * * XmlWriter xmlWriter = XmlWriter.Create("rainbow.xml"); * xmlWriter.WriteStartDocument(); * xmlWriter.WriteStartElement("hashings"); * int i = 0; * int count = arrangements.Count(); * while (i == 0) { * * for (int j = i; j < i+ bufferSize; j++) { * string md5hash = HashesClass.computeHash(arrangements[j], "MD5"); * string sha1hash = HashesClass.computeHash(arrangements[j], "SHA1"); * string sha2hash = HashesClass.computeHash(arrangements[j], "SHA256"); * xmlWriter.WriteStartElement("hashing"); * xmlWriter.WriteAttributeString("password", arrangements[j]); * xmlWriter.WriteAttributeString("md5hash", md5hash); * xmlWriter.WriteAttributeString("sha1hash", sha1hash); * xmlWriter.WriteAttributeString("sha2hash", sha2hash); * xmlWriter.WriteEndElement(); * * } * * i += bufferSize; * xmlWriter.WriteEndElement(); * xmlWriter.WriteEndDocument(); * xmlWriter.Close(); * * var doc = XDocument.Load("rainbow.xml"); * * DataTable dt = new DataTable(); * dt.Columns.Add(new DataColumn("password")); * dt.Columns.Add(new DataColumn("md5hash")); * dt.Columns.Add(new DataColumn("sha1hash")); * dt.Columns.Add(new DataColumn("sha2hash")); * doc.Descendants("hashings").ToList().ForEach(e=>Console.WriteLine(e.Attribute("password").Value)); * * var rows = doc.Descendants("hashing").Select(el => { * DataRow row = dt.NewRow(); * row["password"] = el.Attribute("password").Value; * row["md5hash"] = el.Attribute("md5hash").Value; * row["sha1hash"] = el.Attribute("sha1hash").Value; * row["sha2hash"] = el.Attribute("sha2hash").Value; * return row; * }); * dt.Rows.Add(rows); * Console.WriteLine((dt.Rows[0]["password"]).ToString()); * * SqlConnection connection = new SqlConnection(connectionString); * connection.Open(); * using (var bcp = new SqlBulkCopy(connection)) * { * * * SqlBulkCopyColumnMapping mapPass = * new SqlBulkCopyColumnMapping("password", "password"); * bcp.ColumnMappings.Add(mapPass); * * SqlBulkCopyColumnMapping mapMd5 = * new SqlBulkCopyColumnMapping("md5hash", "md5hash"); * bcp.ColumnMappings.Add(mapMd5); * * SqlBulkCopyColumnMapping mapSha1 = * new SqlBulkCopyColumnMapping("sha1hash", "sha1hash"); * bcp.ColumnMappings.Add(mapSha1); * * SqlBulkCopyColumnMapping mapSha2 = * new SqlBulkCopyColumnMapping("sha2hash", "sha2hash"); * bcp.ColumnMappings.Add(mapSha2); * * * bcp.BatchSize = bufferSize; * bcp.DestinationTableName = "Rainbow"; * bcp.WriteToServer(dt); * } * * connection.Close(); * * * * * } * } */ public void BulkInsert() { DataTable dt = new DataTable(); dt.Columns.Add("password"); dt.Columns.Add("md5hash"); dt.Columns.Add("sha1hash"); dt.Columns.Add("sha2hash"); for (int i = 0; i < arrangements.Count; i++) { DataRow row = dt.NewRow(); row["password"] = arrangements[i]; row["md5hash"] = HashesClass.computeHash(arrangements[i], "MD5"); row["sha1hash"] = HashesClass.computeHash(arrangements[i], "SHA1"); row["sha2hash"] = HashesClass.computeHash(arrangements[i], "SHA256"); dt.Rows.Add(row); // Console.WriteLine(arrangements[i]); } Console.WriteLine("finished creating data table"); SqlConnection connection = new SqlConnection(connectionString); connection.Open(); using (var bcp = new SqlBulkCopy(connection)) { SqlBulkCopyColumnMapping mapPass = new SqlBulkCopyColumnMapping("password", "password"); bcp.ColumnMappings.Add(mapPass); SqlBulkCopyColumnMapping mapMd5 = new SqlBulkCopyColumnMapping("md5hash", "md5hash"); bcp.ColumnMappings.Add(mapMd5); SqlBulkCopyColumnMapping mapSha1 = new SqlBulkCopyColumnMapping("sha1hash", "sha1hash"); bcp.ColumnMappings.Add(mapSha1); SqlBulkCopyColumnMapping mapSha2 = new SqlBulkCopyColumnMapping("sha2hash", "sha2hash"); bcp.ColumnMappings.Add(mapSha2); bcp.BatchSize = 100; bcp.DestinationTableName = "Rainbow"; bcp.WriteToServer(dt); } connection.Close(); }
public override bool Import(DataTable Data, ODAParameter[] Prms) { SqlBulkCopy sqlbulkcopy = null; IDbConnection conn = null; DataTable ImportData = Data.Copy(); try { if (this.Transaction == null) { conn = this.GetConnection(); sqlbulkcopy = new SqlBulkCopy((SqlConnection)conn); } else { sqlbulkcopy = new SqlBulkCopy((SqlConnection)this.Transaction.Connection, SqlBulkCopyOptions.Default, (SqlTransaction)this.Transaction); } for (int i = 0; i < Prms.Length; i++) { if (ImportData.Columns.Contains(Prms[i].ParamsName)) { SqlBulkCopyColumnMapping colMap = new SqlBulkCopyColumnMapping(Prms[i].ParamsName, Prms[i].ParamsName); sqlbulkcopy.ColumnMappings.Add(colMap); } } sqlbulkcopy.BulkCopyTimeout = 600000; //需要操作的数据库表名 sqlbulkcopy.DestinationTableName = ImportData.TableName; //将内存表表写入 sqlbulkcopy.WriteToServer(ImportData); return(true); } catch (Exception ex) { throw new ODAException(202, string.Format("Import data into table [{0}] error:{1}", ImportData.TableName, ex.Message)); } finally { if (conn != null) { conn.Close(); conn.Dispose(); } if (sqlbulkcopy != null) { sqlbulkcopy.Close(); sqlbulkcopy = null; } } }
public void saveToDB() { using (var sqlBulk = new SqlBulkCopy(_connectionString)) { sqlBulk.DestinationTableName = "Warehouse"; SqlBulkCopyColumnMapping mapID = new SqlBulkCopyColumnMapping("Id", "Id"); sqlBulk.ColumnMappings.Add(mapID); SqlBulkCopyColumnMapping mapName = new SqlBulkCopyColumnMapping("Name", "Name"); sqlBulk.ColumnMappings.Add(mapName); sqlBulk.WriteToServer(dtWarehouse); } }
public void UploadData(IEnumerable <Loan> employees) { using (SqlConnection targetConnection = new SqlConnection(_connectionString)) { targetConnection.Open(); PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(Loan)); DataTable table = new DataTable(); foreach (PropertyDescriptor property in properties) { Console.WriteLine($"{property.Name} with type {Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType}"); table.Columns.Add(property.Name, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType); } //table.Columns.Add("LoanId", typeof(int)); //table.Columns.Add("PrincipalRemaining",typeof(decimal)); //table.Columns.Add("Status", typeof(string)); foreach (var emp in employees) { DataRow row = table.NewRow(); foreach (PropertyDescriptor property in properties) { row[property.Name] = property.GetValue(emp) ?? DBNull.Value; } //row["LoanId"] = emp.LoanId; //row["PrincipalRemaining"] = emp.PrincipalRemaining; //row["Status"] = emp.Status; table.Rows.Add(row); } SqlBulkCopy bulkCopy = new SqlBulkCopy(targetConnection); foreach (PropertyDescriptor property in properties) { SqlBulkCopyColumnMapping mapping = new SqlBulkCopyColumnMapping(property.Name, property.Name); bulkCopy.ColumnMappings.Add(mapping); } bulkCopy.DestinationTableName = "dbo.Loan"; bulkCopy.WriteToServer(table); Console.WriteLine("end"); } }
public List <SqlBulkCopyColumnMapping> GetMappingList(List <string> sourceColumns, List <string> destinationColumns) { List <SqlBulkCopyColumnMapping> mappingList = new List <SqlBulkCopyColumnMapping>(); if (sourceColumns.Count == destinationColumns.Count) { for (int i = 0; i < destinationColumns.Count; i++) { SqlBulkCopyColumnMapping mapping = new SqlBulkCopyColumnMapping(sourceColumns[i], destinationColumns[i]); mappingList.Add(mapping); } } return(mappingList); }
protected override void RunBulkCopy(IDataQueue queue) { ITableStructure ts = queue.GetRowFormat; if (ts.Columns.Count == 1) { // SqlBulkCopy has problems when running on tables with one column RunInserts(queue); return; } using (SqlBulkCopy bcp = new SqlBulkCopy((SqlConnection)Connection.SystemConnection, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls, null)) { bcp.DestinationTableName = Connection.Dialect.QuoteFullName(DestinationTable.FullName); ITableStructure dst_ts = DestinationTable; if (ts.Columns.Count < dst_ts.Columns.Count) { int srcindex = 0; foreach (var src in ts.Columns) { SqlBulkCopyColumnMapping map = new SqlBulkCopyColumnMapping(srcindex, dst_ts.Columns.IndexOfIf(col => col.ColumnName == src.ColumnName)); bcp.ColumnMappings.Add(map); srcindex++; } } DataQueueReaderAdapter reader = new DataQueueReaderAdapter(queue); try { bcp.BulkCopyTimeout = 0; bcp.WriteToServer(reader); ProgressInfo.LogMessage("INSERT", LogLevel.Info, Texts.Get("s_inserted_into_table$table$rows", "table", DestinationTable.FullName, "rows", reader.ReadedRows)); } catch (Exception err) { ILogger logger = ProgressInfo; if (err is QueueClosedError) { logger = Logging.Root; } logger.LogMessageDetail( "INSERT", LogLevel.Error, String.Format("{0}", Texts.Get("s_error_inserting_into_table$table", "table", DestinationTable.FullName)), err.ToString()); throw; } finally { reader.Close(); } } }
public SqlBulkCopyColumnMapping[] GetBulk(DataTable dt) { try { SqlBulkCopyColumnMapping[] mapp = new SqlBulkCopyColumnMapping[dt.Columns.Count]; for (int j = 0; j < dt.Columns.Count; j++) { mapp[j] = new SqlBulkCopyColumnMapping(dt.Columns[j].ColumnName, dt.Columns[j].ColumnName); } return(mapp); } catch (Exception e) { throw e; } }
protected void Button2_Click(object sender, EventArgs e) { if (Session["quiztable"] != null) { DataTable qwiz = (DataTable)Session["quiztable"]; //sqlbulkcopy order by date(default getdate()) try { using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SMS"].ConnectionString)) { connection.Open(); //var transaction = connection.BeginTransaction(); using (var bulkcopy = new SqlBulkCopy(connection)) { bulkcopy.DestinationTableName = "QuizQuestions"; SqlBulkCopyColumnMapping QUIZ = new SqlBulkCopyColumnMapping(0, "QUIZ ID"); bulkcopy.ColumnMappings.Add(QUIZ); SqlBulkCopyColumnMapping QUEST = new SqlBulkCopyColumnMapping(1, "QUESTION"); bulkcopy.ColumnMappings.Add(QUEST); SqlBulkCopyColumnMapping OPTION1 = new SqlBulkCopyColumnMapping(2, "OPTION 1"); bulkcopy.ColumnMappings.Add(OPTION1); SqlBulkCopyColumnMapping OPTION2 = new SqlBulkCopyColumnMapping(3, "OPTION 2"); bulkcopy.ColumnMappings.Add(OPTION2); SqlBulkCopyColumnMapping OPTION3 = new SqlBulkCopyColumnMapping(4, "OPTION 3"); bulkcopy.ColumnMappings.Add(OPTION3); SqlBulkCopyColumnMapping OPTION4 = new SqlBulkCopyColumnMapping(5, "OPTION 4"); bulkcopy.ColumnMappings.Add(OPTION4); SqlBulkCopyColumnMapping ANS = new SqlBulkCopyColumnMapping(6, "ANSWER"); bulkcopy.ColumnMappings.Add(ANS); bulkcopy.WriteToServer(qwiz); } } Session.Remove("quiztable"); } catch (Exception ex) { Response.Write("Unable to unable " + ex.Message); } } else { Response.Write("datatable is empty"); } }
public void Members_InvalidRange_ThrowsArgumentOutOfRangeException() { SqlBulkCopyColumnMappingCollection collection = CreateCollection(); var item = new SqlBulkCopyColumnMapping(0, 0); Assert.Throws<ArgumentOutOfRangeException>(() => collection[-1]); Assert.Throws<ArgumentOutOfRangeException>(() => collection[collection.Count]); Assert.Throws<ArgumentOutOfRangeException>(() => collection.Insert(-1, item)); Assert.Throws<ArgumentOutOfRangeException>(() => collection.Insert(collection.Count + 1, item)); Assert.Throws<ArgumentOutOfRangeException>(() => collection.RemoveAt(-1)); Assert.Throws<ArgumentOutOfRangeException>(() => collection.RemoveAt(collection.Count)); IList list = collection; Assert.Throws<ArgumentOutOfRangeException>(() => list[-1]); Assert.Throws<ArgumentOutOfRangeException>(() => list[collection.Count]); Assert.Throws<ArgumentOutOfRangeException>(() => list[-1] = item); Assert.Throws<ArgumentOutOfRangeException>(() => list[collection.Count] = item); Assert.Throws<ArgumentOutOfRangeException>(() => list.Insert(-1, item)); Assert.Throws<ArgumentOutOfRangeException>(() => list.Insert(collection.Count + 1, item)); Assert.Throws<ArgumentOutOfRangeException>(() => list.RemoveAt(-1)); Assert.Throws<ArgumentOutOfRangeException>(() => list.RemoveAt(collection.Count)); }
public void GetEnumerator_ItemsAdded_AllItemsReturnedAndEnumeratorBehavesAsExpected() { var item1 = new SqlBulkCopyColumnMapping(0, 0); var item2 = new SqlBulkCopyColumnMapping(1, 1); var item3 = new SqlBulkCopyColumnMapping(2, 2); SqlBulkCopyColumnMappingCollection collection = CreateCollection(item1, item2, item3); IEnumerator e = collection.GetEnumerator(); const int Iterations = 2; for (int i = 0; i < Iterations; i++) { // Not started Assert.Throws<InvalidOperationException>(() => e.Current); Assert.True(e.MoveNext()); Assert.Same(item1, e.Current); Assert.True(e.MoveNext()); Assert.Same(item2, e.Current); Assert.True(e.MoveNext()); Assert.Same(item3, e.Current); Assert.False(e.MoveNext()); Assert.False(e.MoveNext()); Assert.False(e.MoveNext()); Assert.False(e.MoveNext()); Assert.False(e.MoveNext()); // Ended Assert.Throws<InvalidOperationException>(() => e.Current); e.Reset(); } }
public void Contains_ItemsAdded_MatchesExpectation() { var item1 = new SqlBulkCopyColumnMapping(0, 0); var item2 = new SqlBulkCopyColumnMapping(1, 1); var item3 = new SqlBulkCopyColumnMapping(2, 2); SqlBulkCopyColumnMappingCollection collection = CreateCollection(item1, item2, item3); Assert.True(collection.Contains(item1)); Assert.True(collection.Contains(item2)); Assert.True(collection.Contains(item3)); Assert.False(collection.Contains(null)); IList list = collection; Assert.True(list.Contains(item1)); Assert.True(list.Contains(item2)); Assert.True(list.Contains(item3)); Assert.False(list.Contains(null)); Assert.False(list.Contains("Bogus")); }
public void Indexer_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); Assert.Same(item1, collection[0]); Assert.Same(item2, collection[1]); Assert.Same(item3, collection[2]); IList list = collection; list[0] = item2; list[1] = item3; list[2] = item1; Assert.Same(item2, list[0]); Assert.Same(item3, list[1]); Assert.Same(item1, list[2]); }
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); }
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 void InsertAndClear_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, item1); collection.Insert(1, item2); collection.Insert(2, item3); Assert.Equal(3, collection.Count); Assert.Same(item1, collection[0]); Assert.Same(item2, collection[1]); Assert.Same(item3, collection[2]); collection.Clear(); Assert.Equal(0, collection.Count); collection.Add(item1); collection.Add(item3); Assert.Equal(2, collection.Count); Assert.Same(item1, collection[0]); Assert.Same(item3, collection[1]); collection.Insert(1, item2); Assert.Equal(3, collection.Count); Assert.Same(item1, collection[0]); Assert.Same(item2, collection[1]); Assert.Same(item3, collection[2]); collection.Clear(); Assert.Equal(0, collection.Count); IList list = collection; list.Insert(0, item1); list.Insert(1, item2); list.Insert(2, item3); Assert.Equal(3, list.Count); Assert.Same(item1, list[0]); Assert.Same(item2, list[1]); Assert.Same(item3, list[2]); list.Clear(); Assert.Equal(0, list.Count); list.Add(item1); list.Add(item3); Assert.Equal(2, list.Count); Assert.Same(item1, list[0]); Assert.Same(item3, list[1]); list.Insert(1, item2); Assert.Equal(3, list.Count); Assert.Same(item1, list[0]); Assert.Same(item2, list[1]); Assert.Same(item3, list[2]); list.Clear(); Assert.Equal(0, list.Count); }
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 void IndexOf_BehavesAsExpected() { var item1 = new SqlBulkCopyColumnMapping(0, 0); var item2 = new SqlBulkCopyColumnMapping(1, 1); var item3 = new SqlBulkCopyColumnMapping(2, 2); SqlBulkCopyColumnMappingCollection collection = CreateCollection(item1, item2); Assert.Equal(0, collection.IndexOf(item1)); Assert.Equal(1, collection.IndexOf(item2)); Assert.Equal(-1, collection.IndexOf(item3)); IList list = collection; Assert.Equal(0, list.IndexOf(item1)); Assert.Equal(1, list.IndexOf(item2)); Assert.Equal(-1, list.IndexOf(item3)); Assert.Equal(-1, list.IndexOf("bogus")); }
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]); }
public void CopyTo_ItemsAdded_ItemsCopiedToArray() { var item1 = new SqlBulkCopyColumnMapping(0, 0); var item2 = new SqlBulkCopyColumnMapping(1, 1); var item3 = new SqlBulkCopyColumnMapping(2, 2); SqlBulkCopyColumnMappingCollection collection = CreateCollection(item1, item2, item3); var array1 = new SqlBulkCopyColumnMapping[collection.Count]; collection.CopyTo(array1, 0); Assert.Same(item1, array1[0]); Assert.Same(item2, array1[1]); Assert.Same(item3, array1[2]); var array2 = new SqlBulkCopyColumnMapping[collection.Count]; ((ICollection)collection).CopyTo(array2, 0); Assert.Same(item1, array2[0]); Assert.Same(item2, array2[1]); Assert.Same(item3, array2[2]); }
public void GetEnumerator_ItemsAdded_ItemsFromEnumeratorMatchesItemsFromIndexer() { var item1 = new SqlBulkCopyColumnMapping(0, 0); var item2 = new SqlBulkCopyColumnMapping(1, 1); var item3 = new SqlBulkCopyColumnMapping(2, 2); SqlBulkCopyColumnMappingCollection collection = CreateCollection(item1, item2, item3); int index = 0; foreach (SqlBulkCopyColumnMapping enumeratorItem in collection) { SqlBulkCopyColumnMapping indexerItem = collection[index]; Assert.NotNull(enumeratorItem); Assert.NotNull(indexerItem); Assert.Same(indexerItem, enumeratorItem); index++; } }
public void CopyTo_InvalidArrayType_Throws() { var item1 = new SqlBulkCopyColumnMapping(0, 0); var item2 = new SqlBulkCopyColumnMapping(1, 1); var item3 = new SqlBulkCopyColumnMapping(2, 2); ICollection collection = CreateCollection(item1, item2, item3); Assert.Throws<InvalidCastException>(() => collection.CopyTo(new int[collection.Count], 0)); Assert.Throws<InvalidCastException>(() => collection.CopyTo(new string[collection.Count], 0)); }