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 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);
}
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);
}
}
/// <summary>
/// Inserts DataTable objects using SqlCeBulkCopy
/// </summary>
/// <param name="bC">The SqlBulkCopy connection.</param>
/// <param name="data">The array of data to insert</param>
public static void MultiInsert(SqlCeBulkCopy bC, DataTable[] data)
{
foreach (DataTable t in data)
{
Insert(bC, t);
}
}
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 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 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();
}
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 writteToBD(DataTable dt3)
{
String connString = Properties.Settings.Default.TrudoyomkostDBConnectionString;
SqlCeBulkCopy bulkInsert = new SqlCeBulkCopy(connString);
bulkInsert.DestinationTableName = "infTariff";
bulkInsert.WriteToServer(dt3);
}
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'");
}
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;
}
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 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();
}
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;");
}
/// <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);
}
/// <summary>
/// Updates a beatmap record if it exists, otherwise inserts it
/// </summary>
private void PopulateBeatmaps()
{
oRAControls.ProgressToolTip.Tag = Language["oRA_PopBeatmaps"];
DataTable beatmapData = DBHelper.CreateBeatmapDataTable();
string[] beatmapFiles = Directory.GetFiles(oRAData.BeatmapDirectory, "*.osu", SearchOption.AllDirectories);
Progress.BeginInvoke((Action)(() => Progress.Maximum = beatmapFiles.Length));
using (SqlCeConnection conn = DBHelper.CreateDBConnection())
{
conn.Open();
//Get the hashes that are currently in the database
DataTable existingRows = DBHelper.GetRecords(conn, "Beatmaps", "*");
//Hashset performance >>>>> List performance
HashSet<string> existingHashes = new HashSet<string>(existingRows.AsEnumerable().Select(row => row.Field<string>("Hash")));
HashSet<string> existingFiles = new HashSet<string>(existingRows.AsEnumerable().Select(row => row.Field<string>("Filename")));
using (SqlCeBulkCopy bC = new SqlCeBulkCopy(conn))
{
foreach (string file in beatmapFiles)
{
string beatmapHash = MD5FromFile(file);
//Check if hash exists in database
if (!existingHashes.Contains(beatmapHash))
{
if (existingFiles.Contains(file))
{
//Remove the old file from the database
DBHelper.DeleteRecords(conn, "Beatmaps", "Filename", file);
}
//Add the new file
beatmapData.Rows.Add(beatmapHash, file);
existingHashes.Add(beatmapHash);
//Increment the progressbar
try
{
Progress.BeginInvoke((Action)(() => Progress.Value += 1));
}
catch
{
//If this occurs, the form has disposed
//We don't want to continue any further
return;
}
//Free memory by pushing the rows
//We don't want to set this too low or we spend more time
//pushing to the database. But we don't want to set it too
//high or user won't have his beatmaps for a long time
if (beatmapData.Rows.Count >= 3000)
{
DBHelper.Insert(bC, beatmapData);
beatmapData.Clear();
}
}
}
//Flush any remaining data
DBHelper.Insert(bC, beatmapData);
beatmapData.Clear();
existingHashes.Clear(); //Final cleanup
//Set the first replay in the replayslist as the current replay
ReplaysList.BeginInvoke((Action)(() =>
{
if (ReplaysList.Nodes.Count > 0 && ReplaysList.SelectedNode == null)
ReplaysList.SelectedNode = ReplaysList.Nodes[0];
}));
}
}
Progress.BeginInvoke((Action)(() => Progress.Value = 0));
oRAControls.ProgressToolTip.Tag = Language["oRA_OperationsCompleted"];
}
private static void BeatmapCreated(object sender, FileSystemEventArgs e)
{
SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions();
options |= SqlCeBulkCopyOptions.KeepNulls;
using (SqlCeConnection conn = DBHelper.CreateDBConnection())
{
conn.Open();
using (SqlCeBulkCopy bC = new SqlCeBulkCopy(conn, options))
{
string beatmapHash = MD5FromFile(e.FullPath);
if (!DBHelper.RecordExists(conn, "Beatmaps", "Hash", beatmapHash))
{
//Remove old record if it exists
if (DBHelper.RecordExists(conn, "Beatmaps", "Filename", e.FullPath))
DBHelper.DeleteRecords(conn, "Beatmaps", "Filename", e.FullPath);
DataTable dT = DBHelper.CreateBeatmapDataTable();
dT.Rows.Add(beatmapHash, e.FullPath);
DBHelper.Insert(bC, dT);
}
}
}
}
private void DoBulkCopy(SqlCeConnection connection, IDataReader reader)
{
SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions();
using (SqlCeBulkCopy bc = new SqlCeBulkCopy(connection, options))
{
bc.DestinationTableName = "Coverage";
bc.WriteToServer(reader);
}
}
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 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'");
}
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();
}
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);
}
}
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>
/// 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);
}
/// <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);
}
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.");
}
