Considering that you know a little about .Net Core, open the 'Visual Studio Code' and create a new .NET Core web project.
Apply the nuget packages listed below:
- Dapper
- Dapper.Contrib
- MySqlConnector
Open the file 'appsettings.json' and put yours MySQL's configurations like the example below:
"ConnectionStrings": { "ConnectionString1": "host=localhost;port=3306;user id=USER;password=PASSWORD;database=DATABASENAME;" }
Create a directory called 'Code' in the root folder of your project and in this folder create a class file called 'ConnectionStringList.cs' with the following content:
namespace AspNetCoreDapperMySql.Code { public class ConnectionStringList { public string ConnectionString1 { get; set; } } }
Now, create other directory called 'Models' and three classes files with the following contents:
Pais.cs
using Dapper.Contrib.Extensions; using System.Collections.Generic; namespace AspNetCoreDapperMySql.Models { [Table("GLB_Pais")] public class Pais { [Key] public int Id { get; set; } public string Nome { get; set; } public ICollection<Uf> Ufs { get; set; } } }
Uf.cs
using Dapper.Contrib.Extensions; using System.Collections.Generic; namespace AspNetCoreDapperMySql.Models { [Table("GLB_UF")] public class Uf { [Key] public int Id { get; set; } public int Id_GLB_Pais { get; set; } public string Nome { get; set; } public string Sigla { get; set; } public Pais Pais { get; set; } public ICollection<Cidade> Cidades { get; set; } } }
Cidade.cs
using Dapper.Contrib.Extensions; namespace AspNetCoreDapperMySql.Models { [Table("GLB_Cidade")] public class Cidade { [Key] public int Id { get; set; } public int Id_GLB_UF { get; set; } public string Nome { get; set; } public Uf Uf { get; set; } } }
And, create another directory called 'Repository' and two classes files with the following contents:
namespace AspNetCoreDapperMySql.Code { public class ConnectionStringList { public string ConnectionString1 { get; set; } } }0
using AspNetCoreDapperMySql.Models; using System.Collections.Generic; namespace AspNetCoreDapperMySql.Repository { internal interface IRepositoryBase { List<Cidade> SearchCidades(string nome); List<Uf> SearchUfs(string nome); List<Pais> SearchPaises(string nome); } }
namespace AspNetCoreDapperMySql.Code { public class ConnectionStringList { public string ConnectionString1 { get; set; } } }1
using AspNetCoreDapperMySql.Code; using AspNetCoreDapperMySql.Models; using Dapper; using Microsoft.Extensions.Options; using MySql.Data.MySqlClient; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; namespace AspNetCoreDapperMySql.Repository { public class RepositoryBase : IRepositoryBase { private readonly IDbConnection _db; public RepositoryBase(IOptions<ConnectionStringList> connectionStrings) { _db = new MySqlConnection(connectionStrings.Value.ConnectionString1); } public void Dispose() { _db.Close(); } public List<Cidade> SearchCidades(string nome) { nome = "Juiz de Fora"; if (string.IsNullOrEmpty(nome)) return _db.Query<Cidade>("SELECT * FROM GLB_Cidade ORDER BY Nome ASC LIMIT 10").ToList(); nome = nome.Trim(); return _db.Query<Cidade>("SELECT * FROM GLB_Cidade WHERE Nome LIKE @Nome ORDER BY Nome ASC LIMIT 10", new { Nome = string.Format("%{0}%", nome) }).ToList(); } public List<Uf> SearchUfs(string nome) { if (string.IsNullOrEmpty(nome)) return _db.Query<Uf>("SELECT * FROM GLB_UF ORDER BY Nome ASC LIMIT 10").ToList(); nome = nome.Trim(); return _db.Query<Uf>("SELECT * FROM GLB_UF WHERE Nome LIKE @Nome ORDER BY Nome ASC LIMIT 10", new { Nome = string.Format("%{0}%", nome) }).ToList(); } public List<Pais> SearchPaises(string nome) { if (string.IsNullOrEmpty(nome)) return _db.Query<Pais>("SELECT * FROM GLB_Pais ORDER BY Nome ASC LIMIT 10").ToList(); nome = nome.Trim(); return _db.Query<Pais>("SELECT * FROM GLB_Pais WHERE Nome LIKE @Nome ORDER BY Nome ASC LIMIT 10", new { Nome = string.Format("%{0}%", nome) }).ToList(); } } }
Nuget packages applied
- Dapper
- Dapper.Contrib
- MySqlConnector
License
This example application is MIT Licensed.
About the author
Hello everyone, my name is Diego Anzolin Ferreira. I'm a .NET developer from Brazil. I hope you will enjoy this simple example application as much as I enjoy developing it. If you have any problems, you can post a GitHub issue. You can reach me out at diego@anzolin.com.br.