public int DoFileAction() { int status = 1; if (c.FileAction == "writeSSP" || c.FileAction == "writeFNS" || c.FileAction == "writeFNT") { DataTable temp = c.Tapper.GetDataTable(); string sspFilePath = ""; string sspFileAction = ""; if (c.Tapper.GetRowsAffected() <= 0) { c.Log.AppendLog(string.Format("[Filr] No {0} to write", c.ObjType)); return 0; } int counter = 1; BuildDirectory(); c.Log.AppendLog(string.Format("[Filr] Initiating {0} writing: {1}", c.ObjType, GetSourceDirectory())); for (int i = 0; i < sspCache.Count; i++) { sspFilePath = string.Format(@"{0}\{1}.sql", GetSourceDirectory(), sspCache[i].sspName); sspFileAction = File.Exists(sspFilePath) ? "Overwrite" : "Written"; //write to physical file using (StreamWriter file = new StreamWriter(sspFilePath, false)) { file.WriteLine(sspCache[i].sspDef); file.Close(); } c.Log.AppendLog(string.Format(@"[Filr] {2}: {0}. {1}", counter, sspCache[i].sspName, sspFileAction)); counter = counter + 1; } //foreach (DataRow row in temp.Rows) //{ // sspName = row["ROUTINE_NAME"].ToString(); // sspDef = row["ROUTINE_DEFINITION"].ToString(); // sspFilePath = string.Format(@"{0}\{1}.sql", GetSourceDirectory(), sspName); // sspFileAction = File.Exists(sspFilePath) ? "Overwrite" : "Written"; // //write to physical file // using (StreamWriter file = new StreamWriter(sspFilePath, false)) // { // file.WriteLine(sspDef); // file.Close(); // } // c.Log.AppendLog(string.Format(@"[Filr] {2}: {0}. {1}", counter, sspName, sspFileAction)); // counter = counter + 1; //} c.Log.AppendLog(string.Format("[Filr] Done {0} writing: {1} files", c.ObjType, GetSourceDirectory())); } else if (c.FileAction == "readSSP" || c.FileAction == "readFNS" || c.FileAction == "readFNT") { string diffFile; string filePath; int counter = 0; // read straight from string if (c.InPath.Length <= 0) { c.Log.AppendLog(string.Format("[Filr] Undefined InPath (file with {0} to read)", c.ObjType)); if (c.InData == "") { c.Log.AppendLog("[Filr] InData NOT exists"); return -1; } c.Log.AppendLog(string.Format("[Filr] InData exists proceed to reading {0}", c.ObjType)); foreach (string targetSSP in c.InData.Split(',')) { diffFile = targetSSP.Trim(); if (diffFile != "") { filePath = string.Format(@"{0}\{1}", c.RepoPath, diffFile); if (!File.Exists(filePath)) { c.Log.AppendLog("[Filr] File not exists: " + filePath); break; } if (diffFile.Split('.').Length != 2) { c.Log.AppendLog(string.Format( @"[Filr] Incorrect entry in line {0}, file {1}" , counter + 1 , c.InPath)); break; } SSP temp = new SSP(); temp.sspDef = new StreamReader(filePath).ReadToEnd(); temp.sspFilePath = diffFile; temp.sspName = diffFile.Split('.')[0]; sspCache.Add(temp); counter++; } } } // read sproc from file else { c.Log.AppendLog(string.Format("[Filr] Initiating {0} reading: {1}", c.ObjType, GetSourceDirectory())); try { if (!File.Exists(c.InPath)) { c.Log.AppendLog("[Filr] File not exists: " + c.InPath); return 0; } using (StreamReader file = new StreamReader(c.InPath)) { while ((diffFile = file.ReadLine()) != null) { if (diffFile != "") { filePath = string.Format(@"{0}\{1}", c.RepoPath, diffFile); if (!File.Exists(filePath)) { c.Log.AppendLog("[Filr] File not exists: " + filePath); break; } if (diffFile.Split('.').Length != 2) { c.Log.AppendLog(string.Format( @"[Filr] Incorrect entry in line {0}, file {1}" , counter + 1 , c.InPath)); break; } SSP temp = new SSP(); temp.sspDef = new StreamReader(filePath).ReadToEnd(); temp.sspFilePath = diffFile; temp.sspName = diffFile.Split('.')[0]; sspCache.Add(temp); counter++; } } file.Close(); } } catch (Exception e) { c.Log.AppendLog("[Filr] Error in reading file(s)"); c.Log.AppendLog(e.ToString()); status = -1; } c.Log.AppendLog(string.Format("[Filr] Done {0} reading: {1} files", c.ObjType, counter)); } } else if (c.FileAction == "compareSSP" || c.FileAction == "compareNS" || c.FileAction == "compareFNT") { if (c.RepoPath.Length <= 0 || c.Repo2Path.Length <= 0) { c.Log.AppendLog("[Filr] Undefined RepoPath (source) or Repo2Path (target)"); return 0; } string filename = ""; List<string> messages = new List<string>(); List<string> missings = new List<string>(); List<string> justnames = new List<string>(); string temp = ""; c.Log.AppendLog(string.Format( @"[Filr] Initiating {0} comparison: {1} (Base) {2} (Target)" , c.ObjType , c.RepoPath , c.Repo2Path)); foreach (string filePath in Directory.GetFiles(c.RepoPath)) { filename = Path.GetFileName(filePath); string filePath2 = string.Format(@"{0}\{1}", c.Repo2Path, filename); //checking inconsistent existance of files if (!File.Exists(filePath2)) { temp = "[Filr] Extra file in base: " + filename; missings.Add(temp); continue; } //diff file if (!FileCompare(filePath, filePath2)) { temp = "[Filr] Diff: " + filename; messages.Add(temp); justnames.Add(filename); } } foreach (string filePath2 in Directory.GetFiles(c.Repo2Path)) { filename = Path.GetFileName(filePath2); string filePath = string.Format(@"{0}\{1}", c.RepoPath, filename); //checking inconsistent existance of files if (!File.Exists(filePath)) { temp = "[Filr] Extra file in target: " + filename; missings.Add(temp); continue; } } List<string> total = new List<string>(messages.Count + missings.Count); total.AddRange(missings); total.AddRange(messages); foreach (string entry in total) { c.Log.AppendLog(entry); //Console.WriteLine(entry); } if (missings.Count > 0) c.Log.AppendLog(string.Format("[Filr] Total of {0} extra {1} in source", missings.Count, c.ObjType)); if (messages.Count > 0) c.Log.AppendLog(string.Format("[Filr] Total of {0} diff {1} in source", messages.Count, c.ObjType)); if (c.OutPath.Length > 0) { temp = ""; foreach (string entry in justnames) { temp = temp + string.Format(@"{0} {1}", entry, Environment.NewLine); } File.WriteAllText(c.OutPath, temp); } } return status; }
public int DoDBAction() { int status = 1; List<string> SQLStore; List<string> tempDisplay = new List<string>(); string logHead = string.Format("[SQL {0}]", c.DBAction); string objType = c.ObjType; if (c.DBAction == "selectSSP" || c.DBAction == "selectFNS" || c.DBAction == "selectFNT") { rowsAffected = 0; SQLStore = BuildSQLString(); // only 1 SQL in SSP selection if (SQLStore[0].Length == 0) { c.Log.AppendLog(string.Format("{0} Undefined SQL string", logHead)); return 0; } try { using (SqlCommand cmd = new SqlCommand(SQLStore[0], connection)) { c.Log.AppendLog(string.Format("{0} Read start: {1}", logHead, SQLStore[0])); reader = cmd.ExecuteReader(); c.Log.AppendLog(string.Format("{0} Read end", logHead)); dt.Load(reader); rowsAffected = rowsAffected + dt.Rows.Count; c.Log.AppendLog(string.Format("{0} Loaded datastore: {1} rows", logHead, dt.Rows.Count)); } } catch (Exception e) { c.Log.AppendLog(string.Format("{0} Read error: {1}", logHead, SQLStore[0])); c.Log.AppendLog(e.ToString()); status = -1; } //storing into cache, SSP + FNs foreach (DataRow row in dt.Rows) { //write to physical file SSP temp = new SSP(); temp.sspName = row["ROUTINE_NAME"].ToString(); temp.sspDef = row["ROUTINE_DEFINITION"].ToString(); temp.sspFilePath = ""; c.Filr.sspCache.Add(temp); } //TODO: //Extract portions for custom sql //remember to clear cache when soft/hard reset } else if (c.DBAction == "updateSSP" || c.DBAction == "updateFNS" || c.DBAction == "updateFNT") { if (c.Filr.sspCache.Count <= 0) { c.Log.AppendLog(string.Format("{0} Undefined SSP cache, Exit", logHead)); return 0; } //reset variables SSP.currIndex = 0; countSSP = 0; rowsAffected = 0; SSP temp; //start looping cache c.Log.AppendLog(string.Format("{0} Initiating SSP update procedure", logHead)); for (int i = 0; i < c.Filr.sspCache.Count; i++) { SSP.currIndex = i; temp = c.Filr.sspCache[SSP.currIndex]; SQLStore = BuildSQLString(); if (SQLStore[1].Length == 0) { c.Log.AppendLog(string.Format("{0} Undefined SSP content: {1}", logHead, temp.sspName)); continue; } tempDisplay.Add(string.Format("New {0}:", objType)); tempDisplay.Add(string.Format("Detected existing {0}:", objType)); tempDisplay.Add(string.Format("Created {0}:", objType)); tempDisplay.Add(string.Format("Updated {0}:", objType)); try { using (SqlCommand cmd = new SqlCommand(SQLStore[0], connection)) using (SqlCommand cmd2 = new SqlCommand()) using (SqlCommand cmd3 = new SqlCommand(SQLStore[2], connection)) { // checking existance of SSP countSSP = (int)cmd.ExecuteScalar(); c.Log.AppendLog(string.Format("{0} {1} {2}.{3}" , logHead , (countSSP == 0 ? tempDisplay[0] : tempDisplay[1]) , c.Database , temp.sspName) ); // decide create/ alter SSP cmd2.CommandText = ManaStore.Swap( SQLStore[1] , countSSP > 0 , objType == "SSP" ? "PROCEDURE" : "FUNCTION" ); cmd2.Connection = connection; cmd2.ExecuteNonQuery(); c.Log.AppendLog(string.Format("{0} {1} {2}.{3}" , logHead , (countSSP == 0 ? tempDisplay[2] : tempDisplay[3]) , c.Database , temp.sspName) ); cmd3.ExecuteNonQuery(); c.Log.AppendLog(string.Format("{0} Marked {1} (sp_recompile): {2}.{3}" , logHead , objType , c.Database , temp.sspName) ); rowsAffected = rowsAffected + 1; } } catch (Exception e) { c.Log.AppendLog(string.Format(@"{0} Error: {1} {{{2}}}", logHead, objType, temp.sspName)); c.Log.AppendLog(e.ToString()); if (status > 0) status = -1; else status = status - 1; } } c.Log.AppendLog(string.Format("{0} Updated {1} entries of {2}", logHead, rowsAffected, objType)); } //TODO: Addon/ enable other SQL statements else { c.Log.AppendLog(string.Format("{0} No Action", logHead)); } return status; }