public virtual bool CopyTable(DataAccess Source, DataAccess Dest, CopyContext Ctx, CopyDisplay CD)
{
int count = 0;
QueryHelper QHS = Dest.GetQueryHelper();
try {
DataTable Cols = Dest.SQLRunner("sp_columns " + table + ",'" + Dest.GetSys("userdb").ToString() + "'");
if (Cols.Rows.Count == 0)
{
Cols = Dest.SQLRunner("sp_columns " + table + ",'dbo'");
}
if (ClearDestAtStart)
{
Dest.SQLRunner("DELETE FROM " + table);
}
if (dontCopy)
{
CD.Comment("Table " + table + " skipped");
applied = true;
return(true);
}
if (skipIfNotEmpty)
{
if (Dest.RUN_SELECT_COUNT(table, null, false) > 0)
{
CD.Comment("Table " + table + " skipped cause destination is not empty");
applied = true;
return(true);
}
}
Comment(Dest, Ctx, CD);
int not_to_copy = 0;
int nullskipped = 0;
int nrows = Source.RUN_SELECT_COUNT(table, filter, false);
CD.Start(filter == null? table: table + " where " + filter, "copy", nrows);
string insert = "INSERT INTO " + table + " VALUES(";//(
string s = "";
string err;
DataTable T = Dest.CreateTableByName(table, "*");
string col = QueryCreator.ColumnNameList(T);
DataAccess.DataRowReader DRR = new DataAccess.DataRowReader(Source, table, col, null, filter);
foreach (DataRow row in DRR)
{
CD.Update(1);
if (!IsToCopy(row))
{
not_to_copy++; continue;
}
if (SkipExtNulls(row, Ctx))
{
nullskipped++; continue;
}
Translate(Ctx, row);
if (skipduplicates)
{
int num = Dest.RUN_SELECT_COUNT(table, QHS.CmpKey(row), false);
if (num > 0)
{
continue;
}
}
count++;
string values = GetSQLDataValues(row, Cols);
s += insert + values;
if (count == 10)
{
Dest.SQLRunner(s, 0, out err);
if (err != null)
{
QueryCreator.ShowError(null, "Errore", err);
StreamWriter fsw = new StreamWriter("temp.sql", false, Encoding.Default);
fsw.Write(s.ToString());
fsw.Close();
MessageBox.Show("Errore durante la copia della tabella " + table + "\r\nLo script lanciato si trova nel file 'temp.sql'");
CD.Stop(false);
return(false);
}
s = "";
count = 0;
}
}
if (s != "")
{
Dest.SQLRunner(s, 0, out err);
count = 0;
if (err != null)
{
QueryCreator.ShowError(null, "Errore", err);
StreamWriter fsw = new StreamWriter("temp.sql", false, Encoding.Default);
fsw.Write(s.ToString());
fsw.Close();
MessageBox.Show("Errore durante la copia di " + table + "\r\nLo script lanciato si trova nel file 'temp.sql'");
CD.Stop(false);
return(false);
}
s = "";
}
CD.Stop(true);
if (not_to_copy > 0 || nullskipped > 0)
{
CD.Comment(">>Not to copy:" + not_to_copy.ToString() + " NullSkipped:" + nullskipped.ToString());
}
applied = true;
return(true);
}
catch (Exception E) {
QueryCreator.ShowException(E);
CD.Stop(false);
return(false);
}
}
public override bool CopyTable(DataAccess Source, DataAccess Dest, CopyContext Ctx, CopyDisplay CD)
{
applied = false;
int count = 0;
QueryHelper QHS = Dest.GetQueryHelper();
try {
DataTable Cols = Dest.SQLRunner("sp_columns " + table + ",'" + Dest.GetSys("userdb").ToString() + "'");
if (Cols.Rows.Count == 0)
{
Cols = Dest.SQLRunner("sp_columns " + table + ",'dbo'");
}
Comment(Dest, Ctx, CD);
int nrows = Source.RUN_SELECT_COUNT(table, filter, false);
CD.Start(filter == null ? table : table + " where " + filter, "copy", nrows);
string insert = "INSERT INTO surplus VALUES(";//(
string s = "";
string err;
DataTable T = Dest.CreateTableByName(table, "*");
string col = QueryCreator.ColumnNameList(T);
DataAccess.DataRowReader DRR = new DataAccess.DataRowReader(Source, "surplus", col, null, filter);
foreach (DataRow row in DRR)
{
CD.Update(1);
if (!IsToCopy(row))
{
continue; // copia quelle che NON sono da inserire
}
Translate(Ctx, row);
string delete = "";
DataTable T2 = Dest.RUN_SELECT("surplus", "*", null, QHS.CmpEq("ayear", row["ayear"]), null, false);
if (T2.Rows.Count > 0)
{
DataRow RR = T2.Rows[0];
foreach (DataColumn CR in T2.Columns)
{
string field = CR.ColumnName;
if (CR.DataType == typeof(decimal))
{
if (RR[field] == DBNull.Value)
{
continue;
}
if (row[field] == DBNull.Value)
{
row[field] = RR[field];
}
else
{
row[field] = CfgFn.GetNoNullDecimal(row[field]) + CfgFn.GetNoNullDecimal(RR[field]);
}
}
}
delete = "delete from surplus where " + QHS.CmpEq("ayear", row["ayear"]) + "\n\r";
}
row["locked"] = "S";
count++;
string values = GetSQLDataValues(row, Cols);
s += delete + insert + values;
if (count == 1) //è necessario farlo una riga alla volta
{
Dest.SQLRunner(s, 0, out err);
if (err != null)
{
QueryCreator.ShowError(null, "Errore", err);
StreamWriter fsw = new StreamWriter("temp.sql", false, Encoding.Default);
fsw.Write(s.ToString());
fsw.Close();
MessageBox.Show("Errore durante la copia della tabella " + table + "\r\nLo script lanciato si trova nel file 'temp.sql'");
CD.Stop(false);
return(false);
}
s = "";
count = 0;
}
}
if (s != "")
{
Dest.SQLRunner(s, 0, out err);
count = 0;
if (err != null)
{
QueryCreator.ShowError(null, "Errore", err);
StreamWriter fsw = new StreamWriter("temp.sql", false, Encoding.Default);
fsw.Write(s.ToString());
fsw.Close();
MessageBox.Show("Errore durante la copia di " + table + "\r\nLo script lanciato si trova nel file 'temp.sql'");
CD.Stop(false);
return(false);
}
s = "";
}
CD.Stop(true);
applied = true;
return(true);
}
catch (Exception E) {
QueryCreator.ShowException(E);
CD.Stop(false);
return(false);
}
}