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