Last active
August 29, 2015 13:59
-
-
Save cleytonferrari/10947467 to your computer and use it in GitHub Desktop.
Exemplo de classes para manipulacao de banco de dados, simples
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using System; | |
using System.Collections.Generic; | |
using System.Data.SqlClient; | |
using System.Globalization; | |
using System.Linq; | |
using System.Web; | |
using ExemploADOSimples.Models; | |
using ExemploADOSimples.Repositorio; | |
namespace ExemploADOSimples.Aplicacao | |
{ | |
public class CaoAplicacao | |
{ | |
private readonly Contexto contexto; | |
public CaoAplicacao() | |
{ | |
contexto = new Contexto(); | |
} | |
public List<Cao> Listar() | |
{ | |
//TODO: 2º Executar um comando no banco de dados | |
var strQuery = "SELECT c.caoId, c.nome, c.peso, c.cor, c.sexo, c.racaId, r.raca FROM CAO c, RACA r WHERE c.racaId = r.racaId"; | |
var retorno = contexto.ExecutaComandoComRetorno(strQuery); | |
var listaDeCao = TransformaReaderEmListaDeObjeto(retorno); | |
return listaDeCao; | |
} | |
public Cao ListarPorId(int id) | |
{ | |
//TODO: 2º Executar um comando no banco de dados | |
var strQuery = string.Format("SELECT * FROM CAO WHERE CAOID = {0}", id); | |
var retorno = contexto.ExecutaComandoComRetorno(strQuery); | |
var listaDeCao = TransformaReaderEmListaDeObjeto(retorno); | |
return listaDeCao.FirstOrDefault(); | |
} | |
public void Excluir(int id) | |
{ | |
//TODO: 2º Executar um comando no banco de dados | |
var strQuery = string.Format("DELETE FROM CAO WHERE CAOID = {0}", id); | |
contexto.ExecutaComando(strQuery); | |
} | |
private void Inserir(Cao cao) | |
{ | |
var strQuery = string.Format( | |
"INSERT INTO CAO (Nome, " + | |
"Peso, Cor, Sexo, RacaId) VALUES ('{0}','{1}','{2}','{3}','{4}') ", | |
cao.Nome, cao.Peso.ToString(CultureInfo.InvariantCulture), cao.Cor, | |
cao.Sexo, cao.RacaId); | |
contexto.ExecutaComando(strQuery); | |
} | |
private void Alterar(Cao cao) | |
{ | |
var strQuery = string.Format("UPDATE CAO SET Nome = '{0}', Peso = '{1}', Cor = '{2}', Sexo = '{3}', RacaId = '{4}' WHERE CaoId = {5} ", cao.Nome, cao.Peso.ToString(CultureInfo.InvariantCulture), cao.Cor, cao.Sexo, cao.RacaId, cao.Id); | |
contexto.ExecutaComando(strQuery); | |
} | |
public void Salvar(Cao cao) | |
{ | |
if(cao.Id > 0) | |
Alterar(cao); | |
else | |
Inserir(cao); | |
} | |
private List<Cao> TransformaReaderEmListaDeObjeto(SqlDataReader reader) | |
{ | |
var listaDeCao = new List<Cao>(); | |
while (reader.Read()) | |
{ | |
var tempCao = new Cao(); | |
tempCao.Id = int.Parse(reader["caoId"].ToString()); | |
tempCao.Nome = reader["nome"].ToString(); | |
tempCao.Cor = reader["cor"].ToString(); | |
tempCao.Sexo = reader["sexo"].ToString(); | |
tempCao.Peso = decimal.Parse(reader["peso"].ToString()); | |
tempCao.RacaId = int.Parse(reader["racaId"].ToString()); | |
listaDeCao.Add(tempCao); | |
} | |
reader.Close(); | |
return listaDeCao; | |
} | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using System; | |
using System.Collections.Generic; | |
using System.Drawing; | |
using System.Globalization; | |
using System.Linq; | |
using System.Web; | |
using System.Web.Mvc; | |
using System.Data.SqlClient; | |
using ExemploADOSimples.Aplicacao; | |
using ExemploADOSimples.Models; | |
using ExemploADOSimples.Repositorio; | |
namespace ExemploADOSimples.Controllers | |
{ | |
public class CaoController : Controller | |
{ | |
private readonly CaoAplicacao caoAplicacao; | |
private readonly RacaAplicacao racaAplicacao; | |
public CaoController() | |
{ | |
caoAplicacao = new CaoAplicacao(); | |
racaAplicacao = new RacaAplicacao(); | |
} | |
public ActionResult Index() | |
{ | |
return View(caoAplicacao.Listar()); | |
} | |
public ActionResult Detalhe(int id) | |
{ | |
return View(caoAplicacao.ListarPorId(id)); | |
} | |
public ActionResult Excluir(int id) | |
{ | |
caoAplicacao.Excluir(id); | |
return RedirectToAction("Index"); | |
} | |
public ActionResult Cadastrar() | |
{ | |
ViewBag.ListaDeRacas = racaAplicacao.Listar(); | |
return View(new Cao()); | |
} | |
[HttpPost] | |
public ActionResult Cadastrar(Cao cao) | |
{ | |
if (ModelState.IsValid) | |
{ | |
caoAplicacao.Salvar(cao); | |
return RedirectToAction("Index"); | |
} | |
ViewBag.ListaDeRacas = racaAplicacao.Listar(); | |
return View(cao); | |
} | |
public ActionResult Editar(int id) | |
{ | |
ViewBag.ListaDeRacas = racaAplicacao.Listar(); | |
return View(caoAplicacao.ListarPorId(id)); | |
} | |
[HttpPost] | |
public ActionResult Editar(Cao cao) | |
{ | |
if (ModelState.IsValid) | |
{ | |
caoAplicacao.Salvar(cao); | |
return RedirectToAction("Index"); | |
} | |
ViewBag.ListaDeRacas = racaAplicacao.Listar(); | |
return View(cao); | |
} | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using System; | |
using System.Collections.Generic; | |
using System.Data; | |
using System.Data.SqlClient; | |
using System.Linq; | |
using System.Web; | |
namespace ExemploADOSimples.Repositorio | |
{ | |
public class Contexto : IDisposable | |
{ | |
private readonly SqlConnection minhaConexao; | |
public Contexto() | |
{ | |
//TODO: 1º Criar uma conexao com o banco de dados | |
const string stringConexao = @"data source=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=caopeao"; | |
minhaConexao = new SqlConnection(stringConexao); | |
minhaConexao.Open(); | |
} | |
public void ExecutaComando(string strQuery) | |
{ | |
//TODO: 2º Executar um comando no banco de dados | |
var comando = new SqlCommand(strQuery, minhaConexao); | |
comando.ExecuteNonQuery(); | |
} | |
public SqlDataReader ExecutaComandoComRetorno(string strQuery) | |
{ | |
var comando = new SqlCommand(strQuery, minhaConexao); | |
return comando.ExecuteReader(); | |
} | |
public void Dispose() | |
{ | |
if (minhaConexao.State == ConnectionState.Open) | |
minhaConexao.Close(); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment