public static string GetConfirmationToken(string email)
{
using (var db = new LoginModelsContext())
{
var tsqlQuery = string.Format("SELECT [ConfirmationToken] FROM [webpages_Membership] WHERE [UserId] IN (SELECT [UserId] FROM [LoginModels] WHERE [Email] LIKE '{0}')", email);
return db.Database.SqlQuery<string>(tsqlQuery).First();
}
}
public static ReadOnlyCollection<string> GetAccounts()
{
using (var db = new LoginModelsContext())
{
var tsqlQuery = string.Format("SELECT [Email] FROM [LoginModels]");
return new ReadOnlyCollection<string>(db.Database.SqlQuery<string>(tsqlQuery).ToList());
}
}
public static string GetUserRole(int userId)
{
using (var db = new LoginModelsContext())
{
try
{
var tsqlQuery = string.Format("SELECT [RoleName] FROM [webpages_Roles] WHERE [RoleId] IN (SELECT [RoleId] FROM [webpages_UsersInRoles] WHERE [UserId] = {0})", userId.ToString());
return db.Database.SqlQuery<string>(tsqlQuery).FirstOrDefault();
}
catch { return "user"; }
}
}
public static string GetUserName(int userId)
{
using (var db = new LoginModelsContext())
{
try
{
var tsqlQuery = string.Format("SELECT [UserName] FROM [LoginModels] WHERE [UserId] = {0})", userId.ToString());
return db.Database.SqlQuery<string>(tsqlQuery).FirstOrDefault();
}
catch { return string.Empty; }
}
}
public static ReadOnlyCollection<string> GetAllRoles()
{
using (var db = new LoginModelsContext())
{
try
{
var tsqlQuery = string.Format("SELECT [RoleName] FROM [webpages_Roles]");
return new ReadOnlyCollection<string>(db.Database.SqlQuery<string>(tsqlQuery).ToList());
}
catch { return null; }
}
}
public SimpleMembershipInitializer()
{
Database.SetInitializer<LoginModelsContext>(null);
try
{
using (var context = new LoginModelsContext())
{
if (!context.Database.Exists())
{
// Create the SimpleMembership database without Entity Framework migration schema
((IObjectContextAdapter)context).ObjectContext.CreateDatabase();
}
}
WebSecurity.InitializeDatabaseConnection("LoginDB", "LoginModels", "UserId", "Email", autoCreateTables: true);
InitializeDefault();
}
catch (Exception ex)
{
throw new InvalidOperationException("The ASP.NET Simple Membership database could not be initialized. For more information, please see http://go.microsoft.com/fwlink/?LinkId=256588", ex);
}
}
public static ReadOnlyCollection<string> GetUsersInRole(string role)
{
using (var db = new LoginModelsContext())
{
try
{
var roleIdQuery = string.Format("SELECT [RoleId] FROM [webpages_Roles] WHERE [RoleName] LIKE '{0}'", role);
int roleId = db.Database.SqlQuery<int>(roleIdQuery).FirstOrDefault();
var tsqlQuery = string.Format("SELECT [Email] FROM [LoginModels] WHERE [UserId] IN (SELECT [UserId] FROM [webpages_UsersInRoles] WHERE [RoleId] = {0})", roleId);
return new ReadOnlyCollection<string>(db.Database.SqlQuery<string>(tsqlQuery).ToList());
}
catch { return null; }
}
}