private void btnGeneraScript_Click(object sender, EventArgs e) { if (Meta.IsEmpty) { return; } Meta.SaveFormData(); DataAccess Conn = Meta.Conn; DialogResult r = saveFileDialog1.ShowDialog(this); string fileName = saveFileDialog1.FileName; if (r != DialogResult.OK) { return; } DataRow Curr = DS.exportfunction.Rows[0]; string procedurename = Curr["procedurename"].ToString(); DataTable tRep = Conn.CreateTableByName("exportfunction", "*"); Conn.RUN_SELECT_INTO_TABLE(tRep, null, QHS.CmpEq("procedurename", procedurename), null, false); DataTable tRepPar = Conn.CreateTableByName("exportfunctionparam", "*"); Conn.RUN_SELECT_INTO_TABLE(tRepPar, "number", QHS.CmpEq("procedurename", procedurename), null, false); DataAccess.AddExtendedProperty(Conn, tRep); DataAccess.AddExtendedProperty(Conn, tRepPar); DataSet D1 = new DataSet(); D1.Tables.Add(tRep); GeneraSQL.GeneraStrutturaEDati(Conn, D1, fileName, false, UpdateType.insertAndUpdate, DataGenerationType.onlyData, true); D1 = new DataSet(); D1.Tables.Add(tRepPar); GeneraSQL.GeneraStrutturaEDati(Conn, D1, fileName, true, UpdateType.insertAndUpdate, DataGenerationType.onlyData, true); if (chkScriptSP.Checked) { StreamWriter sw = new StreamWriter(fileName, true); sw.Write(GeneraSQL.scriptOneSP(Conn, procedurename)); sw.Flush(); sw.Close(); } MessageBox.Show(this, "File salvato in " + fileName, "Avviso"); }
private void btnDati_Click(object sender, EventArgs e) { DialogResult dr = saveFileDialog1.ShowDialog(); if (dr != DialogResult.OK) { MessageBox.Show(this, "File non scelto. Procedura interrotta"); return; } string filename = saveFileDialog1.FileName; StreamWriter writer = new StreamWriter(filename, false, Encoding.Default); writer.WriteLine(Header); writer.WriteLine("delete from inventorytree"); writer.WriteLine("GO"); DataTable tInventoryTree = DataAccess.CreateTableByName(Meta.Conn, "inventorytree", "*"); DataAccess.RUN_SELECT_INTO_TABLE(Meta.Conn, tInventoryTree, "idinv", null, null, true); if (tInventoryTree == null) { MessageBox.Show(this, "Errore nel riempimento dell tabella INVENTORYTREE", "Errore"); return; } if (tInventoryTree.Rows.Count == 0) { MessageBox.Show(this, "La tabella INVENTORYTREE risulta vuota, non verrà generato alcuno script", "Avviso"); return; } DataSet dsIT = new DataSet(); dsIT.Tables.Add(tInventoryTree); GeneraSQL.GeneraStrutturaEDati(Meta.Conn, dsIT, writer, UpdateType.onlyInsert, DataGenerationType.onlyData, true); writer.Close(); MessageBox.Show("File generato correttamente"); return; }
private void btnGeneraScript_Click(object sender, EventArgs e) { if (Meta.IsEmpty) { return; } Meta.SaveFormData(); DialogResult r = saveFileDialog1.ShowDialog(this); string fileName = saveFileDialog1.FileName; if (r != DialogResult.OK) { return; } btnGeneraScript.Enabled = false; DataRow Curr = DS.report.Rows[0]; string reportName = Curr["reportname"].ToString(); DataTable tRep = Conn.CreateTableByName("report", "*"); Conn.RUN_SELECT_INTO_TABLE(tRep, null, QHS.CmpEq("reportname", reportName), null, false); DataTable tRepPar = Conn.CreateTableByName("reportparameter", "*"); Conn.RUN_SELECT_INTO_TABLE(tRepPar, "number", QHS.CmpEq("reportname", reportName), null, false); DataTable tRepAddPar = Conn.CreateTableByName("reportadditionalparam", "*"); Conn.RUN_SELECT_INTO_TABLE(tRepAddPar, null, QHS.AppAnd(QHS.CmpEq("reportname", reportName), QHS.IsNull("stop")), null, false); DataAccess.AddExtendedProperty(Conn, tRep); DataAccess.AddExtendedProperty(Conn, tRepPar); DataAccess.AddExtendedProperty(Conn, tRepAddPar); DataSet D1 = new DataSet(); D1.Tables.Add(tRep); GeneraSQL.GeneraStrutturaEDati(Conn, D1, fileName, false, UpdateType.insertAndUpdate, DataGenerationType.onlyData, true); D1 = new DataSet(); D1.Tables.Add(tRepPar); GeneraSQL.GeneraStrutturaEDati(Conn, D1, fileName, true, UpdateType.insertAndUpdate, DataGenerationType.onlyData, true); D1 = new DataSet(); D1.Tables.Add(tRepAddPar); GeneraSQL.GeneraStrutturaEDati(Conn, D1, fileName, true, UpdateType.onlyInsert, DataGenerationType.onlyData, true); Dictionary <string, bool> spAdded = new Dictionary <string, bool>(); if (chkScriptSP.Checked) { ReportDocument ReportDoc = new ReportDocument(); string path = Meta.GetUsr("localreportdir").ToString(); string fName = txtFile.Text; string repFileName = Path.Combine(path, fName); try { // Open a temporary copy of the report. ReportDoc.Load(repFileName, OpenReportMethod.OpenReportByTempCopy); StreamWriter sw = new StreamWriter(fileName, true); string spName = getSPName(ReportDoc); sw.Write(GeneraSQL.scriptOneSP(Conn, spName)); spAdded.Add(spName, true); //mi scorro tutti i subreport (se presenti) del report principale ReportDefinition repDef = ReportDoc.ReportDefinition; foreach (Section sec in repDef.Sections) { foreach (ReportObject repObj in sec.ReportObjects) { if (repObj.Kind != ReportObjectKind.SubreportObject) { continue; } SubreportObject subRep = (SubreportObject)repObj; ReportDocument SubReport = subRep.OpenSubreport(subRep.SubreportName); spName = getSPName(SubReport); if (!spAdded.ContainsKey(spName) && spName != "stampa_logo") { spAdded.Add(spName, true); sw.Write(GeneraSQL.scriptOneSP(Conn, spName)); } SubReport.Close(); } } ReportDoc.Close(); sw.Flush(); sw.Close(); sw.Dispose(); } catch (Exception ee) { QueryCreator.ShowException(this, "Impossibile caricare il report " + repFileName, ee); } } MessageBox.Show(this, "File salvato in " + fileName, "Avviso"); btnGeneraScript.Enabled = true; }
private void btnAddScriptAuditCheck_Click(object sender, EventArgs e) { if (DS.auditcheck.Rows.Count == 0) { return; } QueryHelper QHS = Conn.GetQueryHelper(); DataTable t = Conn.CreateTableByName("auditcheck", "*", true); DataRow r = DS.auditcheck.Rows[0]; string filter = null; bool clear = false; UpdateType updateType = UpdateType.insertAndUpdate; string filename = "auditcheck.sql"; bool append = false; if (radRule.Checked) { filter = QHS.CmpEq("idaudit", r["idaudit"]); clear = true; updateType = UpdateType.bulkinsert; filename = r["idaudit"].ToString().ToLower() + ".sql"; } if (radToday.Checked) { filter = QHS.AppAnd(QHS.CmpEq("idaudit", r["idaudit"]), "( (convert(date,getdate()) = convert(date, lt)) )"); filename = r["idaudit"].ToString().ToLower() + ".sql"; } if (radTable.Checked) { filter = QHS.MCmp(r, "idaudit", "tablename"); clear = true; updateType = UpdateType.bulkinsert; filename = r["idaudit"].ToString().ToLower() + "_" + r["tablename"].ToString().ToLower() + ".sql"; } if (radThis.Checked) { filter = QHS.CmpKey(r); filename = r["idaudit"].ToString().ToLower() + "_" + r["tablename"].ToString().ToLower() + "_" + r["opkind"].ToString().ToLower() + r["idcheck"].ToString().ToLower() + ".sql"; } if (radSingleAppend.Checked) { filter = QHS.CmpKey(r); filename = r["idaudit"].ToString().ToLower() + ".sql"; append = true; } DataAccess.RUN_SELECT_INTO_TABLE(Conn, t, null, filter, null, true); if (t.Rows.Count == 0) { MessageBox.Show("Nessuna riga trovata. Filtro:" + filter + " Ultimo errore:" + Conn.LastError); return; } DataAccess.AddExtendedProperty(Conn, t); DataSet DS2 = new DataSet(); DS2.Tables.Add(t); if (txtFolder.Text != "") { filename = Path.Combine(txtFolder.Text, filename); } StreamWriter writer = new StreamWriter(filename, append, System.Text.Encoding.Default); if (radRule.Checked) { //Eventuale riga di audit writer.WriteLine("-- AUDITCHECK " + r["idaudit"]); DataSet D3 = new DataSet(); DataTable t2 = Conn.RUN_SELECT("audit", "*", null, QHS.CmpEq("idaudit", r["idaudit"]), null, false); D3.Tables.Add(t2); GeneraSQL.DO_GENERATE(Conn, D3, writer, UpdateType.onlyInsert, DataGenerationType.onlyData, true); } //eventuale cleanup if (clear) { writer.WriteLine("delete from auditcheck where " + filter); writer.WriteLine("GO"); writer.Flush(); } //Inserimento dati GeneraSQL.DO_GENERATE(Conn, DS2, writer, updateType, DataGenerationType.onlyData, true); writer.Flush(); writer.Close(); MessageBox.Show("Script " + filename + (append ? " aggiornato " : " generato ") + " con successo", "Avviso"); }
private bool aggiornaDB(string tName) { StringBuilder SB = new StringBuilder(); if (tName == "config") { // A differenza delle altre tabelle CONFIG viene inserita o aggiornata. // L'aggiornamento vinee fatto solo sui campi relativi al patrimonio string assetsetup_fields = "assetload_flag, asset_flagnumbering, asset_flagrestart"; string[] listaCampi = new string[] { "assetload_flag", "asset_flagnumbering", "asset_flagrestart" }; foreach (DataRow rAssetSetup in dsFile.Tables["config"].Rows) { string filtro = QHS.CmpEq("ayear", rAssetSetup["ayear"]); int nRow = Meta.Conn.RUN_SELECT_COUNT("config", filtro, true); if (nRow > 0) { string queryAssetSetup = "UPDATE config SET "; string u = ""; foreach (string fName in listaCampi) { u += fName + " = " + QHS.quote(rAssetSetup[fName]) + ","; } u = u.Substring(0, u.Length - 2); queryAssetSetup += u + " WHERE " + QHS.CmpEq("ayear", rAssetSetup["ayear"]); SB.Append(queryAssetSetup); } else { string fieldForInsert = "ayear," + assetsetup_fields; string sqlInsert = "INSERT INTO config (" + fieldForInsert + ") VALUES ("; string values = GeneraSQL.GetSQLDataValues(rAssetSetup); sqlInsert += values; SB.Append(sqlInsert); } } } else { string f_id; string f_code; string f_descr; ottieniCampiTabella(tName, out f_id, out f_code, out f_descr); if ((f_id == "") || (f_code == "") || (f_descr == "")) { return(false); } string[] tLinked = ottieniElencoTabelleCollegate(tName); // Parte UPDATE delle chiavi esterne e DELETE delle righe che non devono esserci nel DB del dipartimento foreach (DataRow r in dsLookUp.Tables[tName].Rows) { if ((r["!linkedcode"] == DBNull.Value) || (r["!linkedcode"].ToString() == "")) { continue; } foreach (string tabella in tLinked) { if ((tabella == null) || (tabella == "")) { continue; } string valore_codice_db = r[f_code].ToString(); string valore_codice_ds = r["!linkedcode"].ToString(); if (valore_codice_db != valore_codice_ds) { string sqlUpdate = "UPDATE " + tabella + " SET " + f_id + " = " + QueryCreator.quotedstrvalue(r["!linkedid"], true) + " WHERE " + f_id + " = " + QueryCreator.quotedstrvalue(r[f_id], true) + "\n\r"; SB.Append(sqlUpdate); } } string sqlDelete = "DELETE FROM " + tName + " WHERE " + f_id + " = " + QueryCreator.quotedstrvalue(r[f_id], true) + "\n\r"; SB.Append(sqlDelete); if (SB.ToString() != "") { SB.Append("GO\n\r"); } } // Parte delle INSERT delle righe assenti nel DB del dipartimento string fields = ""; foreach (DataColumn c in dsPruned.Tables[tName].Columns) { fields += c.ColumnName + ","; } fields = fields.Remove(fields.Length - 1, 1); foreach (DataRow rDS in dsPruned.Tables[tName].Rows) { string filter = QueryCreator.WHERE_KEY_CLAUSE(rDS, DataRowVersion.Current, false); if (dsLookUp.Tables[tName].Select(filter).Length > 0) { continue; } string sqlInsert = "INSERT INTO " + tName + "(" + fields + ") VALUES ("; string values = GeneraSQL.GetSQLDataValues(rDS); sqlInsert += values; SB.Append(sqlInsert); } } if (SB.ToString() != "") { SB.Append("GO\n\r"); string error; return(Download.RUN_SCRIPT(Meta.Conn, SB, out error)); } return(true); }