private void settingsControl1_ServerChanged(object sender, string serverName, string username, string password, AuthenticationType authType)
{
Connection.ConnectionData oldConnData = new Connection.ConnectionData();
this.connData.Fill(oldConnData);
this.Cursor = Cursors.WaitCursor;
this.connData.SQLServerName = serverName;
if (!string.IsNullOrWhiteSpace(username) && (!string.IsNullOrWhiteSpace(password)))
{
this.connData.UserId = username;
this.connData.Password = password;
}
this.connData.AuthenticationType = authType;
this.connData.ScriptTimeout = 5;
try
{
DatabaseList dbList = SqlSync.DbInformation.InfoHelper.GetDatabaseList(this.connData);
//this.LookUpTable_Load(null, EventArgs.Empty);
}
catch
{
MessageBox.Show("Error retrieving database list. Is the server running?", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
this.connData = oldConnData;
this.settingsControl1.Server = oldConnData.SQLServerName;
}
this.Cursor = Cursors.Default;
}
public RebuildForm(Connection.ConnectionData connData, DatabaseList databaseList)
{
this.dbList = databaseList;
this.connData = connData;
InitializeComponent();
ddDatabases.Items.AddRange(databaseList.ToArray());
}
public ObjectValidation(Connection.ConnectionData connData)
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
this.connData = connData;
}
private ScriptRunHistoryForm(Connection.ConnectionData connData, string databaseName, string currentFileText, string scriptHash)
{
InitializeComponent();
this.connData = connData;
this.connData.DatabaseName = databaseName;
this.currentFileText = currentFileText;
this.scriptHash = scriptHash;
}
public ObjectScriptingConfigData(bool withDelete, bool combineTableObjects, bool zipScripts, bool includeFileHeader, bool reportStatusPerObject, Connection.ConnectionData connData)
{
this.WithDelete = withDelete;
this.CombineTableObjects = combineTableObjects;
this.ZipScripts = zipScripts;
this.IncludeFileHeader = includeFileHeader;
this.ReportStatusPerObject = reportStatusPerObject;
this.ConnData = connData;
}
public AnalysisForm(Connection.ConnectionData connData, string databaseName)
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
this.connData = connData;
this.databaseName = databaseName;
}
private void mnuChangeSqlServer_Click(object sender, System.EventArgs e)
{
ConnectionForm frmConnect = new ConnectionForm("Sql Build Manager");
DialogResult result = frmConnect.ShowDialog();
if (result == DialogResult.OK)
{
this.connData = frmConnect.SqlConnection;
GetDatabaseSummary();
}
}
private void mnuChangeSqlServer_Click(object sender, System.EventArgs e)
{
ConnectionForm frmConnect = new ConnectionForm("Sql Data Extraction");
DialogResult result = frmConnect.ShowDialog();
if (result == DialogResult.OK)
{
this.connData = frmConnect.SqlConnection;
this.settingsControl1.Server = this.connData.SQLServerName;
GetDatabaseList();
}
}
private void mnuChangeSqlServer_Click(object sender, System.EventArgs e)
{
ConnectionForm frmConnect = new ConnectionForm("Sql Object Validator");
DialogResult result = frmConnect.ShowDialog();
if (result == DialogResult.OK)
{
this.connData = frmConnect.SqlConnection;
ddDatabaseList.Items.Clear();
for (int i = 0; i < frmConnect.DatabaseList.Count; i++)
{
ddDatabaseList.Items.Add(frmConnect.DatabaseList[i].DatabaseName);
}
this.settingsControl1.Server = this.connData.SQLServerName;
}
}
private void SprocTestConfigForm_Load(object sender, EventArgs e)
{
if (this.connData == null)
{
ConnectionForm frmConnect = new ConnectionForm("Stored Proc Testing");
DialogResult result = frmConnect.ShowDialog();
if (result == DialogResult.OK)
{
this.connData = frmConnect.SqlConnection;
}
else
{
MessageBox.Show("Stored Procedure Testing can not continue without a valid Sql Connection", "Unable to Load", MessageBoxButtons.OK, MessageBoxIcon.Hand);
this.Close();
Application.Exit();
}
}
this.settingsControl1.Server = this.connData.SQLServerName;
if (this.configFileName.Length > 0)
{
try
{
this.settingsControl1.Project = this.configFileName;
testConfig = SqlSync.SprocTest.TestManager.ReadConfiguration(this.configFileName);
this.connData.DatabaseName = testConfig.Name;
BindTreeView();
pgBar.Style = ProgressBarStyle.Marquee;
bgSprocList.RunWorkerAsync();
}
catch (Exception exe)
{
MessageBox.Show("Error loading config file.\r\n" + exe.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
this.mruManager = new SqlSync.MRU.MRUManager();
this.mruManager.Initialize(
this, // owner form
fileToolStripMenuItem,
mnuFileMRU, // Recent Files menu item
@"Software\Michael McKechney\Sql Sync\Stored Proc Testing"); // Registry path to keep MRU list
this.mruManager.MaxDisplayNameLength = 40;
}
public ScriptRunHistoryForm(Connection.ConnectionData connData, string databaseName, string scriptFileName, string currentFileText, string scriptHash) :
this(connData, databaseName, currentFileText, scriptHash)
{
this.scriptFileName = scriptFileName;
}
public void Validate(Connection.ConnectionData connData, BackgroundWorker bgWorker, DoWorkEventArgs eW)
{
this.bgWorker = bgWorker;
this.connData = connData;
//string getObjSql = "select name, OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn') as quoted_ident_on, OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') as ansi_nulls_on, user_name(o.uid) owner, type from sys.objects o where type in ('P', 'V', 'FN') and category = 0 order by type";
string getObjSql = @"select o.name, OBJECTPROPERTY(object_id, 'ExecIsQuotedIdentOn') as quoted_ident_on,
OBJECTPROPERTY(object_id, 'ExecIsAnsiNullsOn') as ansi_nulls_on, user_name(o.schema_id) owner, s.name as [schema], type
from sys.objects o
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id where type in ('P', 'V', 'FN') order by type";
SqlConnection conn = SqlSync.Connection.ConnectionHelper.GetConnection(connData);
SqlCommand cmd = new SqlCommand(getObjSql, conn);
SqlDataAdapter adapt = new SqlDataAdapter(cmd);
DataTable table = new DataTable();
try
{
adapt.Fill(table);
}
catch (Exception e)
{
this.bgWorker.ReportProgress(0, new ValidationResultEventArgs("", "", e.Message, ValidationResultValue.Invalid));
return;
}
Regex crossDbCheck = new Regex(@"\.\w*\.", RegexOptions.IgnoreCase);
//for each object get the command text and execute the command
foreach (DataRow procRow in table.Rows)
{
if (bgWorker.CancellationPending)
{
eW.Cancel = true;
return;
}
conn.ConnectionString = SqlSync.Connection.ConnectionHelper.GetConnectionString(connData);
string objectName = procRow["Name"].ToString();
string quoted_ident = Convert.ToBoolean(procRow["quoted_ident_on"]) ? "ON" : "OFF";
string ansi_nulls_on = Convert.ToBoolean(procRow["ansi_nulls_on"]) ? "ON" : "OFF";
string owner = procRow["owner"].ToString();
string type = procRow["type"].ToString();
string schema = procRow["schema"].ToString();
//call sp_helptext to the create command
string getObjTextSql = string.Format("exec sp_helptext '{1}.{0}'", objectName, schema);
cmd = new SqlCommand(getObjTextSql, conn);
SqlDataReader textRdr = null;
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
textRdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (SqlException ex)
{
string errText = string.Format("COULD NOT BE READ : {0}", ex.Message);
this.bgWorker.ReportProgress(0, new ValidationResultEventArgs(schema + "." + objectName, type, errText, ValidationResultValue.Invalid));
continue;
}
//loop through the command text and build up the command string
StringBuilder sb = new StringBuilder(8000);
bool detectedCrossDatabase = false;
using (textRdr)
{
while (textRdr.Read())
{
string thisText = textRdr["text"].ToString();
if (detectedCrossDatabase == false && crossDbCheck.Match(thisText).Success&& !thisText.Trim().StartsWith("--")) //Check for 3 part declaration for cross DB check.
{
detectedCrossDatabase = true;
}
sb.Append(thisText);
}
}
string procText = sb.ToString();
#region << Check that the columns are valid >>
//execute the command (this will check for valid colums)
try
{
using (conn)
{
conn.Open();
SqlCommand sqlCmd = new SqlCommand("SET NOEXEC ON", conn);
sqlCmd.CommandType = CommandType.Text;
sqlCmd.ExecuteNonQuery();
sqlCmd = new SqlCommand("SET QUOTED_IDENTIFIER " + quoted_ident, conn);
sqlCmd.CommandType = CommandType.Text;
sqlCmd.ExecuteNonQuery();
sqlCmd = new SqlCommand("SET ANSI_NULLS " + ansi_nulls_on, conn);
sqlCmd.CommandType = CommandType.Text;
sqlCmd.ExecuteNonQuery();
sqlCmd = new SqlCommand(procText, conn);
sqlCmd.CommandType = CommandType.Text;
sqlCmd.ExecuteNonQuery();
sqlCmd = new SqlCommand("SET QUOTED_IDENTIFIER OFF ", conn);
sqlCmd.CommandType = CommandType.Text;
sqlCmd.ExecuteNonQuery();
sqlCmd = new SqlCommand("SET ANSI_NULLS ON", conn);
sqlCmd.CommandType = CommandType.Text;
sqlCmd.ExecuteNonQuery();
sqlCmd = new SqlCommand("SET NOEXEC OFF", conn);
sqlCmd.CommandType = CommandType.Text;
sqlCmd.ExecuteNonQuery();
sqlCmd = new SqlCommand("SET PARSEONLY OFF", conn);
sqlCmd.CommandType = CommandType.Text;
sqlCmd.ExecuteNonQuery();
}
}
catch (SqlException ex)
{
this.bgWorker.ReportProgress(0, new ValidationResultEventArgs(schema + "." + objectName, type, ex.Message.Replace("\r", "; ").Replace("\n", ""), ValidationResultValue.Invalid));
continue;
}
#endregion
#region << Check that the table references are valid >>
string dependsSQL = String.Format("sp_depends [{1}.{0}]", objectName, schema);
conn.ConnectionString = SqlSync.Connection.ConnectionHelper.GetConnectionString(connData);
cmd = new SqlCommand(dependsSQL, conn);
DataSet ds = new DataSet();
adapt.SelectCommand = cmd;
try
{
adapt.Fill(ds);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
string errText;
ValidationResultValue val;
if (detectedCrossDatabase)
{
errText = string.Format("Unable to Validate: Possible Cross Database Join");
val = ValidationResultValue.CrossDatabaseJoin;
}
else
{
errText = string.Format("Invalid Table Reference? No Table Dependencies found.");
val = ValidationResultValue.Caution;
}
this.bgWorker.ReportProgress(0, new ValidationResultEventArgs(schema + "." + objectName, type, errText, val));
continue;
}
}
catch (SqlException ex)
{
//string errText = string.Format("{0} sp_depends FAILED : {1}", objectName, ex.ToString()).Replace("\r", " ").Replace("\n", " ");
this.bgWorker.ReportProgress(0, new ValidationResultEventArgs(schema + "." + objectName, type, ex.Message, ValidationResultValue.Invalid));
continue;
}
#endregion
//Success!
this.bgWorker.ReportProgress(0, new ValidationResultEventArgs(schema + "." + objectName, type, "Valid", ValidationResultValue.Valid));
}
}
public SprocTestConfigForm(Connection.ConnectionData connData) : this()
{
this.connData = connData;
}
public DatabaseSizeSummaryForm(Connection.ConnectionData connData) : this()
{
this.connData = connData;
this.Text = String.Format(this.Text, connData.SQLServerName);
}
