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;
}