public static void BulkCopyTable(DataTable datatable, string table)
{
using (var scon = Connections.Connect())
{
scon.Open();
using (SqlBulkCopy copydtab = new SqlBulkCopy(scon))
{
copydtab.DestinationTableName = table;
copydtab.WriteToServer(datatable);
}
scon.Close();
scon.Dispose();
}
}
void ITransaction.CommitTransaction(string connectionName, SqlCredential credentials, SqlConnection connection)
{
if (!_list.Any())
{
return;
}
base.IndexCheck();
DataTable dt = _helper.CreateDataTable <T>(_columns, _customColumnMappings, _matchTargetOn, _outputIdentity);
dt = _helper.ConvertListToDataTable(dt, _list, _columns);
// Must be after ToDataTable is called.
_helper.DoColumnMappings(_customColumnMappings, _columns, _matchTargetOn);
using (SqlConnection conn = _helper.GetSqlConnection(connectionName, credentials, connection))
{
conn.Open();
DataTable dtCols = null;
if (_outputIdentity == ColumnDirection.InputOutput)
{
dtCols = _helper.GetDatabaseSchema(conn, _schema, _tableName);
}
using (SqlTransaction transaction = conn.BeginTransaction())
{
//Bulk insert into temp table
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn, _sqlBulkCopyOptions, transaction))
{
try
{
bulkcopy.DestinationTableName = _helper.GetFullQualifyingTableName(conn.Database, _schema, _tableName);
_helper.MapColumns(bulkcopy, _columns, _customColumnMappings);
_helper.SetSqlBulkCopySettings(bulkcopy, _bulkCopyEnableStreaming, _bulkCopyBatchSize,
_bulkCopyNotifyAfter, _bulkCopyTimeout);
SqlCommand command = conn.CreateCommand();
command.Connection = conn;
command.Transaction = transaction;
if (_disableAllIndexes || (_disableIndexList != null && _disableIndexList.Any()))
{
command.CommandText = _helper.GetIndexManagementCmd(IndexOperation.Disable, _tableName,
_schema, conn, _disableIndexList, _disableAllIndexes);
command.ExecuteNonQuery();
}
// If InputOutput identity is selected, must use staging table.
if (_outputIdentity == ColumnDirection.InputOutput && dtCols != null)
{
command.CommandText = _helper.BuildCreateTempTable(_columns, dtCols, _outputIdentity);
command.ExecuteNonQuery();
_helper.InsertToTmpTable(conn, transaction, dt, _bulkCopyEnableStreaming, _bulkCopyBatchSize,
_bulkCopyNotifyAfter, _bulkCopyTimeout, _sqlBulkCopyOptions);
command.CommandText = _helper.GetInsertIntoStagingTableCmd(command, conn, _schema, _tableName,
_columns, _identityColumn, _outputIdentity);
command.ExecuteNonQuery();
_helper.LoadFromTmpOutputTable(command, _identityColumn, _outputIdentityDic, OperationType.Insert, _list);
}
else
{
bulkcopy.WriteToServer(dt);
}
if (_disableAllIndexes || (_disableIndexList != null && _disableIndexList.Any()))
{
command.CommandText = _helper.GetIndexManagementCmd(IndexOperation.Rebuild, _tableName,
_schema, conn, _disableIndexList, _disableAllIndexes);
command.ExecuteNonQuery();
}
transaction.Commit();
bulkcopy.Close();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
finally
{
conn.Close();
}
}
}
}
}
public static void Test(string srcConstr, string dstConstr, string dstTable)
{
DataSet dataset;
SqlDataAdapter adapter;
DataTable datatable;
using (SqlConnection dstConn = new SqlConnection(dstConstr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
Helpers.Execute(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];
string columnname;
foreach (DataColumn column in datatable.Columns)
{
columnname = column.ColumnName;
}
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();
bulkcopy.WriteToServer(datatable, DataRowState.Modified);
datatable.Rows.GetEnumerator().Reset();
bulkcopy.WriteToServer(datatable, DataRowState.Deleted);
bulkcopy.Close();
}
Helpers.VerifyResults(dstConn, dstTable, 4, 18);
}
}
finally
{
Helpers.TryExecute(dstCmd, "drop table " + dstTable);
}
}
}
protected void btnImportRecord_Click(object sender, EventArgs e)
{
if (IsValid)
{
// if you have Excel 2007 uncomment this line of code
//string excelConnectionString =string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0",path);
string ExcelContentType = "application/vnd.ms-excel";
string Excel2010ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
if (FileUpload1.HasFile)
{
//Check the Content Type of the file
if (FileUpload1.PostedFile.ContentType == ExcelContentType || FileUpload1.PostedFile.ContentType == Excel2010ContentType)
{
try
{
//Save file path
string path = string.Concat(Server.MapPath("../UploadFile/"), FileUpload1.FileName);
//Save File as Temp then you can delete it if you want
FileUpload1.SaveAs(path);
//string path = @"C:\Users\Johnney\Desktop\ExcelData.xls";
//For Office Excel 2010 please take a look to the followng link http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/0f03c2de-3ee2-475f-b6a2-f4efb97de302/#ae1e6748-297d-4c6e-8f1e-8108f438e62e
string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);
// Create Connection to Excel Workbook
using (OleDbConnection connection =
new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand
("Select * FROM [Sheet1$]", connection);
connection.Open();
// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = ConfigurationManager.ConnectionStrings["PMCDBConnectionString"].ConnectionString;
// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "tbl05_SinhVien";
bulkCopy.WriteToServer(dr);
panelError.Visible = true;
lblError.Text = "The data has been exported succefuly from Excel to SQL";
GridView1.DataBind();
}
}
}
StringBuilder sb = new StringBuilder();
sb.Append(@"<script type='text/javascript'>");
sb.Append("alert('Record Imported Successfully');");
sb.Append("$('#importModal').modal('hide');");
sb.Append(@"</script>");
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "ImportHideModalScript", sb.ToString(), false);
}
catch (Exception ex)
{
lblError.Text = ex.Message;
}
}
}
}
}
public static bool BulktoDb(DataTable elTable, BulkCopyOptions options, string tableName)
{
MOE.Common.Models.Repositories.IApplicationEventRepository errorRepository = MOE.Common.Models.Repositories.ApplicationEventRepositoryFactory.Create();
using (options.Connection)
{
using (var bulkCopy =
new SqlBulkCopy(options.ConnectionString, SqlBulkCopyOptions.UseInternalTransaction))
{
for (var i = 1; ; i++)
{
try
{
options.Connection.Open();
}
catch
{
Thread.Sleep(Settings.Default.SleepTime);
}
if (options.Connection.State == ConnectionState.Open)
{
if (Settings.Default.WriteToConsole)
{
Console.WriteLine("DB connection established");
}
break;
}
}
var sigId = "";
if (elTable.Rows.Count > 0)
{
var row = elTable.Rows[0];
sigId = row[0].ToString();
}
if (options.Connection.State == ConnectionState.Open)
{
bulkCopy.BulkCopyTimeout = Settings.Default.BulkCopyTimeout;
bulkCopy.BatchSize = Settings.Default.BulkCopyBatchSize;
if (Settings.Default.WriteToConsole)
{
Console.WriteLine(" For Signal {0} There are rows {1}", sigId, elTable.Rows.Count);
bulkCopy.SqlRowsCopied +=
OnSqlRowsCopied;
//Console.WriteLine( " For Signal {0}", sigId);
bulkCopy.NotifyAfter = Settings.Default.BulkCopyBatchSize;
}
bulkCopy.DestinationTableName = tableName;
if (elTable.Rows.Count > 0)
{
try
{
bulkCopy.WriteToServer(elTable);
if (Settings.Default.WriteToConsole)
{
Console.WriteLine("!!!!!!!!! The bulk insert executed for Signal " + sigId +
" !!!!!!!!!");
}
options.Connection.Close();
return(true);
}
catch (SqlException ex)
{
if (ex.Number == 2601)
{
if (Properties.Settings.Default.WriteToConsole)
{
errorRepository.QuickAdd("FTPFromAllcontrollers", "Signal", "BulktoDB_SQL.ex",
Models.ApplicationEvent.SeverityLevels.Medium,
"There is a permission error - " + sigId + " - " + ex.Message);
Console.WriteLine("**** There is a permission error - " + sigId + " *****");
}
}
else
{
if (Properties.Settings.Default.WriteToConsole)
{
//Console.WriteLine("****DATABASE ERROR*****");
errorRepository.QuickAdd("FTPFromAllcontrollers", "Signal", "BulktoDB_SQL.ex",
Models.ApplicationEvent.SeverityLevels.Medium,
"General Error - " + sigId + " - " + ex.Message);
Console.WriteLine("DATABASE ERROR - " + sigId + " - " + ex.Message);
}
}
options.Connection.Close();
return(false);
}
catch (Exception ex)
{
errorRepository.QuickAdd("FTPFromAllcontrollers", "Signal", "BulktoDB_Reg.ex",
Models.ApplicationEvent.SeverityLevels.Medium,
"General Error - " + sigId + " - " + ex.Message);
Console.WriteLine(ex);
return(false);
}
}
else
{
options.Connection.Close();
return(false);
}
}
else
{
return(false);
}
}
}
}
/// <summary>
/// Creates a new transaction and does one line at a time bulk insertions of the <paramref name="insert"/> to determine which line (and value)
/// is causing the problem. Transaction is always rolled back.
///
/// </summary>
/// <param name="e"></param>
/// <param name="insert"></param>
/// <param name="dt"></param>
/// <param name="serverForLineByLineInvestigation"></param>
/// <returns></returns>
private Exception AttemptLineByLineInsert(Exception e, SqlBulkCopy insert, DataTable dt, DiscoveredServer serverForLineByLineInvestigation)
{
int line = 1;
string firstPass = ExceptionToListOfInnerMessages(e, true);
firstPass = firstPass.Replace(Environment.NewLine, Environment.NewLine + "\t");
firstPass = Environment.NewLine + SR.MicrosoftSQLBulkCopy_AttemptLineByLineInsert_First_Pass_Exception_ + Environment.NewLine + firstPass;
//have to use a new object because current one could have a broken transaction associated with it
using (var con = (SqlConnection)serverForLineByLineInvestigation.GetConnection())
{
con.Open();
SqlTransaction investigationTransaction = con.BeginTransaction("Investigate BulkCopyFailure");
using (SqlBulkCopy investigationOneLineAtATime = new SqlBulkCopy(con, SqlBulkCopyOptions.KeepIdentity, investigationTransaction)
{
DestinationTableName = insert.DestinationTableName
})
{
foreach (SqlBulkCopyColumnMapping m in insert.ColumnMappings)
{
investigationOneLineAtATime.ColumnMappings.Add(m);
}
//try a line at a time
foreach (DataRow dr in dt.Rows)
{
try
{
investigationOneLineAtATime.WriteToServer(new[] { dr }); //try one line
line++;
}
catch (Exception exception)
{
if (BcpColIdToString(investigationOneLineAtATime, exception as SqlException, out string result, out SqlBulkCopyColumnMapping badMapping))
{
if (dt.Columns.Contains(badMapping.SourceColumn))
{
var sourceValue = dr[badMapping.SourceColumn];
var destColumn = base.TargetTableColumns.SingleOrDefault(c => c.GetRuntimeName().Equals(badMapping.DestinationColumn));
if (destColumn != null)
{
return(new FileLoadException(
string.Format(SR.MicrosoftSQLBulkCopy_AttemptLineByLineInsert_BulkInsert_failed_on_data_row__0__the_complaint_was_about_source_column____1____which_had_value____2____destination_data_type_was____3____4__5_, line, badMapping.SourceColumn, sourceValue, destColumn.DataType, Environment.NewLine, result), exception));
}
}
return(new Exception(string.Format(SR.MicrosoftSQLBulkCopy_AttemptLineByLineInsert_BulkInsert_failed_on_data_row__0___1_, line, result), e));
}
return(new FileLoadException(
string.Format(SR.MicrosoftSQLBulkCopy_AttemptLineByLineInsert_Second_Pass_Exception__Failed_to_load_data_row__0__the_following_values_were_rejected_by_the_database___1__2__3_, line, Environment.NewLine, string.Join(Environment.NewLine, dr.ItemArray), firstPass),
exception));
}
}
//it worked... how!?
investigationTransaction.Rollback();
con.Close();
}
return(new Exception(SR.MicrosoftSQLBulkCopy_AttemptLineByLineInsert_Second_Pass_Exception__Bulk_insert_failed_but_when_we_tried_to_repeat_it_a_line_at_a_time_it_worked + firstPass, e));
}
}
static void Main()
{
string connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
new SqlConnection(connectionString))
{
sourceConnection.Open();
// Perform an initial count on the destination table.
SqlCommand commandRowCount = new SqlCommand(
"SELECT COUNT(*) FROM " +
"dbo.BulkCopyDemoDifferentColumns;",
sourceConnection);
long countStart = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// Get data from the source table as a SqlDataReader.
SqlCommand commandSourceData = new SqlCommand(
"SELECT ProductID, Name, " +
"ProductNumber " +
"FROM Production.Product;", sourceConnection);
SqlDataReader reader =
commandSourceData.ExecuteReader();
// Set up the bulk copy object.
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(connectionString))
{
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoDifferentColumns";
// Set up the column mappings source and destination.
SqlBulkCopyColumnMapping mapID = new SqlBulkCopyColumnMapping();
mapID.SourceOrdinal = 0;
mapID.DestinationOrdinal = 0;
bulkCopy.ColumnMappings.Add(mapID);
SqlBulkCopyColumnMapping mapName = new SqlBulkCopyColumnMapping();
mapName.SourceOrdinal = 1;
mapName.DestinationOrdinal = 2;
bulkCopy.ColumnMappings.Add(mapName);
SqlBulkCopyColumnMapping mapNumber = new SqlBulkCopyColumnMapping();
mapNumber.SourceOrdinal = 2;
mapNumber.DestinationOrdinal = 1;
bulkCopy.ColumnMappings.Add(mapNumber);
// Write from the source to the destination.
try
{
bulkCopy.WriteToServer(reader);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Close the SqlDataReader. The SqlBulkCopy
// object is automatically closed at the end
// of the using block.
reader.Close();
}
}
// Perform a final count on the destination
// table to see how many rows were added.
long countEnd = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Ending row count = {0}", countEnd);
Console.WriteLine("{0} rows were added.", countEnd - countStart);
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
}
private void ExcelToTable(string UplodedFilePath)
{
DataSet ds = new DataSet();
try
{
string sCon = ConfigurationManager.ConnectionStrings["ESConnectionString"].ToString();
var dataTable = new DataTable();
string filePath = string.Empty;
using (SqlConnection conn = new SqlConnection(sCon))
{
conn.Open();
//filePath = @"C:\Users\GANAPATHY\Desktop\Excel\Pending Purchase orders (1).xlsx";
filePath = UplodedFilePath;
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format("SET FMTONLY ON; SELECT * FROM PendingPurchaseOrderExcel; SET FMTONLY OFF;");
dataTable.Load(cmd.ExecuteReader());
}
string filename = Path.GetFileName(filePath);
//FileUploadForPO.SaveAs(Server.MapPath("~/") + filename);
FileStream stream;
IExcelDataReader excelReader = null;
stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
string getExtension = Path.GetExtension(filePath);
if (getExtension == ".xls")
{
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (getExtension == ".xlsx")
{
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else if (getExtension == ".xlsb")
{
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
excelReader.IsFirstRowAsColumnNames = true;
ds = excelReader.AsDataSet();
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
dataTable = ds.Tables[0];
}
}
excelReader.Close();
using (SqlConnection con = new SqlConnection(sCon))
{
con.Open();
// FINALLY, LOAD DATA INTO THE DATABASE TABLE.
using (var oSqlBulk = new SqlBulkCopy(con))
{
oSqlBulk.DestinationTableName = "PendingPurchaseOrderExcel"; // TABLE NAME.
oSqlBulk.ColumnMappings.Add("PO type", "POType");
oSqlBulk.ColumnMappings.Add("PO number", "PurchaseDocument");
oSqlBulk.ColumnMappings.Add("Item no.", "PurchaseDocumentItem");
oSqlBulk.ColumnMappings.Add("Schedule Line", "ScheduleLine");
oSqlBulk.ColumnMappings.Add("PO crd. date", "DocDate");//
oSqlBulk.ColumnMappings.Add("Acc. ast. cat.", "AstCat");
oSqlBulk.ColumnMappings.Add("Vendor code", "VendorCode");
oSqlBulk.ColumnMappings.Add("Vendor name", "VendorName");
oSqlBulk.ColumnMappings.Add("Material code", "Material");
oSqlBulk.ColumnMappings.Add("Material Description", "MaterialDescription");
oSqlBulk.ColumnMappings.Add("PO / Targ Qty", "POQty");
oSqlBulk.ColumnMappings.Add("UOM", "OUN");
oSqlBulk.ColumnMappings.Add("Net Price", "NetPrice");
oSqlBulk.ColumnMappings.Add("Currency", "Currency");
oSqlBulk.ColumnMappings.Add("Exchange rate", "ExchangeRate");
oSqlBulk.ColumnMappings.Add("Scheduled Qty", "ScheduleQty");
oSqlBulk.ColumnMappings.Add("Schedule value(INR)", "ScheduleValue");
oSqlBulk.ColumnMappings.Add("Schedule value in FC", "ScheduleValueFC");
oSqlBulk.ColumnMappings.Add("Delivery date", "DeliveryDate");
oSqlBulk.ColumnMappings.Add("Delivered", "DeliveredQty");
oSqlBulk.ColumnMappings.Add("Open Qty", "OpenQty");
oSqlBulk.ColumnMappings.Add("Balance Value (INR)", "BalanceValue");
oSqlBulk.ColumnMappings.Add("Balance Value in FC", "BalanceValueFC");
oSqlBulk.ColumnMappings.Add("Purchase goup", "PurchaseGroup");
oSqlBulk.ColumnMappings.Add("Purc. group name", "PurchaseGroupName");
oSqlBulk.ColumnMappings.Add("PYT Term", "PYTTerm");
oSqlBulk.ColumnMappings.Add("PYT desc.", "PYTDesc");
oSqlBulk.ColumnMappings.Add("PYT - no. of days", "PYTNod");
oSqlBulk.ColumnMappings.Add("PYT-2 no.of days", "PYT2Nod");
oSqlBulk.ColumnMappings.Add("PYT-3 no.of days", "PYT3Nod");
oSqlBulk.ColumnMappings.Add("Possibe Payment Dt.", "PossiblePaymentDate");
oSqlBulk.ColumnMappings.Add("Drg No.", "DrgNo");
oSqlBulk.ColumnMappings.Add("G/L acct No.", "GLAccountNo");
oSqlBulk.ColumnMappings.Add("G/L acct desc", "GLAccountDesc");
oSqlBulk.ColumnMappings.Add("Incoterms (part 1)", "IncoTerms1");
oSqlBulk.ColumnMappings.Add("Incoterms (part 2)", "IncoTerms2");
oSqlBulk.ColumnMappings.Add("Plant", "Plant");
oSqlBulk.ColumnMappings.Add("Storage Loc.", "StorageLocation");
oSqlBulk.ColumnMappings.Add("Tax code", "TaxCode");
oSqlBulk.ColumnMappings.Add("Material Type", "MaterialType");
oSqlBulk.ColumnMappings.Add("DCI", "DCI");
oSqlBulk.ColumnMappings.Add("GR-IV", "GRIV");
oSqlBulk.ColumnMappings.Add("Under Tol", "UnderTOI");
oSqlBulk.ColumnMappings.Add("Over tol", "OverTOI");
oSqlBulk.ColumnMappings.Add("Shipping inst", "ShippingInstruction");
oSqlBulk.ColumnMappings.Add("Quantity Conv", "QtyConvert");
oSqlBulk.ColumnMappings.Add("Quantity Conv", "QtyConvert2");
oSqlBulk.ColumnMappings.Add("Price ut", "PriceUT");
oSqlBulk.ColumnMappings.Add("Asset", "Asset");
oSqlBulk.ColumnMappings.Add("MRP Cont.", "MRPController");
oSqlBulk.ColumnMappings.Add("Material price determination mode", "MPDM");
oSqlBulk.ColumnMappings.Add("Radiography Test Coverage", "RTCoverage");
oSqlBulk.ColumnMappings.Add("Radiography Test Agent", "RTAgent");
oSqlBulk.ColumnMappings.Add("Impact test requirement", "ImpactTest");
oSqlBulk.ColumnMappings.Add("Component property ID", "ComponentPropertyID");
oSqlBulk.ColumnMappings.Add("Customer project name", "CustomerProjectName");
oSqlBulk.ColumnMappings.Add("Document number", "DocumentNo");
oSqlBulk.ColumnMappings.Add("Document part", "DocumentPart");
oSqlBulk.ColumnMappings.Add("Document version", "DocumentVersion");
oSqlBulk.ColumnMappings.Add("Industry Std Desc.", "IndustryStdDesc");
oSqlBulk.ColumnMappings.Add("Ext. Material Grp", "ExtMatGroup");
oSqlBulk.ColumnMappings.Add("Product type", "ProductType");
oSqlBulk.ColumnMappings.Add("Document Item Processing Status", "DIPS");
oSqlBulk.ColumnMappings.Add("Sales Document", "SalesDocument");
oSqlBulk.ColumnMappings.Add("Sales order item", "SalesOrderItem");
oSqlBulk.ColumnMappings.Add("CDD", "CDD");
oSqlBulk.ColumnMappings.Add("Shiptocode", "ShipToCode");
oSqlBulk.ColumnMappings.Add("Ship to party name", "ShipToName");
oSqlBulk.ColumnMappings.Add("Release indicator", "ReleaseIndicator");
oSqlBulk.ColumnMappings.Add("Release indicator desc", "ReleaseIndicatorDesc");
oSqlBulk.ColumnMappings.Add("Material group", "MaterialGroup");
oSqlBulk.ColumnMappings.Add("HSN/SAC code", "HSNCode");
oSqlBulk.ColumnMappings.Add("HSN/SAC Desp.", "HSNDesc");
//oSqlBulk.ColumnMappings.Add("Pos", "L1Qty");
//oSqlBulk.ColumnMappings.Add("Pos", "L1Date");
//oSqlBulk.ColumnMappings.Add("Pos", "L2Qty");
//oSqlBulk.ColumnMappings.Add("Pos", "L2Date");
//oSqlBulk.ColumnMappings.Add("Pos", "L3Qty");
//oSqlBulk.ColumnMappings.Add("Pos", "L3Date");
//oSqlBulk.ColumnMappings.Add("Pos", "SupplierRemarks");
//oSqlBulk.ColumnMappings.Add("Pos", "LiveOrder");
//oSqlBulk.ColumnMappings.Add("Pos", "Urgent");
//oSqlBulk.ColumnMappings.Add("Pos", "OANumber");
//oSqlBulk.ColumnMappings.Add("Pos", "StatusCode");
//oSqlBulk.ColumnMappings.Add("Pos", "CreatedDateTime");
oSqlBulk.WriteToServer(dataTable);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
static void Main(string[] args)
{
string tmpTable = @"create table #TempTable
(
[Column1] [varchar](50) NOT NULL,
[Column2] [varchar](50) NOT NULL,
[Column3] [varchar](50) NOT NULL
)";
string connString = "Data Source=xxxx.database.windows.net;" +
"Initial Catalog=Adventureworks;" +
"User id=xxxxxx;" +
"Password=xxxxxx;";
var dataTable = new DataTable();
dataTable.Columns.Add("Column1", typeof(string));
dataTable.Columns.Add("Column2", typeof(string));
dataTable.Columns.Add("Column3", typeof(string));
dataTable.BeginLoadData();
for (int i = 0; i < 10000; i++)
{
var r = dataTable.NewRow();
r[0] = $"column1{i}";
r[1] = $"column2{i}";
r[2] = $"column3{i}";
dataTable.Rows.Add(r);
}
dataTable.EndLoadData();
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
SqlCommand cmd = new SqlCommand(tmpTable, connection);
cmd.ExecuteNonQuery();
try
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.NotifyAfter = 1000;
bulkCopy.SqlRowsCopied += (s, a) => Console.WriteLine($"{a.RowsCopied} rows");
bulkCopy.DestinationTableName = "#TempTable";
bulkCopy.WriteToServer(dataTable);
//string mergeSql = "<Will eventually have merge statement here>";
//cmd.CommandText = mergeSql;
//int results = cmd.ExecuteNonQuery();
cmd.CommandText = "DROP TABLE #TempTable";
cmd.ExecuteNonQuery();
}
}
catch (Exception)
{
throw;
}
finally
{
SqlCommand final = new SqlCommand("DROP TABLE #TempTable", connection);
final.ExecuteNonQuery();
}
}
}
public void ImportBodies()
{
const string Bodies = @"d:\Data\Downloads\bodies.json";
var readJsonTotal = new Stopwatch();
var readJsonOneRow = new Stopwatch();
var writeTotal = new Stopwatch();
var write10Rows = new Stopwatch();
long rowsDeserialized = 0;
long rowsWithErrors = 0;
writeTotal.Start();
using (var sr = new StreamReader(File.OpenRead(Bodies)))
using (var jr = new JsonTextReader(sr)
{
CloseInput = false, SupportMultipleContent = true
})
using (var db1 = new SqlConnection(@"Data Source = (LocalDB)\EDMaster; Initial Catalog = ED_Body1"))
using (var db2 = new SqlConnection(@"Data Source = (LocalDB)\EDMaster; Initial Catalog = ED_Body2"))
using (var db3 = new SqlConnection(@"Data Source = (LocalDB)\EDMaster; Initial Catalog = ED_Body3"))
using (var db4 = new SqlConnection(@"Data Source = (LocalDB)\EDMaster; Initial Catalog = ED_Body4"))
{
db1.Open();
db2.Open();
db3.Open();
db4.Open();
var batch1 = new SqlBulkCopy(db1);
var batch2 = new SqlBulkCopy(db2);
var batch3 = new SqlBulkCopy(db3);
var batch4 = new SqlBulkCopy(db4);
batch1.DestinationTableName = "tblEDBody";
batch2.DestinationTableName = "tblEDBody";
batch3.DestinationTableName = "tblEDBody";
batch4.DestinationTableName = "tblEDBody";
var importTable1 = DatabaseManager.GetDataTable("tblEDBody");
var importTable2 = DatabaseManager.GetDataTable("tblEDBody");
var importTable3 = DatabaseManager.GetDataTable("tblEDBody");
var importTable4 = DatabaseManager.GetDataTable("tblEDBody");
readJsonTotal.Start();
writeTotal.Start();
var serializer = new JsonSerializer();
sr.ReadLine();
try
{
while (jr.Read())
{
var body = serializer.Deserialize <EdsmBody>(jr);
rowsDeserialized++;
var bodyNumber = Math.Abs(body.SystemName.GetHashCode() % 4) + 1;
if (bodyNumber == 1)
{
importTable1.Rows.Add(body.Id, body.Id64, body.BodyId, body.Name, body.Type, body.SubType, body.SystemId, body.SystemId64, body.SystemName);
}
else if (bodyNumber == 2)
{
importTable2.Rows.Add(body.Id, body.Id64, body.BodyId, body.Name, body.Type, body.SubType, body.SystemId, body.SystemId64, body.SystemName);
}
else if (bodyNumber == 3)
{
importTable3.Rows.Add(body.Id, body.Id64, body.BodyId, body.Name, body.Type, body.SubType, body.SystemId, body.SystemId64, body.SystemName);
}
else if (bodyNumber == 4)
{
importTable4.Rows.Add(body.Id, body.Id64, body.BodyId, body.Name, body.Type, body.SubType, body.SystemId, body.SystemId64, body.SystemName);
}
if (importTable1.Rows.Count > 500)
{
importTable1.AcceptChanges();
batch1.WriteToServer(importTable1);
importTable1.Rows.Clear();
}
if (importTable2.Rows.Count > 500)
{
importTable2.AcceptChanges();
batch2.WriteToServer(importTable2);
importTable2.Rows.Clear();
}
if (importTable3.Rows.Count > 500)
{
importTable3.AcceptChanges();
batch3.WriteToServer(importTable3);
importTable3.Rows.Clear();
}
if (importTable4.Rows.Count > 500)
{
importTable4.AcceptChanges();
batch4.WriteToServer(importTable4);
importTable4.Rows.Clear();
}
if ((rowsDeserialized % 100000) == 0)
{
Console.WriteLine($"{rowsDeserialized} rows read");
}
}
}
catch (JsonReaderException ex)
{
if (jr.TokenType == JsonToken.EndArray)
{
Console.WriteLine(ex.Message);
}
else
{
throw;
}
}
if (importTable1.Rows.Count > 0)
{
importTable1.AcceptChanges();
batch1.WriteToServer(importTable1);
}
if (importTable2.Rows.Count > 0)
{
importTable2.AcceptChanges();
batch2.WriteToServer(importTable2);
}
if (importTable3.Rows.Count > 0)
{
importTable3.AcceptChanges();
batch3.WriteToServer(importTable3);
}
if (importTable4.Rows.Count > 0)
{
importTable4.AcceptChanges();
batch4.WriteToServer(importTable4);
}
Console.WriteLine($"{rowsDeserialized} rows deserialized");
Console.WriteLine($"{rowsWithErrors} rows had errors");
}
}
//Write a DataTable to the SQL server
public static void SqlWriteDataTable(string tableName, SqlConnection sqlConnection, DataTable dataTable, CreateOutputLog log)
{
//Collect the list of tables from the SQL server
DataTable dt = sqlConnection.GetSchema("Tables");
List <string> existingTables = new List <string>();
foreach (DataRow row in dt.Rows)
{
string existingTableName = (string)row[2];
existingTables.Add(existingTableName);
}
//Check if the SQL server already has a table with the same name
if (existingTables.Contains(tableName))
{
//Record in the log the database table name
log.m_dbTableName = tableName;
using (sqlConnection)
{
//Report in the console that the data is going to be copied
Console.WriteLine("Copying Data To SQL Table");
//Cycle through each row in the DataTable
foreach (DataRow row in dataTable.Rows)
{
bool skip = false;
//Determing if the table already has a row with the SessionId value. If so, add it to the log as an existing entry and set the skip boolean to true because we don't want duplicate entries
using (SqlCommand command = new SqlCommand("SELECT COUNT (*) FROM " + tableName + " WHERE SessionId LIKE '" + row["SessionId"] + "'", sqlConnection))
{
try
{
Int32 count = Convert.ToInt32(command.ExecuteScalar());
if (count > 0)
{
skip = true;
StringBuilder sb = GeneralOperations.BuildCSVStringFromDataTableRow(dataTable, row);
log.m_existingDbEntries.Add(sb.ToString());
}
}
catch { continue; }
}
//Assuming this is a new table entry, continue
if (skip == false)
{
//Specify an entry into the database table by adding the values from the DataTable
using (SqlCommand comm = new SqlCommand("INSERT INTO " + tableName + " (SessionId, ProjectNumber, FileName, FilePath, UserName, Build, Journal, Host, Server, Central, Local, DateTimeStart, DateTimeEnd, Duration) VALUES (@v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12, @v13, @v14)"))
{
comm.Connection = sqlConnection;
comm.Parameters.AddWithValue("@v1", row["SessionId"]);
comm.Parameters.AddWithValue("@v2", row["ProjectNumber"]);
comm.Parameters.AddWithValue("@v3", row["FileName"]);
comm.Parameters.AddWithValue("@v4", row["FilePath"]);
comm.Parameters.AddWithValue("@v5", row["UserName"]);
comm.Parameters.AddWithValue("@v6", row["Build"]);
comm.Parameters.AddWithValue("@v7", row["Journal"]);
comm.Parameters.AddWithValue("@v8", row["Host"]);
comm.Parameters.AddWithValue("@v9", row["Server"]);
comm.Parameters.AddWithValue("@v10", row["Central"]);
comm.Parameters.AddWithValue("@v11", row["Local"]);
comm.Parameters.AddWithValue("@v12", row["DateTimeStart"]);
comm.Parameters.AddWithValue("@v13", row["DateTimeEnd"]);
comm.Parameters.AddWithValue("@v14", row["Duration"]);
try
{
//Execute the command to insert the row into the database table and add the row to the log for new entries
comm.ExecuteNonQuery();
StringBuilder sb = GeneralOperations.BuildCSVStringFromDataTableRow(dataTable, row);
log.m_newDbEntries.Add(sb.ToString());
}
catch (SqlException e)
{
//If something should fail, write it to the console and pause until acknowledged
Console.WriteLine(e.Message);
Console.ReadLine();
}
}
}
}
}
SqlCloseConnection(sqlConnection);
}
//If the database table does not yet exist, create a new table using the SqlBulkCopy
else
{
log.m_dbTableName = tableName;
Console.WriteLine("Creating New SQL Table");
try
{
//Create a new SQL table with the specified headers and data types
SqlCommand sqlCreateTable = new SqlCommand("CREATE TABLE " + tableName + " (SessionId text, ProjectNumber text, FileName text, FilePath text, UserName text, Build text, Journal text, Host text, Server text, Central text, Local text, DateTimeStart datetime, DateTimeEnd datetime, Duration float)", sqlConnection);
sqlCreateTable.ExecuteNonQuery();
}
catch (SqlException f)
{
//If something should fail, write it to the console and pause until acknowledged
Console.WriteLine(f.Message);
Console.ReadLine();
}
//Try to simply do a bulk copy to the table
try
{
SqlBulkCopyOptions options = SqlBulkCopyOptions.Default;
Console.WriteLine("Copying Data To SQL Table");
using (SqlBulkCopy s = new SqlBulkCopy(sqlConnection, options, null))
{
//Specify the destination table and map each column from the DataTable to the database table columns
s.DestinationTableName = "[" + tableName + "]";
foreach (DataColumn appColumn in dataTable.Columns)
{
s.ColumnMappings.Add(appColumn.ToString(), appColumn.ToString());
}
s.WriteToServer(dataTable);
}
//Build a string of the new table and write it to the log as new data entries
StringBuilder sb = GeneralOperations.BuildCSVStringFromDataTable(dt);
log.m_newDbEntries.Add(sb.ToString());
}
catch (SqlException g)
{
//If something should fail, write it to the console and pause until acknowledged
Console.WriteLine(g.Message);
Console.ReadLine();
}
}
//Close the connection and write the log of the user actions to the SQL table for tracking data writes
DatabaseOperations.SqlCloseConnection(sqlConnection);
SqlLogWriter(tableName);
}
public void upload(DataTable dataTable, int bulkSize, string tableName)
{
{
var builder = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
.AddEnvironmentVariables();
IConfiguration Configuration;
builder.AddEnvironmentVariables();
Configuration = builder.Build();
string connectionstring = Configuration.GetConnectionString("BloggingDatabase");
using (SqlConnection sourceConnection = new SqlConnection(connectionstring))
{
sourceConnection.Open();
var command = sourceConnection.CreateCommand();
command.Connection = sourceConnection;
command.CommandText = "DELETE FROM " + tableName;
command.ExecuteNonQuery();
using (SqlTransaction trans = sourceConnection.BeginTransaction())
{
try
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionstring
, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.UseInternalTransaction))
{
bulkCopy.DestinationTableName = tableName;
// Set the BatchSize.
bulkCopy.BatchSize = bulkSize;
bulkCopy.BulkCopyTimeout = 0;
try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(dataTable);
trans.Commit();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sourceConnection.Close();
}
}
}
catch (Exception e)
{
trans.Rollback();
throw e;
}
}
}
}
}
private void ExportDataToSQLServer(string connStr, string schemaName, bool truncateTables)
{
var metadataPane = this.Document.MetadataPane;
// TODO: Use async but to be well done need to apply async on the DBCommand & DBConnection
// TODO: Show warning message?
if (metadataPane.SelectedModel == null)
{
return;
}
Document.QueryStopWatch.Start();
using (var conn = new SqlConnection(connStr))
{
conn.Open();
var currentTableIdx = 0;
var selectedTables = Tables.Where(t => t.IsSelected);
totalTableCnt = selectedTables.Count();
foreach (var table in selectedTables)
{
try
{
EventAggregator.PublishOnUIThread(new ExportStatusUpdateEvent(table));
currentTable = table;
currentTable.Status = ExportStatus.Exporting;
currentTableIdx++;
var daxQuery = $"EVALUATE {table.DaxName}";
using (var statusMsg = new StatusBarMessage(Document, $"Exporting {table.Caption}"))
using (var reader = metadataPane.Connection.ExecuteReader(daxQuery))
{
sqlTableName = $"[{schemaName}].[{table.Caption}]";
EnsureSQLTableExists(conn, sqlTableName, reader);
using (var transaction = conn.BeginTransaction())
{
if (truncateTables)
{
using (var cmd = new SqlCommand($"truncate table {sqlTableName}", conn))
{
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
}
}
using (var sqlBulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock, transaction))
{
sqlBulkCopy.DestinationTableName = sqlTableName;
sqlBulkCopy.BatchSize = 5000;
sqlBulkCopy.NotifyAfter = 5000;
sqlBulkCopy.SqlRowsCopied += SqlBulkCopy_SqlRowsCopied;
sqlBulkCopy.EnableStreaming = true;
sqlBulkCopy.WriteToServer(reader);
currentTable.RowCount = sqlBulkCopy.RowsCopiedCount();
}
transaction.Commit();
currentTable.Status = ExportStatus.Done;
}
}
// jump out of table loop if we have been cancelled
if (CancelRequested)
{
EventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Warning, "Data Export Cancelled"));
break;
}
EventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Information, $"Exported {table.Caption} to {sqlTableName}"));
currentTable.Status = ExportStatus.Done;
}
catch (Exception ex)
{
currentTable.Status = ExportStatus.Error;
Log.Error(ex, "{class} {method} {message}", "ExportDataWizardViewModel", "ExportDataToSQLServer", ex.Message);
EventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Error, $"Error exporting data to SQL Server: {ex.Message}"));
EventAggregator.PublishOnUIThread(new ExportStatusUpdateEvent(currentTable, true));
continue; // skip to next table on error
}
}
}
Document.QueryStopWatch.Stop();
EventAggregator.PublishOnUIThread(new OutputMessage(MessageType.Information, $"Model Export Complete: {currentTableIdx} tables exported", Document.QueryStopWatch.ElapsedMilliseconds));
EventAggregator.PublishOnUIThread(new ExportStatusUpdateEvent(currentTable, true));
Document.QueryStopWatch.Reset();
}
static void Main(string[] args)
{
var cats = DesirializeCats();
var furs = new List <string>();
foreach (var value in Enum.GetValues(typeof(FurTypesEnum)))
{
furs.Add(value.ToString());
}
foreach (var cat in cats)
{
string fur = "";
foreach (var item in cat.FurTypes)
{
fur += $"{item};";
}
Console.WriteLine(cat.CatUrl);
Console.WriteLine(fur);
}
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
connection.Query("CreateTables", commandType: CommandType.StoredProcedure);
using (var bulkCopy = new SqlBulkCopy(connection))
using (var reader = ObjectReader.Create(cats, "CatUrl", "IsAStar", "IsTopOne", "IsAlone", "Rating", "Wins", "ViewsNumber", "ProbabilityWeight"))
{
var catUrlMap = new SqlBulkCopyColumnMapping("CatUrl", "CatUrl");
bulkCopy.ColumnMappings.Add(catUrlMap);
var isAStarMap = new SqlBulkCopyColumnMapping("IsAStar", "IsAStar");
bulkCopy.ColumnMappings.Add(isAStarMap);
var isTopOneMap = new SqlBulkCopyColumnMapping("IsTopOne", "IsTopOne");
bulkCopy.ColumnMappings.Add(isTopOneMap);
var isAloneMap = new SqlBulkCopyColumnMapping("IsAlone", "IsAlone");
bulkCopy.ColumnMappings.Add(isAloneMap);
var ratingMap = new SqlBulkCopyColumnMapping("Rating", "Rating");
bulkCopy.ColumnMappings.Add(ratingMap);
var winsMap = new SqlBulkCopyColumnMapping("Wins", "Wins");
bulkCopy.ColumnMappings.Add(winsMap);
var viewsNumberMap = new SqlBulkCopyColumnMapping("ViewsNumber", "ViewsNumber");
bulkCopy.ColumnMappings.Add(viewsNumberMap);
var probabilityWeightMap = new SqlBulkCopyColumnMapping("ProbabilityWeight", "ProbabilityWeight");
bulkCopy.ColumnMappings.Add(probabilityWeightMap);
bulkCopy.BatchSize = cats.Count();
bulkCopy.DestinationTableName = "Cats";
bulkCopy.WriteToServer(reader);
}
using (var bulkCopy = new SqlBulkCopy(connection))
using (var reader = ObjectReader.Create(furs.Select(x => new { FurType = x }), "FurType"))
{
var furTypeMap = new SqlBulkCopyColumnMapping("FurType", "FurType");
bulkCopy.ColumnMappings.Add(furTypeMap);
bulkCopy.BatchSize = furs.Count();
bulkCopy.DestinationTableName = "FurTypes";
bulkCopy.WriteToServer(reader);
}
var catsWithId = connection.Query <Cat>(@"SELECT Id, CatUrl, IsAStar, IsTopOne, IsAlone, Rating FROM Cats");
cats.ToList().ForEach(x => x.Id = catsWithId.Where(y => y.CatUrl == x.CatUrl).Select(z => z.Id).FirstOrDefault());
var association = new List <Tuple <int, int> >();
foreach (var cat in cats)
{
foreach (var type in cat.FurTypes)
{
association.Add(new Tuple <int, int>(cat.Id, (int)type));
}
}
using (var bulkCopy = new SqlBulkCopy(connection))
using (var reader = ObjectReader.Create(association.Select(x => new { CatId = x.Item1, FurTypeId = x.Item2 }), "CatId", "FurTypeId"))
{
var catIdMap = new SqlBulkCopyColumnMapping("CatId", "CatId");
bulkCopy.ColumnMappings.Add(catIdMap);
var furTypeIdMap = new SqlBulkCopyColumnMapping("FurTypeId", "FurTypeId");
bulkCopy.ColumnMappings.Add(furTypeIdMap);
bulkCopy.BatchSize = association.Count();
bulkCopy.DestinationTableName = "CatsFurs";
bulkCopy.WriteToServer(reader);
}
}
}
private void BtnSave_Click(object sender, EventArgs e)
{
//string Cade_="";//DTTDATE.Text.ToString().Substring(0, 4) + DTTDATE.Text.ToString().Substring(5, 2) + DTTDATE.Text.ToString().Substring(7, 2);
//SqlConnection conn = sqlcon.getcon("");
//DataSet ds = new DataSet();
//string sqlselect;
//sqlselect = "select max(cade) as cade from Wph_Report where cade like '%" + DTTDATE.Value.ToString("yyyyMMdd") + "%'";
//SqlDataAdapter sqlDaper = new SqlDataAdapter(sqlselect, conn);
//conn.Open();
//sqlDaper.Fill(ds);
//if (ds.Tables[0].Rows[0]["Cade"].ToString() == "")
//{
// Cade_ = "0001";
//}
//else
//{
// Cade_ = ("0000" + (Convert.ToInt32(ds.Tables[0].Rows[0]["Cade"].ToString().Substring(ds.Tables[0].Rows[0]["Cade"].ToString().Length - 4, 4)) + 1).ToString())
// .Substring(("0000" + (Convert.ToInt32(ds.Tables[0].Rows[0]["Cade"].ToString().Substring(ds.Tables[0].Rows[0]["Cade"].ToString().Length - 4, 4)) + 1).ToString()).Length-4,4);
//}
//conn.Close();
//string strsql = "";
//try
//{
// for (int i = 0; i < ProudctDGV.Rows.Count; i++)//得到总行数并在之内循环
// {
// strsql += "insert into Wph_Report (Cade,Date,Special,pid,SPPrice,ColourID,SizeID,NO,StorageID) values ('WB" + DTTDATE.Value.ToString("yyyyMMdd") + Cade_ + "','"
// + DTTDATE.Value.ToString("yyyy-MM-dd") + "','" + txtSpecial.Text.ToString() + "','" + ProudctDGV.Rows[i].Cells["Pid"].Value.ToString() +
// "','" + ProudctDGV.Rows[i].Cells["SPPrice"].Value.ToString() + "','" + ProudctDGV.Rows[i].Cells["ColourID"].Value.ToString() +
// "','" + ProudctDGV.Rows[i].Cells["SizeID"].Value.ToString() + "','" + ProudctDGV.Rows[i].Cells["NOs"].Value.ToString() + "','" +
// ProudctDGV.Rows[i].Cells["StorageID"].Value.ToString() + "') ;";
// }
// // //for (int j = 0; j < cell; j++)//得到总列数并在之内循环
// // //{
// // string Product = ProudctDGV.Rows[i].Cells[3].Value.ToString();
// // string ok_ = ProudctDGV.Rows[i].Cells[0].Value.ToString();
// // //}
// // string str = "select * from Wph_Report where sizeid='" + sizeid + "' and pid='" + Product + "'";
// // SqlDataAdapter sqldaper = new SqlDataAdapter(str, conn);
// // DataSet ds = new DataSet();
// // conn.Open();
// // sqldaper.Fill(ds);
// // if (ok_ == "True")
// // {
// // if (ds.Tables[0].Rows.Count <= 0)
// // {
// // strsql += "insert into m_ProductSize(sizeid,pid) values (" + sizeid + "," + Product + ") ";
// // }
// // conn.Close();
// // }
// // if (ok_ == "False")
// // {
// // if (ds.Tables[0].Rows.Count > 0)
// // {
// // strsql += "delete from m_ProductSize where sizeid='" + sizeid + "' and pid='" + Product + "' ";
// // }
// // conn.Close();
// // }
// //}
// conn.Open();
// SqlCommand cmd = new SqlCommand(strsql, conn);
// cmd.ExecuteNonQuery();
// conn.Close();
// MessageBox.Show("数据更新成功！", "系统提示：", MessageBoxButtons.OK);
//}
//catch (Exception ex)
//{
// Console.WriteLine(ex.Message);
//}
string sqlselect = "";
//for (int i = 0; i < DataDGV.Rows.Count; i++)//得到总行数并在之内循环
//{
// if (sqlselect != "")
// {
// sqlselect += " or ";
// }
// sqlselect += " OrderCade ='" + DataDGV.Rows[i].Cells["OrderCade"].Value.ToString() + "'";
//}
dt.Columns.Remove("item_no");
dt.Columns.Remove("SDname");
dt.Columns.Remove("STname");
dt.Columns.Remove("s_color");
dt.Columns.Remove("price_tag");
dt.Columns.Remove("SDPrice");
dt.Columns.Remove("stmoney");
SqlConnection conn = sqlcon.getcon("");
DataSet ds = new DataSet();
SqlDataAdapter sqlDaper = new SqlDataAdapter("select * from Wph_Report where Special='" + txtSpecial.Text.ToString() + "'", conn);
conn.Open();
sqlDaper.Fill(ds);
conn.Close();
if (ds.Tables[0].Rows.Count > 0)
{
if (MessageBox.Show("\n该专场已存在，是否从新保存 \n\n\n ", "系统提示", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
conn.Open();
SqlCommand cmd = new SqlCommand("delete from Wph_Report where Special='" + txtSpecial.Text.ToString() + "'", conn);
cmd.ExecuteNonQuery();
conn.Close();
}
else
{
return;
}
}
try
{
using (TransactionScope scope = new TransactionScope())
{
conn.Open();
using (SqlBulkCopy sbc = new SqlBulkCopy(conn))
{
////服务器上目标表的名称
sbc.DestinationTableName = "Wph_Report";
sbc.WriteToServer(dt);
scope.Complete();//有效的事务
}
conn.Close();
}
////}
MessageBox.Show("数据更新成功！", "系统提示：", MessageBoxButtons.OK);
}
catch (Exception ex)
{
MessageBox.Show("数据更新失败！", "系统提示：", MessageBoxButtons.OK);
Console.WriteLine(ex.Message);
}
ProudctDGV.DataSource = "";
}
public static bool BulkCopy(DataTable sourceTable, string sDestTable)
{
// mpp mat.
using (var scon = Connections.Connect())
{
SqlBulkCopy bc = new SqlBulkCopy(scon);
bc.DestinationTableName = sDestTable;
bc.WriteToServer(sourceTable);
return true;
}
}
private void ProcesoCarga()
{
objclsfrmBases.MensajeProceso = "Procesando carga de datos...";
int porcentaje = 0;
int intTotal = 2400000;
decimal j = 0;
try
{
//Eliminar los registros solicitados
bool resultado = m_procesogestionDP.EliminarProcesoGestionTasas(conexion);
#region Crear dataTable
DataTable dtDP = new DataTable();
dtDP.Columns.Add("codcategoria", typeof(string));
dtDP.Columns.Add("categoria", typeof(string));
dtDP.Columns.Add("codsubcategoria", typeof(string));
dtDP.Columns.Add("subcategoria", typeof(string));
dtDP.Columns.Add("cuenta", typeof(string));
dtDP.Columns.Add("tipodocumento", typeof(string));
dtDP.Columns.Add("numerodocumento", typeof(string));
dtDP.Columns.Add("nombre", typeof(string));
dtDP.Columns.Add("ejecutivo", typeof(string));
dtDP.Columns.Add("estado", typeof(string));
dtDP.Columns.Add("descestado", typeof(string));
dtDP.Columns.Add("fecproceso", typeof(string));
dtDP.Columns.Add("mesproceso", typeof(string));
dtDP.Columns.Add("fecapertura", typeof(string));
dtDP.Columns.Add("mesapertura", typeof(string));
dtDP.Columns.Add("fecrenovacion", typeof(string));
dtDP.Columns.Add("mesrenovacion", typeof(string));
dtDP.Columns.Add("fecvencimiento", typeof(string));
dtDP.Columns.Add("mesvencimiento", typeof(string));
dtDP.Columns.Add("msaldo", typeof(string));
dtDP.Columns.Add("tasa", typeof(string));
dtDP.Columns.Add("numeradortasa", typeof(double));
dtDP.Columns.Add("tipomoneda", typeof(string));
dtDP.Columns.Add("plazo", typeof(string));
dtDP.Columns.Add("plazoactual", typeof(string));
dtDP.Columns.Add("numeradorplzpact", typeof(string));
dtDP.Columns.Add("numeradorplzact", typeof(string));
dtDP.Columns.Add("saldodisponible", typeof(string));
dtDP.Columns.Add("rucempleador", typeof(string));
dtDP.Columns.Add("codtienda", typeof(string));
dtDP.Columns.Add("desctienda", typeof(string));
dtDP.Columns.Add("montoconvenio", typeof(string));
dtDP.Columns.Add("diacargo", typeof(string));
dtDP.Columns.Add("fecinicioconv", typeof(string));
dtDP.Columns.Add("cuentacmr", typeof(string));
dtDP.Columns.Add("tarjetacmr", typeof(string));
dtDP.Columns.Add("codigoejecutivo", typeof(string));
dtDP.Columns.Add("tipocambio", typeof(decimal));
#endregion
using (var sr = new StreamReader(objclsfrmBases.pathNombreArchivosDP, System.Text.Encoding.Default, false))
{
var reader = new CsvReader(sr);
IEnumerable <EntidadProyMDIBFGestionTasasDP.ProcesoGestionDP> records = reader.GetRecords <EntidadProyMDIBFGestionTasasDP.ProcesoGestionDP>();
objclsfrmBases.BotonMenuMantenimientos = false;
objclsfrmBases.BotonMenuProcesos = false;
objclsfrmBases.BotonProcesar = false;
objclsfrmBases.BotonLimpiar = false;
objclsfrmBases.BotonSalir = false;
objclsfrmBases.BotonAgregarDP = false;
objclsfrmBases.TextoTipoCambio = false;
dtDP.Rows.Clear();
foreach (EntidadProyMDIBFGestionTasasDP.ProcesoGestionDP record in records)
{
DataRow drDP = dtDP.NewRow();
#region Asignar valores dataTable
drDP["codcategoria"] = record.codcategoria.Trim();
drDP["categoria"] = record.categoria.Trim();
drDP["codsubcategoria"] = record.codsubcategoria.Trim();
drDP["subcategoria"] = record.subcategoria.Replace('ó', 'o').Replace('á', 'a').Replace('?', 'o');
drDP["cuenta"] = record.cuenta.Trim();
drDP["tipodocumento"] = record.tipodocumento.Trim();
if (record.numerodocumento.Length > 7)
{
drDP["numerodocumento"] = record.numerodocumento.Trim();
}
if (record.numerodocumento.Length == 7)
{
drDP["numerodocumento"] = "0" + record.numerodocumento.Trim();
}
if (record.numerodocumento.Length == 6)
{
drDP["numerodocumento"] = "00" + record.numerodocumento.Trim();
}
if (record.numerodocumento.Length == 5)
{
drDP["numerodocumento"] = "000" + record.numerodocumento.Trim();
}
drDP["nombre"] = record.nombre.Replace(',', ' ');
drDP["ejecutivo"] = record.ejecutivo.Trim();
drDP["estado"] = record.estado.Trim();
drDP["descestado"] = record.descestado.Trim();
drDP["fecproceso"] = record.fecproceso.Trim();
drDP["mesproceso"] = record.mesproceso.Trim();
drDP["fecapertura"] = record.fecapertura.Trim();
drDP["mesapertura"] = record.mesapertura.Trim();
drDP["fecrenovacion"] = record.fecrenovacion.Trim();
drDP["mesrenovacion"] = record.mesrenovacion.Trim();
drDP["fecvencimiento"] = record.fecvencimiento.Trim();
drDP["mesvencimiento"] = record.mesvencimiento.Trim();
drDP["msaldo"] = record.msaldo.Trim();
drDP["tasa"] = record.tasa.Trim();
drDP["numeradortasa"] = record.numeradortasa.Trim();
drDP["tipomoneda"] = record.tipomoneda.Trim();
drDP["plazo"] = record.plazo.Trim();
drDP["plazoactual"] = record.plazoactual.Trim();
drDP["numeradorplzpact"] = record.numeradorplzpact.Trim();
drDP["numeradorplzact"] = record.numeradorplzact.Trim();
drDP["saldodisponible"] = record.saldodisponible.Trim();
drDP["rucempleador"] = record.rucempleador.Trim();
drDP["codtienda"] = record.codtienda.Trim();
drDP["desctienda"] = record.desctienda.Trim();
drDP["montoconvenio"] = record.montoconvenio.Trim();
drDP["diacargo"] = record.diacargo.Trim();
drDP["fecinicioconv"] = record.fecinicioconv.Trim();
drDP["cuentacmr"] = record.cuentacmr.Trim();
drDP["tarjetacmr"] = record.tarjetacmr.Trim();
drDP["codigoejecutivo"] = record.codigoejecutivo.Trim();
drDP["tipocambio"] = objclsfrmBases.TipoCambio;
#endregion
dtDP.Rows.Add(drDP);
if (j == 1000000 || j == 1800000)
{
#region Inserciones temporales
using (var conexionBulkCopy = new SqlConnection(conexion))
{
conexionBulkCopy.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conexion))
{
bulkCopy.BulkCopyTimeout = int.MaxValue;
bulkCopy.DestinationTableName = "dbo.ProcesoGestionTasas";
bulkCopy.ColumnMappings.Add("codcategoria", "codcategoria");
bulkCopy.ColumnMappings.Add("categoria", "categoria");
bulkCopy.ColumnMappings.Add("codsubcategoria", "codsubcategoria");
bulkCopy.ColumnMappings.Add("subcategoria", "subcategoria");
bulkCopy.ColumnMappings.Add("cuenta", "cuenta");
bulkCopy.ColumnMappings.Add("tipodocumento", "tipodocumento");
bulkCopy.ColumnMappings.Add("numerodocumento", "numerodocumento");
bulkCopy.ColumnMappings.Add("nombre", "nombre");
bulkCopy.ColumnMappings.Add("ejecutivo", "ejecutivo");
bulkCopy.ColumnMappings.Add("estado", "estado");
bulkCopy.ColumnMappings.Add("descestado", "descestado");
bulkCopy.ColumnMappings.Add("fecproceso", "fecproceso");
bulkCopy.ColumnMappings.Add("mesproceso", "mesproceso");
bulkCopy.ColumnMappings.Add("fecapertura", "fecapertura");
bulkCopy.ColumnMappings.Add("mesapertura", "mesapertura");
bulkCopy.ColumnMappings.Add("fecrenovacion", "fecrenovacion");
bulkCopy.ColumnMappings.Add("mesrenovacion", "mesrenovacion");
bulkCopy.ColumnMappings.Add("fecvencimiento", "fecvencimiento");
bulkCopy.ColumnMappings.Add("mesvencimiento", "mesvencimiento");
bulkCopy.ColumnMappings.Add("msaldo", "msaldo");
bulkCopy.ColumnMappings.Add("tasa", "tasa");
bulkCopy.ColumnMappings.Add("numeradortasa", "numeradortasa");
bulkCopy.ColumnMappings.Add("tipomoneda", "tipomoneda");
bulkCopy.ColumnMappings.Add("plazo", "plazo");
bulkCopy.ColumnMappings.Add("plazoactual", "plazoactual");
bulkCopy.ColumnMappings.Add("numeradorplzpact", "numeradorplzpact");
bulkCopy.ColumnMappings.Add("numeradorplzact", "numeradorplzact");
bulkCopy.ColumnMappings.Add("saldodisponible", "saldodisponible");
bulkCopy.ColumnMappings.Add("rucempleador", "rucempleador");
bulkCopy.ColumnMappings.Add("codtienda", "codtienda");
bulkCopy.ColumnMappings.Add("desctienda", "desctienda");
bulkCopy.ColumnMappings.Add("montoconvenio", "montoconvenio");
bulkCopy.ColumnMappings.Add("diacargo", "diacargo");
bulkCopy.ColumnMappings.Add("fecinicioconv", "fecinicioconv");
bulkCopy.ColumnMappings.Add("cuentacmr", "cuentacmr");
bulkCopy.ColumnMappings.Add("tarjetacmr", "tarjetacmr");
bulkCopy.ColumnMappings.Add("codigoejecutivo", "codigoejecutivo");
bulkCopy.ColumnMappings.Add("tipocambio", "tipocambio");
bulkCopy.WriteToServer(dtDP);
}
}
#endregion
dtDP.Rows.Clear();
}
j++;
porcentaje = Convert.ToInt32((j / intTotal) * 100);
bw1.ReportProgress(porcentaje);
}
}
using (var conexionBulkCopy = new SqlConnection(conexion))
{
conexionBulkCopy.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conexion))
{
bulkCopy.BulkCopyTimeout = int.MaxValue;
bulkCopy.DestinationTableName = "dbo.ProcesoGestionTasas";
#region Datatable bullcopy
bulkCopy.ColumnMappings.Add("codcategoria", "codcategoria");
bulkCopy.ColumnMappings.Add("categoria", "categoria");
bulkCopy.ColumnMappings.Add("codsubcategoria", "codsubcategoria");
bulkCopy.ColumnMappings.Add("subcategoria", "subcategoria");
bulkCopy.ColumnMappings.Add("cuenta", "cuenta");
bulkCopy.ColumnMappings.Add("tipodocumento", "tipodocumento");
bulkCopy.ColumnMappings.Add("numerodocumento", "numerodocumento");
bulkCopy.ColumnMappings.Add("nombre", "nombre");
bulkCopy.ColumnMappings.Add("ejecutivo", "ejecutivo");
bulkCopy.ColumnMappings.Add("estado", "estado");
bulkCopy.ColumnMappings.Add("descestado", "descestado");
bulkCopy.ColumnMappings.Add("fecproceso", "fecproceso");
bulkCopy.ColumnMappings.Add("mesproceso", "mesproceso");
bulkCopy.ColumnMappings.Add("fecapertura", "fecapertura");
bulkCopy.ColumnMappings.Add("mesapertura", "mesapertura");
bulkCopy.ColumnMappings.Add("fecrenovacion", "fecrenovacion");
bulkCopy.ColumnMappings.Add("mesrenovacion", "mesrenovacion");
bulkCopy.ColumnMappings.Add("fecvencimiento", "fecvencimiento");
bulkCopy.ColumnMappings.Add("mesvencimiento", "mesvencimiento");
bulkCopy.ColumnMappings.Add("msaldo", "msaldo");
bulkCopy.ColumnMappings.Add("tasa", "tasa");
bulkCopy.ColumnMappings.Add("numeradortasa", "numeradortasa");
bulkCopy.ColumnMappings.Add("tipomoneda", "tipomoneda");
bulkCopy.ColumnMappings.Add("plazo", "plazo");
bulkCopy.ColumnMappings.Add("plazoactual", "plazoactual");
bulkCopy.ColumnMappings.Add("numeradorplzpact", "numeradorplzpact");
bulkCopy.ColumnMappings.Add("numeradorplzact", "numeradorplzact");
bulkCopy.ColumnMappings.Add("saldodisponible", "saldodisponible");
bulkCopy.ColumnMappings.Add("rucempleador", "rucempleador");
bulkCopy.ColumnMappings.Add("codtienda", "codtienda");
bulkCopy.ColumnMappings.Add("desctienda", "desctienda");
bulkCopy.ColumnMappings.Add("montoconvenio", "montoconvenio");
bulkCopy.ColumnMappings.Add("diacargo", "diacargo");
bulkCopy.ColumnMappings.Add("fecinicioconv", "fecinicioconv");
bulkCopy.ColumnMappings.Add("cuentacmr", "cuentacmr");
bulkCopy.ColumnMappings.Add("tarjetacmr", "tarjetacmr");
bulkCopy.ColumnMappings.Add("codigoejecutivo", "codigoejecutivo");
bulkCopy.ColumnMappings.Add("tipocambio", "tipocambio");
#endregion
bulkCopy.WriteToServer(dtDP);
}
}
objclsfrmBases.BotonMenuMantenimientos = true;
objclsfrmBases.BotonMenuProcesos = true;
//objclsfrmBases.BotonActualizar = true;
objclsfrmBases.BotonProcesar = true;
objclsfrmBases.BotonLimpiar = true;
objclsfrmBases.BotonSalir = true;
objclsfrmBases.BotonAgregarDP = true;
objclsfrmBases.TextoTipoCambio = true;
bw1.ReportProgress(100);
//Insertar el proceso
EntidadProyMDIBFGestionTasasDP.proceso _proceso = new EntidadProyMDIBFGestionTasasDP.proceso();
_proceso.responsableId = 1;
_proceso.nombrearchivo = Path.GetFileName(objclsfrmBases.pathNombreArchivosDP);
_proceso.tipo = "Archivo Stock BF";
_intRetorno = m_proceso.InsertarProceso(conexion, _proceso);
MessageBox.Show("Archivo(s) procesados con éxito.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("Ocurrió un error en el proceso: " + ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
objclsfrmBases.BotonMenuMantenimientos = true;
objclsfrmBases.BotonMenuProcesos = true;
//objclsfrmBases.BotonActualizar = true;
objclsfrmBases.BotonProcesar = true;
objclsfrmBases.BotonLimpiar = true;
objclsfrmBases.BotonSalir = true;
objclsfrmBases.BotonAgregarDP = true;
objclsfrmBases.TextoTipoCambio = true;
bw1.ReportProgress(100);
}
}
/// <summary>
/// 将DataTable批量导入到数据库
/// </summary>
/// <param name="db">待导入的数据</param>
public bool ImportData(string connectionString, string tableName, DataTable dt)
{
try
{
if (dt == null)
{
return(true);
}
//修复为为空的数据不能正常导入
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (string.IsNullOrWhiteSpace(dt.Rows[i][j].ToString()))
{
dt.Rows[i][j] = DBNull.Value;
}
}
}
SqlConnection sqlConn = new SqlConnection(connectionString);
sqlConn.Open();
using (SqlTransaction tran = sqlConn.BeginTransaction())
{
// 批量保存数据，只能用于Sql
SqlBulkCopy sqlbulkCopy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.Default, tran);
// 设置源表名称
sqlbulkCopy.DestinationTableName = tableName;
// 设置超时限制
sqlbulkCopy.BulkCopyTimeout = 2000;
sqlbulkCopy.BatchSize = 10;
for (int i = 0; i < dt.Columns.Count; i++)
{
sqlbulkCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
}
try
{
// 写入
sqlbulkCopy.WriteToServer(dt);
// 提交事务
tran.Commit();
}
catch (Exception e)
{
tran.Rollback();
sqlbulkCopy.Close();
return(false);
}
finally
{
sqlbulkCopy.Close();
}
}
return(true);
}
catch (Exception ex)
{
LogWriter.Write("批量导入到数据库时发生错误");
LogWriter.Write(ex.Message);
throw ex;
}
}
public ActionResult UploadEmployeeCSV(HttpPostedFileBase postedFile)
{
List <string> errors = new List <string>();
string filePath = string.Empty;
if (postedFile != null)
{
string path = Server.MapPath("~/Uploads/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
filePath = path + Path.GetFileName(postedFile.FileName);
string extension = Path.GetExtension(postedFile.FileName);
postedFile.SaveAs(filePath);
//Create a DataTable.
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[4] {
new DataColumn("employeeId", typeof(int)),
new DataColumn("managerID", typeof(int)),
new DataColumn("ceoID", typeof(int)),
new DataColumn("employeeSalary", typeof(Decimal)),
});
//validating the salary Input to numeric
//var isNumeric = int.TryParse("employeeSalary", out int n);
//if (!isNumeric)
//{
// errors.Add("The Salary Value Must be Numeric.");
//}
//Read the contents of CSV file.
string csvData = System.IO.File.ReadAllText(filePath);
//Execute a loop over the rows.
foreach (string row in csvData.Split('\n'))
{
if (!string.IsNullOrEmpty(row))
{
dt.Rows.Add();
int i = 0;
//Execute a loop over the columns.
foreach (string cell in row.Split(','))
{
dt.Rows[dt.Rows.Count - 1][i] = cell;
i++;
}
}
}
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DatabaseConnection"].ToString()))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name.--fields mappings
sqlBulkCopy.DestinationTableName = "dbo.Employees";
sqlBulkCopy.ColumnMappings.Add("employeeId", "employeeId");
sqlBulkCopy.ColumnMappings.Add("managerID", "managerID");
sqlBulkCopy.ColumnMappings.Add("CeoID", "CeoID");
sqlBulkCopy.ColumnMappings.Add("employeeSalary", "employeeSalary");
con.Open();
sqlBulkCopy.WriteToServer(dt);
TempData["Message"] = "The Employee(s) has been uploaded Successfully";
con.Close();
}
}
}
return(RedirectToAction("BulkUploadEmployees"));
}
public static void Test(string srcConstr, string dstConstr, string dstTable)
{
Debug.Assert((int)SqlBulkCopyOptions.UseInternalTransaction == 1 << 5, "Compiler screwed up the options");
dstTable = destinationTable != null ? destinationTable : dstTable;
string sourceQuery = string.Format(sourceQueryTemplate, sourceTable);
string initialQuery = string.Format(initialQueryTemplate, dstTable);
using (SqlConnection dstConn = new SqlConnection(dstConstr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
Helpers.TryExecute(dstCmd, initialQuery);
using (SqlConnection srcConn = new SqlConnection(srcConstr))
using (SqlCommand srcCmd = new SqlCommand(sourceQuery, srcConn))
{
srcConn.Open();
using (DbDataReader reader = srcCmd.ExecuteReader())
{
IDictionary stats;
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn))
{
bulkcopy.DestinationTableName = dstTable;
dstConn.StatisticsEnabled = true;
bulkcopy.WriteToServer(reader);
dstConn.StatisticsEnabled = false;
stats = dstConn.RetrieveStatistics();
}
Helpers.VerifyResults(dstConn, dstTable, 3, 5);
Assert.True(0 < (long)stats["BytesReceived"], "BytesReceived is non-positive.");
Assert.True(0 < (long)stats["BytesSent"], "BytesSent is non-positive.");
Assert.True((long)stats["ConnectionTime"] >= (long)stats["ExecutionTime"], "Connection Time is less than Execution Time.");
Assert.True((long)stats["ExecutionTime"] >= (long)stats["NetworkServerTime"], "Execution Time is less than Network Server Time.");
DataTestUtility.AssertEqualsWithDescription((long)0, (long)stats["UnpreparedExecs"], "Non-zero UnpreparedExecs value: " + (long)stats["UnpreparedExecs"]);
DataTestUtility.AssertEqualsWithDescription((long)0, (long)stats["PreparedExecs"], "Non-zero PreparedExecs value: " + (long)stats["PreparedExecs"]);
DataTestUtility.AssertEqualsWithDescription((long)0, (long)stats["Prepares"], "Non-zero Prepares value: " + (long)stats["Prepares"]);
DataTestUtility.AssertEqualsWithDescription((long)0, (long)stats["CursorOpens"], "Non-zero CursorOpens value: " + (long)stats["CursorOpens"]);
DataTestUtility.AssertEqualsWithDescription((long)0, (long)stats["IduRows"], "Non-zero IduRows value: " + (long)stats["IduRows"]);
DataTestUtility.AssertEqualsWithDescription((long)3, stats["BuffersReceived"], "Unexpected BuffersReceived value.");
DataTestUtility.AssertEqualsWithDescription((long)3, stats["BuffersSent"], "Unexpected BuffersSent value.");
DataTestUtility.AssertEqualsWithDescription((long)0, stats["IduCount"], "Unexpected IduCount value.");
DataTestUtility.AssertEqualsWithDescription((long)3, stats["SelectCount"], "Unexpected SelectCount value.");
DataTestUtility.AssertEqualsWithDescription((long)3, stats["ServerRoundtrips"], "Unexpected ServerRoundtrips value.");
DataTestUtility.AssertEqualsWithDescription((long)4, stats["SelectRows"], "Unexpected SelectRows value.");
DataTestUtility.AssertEqualsWithDescription((long)2, stats["SumResultSets"], "Unexpected SumResultSets value.");
DataTestUtility.AssertEqualsWithDescription((long)0, stats["Transactions"], "Unexpected Transactions value.");
}
}
}
finally
{
Helpers.TryExecute(dstCmd, "drop table " + dstTable);
}
}
}
/// <summary>
/// Global variables for code below: input files, ftp/sql connection strings
/// </summary>
// public static string conStringName = ReturnConStringName();
// public static string sqldbconnection = System.Configuration.ConfigurationManager.ConnectionStrings[conStringName].ConnectionString;
// based on app approval output file columns
public static void InsertDataIntoSQLServerUsingSQLBulkCopy_2(DataTable dtable, string sqlTableName, Int32 batch_size, string connString)
{
try
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connString, System.Data.SqlClient.SqlBulkCopyOptions.TableLock))
{
bulkCopy.DestinationTableName = sqlTableName;
try
{
// Write from the source to the destination.
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.BatchSize = batch_size;
// Set up the event handler to notify after 50 rows.
// bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
// bulkCopy.NotifyAfter = 10000;
bulkCopy.WriteToServer(dtable);
}
catch (SqlException ex)
{
if (ex.Message.Contains("Received an invalid column length from the bcp client for colid"))
{
string pattern = @"\d+";
Match match = Regex.Match(ex.Message.ToString(), pattern);
var index = Convert.ToInt32(match.Value) - 1;
FieldInfo fi = typeof(SqlBulkCopy).GetField("_sortedColumnMappings", BindingFlags.NonPublic | BindingFlags.Instance);
var sortedColumns = fi.GetValue(bulkCopy);
var items = (Object[])sortedColumns.GetType().GetField("_items", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(sortedColumns);
FieldInfo itemdata = items[index].GetType().GetField("_metadata", BindingFlags.NonPublic | BindingFlags.Instance);
var metadata = itemdata.GetValue(items[index]);
var column = metadata.GetType().GetField("column", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);
var length = metadata.GetType().GetField("length", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);
Console.WriteLine("Error message: Column [" + column + "] contains data with a length greater than " + length);
Console.WriteLine();
Console.WriteLine("Table " + sqlTableName + " already exists in DB, just change data type - see the tip below.");
Console.WriteLine("Tip: try something like ALTER TABLE table_name ALTER COLUMN column_name datatype;");
// CleanUpTable(sqlTableName, connString);
Environment.Exit(1);
}
else
{
Console.WriteLine(ex.Message.ToString());
// CleanUpTable(sqlTableName, connString);
Environment.Exit(1);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message.ToString());
// CleanUpTable(sqlTableName, connString);
Environment.Exit(1);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message.ToString());
Environment.Exit(1);
}
}
static void Main()
{
string connectionString = GetConnectionString();
// Open a connection to the AdventureWorks database.
using (SqlConnection connection =
new SqlConnection(connectionString))
{
connection.Open();
// Empty the destination tables.
SqlCommand deleteHeader = new SqlCommand(
"DELETE FROM dbo.BulkCopyDemoOrderHeader;",
connection);
deleteHeader.ExecuteNonQuery();
SqlCommand deleteDetail = new SqlCommand(
"DELETE FROM dbo.BulkCopyDemoOrderDetail;",
connection);
deleteDetail.ExecuteNonQuery();
// Perform an initial count on the destination
// table with matching columns.
SqlCommand countRowHeader = new SqlCommand(
"SELECT COUNT(*) FROM dbo.BulkCopyDemoOrderHeader;",
connection);
long countStartHeader = System.Convert.ToInt32(
countRowHeader.ExecuteScalar());
Console.WriteLine(
"Starting row count for Header table = {0}",
countStartHeader);
// Perform an initial count on the destination
// table with different column positions.
SqlCommand countRowDetail = new SqlCommand(
"SELECT COUNT(*) FROM dbo.BulkCopyDemoOrderDetail;",
connection);
long countStartDetail = System.Convert.ToInt32(
countRowDetail.ExecuteScalar());
Console.WriteLine(
"Starting row count for Detail table = {0}",
countStartDetail);
// Get data from the source table as a SqlDataReader.
// The Sales.SalesOrderHeader and Sales.SalesOrderDetail
// tables are quite large and could easily cause a timeout
// if all data from the tables is added to the destination.
// To keep the example simple and quick, a parameter is
// used to select only orders for a particular account
// as the source for the bulk insert.
SqlCommand headerData = new SqlCommand(
"SELECT [SalesOrderID], [OrderDate], " +
"[AccountNumber] FROM [Sales].[SalesOrderHeader] " +
"WHERE [AccountNumber] = @accountNumber;",
connection);
SqlParameter parameterAccount = new SqlParameter();
parameterAccount.ParameterName = "@accountNumber";
parameterAccount.SqlDbType = SqlDbType.NVarChar;
parameterAccount.Direction = ParameterDirection.Input;
parameterAccount.Value = "10-4020-000034";
headerData.Parameters.Add(parameterAccount);
SqlDataReader readerHeader = headerData.ExecuteReader();
// Get the Detail data in a separate connection.
using (SqlConnection connection2 = new SqlConnection(connectionString))
{
connection2.Open();
SqlCommand sourceDetailData = new SqlCommand(
"SELECT [Sales].[SalesOrderDetail].[SalesOrderID], [SalesOrderDetailID], " +
"[OrderQty], [ProductID], [UnitPrice] FROM [Sales].[SalesOrderDetail] " +
"INNER JOIN [Sales].[SalesOrderHeader] ON [Sales].[SalesOrderDetail]." +
"[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID] " +
"WHERE [AccountNumber] = @accountNumber;", connection2);
SqlParameter accountDetail = new SqlParameter();
accountDetail.ParameterName = "@accountNumber";
accountDetail.SqlDbType = SqlDbType.NVarChar;
accountDetail.Direction = ParameterDirection.Input;
accountDetail.Value = "10-4020-000034";
sourceDetailData.Parameters.Add(accountDetail);
SqlDataReader readerDetail = sourceDetailData.ExecuteReader();
// Create the SqlBulkCopy object.
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(connectionString))
{
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoOrderHeader";
// Guarantee that columns are mapped correctly by
// defining the column mappings for the order.
bulkCopy.ColumnMappings.Add("SalesOrderID", "SalesOrderID");
bulkCopy.ColumnMappings.Add("OrderDate", "OrderDate");
bulkCopy.ColumnMappings.Add("AccountNumber", "AccountNumber");
// Write readerHeader to the destination.
try
{
bulkCopy.WriteToServer(readerHeader);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
readerHeader.Close();
}
// Set up the order details destination.
bulkCopy.DestinationTableName = "dbo.BulkCopyDemoOrderDetail";
// Clear the ColumnMappingCollection.
bulkCopy.ColumnMappings.Clear();
// Add order detail column mappings.
bulkCopy.ColumnMappings.Add("SalesOrderID", "SalesOrderID");
bulkCopy.ColumnMappings.Add("SalesOrderDetailID", "SalesOrderDetailID");
bulkCopy.ColumnMappings.Add("OrderQty", "OrderQty");
bulkCopy.ColumnMappings.Add("ProductID", "ProductID");
bulkCopy.ColumnMappings.Add("UnitPrice", "UnitPrice");
// Write readerDetail to the destination.
try
{
bulkCopy.WriteToServer(readerDetail);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
readerDetail.Close();
}
}
// Perform a final count on the destination
// tables to see how many rows were added.
long countEndHeader = System.Convert.ToInt32(
countRowHeader.ExecuteScalar());
Console.WriteLine("{0} rows were added to the Header table.",
countEndHeader - countStartHeader);
long countEndDetail = System.Convert.ToInt32(
countRowDetail.ExecuteScalar());
Console.WriteLine("{0} rows were added to the Detail table.",
countEndDetail - countStartDetail);
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
try
{
//Upload and save the file
string excelPath = Server.MapPath("~/uploads/UploadedFile/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(excelPath);
string conString = string.Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
switch (extension)
{
case ".xls": //Excel 97-03
// conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
conString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "data source=" + excelPath + ";Extended Properties='Excel 8.0;IMEX=1'"
;
break;
case ".xlsx": //Excel 07 or higher
// conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
conString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "data source=" + excelPath + ";Extended Properties='Excel 8.0;IMEX=1'"
;
break;
}
conString = string.Format(conString, excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();
//[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
dtExcelData.Columns.AddRange(new DataColumn[11] {
new DataColumn("NAME", typeof(string)),
new DataColumn("MOBILE", typeof(string)),
new DataColumn("CITY", typeof(string)),
new DataColumn("REMARK", typeof(string)),
new DataColumn("Customer", typeof(string)),
new DataColumn("Product", typeof(string)),
new DataColumn("Enquiry From", typeof(string)),
new DataColumn("Enquiry For", typeof(string)),
new DataColumn("NextTimetoCall", typeof(string)),
new DataColumn("Lead", typeof(string)),
new DataColumn("Other", typeof(string)),
});
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
string consString = ConfigurationManager.ConnectionStrings["cnstring"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "EZACUSTblFollowup";
//[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("NAME", "Name");
sqlBulkCopy.ColumnMappings.Add("MOBILE", "Mobile");
sqlBulkCopy.ColumnMappings.Add("CITY", "City");
sqlBulkCopy.ColumnMappings.Add("REMARK", "Remark");
sqlBulkCopy.ColumnMappings.Add("Customer", "CustomerType");
sqlBulkCopy.ColumnMappings.Add("Product", "Product");
sqlBulkCopy.ColumnMappings.Add("Enquiry From", "EnquiryFrom");
sqlBulkCopy.ColumnMappings.Add("Enquiry For", "EnquiryFor");
sqlBulkCopy.ColumnMappings.Add("Next Time Call", "NextTimetoCall");
sqlBulkCopy.ColumnMappings.Add("Lead", "Lead");
sqlBulkCopy.ColumnMappings.Add("Other", "Other");
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
}
Label1.Text = "File Uploaded Successfully";
}
BindNews();
}
}
catch (Exception ex)
{
Response.Write(ex.Message + ex.StackTrace);
}
}
private void PerformWriteToDatabase(IEnumerable<LoggingEvent> events)
{
try
{
DataTable table = CreateTable(events);
using (SqlBulkCopy bulk = new SqlBulkCopy(connectionString, options))
{
foreach (BulkInsertMapping mapping in mappings)
{
bulk.ColumnMappings.Add(mapping.Column, mapping.Column);
}
bulk.DestinationTableName = tableName;
bulk.WriteToServer(table);
}
}
catch (Exception ex)
{
LogLog.Error("Could not write logs to database in the background", ex);
}
}
public Tuple <int, int> Convert()
{
int gemeentenLines = 0;
int postcodeLines = 0;
String myConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;" + "data source=" + fileName;
{
try
{
// Open OleDb Connection
OleDbConnection myConnection = new OleDbConnection();
myConnection.ConnectionString = myConnectionString;
myConnection.Open();
// Execute Queries
OleDbCommand cmd = myConnection.CreateCommand();
cmd.CommandText = "SELECT * FROM `GEMEENTEN`";
OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); // close conn after complete
// Load the result into a DataTable
DataTable gemeenten = new DataTable();
gemeenten.Load(reader);
gemeenten.TableName = "dbo.Gemeenten";
gemeenten.Columns["N42_GEM_KODE"].ColumnName = "Kode";
gemeenten.Columns["N42_GEM_NAAM"].ColumnName = "Naam";
gemeentenLines = gemeenten.Rows.Count;
myConnection.ConnectionString = myConnectionString;
myConnection.Open();
cmd.CommandText = "SELECT * FROM `POSTCODES`";
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); // close conn after complete
DataTable postcodes = new DataTable();
postcodes.Load(reader);
postcodes.TableName = "dbo.Postcodes";
postcodes.Columns["A13_POSTCODE"].ColumnName = "Postcode";
postcodes.Columns["A13_REEKSIND"].ColumnName = "Reeksind";
postcodes.Columns["A13_BREEKPUNT_VAN"].ColumnName = "Breekpunt_van";
postcodes.Columns["A13_BREEKPUNT_TEM"].ColumnName = "Breekpunt_tem";
postcodes.Columns["A13_WOONPLAATS"].ColumnName = "Woonplaats";
postcodes.Columns["A13_STRAATNAAM"].ColumnName = "Straatnaam";
postcodes.Columns["A13_GEMEENTECODE"].ColumnName = "Gemeentecode";
SqlConnection conn = SqlConnectionMaker.ReturnConnection();
postcodeLines = postcodes.Rows.Count;
try
{
conn.Open();
SqlCommand command = conn.CreateCommand();
command.CommandText = "DROP TABLE IF EXISTS dbo.Gemeenten";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE dbo.Gemeenten (Kode varchar(200),Naam varchar(200))";
command.ExecuteNonQuery();
}
catch (Exception e)
{
logwarn.Warn(e.Message);
throw;
}
finally
{
conn.Close();
}
try
{
conn.Open();
SqlCommand command = conn.CreateCommand();
command.CommandText = "DROP TABLE IF EXISTS dbo.Postcodes";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE dbo.Postcodes (Postcode varchar(200),Reeksind varchar(200),Breekpunt_van varchar(200),Breekpunt_tem varchar(200),Woonplaats varchar(200),Straatnaam varchar(200),Gemeeentecode varchar(200))";
command.ExecuteNonQuery();
}
catch (Exception e)
{
logwarn.Warn(e.Message);
throw;
}
finally
{
conn.Close();
}
conn.Open();
SqlBulkCopy bulkcopy = new SqlBulkCopy(conn);
bulkcopy.DestinationTableName = gemeenten.TableName;
try
{
bulkcopy.WriteToServer(gemeenten);
}
catch (Exception e)
{
logwarn.Warn(e.Message);
}
bulkcopy.DestinationTableName = postcodes.TableName;
try
{
bulkcopy.WriteToServer(postcodes);
}
catch (Exception e)
{
logwarn.Warn(e.Message);
}
finally
{
conn.Close();
}
}
catch (Exception ex)
{
Console.WriteLine("OLEDB Connection FAILED: " + ex.Message);
logwarn.Warn(ex.Message);
}
}
return(Tuple.Create(gemeentenLines, postcodeLines));
}
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
app.UseRouting();
app.UseEndpoints(endpoints =>
{
endpoints.MapGet("/", async context =>
{
const string connectionString =
@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=AppDbContext;Integrated Security=SSPI; MultipleActiveResultSets=true";
using (SqlConnection connection = new SqlConnection(connectionString))
{
const string commandText =
"SELECT TOP 8 Id, FullName, Age FROM People WHERE Id < @Age";
connection.Open();
using SqlCommand command = new SqlCommand
{
Connection = connection,
CommandText = commandText,
CommandTimeout = 15,
CommandType = CommandType.Text,
};
SqlParameter parameter = new SqlParameter("@Age", SqlDbType.Int)
{
Value = 28,
};
command.Parameters.Add(parameter);
SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable);
command.Transaction = transaction;
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
using SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString)
{
DestinationTableName = "dbo.AppDbContext",
BatchSize = 50
};
try
{
bulkCopy.WriteToServer(reader);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
transaction.Commit();
reader.Close();
}
command.ExecuteScalar();
IList <object> people = new List <object>();
while (reader.Read())
{
object id = reader[0];
object fullName = reader[1];
object age = reader[2];
people.Add(new { Id = id, FullName = fullName, Age = age });
}
reader.Close();
SqlCommand command1 = connection.CreateCommand();
command1.CommandText = "IF EXISTS (SELECT * FROM People WHERE FullName=N'SinjulMSBH') DROP TABLE People";
try
{
command1.ExecuteNonQuery();
command1.BeginExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
IReadOnlyList <People> result = people as List <People>;
await context.Response.WriteAsync(JsonConvert.SerializeObject(people));
}
});
});
}
protected override void Process()
{
log.Info("Bulk Zipcode import started");
using (var uni = GetUnitOfWork())
{
var config = GetConfiguration().AppSettings.Settings;
//using (StreamReader sr = new StreamReader(config["FullZipPath"].Value))
//{
// int linecount = 0;
// int logline = 0;
// string line;
// var zipcodes = (from z in dbcontext.ZipCodes
// select z).ToList();
// while ((line = sr.ReadLine()) != null)
// {
// linecount++;
// if (linecount <= 1 || line.StartsWith("**"))
// continue;
// #region Parse PostCode
// Concentrator.Objects.Vendors.ZipCode newzip = new Concentrator.Objects.Vendors.ZipCode()
// {
// PCWIJK = line.Substring(0, 4).Trim(),
// PCLETTER = line.Substring(4, 2).Trim(),
// PCREEKSID = line.Substring(6, 1).Trim(),
// PCREEKSVAN = string.IsNullOrEmpty(line.Substring(7, 5).Trim()) ? 0 : int.Parse(line.Substring(7, 5).Trim()),
// PCREEKSTOT = string.IsNullOrEmpty(line.Substring(12, 5).Trim()) ? 0 : int.Parse(line.Substring(12, 5).Trim()),
// PCCITYTPG = line.Substring(17, 18).Trim(),
// PCCITYNEN = line.Substring(35, 24).Trim(),
// PCSTRTPG = line.Substring(59, 17).Trim(),
// PCSTRNEN = line.Substring(76, 24).Trim(),
// PCSTROFF = line.Substring(100, 43).Trim(),
// PCCITYEXT = line.Substring(143, 4).Trim(),
// PCSTREXT = line.Substring(147, 5).Trim(),
// PCGEMEENTEID = string.IsNullOrEmpty(line.Substring(152, 4).Trim()) ? 0 : int.Parse(line.Substring(152, 4).Trim()),
// PCGEMEENTENAAM = line.Substring(156, 24).Trim(),
// PCPROVINCIE = line.Substring(180, 1).Trim(),
// PCCEBUCO = string.IsNullOrEmpty(line.Substring(181, 3).Trim()) ? 0 : int.Parse(line.Substring(181, 3).Trim())
// };
// var existingVar = (from c in zipcodes
// where
// c.PCWIJK.Trim() == newzip.PCWIJK
// && c.PCLETTER.Trim() == newzip.PCLETTER
// && c.PCREEKSID.Trim() == newzip.PCREEKSID
// && c.PCREEKSVAN == newzip.PCREEKSVAN
// && c.PCREEKSTOT == newzip.PCREEKSTOT
// && c.PCCITYTPG.Trim() == newzip.PCCITYTPG
// && c.PCCITYNEN.Trim() == newzip.PCCITYNEN
// && c.PCSTRTPG.Trim() == newzip.PCSTRTPG
// && c.PCSTRNEN.Trim() == newzip.PCSTRNEN
// && c.PCSTROFF.Trim() == newzip.PCSTROFF
// && c.PCCITYEXT.Trim() == newzip.PCCITYEXT
// && c.PCSTREXT.Trim() == newzip.PCSTREXT
// && c.PCGEMEENTEID == newzip.PCGEMEENTEID
// && c.PCGEMEENTENAAM.Trim() == newzip.PCGEMEENTENAAM
// && c.PCPROVINCIE.Trim() == newzip.PCPROVINCIE
// && c.PCCEBUCO == newzip.PCCEBUCO
// select c).FirstOrDefault();
// if (existingVar == null)
// {
// existingVar = new Concentrator.Objects.Vendors.ZipCode()
// {
// PCWIJK = newzip.PCWIJK,
// PCLETTER = newzip.PCLETTER,
// PCREEKSID = newzip.PCREEKSID,
// PCREEKSVAN = newzip.PCREEKSVAN,
// PCREEKSTOT = newzip.PCREEKSTOT,
// PCCITYTPG = newzip.PCCITYTPG,
// PCCITYNEN = newzip.PCCITYNEN,
// PCSTRTPG = newzip.PCSTRTPG,
// PCSTRNEN = newzip.PCSTRNEN,
// PCSTROFF = newzip.PCSTROFF,
// PCCITYEXT = newzip.PCCITYEXT,
// PCSTREXT = newzip.PCSTREXT,
// PCGEMEENTEID = newzip.PCGEMEENTEID,
// PCGEMEENTENAAM = newzip.PCGEMEENTENAAM,
// PCPROVINCIE = newzip.PCPROVINCIE,
// PCCEBUCO = newzip.PCCEBUCO
// };
// dbcontext.ZipCodes.InsertOnSubmit(existingVar);
// log.DebugFormat("Add new zipcode {0}{1} line {2}", newzip.PCWIJK, newzip.PCLETTER, linecount);
// dbcontext.SubmitChanges();
// zipcodes.Add(existingVar);
// }
// #endregion
// }
//}
SqlBulkCopy copy = new SqlBulkCopy(Environments.Current.Connection);
copy.BatchSize = 10000;
copy.BulkCopyTimeout = 300;
copy.DestinationTableName = "[ZipCode]";
copy.NotifyAfter = 1000;
copy.SqlRowsCopied += (s, e) => log.DebugFormat("Processed {0} rows", e.RowsCopied);
using (var fileStream = new FileStream(config["FullZipPath"].Value, FileMode.Open, FileAccess.Read, FileShare.Read, 5120, FileOptions.SequentialScan))
{
List <DataRow> dataRows = new List <DataRow>();
using (StreamReader sr = new StreamReader(fileStream))
{
int linecount = 0;
int logline = 0;
string line;
DataTable table = new DataTable();
table.Columns.Add("PCWIJK");
table.Columns.Add("PCLETTER");
table.Columns.Add("PCREEKSID");
table.Columns.Add("PCREEKSVAN");
table.Columns.Add("PCREEKSTOT");
table.Columns.Add("PCCITYTPG");
table.Columns.Add("PCCITYNEN");
table.Columns.Add("PCSTRTPG");
table.Columns.Add("PCSTRNEN");
table.Columns.Add("PCSTROFF");
table.Columns.Add("PCCITYEXT");
table.Columns.Add("PCSTREXT");
table.Columns.Add("PCGEMEENTEID");
table.Columns.Add("PCGEMEENTENAAM");
table.Columns.Add("PCPROVINCIE");
table.Columns.Add("PCCEBUCO");
while ((line = sr.ReadLine()) != null)
{
linecount++;
if (linecount <= 1 || line.StartsWith("**"))
{
continue;
}
List <string> items = new List <string>();
items.Add(line.Substring(0, 4).Trim());
items.Add(line.Substring(4, 2).Trim());
items.Add(line.Substring(6, 1).Trim());
items.Add(string.IsNullOrEmpty(line.Substring(7, 5).Trim()) ? "0" : line.Substring(7, 5).Trim());
items.Add(string.IsNullOrEmpty(line.Substring(12, 5).Trim()) ? "0" : line.Substring(12, 5).Trim());
items.Add(line.Substring(17, 18).Trim());
items.Add(line.Substring(35, 24).Trim());
items.Add(line.Substring(59, 17).Trim());
items.Add(line.Substring(76, 24).Trim());
items.Add(line.Substring(100, 43).Trim());
items.Add(line.Substring(143, 4).Trim());
items.Add(line.Substring(147, 5).Trim());
items.Add(string.IsNullOrEmpty(line.Substring(152, 4).Trim()) ? "0" : line.Substring(152, 4).Trim());
items.Add(line.Substring(156, 24).Trim());
items.Add(line.Substring(180, 1).Trim());
items.Add(string.IsNullOrEmpty(line.Substring(181, 3).Trim()) ? "0" : line.Substring(181, 3).Trim());
DataRow row = table.NewRow();
row.ItemArray = items.ToArray();
table.Rows.Add(row);
}
copy.ColumnMappings.Add(0, "PCWIJK");
copy.ColumnMappings.Add(1, "PCLETTER");
copy.ColumnMappings.Add(2, "PCREEKSID");
copy.ColumnMappings.Add(3, "PCREEKSVAN");
copy.ColumnMappings.Add(4, "PCREEKSTOT");
copy.ColumnMappings.Add(5, "PCCITYTPG");
copy.ColumnMappings.Add(6, "PCCITYNEN");
copy.ColumnMappings.Add(7, "PCSTRTPG");
copy.ColumnMappings.Add(8, "PCSTRNEN");
copy.ColumnMappings.Add(9, "PCSTROFF");
copy.ColumnMappings.Add(10, "PCCITYEXT");
copy.ColumnMappings.Add(11, "PCSTREXT");
copy.ColumnMappings.Add(12, "PCGEMEENTEID");
copy.ColumnMappings.Add(13, "PCGEMEENTENAAM");
copy.ColumnMappings.Add(14, "PCPROVINCIE");
copy.ColumnMappings.Add(15, "PCCEBUCO");
copy.WriteToServer(table);
}
}
}
log.AuditSuccess("Post Code import finished", "Post Code Import");
}
static void ReadFrom(string path, string file)
{
//Create COM Objects. Create a COM object for everything that is referenced
Excel.Application xlApp = new Excel.Application();
//必须要使用绝对路径打开？如果可以的话可以直接放入当前所在路径或者是path这个参量
//注意此处的单斜杠在c#中有具体的功能，如果是要当作字符使用则需要在前面再加上一个斜杠
//可以在此处加上对于excel版本的判断，对于旧版本的支持
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@path + "\\" + file);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
//将excel表格中的数据导入到datatable中
InitColumn2(xlRange, rowCount, colCount);
//将数据插入datatable中
InsertData(xlRange, rowCount, colCount);
//将数据从datatable中插入到SQL Server
try
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
Console.Write("Please input the datasource\n");
builder.DataSource = Console.ReadLine();
Console.Write("Please input the useid\n");
builder.UserID = Console.ReadLine();
Console.Write("Please input the password\n");
builder.Password = Console.ReadLine();
Console.Write("Please input the initialcatalog\n");
builder.InitialCatalog = Console.ReadLine();
//connect to sql server
Console.Write("connecting to SQL Server...");
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
connection.Open();
Console.WriteLine("Done.");
//create a new database list
Console.Write("Dropping and creating database 'list'...");
string sql = "drop database if exists [list];create database [list]";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.ExecuteNonQuery();
Console.WriteLine("Done.");
}
// //create a new table and insert the data from the datatable
//创建一个新的table
Console.Write("Creatiing data table from datatable, press any key to continute...");
Console.ReadKey(true);
StringBuilder sb = new StringBuilder();
sb.Append("use list;");
sb.Append("if not exists (select * from sysobjects where name='datatable' and xtype='U') create table datatable (");
foreach (DataColumn columns in datatable.Columns)
{
sb.Append(" ");
//插入的数据的列名称
sb.Append(columns.ColumnName.ToString());
sb.Append(" ");
//插入列的数据类型
if (columns.DataType == System.Type.GetType("System.String"))
{
sb.Append("nvarchar(50)");
}
if (columns.DataType == System.Type.GetType("System.Int64"))
{
sb.Append("bigint");
}
else
{
if (columns.DataType == System.Type.GetType("System.Int32"))
{
sb.Append("int");
}
else
{
if (columns.DataType == System.Type.GetType("System.Int16"))
{
sb.Append("smallint");
}
else
if (columns.DataType == System.Type.GetType("System.Double"))
{
sb.Append("real");
}
}
}
if (columns.DataType == System.Type.GetType("System.DateTime"))
{
sb.Append("datetime");
}
sb.Append(",");
}
//移除最后一个','
sb = sb.Remove(sb.Length - 1, 1);
sb.Append(");");
sql = sb.ToString();
Console.Write(sql);
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.ExecuteNonQuery();
Console.Write("List creation is done.");
}
//test
foreach (DataRow row2 in datatable.Rows)
{
Console.Write(row2.ToString());
StringBuilder jd = new StringBuilder();
jd.Append("SELECT * FROM LIST WHERE ");
jd.Append(datatable.Columns[0].ColumnName);
jd.Append(" like '");
jd.Append(row2[datatable.Columns[0].ColumnName]);
jd.Append("';");
sql = jd.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.ExecuteNonQuery();
Console.Write("receive data from sql server\n");
SqlDataAdapter rec = new SqlDataAdapter(command);
System.Data.DataTable temp = new DataTable();
rec.Fill(temp);
if ((temp.Rows[1]) == (row2))
{
Console.Write("The record is existed! Do you want ro recover it? y/n:");
char flag = Convert.ToChar(Console.ReadLine());
if (flag == 'y')
{
StringBuilder di = new StringBuilder();
di.Append("delect from datatable where ");
di.Append(datatable.Columns[0].ColumnName);
di.Append(" = '");
di.Append(row2[datatable.Columns[0]].ToString());
di.Append("';");
sql = di.ToString();
using (SqlCommand command2 = new SqlCommand(sql, connection))
{
command.ExecuteNonQuery();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName =
"dbo.datatable";
try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(datatable);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
else
{
//insert the data using the datatable
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName =
"dbo.datatable";
try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(datatable);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
}
}
////insert the data using the datatable
//using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
//{
// bulkCopy.DestinationTableName =
// "dbo.datatable";
// try
// {
// // Write from the source to the destination.
// bulkCopy.WriteToServer(datatable);
// }
// catch (Exception ex)
// {
// Console.WriteLine(ex.Message);
// }
//}
}
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
Console.WriteLine("All done. Press any key to finish...");
Console.ReadKey(true);
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
public static void Test(string srcConstr, string dstConstr, string dstTable)
{
using (SqlConnection dstConn = new SqlConnection(dstConstr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
Helpers.TryExecute(dstCmd, "create table " + dstTable + " (col1 int, col3 nvarchar(10))");
using (SqlConnection srcConn = new SqlConnection(srcConstr))
using (SqlCommand srcCmd = new SqlCommand("select top 5 EmployeeID, LastName, FirstName from employees", srcConn))
{
srcConn.Open();
using (DbDataReader reader = srcCmd.ExecuteReader())
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn))
{
bulkcopy.DestinationTableName = dstTable;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add("EmployeeID", "col1");
ColumnMappings.Add("LastName", "col2"); // this column does not exist
ColumnMappings.Add("FirstName", "col3");
string errorMsg = SystemDataResourceManager.Instance.SQL_BulkLoadNonMatchingColumnMapping;
DataTestUtility.AssertThrowsWrapper <InvalidOperationException>(() => bulkcopy.WriteToServer(reader), exceptionMessage: errorMsg);
}
}
}
finally
{
Helpers.TryExecute(dstCmd, "drop table " + dstTable);
}
}
}
static void Main(string[] args)
{
var p = new Program(new BatchService());
p.service.WriteToEventLog("Active Directory Extract -- Starting");
try
{
using (var context = new PrincipalContext(ContextType.Domain, p.adDomain))
{
using (var searcher = new PrincipalSearcher(new UserPrincipal(context)))
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
foreach (Principal result in searcher.FindAll())
{
if (p.recordList.Count % 10000 == 0 && p.recordList.Count > 0)
{
p.service.WriteToEventLog("Active Directory Extract -- Processed " + p.recordList.Count + " records in: " + p.service.getElapsedTime(stopWatch));
}
DirectoryEntry entry = result.GetUnderlyingObject() as DirectoryEntry;
/* if (
* (entry.Properties["company"].Value != null || entry.Properties["extensionAttribute2"].Value != null) &&
* (entry.Properties["extensionAttribute1"].Value != null || entry.Properties["employeeId"].Value != null) &&
* entry.Properties["objectClass"].Value != null &&
* !String.IsNullOrEmpty(entry.Properties["objectClass"].Value.ToString()) &&
* ((IEnumerable)entry.Properties["objectClass"]).Cast<object>()
* .Select(x => x.ToString())
* .ToArray().Contains("user"))
* {
* p.recordList.Add(ADDepUser.GetADDepUserFromEntry(entry));
* }*/
p.recordList.Add(ADDepUser.GetADDepUserFromEntry(entry));
}
p.service.WriteToEventLog("Active Directory Extract -- Processed " + p.recordList.Count + " records in: " + p.service.getElapsedTime(stopWatch));
stopWatch.Stop();
}
}
DataTable dt = new DataTable();
foreach (string columnName in p.columnNames)
{
dt.Columns.Add(new DataColumn(columnName, typeof(string)));
}
// if the is filtered is set to true in config file then only add the employees that belong to the companies in the list
if (p.isFiltered)
{
List <ADDepUser> list1 = (from r in p.recordList
join c in p.filteredCompanies on r.Company equals c
select r).ToList();
List <ADDepUser> list2 = (from r in p.recordList
join c in p.filteredCompanies on r.ExtensionAttribute2 equals c
select r).ToList();
p.recordList = list1;
p.recordList.AddRange(list2);
}
//var distinctRecordList = p.recordList.GroupBy(x => x.ExtensionAttribute1).Select(x => x.First()).ToList(); // get rid of any duplicates
foreach (var record in p.recordList)
{
dt.Rows.Add(new string[]
{
p.service.GetDBValue(record.Cn),
p.service.GetDBValue(record.ExtensionAttribute1),
p.service.GetDBValue(record.ExtensionAttribute13),
p.service.GetDBValue(record.ExtensionAttribute3),
p.service.GetDBValue(record.ExtensionAttribute4),
p.service.GetDBValue(record.ExtensionAttribute7),
p.service.GetDBValue(record.GivenName),
p.service.GetDBValue(record.Initials),
p.service.GetDBValue(record.L),
p.service.GetDBValue(record.PostalCode),
p.service.GetDBValue(record.Sn),
p.service.GetDBValue(record.StreetAddress),
p.service.GetDBValue(record.TelephoneNumber),
p.service.GetDBValue(record.WhenChanged),
p.service.GetDBValue(record.WhenCreated),
p.service.GetDBValue(record.Company)
});
}
p.service.ExecuteNonQuery("truncate table ad_dep_users");
using (SqlBulkCopy bc = new SqlBulkCopy(p.connectionString))
{
bc.DestinationTableName = "[dbo].[AD_DEP_USERS]";
bc.WriteToServer(dt);
}
p.service.ExecuteNonQuery("truncate table cwopa_agency_file_no_filter");
p.service.ExecuteNonQuery(p.service.getCWOPAInsertStatement());
p.service.WriteToEventLog("Active Directory Extract -- Completed");
}
catch (Exception ex)
{
p.service.WriteToEventLog($"Active Directory Extract Error: {ex.Message}");
throw;
}
}
public static IQueryable <T> StageOnSqlServerUsingBcp <T>(this IEnumerable <T> items, ChoETLSqlServerSettings sqlServerSettings = null)
where T : class
{
if (typeof(T).IsDynamicType() || typeof(T) == typeof(object))
{
throw new NotSupportedException();
}
Dictionary <string, PropertyInfo> PIDict = ChoType.GetProperties(typeof(T)).ToDictionary(p => p.Name);
sqlServerSettings = ValidateSettings <T>(sqlServerSettings);
CreateDatabaseIfLocalDb(sqlServerSettings);
var firstItem = items.FirstOrDefault();
if (firstItem != null)
{
using (var conn1 = new SqlConnection(sqlServerSettings.ConnectionString))
{
conn1.Open();
try
{
SqlCommand command = new SqlCommand(firstItem.CreateTableScript(sqlServerSettings.ColumnDataMapper, sqlServerSettings.TableName), conn1);
command.ExecuteNonQuery();
}
catch { }
//Truncate table
try
{
SqlCommand command = new SqlCommand("TRUNCATE TABLE [{0}]".FormatString(sqlServerSettings.TableName), conn1);
command.ExecuteNonQuery();
}
catch
{
try
{
SqlCommand command = new SqlCommand("DELETE FROM [{0}]".FormatString(sqlServerSettings.TableName), conn1);
command.ExecuteNonQuery();
}
catch { }
}
}
using (SqlBulkCopy bcp = new SqlBulkCopy(sqlServerSettings.ConnectionString))
{
bcp.DestinationTableName = sqlServerSettings.TableName;
bcp.EnableStreaming = true;
//bcp.NotifyAfter = 10;
//bcp.SqlRowsCopied += delegate (object sender, SqlRowsCopiedEventArgs e)
//{
// Console.WriteLine(e.RowsCopied.ToString("#,##0") + " rows copied.");
//};
bcp.WriteToServer(new ChoEnumerableDataReader(items));
}
var ctx = new ChoETLSqlServerDbContext <T>(sqlServerSettings.ConnectionString);
var dbSet = ctx.Set <T>();
return(dbSet);
}
else
{
return(items.AsQueryable());
}
}
public void DoBulkCopy(DataTable srcData, SqlTransaction oTran)
{
SqlBulkCopyOptions setting = SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.TableLock;
using (SqlBulkCopy sqlBC = new SqlBulkCopy(oTran.Connection, setting, oTran))
{
sqlBC.BulkCopyTimeout = 600; ///設定逾時的秒數
//sqlBC.BatchSize = 1000; ///設定一個批次量寫入多少筆資料, 設定值太小會影響效能
////設定 NotifyAfter 屬性，以便在每複製 10000 個資料列至資料表後，呼叫事件處理常式。
//sqlBC.NotifyAfter = 10000;
///設定要寫入的資料庫
sqlBC.DestinationTableName = "CitySummaryTable";
/// 對應來源與目標資料欄位 左邊：C# DataTable欄位 右邊：資料庫Table欄位
sqlBC.ColumnMappings.Add("CS_PlanSchedule", "CS_PlanSchedule");
sqlBC.ColumnMappings.Add("CS_No", "CS_No");
sqlBC.ColumnMappings.Add("CS_PlanType", "CS_PlanType");
sqlBC.ColumnMappings.Add("CS_PlanTypeDetail", "CS_PlanTypeDetail");
sqlBC.ColumnMappings.Add("CS_CaseNo", "CS_CaseNo");
sqlBC.ColumnMappings.Add("CS_HostCompany", "CS_HostCompany");
sqlBC.ColumnMappings.Add("CS_JointCompany", "CS_JointCompany");
sqlBC.ColumnMappings.Add("CS_PlanName", "CS_PlanName");
sqlBC.ColumnMappings.Add("CS_PlanSummary", "CS_PlanSummary");
sqlBC.ColumnMappings.Add("CS_PlanDefect", "CS_PlanDefect");
sqlBC.ColumnMappings.Add("CS_NowResult", "CS_NowResult");
sqlBC.ColumnMappings.Add("CS_DoneResult", "CS_DoneResult");
sqlBC.ColumnMappings.Add("CS_ServiceArea", "CS_ServiceArea");
sqlBC.ColumnMappings.Add("CS_ServiceType", "CS_ServiceType");
sqlBC.ColumnMappings.Add("CS_AllArea", "CS_AllArea");
sqlBC.ColumnMappings.Add("CS_CityArea", "CS_CityArea");
sqlBC.ColumnMappings.Add("CS_CityAreaDetail", "CS_CityAreaDetail");
sqlBC.ColumnMappings.Add("CS_PlanTotalMoney", "CS_PlanTotalMoney");
sqlBC.ColumnMappings.Add("CS_PlanSubMoney", "CS_PlanSubMoney");
sqlBC.ColumnMappings.Add("CS_NewTaipei_Total", "CS_NewTaipei_Total");
sqlBC.ColumnMappings.Add("CS_Taipei_Total", "CS_Taipei_Total");
sqlBC.ColumnMappings.Add("CS_Taoyuan_Total", "CS_Taoyuan_Total");
sqlBC.ColumnMappings.Add("CS_Taichung_Total", "CS_Taichung_Total");
sqlBC.ColumnMappings.Add("CS_Tainan_Total", "CS_Tainan_Total");
sqlBC.ColumnMappings.Add("CS_Kaohsiung_Total", "CS_Kaohsiung_Total");
sqlBC.ColumnMappings.Add("CS_Yilan_Total", "CS_Yilan_Total");
sqlBC.ColumnMappings.Add("CS_HsinchuCounty_Total", "CS_HsinchuCounty_Total");
sqlBC.ColumnMappings.Add("CS_Miaoli_Total", "CS_Miaoli_Total");
sqlBC.ColumnMappings.Add("CS_Changhua_Total", "CS_Changhua_Total");
sqlBC.ColumnMappings.Add("CS_Nantou_Total", "CS_Nantou_Total");
sqlBC.ColumnMappings.Add("CS_Yunlin_Total", "CS_Yunlin_Total");
sqlBC.ColumnMappings.Add("CS_ChiayiCounty_Total", "CS_ChiayiCounty_Total");
sqlBC.ColumnMappings.Add("CS_Pingtung_Total", "CS_Pingtung_Total");
sqlBC.ColumnMappings.Add("CS_Taitung_Total", "CS_Taitung_Total");
sqlBC.ColumnMappings.Add("CS_Hualien_Total", "CS_Hualien_Total");
sqlBC.ColumnMappings.Add("CS_Penghu_Total", "CS_Penghu_Total");
sqlBC.ColumnMappings.Add("CS_Keelung_Total", "CS_Keelung_Total");
sqlBC.ColumnMappings.Add("CS_HsinchuCity_Total", "CS_HsinchuCity_Total");
sqlBC.ColumnMappings.Add("CS_ChiayiCity_Total", "CS_ChiayiCity_Total");
sqlBC.ColumnMappings.Add("CS_Kinmen_Total", "CS_Kinmen_Total");
sqlBC.ColumnMappings.Add("CS_Lienchiang_Total", "CS_Lienchiang_Total");
sqlBC.ColumnMappings.Add("CS_NewTaipei_Sub", "CS_NewTaipei_Sub");
sqlBC.ColumnMappings.Add("CS_Taipei_Sub", "CS_Taipei_Sub");
sqlBC.ColumnMappings.Add("CS_Taoyuan_Sub", "CS_Taoyuan_Sub");
sqlBC.ColumnMappings.Add("CS_Taichung_Sub", "CS_Taichung_Sub");
sqlBC.ColumnMappings.Add("CS_Tainan_Sub", "CS_Tainan_Sub");
sqlBC.ColumnMappings.Add("CS_Kaohsiung_Sub", "CS_Kaohsiung_Sub");
sqlBC.ColumnMappings.Add("CS_Yilan_Sub", "CS_Yilan_Sub");
sqlBC.ColumnMappings.Add("CS_HsinchuCounty_Sub", "CS_HsinchuCounty_Sub");
sqlBC.ColumnMappings.Add("CS_Miaoli_Sub", "CS_Miaoli_Sub");
sqlBC.ColumnMappings.Add("CS_Changhua_Sub", "CS_Changhua_Sub");
sqlBC.ColumnMappings.Add("CS_Nantou_Sub", "CS_Nantou_Sub");
sqlBC.ColumnMappings.Add("CS_Yunlin_Sub", "CS_Yunlin_Sub");
sqlBC.ColumnMappings.Add("CS_ChiayiCounty_Sub", "CS_ChiayiCounty_Sub");
sqlBC.ColumnMappings.Add("CS_Pingtung_Sub", "CS_Pingtung_Sub");
sqlBC.ColumnMappings.Add("CS_Taitung_Sub", "CS_Taitung_Sub");
sqlBC.ColumnMappings.Add("CS_Hualien_Sub", "CS_Hualien_Sub");
sqlBC.ColumnMappings.Add("CS_Penghu_Sub", "CS_Penghu_Sub");
sqlBC.ColumnMappings.Add("CS_Keelung_Sub", "CS_Keelung_Sub");
sqlBC.ColumnMappings.Add("CS_HsinchuCity_Sub", "CS_HsinchuCity_Sub");
sqlBC.ColumnMappings.Add("CS_ChiayiCity_Sub", "CS_ChiayiCity_Sub");
sqlBC.ColumnMappings.Add("CS_Kinmen_Sub", "CS_Kinmen_Sub");
sqlBC.ColumnMappings.Add("CS_Lienchiang_Sub", "CS_Lienchiang_Sub");
sqlBC.ColumnMappings.Add("CS_CreateId", "CS_CreateId");
sqlBC.ColumnMappings.Add("CS_CreateName", "CS_CreateName");
sqlBC.ColumnMappings.Add("CS_Version", "CS_Version");
sqlBC.ColumnMappings.Add("CS_Status", "CS_Status");
/// 開始寫入資料
sqlBC.WriteToServer(srcData);
}
}
static void Main(string[] args)
{
double batchDataSize = 100000; //kB
bool showHelp = false;
string sourceServerName = null, sourceUsername = null, sourcePassword = null,
sourceDatabaseName = null, destinationServerName = null, destinationUsername = null,
destinationPassword = null, destinationDatabaseName = null;
bool clearDestinationDatabase = false, checkIdentityExists = false;
IEnumerable <string> ignoredTables = Enumerable.Empty <string>();
var optionSet = new OptionSet()
{
{ "h|help", "show this message and exit", x => showHelp = x != null },
{ "srcserver=", "source server (eg. db000.appharbor.net)", x => sourceServerName = x },
{ "srcusername="******"username on source server", x => sourceUsername = x },
{ "srcpassword="******"password on source server", x => sourcePassword = x },
{ "srcdatabasename=", "source database name", x => sourceDatabaseName = x },
{ "dstserver=", "destination server", x => destinationServerName = x },
{ "dstusername="******"username on destination server", x => destinationUsername = x },
{ "dstpassword="******"password on destination server", x => destinationPassword = x },
{ "dstdatabasename=", "destination database name", x => destinationDatabaseName = x },
{ "ignoretables=", "names of tables not to copy", x => ignoredTables = x.Split(',') },
{ "cleardstdatabase", "clears the destination database before copying the data", x => clearDestinationDatabase = x != null },
{ "checkidentityexists", "only reseed identity if table has identity column", x => checkIdentityExists = x != null }
};
try
{
optionSet.Parse(args);
if (showHelp)
{
ShowHelp(optionSet);
return;
}
if (sourceServerName == null)
{
throw new OptionException("source server not specified", "srcserver");
}
if (sourceUsername == null && sourcePassword != null)
{
throw new OptionException("source username not specified", "srcusername");
}
if (sourcePassword == null && sourceUsername != null)
{
throw new OptionException("source password not specified", "srcpassword");
}
if (sourceDatabaseName == null)
{
throw new OptionException("source database name not specified", "srcdatabasename");
}
if (destinationServerName == null)
{
throw new OptionException("destination server not specified", "dstserver");
}
if (destinationUsername == null && destinationPassword != null)
{
throw new OptionException("destination username not specified", "dstusername");
}
if (destinationPassword == null && destinationUsername != null)
{
throw new OptionException("destination password not specified", "dstpassword");
}
if (destinationDatabaseName == null)
{
throw new OptionException("destination database name not specified", "dstdatabasename");
}
}
catch (OptionException exception)
{
Console.Write(string.Format("{0}: ", AppDomain.CurrentDomain.FriendlyName));
Console.WriteLine(exception.Message);
Console.WriteLine("Try {0} --help for more information", AppDomain.CurrentDomain.FriendlyName);
return;
}
Console.WriteLine("Retrieving source database table information...");
var usingTrustedConnection = string.IsNullOrEmpty(sourceUsername) && string.IsNullOrEmpty(sourcePassword);
var sourceConnection = usingTrustedConnection
? new ServerConnection(sourceServerName)
{
LoginSecure = true
}
: new ServerConnection(sourceServerName, sourceUsername, sourcePassword);
var sourceServer = new Server(sourceConnection);
var sourceDatabase = sourceServer.Databases[sourceDatabaseName];
var tables = sourceDatabase.Tables
.OfType <Table>()
.Where(x => !x.IsSystemObject)
.Select(x => '[' + x.Schema + ']' + ".[" + x.Name + ']')
.ToList();
var actualExcludedTables = tables.Intersect(ignoredTables);
if (actualExcludedTables.Any())
{
Console.WriteLine(string.Format("Ignoring: {0}", string.Join(",", actualExcludedTables)));
}
tables = tables.Except(ignoredTables).ToList();
Console.WriteLine(string.Format("Copying {0} tables: {1}", tables.Count(), string.Join(",", tables)));
var destinationConnectionString = GetConnectionString(destinationServerName,
destinationDatabaseName, destinationUsername, destinationPassword);
var sourceConnectionString = GetConnectionString(sourceServerName,
sourceDatabaseName, sourceUsername, sourcePassword);
var watch = Stopwatch.StartNew();
// clear the data before copying
if (clearDestinationDatabase)
{
using (var connection = new SqlConnection(destinationConnectionString))
{
using (SqlCommand command = connection.CreateCommand())
{
// http://stackoverflow.com/questions/155246/how-do-you-truncate-all-tables-in-a-database-using-tsql/156813#156813
StringBuilder commandBuilder = new StringBuilder();
commandBuilder.Append(
@"
-- disable all constraints
EXEC sp_msforeachtable ""ALTER TABLE ? NOCHECK CONSTRAINT all""
-- delete data in all tables
EXEC sp_msforeachtable ""DELETE FROM ?""
-- enable all constraints
exec sp_msforeachtable ""ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all""
" );
if (checkIdentityExists)
{
// http://stackoverflow.com/questions/6542061/reseed-sql-server-identity-columns
commandBuilder.Append(
@"-- reseed (auto increment to 0) on user tables with identity column
DECLARE @reseedSql NVARCHAR(MAX);
SET @reseedSql = N'';
SELECT @reseedSql = @reseedSql + N'DBCC CHECKIDENT('''
+ QUOTENAME(OBJECT_SCHEMA_NAME(col.[object_id]))
+ '.' + QUOTENAME(OBJECT_NAME(col.[object_id]))
+ ''', RESEED, 0);' + CHAR(13) + CHAR(10)
FROM sys.columns as col
JOIN sys.tables as tbl
ON col.[object_id] = tbl.[object_id]
WHERE tbl.[type] = 'U'
AND col.[is_identity] = 1;
EXEC sp_executesql @reseedSql;" );
}
else
{
commandBuilder.Append(@"
-- reseed (auto increment to 0)
EXEC sp_msforeachtable ""DBCC CHECKIDENT ( '?', RESEED, 0)""
" );
}
command.CommandText = commandBuilder.ToString();
Console.WriteLine("Clearing the destination database");
connection.Open();
command.ExecuteNonQuery();
}
}
}
foreach (var table in tables)
{
using (var connection = new SqlConnection(sourceConnectionString))
{
double rowBatchSize = 10000;
double rows = 0;
double dataSize;
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format("exec sp_spaceused '{0}'", table);
using (var reader = command.ExecuteReader())
{
reader.Read();
var rowString = (string)reader["rows"];
rows = double.Parse(rowString);
var dataSizeString = (string)reader["data"];
dataSize = double.Parse(dataSizeString.Split(' ').First()); //kB
if (rows > 0 && dataSize > 0)
{
double rowSize = dataSize / rows;
rowBatchSize = (int)(batchDataSize / rowSize);
}
}
}
if (rows > 0)
{
var columns = GetColumnNames(connection, table);
Console.Write(string.Format("Copying {0} - {1} rows, {2:0.00} MB: ", table, rows, dataSize / 1024));
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format("select * from {0}", table);
using (var reader = command.ExecuteReader())
{
using (var bulkCopy = new SqlBulkCopy(
destinationConnectionString, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.TableLock))
{
bulkCopy.NotifyAfter = Math.Max((int)rows / 10, 1);
bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(SqlRowsCopied);
bulkCopy.DestinationTableName = table;
bulkCopy.BatchSize = (int)rowBatchSize;
bulkCopy.BulkCopyTimeout = int.MaxValue;
foreach (var columnName in columns)
{
bulkCopy.ColumnMappings.Add(columnName, columnName);
}
bulkCopy.WriteToServer(reader);
}
}
}
Console.WriteLine();
}
else
{
Console.WriteLine(string.Format("{0} had no rows", table));
}
}
}
watch.Stop();
Console.WriteLine("Copy complete, total time {0} s", watch.ElapsedMilliseconds / 1000);
}
