Conexión a Base de Datos (SQL Server)
En este ejercicio realizamos la conexión a SQL Server por medio de un formulario sencillo y 4 botones que realizan la conexión: Registrar, Modificar, Borrar y Consultar registros.
Código
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Laboratorio1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void btn_salir_Click(object sender, EventArgs e)
{
this.Close();
}
private void btn_verificar_Click(object sender, EventArgs e)
{
SqlConnection conexion = new SqlConnection("Data Source=DANY-PC\\SQLEXPRESS; Initial Catalog=Vacunas; Integrated Security=True");
String cadena = "SELECT * FROM RegistroVacunas WHERE NoRegistro = " + txt_registro.Text;
SqlCommand comando = new SqlCommand(cadena, conexion);
try
{
conexion.Open();
SqlDataReader datos;
datos = comando.ExecuteReader();
if (datos.HasRows == true)
{
while (datos.Read())
{
txt_nombre.Text = datos.GetString(1);
if (Convert.ToInt16(datos.GetString(2)) == 1)
{
chBox_hepatitisA.Checked = true;
}
else
{
chBox_hepatitisA.Checked = false;
}
if (Convert.ToInt16(datos.GetString(3)) == 1)
{
chBox_hepatitisB.Checked = true;
}
else
{
chBox_hepatitisB.Checked = false;
}
if (Convert.ToInt16(datos.GetString(4)) == 1)
{
chBox_Polio.Checked = true;
}
else
{
chBox_Polio.Checked = false;
}
if (Convert.ToInt16(datos.GetString(5)) == 1)
{
chBox_neumococo.Checked = true;
}
else
{
chBox_neumococo.Checked = false;
}
}
}
else
{
MessageBox.Show("No existen registros con ese número", "Error");
txt_nombre.Clear();
txt_registro.Clear();
chBox_hepatitisA.Checked = false;
chBox_hepatitisB.Checked = false;
chBox_neumococo.Checked = false;
chBox_Polio.Checked = false;
}
}
catch
{
MessageBox.Show("ERROR", "Error");
}
finally
{
conexion.Close();
}
}
private void btn_registrar_Click(object sender, EventArgs e)
{
SqlConnection conexion = new SqlConnection("Data Source=DANY-PC\\SQLEXPRESS;Initial Catalog=Vacunas;Integrated Security=True");
int hepatitisA = 0;
int hepatitisB = 0;
int polio = 0;
int neumococo = 0;
if (chBox_hepatitisA.Checked == true)
{
hepatitisA = 1;
}
else
{
hepatitisB = 0;
}
if (chBox_hepatitisB.Checked == true)
{
hepatitisB = 1;
}
else
{
hepatitisB = 0;
}
if (chBox_Polio.Checked == true)
{
polio = 1;
}
else
{
polio = 0;
}
if (chBox_neumococo.Checked == true)
{
neumococo = 1;
}
else
{
neumococo = 0;
}
String cadena = "INSERT INTO RegistroVacunas VALUES (" + txt_registro.Text + ", '" + txt_nombre.Text + "', '" + hepatitisA.ToString() + "', '" + hepatitisB.ToString() + "', '" + polio.ToString() + "', '" + neumococo.ToString() + "')";
SqlCommand comando = new SqlCommand(cadena, conexion);
try
{
conexion.Open();
comando.ExecuteNonQuery();
MessageBox.Show("Datos registrados correctamente!");
}
catch
{
MessageBox.Show("ERROR", "Error");
}
finally
{
conexion.Close();
}
}
private void btn_modificar_Click(object sender, EventArgs e)
{
SqlConnection conexion = new SqlConnection("Data Source=DANY-PC\\SQLEXPRESS;Initial Catalog=Vacunas;Integrated Security=True");
int hepatitisA = 0;
int hepatitisB = 0;
int polio = 0;
int neumococo = 0;
if (chBox_hepatitisA.Checked == true)
{
hepatitisA = 1;
}
else
{
hepatitisB = 0;
}
if (chBox_hepatitisB.Checked == true)
{
hepatitisB = 1;
}
else
{
hepatitisB = 0;
}
if (chBox_Polio.Checked == true)
{
polio = 1;
}
else
{
polio = 0;
}
if (chBox_neumococo.Checked == true)
{
neumococo = 1;
}
else
{
neumococo = 0;
}
String cadena = "UPDATE RegistroVacunas SET Nombre = '" + txt_nombre.Text + "', HepatitisA = '" + hepatitisA.ToString() + "', HepatitisB = '" + hepatitisB.ToString() + "', Polio = '" + polio.ToString() + "', Neumococo = '" + neumococo.ToString() + "' WHERE NoRegistro = " + txt_registro.Text;
SqlCommand comando = new SqlCommand(cadena, conexion);
try
{
conexion.Open();
comando.ExecuteNonQuery();
MessageBox.Show("Datos modificados correctamente!");
}
catch
{
MessageBox.Show("ERROR", "Error");
}
finally
{
conexion.Close();
}
}
private void btn_eliminar_Click(object sender, EventArgs e)
{
SqlConnection conexion = new SqlConnection("Data Source=DANY-PC\\SQLEXPRESS;Initial Catalog=Vacunas;Integrated Security=True");
String cadena = "DELETE FROM RegistroVacunas WHERE NoRegistro = " + txt_registro.Text;
SqlCommand comando = new SqlCommand(cadena, conexion);
try
{
conexion.Open();
comando.ExecuteNonQuery();
MessageBox.Show("Registro eliminado correctamente");
txt_nombre.Clear();
txt_registro.Clear();
chBox_hepatitisA.Checked = false;
chBox_hepatitisB.Checked = false;
chBox_neumococo.Checked = false;
chBox_Polio.Checked = false;
}
catch
{
MessageBox.Show("ERROR", "Error");
}
finally
{
conexion.Close();
}
}
}
}
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Laboratorio1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void btn_salir_Click(object sender, EventArgs e)
{
this.Close();
}
private void btn_verificar_Click(object sender, EventArgs e)
{
SqlConnection conexion = new SqlConnection("Data Source=DANY-PC\\SQLEXPRESS; Initial Catalog=Vacunas; Integrated Security=True");
String cadena = "SELECT * FROM RegistroVacunas WHERE NoRegistro = " + txt_registro.Text;
SqlCommand comando = new SqlCommand(cadena, conexion);
try
{
conexion.Open();
SqlDataReader datos;
datos = comando.ExecuteReader();
if (datos.HasRows == true)
{
while (datos.Read())
{
txt_nombre.Text = datos.GetString(1);
if (Convert.ToInt16(datos.GetString(2)) == 1)
{
chBox_hepatitisA.Checked = true;
}
else
{
chBox_hepatitisA.Checked = false;
}
if (Convert.ToInt16(datos.GetString(3)) == 1)
{
chBox_hepatitisB.Checked = true;
}
else
{
chBox_hepatitisB.Checked = false;
}
if (Convert.ToInt16(datos.GetString(4)) == 1)
{
chBox_Polio.Checked = true;
}
else
{
chBox_Polio.Checked = false;
}
if (Convert.ToInt16(datos.GetString(5)) == 1)
{
chBox_neumococo.Checked = true;
}
else
{
chBox_neumococo.Checked = false;
}
}
}
else
{
MessageBox.Show("No existen registros con ese número", "Error");
txt_nombre.Clear();
txt_registro.Clear();
chBox_hepatitisA.Checked = false;
chBox_hepatitisB.Checked = false;
chBox_neumococo.Checked = false;
chBox_Polio.Checked = false;
}
}
catch
{
MessageBox.Show("ERROR", "Error");
}
finally
{
conexion.Close();
}
}
private void btn_registrar_Click(object sender, EventArgs e)
{
SqlConnection conexion = new SqlConnection("Data Source=DANY-PC\\SQLEXPRESS;Initial Catalog=Vacunas;Integrated Security=True");
int hepatitisA = 0;
int hepatitisB = 0;
int polio = 0;
int neumococo = 0;
if (chBox_hepatitisA.Checked == true)
{
hepatitisA = 1;
}
else
{
hepatitisB = 0;
}
if (chBox_hepatitisB.Checked == true)
{
hepatitisB = 1;
}
else
{
hepatitisB = 0;
}
if (chBox_Polio.Checked == true)
{
polio = 1;
}
else
{
polio = 0;
}
if (chBox_neumococo.Checked == true)
{
neumococo = 1;
}
else
{
neumococo = 0;
}
String cadena = "INSERT INTO RegistroVacunas VALUES (" + txt_registro.Text + ", '" + txt_nombre.Text + "', '" + hepatitisA.ToString() + "', '" + hepatitisB.ToString() + "', '" + polio.ToString() + "', '" + neumococo.ToString() + "')";
SqlCommand comando = new SqlCommand(cadena, conexion);
try
{
conexion.Open();
comando.ExecuteNonQuery();
MessageBox.Show("Datos registrados correctamente!");
}
catch
{
MessageBox.Show("ERROR", "Error");
}
finally
{
conexion.Close();
}
}
private void btn_modificar_Click(object sender, EventArgs e)
{
SqlConnection conexion = new SqlConnection("Data Source=DANY-PC\\SQLEXPRESS;Initial Catalog=Vacunas;Integrated Security=True");
int hepatitisA = 0;
int hepatitisB = 0;
int polio = 0;
int neumococo = 0;
if (chBox_hepatitisA.Checked == true)
{
hepatitisA = 1;
}
else
{
hepatitisB = 0;
}
if (chBox_hepatitisB.Checked == true)
{
hepatitisB = 1;
}
else
{
hepatitisB = 0;
}
if (chBox_Polio.Checked == true)
{
polio = 1;
}
else
{
polio = 0;
}
if (chBox_neumococo.Checked == true)
{
neumococo = 1;
}
else
{
neumococo = 0;
}
String cadena = "UPDATE RegistroVacunas SET Nombre = '" + txt_nombre.Text + "', HepatitisA = '" + hepatitisA.ToString() + "', HepatitisB = '" + hepatitisB.ToString() + "', Polio = '" + polio.ToString() + "', Neumococo = '" + neumococo.ToString() + "' WHERE NoRegistro = " + txt_registro.Text;
SqlCommand comando = new SqlCommand(cadena, conexion);
try
{
conexion.Open();
comando.ExecuteNonQuery();
MessageBox.Show("Datos modificados correctamente!");
}
catch
{
MessageBox.Show("ERROR", "Error");
}
finally
{
conexion.Close();
}
}
private void btn_eliminar_Click(object sender, EventArgs e)
{
SqlConnection conexion = new SqlConnection("Data Source=DANY-PC\\SQLEXPRESS;Initial Catalog=Vacunas;Integrated Security=True");
String cadena = "DELETE FROM RegistroVacunas WHERE NoRegistro = " + txt_registro.Text;
SqlCommand comando = new SqlCommand(cadena, conexion);
try
{
conexion.Open();
comando.ExecuteNonQuery();
MessageBox.Show("Registro eliminado correctamente");
txt_nombre.Clear();
txt_registro.Clear();
chBox_hepatitisA.Checked = false;
chBox_hepatitisB.Checked = false;
chBox_neumococo.Checked = false;
chBox_Polio.Checked = false;
}
catch
{
MessageBox.Show("ERROR", "Error");
}
finally
{
conexion.Close();
}
}
}
}
No hay comentarios.:
Publicar un comentario