using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace ADO2
{
public class Person
{
#region Estructura interna y Propiedades
/// <summary>
/// Identificador de la persona
/// </summary>
private int _Id;
/// <summary>
/// Accede al identificador de la persona
/// </summary>
public int Id
{
get { return _Id; }
set { _Id = value; }
}
/// <summary>
/// Nombre de la persona
/// </summary>
private string _Nombre;
/// <summary>
/// Accede al nombre de la persona
/// </summary>
public string Nombre
{
get { return _Nombre; }
set { _Nombre = value; }
}
/// <summary>
/// Apellido de la persona
/// </summary>
private string _Apellido;
/// <summary>
/// Accede al apellido de la persona
/// </summary>
public string Apellido
{
get { return _Apellido; }
set { _Apellido = value; }
}
/// <summary>
/// Mail de la persona
/// </summary>
private string _Mail;
/// <summary>
/// Accede al mail de la persona
/// </summary>
public string Mail
{
get { return _Mail; }
set { _Mail = value; }
}
/// <summary>
/// Comentarios
/// </summary>
private string _Comentarios;
/// <summary>
/// Accede a los comentarios
/// </summary>
public string Comentarios
{
get { return _Comentarios; }
set { _Comentarios = value; }
}
/// <summary>
/// Indicador si está borrado (lógicamente)
/// </summary>
private bool _IsDeleted;
/// <summary>
/// Accede al indicador de si está borrado
/// </summary>
public bool IsDeleted
{
get { return _IsDeleted; }
set { _IsDeleted = value; }
}
#endregion
#region Constructores
/// <summary>
/// Constructor por defecto
/// </summary>
public Person()
{
this.Id = 0;
this.Nombre = string.Empty;
this.Apellido = string.Empty;
this.Mail = string.Empty;
this.Comentarios = string.Empty;
this.IsDeleted = false;
}
#endregion
#region SQL
public void Insert()
{
SqlConnection mySqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Postal"].ConnectionString);
SqlCommand mySqlCommand = new SqlCommand();
mySqlCommand.Connection = mySqlConnection;
mySqlCommand.CommandText = "INSERT INTO Personas (Nombre, Apellido, Mail, Comentarios, IsDeleted) VALUES (@Nombre, @Apellido, @Mail, @Comentarios, DEFAULT)";
mySqlCommand.CommandType = CommandType.Text;
mySqlCommand.Parameters.AddWithValue("@Nombre", this.Nombre);
mySqlCommand.Parameters.AddWithValue("@Apellido", this.Apellido);
mySqlCommand.Parameters.AddWithValue("@Mail", this.Mail);
mySqlCommand.Parameters.AddWithValue("@Comentarios", this.Comentarios);
mySqlConnection.Open();
mySqlCommand.ExecuteNonQuery();
mySqlConnection.Close();
}
public void SelectById(int id)
{
SqlConnection mySqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Postal"].ConnectionString);
SqlCommand mySqlCommand = new SqlCommand();
mySqlCommand.Connection = mySqlConnection;
mySqlCommand.CommandText = "SELECT Id, Nombre, Apellido, Mail, Comentarios, IsDeleted FROM Personas WHERE Id=@Id";
mySqlCommand.CommandType = CommandType.Text;
mySqlCommand.Parameters.AddWithValue("@Id", id);
mySqlConnection.Open();
SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
if (mySqlDataReader.Read())
{
this.Id = mySqlDataReader.GetInt32(0);
if (!mySqlDataReader.IsDBNull(1))
this.Nombre = mySqlDataReader.GetString(1);
if (!mySqlDataReader.IsDBNull(2))
this.Apellido = mySqlDataReader.GetString(2);
if (!mySqlDataReader.IsDBNull(3))
this.Mail = mySqlDataReader.GetString(3);
if (!mySqlDataReader.IsDBNull(4))
this.Comentarios = mySqlDataReader.GetString(4);
this.IsDeleted = mySqlDataReader.GetBoolean(5);
}
mySqlConnection.Close();
}
public void Update()
{
SqlConnection mySqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Postal"].ConnectionString);
SqlCommand mySqlCommand = new SqlCommand();
mySqlCommand.Connection = mySqlConnection;
mySqlCommand.CommandText = "UPDATE Personas SET Nombre = @Nombre, Apellido = @Apellido, Mail = @Mail, Comentarios = @Comentarios WHERE Id=@Id";
mySqlCommand.CommandType = CommandType.Text;
mySqlCommand.Parameters.AddWithValue("@Id", this.Id);
mySqlCommand.Parameters.AddWithValue("@Nombre", this.Nombre);
mySqlCommand.Parameters.AddWithValue("@Apellido", this.Apellido);
mySqlCommand.Parameters.AddWithValue("@Mail", this.Mail);
mySqlCommand.Parameters.AddWithValue("@Comentarios", this.Comentarios);
mySqlConnection.Open();
mySqlCommand.ExecuteNonQuery();
mySqlConnection.Close();
}
public void Delete()
{
Delete(true);
}
public void Delete(bool isDeleted)
{
this.IsDeleted = isDeleted;
SqlConnection mySqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Postal"].ConnectionString);
SqlCommand mySqlCommand = new SqlCommand();
mySqlCommand.Connection = mySqlConnection;
mySqlCommand.CommandText = "UPDATE Personas SET IsDeleted = @IsDeleted WHERE Id=@Id";
mySqlCommand.CommandType = CommandType.Text;
mySqlCommand.Parameters.AddWithValue("@Id", this.Id);
mySqlCommand.Parameters.AddWithValue("@IsDeleted", this.IsDeleted);
mySqlConnection.Open();
mySqlCommand.ExecuteNonQuery();
mySqlConnection.Close();
}
#endregion
}
}