Código DAO

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]

Publicado por

Juan Pablo Fuentes

Formador de programación y bases de datos