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:
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:
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; } } }
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; } } }
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
LicenseThis example application is MIT Licensed. About the authorHello 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 [email protected]. |