void AddNewStoredProcedure()
{
string spname = txtspname.Text.Trim();
StoredProcedureBusiness spData = new StoredProcedureBusiness();
var splist = spData.GetStoredProcedureList(AppTimeConfiguration.MainServer);
if (splist.Select(x => x.SPName.ToLower()).Contains(spname.ToLower()))
{
string procname = splist.Where(x => x.SPName.ToLower() == spname.ToLower()).First().SPName;
EntityBusiness edata = new EntityBusiness();
SPModel sp = new SPModel();
sp.SPName = procname;
sp.SPDate = dtpScriptDate.Value;
sp.CreatedDate = DateTime.Now;
long res = edata.AddNewSP(sp);
if (res != -1)
{
LoadStoredProcedures(dtpScriptDate.Value);
txtspname.Clear();
}
else
{
MessageBox.Show("Stored Procedures already exists.", "Information", MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show("Stored Procedures does not exists in current database.", "Information", MessageBoxButtons.OK,
MessageBoxIcon.Exclamation);
}
}
void LoadDatabaseStoredProcedures()
{
StoredProcedureBusiness spData = new StoredProcedureBusiness();
var splist = spData.GetStoredProcedureList(AppTimeConfiguration.MainServer);
if (splist != null)
{
txtspname.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
txtspname.AutoCompleteSource = AutoCompleteSource.CustomSource;
var autosource = new AutoCompleteStringCollection();
autosource.AddRange(splist.Select(x => x.SPName).ToArray());
txtspname.AutoCompleteCustomSource = autosource;
}
}
public string GenerateVersionScript(List<string> splist)
{
StoredProcedureBusiness spData = new StoredProcedureBusiness();
string ver_script = "";
foreach (var spname in splist)
{
string dropquery = DropQuery(spname,true);
string script = dropquery + spData.GetScript(null, spname).Trim() + Environment.NewLine + Environment.NewLine + "GO" + Environment.NewLine + Environment.NewLine + Environment.NewLine;
script += "--- *** End " + spname + " *** ---" + Environment.NewLine + Environment.NewLine
+ Environment.NewLine;
ver_script += script;
}
return ver_script;
}
public void SaveSingleScript(List<string> splist)
{
if (splist != null && splist.Count > 0)
{
string dirname = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString();
string path = Application.StartupPath + "\\GeneratedScripts\\" + dirname;
Directory.CreateDirectory(path);
string[] dirs = Directory.GetDirectories(path);
int dirno = 0;
if (dirs.Length > 0)
{
int maxno = 1;
foreach (var dir in dirs)
{
string no = Regex.Match(new DirectoryInfo(dir).Name, @"\d+").Value;
if (no.Trim().Length > 0)
{
if (Convert.ToInt32(no.Trim()) > maxno)
maxno = Convert.ToInt32(no.Trim());
}
}
if (dirno < maxno)
dirno = maxno;
}
dirno = dirno + 1;
string grouppath = path + "\\" + dirno.ToString("00");
Directory.CreateDirectory(grouppath);
StoredProcedureBusiness spdata = new StoredProcedureBusiness();
ScriptHelper spHelper = new ScriptHelper();
foreach (var sp in splist)
{
string query = spHelper.GenerateIndividualScript(sp, spdata.GetScript(null, sp),false);
System.IO.File.WriteAllText(grouppath + "\\" + sp + ".sql", query);
}
Process.Start(grouppath);
}
}
private void ExecuteQueryOnServer(ServerModel server, string query)
{
StoredProcedureBusiness spdata = new StoredProcedureBusiness();
var res = spdata.ExecuteQuery(server == null ? AppTimeConfiguration.MainServer : server, query);
}
void CheckInScripts()
{
List<CheckInModel> errorlist = new List<CheckInModel>();
List<CheckInModel> successlist = new List<CheckInModel>();
OpResult = new List<LogText>();
bool vsucess = true;
successlist = StoredProcedures;
var bVersion = cbVersion.Checked;
var bSingle = cbSP.Checked;
try
{
if (_username.Length > 0 && _password.Length > 0)
{
TFSAuthModel auth = new TFSAuthModel() { Username = _username, Password = _password };
if (successlist.Count > 0)
{
if (bVersion || bSingle)
{
// get latest project files
if (bVersion)
{
var res = TFSHelper.GetLatest(_path_version_tfs, auth);
AddConsoleText(res);
if (res.IsError)
{
OpResult.Add(new LogText()
{
IsError = true,
Output = "Operation failed - Error occured while updating version scripts."
});
vsucess = false;
MessageBox.Show("Error occured while updating version scripts", "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
if (bSingle)
{
var res = TFSHelper.GetLatest(_path_sp_tfs, auth);
AddConsoleText(res);
if (res.IsError)
{
OpResult.Add(new LogText()
{
IsError = true,
Output = "Operation failed - Error occured while updating Stored Procedures."
});
MessageBox.Show("Error occured while updating Stored Procedures", "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
AddManualConsoleTest("Getting latest scripts successful....");
// end getting latest
//create files if not exists - version
bool vcreated = false;
if (bVersion)
{
string path = _path_version_local + "\\" + FileHelper.GenerateDateFile(ScriptDate);
if (!File.Exists(path))
{
FileHelper.CreateFile(path);
vcreated = true;
}
}
//create files if not exists - single
if (bSingle)
{
foreach (var sp in successlist)
{
string path = _path_sp_local + "\\" + sp.Name + ".sql";
if (!File.Exists(path))
{
FileHelper.CreateFile(path);
sp.fCreated = true;
}
}
}
//checkout files for edit
if (bVersion)
{
if (!vcreated)
{
string path = _path_version_tfs + "\\" + FileHelper.GenerateDateFile(ScriptDate);
var res = TFSHelper.Checkout(path, auth);
AddConsoleText(res);
if (res != null && res.IsError)
{
OpResult.Add(new LogText()
{
IsError = true,
Output = "Version Script(" + FileHelper.GenerateDateFile(ScriptDate) + ") - Failed."
});
vsucess = false;
}
}
}
if (bSingle)
{
foreach (var sp in successlist)
{
string path = _path_sp_tfs + "\\" + sp.Name + ".sql";
if (!sp.fCreated)
{
var res = TFSHelper.Checkout(path, auth);
AddConsoleText(res);
if (res.IsError)
{
sp.IsError = true;
errorlist.Add(sp);
}
}
}
foreach (var ersp in errorlist)
{
var sp = successlist.Where(x => x.ID == ersp.ID).SingleOrDefault();
if (sp != null)
successlist.Remove(sp);
}
}
//add new files to tfs
//version script
if (bVersion)
{
if (vcreated)
{
var res = TFSHelper.AddFile(_path_version_local, FileHelper.GenerateDateFile(ScriptDate), auth);
AddConsoleText(res);
}
}
if (bSingle)
{
foreach (var sp in successlist)
{
string name = sp.Name + ".sql";
if (sp.fCreated)
{
var res = TFSHelper.AddFile(_path_sp_local, name, auth);
AddConsoleText(res);
}
}
}
//update scripts text
if (bVersion)
{
if (vcreated)
{
string path = _path_version_local + "\\" + FileHelper.GenerateDateFile(ScriptDate);
FileHelper.UpdateFileText(path, new ScriptHelper().GenerateVersionScript(successlist.Select(x => x.Name).ToList()));
}
else
{
string path = _path_version_local + "\\" + FileHelper.GenerateDateFile(ScriptDate);
string vscript = FileHelper.ReadFile(path);
StoredProcedureBusiness spData = new StoredProcedureBusiness();
foreach (var s in successlist)
{
string script = new ScriptHelper().GenerateIndividualScript(s.Name, spData.GetScript(null, s.Name).Trim(), true);
vscript = ReplaceScript(vscript, script, s.Name);
}
FileHelper.UpdateFileText(path, vscript);
}
}
//update single scripts
if (bSingle)
{
StoredProcedureBusiness spData = new StoredProcedureBusiness();
foreach (var sp in successlist)
{
string script = new ScriptHelper().GenerateIndividualScript(sp.Name, spData.GetScript(null, sp.Name).Trim(), false);
string path = _path_sp_local + "\\" + sp.Name + ".sql";
FileHelper.UpdateFileText(path, script);
}
}
//checkin pending changes
string comment = "";
if (txtComment.Text.Trim().Length > 0)
comment = txtComment.Text.Trim();
else
comment = "Script Changes";
var checkinres = TFSHelper.CheckIn(comment, auth);
AddConsoleText(checkinres);
AddManualConsoleTest("Operation completed successfuly");
}
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
GenerateLog(successlist, errorlist, vsucess, bVersion);
}
}
void CompareScript(int rowindex)
{
string spname = dgvSP.Rows[rowindex].Cells[3].Value.ToString();
StoredProcedureBusiness spdata = new StoredProcedureBusiness();
int sourceid = Convert.ToInt32(dgvSP.Rows[rowindex].Cells[5].Value);
int toid = Convert.ToInt32(dgvSP.Rows[rowindex].Cells[6].Value);
var serverlist = new EntityBusiness().GetServerList();
var serversource = serverlist.Where(x => x.ServerID == sourceid).First();
var serverto = serverlist.Where(x => x.ServerID == toid).First();
string leftquery = new StoredProcedureBusiness().GetScript(serversource, spname);
string rightquery = new StoredProcedureBusiness().GetScript(serverto, spname);
frmCompare frm = new frmCompare(leftquery,rightquery);
frm.Show();
}
private void GenerateScript(int index)
{
string spname = dgvSP.Rows[index].Cells[3].Value.ToString();
StoredProcedureBusiness spdata = new StoredProcedureBusiness();
string spbody = spdata.GetScript(null, spname);
frmScriptViewer frm = new frmScriptViewer(new ScriptHelper().GenerateIndividualScript(spname, spbody,false), spname);
frm.Show();
}
