VB.NET Member Management Form: Full Source Code for Socios CRUD, Import/Export and Loan Transaction

Socios Management Form in VB.NET

Private Sub frmSocios_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
cargarCombos()
cargarSocios()
configurarGrilla()
ConfigurarFuncion(alta)
End Sub

Private Sub btnSeleccionar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSeleccionar.Click
seleccionarSocio()
End Sub

Private Sub btnAlta_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAlta.Click
ConfigurarFuncion(alta)
End Sub 'Lo mismo para baja y modificación.

Select Case funcion
Case Is = alta
RemoveHandler btnAceptar.Click, AddressOf borrar
RemoveHandler btnAceptar.Click, AddressOf modificar
AddHandler btnAceptar.Click, AddressOf insertar
btnAlta.Enabled = False
btnBaja.Enabled = True
btnModificar.Enabled = True
btnAceptar.Text = "Aceptar Alta"
lblAccion.Text = "Alta de registro"

Load Combo Boxes

Private Sub cargarCombos()
Dim objProvinciasNegocio As New ProvinciaNegocio
Dim ds As System.Data.DataSet

Try
ds = objProvinciasNegocio.getProvincias
cboProvincia.DataSource = ds.Tables(0)
cboProvincia.ValueMember = "idProvincia"
cboProvincia.DisplayMember = "dsDescripcion"
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

End Sub

Load Members

Private Sub cargarSocios()
Dim objSocioNegocio As New SocioNegocio
Dim ds As System.Data.DataSet

Try
ds = objSocioNegocio.getSocios
dgSocios.DataSource = ds.Tables(0)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

End Sub

Delete Member

Public Sub borrar(ByVal sender As System.Object, ByVal e As System.EventArgs)
Dim objSocio As New Socio
Dim objSocioNegocio As New SocioNegocio

If idSocio = -1 Then
MessageBox.Show("Seleccione un socio para borrar")
Exit Sub
End If

objSocio.idSocio = idSocio

Try
objSocioNegocio.borrar(objSocio)
idSocio = -1
MessageBox.Show("Socio Eliminado")

cargarSocios()
limpiarControles()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub

Update Member

Public Sub modificar(ByVal sender As System.Object, ByVal e As System.EventArgs)
Dim objSocio As New Socio
Dim objSocioNegocio As New SocioNegocio

If txtNroDoc.Text = "" Or txtTipoDoc.Text = "" Then
MessageBox.Show("El tipo y Nro. de Documento son datos obligatorios para un socio")
Exit Sub
End If

With objSocio
.activo = True
.cdProvincia = cboProvincia.SelectedValue
.dsApellido = txtApellido.Text
.dsCalle = txtCalle.Text
.dsNombre = txtNombre.Text
.dsNroDoc = txtNroDoc.Text
.dsTelefono = txtTelefono.Text
.dsTipoDoc = txtTipoDoc.Text
.idSocio = idSocio
End With

Try
objSocioNegocio.modificar(objSocio)
MessageBox.Show("Socio modificado con éxito!!")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
cargarSocios()
limpiarControles()
End Sub

Insert Member

Public Sub insertar(ByVal sender As System.Object, ByVal e As System.EventArgs)
Dim objSocio As New Socio
Dim objSocioNegocio As New SocioNegocio

If txtNroDoc.Text = "" Or txtTipoDoc.Text = "" Then
MessageBox.Show("El tipo y Nro. de Documento son datos obligatorios para registrar a un socio")
Exit Sub
End If

With objSocio
.activo = True
.cdProvincia = cboProvincia.SelectedValue
.dsApellido = txtApellido.Text
.dsCalle = txtCalle.Text
.dsNombre = txtNombre.Text
.dsNroDoc = txtNroDoc.Text
.dsTelefono = txtTelefono.Text
.dsTipoDoc = txtTipoDoc.Text
End With

Try
objSocioNegocio.insertar(objSocio)
MessageBox.Show("Socio insertado con éxito!!")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
cargarSocios()
limpiarControles()
End Sub

Select Member from Grid

Private Sub seleccionarSocio()
If dgSocios.SelectedRows.Count = 0 Then
MessageBox.Show("Seleccione un socio")
Exit Sub
End If
idSocio = dgSocios.SelectedRows(0).Cells("idSocio").Value
With dgSocios.SelectedRows(0)
txtNombre.Text = .Cells("Nombre").Value
txtApellido.Text = .Cells("Apellido").Value
txtTelefono.Text = .Cells("dsTelefono").Value
txtCalle.Text = .Cells("Calle").Value
txtNroDoc.Text = .Cells("NroDoc").Value
txtTipoDoc.Text = .Cells("TipoDoc").Value
cboProvincia.SelectedValue = .Cells("idProvincia").Value
End With
End Sub

Configure Grid

Private Sub configurarGrilla()
dgSocios.AllowUserToAddRows = False
dgSocios.AllowUserToDeleteRows = False
dgSocios.EditMode = DataGridViewEditMode.EditProgrammatically
dgSocios.MultiSelect = False
dgSocios.RowHeadersVisible = True
If dgSocios.ColumnCount > 0 Then
dgSocios.Columns("idSocio").Visible = False
End If
End Sub

Accept Button and Validations

Private Sub btnAceptar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAceptar.Click

End Sub

Validaciones

If txtEdicion.Text = String.Empty Then
MessageBox.Show("Debe ingresar la edición del disco")
Return False
End If

If cmbGenero.SelectedValue = 0 Then
MessageBox.Show("Debe seleccionar un género")
Return False
End If

Return True

---------Hasta acá FRMSOCIO-------------------------------------------

Business Layer: Socios

CapaNegocioSociosI

Public Class SocioNegocio
Public Function getSocios() As System.Data.DataSet
Dim objSocioDatos As New SocioDatos
Try
Return objSocioDatos.getSocios()
Catch ex As Exception
Throw ex
End Try
End Function
Public Function getSociosCombo() As System.Data.DataSet
Dim objSocioDatos As New SocioDatos
Try
Return objSocioDatos.getSociosCombo()
Catch ex As Exception
Throw ex
End Try
End Function

Public Sub insertar(ByVal objsocio As Socio)
Dim objSocioDatos As New SocioDatos
If existeSocio(objsocio.dsNroDoc, objsocio.dsTipoDoc) Then
Throw New Exception("El socio que desea agregar ya existe")
End If
Try
objSocioDatos.insertar(objsocio)
Catch ex As Exception
Throw ex
End Try
End Sub

Public Sub modificar(ByVal objsocio As Socio)
Dim objSocioDatos As New SocioDatos
Try
objSocioDatos.modificar(objsocio)
Catch ex As Exception
Throw ex
End Try
End Sub

CapaNegocioSociosII

Public Sub borrar(ByVal objsocio As Socio)
Dim objSocioDatos As New SocioDatos
Try
objSocioDatos.borrar(objsocio)
Catch ex As Exception
Throw ex
End Try
End Sub

Public Function existeSocio(ByVal dsNroDoc As String, ByVal dsTipoDoc As String) As Boolean
Dim objSocioDatos As New SocioDatos
Try
Return objSocioDatos.existeSocio(dsNroDoc, dsTipoDoc)
Catch ex As Exception
Throw ex
End Try
End Function
End Class

Data Layer: Socios

CapaDatosSociosI

Public Class SocioDatos
Private objConexion As SqlClient.SqlConnection
Private objAdapter As SqlClient.SqlDataAdapter
Private objCommand As SqlClient.SqlCommand
Public Function getSociosCombo()
Try
Return Datos.executeDataSet("tbSocio_GetComboSocio")
Catch ex As Exception
Throw ex
End Try
End Function

Public Function getSocios() As System.Data.DataSet
Try
Return Datos.executeDataSet("tbSocio_GetLista")
Catch ex As Exception
Throw New Exception("Error al traer socios")
End Try
End Function

CapaDatosSociosII

Public Sub insertar(ByVal objSocio As Socio)
Dim objSqlParameters() As SqlClient.SqlParameter = New SqlClient.SqlParameter(7) {}
objSqlParameters(0) = New SqlClient.SqlParameter("@dsNombre", SqlDbType.VarChar, 40)
objSqlParameters(0).Value = objSocio.dsNombre
objSqlParameters(1) = New SqlClient.SqlParameter("@dsApellido", SqlDbType.VarChar, 40)
objSqlParameters(1).Value = objSocio.dsApellido
objSqlParameters(2) = New SqlClient.SqlParameter("@dsTipoDoc", SqlDbType.VarChar, 5)
objSqlParameters(2).Value = objSocio.dsTipoDoc
objSqlParameters(3) = New SqlClient.SqlParameter("@dsNroDoc", SqlDbType.VarChar, 8)
objSqlParameters(3).Value = objSocio.dsNroDoc
objSqlParameters(4) = New SqlClient.SqlParameter("@dscalle", SqlDbType.VarChar, 60)
objSqlParameters(4).Value = objSocio.dsCalle
objSqlParameters(5) = New SqlClient.SqlParameter("@idProvincia", SqlDbType.Int, 4)
objSqlParameters(5).Value = objSocio.cdProvincia
objSqlParameters(6) = New SqlClient.SqlParameter("@dsTelefono", SqlDbType.VarChar, 15)
objSqlParameters(6).Value = objSocio.dsTelefono
objSqlParameters(7) = New SqlClient.SqlParameter("@activo", SqlDbType.Bit, 1)
objSqlParameters(7).Value = objSocio.activo
Try
Datos.executeNonQuery("tbSocio_Insertar", objSqlParameters)
Catch ex As Exception
Throw New Exception("Error al insertar socio")
End Try
End Sub

Public Sub borrar(ByVal objSocio As Socio)
Dim objSqlParameter() As SqlClient.SqlParameter = New SqlClient.SqlParameter(0) {}
objSqlParameter(0) = New SqlClient.SqlParameter("@idSocio", SqlDbType.Int, 4)
objSqlParameter(0).Value = objSocio.idSocio
Try
Datos.executeNonQuery("tbSocio_borrar", objSqlParameter)
Catch ex As Exception
Throw New Exception("Error al borrar socio")
End Try
End Sub

Public Sub modificar(ByVal objSocio As Socio)
Dim objSqlParameters() As SqlClient.SqlParameter = New SqlClient.SqlParameter(7) {}

objSqlParameters(0) = New SqlClient.SqlParameter("@idSocio", SqlDbType.Int, 4)
objSqlParameters(0).Value = objSocio.idSocio

objSqlParameters(1) = New SqlClient.SqlParameter("@dsNombre", SqlDbType.VarChar, 40)
objSqlParameters(1).Value = objSocio.dsNombre

objSqlParameters(2) = New SqlClient.SqlParameter("@dsApellido", SqlDbType.VarChar, 40)
objSqlParameters(2).Value = objSocio.dsApellido

objSqlParameters(3) = New SqlClient.SqlParameter("@dsTipoDoc", SqlDbType.VarChar, 5)
objSqlParameters(3).Value = objSocio.dsTipoDoc

objSqlParameters(4) = New SqlClient.SqlParameter("@dsNroDoc", SqlDbType.VarChar, 8)
objSqlParameters(4).Value = objSocio.dsNroDoc

objSqlParameters(5) = New SqlClient.SqlParameter("@dscalle", SqlDbType.VarChar, 60)
objSqlParameters(5).Value = objSocio.dsCalle

objSqlParameters(6) = New SqlClient.SqlParameter("@idProvincia", SqlDbType.Int, 4)
objSqlParameters(6).Value = objSocio.cdProvincia

objSqlParameters(7) = New SqlClient.SqlParameter("@dsTelefono", SqlDbType.VarChar, 15)
objSqlParameters(7).Value = objSocio.dsTelefono

Try
Datos.executeNonQuery("tbSocio_actualizar", objSqlParameters)
Catch ex As Exception
Throw New Exception("Error al modificar socio")
End Try

End Sub

Public Function existeSocio(ByVal dsNroDoc As String, ByVal dsTipoDoc As String) As Boolean
Dim objSqlParameters() As SqlClient.SqlParameter = New SqlClient.SqlParameter(2) {}

objSqlParameters(0) = New SqlClient.SqlParameter("@dsNroDoc", SqlDbType.VarChar, 8)
objSqlParameters(0).Value = dsNroDoc

objSqlParameters(1) = New SqlClient.SqlParameter("@dsTipoDoc", SqlDbType.VarChar, 5)
objSqlParameters(1).Value = dsTipoDoc

objSqlParameters(2) = New SqlClient.SqlParameter("@resultado", SqlDbType.Bit, 1)
objSqlParameters(2).Direction = ParameterDirection.Output

Try
Datos.executeNonQuery("tbSocio_existeSocio", objSqlParameters)
Return objSqlParameters(2).Value
Catch ex As Exception
Throw New Exception("Error al chequear la existencia del socio")
End Try

End Function

End Class

Shared Data Access Class

CapaDeDatosClaseDATOS (USAN TODOS)

Public Class Datos
Private Shared objConexion As SqlClient.SqlConnection
Private Shared objAdapter As SqlClient.SqlDataAdapter
Private Shared objCommand As SqlClient.SqlCommand
Private Shared lstrConexion As String = Configuration.ConfigurationManager.ConnectionStrings("conStr").ConnectionString

Public Shared Function executeDataSet(ByVal nombreSP As String) As System.Data.DataSet
Dim ds As New DataSet
objConexion = New SqlClient.SqlConnection(lstrConexion)
objCommand = New SqlClient.SqlCommand()
objCommand.CommandType = CommandType.StoredProcedure
objCommand.CommandText = nombreSP
objCommand.Connection = objConexion
objAdapter = New SqlClient.SqlDataAdapter
objAdapter.SelectCommand = objCommand
Try
objConexion.Open()
objAdapter.Fill(ds)
Return ds
objConexion.Close()
Catch ex As Exception
objConexion.Close()
Throw ex
End Try
End Function

Public Shared Function executeDataSet(ByVal nombreSP As String, ByVal params As SqlClient.SqlParameter()) As System.Data.DataSet
Dim ds As New DataSet
objConexion = New SqlClient.SqlConnection(lstrConexion)
objCommand = New SqlClient.SqlCommand()
objCommand.CommandType = CommandType.StoredProcedure
objCommand.CommandText = nombreSP
objCommand.Connection = objConexion
For Each parametro As SqlClient.SqlParameter In params
objCommand.Parameters.Add(parametro)
Next
objAdapter = New SqlClient.SqlDataAdapter
objAdapter.SelectCommand = objCommand
Try
objConexion.Open()
objAdapter.Fill(ds)
Return ds
objConexion.Close()
Catch ex As Exception
objConexion.Close()
Throw ex
End Try
End Function

Public Shared Function executeReader(ByVal nombreSP As String) As SqlClient.SqlDataReader
objConexion = New SqlClient.SqlConnection(lstrConexion)
objCommand = New SqlClient.SqlCommand()
objCommand.CommandType = CommandType.StoredProcedure
objCommand.CommandText = nombreSP
objCommand.Connection = objConexion
Try
objConexion.Open()
Return objCommand.ExecuteReader()
objConexion.Close()
Catch ex As Exception
objConexion.Close()
Throw ex
End Try
End Function

Public Function executeReader(ByVal nombreSP As String, ByVal params As SqlClient.SqlParameter()) As SqlClient.SqlDataReader
objConexion = New SqlClient.SqlConnection(lstrConexion)
objCommand = New SqlClient.SqlCommand()
objCommand.CommandType = CommandType.StoredProcedure
objCommand.CommandText = nombreSP
objCommand.Connection = objConexion
For Each parametro As SqlClient.SqlParameter In params
objCommand.Parameters.Add(parametro)
Next
Try
objConexion.Open()
Return objCommand.ExecuteReader()
objConexion.Close()
Catch ex As Exception
Throw ex
End Try
End Function

Public Shared Sub executeNonQuery(ByVal nombreSP As String)
objConexion = New SqlClient.SqlConnection(lstrConexion)
objCommand = New SqlClient.SqlCommand()
objCommand.CommandType = CommandType.StoredProcedure
objCommand.CommandText = nombreSP
objCommand.Connection = objConexion

Try
objConexion.Open()
objCommand.ExecuteNonQuery()
objConexion.Close()
Catch ex As Exception
Throw ex
End Try
End Sub

Public Shared Sub executeNonQuery(ByVal nombreSP As String, ByVal params As SqlClient.SqlParameter())
objConexion = New SqlClient.SqlConnection(lstrConexion)
objCommand = New SqlClient.SqlCommand()
objCommand.CommandType = CommandType.StoredProcedure
objCommand.CommandText = nombreSP
objCommand.Connection = objConexion

'Agrego parámetros al command
For Each parametro As SqlClient.SqlParameter In params
objCommand.Parameters.Add(parametro)
Next

Try
objConexion.Open()
objCommand.ExecuteNonQuery()
objConexion.Close()
Catch ex As Exception
Throw ex
End Try
End Sub

Public Shared Sub executeNonQuery(ByVal nombreSP As String, ByRef params As SqlClient.SqlParameter(), ByRef objConn As SqlClient.SqlConnection, ByRef objTran As SqlClient.SqlTransaction)
Try
objCommand = New SqlClient.SqlCommand()
objCommand.CommandType = CommandType.StoredProcedure
objCommand.CommandText = nombreSP
objCommand.Connection = objConn
objCommand.Transaction = objTran
For Each parametro As SqlClient.SqlParameter In params
objCommand.Parameters.Add(parametro)
Next
objCommand.Connection = objConn
objCommand.ExecuteNonQuery()
Catch ex As Exception
Throw New Exception("Error al insertar préstamo")
End Try
End Sub
End Class

Loan Transaction Form

PrestamoLibro TRANSACCION

Public Class frmPrestamos
Private idSocio As Integer = -1
Private Sub configurarGrilla()
Me.dgLibros.SelectionMode = DataGridViewSelectionMode.FullRowSelect
Me.dgLibros.MultiSelect = False
Me.dgLibros.AllowUserToAddRows = False
Me.dgLibros.AllowUserToDeleteRows = False
Me.dgLibros.EditMode = DataGridViewEditMode.EditProgrammatically
Me.dgLibros.RowHeadersVisible = False
End Sub

Private Sub insertar()
Dim objPrestamo As New Prestamo
Dim lstPrestamoDetalle As New List(Of PrestamoDetalle)
Dim objPrestamoNegocio As New PrestamoNegocio
Try
If idSocio = -1 Then
MessageBox.Show("Seleccione un socio")
Exit Sub
End If
With objPrestamo
.idSocio = idSocio
.devuelto = False
.dtDevolucion = Date.Parse(txtFechaDevolucion.Text)
.dtPrestamo = Date.Parse(txtFechaPrestamo.Text)
End With
For Each detalle As DataGridViewRow In Me.dgLibros.Rows
lstPrestamoDetalle.Add(New PrestamoDetalle(detalle.Cells("idStock").Value))
Next
objPrestamoNegocio.insertar(objPrestamo, lstPrestamoDetalle)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub

Private Sub frmPrestamo_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
configurarGrilla()
End Sub

Private Sub btnQuitarLibro_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnQuitarLibro.Click
If Me.dgLibros.SelectedRows.Count = 0 Then
MessageBox.Show("Seleccione un libro")
Exit Sub
End If
Me.dgLibros.Rows.Remove(Me.dgLibros.SelectedRows(0))
End Sub

Private Sub btnBuscarSocio_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBuscarSocio.Click
Dim objSocioNegocio As New SocioNegocio
Dim ds As DataSet
Dim objfrmBuscador As frmBuscador
Try
ds = objSocioNegocio.getSocios
objfrmBuscador = New frmBuscador(ds)
If objfrmBuscador.ShowDialog() = Windows.Forms.DialogResult.OK Then
With objfrmBuscador.objSeleccionado
Me.txtApellidoSocio.Text = .Cells("Apellido").Value
Me.txtNombreSocio.Text = .Cells("Nombre").Value
Me.txtNroDoc.Text = .Cells("NroDoc").Value
Me.txtTipoDoc.Text = .Cells("TipoDoc").Value
Me.idSocio = .Cells("idSocio").Value
End With
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub

Private Sub btnBuscarLibro_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBuscarLibro.Click
Dim objStockNegocio As New StockNegocio
Dim ds As DataSet
Dim objfrmBuscador As frmBuscador
Dim nuevoRow As New DataGridViewRow
Try
ds = objStockNegocio.getListaDisponibles
objfrmBuscador = New frmBuscador(ds)
If objfrmBuscador.ShowDialog() = Windows.Forms.DialogResult.OK Then
'Si no tengo las columnas de la grilla, las genero
If Me.dgLibros.Columns.Count = 0 Then
For Each col As DataColumn In ds.Tables(0).Columns
Me.dgLibros.Columns.Add(col.ColumnName, col.ColumnName)
Next
End If
If Me.dgLibros.Rows.Contains(objfrmBuscador.objSeleccionado) Then
MessageBox.Show("Ya está en la lista")
Exit Sub
End If
With objfrmBuscador.objSeleccionado
Me.dgLibros.Rows.Add(New Object() {.Cells(0).Value, .Cells(1).Value, .Cells(2).Value, .Cells(3).Value, .Cells(4).Value, .Cells(5).Value, .Cells(6).Value, .Cells(7).Value})
End With
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub

Private Sub btnAceptar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAceptar.Click
insertar()
End Sub

Loan Business Layer

PrestamoNegocio

Public Class PrestamoNegocio
Private objConexion As New SqlClient.SqlConnection("Data Source=localhost;Initial Catalog=Biblioteca;Integrated Security=True")
Private objTransaction As SqlClient.SqlTransaction
Public Sub insertar(ByVal objPrestamo As Prestamo, ByVal objPrestamoDetalles As List(Of PrestamoDetalle))
Dim objPrestamoDatos As New PrestamoDatos
Dim objPrestamoDetalleDatos As New PrestamoDetalleDatos
Dim objStockDatos As New StockDatos
Dim blnTrans As Boolean = False
Try
If objPrestamoDatos.socioDeudor(objPrestamo.idSocio) Then
Throw New Exception("El socio tiene préstamos pendientes de devolución")
End If
objConexion.Open()
objTransaction = objConexion.BeginTransaction
blnTrans = True
objPrestamo.IdPrestamos = objPrestamoDatos.insertar(objPrestamo, objConexion, objTransaction)
For Each objDetalle As PrestamoDetalle In objPrestamoDetalles
objDetalle.idPrestamo = objPrestamo.IdPrestamos
objPrestamoDetalleDatos.insertar(objDetalle, objConexion, objTransaction)
Dim objStock As New Stock
objStock.idEstado = "P"
objStock.idStock = objDetalle.idStock
objStockDatos.actualizarEstado(objStock, objConexion, objTransaction)
Next
objTransaction.Commit()
blnTrans = False
Catch ex As Exception
If blnTrans Then
objTransaction.Rollback()
End If
Throw (ex)
Finally
objConexion.Close()
End Try
End Sub
End Class

Loan Data Layer

PrestamoDatos

Public Class PrestamoDatos
Public Function socioDeudor(ByVal idSocio As Integer) As Boolean
Dim param() As SqlClient.SqlParameter = New SqlClient.SqlParameter(0) {}
Dim objDatos As New Datos
param(0) = New SqlClient.SqlParameter("@idSocio", SqlDbType.Int, 4)
param(0).Value = idSocio
Try
Dim ds As DataSet = objDatos.executeDataSet("tbPrestamo_SocioDeudor", param)
If ds.Tables(0).Rows.Count = 0 Then
Return False
Else
Return True
End If
Catch ex As Exception
Throw New Exception("Error al consultar si el socio es deudor")
End Try
End Function

Public Function insertar(ByVal objPrestamo As Prestamo, ByVal objConn As SqlClient.SqlConnection, ByVal objTran As SqlClient.SqlTransaction) As Integer
Try
Dim objDatos As New Datos
Dim objPrestamoParam() As SqlClient.SqlParameter = New SqlClient.SqlParameter(3) {}

objPrestamoParam(0) = New SqlClient.SqlParameter("@idSocio", SqlDbType.Int, 4)
objPrestamoParam(0).Value = objPrestamo.idSocio

objPrestamoParam(1) = New SqlClient.SqlParameter("@dtPrestamo", SqlDbType.DateTime, 8)
objPrestamoParam(1).Value = objPrestamo.dtPrestamo

objPrestamoParam(2) = New SqlClient.SqlParameter("@dtDevolucion", SqlDbType.DateTime, 8)
objPrestamoParam(2).Value = objPrestamo.dtDevolucion

objPrestamoParam(3) = New SqlClient.SqlParameter("@idPrestamo", SqlDbType.Int, 4)
objPrestamoParam(3).Direction = ParameterDirection.Output

objDatos.executeNonQuery("tbPrestamo_insertar", objPrestamoParam, objConn, objTran)
Return objPrestamoParam(3).Value
Catch ex As Exception
Throw ex
End Try

End Function
End Class
fin transacción

XML Import Form

frmImportadorSocios

Imports BiblioNegocio
Public Class frmImportadorSocios

Private Sub btnSeleccionar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSeleccionar.Click
If Me.OpenFileDialog1.ShowDialog() <> Windows.Forms.DialogResult.OK Then
lblArchivo.Text = "Ninguno"
lblInsertReg.Text = ""
lblRepeReg.Text = ""
lblTotalReg.Text = ""
Me.btnImportar.Enabled = False
Exit Sub
End If
Me.btnImportar.Enabled = True
lblArchivo.Text = Me.OpenFileDialog1.FileName
End Sub

Private Sub btnImportar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImportar.Click
Dim objSocioNeg As New BiblioNegocio.SocioNegocio
Dim cantidadRepetidos As Integer, cantidadInsertados As Integer
Dim nombreArchivo As String
nombreArchivo = Me.OpenFileDialog1.FileName
Try
objSocioNeg.importarXML(nombreArchivo, cantidadRepetidos, cantidadInsertados)
Me.lblInsertReg.Text = cantidadInsertados
Me.lblRepeReg.Text = cantidadRepetidos
Me.lblTotalReg.Text = cantidadInsertados + cantidadRepetidos
MessageBox.Show("Listo")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub

Private Sub frmImportadorSocios_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub
End Class

XML Export Form

frmExportadorSocios

Imports BiblioNegocio
Public Class frmExportadorSocios

Private Sub btnExportar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportar.Click
Dim objSociosNeg As New BiblioNegocio.SocioNegocio

If txtNombreArchivo.Text = "" Then
MessageBox.Show("Ingrese nombre para el archivo")
Exit Sub
End If

Try
lblCantidad.Text = objSociosNeg.exportarXML(Application.StartupPath & "\Exportaciones\" & txtNombreArchivo.Text & ".xml")
MessageBox.Show("Listo!")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub

Private Sub frmExportadorSocios_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

End Sub
End Class

XML Business Layer

XML SOCIONEGOCIO

Public Class SocioNegocio
Public Function exportarXML(ByVal path As String) As Integer
Dim objSocioDatos As New SocioDatos
Dim dsSocios As New DataSet
Dim lstSocios As New List(Of BiblioEntidades.Socio)
Try
dsSocios = objSocioDatos.getSocios
For Each row As Data.DataRow In dsSocios.Tables(0).Rows
Dim objSocio As New BiblioEntidades.Socio()
With objSocio
.idSocio = row.Item("idSocio")
.dsNombre = row.Item("Nombre")
.dsApellido = row.Item("Apellido")
.dsTipoDoc = row.Item("TipoDoc")
.dsNroDoc = row.Item("NroDoc")
.dsCalle = row.Item("Calle")
.cdProvincia = row.Item("idProvincia")
.dsTelefono = row.Item("dsTelefono")
.activo = row.Item("activo").ToString
End With
lstSocios.Add(objSocio)
Next
Return objSocioDatos.exportarXML(path, lstSocios)
Catch ex As Exception
Throw ex
End Try
End Function

Public Sub importarXML(ByVal path As String, ByRef cantidadRepetidos As Integer, ByRef cantidadInsertados As Integer)
Dim objSocioDatos As New SocioDatos
Dim objT As Xml.XmlTextReader
Dim lstSocios As New List(Of Socio)
Dim objSocioNegocio As New SocioNegocio
Try
lstSocios = objSocioDatos.importarXML(path)
For Each socio As Socio In lstSocios
Try
If objSocioDatos.existeSocio(socio.dsNroDoc, socio.dsTipoDoc) Then
cantidadRepetidos += 1
Else
objSocioDatos.insertar(socio)
cantidadInsertados += 1
End If
Catch ex As Exception
Throw New Exception("Error al insertar el socio: " & socio.dsApellido & "," & socio.dsNombre)
End Try
Next
Catch ex As Exception
Throw ex
End Try
End Sub