utilizamos sql server 2008 .creamos una nueva query y escribimos el siguiente codigo
Código
GO USE master GO SET LANGUAGE spanish GO IF(DB_ID('ejemplo')IS NOT NULL) DROP DATABASE ejemplo GO CREATE DATABASE ejemplo GO USE ejemplo GO CREATE TABLE individuo (dni CHAR(8) PRIMARY KEY, apellidos VARCHAR(30) NOT NULL, nombre VARCHAR(30) NOT NULL, sexo CHAR(1) NOT NULL CHECK (sexo IN('M','F')), fecnac DATE NOT NULL, edad tinyint NOT NULL, salario money) GO CREATE proc registrar(@dni CHAR(8), @ape VARCHAR(30), @n VARCHAR(30), @s CHAR(1), @fn DATE, @e tinyint, @sueldo money, @msj VARCHAR(60)output) AS BEGIN IF(NOT EXISTS(SELECT * FROM individuo WHERE dni =@dni )) BEGIN INSERT INTO individuo VALUES(@dni,@ape,@n,@s,@fn,@e,@sueldo) SET @msj ='REGISTRADO' END ELSE --NO EXISTE UNA PERSONA CON EL MISMO DNI SET @msj ='DNI YA EXISTE' END GO CREATE proc modificar(@dni CHAR(8), @ape VARCHAR(30), @n VARCHAR(30), @s CHAR(1), @fn DATE, @e tinyint, @sueldo money, @msj VARCHAR(60)output) AS BEGIN IF(EXISTS(SELECT * FROM individuo WHERE dni =@dni )) BEGIN UPDATE individuo SET apellidos =@ape ,nombre =@n ,sexo =@s ,fecnac =@fn ,edad =@e ,salario =@sueldo WHERE dni =@dni SET @msj ='Datos modificados' END ELSE SET @msj ='DNI no existe' END GO CREATE proc eliminar(@dni CHAR(8),@msj VARCHAR(60)output) AS BEGIN IF(EXISTS(SELECT * FROM individuo WHERE dni =@dni )) BEGIN DELETE FROM individuo WHERE dni =@dni SET @msj ='Datos eliminados' END ELSE SET @msj ='DNI no existe' END
2_despues de haber hecho el code en sql pasamos a visual
2.1_creamos un nuevo proyecto de tipo solucion(otros tipo de proyecto)
http://img143.imageshack.us/i/53607601.jpg/
2.2_siguiendo nos vamos a herramientas (proyectos y soluciones-general-activamos mostrar solucion siempre)
http://img828.imageshack.us/i/21239949.jpg/
2.3_le damos anticlik ala solucion y agregar nuevo proyecto de tipo biblioteca de clases.(le pondremos por nombre biblioteca)por defecto aparecera una clase ya creada(utilizaremos mas adelante)
http://img203.imageshack.us/i/85722103.jpg/
http://img573.imageshack.us/i/95876910.jpg/
2.4_hacemos lo mismo del paso 2.3 pero esta ves creamos una aplicacion de windows forms llamada formularios
http://img713.imageshack.us/i/38902831.jpg/
2.5_le damos anticlik a biblioteca (agregar ->modulo)y aplicamos el siguiente code
Código
Imports System.Data Imports System.Data.SqlClient Module Module1 Public con As New SqlConnection("Data Source=.;DataBase=ejemplo;Integrated Security=true") Public Sub abrir() If con.State = 0 Then con.Open() End Sub Public Sub cerrar() If con.State = 1 Then con.Close() End Sub End Module
2.6_ahora le damos anticlik ala solucion y propiedades escogemos como proyecto de inicio formularios
http://img200.imageshack.us/i/82265367.jpg/
2.7_le agregamos referencia (anticlik a formularios-agregar referencia.proyectos-boblioteca(unika opcion))y aceptar
http://img256.imageshack.us/i/20827082.jpg/
2.8_entramos a clase 1 escribiremos el code para manejar los procedimientos (registrar-modificar-eliminar)de la BD
Código
Imports System.Data Imports System.Data.SqlClient Public Class Class1 Private m_dni, m_ape, m_n, m_s As String Private m_fn As Date Private m_edad As Integer Private m_sueldo As Decimal Public Property dni() As String Get Return m_dni End Get Set(ByVal value As String) m_dni = value End Set End Property Public Property ape() As String Get Return m_ape End Get Set(ByVal value As String) m_ape = value End Set End Property Public Property n() As String Get Return m_n End Get Set(ByVal value As String) m_n = value End Set End Property Public Property s() As String Get Return m_s End Get Set(ByVal value As String) m_s = value End Set End Property Public Property fn() As Date Get Return m_fn End Get Set(ByVal value As Date) m_fn = value End Set End Property Public Property edad() As Integer Get Return m_edad End Get Set(ByVal value As Integer) m_edad = value End Set End Property Public Property sueldo() As Decimal Get Return m_sueldo End Get Set(ByVal value As Decimal) m_sueldo = value End Set End Property Public Function listado() Dim dt As New DataTable Dim da As SqlDataAdapter Try abrir() da = New SqlDataAdapter("select *from individuo", con) da.Fill(dt) Catch ex As Exception : Throw ex End Try cerrar() Return dt End Function Public Function registrar() As String Dim msj As String Dim cmd As SqlCommand Try '@dni,@idt ,@ape,@nom,@sex,@fn,@fi,@fono,@est abrir() cmd = New SqlCommand("registrar", con) cmd.CommandType = CommandType.StoredProcedure With cmd.Parameters cmd.Parameters.AddWithValue("@dni", dni) cmd.Parameters.AddWithValue("@ape", ape) cmd.Parameters.AddWithValue("@n", n) cmd.Parameters.AddWithValue("@s", s) cmd.Parameters.AddWithValue("@fn", fn) cmd.Parameters.AddWithValue("@e", edad) cmd.Parameters.AddWithValue("@sueldo", sueldo) cmd.Parameters.Add("@msj", SqlDbType.VarChar, 30).Direction = ParameterDirection.Output cmd.ExecuteNonQuery() msj = cmd.Parameters("@msj").Value End With Catch ex As Exception : Throw ex End Try cerrar() Return msj End Function Public Function modificar() As String Dim msj As String Dim cmd As SqlCommand Try '@dni,@idt ,@ape,@nom,@sex,@fn,@fi,@fono,@est abrir() cmd = New SqlCommand("modificar", con) cmd.CommandType = CommandType.StoredProcedure With cmd.Parameters cmd.Parameters.AddWithValue("@dni", dni) cmd.Parameters.AddWithValue("@ape", ape) cmd.Parameters.AddWithValue("@n", n) cmd.Parameters.AddWithValue("@s", s) cmd.Parameters.AddWithValue("@fn", fn) cmd.Parameters.AddWithValue("@e", edad) cmd.Parameters.AddWithValue("@sueldo", sueldo) cmd.Parameters.Add("@msj", SqlDbType.VarChar, 30).Direction = ParameterDirection.Output cmd.ExecuteNonQuery() msj = cmd.Parameters("@msj").Value End With Catch ex As Exception : Throw ex End Try cerrar() Return msj End Function Public Function eliminar() As String Dim msj As String Dim cmd As SqlCommand Try '@dni,@idt ,@ape,@nom,@sex,@fn,@fi,@fono,@est abrir() cmd = New SqlCommand("eliminar", con) cmd.CommandType = CommandType.StoredProcedure With cmd.Parameters cmd.Parameters.AddWithValue("@dni", dni) cmd.Parameters.Add("@msj", SqlDbType.VarChar, 30).Direction = ParameterDirection.Output cmd.ExecuteNonQuery() msj = cmd.Parameters("@msj").Value End With Catch ex As Exception : Throw ex End Try cerrar() Return msj End Function End Class
2.9 _despues de haber hecho lo anterior nos vamos al formulario(acomodar la interfaz)Y ESCRIBIMOS EL CODE
http://img41.imageshack.us/i/81843289.jpg/
Código
Imports biblioteca Public Class Form1 Public p As New biblioteca.Class1 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load listado() RadioButton1.Checked = True End Sub Sub listado() Try DataGridView1.DataSource = p.listado() Catch ex As Exception : MessageBox.Show(ex.Message) End Try End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Try p.dni = TextBox1.Text p.ape = TextBox2.Text p.n = TextBox3.Text If RadioButton1.Checked = True Then p.s = "M" Else p.s = "F" End If p.fn = DateTimePicker1.Value p.edad = TextBox4.Text p.sueldo = TextBox5.Text MessageBox.Show(p.registrar()) Catch ex As Exception : MessageBox.Show(ex.Message) End Try listado() limpiar() End Sub Sub limpiar() TextBox1.Clear() TextBox2.Clear() TextBox3.Clear() TextBox4.Clear() TextBox1.ReadOnly = False TextBox5.Clear() DateTimePicker1.Value = Now RadioButton1.Checked = True TextBox1.Focus() End Sub Private Sub DateTimePicker1_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker1.ValueChanged Dim ed As Integer ed = Now.Year - DateTimePicker1.Value.Year TextBox4.Text = ed End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Try p.dni = TextBox1.Text p.ape = TextBox2.Text p.n = TextBox3.Text If RadioButton1.Checked = True Then p.s = "M" Else p.s = "F" End If p.fn = DateTimePicker1.Value p.edad = TextBox4.Text p.sueldo = TextBox5.Text MessageBox.Show(p.modificar()) Catch ex As Exception : MessageBox.Show(ex.Message) End Try listado() limpiar() End Sub Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click Try p.dni = TextBox1.Text MessageBox.Show(p.eliminar()) Catch ex As Exception : MessageBox.Show(ex.Message) End Try listado() limpiar() End Sub Private Sub DataGridView1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataGridView1.Click Try Dim fila As Integer = DataGridView1.CurrentRow.Index With DataGridView1.Rows(fila) TextBox1.Text = .Cells(0).Value.ToString TextBox2.Text = .Cells(1).Value.ToString TextBox3.Text = .Cells(2).Value.ToString If .Cells(3).Value.ToString = "M" Then RadioButton1.Checked = True Else RadioButton2.Checked = True End If DateTimePicker1.Value = .Cells(4).Value.ToString TextBox4.Text = .Cells(5).Value.ToString TextBox5.Text = .Cells(6).Value.ToString End With TextBox1.ReadOnly = True Catch ex As Exception : MessageBox.Show("fila sin datos") End Try End Sub End Class
nota: UTILIZAMOS PROCEDIMIENTOS ,Funciones,Parametros de salida(output)
PARA BUSCAR LOS DATOS SOLO DA CLIK EN CUALQUIERA CELDA .
PARA LIMPIAR LAS CAJAS AGREGA UN LINKLABEL (LE AGREGAS EL CODE(limpiar))
saludos : ojala les sirva
Nota del Moderador: Por favor, existe la etiqueta Code, la cual puedes usar sin ningún problema.Gracias.