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