/// <summary>
/// selecciona los accesos de una familia
/// </summary>
/// <param name="IdFamiliaElement">strin</param>
/// <returns>datatable</returns>
public static DataTable GetAccesos(System.String IdFamiliaElement)
{
using (SqlConnection conn = ConnectionBD.Instance().Conect())
{
using (SqlCommand cmd = new SqlCommand("Familia_Familia_SelectParticular @IdFamilia", conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@IdFamilia", IdFamiliaElement);
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
return(dt);
}
}
//Database myDatabase = DatabaseFactory.CreateDatabase();
//DbCommand myCommand = myDatabase.GetStoredProcCommand("Familia_Familia_SelectParticular");
//myDatabase.AddInParameter(myCommand, "@IdFamilia", DbType.String, IdFamiliaElement);
//return myDatabase.ExecuteDataSet(myCommand).Tables[0];
}
/// <summary>
/// selecciona una familia
/// </summary>
/// <param name="IdFamiliaElement">string</param>
/// <returns>data set</returns>
public static DataSet Select(System.String IdFamiliaElement)
{
try
{
using (SqlConnection conn = ConnectionBD.Instance().Conect())
{
using (SqlCommand cmd = new SqlCommand("Familia_Select @IdFamiliaElement", conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@IdFamiliaElement", IdFamiliaElement);
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
da.Dispose();
return(ds);
}
}
//Database myDatabase = DatabaseFactory.CreateDatabase();
//DbCommand myCommand = myDatabase.GetStoredProcCommand("Familia_Select");
//myDatabase.AddInParameter(myCommand, "@IdFamilia", DbType.String, IdFamiliaElement);
//return myDatabase.ExecuteDataSet(myCommand);
}
catch (Exception ex)
{
throw ex;
}
}
private void button1_Click(object sender, EventArgs e)
{
if (textBoxnamecomplete.Text.Equals("") ||
textBoxnameuser.Text.Equals("") ||
textBoxpassword.Text.Equals(""))
{
MessageBox.Show("No se pueden dejar campos vacios");
}
else
{
try
{
ConnectionBD.ExecuteNonQuery($"INSERT INTO APPUSER(fullname,username,password,usertype,activo) VALUES( " +
$"'{textBoxnamecomplete.Text.ToString()}' ," +
$"'{textBoxnameuser.Text.ToString()}'," +
$"'{textBoxpassword.Text.ToString()}', " +
$"'{comboBoxadmi.SelectedItem.ToString()}' , " +
$"{true})");
MessageBox.Show("Usuario guardado exitosamente");
}
catch (Exception ex)
{
MessageBox.Show("Ha ocurrido un error");
}
}
}
/// <summary>
/// elimina una familia
/// </summary>
/// <param name="_object">familia</param>
public static void Delete(Entities.UFP.Familia _object)
{
if (_object.Accesos != null)
{
DeleteAccesos(_object);
}
using (SqlConnection conn = ConnectionBD.Instance().Conect())
{
using (SqlCommand cmd = new SqlCommand("Familia_Delete @IdFamiliaElement", conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@IdFamiliaElement", _object.IdFamiliaElement);
conn.Open();
cmd.ExecuteNonQuery();
}
}
//Database myDatabase = DatabaseFactory.CreateDatabase();
//DbCommand myCommand = myDatabase.GetStoredProcCommand("Familia_Delete");
//myDatabase.AddInParameter(myCommand, "@IdFamilia", DbType.String, _object.IdFamiliaElement);
//myDatabase.ExecuteNonQuery(myCommand);
}
/// <summary>
/// selecciona las faimilias
/// </summary>
/// <returns>data set</returns>
public static DataSet SelectAll()
{
try
{
using (SqlConnection conn = ConnectionBD.Instance().Conect())
{
using (SqlCommand cmd = new SqlCommand("Familia_SelectAll", conn))
{
cmd.CommandType = CommandType.Text;
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
da.Dispose();
return(ds);
}
}
//Database myDatabase = DatabaseFactory.CreateDatabase();
//DbCommand myCommand = myDatabase.GetStoredProcCommand("Familia_SelectAll");
//return myDatabase.ExecuteDataSet(myCommand);
}
catch (Exception ex)
{
throw ex;
}
}
private void button1agregar_Click(object sender, EventArgs e)
{
if (textBoxnombreproduct.Text.Equals(""))
{
MessageBox.Show("No se pueden dejar campos vacios");
}
else
{
try
{
string query = $"SELECT B.idBusiness FROM BUSINESS B WHERE B.name='{comboBoxempresa.Text.ToString()}' ";
var dt2 = ConnectionBD.ExecuteQuery(query);
var dr2 = dt2.Rows[0];
var idBusiness = dr2[0].ToString();
ConnectionBD.ExecuteNonQuery($"INSERT INTO PRODUCT(idBusiness,name) VALUES( " +
$"'{idBusiness}' ," +
$"'{textBoxnombreproduct.Text.ToString()}') ");
MessageBox.Show("Producto guardado exitosamente");
}
catch (Exception ex)
{
MessageBox.Show("Ha ocurrido un error");
}
}
}
public void TheIdDoesNotExist()
{
//Inicializar datos
int id = 999;
//Ejecutar método
var idExists = ConnectionBD.GetAuthenticationData(id);
//Comprobar resultado
Assert.Equal(0, idExists.EmployeeId);
}
public void TheIdExists()
{
//Inicializar datos
int id = 1;
//Ejecutar método
var idExists = ConnectionBD.GetAuthenticationData(id);
//Comprobar resultado
Assert.Equal("joantan", idExists.UserName);
Assert.Equal("12345", idExists.Password);
}
public void TheEmployeeDoesNotExist()
{
//Inicializar datos
string documentType = "CX";
string documentNumber = "87457212";
//Ejecutar método
var data = ConnectionBD.GetEmployee(documentType, documentNumber);
//Comprobar resultado
Assert.Equal(0, data.Id);
}
public void TheEmployeeExists()
{
//Inicializar datos
string documentType = "CC";
string documentNumber = "98000000";
//Ejecutar método
var data = ConnectionBD.GetEmployee(documentType, documentNumber);
//Comprobar resultado
Assert.Equal(1, data.Id);
}
private void AddProduct_Load(object sender, EventArgs e)
{
var name = ConnectionBD.ExecuteQuery("SELECT name FROM BUSINESS ");
var nameCombo = new List <string>();
foreach (DataRow dr in name.Rows)
{
nameCombo.Add(dr[0].ToString());
}
comboBoxempresa.DataSource = nameCombo;
}
private void EliminateUser_Load(object sender, EventArgs e)
{
var user = ConnectionBD.ExecuteQuery("SELECT username FROM APPUSER where activo='true'");
var userCombo = new List <string>();
foreach (DataRow dr in user.Rows)
{
userCombo.Add(dr[0].ToString());
}
comboBoxusuarios.DataSource = userCombo;
}
// Crear jugador en la base de datos
public static bool CreatePlayer(string username)
{
var dt = ConnectionBD.ExecuteQuery($"SELECT * FROM PLAYER WHERE username = '******'");
if (dt.Rows.Count > 0)
{
return(true);
}
else
{
ConnectionBD.ExecuteNonQuery("INSERT INTO PLAYER(username) VALUES" +
$"('{username}')");
return(false);
}
}
private void button1_Click(object sender, EventArgs e)
{
if (textBoxuser.Text.Equals("") ||
textBoxdireccion.Text.Equals(""))
{
MessageBox.Show("No se pueden dejar campos vacios");
}
else
{
try
{
DateTime fecha = new DateTime();
string sfecha = fecha.ToString("MM/dd/yyyy");
string query2 = $"SELECT idproduct FROM product WHERE name='{comboBoxmenu.Text.ToString()}' ";
var dt2 = ConnectionBD.ExecuteQuery(query2);
var dr2 = dt2.Rows[0];
var idProduct = dr2[0].ToString();
string query3 = $"SELECT iduser FROM appuser WHERE username='******' ";
var dt3 = ConnectionBD.ExecuteQuery(query2);
var dr3 = dt3.Rows[0];
var idUSER = dr3[0].ToString();
string query = $"SELECT a.idaddress FROM ADDRESS a, APPUSER U WHERE U.iduser=a.iduser and a.address='{textBoxdireccion.Text.ToString()}' ";
var dt = ConnectionBD.ExecuteQuery(query);
var dr = dt.Rows[0];
var idAddres = dr[0].ToString();
ConnectionBD.ExecuteNonQuery($"INSERT INTO ADDORDER(createdate,idproduct,idaddress,activo) VALUES( " +
$"'{sfecha}' , " +
$"{idProduct} , " +
$"{idAddres}, " +
$"{true} ");
ConnectionBD.ExecuteNonQuery($"INSERT INTO ADDRESS(iduser,address,activo)VALUES({idUSER},'{textBoxdireccion.Text.ToString()}' , " +
$"{true} ) ");
MessageBox.Show("Pedido guardado exitosamente");
}
catch (Exception ex)
{
MessageBox.Show("Pedido guardado exitosamente");
}
}
}
/// <summary>
/// elimina las patentes de un usuario
/// </summary>
/// <param name="_object"></param>
public static void DeletePatentes(Entities.UFP.Usuario _object)
{
using (SqlConnection conn = ConnectionBD.Instance().Conect())
{
using (SqlCommand cmd = new SqlCommand("Usuario_Patente_DeleteParticular @IdUsuario", conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@IdUsuario", _object.IdUsuario);
conn.Open();
cmd.ExecuteNonQuery();
}
}
//Database myDatabase = DatabaseFactory.CreateDatabase();
//DbCommand myCommand = myDatabase.GetStoredProcCommand("Usuario_Patente_DeleteParticular");
//myDatabase.AddInParameter(myCommand, "@IdUsuario", DbType.String, _object.IdUsuario);
//myDatabase.ExecuteNonQuery(myCommand);
}
/// <summary>
/// inserta una patente
/// </summary>
/// <param name="_object">patente</param>
public static void Insert(Entities.UFP.Patente _object)
{
using (SqlConnection conn = ConnectionBD.Instance().Conect())
{
using (SqlCommand cmd = new SqlCommand("Patente_Insert @IdPatente, @Nombre", conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@IdPatente", _object.IdFamiliaElement);
cmd.Parameters.AddWithValue("@Nombre", _object.Nombre);
conn.Open();
cmd.ExecuteNonQuery();
}
}
//Database myDatabase = DatabaseFactory.CreateDatabase();
//DbCommand myCommand = myDatabase.GetStoredProcCommand("Patente_Insert");
//myDatabase.AddInParameter(myCommand, "@IdPatente", DbType.String, _object.IdFamiliaElement);
//myDatabase.AddInParameter(myCommand, "@Nombre", DbType.String, _object.Nombre);
//myDatabase.ExecuteNonQuery(myCommand);
}
public static void IngresarFicha(String IdFicha, String DcumentoInstructor, String IdPrograma, String Jornada, DateTime FefchaInicio1, DateTime FechaFin1, String Estado)
{
try
{
SqlCommand Commando;
ConnectionBD Puente;
Puente = new ConnectionBD();
Commando = new SqlCommand("insert into Ficha (IdFicha,DocumentoInstructor,IdPrograma,Jornada,FechaInicio,FechaFin,Estado) values('" + IdFicha.ToUpper() + "','"
+ DocumentoInstructor.ToUpper() + "','" + IdPrograma.ToUpper() + "','"
+ Jornada.ToUpper() + "','" + FechaInicio1 + "','" + FechaFin1
+ "','" + Estado + "')", Puente.RetornarConnexion());
Commando.ExecuteNonQuery();
MessageBox.Show("INGRESO EXITOSO");
Commando.Dispose();
}
catch (Exception ex)
{
MessageBox.Show("INGRESO ERRONEO");
}
}
private void buttoneliminar_Click(object sender, EventArgs e)
{
if (textBoxID.Text.Equals(""))
{
MessageBox.Show("No se puede dejar datos vacios");
}
else
{
try
{
ConnectionBD.ExecuteNonQuery($"UPDATE APPORDER set activo={false}" +
$" WHERE idOrder='{textBoxID.Text.ToString()}' ");
MessageBox.Show("Orden eliminada exitosamente");
}
catch (Exception ex)
{
MessageBox.Show("Ha ocurrido un problema");
}
}
}
private void buttonverpedido_Click(object sender, EventArgs e)
{
if (textBoxusuario.Text.Equals(""))
{
MessageBox.Show("No se puede dejar datos vacios");
}
else
{
try
{
var dt =
ConnectionBD.ExecuteQuery("select o.idOrder,p.name,o.createdate,b.name,a.address from address a,apporder o,business b,product p,APPUSER u " +
$"\n\twhere a.idAddress=o.idOrder \n\tand o.idProduct=p.idProduct\n\tand p.idBusiness=b.idBusiness\n\tand a.idUser=u.idUser " +
$" \n\tand u.username= '******' and o.activo=true");
dataGridView1.DataSource = dt;
MessageBox.Show("Datos obtenidos exitosamente");
}
catch (Exception ex)
{
MessageBox.Show("Ha ocurrido un problema");
}
}
}
/// <summary>
/// Update de tabla DVV (contiene info de digítos verificadores verticales)
/// </summary>
/// <param name="entity"></param>
public void UpdateDVV(DVV entity)
{
try
{
using (SqlConnection conn = ConnectionBD.Instance().Conect())
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("sp_insert_updateDVV @tabla, @columna, @DV", conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@tabla", entity.tabla);
cmd.Parameters.AddWithValue("@columna", entity.columna);
cmd.Parameters.AddWithValue("@DV", entity.DV);
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
//=================================================================
/// <summary>
/// valida si un usuario puede loggearse
/// </summary>
/// <param name="entity">usuario</param>
/// <returns>bool, string (idusuario)</returns>
public static (bool, string) Login(Entities.UFP.Usuario entity)
{
string idUser = null;
try
{
using (SqlConnection conn = ConnectionBD.Instance().Conect())
{
using (SqlCommand cmd = new SqlCommand("sp_login @nombre, @pass", conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@nombre", entity.Nombre);
cmd.Parameters.AddWithValue("@pass", entity.Pass);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
idUser = reader.GetString(0);
}
return(true, idUser);
}
else
{
return(false, idUser);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Update genérico para ejecutar stores de de DVH
/// </summary>
/// <param name="entity">IEntityDV</param>
/// <param name="tabla">string</param>
public void Update(IEntityDV entity, string tabla)
{
string sp = "sp_updateDVH_" + tabla + " @DVH, @id";
try
{
using (SqlConnection conn = ConnectionBD.Instance().Conect())
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(sp, conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@id", entity.id);
cmd.Parameters.AddWithValue("@DVH", entity.DVH);
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static User Login(string username, string password)
{
User membre = null;
try
{
ConnectionBD db = new ConnectionBD();
string query;
query = "select * from user WHERE username = @username AND password = @password";
Dictionary <string, string> param = new Dictionary <string, string>();
param.Add("username", username);
param.Add("password", password);
MySqlDataReader reader = db.Select(query, param);
membre = db.Serialize <User>(reader);
db.CloseConnection();
}
catch (Exception e)
{
}
return(membre);
}
public ActionResult <bool> Put(int id, [FromBody] Employee data)
{
return(ConnectionBD.UpdateEmployee(data));
}
public ActionResult <Employee> Get(string docType, string docNum)
{
return(ConnectionBD.GetEmployee(docType, docNum));
}
/// <summary>
/// Client
/// </summary>
/// <param name="args"></param>
static void Main(string[] args)
{
#region Abstract Factory
Console.WriteLine("Abstract Factory initialize");
//Step 1
SqlCommand cmd = new SqlCommand();
SqlConection cnn = new SqlConection();
cmd.Execute();
//Apply Abstract Factory pattern
DbFactory db = new OracleFactory();
var con = db.createConnection();
con.Open();
var command = db.createCommand();
command.Execute();
Console.WriteLine("Abstract Factory finalize" + Environment.NewLine);
#endregion
#region Builder
Console.WriteLine("Builder initialize");
//Step 1
var car = new Auto("Car");
car["gear"] = "127c";
car["doors"] = "4";
car["tires"] = "4";
car.Show();
Console.ReadLine();
var bike = new Auto("bike");
bike["gear"] = "50c";
bike["doors"] = "0";
bike["tires"] = "2";
bike.Show();
Console.ReadLine();
//Apply Builder pattern
AutoBuilder builder;
var director = new Director();
builder = new CarBuilder();
director.Construct(builder);
builder.Auto.Show();
builder = new BikeBuilder();
director.Construct(builder);
builder.Auto.Show();
Console.WriteLine("Builder finalize" + Environment.NewLine);
#endregion
#region Factory
Console.WriteLine("Factory Method Initialize");
//Step 1
var login = new AuthGoogle();
login.Authorize();
Console.ReadLine();
//Apply Factory
Creator[] creators = new Creator[2];
creators[0] = new FacebookCreator();
creators[1] = new GoogleCreator();
foreach (Creator c in creators)
{
Auth aut = c.CreateInstance(); //Factory Method
aut.Authorize();
}
Console.ReadLine();
Console.WriteLine("Factory Finalize" + Environment.NewLine);
#endregion
#region Prototype
Console.WriteLine("Prototype initialize");
Book p1 = new Book(1, "Design Patterns - Book", 20.00);
Book c1 = (Book)p1.Clone();
Console.WriteLine(c1.Description);
DVD p2 = new DVD(2, "Design Patterns - DVD", 25.00);
DVD c2 = (DVD)p2.Clone();
Console.WriteLine(c2.Description);
Console.WriteLine("Prototype Finalize" + Environment.NewLine);
Console.ReadKey();
#endregion
#region Singleton
Console.WriteLine("Singleton Initialize");
var con1 = ConnectionBD.Instance();
con1.stringConexao = "SQLServer";
con1.Open();
var con2 = ConnectionBD.Instance();
con2.stringConexao = "Oracle";
con2.Open();
if (con1.Equals(con2))
{
Console.WriteLine("Equals Instances");
}
Console.WriteLine("Singleton Finalize" + Environment.NewLine);
Console.ReadKey();
#endregion
}
public ActionResult <bool> Post([FromBody] Employee data)
{
return(ConnectionBD.InsertEmployee(data));
}
/// <summary>
/// inserta una familia
/// </summary>
/// <param name="_object">familia</param>
public static void Insert(Entities.UFP.Familia _object)
{
using (SqlConnection conn = ConnectionBD.Instance().Conect())
{
using (SqlCommand cmd = new SqlCommand("Familia_Insert @IdFamilia, @Nombre", conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@IdFamilia", _object.IdFamiliaElement);
cmd.Parameters.AddWithValue("@Nombre", _object.Nombre);
conn.Open();
cmd.ExecuteNonQuery();
}
}
//Database myDatabase = DatabaseFactory.CreateDatabase();
//DbCommand myCommand = myDatabase.GetStoredProcCommand("Familia_Insert");
//myDatabase.AddInParameter(myCommand, "@IdFamilia", DbType.String, _object.IdFamiliaElement);
//myDatabase.AddInParameter(myCommand, "@Nombre", DbType.String, _object.Nombre);
//myDatabase.ExecuteNonQuery(myCommand);
if (_object.Accesos != null)
{
DeleteAccesos(_object);
Familia_Patente.DeleteAccesos(_object);
foreach (Entities.UFP.FamiliaElement _tipo in _object.Accesos)
{
if (_tipo.GetType().Name == "Familia")
{
using (SqlConnection conn = ConnectionBD.Instance().Conect())
{
using (SqlCommand cmd = new SqlCommand("Familia_Familia_Insert @IdFamilia, @IdFamiliaHijo", conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@IdFamilia", _object.IdFamiliaElement);
cmd.Parameters.AddWithValue("@IdFamiliaHijo", _tipo.IdFamiliaElement);
conn.Open();
cmd.ExecuteNonQuery();
}
}
//DbCommand myCommandAccesos = myDatabase.GetStoredProcCommand("Familia_Familia_Insert");
//myDatabase.AddInParameter(myCommandAccesos, "@IdFamilia", DbType.String, _object.IdFamiliaElement);
//myDatabase.AddInParameter(myCommandAccesos, "@IdFamiliaHijo", DbType.String, _tipo.IdFamiliaElement);
//myDatabase.ExecuteNonQuery(myCommandAccesos);
}
else
{
using (SqlConnection conn = ConnectionBD.Instance().Conect())
{
using (SqlCommand cmd = new SqlCommand("Familia_Patente_Insert @IdFamilia, @IdPatente", conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@IdFamilia", _object.IdFamiliaElement);
cmd.Parameters.AddWithValue("@IdPatente", _tipo.IdFamiliaElement);
conn.Open();
cmd.ExecuteNonQuery();
}
}
//DbCommand myCommandAccesos = myDatabase.GetStoredProcCommand("Familia_Patente_Insert");
//myDatabase.AddInParameter(myCommandAccesos, "@IdFamilia", DbType.String, _object.IdFamiliaElement);
//myDatabase.AddInParameter(myCommandAccesos, "@IdPatente", DbType.String, _tipo.IdFamiliaElement);
//myDatabase.ExecuteNonQuery(myCommandAccesos);
}
}
}
}
// Agregar score al jugador cuando ha ganado el juego
public static void CreateNewScore(string player, int score)
{
ConnectionBD.ExecuteNonQuery(string.Format("insert into score(score, playerid) select {0}, pl.playerid from player pl where pl.username = '******' ", score, player));
}
private void buttoneliminar_Click(object sender, EventArgs e)
{
ConnectionBD.ExecuteNonQuery($"UPDATE APPUSER set activo='false' WHERE username='******' ");
MessageBox.Show("Usuario eliminado exitosamente");
}
