public static void InsertFromANTables(DataSet tableList) { using (var currentContext = new TrudoyomkostDBContext(Properties.Settings.Default.TrudoyomkostDBConnectionString)) { LinqQueryForTrudoyomkost.FilldcInfProducts(currentContext, ref DcInfProducts); LinqQueryForTrudoyomkost.FillDictDetNumID(currentContext, ref DicDetNumAndId); LinqQueryForTrudoyomkost.FillDictDepIDCode(currentContext, ref DicDepCodeAndId); Properties.Settings.Default.DetID = currentContext.InfDet.ToList().Count == 0 ? 0 : ++currentContext.InfDet.ToList().Last().ID; } if (int.Parse((tableList.Tables[0].Rows[0].Field <string>("CEH").Trim())) != Properties.Settings.Default.DepNum) { return; } _whereUseTableAdapter.DeleteAllQuery(); foreach (DataTable itemTable in tableList.Tables) { foreach (DataRow itemRow in itemTable.Rows) { string NDET = (string)itemRow["NDET"]; if (!DicDetNumAndId.ContainsKey(NDET)) { var infDetItem = FillItemInfDet(itemRow); FilltmpInfDetRow(ref infDetItem); DicDetNumAndId.Add(NDET, Properties.Settings.Default.DetID); Properties.Settings.Default.DetID++; } var itemWhereuse = FillItemWhereUse(itemRow); FilltmpWhereUseRow(itemWhereuse); } } Properties.Settings.Default.Save(); SqlCeBulkCopy bulkInsert = new SqlCeBulkCopy(connString); DataTable tmptbInfDet = InfDetDataTable; DataTable tmptbWhereUse = WhereUseDataTable; if (tmptbInfDet.Rows.Count > 0) { bulkInsert.DestinationTableName = "infDet"; bulkInsert.WriteToServer(tmptbInfDet); } if (tmptbWhereUse.Rows.Count > 0) { bulkInsert.DestinationTableName = "whereUse"; bulkInsert.WriteToServer(tmptbWhereUse); } WhereUseDataTable.Clear(); InfDetDataTable.Clear(); bulkInsert.Close(); }
public Result InsertBulk(string tableName, IEnumerable <object> entity) { Result result = new Result(); try { using (var con = new DatabaseConnection().Connection) { con.Open(); using (SqlCeBulkCopy copy = new SqlCeBulkCopy(con)) { copy.DestinationTableName = tableName; var table = DataTableHelper.ToDataTable(entity.ToList(), tableName); copy.WriteToServer(table); result.IsSuccess = true; } con.Close(); } } catch (Exception ex) { } return(result); }
static async Task PerformAsync() { List <DbProfile> parsedData; await Task.Run(() => { parsedData = ParseJsonResponce(Properties.Settings.Default.Sample); Database.SetInitializer(new DropCreateDatabaseAlways <DbprofilDbcontext>()); using (var context = new DbprofilDbcontext()) { context.Database.Initialize(true); //radProgressBar1.Text = "Working"; using (SqlCeBulkCopy bcp = new SqlCeBulkCopy(context.Database.Connection.ConnectionString)) { bcp.DestinationTableName = "DbProfiles"; // sw.Restart(); bcp.WriteToServer(parsedData); // parsedData = null; //sw.Stop(); //Console.WriteLine( // "Saved {0} entities using SqlCeBulkCopy in {1}", students.Count, sw.Elapsed.ToString()); } } }); }
public string BulkInsert(DataTable x, string local_table) { SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions(); options = options |= SqlCeBulkCopyOptions.KeepNulls; using (SqlCeBulkCopy bc = new SqlCeBulkCopy(varSqlConnect, options)) { bc.DestinationTableName = local_table; try { //conn.Open(); bc.WriteToServer(x); } catch (Exception ex) { Cursor.Current = Cursors.Default; return(ex.ToString() + local_table); //clsException.EnableException(ex); } finally { //conn.Close(); } return(null); } }
private void writteToBD(DataTable dt3) { String connString = Properties.Settings.Default.TrudoyomkostDBConnectionString; SqlCeBulkCopy bulkInsert = new SqlCeBulkCopy(connString); bulkInsert.DestinationTableName = "infTariff"; bulkInsert.WriteToServer(dt3); }
private StringBuilder WriteOutputToDatabaseSqlCe(List <Row> rows) { StringBuilder sb = new StringBuilder(); DateTime StartTime = DateTime.Now; ExtensionMethods.TraceInformation("Converting to DataTable - " + StartTime.ToString()); DataTable outDataTable = ToDataTable(rows); sb = outDataTable.ToCsvStringBuilder(); DateTime EndTime = DateTime.Now; ExtensionMethods.TraceInformation("Converted - {0}. Time taken: {1}", EndTime, (EndTime - StartTime).ToString()); bool keepNulls = true; SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions(); if (keepNulls) { options = options |= SqlCeBulkCopyOptions.KeepNulls; } using (SqlCeBulkCopy bulkCopy = new SqlCeBulkCopy(ConnectionStringKey.Value, options)) { bulkCopy.DestinationTableName = DatabaseMap.TargetTableName; foreach (ColumnMapInfo columnMapInfo in DatabaseMap) { if ((!string.IsNullOrEmpty(columnMapInfo.InputColumn)) && (columnMapInfo.InputColumn != ColumnMapInfo.DatabaseDefault)) { SqlCeBulkCopyColumnMapping mapInfo = new SqlCeBulkCopyColumnMapping(columnMapInfo.InputColumn, columnMapInfo.OutputColumn); bulkCopy.ColumnMappings.Add(mapInfo); } } try { bulkCopy.WriteToServer(outDataTable); } catch (Exception ex) { IsErrored = true; ExtensionMethods.TraceError(ex.Message + ". Trying to get more information...!"); _Job.TraceError(GetBulkCopyFailedData(outDataTable, bulkCopy.ColumnMappings)); } finally { if (outDataTable != null) { outDataTable.Dispose(); } } } return(sb); }
public virtual void BulkInsert(List <TDb> values) { SqlCeConnection conn = Database.GetConnection(); var getDestinationTable = GetTableName(); var sqlBulk = new SqlCeBulkCopy(conn); sqlBulk.DestinationTableName = getDestinationTable; var test = values.ToDataTable(); sqlBulk.WriteToServer(values.ToDataTable()); }
private void BULK(DataTable dt) { var options = new SqlCeBulkCopyOptions(); options = options |= SqlCeBulkCopyOptions.KeepNulls; using (SqlCeBulkCopy bc = new SqlCeBulkCopy(this.cnn, options)) { bc.DestinationTableName = "CatInventario"; bc.WriteToServer(dt); } }
public static void AlgStatBulkInsert(APIStatsCurrent stats) { SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions(); var reader = new AlgorithmStatDataReader(stats); using (SqlCeBulkCopy bc = new SqlCeBulkCopy(ConfigurationManager.ConnectionStrings["MonDb"].ConnectionString, options)) { bc.DestinationTableName = "AlgorithmStat"; bc.WriteToServer(reader); } }
private void BULK(DataTable dt) { SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions(); options = options |= SqlCeBulkCopyOptions.KeepNulls; using (SqlCeBulkCopy bc = new SqlCeBulkCopy(this.cnnstr, options)) { bc.DestinationTableName = "CatMaterial"; bc.WriteToServer(dt); } }
private static void DoBulkCopy(string tableName, DataTable dataTable, SqlCeConnection conn) { //SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions(); //if (keepNulls) //{ // options = options |= SqlCeBulkCopyOptions.KeepNulls; //} using (SqlCeBulkCopy bc = new SqlCeBulkCopy(conn)) { bc.DestinationTableName = tableName; bc.WriteToServer(dataTable); } }
/// <summary> /// The DoBulkCopy /// </summary> /// <param name="keepNulls">The <see cref="bool"/></param> /// <param name="reader">The <see cref="IEnumerable{Maaslar}"/></param> /// <param name="tablo">The <see cref="string"/></param> public static void DoBulkCopy(bool keepNulls, IEnumerable <Maaslar> reader, string tablo) { var options = new SqlCeBulkCopyOptions(); if (keepNulls) { options |= SqlCeBulkCopyOptions.KeepNulls; } using (var bc = new SqlCeBulkCopy(Database.Connection, options)) { bc.DestinationTableName = tablo; bc.WriteToServer(reader); } }
public void Vs() { var sw = new Stopwatch(); sw.Start(); SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions(); using (SqlCeBulkCopy bc = new SqlCeBulkCopy("Data Source=|DataDirectory|Database1.sdf", options)) { bc.DestinationTableName = "Items"; bc.WriteToServer(Items(1000000), typeof(Item)); } sw.Stop(); Console.WriteLine("bulk insert elapsed {0}ms", sw.Elapsed.TotalMilliseconds); }
public static void InsertDt <T>(ref T dtInput, string tableName) where T : DataTable { SqlCeBulkCopy bulkInsert = new SqlCeBulkCopy(connString); DataTable tmptable = dtInput; if (tmptable.Rows.Count > 0) { bulkInsert.DestinationTableName = tableName; bulkInsert.WriteToServer(tmptable); } dtInput.Clear(); bulkInsert.Close(); bulkInsert.Dispose(); tmptable.Dispose(); }
static void InsertISharedRecord(Type tableType, SqlCeConnection sourceConn, SqlCeConnection destConn, IEnumerable<IntegerRange> destDbRemainingAllocations, IEnumerable<int> IdsToUpdate) { string tableName = TableName(tableType); bool wasSourceClosed; bool wasDestClosed; if (sourceConn.State == ConnectionState.Closed) { wasSourceClosed = true; sourceConn.Open(); } else { wasSourceClosed = false; } if (destConn.State == ConnectionState.Closed) { wasDestClosed = true; destConn.Open(); } else { wasDestClosed = false; } string queryForNewRecords = string.Format("select * from {0} where ({1})", tableName, WhereRange(destDbRemainingAllocations, tableName)); if (IdsToUpdate.Any()) { string updatedRecordWhereClause = string.Format("Id in ({0})", string.Join(",", IdsToUpdate)); using (SqlCeCommand cmd = new SqlCeCommand(string.Format("delete from {0} where {1}", tableName, updatedRecordWhereClause), destConn)) { cmd.ExecuteNonQuery(); } queryForNewRecords += " or " + updatedRecordWhereClause; } using (SqlCeBulkCopy bc = new SqlCeBulkCopy(destConn)) { using (SqlCeCommand cmd = new SqlCeCommand(queryForNewRecords, sourceConn)) { bc.DestinationTableName = tableName; bc.WriteToServer((System.Data.IDataReader)cmd.ExecuteReader()); } } if (wasDestClosed) { destConn.Close(); } if (wasSourceClosed) { sourceConn.Close();} }
private static void transferTableData(SqlCeConnection sourceConnection, SqlCeConnection targetConnection, SqlCeTransaction transaction, string tableName) { using (SqlCeCommand sourceCommand = sourceConnection.CreateCommand()) { sourceCommand.CommandText = "Select * from " + tableName; using (SqlCeDataReader sourceReader = sourceCommand.ExecuteReader()) { SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions(); options = options |= SqlCeBulkCopyOptions.KeepNulls; string[] tableNames = { "FirmDetails" }; if (!tableNames.Contains(tableName)) { options |= SqlCeBulkCopyOptions.KeepIdentity; } using (SqlCeBulkCopy bc = new SqlCeBulkCopy(targetConnection, options, transaction)) { bc.DestinationTableName = tableName; bc.WriteToServer((IDataReader)sourceReader); } if (!tableNames.Contains(tableName)) { using (SqlCeCommand command = targetConnection.CreateCommand()) { command.Transaction = transaction; command.CommandText = "SELECT MAX(ID) FROM " + tableName; object value = command.ExecuteScalar(); int id; if (value == null || value == DBNull.Value) { id = 1; } else { id = (int)value; } command.CommandText = "ALTER TABLE " + tableName + " ALTER COLUMN ID IDENTITY (" + (id + 1) + ", 1)"; command.ExecuteNonQuery(); } } } } }
public static void ProfitStatBulkInsert(IEnumerable <CoinProfit> coinProfitList) { var filteredList = coinProfitList.Where(c => !c.ProfitCount.Equals(double.NaN) && !c.ProfitCountPercent.Equals(double.NaN)); if (filteredList.Count() == 0) { return; } SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions(); var reader = new ProfitStatDataReader(filteredList); using (SqlCeBulkCopy bc = new SqlCeBulkCopy(ConfigurationManager.ConnectionStrings["MonDb"].ConnectionString, options)) { bc.DestinationTableName = "ProfitStat"; bc.WriteToServer(reader); } }
private void BULK(DataTable dt) { try { SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions(); options = options |= SqlCeBulkCopyOptions.KeepNulls; using (SqlCeBulkCopy bc = new SqlCeBulkCopy(this.cnnstr, options)) { bc.DestinationTableName = "CatMaterial"; bc.WriteToServer(dt); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public static void InsertFromTARIF() { foreach (var item in TarifDataTable) { InfTariff infTariff = FillInfTarifItem(item); FilltmpInfTarifDetRow(ref infTariff); } SqlCeBulkCopy bulkInsert = new SqlCeBulkCopy(connString); DataTable tmpInfTariff = InfTariffDataTable; if (tmpInfTariff.Rows.Count > 0) { bulkInsert.DestinationTableName = "infTariff"; bulkInsert.WriteToServer(tmpInfTariff); } bulkInsert.Close(); bulkInsert.Dispose(); tmpInfTariff.Dispose(); }
public static void InsertFromANTables(DataSet tableList) { using (var currentContext = new TrudoyomkostDBContext(Properties.Settings.Default.TrudoyomkostDBConnectionString)) { LinqQueryForTrudoyomkost.FilldcInfProducts(currentContext, ref DcInfProducts); LinqQueryForTrudoyomkost.FillDictDetNumID(currentContext, ref DicDetNumAndId); LinqQueryForTrudoyomkost.FillDictDepIDCode(currentContext, ref DicDepCodeAndId); Properties.Settings.Default.DetID = currentContext.InfDet.ToList().Count == 0 ? 0 : ++currentContext.InfDet.ToList().Last().ID; } if (int.Parse((tableList.Tables[0].Rows[0].Field<string>("CEH").Trim())) != Properties.Settings.Default.DepNum) return; _whereUseTableAdapter.DeleteAllQuery(); foreach (DataTable itemTable in tableList.Tables) { foreach (DataRow itemRow in itemTable.Rows) { string NDET = (string)itemRow["NDET"]; if (!DicDetNumAndId.ContainsKey(NDET)) { var infDetItem = FillItemInfDet(itemRow); FilltmpInfDetRow(ref infDetItem); DicDetNumAndId.Add(NDET, Properties.Settings.Default.DetID); Properties.Settings.Default.DetID++; } var itemWhereuse = FillItemWhereUse(itemRow); FilltmpWhereUseRow(itemWhereuse); } } Properties.Settings.Default.Save(); SqlCeBulkCopy bulkInsert = new SqlCeBulkCopy(connString); DataTable tmptbInfDet = InfDetDataTable; DataTable tmptbWhereUse = WhereUseDataTable; if (tmptbInfDet.Rows.Count > 0) { bulkInsert.DestinationTableName = "infDet"; bulkInsert.WriteToServer(tmptbInfDet); } if (tmptbWhereUse.Rows.Count > 0) { bulkInsert.DestinationTableName = "whereUse"; bulkInsert.WriteToServer(tmptbWhereUse); } WhereUseDataTable.Clear(); InfDetDataTable.Clear(); bulkInsert.Close(); }
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e) { AddTarrif at = new AddTarrif(); // создаем обект аддтариф DataTable dt = new DataTable(); string zapros = " SELECT * FROM LabourNorm ORDER BY ID"; //селектим все кроме ID - оно будет автоматически ставится, и Valuation и ValPreparTime заместо них будут новые значения dt = at.ReturnDataTable(zapros); //Присваеваем дата тейблу содержание таблици at.deleteFromDB("EXEC sp_rename 'LabourNorm', 'LabourNorm " + dt.Rows[0]["DocNum"] + "'"); //переименовуем LabourNorm int lastID = (int)dt.Rows[dt.Rows.Count - 1]["ID"]; //Последний ID(Самый большой) for (int id = 0; id != lastID; id++) { if (id + 1 != (int)dt.Rows[id]["ID"]) { DataRow _ravi = dt.NewRow(); _ravi["ID"] = id + 1; _ravi["infDetID"] = -1; _ravi["OperNum"] = "---"; _ravi["DepRegion"] = -1; _ravi["ProfCode"] = -1; _ravi["NameKindWork"] = ""; _ravi["KindPay"] = "П"; _ravi["TariffNetNum"] = 1; _ravi["WorkerRate"] = 1; _ravi["ItemCTN"] = -1; _ravi["PreparTimeCTN"] = -1; _ravi["ItemPayNorm"] = -1; _ravi["PreparTimePayNorm"] = -1; _ravi["Valuation"] = -1; _ravi["ValPreparTime"] = -1; _ravi["CoeffCTN"] = -1; _ravi["DocNum"] = "---"; _ravi["Date"] = "01.01.1980 00:00:00"; _ravi["TaskNumber"] = "---"; dt.Rows.InsertAt(_ravi, id); } } DataTable dataTableForTariff = new DataTable(); dataTableForTariff = at.ReturnDataTable(" SELECT * FROM infTariff"); for (int i = 0; i < dt.Rows.Count; i++) { string tariffNetNum = dt.Rows[i]["TariffNetNum"].ToString(); string kindPay = dt.Rows[i]["KindPay"].ToString(); string workerRate = dt.Rows[i]["WorkerRate"].ToString(); double coef = (double.Parse(dt.Rows[i]["CoeffCTN"].ToString()) == 0) ? 1 : double.Parse(dt.Rows[i]["CoeffCTN"].ToString()); double itemPayNorm = double.Parse(dt.Rows[i]["ItemPayNorm"].ToString()); if (itemPayNorm == 0.4051) { } var results = from DataRow myRow in dataTableForTariff.Rows where (myRow["TariffNetNum"].ToString() == tariffNetNum) && (myRow["KindPay"].ToString() == kindPay) && (myRow["WorkerRate"].ToString() == workerRate) select myRow; double currTarif; try { currTarif = double.Parse(results.CopyToDataTable().Rows[0]["HourCost"].ToString()); } catch { currTarif = 0; } double preparTimePayNorm = double.Parse(dt.Rows[i]["PreparTimePayNorm"].ToString()); string g = (itemPayNorm * currTarif).ToString("N" + Properties.Settings.Default.RoundNum); dt.Rows[i]["Valuation"] = (itemPayNorm * currTarif).ToString("N" + Properties.Settings.Default.RoundNum); dt.Rows[i]["ValPreparTime"] = (preparTimePayNorm * currTarif).ToString("N" + Properties.Settings.Default.RoundNum); dt.Rows[i]["DocNum"] = textBoxDataPrikaz.Text; } at.deleteFromDB("CREATE TABLE [LabourNorm]([ID] INT NOT NULL IDENTITY (1,1),[infDetID] INT NOT NULL,[OperNum] NVARCHAR(100),[DepRegion] FLOAT NOT NULL,[ProfCode] INT NOT NULL,[NameKindWork] NVARCHAR(50),[KindPay] NVARCHAR(1) NOT NULL,[TariffNetNum] TINYINT NOT NULL,[WorkerRate] FLOAT NOT NULL,[ItemCTN] FLOAT NOT NULL,[PreparTimeCTN] FLOAT NOT NULL,[ItemPayNorm] FLOAT NOT NULL,[PreparTimePayNorm] FLOAT NOT NULL,[Valuation] FLOAT NOT NULL,[ValPreparTime] FLOAT,[CoeffCTN] FLOAT NOT NULL,[DocNum] NVARCHAR(100),[Date] DATETIME NOT NULL,[TaskNumber] NVARCHAR(30));"); //очишаем таблицу перед записью String connString = Properties.Settings.Default.TrudoyomkostDBConnectionString; SqlCeBulkCopy bulkInsert = new SqlCeBulkCopy(connString); bulkInsert.DestinationTableName = "LabourNorm"; bulkInsert.WriteToServer(dt); at.deleteFromDB("DELETE FROM LabourNorm WHERE infDetID ='-1' AND ProfCode = '-1'"); }
private string GetBulkCopyFailedData(DataTable table, SqlCeBulkCopyColumnMappingCollection columnMappings) { StringBuilder errorMessage = new StringBuilder("Bulk copy failures:" + Environment.NewLine); SqlCeConnection connection = null; SqlCeTransaction transaction = null; SqlCeBulkCopy bulkCopy = null; try { connection = new SqlCeConnection(ConnectionStringKey.Value); connection.Open(); transaction = connection.BeginTransaction(); SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions(); options = options |= SqlCeBulkCopyOptions.KeepNulls; bulkCopy = new SqlCeBulkCopy(connection, options, transaction); bulkCopy.DestinationTableName = DatabaseMap.TargetTableName; foreach (SqlCeBulkCopyColumnMapping mapping in columnMappings) { bulkCopy.ColumnMappings.Add(mapping); } DataTable oneRecordTable = table.Clone(); foreach (DataRow row in table.Rows) { oneRecordTable.Rows.Clear(); oneRecordTable.ImportRow(row); try { bulkCopy.WriteToServer(oneRecordTable); } catch (Exception ex) { DataRow faultyDataRow = oneRecordTable.Rows[0]; errorMessage.AppendFormat("Error: {0}{1}", ex.Message, Environment.NewLine); errorMessage.AppendFormat("Row data: {0}", Environment.NewLine); foreach (DataColumn column in oneRecordTable.Columns) { errorMessage.AppendFormat( "\tColumn {0} - [{1}]{2}", column.ColumnName, faultyDataRow[column.ColumnName].ToString(), Environment.NewLine); } } } } catch (Exception ex) { errorMessage.Append("Unable to document SqlBulkCopy errors. See inner exceptions for details."); errorMessage.Append(ex.ToString()); } finally { if (transaction != null) { transaction.Rollback(); } if (connection.State != ConnectionState.Closed) { connection.Close(); } } return(errorMessage.ToString()); }
private void pcmarket_schema_import() { string[] filePaths = Directory.GetFiles(dfile, "*.txt"); string connectionString; connectionString = "DataSource=Baza.sdf; Password=matrix1"; SqlCeConnection cn = new SqlCeConnection(connectionString); cn.Open(); foreach (string r in filePaths) { StreamReader sr = new StreamReader(r, Encoding.Default); DataTable edihead = new DataTable("edihead"); //edihead.Locale = CultureInfo.CurrentCulture; edihead.Columns.Add("FileName", typeof(String)); edihead.Columns.Add("TypPolskichLiter", typeof(String)); edihead.Columns.Add("TypDok", typeof(String)); edihead.Columns.Add("NrDok", typeof(String)); edihead.Columns.Add("Data", typeof(String)); edihead.Columns.Add("DataRealizacji", typeof(String)); edihead.Columns.Add("Magazyn", typeof(String)); edihead.Columns.Add("SposobPlatn", typeof(String)); edihead.Columns.Add("TerminPlatn", typeof(String)); edihead.Columns.Add("IndeksCentralny", typeof(String)); edihead.Columns.Add("NazwaWystawcy", typeof(String)); edihead.Columns.Add("AdresWystawcy", typeof(String)); edihead.Columns.Add("KodWystawcy", typeof(String)); edihead.Columns.Add("MiastoWystawcy", typeof(String)); edihead.Columns.Add("UlicaWystawcy", typeof(String)); edihead.Columns.Add("NIPWystawcy", typeof(String)); edihead.Columns.Add("BankWystawcy", typeof(String)); edihead.Columns.Add("KontoWystawcy", typeof(String)); edihead.Columns.Add("TelefonWystawcy", typeof(String)); edihead.Columns.Add("NrWystawcyWSieciSklepow", typeof(String)); edihead.Columns.Add("NazwaOdbiorcy", typeof(String)); edihead.Columns.Add("AdresOdbiorcy", typeof(String)); edihead.Columns.Add("KodOdbiorcy", typeof(String)); edihead.Columns.Add("MiastoOdbiorcy", typeof(String)); edihead.Columns.Add("UlicaOdbiorcy", typeof(String)); edihead.Columns.Add("NIPOdbiorcy", typeof(String)); edihead.Columns.Add("BankOdbiorcy", typeof(String)); edihead.Columns.Add("KontoOdbiorcy", typeof(String)); edihead.Columns.Add("TelefonOdbiorcy", typeof(String)); edihead.Columns.Add("NrOdbiorcyWSieciSklepow", typeof(String)); edihead.Columns.Add("DoZaplaty", typeof(String)); edihead.Columns.Add("status", typeof(String)); edihead.Columns.Add("complete", typeof(Boolean)); DataTable edibody = new DataTable("edibody"); // edibody.Locale = CultureInfo.CurrentCulture; edibody.Columns.Add("NrDok", typeof(String)); edibody.Columns.Add("Nazwa", typeof(String)); edibody.Columns.Add("Kod", typeof(String)); edibody.Columns.Add("Vat", typeof(String)); edibody.Columns.Add("Jm", typeof(String)); edibody.Columns.Add("Asortyment", typeof(String)); edibody.Columns.Add("Sww", typeof(String)); edibody.Columns.Add("PKWiU", typeof(String)); edibody.Columns.Add("Ilosc", typeof(String)); edibody.Columns.Add("Cena", typeof(String)); edibody.Columns.Add("Wartosc", typeof(String)); edibody.Columns.Add("IleWOpak", typeof(String)); edibody.Columns.Add("CenaSp", typeof(String)); edibody.Columns.Add("status", typeof(String)); edibody.Columns.Add("complete", typeof(Boolean)); DataTable ediend = new DataTable("ediend"); // ediend.Locale = CultureInfo.CurrentCulture; ediend.Columns.Add("NrDok", typeof(String)); ediend.Columns.Add("Vat", typeof(String)); ediend.Columns.Add("SumaNet", typeof(String)); ediend.Columns.Add("SumaVat", typeof(String)); ediend.Columns.Add("status", typeof(String)); ediend.Columns.Add("complete", typeof(Boolean)); string typdok = ""; string Numerdok = ""; DataRow row = edihead.NewRow(); row["FileName"] = Path.GetFileName(r); row["status"] = "Nowy"; row["complete"] = false; string line; while ((line = sr.ReadLine()) != null) { string[] items = line.Split(':'); //make sure it has 3 items if (items[0] == "TypPolskichLiter") { row["TypPolskichLiter"] = items[1]; } if (items[0] == "TypDok") { row["TypDok"] = items[1]; typdok = items[1]; } if (items[0] == "NrDok") { row["NrDok"] = items[1]; Numerdok = items[1]; } if (items[0] == "Data") { row["Data"] = items[1]; } if (items[0] == "DataRealizacji") { row["DataRealizacji"] = items[1]; } if (items[0] == "Magazyn") { row["Magazyn"] = items[1]; } if (items[0] == "SposobPlatn") { row["SposobPlatn"] = items[1]; } if (items[0] == "TerminPlatn") { row["TerminPlatn"] = items[1]; } if (items[0] == "IndeksCentralny") { row["IndeksCentralny"] = items[1]; } if (items[0] == "NazwaWystawcy") { row["NazwaWystawcy"] = items[1]; } if (items[0] == "AdresWystawcy") { row["AdresWystawcy"] = items[1]; } if (items[0] == "KodWystawcy") { row["KodWystawcy"] = items[1]; } if (items[0] == "MiastoWystawcy") { row["MiastoWystawcy"] = items[1]; } if (items[0] == "UlicaWystawcy") { row["UlicaWystawcy"] = items[1]; } if (items[0] == "NIPWystawcy") { row["NIPWystawcy"] = items[1]; } if (items[0] == "BankWystawcy") { row["BankWystawcy"] = items[1]; } if (items[0] == "KontoWystawcy") { row["KontoWystawcy"] = items[1]; } if (items[0] == "TelefonWystawcy") { row["TelefonWystawcy"] = items[1]; } if (items[0] == "NrWystawcyWSieciSklepow") { row["NrWystawcyWSieciSklepow"] = items[1]; } if (items[0] == "NazwaOdbiorcy") { row["NazwaOdbiorcy"] = items[1]; } if (items[0] == "AdresOdbiorcy") { row["AdresOdbiorcy"] = items[1]; } if (items[0] == "KodOdbiorcy") { row["KodOdbiorcy"] = items[1]; } if (items[0] == "MiastoOdbiorcy") { row["MiastoOdbiorcy"] = items[1]; } if (items[0] == "UlicaOdbiorcy") { row["UlicaOdbiorcy"] = items[1]; } if (items[0] == "NIPOdbiorcy") { row["NIPOdbiorcy"] = items[1]; } if (items[0] == "BankOdbiorcy") { row["BankOdbiorcy"] = items[1]; } if (items[0] == "KontoOdbiorcy") { row["KontoOdbiorcy"] = items[1]; } if (items[0] == "TelefonOdbiorcy") { row["TelefonOdbiorcy"] = items[1]; } if (items[0] == "NrOdbiorcyWSieciSklepow") { row["NrOdbiorcyWSieciSklepow"] = items[1]; } if (items[0] == "DoZaplaty") { row["DoZaplaty"] = items[1]; } if (items[0] == "Linia") { char[] delim = new char[2]; delim[0] = '{'; delim[1] = '}'; string[] linie = items[1].Split(delim); DataRow row1 = edibody.NewRow(); row1["NrDok"] = Numerdok; row1["Nazwa"] = linie[1]; row1["Kod"] = linie[3]; row1["Vat"] = linie[5]; row1["Jm"] = linie[7]; row1["Asortyment"] = linie[9]; row1["Sww"] = linie[11]; row1["PKWiU"] = linie[13]; row1["Ilosc"] = linie[15]; row1["Cena"] = linie[17]; row1["Wartosc"] = linie[19]; row1["IleWOpak"] = linie[21]; row1["CenaSp"] = linie[23]; row1["status"] = "Nowy"; row1["complete"] = false; edibody.Rows.Add(row1); } if (items[0] == "Stawka") { char[] delim = new char[2]; delim[0] = '{'; delim[1] = '}'; string[] linie = items[1].Split(delim); DataRow row2 = ediend.NewRow(); row2["NrDok"] = Numerdok; row2["Vat"] = linie[1]; row2["SumaNet"] = linie[3]; row2["SumaVat"] = linie[5]; row2["status"] = "Nowy"; row2["complete"] = false; ediend.Rows.Add(row2); } } string testdokeh = ""; int flagaimp = 0; SqlCeCommand testnrdok = cn.CreateCommand(); testnrdok.CommandText = "SELECT NrDok From edihead"; testnrdok.Prepare(); SqlCeDataReader sdr = testnrdok.ExecuteReader(); while (sdr.Read()) { if (sdr.IsDBNull(0) != true) { testdokeh = sdr.GetString(0); if (testdokeh == Numerdok) { flagaimp = 1; } } } if (typdok != transfer) { flagaimp = 1; } if (flagaimp != 1) { edihead.Rows.Add(row); SqlCeBulkCopy bulkcopy = new SqlCeBulkCopy(connectionString); bulkcopy.DestinationTableName = edihead.TableName; try { bulkcopy.WriteToServer(edihead); } catch (Exception e) { MessageBox.Show(e.Message); } bulkcopy.DestinationTableName = edibody.TableName; try { bulkcopy.WriteToServer(edibody); } catch (Exception e) { MessageBox.Show(e.Message); } bulkcopy.DestinationTableName = ediend.TableName; try { bulkcopy.WriteToServer(ediend); } catch (Exception e) { MessageBox.Show(e.Message); } sr.Close(); if (Directory.Exists(dfile + "\\usunięte\\") != true) { Directory.CreateDirectory(dfile + "\\usunięte\\"); } if (File.Exists(dfile + "\\usunięte\\" + Path.GetFileName(r)) != false) { File.Delete(dfile + "\\usunięte\\" + Path.GetFileName(r)); } File.Move(r, dfile + "\\usunięte\\" + Path.GetFileName(r)); } } cn.Close(); }
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e) { AddTarrif at = new AddTarrif(); // создаем обект аддтариф DataTable dt = new DataTable(); string zapros = " SELECT * FROM LabourNorm ORDER BY ID";//селектим все кроме ID - оно будет автоматически ставится, и Valuation и ValPreparTime заместо них будут новые значения dt = at.ReturnDataTable(zapros); //Присваеваем дата тейблу содержание таблици at.deleteFromDB("EXEC sp_rename 'LabourNorm', 'LabourNorm " + dt.Rows[0]["DocNum"] + "'");//переименовуем LabourNorm int lastID = (int)dt.Rows[dt.Rows.Count - 1]["ID"]; //Последний ID(Самый большой) for (int id = 0; id != lastID; id++) { if (id + 1 != (int)dt.Rows[id]["ID"]) { DataRow _ravi = dt.NewRow(); _ravi["ID"] = id + 1; _ravi["infDetID"] = -1; _ravi["OperNum"] = "---"; _ravi["DepRegion"] = -1; _ravi["ProfCode"] = -1; _ravi["NameKindWork"] = ""; _ravi["KindPay"] = "П"; _ravi["TariffNetNum"] = 1; _ravi["WorkerRate"] = 1; _ravi["ItemCTN"] = -1; _ravi["PreparTimeCTN"] = -1; _ravi["ItemPayNorm"] = -1; _ravi["PreparTimePayNorm"] = -1; _ravi["Valuation"] = -1; _ravi["ValPreparTime"] = -1; _ravi["CoeffCTN"] = -1; _ravi["DocNum"] = "---"; _ravi["Date"] = "01.01.1980 00:00:00"; _ravi["TaskNumber"] = "---"; dt.Rows.InsertAt(_ravi, id); } } DataTable dataTableForTariff = new DataTable(); dataTableForTariff = at.ReturnDataTable(" SELECT * FROM infTariff"); for (int i = 0; i < dt.Rows.Count; i++) { string tariffNetNum = dt.Rows[i]["TariffNetNum"].ToString(); string kindPay = dt.Rows[i]["KindPay"].ToString(); string workerRate = dt.Rows[i]["WorkerRate"].ToString(); double coef = (double.Parse(dt.Rows[i]["CoeffCTN"].ToString()) == 0) ? 1 : double.Parse(dt.Rows[i]["CoeffCTN"].ToString()); double itemPayNorm = double.Parse(dt.Rows[i]["ItemPayNorm"].ToString()); if (itemPayNorm == 0.4051) { } var results = from DataRow myRow in dataTableForTariff.Rows where (myRow["TariffNetNum"].ToString() == tariffNetNum) && (myRow["KindPay"].ToString() == kindPay) && (myRow["WorkerRate"].ToString() == workerRate) select myRow; double currTarif; try { currTarif = double.Parse(results.CopyToDataTable().Rows[0]["HourCost"].ToString()); } catch { currTarif = 0; } double preparTimePayNorm = double.Parse(dt.Rows[i]["PreparTimePayNorm"].ToString()); string g = (itemPayNorm * currTarif).ToString("N" + Properties.Settings.Default.RoundNum); dt.Rows[i]["Valuation"] = (itemPayNorm * currTarif).ToString("N" + Properties.Settings.Default.RoundNum); dt.Rows[i]["ValPreparTime"] = (preparTimePayNorm * currTarif).ToString("N" + Properties.Settings.Default.RoundNum); dt.Rows[i]["DocNum"] = textBoxDataPrikaz.Text; } at.deleteFromDB("CREATE TABLE [LabourNorm]([ID] INT NOT NULL IDENTITY (1,1),[infDetID] INT NOT NULL,[OperNum] NVARCHAR(100),[DepRegion] FLOAT NOT NULL,[ProfCode] INT NOT NULL,[NameKindWork] NVARCHAR(50),[KindPay] NVARCHAR(1) NOT NULL,[TariffNetNum] TINYINT NOT NULL,[WorkerRate] FLOAT NOT NULL,[ItemCTN] FLOAT NOT NULL,[PreparTimeCTN] FLOAT NOT NULL,[ItemPayNorm] FLOAT NOT NULL,[PreparTimePayNorm] FLOAT NOT NULL,[Valuation] FLOAT NOT NULL,[ValPreparTime] FLOAT,[CoeffCTN] FLOAT NOT NULL,[DocNum] NVARCHAR(100),[Date] DATETIME NOT NULL,[TaskNumber] NVARCHAR(30));"); //очишаем таблицу перед записью String connString = Properties.Settings.Default.TrudoyomkostDBConnectionString; SqlCeBulkCopy bulkInsert = new SqlCeBulkCopy(connString); bulkInsert.DestinationTableName = "LabourNorm"; bulkInsert.WriteToServer(dt); at.deleteFromDB("DELETE FROM LabourNorm WHERE infDetID ='-1' AND ProfCode = '-1'"); }
/// <summary> /// Inserts a DataTable object using SqlCeBulkCopy /// </summary> /// <param name="bC">The SqlBulkCopy connection.</param> /// <param name="data">The data to insert</param> public static void Insert(SqlCeBulkCopy bC, DataTable data) { bC.DestinationTableName = data.TableName; bC.WriteToServer(data); }
protected override void Execute(CodeActivityContext context) { DataSource dataSource = null; Job job = context.GetValue(this.Job); if (job != null) { dataSource = job.DataSource; } else { WorkerData data = context.GetValue(this.Data); data.ThrowErrorIfNull(this.DisplayName); dataSource = data.Job.DataSource; } bool createTable = context.GetValue(this.CreateTable); DataTable table = context.GetValue(this.Table); string connectionStringKeyName = context.GetValue(this.ConnectionStringKeyName); string tableName = context.GetValue(this.TableName); string specificColumnTypes = context.GetValue(this.SpecificColumnTypes); int timeOut = context.GetValue(this.TimeOut); if (timeOut == 0) { timeOut = 5;//default } int batchSize = context.GetValue(this.BatchSize); if (batchSize == 0) { batchSize = 5000;//default } IdpeKey connectionStringKey = null; if (string.IsNullOrEmpty(connectionStringKeyName)) { //default is repository database connectionStringKey = new IdpeKey(); connectionStringKey.Name = "cs"; connectionStringKey.Type = (int)Information.EyediaCoreConfigurationSection.Database.DatabaseType.GetSreType(); connectionStringKey.Value = ConfigurationManager.ConnectionStrings[Constants.ConnectionStringName].ToString(); } else { connectionStringKey = dataSource.Keys.GetKey(connectionStringKeyName); if (connectionStringKey == null) { throw new Exception(string.Format("Can not load data table, the connection string was null! Connection string key name was '{0}'" , connectionStringKeyName)); } } try { #region Bulk Insert bool keepNulls = true; SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions(); if (keepNulls) { options = options |= SqlCeBulkCopyOptions.KeepNulls; } DatabaseTypes databaseType = connectionStringKey.GetDatabaseType(); if (databaseType == DatabaseTypes.SqlServer) { using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionStringKey.Value)) { bulkCopy.BulkCopyTimeout = timeOut * 60; bulkCopy.BatchSize = batchSize; for (int c = 0; c < table.Columns.Count; c++) { bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(table.Columns[c].ColumnName, table.Columns[c].ColumnName)); } bulkCopy.DestinationTableName = tableName; bulkCopy.WriteToServer(table); } } else { if (createTable) { SqlCeTableCreator.Create(table, new System.Data.SqlServerCe.SqlCeConnection(connectionStringKey.Value), tableName, specificColumnTypes); } using (SqlCeBulkCopy bulkCopy = new SqlCeBulkCopy(connectionStringKey.Value, options)) { bulkCopy.BulkCopyTimeout = timeOut * 60; bulkCopy.BatchSize = batchSize; for (int c = 0; c < table.Columns.Count; c++) { bulkCopy.ColumnMappings.Add(new SqlCeBulkCopyColumnMapping(table.Columns[c].ColumnName, table.Columns[c].ColumnName)); } bulkCopy.DestinationTableName = tableName; bulkCopy.WriteToServer(table); } } #endregion Bulk Insert } catch (Exception ex) { context.SetValue(ErrorMessage, ex.Message); } }
static void CreateAllocationBlocks(TrialDataContext context) { var participants = context.Database.SqlQuery(typeof(OldParticipant), string.Format( "select Id,CentreId,WasEnvelopeRandomised,BlockNumber,BlockSize, case when IsMale = 1 then case when AdmissionWeight< {0} then {2} when AdmissionWeight >={1} then {3} else {4} end else case when AdmissionWeight< {0} then {5} when AdmissionWeight >={1} then {6} else {7} end end as Strata from Participants order by CentreId, BlockNumber, Id", Engine.BlockWeight1, Engine.BlockWeight2, (int)RandomisationStrata.SmallestWeightMale, (int)RandomisationStrata.TopWeightMale, (int)RandomisationStrata.MidWeightMale, (int)RandomisationStrata.SmallestWeightFemale, (int)RandomisationStrata.TopWeightFemale, (int)RandomisationStrata.MidWeightFemale)).Cast <OldParticipant>(); const string updatePreface = "Update Participants set AllocationBlockId = Case Id"; const string updateSufix = " else AllocationBlockId end;"; const int maxArgs = 64; List <StringBuilder> participantUpdate = new List <StringBuilder>(); StringBuilder currentUpdate = new StringBuilder(updatePreface); participantUpdate.Add(currentUpdate); List <AllocationBlock> blocksToInsert = new List <AllocationBlock>(); var now = DateTime.Now; int currentCentreId = 0; int nextId = 0; int argCount = 0; IDictionary <int, RandomisationStrata> blockNumberLookup = null; foreach (var g in participants.GroupBy(p => new { p.CentreId, p.BlockNumber })) { if (g.Key.CentreId != currentCentreId) { currentCentreId = g.Key.CentreId; nextId = (currentCentreId == 1) ?EnvelopeDetails.MaxEnvelopeNumber + 1 :currentCentreId; } //if envelope randomised, use the allocationGroupId for the correctly assigned group //otherwise, keep the allocationGroupId for the goup with the highest number IEnumerable <IGrouping <int, OldParticipant> > forReallocation = g.GroupBy(p => p.Strata).ToList(); if (currentCentreId == 1 && g.Key.BlockNumber.HasValue) { var currentAlloc = new AllocationBlock { Id = g.Key.BlockNumber.Value, AllocationGroup = AllocationGroups.India2Arm, RecordLastModified = now }; blocksToInsert.Add(currentAlloc); if (g.Any(p => p.WasEnvelopeRandomised)) { (blockNumberLookup ?? (blockNumberLookup = EnvelopeDetails.GetStrataByBlockNumber())).TryGetValue(g.Key.BlockNumber.Value, out RandomisationStrata definedStrata); int strataVal = (int)definedStrata; var correctlyAssigned = forReallocation.FirstOrDefault(r => r.Key == strataVal); if (correctlyAssigned == null) { blocksToInsert.RemoveAt(blocksToInsert.Count - 1); } else { currentAlloc.GroupRepeats = (byte)((correctlyAssigned.Count() - 1) / 2 + 1); currentAlloc.RandomisationCategory = definedStrata; forReallocation = forReallocation.Where(r => r != correctlyAssigned); } } else { int maxCount = 0; int largestRandCat = 0; foreach (var r in forReallocation) { int grpCount = r.Count(); if (grpCount > maxCount) { maxCount = grpCount; largestRandCat = r.Key; } } currentAlloc.RandomisationCategory = (RandomisationStrata)largestRandCat; currentAlloc.GroupRepeats = (byte)((maxCount - 1) / 2 + 1); forReallocation = forReallocation.Where(r => r.Key != largestRandCat); System.Diagnostics.Debug.Assert(currentAlloc.Id != 1); } } foreach (var s in forReallocation) { string nextIdStr = nextId.ToString(); blocksToInsert.Add(new AllocationBlock { Id = nextId++, AllocationGroup = AllocationGroups.India2Arm, GroupRepeats = (byte)((s.Count() - 1) / 2 + 1), RandomisationCategory = (RandomisationStrata)s.Key, RecordLastModified = now }); argCount += s.Count(); if (argCount > maxArgs) { argCount = 0; currentUpdate.Append(updateSufix); currentUpdate = new StringBuilder(updatePreface); participantUpdate.Add(currentUpdate); } currentUpdate.AppendFormat(string.Join("", s.Select(p => " When " + p.Id + " Then " + nextIdStr))); } } using (SqlCeBulkCopy bc = new SqlCeBulkCopy((System.Data.SqlServerCe.SqlCeConnection)context.Database.Connection)) { bc.DestinationTableName = "AllocationBlocks"; bc.WriteToServer(blocksToInsert); } if (argCount == 0) { participantUpdate.RemoveAt(participantUpdate.Count - 1); } else { currentUpdate.Append(updateSufix); } foreach (var sb in participantUpdate) { context.Database.ExecuteSqlCommand(sb.ToString()); } context.Database.ExecuteSqlCommand("update Participants set RecordLastModified = GetDate()"); context.Database.ExecuteSqlCommand("Update Participants set AllocationBlockId = BlockNumber where AllocationBlockId is null;"); }
public static void InsertFromTNTables() { InfDetDataTable.Clear(); using (var newLocalDb = new TrudoyomkostDBContext(Properties.Settings.Default.TrudoyomkostDBConnectionString)) { LinqQueryForTrudoyomkost.FillDictDetNumID(newLocalDb, ref DicDetNumAndId); LinqQueryForTrudoyomkost.FillDictDepIDCode(newLocalDb, ref DicDepCodeAndId); LinqQueryForTrudoyomkost.FillDictInfProfession(newLocalDb, ref DictInfProfession); } int whereOperUseId = 1; foreach (var item in Tn148DataTable) { if (DicDetNumAndId.ContainsKey(item.NDET)) { LabourNorm itemlabourNorm = FillItemLabourNorm(item); itemlabourNorm.ID = whereOperUseId; FilltmpLabourNormRow(itemlabourNorm); WhereOperationUse itemWhereOperUse = FillItemWhereOperationUse(item); itemWhereOperUse.LabourNormID = whereOperUseId; FilltmpWhereOperUseRow(itemWhereOperUse); ListAllLabourNorm.Add(item.NDET); whereOperUseId++; } } using (var newLocalDb = new TrudoyomkostDBContext(Properties.Settings.Default.TrudoyomkostDBConnectionString)) { LinqQueryForTrudoyomkost.FillDictDetNumID(newLocalDb, ref DicDetNumAndId); } DicCheckRepeatTN.Clear(); foreach (var item in Tn158DataTable) { if (DicDetNumAndId.ContainsKey(item.NDET)) { LabourNorm itemlabourNorm = FillItemLabourNorm(item); itemlabourNorm.ID = whereOperUseId; FilltmpLabourNormRow(itemlabourNorm); WhereOperationUse itemWhereOperUse = FillItemWhereOperationUse(item); itemWhereOperUse.LabourNormID = whereOperUseId; FilltmpWhereOperUseRow(itemWhereOperUse); ListAllLabourNorm.Add(item.NDET); whereOperUseId++; } } Properties.Settings.Default.Save(); SqlCeBulkCopy bulkInsert = new SqlCeBulkCopy(connString); DataTable tmptbLabourNorm = LabourNormDataTable; DataTable tmpWhereOperUse = WhereOperationUseDataTable; if (tmptbLabourNorm.Rows.Count > 0) { bulkInsert.DestinationTableName = "LabourNorm"; bulkInsert.WriteToServer(tmptbLabourNorm); } if (tmpWhereOperUse.Rows.Count > 0) { bulkInsert.DestinationTableName = "whereOperationUse"; bulkInsert.WriteToServer(tmpWhereOperUse); } LabourNormDataTable.Clear(); WhereOperationUseDataTable.Clear(); bulkInsert.Close(); }
/// <summary> /// Imports the data into SQL CE. This is identical to <see cref="ImportIntoSqlServer" /> except for the names of the classes /// SqlCeConnection/SqlConnection, SqlCeBulkCopy/SqlBulkCopy, SqlCeBulkCopyOptions/SqlBulkCopyOptions /// </summary> /// <param name="backupFile">The backup file.</param> private static void ImportIntoSqlCe(IBackupFile backupFile) { using (var ds = GenerateDataSet(backupFile)) { backupFile.SchemaVersion = GetDataSchemaVersion(ds); using (var cn = new SqlCeConnection(backupFile.ConnectionString)) { cn.Open(); using (var tran = cn.BeginTransaction()) { if (backupFile.SchemaVersion == GalleryDataSchemaVersion.V2_6_0) { Migrate26Controller.UpgradeData(ds, GalleryDataSchemaVersion.V3_2_1, backupFile.GalleryDataStore, cn, tran); } if (backupFile.SchemaVersion < GalleryDataSchemaVersion.V4_0_0) { MigrateController.UpgradeToCurrentSchema(ds); } DropSelfReferencingAlbumConstraint(backupFile, cn, tran); ClearData(backupFile, cn, tran); if (backupFile.IncludeMembershipData) { // SqlBulkCopy requires SQL permissions equivalent to that provided in the db_ddladmin or db_owner roles. using (var bulkCopy = new SqlCeBulkCopy(cn, SqlCeBulkCopyOptions.KeepIdentity, tran)) { bulkCopy.BulkCopyTimeout = 3600; // 1 hour foreach (var tableName in backupFile.MembershipTables) { bulkCopy.ColumnMappings.Clear(); bulkCopy.DestinationTableName = Utils.GetSqlName(tableName, backupFile.GalleryDataStore, "dbo"); foreach (DataColumn dc in ds.Tables[tableName].Columns) { bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName); } // Write from the source to the destination. try { bulkCopy.WriteToServer(ds.Tables[tableName]); } catch (Exception ex) { // Add a little info to exception and re-throw. if (!ex.Data.Contains("SQL Bulk copy error")) { ex.Data.Add("SQL Bulk copy error", String.Format(CultureInfo.CurrentCulture, "Error occurred while importing table {0}.", tableName)); } throw; } } } } if (backupFile.IncludeGalleryData) { // Tables to skip: Event, MediaQueue, Synchronize // SqlBulkCopy requires SQL permissions equivalent to that provided in the db_ddladmin or db_owner roles. using (var bulkCopy = new SqlCeBulkCopy(cn, SqlCeBulkCopyOptions.KeepIdentity, tran)) { bulkCopy.BulkCopyTimeout = 3600; // 1 hour foreach (var tableName in backupFile.GalleryTableNames) { if (backupFile.SchemaVersion == GalleryDataSchemaVersion.V2_6_0 && Array.IndexOf(GalleryTableNamesToIgnoreDuringUpgrade, tableName) >= 0) { continue; // Don't import certain tables when upgrading because we want to keep the 3.0 data } bulkCopy.DestinationTableName = Utils.GetSqlName(tableName, backupFile.GalleryDataStore); // Don't need to map the columns like we did in the membership section because it works without it. // Write from the source to the destination. try { bulkCopy.WriteToServer(ds.Tables[tableName]); } catch (Exception ex) { // Add a little info to exception and re-throw. if (!ex.Data.Contains("SQL Bulk copy error")) { ex.Data.Add("SQL Bulk copy error", String.Format(CultureInfo.CurrentCulture, "Error occurred while importing table {0}.", tableName)); } throw; } } } } if (backupFile.SchemaVersion == GalleryDataSchemaVersion.V2_6_0) { Migrate26Controller.AddMissingMeta(backupFile.GalleryDataStore, cn, tran); } RestoreSelfReferencingAlbumConstraint(backupFile, cn, tran); tran.Commit(); } } } CompactSqlCeDb(backupFile); }
public void CriarListasSorteio(List <string> empreendimentos, IDataReader dataReader, Action <string> updateStatus, Action <int> updateProgress) { int listaAtual = 0; int totalListas = 9 * empreendimentos.Count(); updateStatus("Iniciando importação..."); /* Exclui os dados anteriores. */ ExecuteNonQuery("DELETE FROM CANDIDATO_LISTA"); ExecuteNonQuery("DELETE FROM CANDIDATO"); ExecuteNonQuery("DELETE FROM LISTA"); /* Copia os candidatos da lista de importação. */ updateStatus("Importando candidatos."); SqlCeBulkCopy bulkCopy = new SqlCeBulkCopy(Connection, Transaction); bulkCopy.DestinationTableName = "CANDIDATO"; bulkCopy.ColumnMappings.Add(new SqlCeBulkCopyColumnMapping(0, "CPF")); bulkCopy.ColumnMappings.Add(new SqlCeBulkCopyColumnMapping(1, "NOME")); bulkCopy.ColumnMappings.Add(new SqlCeBulkCopyColumnMapping(2, "QUANTIDADE_CRITERIOS")); bulkCopy.ColumnMappings.Add(new SqlCeBulkCopyColumnMapping(3, "LISTA_DEFICIENTES")); bulkCopy.ColumnMappings.Add(new SqlCeBulkCopyColumnMapping(4, "LISTA_IDOSOS")); bulkCopy.ColumnMappings.Add(new SqlCeBulkCopyColumnMapping(5, "LISTA_INDICADOS")); bulkCopy.WriteToServer(dataReader); /* Gera as listas de sorteio para os empreendimentos. */ int qtdEmpreendimentos = empreendimentos.Count(); int incrementoOrdem = 1; int idUltimaLista; foreach (string emp in empreendimentos) { updateStatus($"Gerando lista {++listaAtual} de {totalListas}."); updateProgress((int)((listaAtual / (double)totalListas) * 100)); idUltimaLista = CriarListaSorteio(emp, "Deficientes", 0, incrementoOrdem, qtdEmpreendimentos); ExecuteNonQuery($"INSERT INTO CANDIDATO_LISTA(ID_LISTA, ID_CANDIDATO) SELECT {idUltimaLista}, ID_CANDIDATO FROM CANDIDATO WHERE LISTA_DEFICIENTES = 1"); ClassificarListaSorteioSimples(idUltimaLista); updateStatus($"Gerando lista {++listaAtual} de {totalListas}."); updateProgress((int)((listaAtual / (double)totalListas) * 100)); idUltimaLista = CriarListaSorteio(emp, "Idosos", 1, incrementoOrdem, qtdEmpreendimentos); ExecuteNonQuery($"INSERT INTO CANDIDATO_LISTA(ID_LISTA, ID_CANDIDATO) SELECT {idUltimaLista}, ID_CANDIDATO FROM CANDIDATO WHERE LISTA_IDOSOS = 1"); ClassificarListaSorteioSimples(idUltimaLista); updateStatus($"Gerando lista {++listaAtual} de {totalListas}."); updateProgress((int)((listaAtual / (double)totalListas) * 100)); idUltimaLista = CriarListaSorteio(emp, "Indicados", 2, incrementoOrdem, qtdEmpreendimentos); ExecuteNonQuery($"INSERT INTO CANDIDATO_LISTA(ID_LISTA, ID_CANDIDATO) SELECT {idUltimaLista}, ID_CANDIDATO FROM CANDIDATO WHERE LISTA_INDICADOS = 1"); ClassificarListaSorteioSimples(idUltimaLista); updateStatus($"Gerando lista {++listaAtual} de {totalListas}."); updateProgress((int)((listaAtual / (double)totalListas) * 100)); idUltimaLista = CriarListaSorteio(emp, "Geral I", 3, incrementoOrdem, qtdEmpreendimentos); ExecuteNonQuery($"INSERT INTO CANDIDATO_LISTA(ID_LISTA, ID_CANDIDATO) SELECT {idUltimaLista}, ID_CANDIDATO FROM CANDIDATO"); ClassificarListaSorteioComposto(idUltimaLista); updateStatus($"Gerando lista {++listaAtual} de {totalListas}."); updateProgress((int)((listaAtual / (double)totalListas) * 100)); idUltimaLista = CriarListaSorteio(emp, "Geral II", 4, incrementoOrdem, qtdEmpreendimentos); ExecuteNonQuery($"INSERT INTO CANDIDATO_LISTA(ID_LISTA, ID_CANDIDATO) SELECT {idUltimaLista}, ID_CANDIDATO FROM CANDIDATO WHERE QUANTIDADE_CRITERIOS < 5"); ClassificarListaSorteioConstante(idUltimaLista); incrementoOrdem++; } /* Gera as listas de sorteio de reserva para os empreendimentos. */ incrementoOrdem = 1; foreach (string emp in empreendimentos) { updateStatus($"Gerando lista {++listaAtual} de {totalListas}."); updateProgress((int)((listaAtual / (double)totalListas) * 100)); idUltimaLista = CriarListaSorteio(emp, "Deficientes (Reserva)", 5, incrementoOrdem, qtdEmpreendimentos); ExecuteNonQuery($"INSERT INTO CANDIDATO_LISTA(ID_LISTA, ID_CANDIDATO) SELECT {idUltimaLista}, ID_CANDIDATO FROM CANDIDATO WHERE LISTA_DEFICIENTES = 1"); ClassificarListaSorteioConstante(idUltimaLista); updateStatus($"Gerando lista {++listaAtual} de {totalListas}."); updateProgress((int)((listaAtual / (double)totalListas) * 100)); idUltimaLista = CriarListaSorteio(emp, "Idosos (Reserva)", 6, incrementoOrdem, qtdEmpreendimentos); ExecuteNonQuery($"INSERT INTO CANDIDATO_LISTA(ID_LISTA, ID_CANDIDATO) SELECT {idUltimaLista}, ID_CANDIDATO FROM CANDIDATO WHERE LISTA_IDOSOS = 1"); ClassificarListaSorteioConstante(idUltimaLista); updateStatus($"Gerando lista {++listaAtual} de {totalListas}."); updateProgress((int)((listaAtual / (double)totalListas) * 100)); idUltimaLista = CriarListaSorteio(emp, "Indicados (Reserva)", 7, incrementoOrdem, qtdEmpreendimentos); ExecuteNonQuery($@"INSERT INTO CANDIDATO_LISTA(ID_LISTA, ID_CANDIDATO) SELECT {idUltimaLista}, ID_CANDIDATO FROM CANDIDATO WHERE LISTA_INDICADOS = 1"); ClassificarListaSorteioConstante(idUltimaLista); updateStatus($"Gerando lista {++listaAtual} de {totalListas}."); updateProgress((int)((listaAtual / (double)totalListas) * 100)); idUltimaLista = CriarListaSorteio(emp, "Geral (Reserva)", 8, incrementoOrdem, qtdEmpreendimentos); ExecuteNonQuery($"INSERT INTO CANDIDATO_LISTA(ID_LISTA, ID_CANDIDATO) SELECT {idUltimaLista}, ID_CANDIDATO FROM CANDIDATO"); ClassificarListaSorteioConstante(idUltimaLista); incrementoOrdem++; } updateStatus("Finalizando importação."); }
public void DoBulkCopy(DataTable table, string tableName) { using (SqlCeBulkCopy bc = new SqlCeBulkCopy(main.connection)) { bc.DestinationTableName = tableName; bc.WriteToServer(table); } }
public void AddDoubleItems() { this.Enabled = false; var labourNormlist = LinqQueryForTrudoyomkost.FillLabournNormForDet(_destinationDetID); foreach (var item in labourNormlist) { var whereOperUselstdel = LinqQueryForTrudoyomkost.FillWhereOperationUseList(item.ID); if (whereOperUselstdel.Count() > 0) { foreach (var applydel in whereOperUselstdel) { FillTrudoyomkostDB.WhereOperationUseList.Remove(applydel); } } FillTrudoyomkostDB.LabourNormTableAdapter.DeleteQuery(item.ID); FillTrudoyomkostDB.WhereOperationUseTableAdapter.DeleteQuery(item.ID); FillTrudoyomkostDB.LabourNormList.Remove(item); } labourNormlist = LinqQueryForTrudoyomkost.FillLabournNormForDet(_sourceDetID); using (var currentContext = new TrudoyomkostDBContext(Properties.Settings.Default.TrudoyomkostDBConnectionString)) { int Id = 0; int whereOperUseId = FillTrudoyomkostDB.WhereOperationUseList.Last().Id; Id = currentContext.LabourNorm.ToList().Max().ID; foreach (var item in labourNormlist) { var whereOperUselst = LinqQueryForTrudoyomkost.FillWhereOperationUseList(item.ID); if (whereOperUselst.Count() > 0) { Id++; //FillTrudoyomkostDB.LabourNormTableAdapter.InsertQuery(_destinationDetID, item.OperNum, item.DepRegion, item.ProfCode, item.NameKindWork, // item.TariffNetNum, item.KindPay, item.WorkerRate, item.ItemCTN, item.PreparTimeCTN, //item.ItemPayNorm, item.PreparTimePayNorm, item.Valuation, item.ValPreparTime, item.CoeffCTN, item.DocNum, item.Date, item.TaskNumber); FillTrudoyomkostDB.FilltmpLabourNormRow(new LabourNorm(Id, _destinationDetID, item.OperNum, item.DepRegion, item.ProfCode, item.NameKindWork, item.TariffNetNum, item.KindPay, item.WorkerRate, item.ItemCTN, item.PreparTimeCTN, item.ItemPayNorm, item.PreparTimePayNorm, item.Valuation, item.ValPreparTime, item.CoeffCTN, item.DocNum, DateTime.Now, item.TaskNumber)); FillTrudoyomkostDB.LabourNormList.Add(new LabourNorm(Id, _destinationDetID, item.OperNum, item.DepRegion, item.ProfCode, item.NameKindWork, item.TariffNetNum, item.KindPay, item.WorkerRate, item.ItemCTN, item.PreparTimeCTN, item.ItemPayNorm, item.PreparTimePayNorm, item.Valuation, item.ValPreparTime, item.CoeffCTN, item.DocNum, DateTime.Now, item.TaskNumber)); //currentContext.LabourNorm.InsertOnSubmit(new LabourNorm(Id, _destinationDetID, item.OperNum, item.DepRegion, item.ProfCode, // item.NameKindWork, item.TariffNetNum, item.KindPay, item.WorkerRate, item.ItemCTN, item.PreparTimeCTN, item.ItemPayNorm, item.PreparTimePayNorm, // item.Valuation, item.ValPreparTime, item.CoeffCTN, item.DocNum, DateTime.Now, item.TaskNumber)); foreach (var applyItem in whereOperUselst) { whereOperUseId++; FillTrudoyomkostDB.FilltmpWhereOperUseRow(new WhereOperationUse(Id, applyItem.SeriaFrom, applyItem.SeriaTo, applyItem.InfProductsChipher)); FillTrudoyomkostDB.WhereOperationUseList.Add(new WhereOperationUse(Id, applyItem.SeriaFrom, applyItem.SeriaTo, applyItem.InfProductsChipher, whereOperUseId)); //currentContext.WhereOperationUse.InsertOnSubmit(new WhereOperationUse(Id, applyItem.SeriaFrom, applyItem.SeriaTo, applyItem.InfProductsChipher,whereOperUseId)); //FillTrudoyomkostDB.WhereOperationUseTableAdapter.InsertQuery(Id, applyItem.SeriaFrom, applyItem.SeriaTo, applyItem.InfProductsChipher); } } //currentContext.SubmitChanges(); } DataTable tmpWhereOperUse = FillTrudoyomkostDB.WhereOperationUseDataTable; DataTable tmpLabourNorm = FillTrudoyomkostDB.LabourNormDataTable; SqlCeBulkCopy bulkInsert = new SqlCeBulkCopy(Properties.Settings.Default.TrudoyomkostDBConnectionString); if (tmpWhereOperUse.Rows.Count > 0) { bulkInsert.DestinationTableName = "whereOperationUse"; bulkInsert.WriteToServer(tmpWhereOperUse); } if (tmpLabourNorm.Rows.Count > 0) { bulkInsert.DestinationTableName = "LabourNorm"; bulkInsert.WriteToServer(tmpLabourNorm); } FillTrudoyomkostDB.LabourNormDataTable.Clear(); FillTrudoyomkostDB.WhereOperationUseDataTable.Clear(); bulkInsert.Close(); } this.Enabled = true; }
private void DoBulkCopy(SqlCeConnection connection, IDataReader reader) { SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions(); using (SqlCeBulkCopy bc = new SqlCeBulkCopy(connection, options)) { bc.DestinationTableName = "Coverage"; bc.WriteToServer(reader); } }
/// <summary> /// Imports the data into SQL CE. This is identical to <see cref="ImportIntoSqlServer" /> except for the names of the classes /// SqlCeConnection/SqlConnection, SqlCeBulkCopy/SqlBulkCopy, SqlCeBulkCopyOptions/SqlBulkCopyOptions /// </summary> /// <param name="backupFile">The backup file.</param> private static void ImportIntoSqlCe(IBackupFile backupFile) { using (var ds = GenerateDataSet(backupFile)) { backupFile.SchemaVersion = GetDataSchemaVersion(ds); using (var cn = new SqlCeConnection(backupFile.ConnectionString)) { cn.Open(); using (var tran = cn.BeginTransaction()) { if (backupFile.SchemaVersion == GalleryDataSchemaVersion.V2_6_0) { Migrate26Controller.UpgradeData(ds, GalleryDb.DataSchemaVersion, backupFile.GalleryDataStore, cn, tran); } DropSelfReferencingAlbumConstraint(backupFile, cn, tran); ClearData(backupFile, cn, tran); if (backupFile.IncludeMembershipData) { // SqlBulkCopy requires SQL permissions equivalent to that provided in the db_ddladmin or db_owner roles. using (var bulkCopy = new SqlCeBulkCopy(cn, SqlCeBulkCopyOptions.KeepIdentity, tran)) { bulkCopy.BulkCopyTimeout = 3600; // 1 hour foreach (var tableName in backupFile.MembershipTables) { bulkCopy.ColumnMappings.Clear(); bulkCopy.DestinationTableName = Utils.GetSqlName(tableName, backupFile.GalleryDataStore, "dbo"); foreach (DataColumn dc in ds.Tables[tableName].Columns) { bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName); } // Write from the source to the destination. try { bulkCopy.WriteToServer(ds.Tables[tableName]); } catch (Exception ex) { // Add a little info to exception and re-throw. if (!ex.Data.Contains("SQL Bulk copy error")) { ex.Data.Add("SQL Bulk copy error", String.Format(CultureInfo.CurrentCulture, "Error occurred while importing table {0}.", tableName)); } throw; } } } } if (backupFile.IncludeGalleryData) { // Tables to skip: Event, MediaQueue, Synchronize // SqlBulkCopy requires SQL permissions equivalent to that provided in the db_ddladmin or db_owner roles. using (var bulkCopy = new SqlCeBulkCopy(cn, SqlCeBulkCopyOptions.KeepIdentity, tran)) { bulkCopy.BulkCopyTimeout = 3600; // 1 hour foreach (var tableName in backupFile.GalleryTableNames) { if (backupFile.SchemaVersion == GalleryDataSchemaVersion.V2_6_0 && Array.IndexOf(GalleryTableNamesToIgnoreDuringUpgrade, tableName) >= 0) continue; // Don't import certain tables when upgrading because we want to keep the 3.0 data bulkCopy.DestinationTableName = Utils.GetSqlName(tableName, backupFile.GalleryDataStore); // Don't need to map the columns like we did in the membership section because it works without it. // Write from the source to the destination. try { bulkCopy.WriteToServer(ds.Tables[tableName]); } catch (Exception ex) { // Add a little info to exception and re-throw. if (!ex.Data.Contains("SQL Bulk copy error")) { ex.Data.Add("SQL Bulk copy error", String.Format(CultureInfo.CurrentCulture, "Error occurred while importing table {0}.", tableName)); } throw; } } } } if (backupFile.SchemaVersion == GalleryDataSchemaVersion.V2_6_0) { Migrate26Controller.AddMissingMeta(backupFile.GalleryDataStore, cn, tran); } RestoreSelfReferencingAlbumConstraint(backupFile, cn, tran); tran.Commit(); } } } CompactSqlCeDb(backupFile); }