none
How can I create a new table in a MySQL database in MVC 5 RRS feed

  • Question

  • I have an MVC 5 app, which uses MySQL hosted in Azure as a data source. The point is that inside the database, I want to create a new table called "request". I have already activated migrations for my database in code. I also have the following code in my app.

    Request.cs: (inside Models folder)

    public class Request
    {
        public int RequestID { get; set; }
    
        [Required]
        [Display(Name = "Request type")]
        public string RequestType { get; set; }
    }

    Test.cshtml:

    @model Workfly.Models.Request
    @{
        ViewBag.Title = "Test";
    }
    
    <h2>@ViewBag.Title.</h2>
    <h3>@ViewBag.Message</h3>
    
    @using (Html.BeginForm("SaveAndShare", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
    {
        @Html.AntiForgeryToken()
        <h4>Create a new request.</h4>
        <hr />
        @Html.ValidationSummary("", new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(m => m.RequestType, new { @class = "col-md-2 control-label" })
            <div class="col-md-10">
                @Html.TextBoxFor(m => m.RequestType, new { @class = "form-control", @id = "keywords-manual" })
            </div>
        </div>
        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" class="btn btn-default" value="Submit!" />
            </div>
        </div>
    }
    
    @section Scripts {
        @Scripts.Render("~/bundles/jqueryval")
    }

    HomeController.cs:

        [HttpPost]
        public ActionResult SaveAndShare(Request request)
        {
            if (ModelState.IsValid)
            {
                var req = new Request { RequestType = request.RequestType };
            }
    
            return RedirectToAction("Share");
        }

    The point is that, I want the user to fill the form inside the Test view and click submit, and when the submit is clicked, I want a new entry in the new table to be created. But first of course I need to create the table. Should I create it using SQL query through MySQL workbench? If yes, then how can I connect the new table with my code? I guess I need some DB context but don't know how to do it. If someone can post some code example, I would be glad.

    UPDATE:

    I created a new class inside the Models folder and named it RequestContext.cs, and its contents can be found below:

    public class RequestContext : DbContext
    {
        public DbSet<Request> Requests { get; set; }
    }

    Then, I did "Add-Migration Request", and "Update-Database" commands, but still nothing. Please also note that I have a MySqlInitializer class, which looks something like this:

    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'",
                  ""));
    
                // 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();
                }
            }
        }
    

    Thursday, July 31, 2014 11:21 PM