private List <int> CheckScriptsAlreadyRun(DatabaseInfoList dbinformation)
{
DOScriptRun dr = new DOScriptRun();
List <int> alreadyrunscripts = dr.CheckPreviouslyRunScripts(dbinformation);
alreadyrunscripts.Sort();
return(alreadyrunscripts);
}
public List <DatabaseInfoList> GetDatabaseInfo()
{
List <DatabaseInfoList> databaseitems = new List <DatabaseInfoList>();
string ConnectionString = $"Data Source={AppServerName};User ID={AppUserId};Password={AppPassword};Initial Catalog={AppDBName};";
string getstring = $"SELECT * FROM [{AppDBName}].[dbo].[Run_Script_Database_Info]";
try
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (SqlCommand getcommand = new SqlCommand(getstring, connection))
{
SqlDataReader data = getcommand.ExecuteReader();
if (data != null)
{
while (data.Read())
{
DatabaseInfoList row = new DatabaseInfoList();
row.ID = (int)data["ID"];
row.UniqueName = (string)data["UniqueName"];
row.ServerName = (string)data["ServerName"];
row.UserName = (string)data["UserName"];
row.Password = (string)data["Password"];
row.DatabaseName = (string)data["DatabaseName"];
databaseitems.Add(row);
}
}
if (data != null)
{
data.Dispose();
}
}
}
}
catch (Exception ex)
{
}
return(databaseitems);
}
public void RunScripts(List <int> fileversionstorun, DatabaseInfoList DBInfo)
{
string ConnectionString = $"Data Source={DBInfo.ServerName};User ID={DBInfo.UserName};Password={DBInfo.Password};Initial Catalog={DBInfo.DatabaseName};";
string location = DOLocationInfo.GetFolderLocation("runscript");
string[] file = null;
foreach (int fileversion in fileversionstorun)
{
file = Directory.GetFiles(location, "*" + fileversion.ToString() + " - " + "*");
string contentsoffile = File.ReadAllText(file[0]);
var queries = contentsoffile.Split(new[] { " GO " }, StringSplitOptions.RemoveEmptyEntries);
//split the script on "GO" commands
string[] splitter = new string[] { "\r\nGO\r\n" };
string[] commandTexts = contentsoffile.Split(splitter, StringSplitOptions.RemoveEmptyEntries);
try
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
foreach (string command in commandTexts)
{
using (SqlCommand scriptcommand = new SqlCommand(command, connection))
{
scriptcommand.CommandType = System.Data.CommandType.Text;
scriptcommand.ExecuteNonQuery();
}
}
}
}
catch (Exception ex)
{
}
}
}
public static void UpdateDatabaseInfoTable(DatabaseInfoList DBInfo)
{
string ConnectionString = $"Data Source={DBInfo.ServerName};User ID={DBInfo.UserName};Password={DBInfo.Password};Initial Catalog={DBInfo.DatabaseName};";
string updatestring = $"UPDATE [{DBInfo.DatabaseName}].[dbo].[DatabaseInfo] SET Version = '0';";
try
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (SqlCommand updatecommand = new SqlCommand(updatestring, connection))
{
updatecommand.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
}
}
public List <int> CheckPreviouslyRunScripts(DatabaseInfoList DBInfo)
{
List <int> alreadyrunscripts = new List <int>();
string ConnectionString = $"Data Source={DBInfo.ServerName};User ID={DBInfo.UserName};Password={DBInfo.Password};Initial Catalog={DBInfo.DatabaseName};";
string getstring = $"SELECT DISTINCT Version FROM [{DBInfo.DatabaseName}].[dbo].[DatabaseUpgrades] WHERE Success='1' ORDER BY VERSION;";
try
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (SqlCommand getcommand = new SqlCommand(getstring, connection))
{
SqlDataReader data = getcommand.ExecuteReader();
if (data != null)
{
while (data.Read())
{
alreadyrunscripts.Add((int)data["Version"]);
}
}
if (data != null)
{
data.Dispose();
}
}
}
}
catch (Exception ex)
{
}
return(alreadyrunscripts);
}
private void RunScripts(List <int> scriptstorun, DatabaseInfoList dbinforamtion)
{
DOScriptRun sr = new DOScriptRun();
sr.RunScripts(scriptstorun, dbinforamtion);
}
private void btnRunScript_Click(object sender, EventArgs e)
{
//Validate the Inputs
//Update the folder location in DB
//Get the Files to Run
//Get the Database Details where Scripts is to run.
//Check which scripts have already run - do it by checking from database upgrades and compare it with scripts to run
//Ask want to run the already run scripts, or select again
//If says yes, then update database info table and then run all
//If no, then update database info table, and then run only new ones
//Cancel, user will select again
if (!InputValidation())
{
return;
}
if (!UpdateLocationInDB())
{
CommonUtilities.ErrorMessage("Unable to Update Location in Database");
return;
}
List <int> fileversionstorun = GetSelectedFileVersions();
int index = comboBoxViewName.SelectedIndex;
DatabaseInfoList dbinfo = dbinfoitems[index];
List <int> alreadyrunscripts = CheckScriptsAlreadyRun(dbinfo);
string CommonScriptMessage = null;
foreach (int common in fileversionstorun.Intersect(alreadyrunscripts)) //gets a string which contains all the common scripts from which are selected and to run
{
CommonScriptMessage += common.ToString() + " ";
}
if (CommonScriptMessage != null)
{
DialogResult reply = MessageBox.Show("Application found few already run scripts.\n" + CommonScriptMessage + "\nWant to rerun the scripts?",
"Question", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
if (reply == DialogResult.Yes)
{
DOScriptRun.UpdateDatabaseInfoTable(dbinfo);
RunScripts(fileversionstorun, dbinfo);
}
if (reply == DialogResult.No)
{
DOScriptRun.UpdateDatabaseInfoTable(dbinfo);
List <int> uncommonscripts = fileversionstorun.Except(alreadyrunscripts).ToList();
RunScripts(uncommonscripts, dbinfo);
}
if (reply == DialogResult.Cancel)
{
}
}
else
{
DOScriptRun.UpdateDatabaseInfoTable(dbinfo);
RunScripts(fileversionstorun, dbinfo);
}
}
