Exemple #1
0
        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);
            }
        }
Exemple #2
0
        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);
            }
        }