Ejemplo de module / modulo visual basic 2010
asignarle datos de la base de datos a un textboxt, combobox, label etc
Agregar datos a la base de datos.
Ejecutar reportes, consultas, buscar imágenes etc
muy útil aquí esta el código:
Imports System.Data
Imports System.IO
Imports MySql.Data.MySqlClient
Module Module1
Public cnn As MySqlConnection
Public base = "transporte"
Dim StrConexion As String = "server=" & Trim("localhost") & ";uid=root;password=;database=" & base
Public Sub cargarGrid(ByVal xsql As String, ByVal grid As DataGridView)
Dim conta As New Integer
Dim conexion As New MySqlConnection
conexion.ConnectionString = StrConexion
' crear comando
Dim oComando As New MySqlCommand(xsql, conexion)
''''''''''''''''''''''''
Dim Tabla As New DataTable
Dim Adaptador As New MySqlDataAdapter(xsql, StrConexion)
Tabla.Clear()
Adaptador.Fill(Tabla)
grid.DataSource = Tabla
End Sub
Public Sub cargarTextBox(ByVal xsql As String, ByVal txt As TextBox)
Dim conexion As MySqlConnection
Dim conta As New Integer
conexion = New MySql.Data.MySqlClient.MySqlConnection
'Dim conexion As MySqlConnection
conexion.ConnectionString = StrConexion
' crear comando
Dim oComando As New MySqlCommand(xsql, conexion)
txt.Text = ""
' crear DataReader
Dim oDataReader As MySqlDataReader
conexion.Open() ' obtener DataReader
oDataReader = oComando.ExecuteReader()
' recorrer filas
While oDataReader.Read()
'conta = oDataReader(0)
txt.Text = (oDataReader(0))
End While
'txt.Text = conta + 1
oDataReader.Close()
conexion.Close()
End Sub
Public Sub CamposTabla(ByVal sql1 As String, ByVal combo As ComboBox)
Dim conexion As MySqlConnection 'gestiona la conexion a la Base de Datos
Dim schemaTable As DataTable
Dim myField As DataRow
Dim cont As Integer
conexion = New MySql.Data.MySqlClient.MySqlConnection
conexion.ConnectionString = StrConexion ' "server=localhost;user=root; password=root;database=" & base & ";port=3306;"
conexion.Open()
combo.Items.Clear()
Dim sql As MySqlCommand = New MySqlCommand
sql.Connection = conexion
sql.CommandText = sql1
sql.CommandType = CommandType.Text
Dim dr As System.Data.IDataReader
Try
dr = sql.ExecuteReader()
schemaTable = dr.GetSchemaTable()
cont = 0
For Each myField In schemaTable.Rows
'Para cada propiedad del campo...
For Each myProperty In schemaTable.Columns
'Mostrar el nombre y el valor del campo.
Console.WriteLine(myProperty.ColumnName & " = " & myField(myProperty).ToString())
If (myProperty.ColumnName = "ColumnName") Then
'MsgBox(myProperty.ColumnName & " " & myField(myProperty).ToString())
combo.Items.Add(myField(myProperty).ToString())
cont += 1
End If
Next
Next
combo.DropDownStyle = ComboBoxStyle.DropDownList
Catch mierror As MySqlException
MessageBox.Show("Error de Conexión a la Base de Datos: " & mierror.Message)
Finally
'conexion.Dispose()
End Try
End Sub
Public Sub EjecutarReporte(ByVal sql As String, ByVal rutax As String)
Dim Tabla As New DataTable 'se encarga de representar la tabla de datos en la memoria
Dim conexion As MySqlConnection 'gestiona la conexion a la Base de Datos
'Dim rutax As String
Dim Ds As New DataSet 'representa la memoria interna de los datos
conexion = New MySql.Data.MySqlClient.MySqlConnection
conexion.ConnectionString = StrConexion ''"server=localhost;user=root; password=root;database=" & base & ";port=3306;"
conexion.Open()
Dim insertar As New MySqlDataAdapter(sql, conexion)
Dim Cmd As New MySqlCommandBuilder(insertar)
Try
insertar.Fill(Ds)
Ds.WriteXml(rutax, XmlWriteMode.WriteSchema)
'MessageBox.Show("Conexión Abierta Con Éxito")
'conexion.Close()
Catch mierror As MySqlException
MessageBox.Show("Error de Conexión a la Base de Datos: " & mierror.Message)
Finally
'conexion.Dispose()
End Try
End Sub
Public Sub EjecutarConsulta(ByVal sql As String)
Dim Tabla As New DataTable 'se encarga de representar la tabla de datos en la memoria
Dim conexion As MySqlConnection 'gestiona la conexion a la Base de Datos
Dim Ds As New DataSet 'representa la memoria interna de los datos
conexion = New MySql.Data.MySqlClient.MySqlConnection
conexion.ConnectionString = StrConexion ''"server=localhost;user=root; password=root;database=" & base & ";port=3306;"
conexion.Open()
Dim insertar As New MySqlDataAdapter(sql, conexion)
Dim Cmd As New MySqlCommandBuilder(insertar)
Try
insertar.Fill(Tabla)
MessageBox.Show("Transaccion Realizada Exitosamente", "Sistema", MessageBoxButtons.OK)
'conexion.Close()
Catch mierror As MySqlException
MessageBox.Show("Error de Conexión a la Base de Datos: " & mierror.Message)
Finally
'conexion.Dispose()
End Try
End Sub
Public Sub IncrementarID(ByVal sql1 As String, ByVal combo As TextBox)
Dim conexion As MySqlConnection 'gestiona la conexion a la Base de Datos
Dim Ds As New DataSet 'representa la memoria interna de los datos
Dim cont As Integer
conexion = New MySql.Data.MySqlClient.MySqlConnection
conexion.ConnectionString = StrConexion ' "server=localhost;user=root; password=root;database=" & base & ";port=3306;"
conexion.Open()
combo.Text = ""
Dim sql As MySqlCommand = New MySqlCommand
sql.Connection = conexion
sql.CommandText = sql1
sql.CommandType = CommandType.Text
Dim dr As System.Data.IDataReader
Try
dr = sql.ExecuteReader()
cont = 0
While dr.Read()
'cont += 1
cont = dr(0)
End While
combo.Text = cont + 1
'Cerramos la conexión con el servidor
Catch mierror As MySqlException
MessageBox.Show("Error de Conexión a la Base de Datos: " & mierror.Message)
Finally
'conexion.Dispose()
End Try
End Sub
Public Sub UnSoloValor(ByVal sql1 As String, ByVal combo As TextBox)
Dim conexion As MySqlConnection 'gestiona la conexion a la Base de Datos
Dim Ds As New DataSet 'representa la memoria interna de los datos
Dim cont As Integer
conexion = New MySql.Data.MySqlClient.MySqlConnection
conexion.ConnectionString = StrConexion ' "server=localhost;user=root; password=root;database=" & base & ";port=3306;"
conexion.Open()
combo.Text = ""
Dim sql As MySqlCommand = New MySqlCommand
'Establecemos el tipo Connection que usará para realizar la petición
sql.Connection = conexion
sql.CommandText = sql1
sql.CommandType = CommandType.Text
Dim dr As System.Data.IDataReader
Try
dr = sql.ExecuteReader()
cont = 0
While dr.Read()
combo.Text = (dr(0))
'cont += 1
End While
Catch mierror As MySqlException
MessageBox.Show("Error de Conexión a la Base de Datos: " & mierror.Message)
Finally
'conexion.Dispose()
End Try
End Sub
Public Sub LlenarCombo(ByVal sql1 As String, ByVal combo As ComboBox)
Dim conexion As MySqlConnection 'gestiona la conexion a la Base de Datos
Dim Ds As New DataSet 'representa la memoria interna de los datos
Dim cont As Integer
conexion = New MySql.Data.MySqlClient.MySqlConnection
conexion.ConnectionString = StrConexion ' "server=localhost;user=root; password=root;database=" & base & ";port=3306;"
conexion.Open()
combo.Items.Clear()
combo.DropDownStyle = ComboBoxStyle.DropDownList
Dim sql As MySqlCommand = New MySqlCommand
'Establecemos el tipo Connection que usará para realizar la petición
sql.Connection = conexion
sql.CommandText = sql1
sql.CommandType = CommandType.Text
Dim dr As System.Data.IDataReader
Try
dr = sql.ExecuteReader()
cont = 0
While dr.Read()
combo.Items.Add(dr(0))
'cont += 1
End While
Catch mierror As MySqlException
MessageBox.Show("Error de Conexión a la Base de Datos: " & mierror.Message)
Finally
'conexion.Dispose()
End Try
End Sub
Public Sub llenarLista(ByVal sql1 As String, ByVal list As ListView)
Dim conexion As MySqlConnection 'gestiona la conexion a la Base de Datos
Dim Ds As New DataSet 'representa la memoria interna de los datos
Dim listar As New ListViewItem("x")
Dim listar1 As New ListViewItem("xwq")
Dim schemaTable As DataTable
'Dim myField As DataRow
Dim cont As Integer
conexion = New MySql.Data.MySqlClient.MySqlConnection
conexion.ConnectionString = StrConexion ' "server=localhost;user=root; password=root;database=" & base & ";port=3306;"
conexion.Open()
list.Items.Clear()
list.GridLines = True
list.FullRowSelect = True
list.LabelEdit = True
list.View = View.Details
' list.Columns.Clear()
Dim sql As MySqlCommand = New MySqlCommand
sql.Connection = conexion
sql.CommandText = sql1
sql.CommandType = CommandType.Text
Dim dr As System.Data.IDataReader
Try
dr = sql.ExecuteReader()
schemaTable = dr.GetSchemaTable()
cont = 0
'' For Each myField In schemaTable.Rows
'Para cada propiedad del campo...
'For Each myProperty In schemaTable.Columns
'Mostrar el nombre y el valor del campo.
'Console.WriteLine(myProperty.ColumnName & " = " & myField(myProperty).ToString())
'If (myProperty.ColumnName = "ColumnName") Then
'MsgBox(myProperty.ColumnName & " " & myField(myProperty).ToString())
'list.Columns.Add(myField(myProperty).ToString())
'cont += 1
'End If
'Next
'Next
'Mientras haya datos para leer los mostramos
While dr.Read()
'Al igual de los objetos DataRow, la clase IDataReader también tiene
'un método por defecto .Item que funciona exactamente igual
listar1 = list.Items.Add(dr(0))
For i = 1 To dr.FieldCount - 1
listar1.SubItems.Add(i).Text = dr(i)
Next
End While
Catch mierror As MySqlException
MessageBox.Show("Error de Conexión a la Base de Datos: " & mierror.Message)
Finally
'conexion.Dispose()
End Try
End Sub
Public Sub guardarImagen(ByVal sql As String, ByVal PictureBox1 As PictureBox)
Try
Dim sq As String = sql
';Dim sql As String = "insert into tbl_imagenes(imagen)values(?imagen)"
cnn = New MySqlConnection(StrConexion)
Dim Comando As New MySqlCommand(sql, cnn)
Dim Imag As Byte()
Imag = Imagen_Bytes(PictureBox1.Image)
Comando.Parameters.AddWithValue("?imagen", Imag)
cnn.Open()
If cnn.State = ConnectionState.Open Then
Comando.ExecuteNonQuery()
End If
cnn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
Public Sub BuscarImagen(ByVal sql1 As String, ByVal PictureBox1 As PictureBox)
Try
Dim Sql As String = sql1 '"select * from tbl_imagenes where id=" & Me.TxtID.Text & ""
Dim lector As MySqlDataReader
cnn = New MySqlConnection(StrConexion)
cnn.Open()
If cnn.State = ConnectionState.Open Then
Dim Imag As Byte()
Dim Comando As New MySqlCommand(Sql, cnn)
lector = Comando.ExecuteReader
While lector.Read
Imag = lector("imagen")
PictureBox1.Image = Bytes_Imagen(Imag)
End While
End If
cnn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
'convertir binario a imágen
Private Function Bytes_Imagen(ByVal Imagen As Byte()) As Image
Try
'si hay imagen
If Not Imagen Is Nothing Then
'caturar array con memorystream hacia Bin
Dim Bin As New MemoryStream(Imagen)
'con el método FroStream de Image obtenemos imagen
Dim Resultado As Image = Image.FromStream(Bin)
'y la retornamos
Return Resultado
Else
Return Nothing
End If
Catch ex As Exception
Return Nothing
End Try
End Function
'convertir imagen a binario
Private Function Imagen_Bytes(ByVal Imagen As Image) As Byte()
'si hay imagen
If Not Imagen Is Nothing Then
'variable de datos binarios en stream(flujo)
Dim Bin As New MemoryStream
'convertir a bytes
Imagen.Save(Bin, Imaging.ImageFormat.Jpeg)
'retorna binario
Return Bin.GetBuffer
Else
Return Nothing
End If
End Function
End Module
Descargar modulo:
Comentarios
Publicar un comentario