public void DalSetUpDb()
{
Database.SetInitializer(new DropCreateDatabaseIfModelChanges <PhotoManagerDbContext>());
PhotoManagerDbContext context = new PhotoManagerDbContext();
context.Database.Initialize(true);
if (!WebSecurity.Initialized)
{
WebSecurity.InitializeDatabaseConnection("PhotoManagerDB_3_layer", "Users", "UserId", "UserName", autoCreateTables: true);
}
PrepareStoredProcedures(context);
}
private void PrepareStoredProcedures(PhotoManagerDbContext context)
{
// Drop Stored Procs
var path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "..\\PhotoManager.DAL\\StoredProcs\\");
foreach (var file in Directory.GetFiles(path))
{
// Try to drop proc if its already created
// Without this, for new procs, seed method fail on trying to delete
try
{
StreamReader reader = new StreamReader(file);
// Read first line of file to create drop command (turning CREATE [dbo].[TheProc] into DROP [dbo].[TheProc])
string dropCommand = reader.ReadLine().Replace("CREATE", "DROP");
context.Database.ExecuteSqlCommand(dropCommand, new object[0]);
}
catch { }
}
// Add Stored Procs
foreach (var file in Directory.GetFiles(path))
{
// File/Proc names must match method mapping names in DbContext
int lastSlash = file.LastIndexOf('\\');
string fileName = file.Substring(lastSlash + 1);
string procName = fileName.Substring(0, fileName.LastIndexOf('.'));
// First make sure proc mapping in DbContext contain matching parameters. If not throw exception.
// Get parameters for matching mapping
MethodInfo mi = typeof(PhotoManagerDbContext).GetMethod(procName);
if (mi == null)
{
throw new Exception(string.Format("Stored proc mapping for {0} missing in DBContext", procName));
}
ParameterInfo[] methodParams = mi.GetParameters();
// Finished getting parameters
// Get parameters from stored proc
int spParamCount = 0;
using (StreamReader reader = new StreamReader(file))
{
string line;
while ((line = reader.ReadLine()) != null)
{
// If end of parameter section, break out
if (line.ToUpper() == "BEGIN")
{
break;
}
if (line.Contains("@"))
{
spParamCount++;
}
}
}
// Finished get parameters from stored proc
if (methodParams.Count() != spParamCount)
{
string err = string.Format("Stored proc mapping for {0} in DBContext exists but has {1} parameter(s)" +
" The stored procedure {0} has {2} parameter(s)", procName, methodParams.Count().ToString(), spParamCount.ToString());
throw new Exception(err);
}
context.Database.ExecuteSqlCommand(File.ReadAllText(file), new object[0]);
}
}
