/// <summary>
/// Creates database tables and reports back with an installer result
/// </summary>
/// <param name="connectionStringOveride">If you don't want to run this sql against the default DB then pass in a different connection string here</param>
/// <param name="sqlFilePath">Overide what SQL you want to run, pass in the file path here i.e. /myfolder/myscript.sql</param>
/// <param name="currentVersion">The current app version</param>
/// <returns></returns>
public InstallerResult CreateDbTables(string connectionStringOveride, string sqlFilePath, string currentVersion)
{
// Setup the installer result
var insResult = new InstallerResult {
Successful = true, Message = "Successfully created the database tables"
};
// Sort the connection string out
string connString;
try
{
// Set the connection string
connString = connectionStringOveride ?? ConfigurationManager.ConnectionStrings["MVCForumContext"].ConnectionString;
}
catch (Exception ex)
{
insResult.Exception = ex;
insResult.Message = "Error trying to get the connection string";
insResult.Successful = false;
return(insResult);
}
// Get and open the SQL
string script;
var filePath = string.Empty;
try
{
filePath = string.IsNullOrEmpty(sqlFilePath) ? HttpContext.Current.Server.MapPath(InstallerHelper.GetMainDatabaseFilePath(currentVersion)) : sqlFilePath;
var file = new FileInfo(filePath);
script = file.OpenText().ReadToEnd();
}
catch (Exception ex)
{
insResult.Exception = ex;
insResult.Message = string.Format("Error trying to read the SQL file '{0}' create db", filePath);
insResult.Successful = false;
return(insResult);
}
using (var conn = new SqlConnection(connString))
{
// split script on GO command
IEnumerable <string> commandStrings = Regex.Split(script, "^\\s*GO\\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase);
conn.Open();
foreach (var commandString in commandStrings)
{
if (commandString.Trim() != "")
{
try
{
new SqlCommand(commandString, conn).ExecuteNonQuery();
}
catch (Exception ex)
{
//NOTE: Surpress errors where tables already exist, and just carry on
if (!ex.Message.Contains("There is already an object named") &&
!ex.Message.Contains("Column already has a DEFAULT bound to it"))
{
insResult.Exception = ex;
insResult.Message = "Error trying to create the database tables. Check you have correct permissions in SQL Server";
insResult.Successful = false;
return(insResult);
}
}
}
}
}
return(insResult);
}