ola gente!! a ver q os parece esta clase que e echo para enlazar a base de datos:
solo os tendreis q crear una clase con este codigo y declarar una varible en un modulo
del tipo de esta clase para q la podais utilizar en cualquier sitio del proyecto.
la funcion exescalar sirve para pasarle las select...
y la funcion nonquery para los update insert o delete...
decirme si os gusta y en q se podria mejorar!!saludos.
Imports System.Data.Odbc
Imports System.Data.OleDb
Public Class AccesoDatos
Private conexion As New OdbcConnection
Private command As New OdbcCommand
Private datos As New DataSet
Private adapters As New SortedList
Public Sub New()
End Sub
Public Function conectar(ByVal dsn As String)
Try
conexion.ConnectionString = "Provider=MSDASQL;" & "FileDSN=" & dsn
'Abro conexión
conexion.Open()
If conexion.State = ConnectionState.Open Then
MsgBox("Conexion abierta", MsgBoxStyle.Information, "Estado Conexion")
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Function
Public Function cerrarconexion()
Try
conexion.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Function
Public Function estado_conexion()
Return conexion.State
End Function
Public Function creartabla(ByVal sql As String, ByVal tabla As String, ByVal modificable As Boolean)
Try
If adapters.Contains(tabla) Then
adapters.Item(tabla) = New OdbcDataAdapter(sql, conexion)
datos.Tables(tabla).Clear()
Else
adapters.Add(tabla, New OdbcDataAdapter(sql, conexion))
End If
adapters(tabla).fill(datos, tabla)
If modificable = True Then
Dim commandbuilder As New OdbcCommandBuilder(adapters.Item(tabla))
End If
Catch ex As Exception
MsgBox("No se ha podido añadir la tabla" & ex.Message, MsgBoxStyle.Exclamation)
End Try
End Function
Public Function recuperartabla(ByVal tabla As String) As DataTable
Try
If adapters.Contains(tabla) Then
Return (datos.Tables(tabla))
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Function
Public Function anadirfila(ByVal tabla As String, ByVal drnewrow As DataRow)
Try
If adapters.Contains(tabla) Then
datos.Tables(tabla).Rows.Add(drnewrow)
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Function
Public Function guardartabla(ByVal nombre As String)
Try
If adapters.Contains(nombre) Then
adapters(nombre).update(datos, nombre)
datos.AcceptChanges()
Else
MsgBox("No se ha encontrado la tabla deseada", MsgBoxStyle.Exclamation)
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Function
Public Function ExeScalar(ByVal sql As String, ByVal tipo As CommandType) As String
Try
command.Connection = conexion
command.CommandText = sql
command.CommandType = tipo
ExeScalar = command.ExecuteScalar
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Function
Public Function ExeReader(ByVal sql As String, ByVal tipo As CommandType) As Object
Try
command.Connection = conexion
command.CommandText = sql
command.CommandType = tipo
ExeReader = command.ExecuteReader
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Function
Public Function ExeNonQuery(ByVal sql As String, ByVal tipo As CommandType) As Integer
Try
command.Connection = conexion
command.CommandText = sql
command.CommandType = tipo
ExeNonQuery = command.ExecuteNonQuery
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Function
End Class
