ASP.NET Core MVC & MySQL Data Core

yo aspnet

create table guestbook(
  guestbook_id INT NOT NULL AUTO_INCREMENT,
  guest_name VARCHAR(100) NOT NULL,
  guest_email VARCHAR(100) NOT NULL,
  message VARCHAR(256) NOT NULL,
  PRIMARY KEY ( guestbook_id )
);

"Microsoft.AspNetCore.Mvc": "1.1.0",
"Microsoft.AspNetCore.StaticFiles": "1.1.0",
"MySql.Data.Core": "7.0.4-IR-191"

{
  "dependencies": {
    "Microsoft.NETCore.App": {
      "version": "1.1.0",
      "type": "platform"
    },
    "Microsoft.AspNetCore.Diagnostics": "1.1.0",
    "Microsoft.AspNetCore.Server.IISIntegration": "1.1.0",
    "Microsoft.AspNetCore.Server.Kestrel": "1.1.0",
    "Microsoft.Extensions.Logging.Console": "1.1.0",
    "Microsoft.Extensions.Configuration.EnvironmentVariables": "1.1.0",
    "Microsoft.Extensions.Configuration.FileExtensions": "1.1.0",
    "Microsoft.Extensions.Configuration.Json": "1.1.0",
    "Microsoft.Extensions.Configuration.CommandLine": "1.1.0",
    "Microsoft.AspNetCore.Mvc": "1.1.0",
    "Microsoft.AspNetCore.StaticFiles": "1.1.0",
    "MySql.Data.Core": "7.0.4-IR-191"
  },

“tools”: {
“Microsoft.AspNetCore.Server.IISIntegration.Tools”: “1.1.0-preview4-final”
},

“frameworks”: {
“netcoreapp1.1”: {
“imports”: [
“dotnet5.6”,
“portable-net45+win8”
]
}
},

“buildOptions”: {
“emitEntryPoint”: true,
“preserveCompilationContext”: true
},

“runtimeOptions”: {
“configProperties”: {
“System.GC.Server”: true
}
},

“publishOptions”: {
“include”: [
“wwwroot”,
“web.config”
]
},

“scripts”: {
“postpublish”: [ “dotnet publish-iis –publish-folder %publish:OutputPath% –framework %publish:FullTargetFramework%” ]
},

“tooling”: {
“defaultNamespace”: “ASPNETCoreMySQL”
}
}

{
    "ConnectionStrings":
    {
        "Default": "server=localhost; userid=root; password=rahasia; database=elibrary;SslMode=None"
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Configuration;

namespace ASPNETCoreMySQL
{
public class Startup
{
public static string ConnectionString {get; private set;}
public static IConfigurationRoot Configuration { get; set; }

public Startup(IHostingEnvironment env)
{
var builder = new ConfigurationBuilder()
.SetBasePath(env.ContentRootPath)
.AddJsonFile(“appsettings.json”, optional: true, reloadOnChange: true)
.AddEnvironmentVariables();

Configuration = builder.Build();

ConnectionString = Configuration[“ConnectionStrings:Default”];
}

// This method gets called by the runtime. Use this method to add services to the container.
// For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940
public void ConfigureServices(IServiceCollection services)
{
services.AddMvc();
}

// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
{
loggerFactory.AddConsole();

if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
else
{
app.UseExceptionHandler(“/Home/Error”);
}

app.UseStaticFiles();

app.UseMvc(routes =>
{
routes.MapRoute(
name: “default”,
template: “{controller=Home}/{action=Index}/{id?}”);
});
}
}
}

using System;

namespace ASPNETCoreMySQL.Models
{
public class GuestBook{
public String Email {set; get;}
public String Name {set; get;}
public String Message {set; get;}
}
}

using System;
using System.Collections.Generic;
using Microsoft.AspNetCore.Mvc;
using ASPNETCoreMySQL.Models;

using MySql.Data.MySqlClient;

namespace ASPNETCoreMySQL.Controllers
{
public class HomeController : Controller
{
[HttpGet]
public IActionResult Index()
{
IList<GuestBook> items = new List<GuestBook>();

// koneksi database
MySqlConnection conn = new MySqlConnection{
ConnectionString = Startup.ConnectionString
};
conn.Open();

// menyiapkan query
MySqlCommand cmd = new MySqlCommand(“SELECT * FROM guestbook;”, conn);

// membaca data
MySqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read()){
// menyimpan record ke object model
GuestBook item = new GuestBook();
item.Email = Convert.ToString(dataReader[“guest_email”]);
item.Name = Convert.ToString(dataReader[“guest_name”]);
item.Message = Convert.ToString(dataReader[“message”]);

// menyimpan object model ke collection
items.Add(item);
}
dataReader.Close();

return View(items);
}

[HttpGet]
public IActionResult Create(){
return View();
}

[HttpPost]
public IActionResult Create(GuestBook item){
if(ModelState.IsValid){
MySqlConnection conn = new MySqlConnection{
ConnectionString = Startup.ConnectionString
};
conn.Open();

MySqlCommand command = conn.CreateCommand();
command.CommandText = “INSERT INTO guestbook (guest_name, guest_email, message) VALUES (?name, ?email, ?message)”;
command.Parameters.AddWithValue(“?name”, item.Name);
command.Parameters.AddWithValue(“?email”, item.Email);
command.Parameters.AddWithValue(“?message”, item.Message);
command.ExecuteNonQuery();

conn.Close();

return RedirectToAction(“Index”);
}
return View();
}

[HttpGet]
public IActionResult Error()
{
return View();
}
}
}

using MySql.Data.MySqlClient;

using ASPNETCoreMySQL.Models;

@{
    Layout = null;
}

@model IList<ASPNETCoreMySQL.Models.GuestBook>

<html>
<head>
<title>ASP.NET Core MVC – MySQL: GuestBook</title>
</head>

<body>
<h1>Daftar GuestBook</h1>
<div>
<p>@Html.ActionLink(“Tambah data”, “Create”)</p>
<table style=”width:100%; border: solid 1px #000″>
<tr>
<td>Nama</td>
<td>Email</td>
<td>Message</td>
</tr>
@foreach (var item in Model)
{
<tr>
<td>@Html.DisplayFor(modelItem => item.Name)</td>
<td>@Html.DisplayFor(modelItem => item.Email)</td>
<td>@Html.DisplayFor(modelItem => item.Message)</td>
</tr>
}
</div>
</body>
</html>

@{
    Layout = null;
}

@model ASPNETCoreMySQL.Models.GuestBook

<html>
<head>
<title>ASP.NET Core MVC – MySQL: GuestBook</title>
</head>

<body>
<h1>Form GuestBook</h1>
<div>
@using (Html.BeginForm())
{
<p>
@Html.LabelFor(m=>m.Name)
@Html.TextBoxFor(m=>m.Name)
</p>
<p>
@Html.LabelFor(m=>m.Email)
@Html.TextBoxFor(m=>m.Email)
</p>
<p>
@Html.LabelFor(m=>m.Message)
@Html.TextBoxFor(m=>m.Message)
</p>
<p>
@ViewBag.GuestBookMessage
</p>
<p>
<input type=”submit” value=”Send” />
</p>
}
</div>
</body>
</html>