Public Class Alumnos
Private Const CadenaDeConexion = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Facu\Ejercicio Final\BASE.MDB;User Id=admin;"
'Declaramos el tipo de Conexion A utilizar
Private Const TipoDeConexion As Integer = 1
Private Function dameNuevaConexion() As System.Data.IDbConnection
Dim objConnection As System.Data.IDbConnection
Select Case TipoDeConexion
Case 1 'oledb
objConnection = New Data.OleDb.OleDbConnection(CadenaDeConexion)
Case 2 'Sql
objConnection = New Data.SqlClient.SqlConnection(CadenaDeConexion)
Case 3 'oracle
'objConnection = New Data.(CadenaDeConexion)
Case 4 'ODBc
objConnection = New Data.Odbc.OdbcConnection(CadenaDeConexion)
End Select
Return objConnection
End Function
Private Function dameNuevoComando() As Data.IDbCommand
Dim objCommand As Data.IDbCommand
Select Case TipoDeConexion
Case 1 'oledb
objCommand = New Data.OleDb.OleDbCommand
Case 2 'Sql
objCommand = New Data.SqlClient.SqlCommand
Case 3 'oracle
'objCommand = New Data
Case 4 'ODBc
objCommand = New Data.Odbc.OdbcCommand
End Select
Return objCommand
End Function
Public Function dameParametro(ByVal parameterName As String, _
ByVal parameterValue As Object) As IDbDataParameter
Dim objParameter As Data.IDbDataParameter
Select Case TipoDeConexion
Case 1 'oledb
objParameter = New Data.OleDb.OleDbParameter
Case 2 'Sql
objParameter = New Data.SqlClient.SqlParameter
Case 3 'oracle
'objCommand = New Data
Case 4 'ODBc
objParameter = New Data.Odbc.OdbcParameter
End Select
objParameter.ParameterName = parameterName
objParameter.Value = parameterValue
Return objParameter
End Function
Function dameDataAdapter() As IDataAdapter
Dim objAdapter As Data.IDbDataParameter
Select Case TipoDeConexion
Case 1 'oledb
objAdapter = New Data.OleDb.OleDbDataAdapter
Case 2 'Sql
objAdapter = New Data.SqlClient.SqlDataAdapter
Case 3 'oracle
'objCommand = New Data
Case 4 'ODBc
objAdapter = New Data.Odbc.OdbcDataAdapter
End Select
Return objAdapter
End Function
Public Function consultaQueNoRetornaDatos(ByVal strSql As String, _
ByVal vParametros As List(Of Data.IDbDataParameter)) As Boolean
Dim oConexion As IDbConnection
Dim oCommand As IDbCommand
oConexion = dameNuevaConexion()
oCommand = dameNuevoComando()
Try
oCommand.CommandType = CommandType.Text
oCommand.Connection = oConexion
oCommand.CommandText = strSql
For Each p As Data.IDbDataParameter In vParametros
oCommand.Parameters.Add(p)
Next
oConexion.Open()
oCommand.ExecuteNonQuery()
Catch ex As Exception
Throw ex
Finally
oConexion.Close()
End Try
End Function
Public Function consultaQueRetornaDatos(ByVal strSql As String, _
ByVal vParametros As List(Of Data.IDbDataParameter)) As Data.DataTable
Dim dataSetRetorno As New DataSet
Dim oConexion As IDbConnection
Dim oCommand As IDbCommand
oConexion = dameNuevaConexion()
oCommand = dameNuevoComando()
Try
oCommand.CommandType = CommandType.Text
oCommand.Connection = oConexion
oCommand.CommandText = strSql
For Each p As Data.IDbDataParameter In vParametros
oCommand.Parameters.Add(p)
Next
oConexion.Open()
Dim oAdapter As Data.IDbDataAdapter
oAdapter = dameDataAdapter()
oAdapter.Fill(dataSetRetorno)
Return dataSetRetorno.Tables(0)
Catch ex As Exception
Throw ex
Finally
oConexion.Close()
End Try
Return Nothing
End Function
Public Sub AgregarAlumno(ByVal Apellidos As String, _
ByVal Nombres As String, _
ByVal Legajo As String, _
ByVal Fecha_De_Nacimiento As Date)
Dim strSql As String
strSql = "Insert Into ALUMNOS "
strSql += "(ALU_LEGAJO, ALU_APELLIDO, ALU_NOMBRES, ALU_FECHANACIMIENTO) "
strSql += " values "
strSql += " (@pmtLegajo,@pmtApellido,@pmtNombres,@pmtFechaNac) "
Try
Dim oPmtLegajo As IDbDataParameter
oPmtLegajo = dameParametro("@pmtLegajo", Legajo)
oPmtLegajo.DbType = DbType.String
Dim oPmtApellido As IDbDataParameter = dameParametro("@pmtApellido", Apellidos)
oPmtApellido.DbType = DbType.String
Dim oPmtNombres As IDbDataParameter = dameParametro("@pmtNombres", Nombres)
oPmtNombres.DbType = DbType.String
Dim oPmtFecha As IDbDataParameter = dameParametro("@pmtFechaNac", Fecha_De_Nacimiento)
oPmtFecha.DbType = DbType.Date
Dim pmtList As New List(Of IDbDataParameter)
pmtList.Add(oPmtLegajo)
pmtList.Add(oPmtApellido)
pmtList.Add(oPmtNombres)
pmtList.Add(oPmtFecha)
Me.consultaQueNoRetornaDatos(strSql, pmtList)
Catch ex As Exception
Throw ex
End Try
End Sub
Public Sub ActualizarAlumno(ByVal ID As Integer, _
ByVal Apellidos As String, _
ByVal Nombres As String, _
ByVal Legajo As String, _
ByVal Fecha_De_Nacimiento As Date)
Dim oConexion As Data.OleDb.OleDbConnection
Dim oComandos As Data.OleDb.OleDbCommand
Dim strSql As String
strSql = " UPDATE ALUMNOS "
strSql += " Set ALU_LEGAJO = @pmtLegajo,"
strSql += " ALU_APELLIDO = @pmtApellido,"
strSql += " ALU_NOMBRES = @pmtNombres,"
strSql += " ALU_FECHANACIMIENTO = @pmtFechaNac"
strSql += " WHERE (ALU_ID = @pmtID)"
oConexion = New Data.OleDb.OleDbConnection(CadenaDeConexion)
oComandos = New Data.OleDb.OleDbCommand()
Try
oConexion.Open()
'TODO Escribir el código
oComandos.Connection = oConexion
oComandos.CommandType = CommandType.Text
oComandos.CommandText = strSql
Dim oPmtLegajo As New Data.OleDb.OleDbParameter("@pmtLegajo", Legajo)
oPmtLegajo.DbType = DbType.String
Dim oPmtApellido As New Data.OleDb.OleDbParameter("@pmtApellido", Apellidos)
oPmtLegajo.DbType = DbType.String
Dim oPmtNombres As New Data.OleDb.OleDbParameter("@pmtNombres", Nombres)
oPmtLegajo.DbType = DbType.String
Dim oPmtFecha As New Data.OleDb.OleDbParameter("@pmtFechaNac", Fecha_De_Nacimiento)
oPmtLegajo.DbType = DbType.Date
Dim oPmtID As New Data.OleDb.OleDbParameter("@pmtID", ID)
oPmtLegajo.DbType = DbType.Int32
oComandos.Parameters.Add(oPmtLegajo)
oComandos.Parameters.Add(oPmtApellido)
oComandos.Parameters.Add(oPmtNombres)
oComandos.Parameters.Add(oPmtFecha)
oComandos.Parameters.Add(oPmtID)
Dim filasAfectadas As Integer = oComandos.ExecuteNonQuery()
Catch ex As Exception
Throw ex
Finally
oConexion.Close()
End Try
End Sub
Public Sub EliminarAlumno(ByVal ID As Integer)
Dim oConexion As Data.OleDb.OleDbConnection
Dim oComandos As Data.OleDb.OleDbCommand
Dim strSql As String
strSql = " DELETE * FROM ALUMNOS "
strSql += " WHERE (ALU_ID = @pmtID)"
oConexion = New Data.OleDb.OleDbConnection(CadenaDeConexion)
oComandos = New Data.OleDb.OleDbCommand()
Try
oConexion.Open()
'TODO Escribir el código
oComandos.Connection = oConexion
oComandos.CommandType = CommandType.Text
oComandos.CommandText = strSql
Dim oParameter As New OleDb.OleDbParameter("@pmtID", ID)
oComandos.Parameters.Add(oParameter)
Dim filasAfectadas As Integer = oComandos.ExecuteNonQuery()
Catch ex As Exception
Throw ex
Finally
oConexion.Close()
End Try
End Sub
Public Function BuscarAlumno(ByVal ID As Nullable(Of Integer), _
ByVal Apellidos As String, _
ByVal Nombres As String, _
ByVal Legajo As String, _
ByVal Fecha_De_Nacimiento As Nullable(Of Date)) As DataTable
Dim oConexion As Data.OleDb.OleDbConnection
Dim dt As New DataTable
Try
Dim oComandos As Data.OleDb.OleDbCommand
oConexion = New Data.OleDb.OleDbConnection(CadenaDeConexion)
oComandos = New Data.OleDb.OleDbCommand()
oConexion.Open()
oComandos.CommandType = CommandType.Text
oComandos.Connection = oConexion
Dim strSql As String
strSql = " Select * From Alumnos "
Dim where As String = ""
If ID.HasValue Then
where += " ALU_ID = @pmtID AND"
Dim pmt As New OleDb.OleDbParameter("@pmtid", ID)
oComandos.Parameters.Add(pmt)
End If
If Apellidos IsNot Nothing Then
where += " ALU_Apellido = @pmtapellido AND"
Dim pmt As New OleDb.OleDbParameter("@pmtapellido", Apellidos)
oComandos.Parameters.Add(pmt)
End If
If Nombres IsNot Nothing Then
where += " ALU_Nombre = @pmtNombres AND"
Dim pmt As New OleDb.OleDbParameter("@pmtNombres", Nombres)
oComandos.Parameters.Add(pmt)
End If
If Legajo IsNot Nothing Then
where += " ALU_Legajo = @pmtLegajo AND"
Dim pmt As New OleDb.OleDbParameter("@pmtLegajo", Legajo)
oComandos.Parameters.Add(pmt)
End If
If Fecha_De_Nacimiento.HasValue Then
where += " ALU_FECHANACIMIENTO = @pmtFechaNac AND"
Dim pmt As New OleDb.OleDbParameter("@Fecha_De_Nacimiento", Fecha_De_Nacimiento)
pmt.DbType = DbType.Date
oComandos.Parameters.Add(pmt)
End If
If where <> "" Then
where = " Where " & where.Substring(0, where.Length - 3)
End If
oComandos.CommandText = strSql & " " & where
Dim oDataAdapter As New OleDb.OleDbDataAdapter(oComandos)
oDataAdapter.Fill(dt)
Catch ex As Exception
Throw ex
Finally
oConexion.Close()
End Try
If dt IsNot Nothing Then
Return dt
Else
Return Nothing
End If
End Function
End Class
No hay comentarios:
Publicar un comentario