1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 | using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Microsoft.Data.SqlClient; namespace DAOAcademia { internal class DAOAlumno:IDAO<Alumno> { SqlConnection conn; public DAOAlumno() { conn=Conexion.getConn(); } public int Delete( int id) { try { conn.Open(); String sql = "delete alumno where id=@id" ; SqlCommand comando = new SqlCommand(sql, conn); comando.Parameters.AddWithValue( "@id" , id); int res = comando.ExecuteNonQuery(); conn.Close(); return res; } catch (Exception ex) { Console.WriteLine(ex.Message); return 0; } } public List<Alumno> GetAll() { try { conn.Open(); String sql = "select * from alumno" ; SqlCommand lectura = new SqlCommand(sql, conn); SqlDataReader sqlDataReader = lectura.ExecuteReader(); List<Alumno> alumnos= new List<Alumno>(); while (sqlDataReader.Read()) { Alumno alumno = new Alumno(sqlDataReader.GetInt32(0), sqlDataReader.GetString(1) ?? "" , sqlDataReader.GetString(2)?? "" , sqlDataReader.GetString(3)?? "" ); alumnos.Add( alumno ); } conn.Close(); return alumnos; } catch (Exception ex) { Console.WriteLine(ex.Message); return null; } } public Alumno GetById( int id) { Alumno alumno = null; try { conn.Open(); String sql = "select *from alumno where id=@id" ; SqlCommand lectura = new SqlCommand(sql, conn); lectura.Parameters.AddWithValue( "@id" , id); SqlDataReader sqlDataReader = lectura.ExecuteReader(); if (sqlDataReader.Read()) { alumno = new Alumno(sqlDataReader.GetInt32(0), sqlDataReader[1] as string ?? "" , sqlDataReader.GetString(2) ?? "" , sqlDataReader.GetString(3) ?? "" ); } conn.Close(); return alumno; } catch (Exception ex) { Console.WriteLine(ex.Message); return alumno; } } public int Insert(Alumno alumno) { try { conn.Open(); String sql = "insert into alumno (nombre,dni,email) values (@nombre,@dni,@email)" ; SqlCommand comando = new SqlCommand(sql, conn); comando.Parameters.AddWithValue( "@nombre" , alumno.Nombre); comando.Parameters.AddWithValue( "@dni" , alumno.DNI); comando.Parameters.AddWithValue( "@email" , alumno.Email); int res = comando.ExecuteNonQuery(); conn.Close(); return res; } catch (Exception ex) { Console.WriteLine(ex.Message); return 0; } } public int Save(Alumno alumno) { if (alumno.Id == 0) { return Insert(alumno); } else { return Update(alumno); } } public int Update(Alumno alumno) { try { conn.Open(); String sql = "update alumno set nombre=@nombre,dni=@dni,email=@email where id=@id" ; SqlCommand comando = new SqlCommand(sql, conn); comando.Parameters.AddWithValue( "@nombre" , alumno.Nombre); comando.Parameters.AddWithValue( "@dni" , alumno.DNI); comando.Parameters.AddWithValue( "@email" , alumno.Email); comando.Parameters.AddWithValue( "@id" , alumno.Id); int res = comando.ExecuteNonQuery(); conn.Close(); return res; } catch (Exception ex) { Console.WriteLine(ex.Message); return 0; } } } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace DAOAcademia { internal interface IDAO<T> { List<T> GetAll(); T GetById( int id); int Insert(T entity); int Update(T entity); int Save(T entity); int Delete( int id); } } |
using System;
using System.Collections.Generic;
using Microsoft.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DAOAcademia
{
internal class Conexion
{
public static SqlConnection getConn()
{
var connection = new SqlConnection();
connection.ConnectionString = “Data Source=.\\SQLEXPRESS;” +
“Initial Catalog=pasiona;Integrated Security=True;” +
“TrustServerCertificate=True”;
return connection;
}
}
}
]/c]