Retrieving data from the same database with the Identity

Question

I connected the identity model of my WebApp to my MySQL Database, and modified it like I want to connect.

My question is the next: If I want to get data from tables not used in the Identity but still from the same database, What is the correct way to do it? Should I open a new connection and get the data with a class? Is there a way to implement it in the model?

I attach the code to connect to the database:

public void Configuration(IAppBuilder app)
{
    DbConfiguration.SetConfiguration(new MySqlEFConfiguration());
    ConfigureAuth(app);
    context = new ApplicationDbContext();
}

And my connectionStrings:

<connectionStrings>
    <add name="DefaultConnection" connectionString="server=x.x.x.x;uid=user;password=pwd;database=db;port=3306;charset=utf8" providerName="MySql.Data.MySqlClient" />
</connectionStrings>

(Sorry if the question is too obvious, I'm starting to develop apps in ASP.NET MVC)

Answer 1

Yes, all you need to do is open the connection when it is required. So, i assume you did something like this with your MySQL Database connections

public class MySqlInitializer : IDatabaseInitializer<ApplicationDbContext>
{
    public void InitializeDatabase(ApplicationDbContext context)
    {
         if (!context.Database.Exists())
         {
          // if database did not exist before - create it
           context.Database.Create();
         }
         else
         {
          // query to check if MigrationHistory table is present in the database 
           var migrationHistoryTableExists =        ((IObjectContextAdapter)context).ObjectContext.ExecuteStoreQuery<int>(
            string.Format("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '{0}' AND table_name = '__MigrationHistory'",
      "[Insert your database schema here - such as 'users']"));

    // if MigrationHistory table is not there (which is the case first time we run) - create it
          if (migrationHistoryTableExists.FirstOrDefault() == 0)
          {
             context.Database.Delete();
             context.Database.Create();
          }
        }
   }}

and your ApplicationDbContext class will be like this

public class ApplicationUser : IdentityUser{}

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
   static ApplicationDbContext()
   {
       Database.SetInitializer(new MySqlInitializer());
   }

   public ApplicationDbContext()
  : base("DefaultConnection")
  {
  }
}

Now, to do some queries from database, you need to create an object of ApplicationDbContext

So, your Next Question "Is there a way to implement it in the model?" Yes there is you need to Create a View Model.

public class ItemProduct
{
    public int Id {get; set;};
    public string Name {get; set;};
}

So, lets say a Product Table exists in your database and we can query on it.

public class HomeController : Controller
{ 
   ApplicationDbContext context = new ApplicationDbContext();
  public ActionResult AnyName() 
  {
     List<ItemProduct> lstProducts = new List<ItemProduct>();
     var getProducts = (from p in context.Products
                        select p).ToList();
     foreach(var item in getProducts)
     {
         ItemProduct pro = new ItemProduct()
         {
            pro.Id = item.Id,
            pro.Name = item.Name
         };
         lstProducts.Add(pro);
     }

     // So, you have all your Products in ItemProduct Class list.
     // Now, return lstProducts to your View to show it on front end

  }
}

Hope this answer your question :)