#Region "IMPORTS - REFERENCIAS"
Imports System.Data.SqlClient ' >> .NET >> System.data
Imports System.Data.OleDb
#End Region
Public Class BD
#Region "VARIABLES"
Public Conex As SqlConnection, SqlComm As SqlCommand, SqlComm2 As SqlCommand, CadenaConex As String
Private DyC_RS As Dictionary(Of Integer, SqlDataReader)
#End Region
Public Sub New(StrServidor As String, _
StrBaseDatos As String, _
StrUsuario As String, _
StrPass As String, _
Optional StrUbicacion As String = "")
_sError = ""
_Servidor = StrServidor : _BaseDatos = StrBaseDatos : _Usuario = StrUsuario : _Pass = StrPass
_Ubicacion = StrUbicacion
CadenaConex = "server=" & _Servidor & ";uid=" & _Usuario & ";pwd=" & _Pass & ";database=" & _BaseDatos
' _BDAbierta = False
_VersionSQL = DameVersionSQL()
End Sub
#Region "PROPIEDADES"
Private _Ubicacion As String
Public Property Ubicacion() As String
Get
Return _Ubicacion
End Get
Set(ByVal value As String)
_Ubicacion = value
End Set
End Property
Private _Servidor As String
Public Property Servidor() As String
Get
Return _Servidor
End Get
Set(ByVal value As String)
_Servidor = value
End Set
End Property
Private _VersionSQL As String
Public Property VersionSQL() As String
Get
Return _VersionSQL
End Get
Set(ByVal value As String)
_VersionSQL = value
End Set
End Property
Private _BaseDatos As String
Public Property BaseDatos() As String
Get
Return _BaseDatos
End Get
Set(ByVal value As String)
_BaseDatos = value
End Set
End Property
Private _Usuario As String
Public Property Usuario() As String
Get
Return _Usuario
End Get
Set(ByVal value As String)
_Usuario = value
End Set
End Property
Private _Pass As String
Public Property Pass() As String
Get
Return _Pass
End Get
Set(ByVal value As String)
_Pass = value
End Set
End Property
Private _Sql As String
Public Property Sql() As String
Get
Return _Sql
End Get
Set(ByVal value As String)
_Sql = value
End Set
End Property
Private _Tabla As String
Public Property Tabla() As String
Get
Return _Tabla
End Get
Set(ByVal value As String)
_Tabla = value
End Set
End Property
Private _RutaFichero As String
Public Property RutaFichero() As String
Get
Return _RutaFichero
End Get
Set(ByVal value As String)
_RutaFichero = value
End Set
End Property
Private _Fichero As String
Public Property Fichero() As String
Get
Return _Fichero
End Get
Set(ByVal value As String)
_Fichero = value
End Set
End Property
Private _BDAbierta As Boolean
Public Property BDAbierta() As Boolean
Get
Return _BDAbierta
End Get
Set(ByVal value As Boolean)
_BDAbierta = value
End Set
End Property
Private _sError As String
Public Property sError() As String
Get
Return _sError
End Get
Set(ByVal value As String)
_sError = value
End Set
End Property
#End Region
#Region "MÉTODOS PÚBLICOS"
Public Sub Abrir()
Try
Conex = New SqlConnection(CadenaConex)
Conex.Open()
_BDAbierta = True
'If _VersionSQL = "" Then _VersionSQL = DameVersionSQL()
Catch ex As Exception
_sError = "BD.Abrir() : " & ex.Message
_BDAbierta = False
End Try
End Sub
Public Sub Cerrar()
Try
Conex.Close()
Conex = Nothing
_BDAbierta = False
Catch ex As Exception
_sError = "BD.Cerrar() : " & ex.Message
End Try
End Sub
Public Sub CambiarBD(StrBD As String)
Try
Conex.ChangeDatabase(StrBD)
Catch ex As Exception
_sError = "BD.CambiarBD() : " & ex.Message
End Try
End Sub
''' <summary>
''' Ejecutar Sentencia SQL,Previamente hay que dar valor a la Variable BD.SQL con la Sentencia a Ejecutar
''' </summary>
''' <param name="TiempoEspera"></param>
''' <remarks></remarks>
Public Sub EjecutaSQL(Optional sSQL As String = "", Optional TiempoEspera As Integer = 500)
If sSQL = "" Then sSQL = _Sql
Try
SqlComm = New SqlCommand(sSQL)
SqlComm.Connection = Conex
SqlComm.CommandTimeout = TiempoEspera
SqlComm.ExecuteNonQuery()
Catch ex As Exception
_sError = "BD.EjecutaSQL() : " & ex.Message
End Try
SqlComm.Dispose() : SqlComm = Nothing
End Sub
'******************** DATA READER
Public Function CrearDReader(StrSql) As SqlDataReader
CrearDReader = Nothing
Try
SqlComm = New SqlCommand(StrSql, Conex)
CrearDReader = SqlComm.ExecuteReader
Catch ex As Exception
_sError = "BD.CrearDReader() : " & ex.Message
End Try
SqlComm.Dispose() : SqlComm = Nothing
End Function
Public Sub CerrarDReader(oReader As SqlDataReader)
oReader.Close() : oReader = Nothing
End Sub
'********************* DATA TABLE
Public Function CrearDTable(StrSql As String) As DataTable
Dim SqlAdapter As New SqlDataAdapter()
SqlComm = New SqlCommand(StrSql)
SqlComm.Connection = Conex
SqlAdapter.SelectCommand = SqlComm
CrearDTable = New DataTable
Try
SqlAdapter.Fill(CrearDTable)
Catch ex As Exception
_sError = "BD.CrearDTable() : " & ex.Message
End Try
SqlAdapter.Dispose()
SqlComm.Dispose() : SqlComm = Nothing
End Function
Public Sub CerrarDTable(oDTable As DataTable)
oDTable.Dispose() : oDTable = Nothing
End Sub
''' <summary>
''' Importa Fichero de Texto a Tabla SQL Opcional usar Fichero de Formato FMT
''' </summary>
''' <param name="bBorrar">Opcional,Si es true Truca la Tabla SQL de destino</param>
''' <param name="FilaIni">Fila de inicio en el Txt</param>
''' <param name="FicheroFormato">Opciona,Ruta del Fichero de Formato FMT</param>
''' <remarks></remarks>
Public Sub ImportarFichero(Optional bBorrar As Boolean = True, Optional FilaIni As Integer = 2, Optional FicheroFormato As String = "")
Try
If bBorrar Then
_Sql = "TRUNCATE TABLE " & _Tabla
EjecutaSQL() : If _sError <> "" Then Exit Sub
End If
_Sql = "BULK INSERT " & _Tabla & " "
_Sql = _Sql & "FROM " & "'" & _RutaFichero & _Fichero & "' "
If FicheroFormato = "" Then
_Sql = _Sql & "WITH (ROWTERMINATOR ='\n',FIRSTROW = " & FilaIni & ")"
Else
_Sql = _Sql & "WITH (FIRSTROW = " & FilaIni & ",FORMATFILE = '" & FicheroFormato & "')"
End If
EjecutaSQL()
Catch ex As Exception
_sError = "BD.ImportarFichero() : " & ex.Message
End Try
End Sub
Public Sub ImportarAccess(sBDAccess As String, TablaAccess As String, TablaSQL As String, Optional bBorrar As Boolean = False)
Dim Tabla As DataTable = New DataTable
Dim AccessConex As OleDb.OleDbConnection
Dim AccessAdapter As OleDb.OleDbDataAdapter
Dim StrConex As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sBDAccess & ";"
AccessConex = New OleDb.OleDbConnection(StrConex)
Try
If bBorrar Then EjecutaSQL("TRUNCATE TABLE " & TablaSQL)
If _sError <> "" Then Exit Sub
AccessConex.Open()
AccessAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM " & TablaAccess, AccessConex)
AccessAdapter.Fill(Tabla)
AccessConex.Close() : AccessConex = Nothing
Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(Conex)
bulkCopy.DestinationTableName = TablaSQL
bulkCopy.BulkCopyTimeout = 900
'For Each Col As SqlBulkCopyColumnMapping In bulkCopy.ColumnMappings
' Debug.Print(Col.SourceColumn.ToString)
'Next
bulkCopy.WriteToServer(Tabla)
Catch ex As Exception
_sError = "BD.ImportarAccess() : " & ex.Message
End Try
End Sub
Public Sub ImportarExcel(RutaExcel As String, FicExcel As String, HojaExcel As String, TablaSQL As String, Optional bBorrar As Boolean = False)
Dim Tabla As DataTable = New DataTable
Dim ExcelConex As New OleDb.OleDbConnection
Dim ExcelAdapter As OleDb.OleDbDataAdapter
'Dim StrConex As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & RutaExcel & FicExcel & ";Extended Properties=""Excel 8.0;"
'Dim StrConex As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
' "Data Source= " & RutaExcel & FicExcel & _
' ";Extended Properties=""Excel 8.0;"""
Dim StrConex As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & RutaExcel & FicExcel & ";Extended Properties=Excel 8.0;"
'ExcelConex.ConnectionString = StrConex
ExcelConex = New OleDb.OleDbConnection(StrConex)
Try
If bBorrar Then EjecutaSQL("TRUNCATE TABLE " & TablaSQL)
If _sError <> "" Then Exit Sub
ExcelConex.Open()
ExcelAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM " & HojaExcel, ExcelConex)
ExcelAdapter.Fill(Tabla)
ExcelConex.Close() : ExcelConex = Nothing
Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(Conex)
bulkCopy.DestinationTableName = TablaSQL
bulkCopy.BulkCopyTimeout = 900
'For Each Col As SqlBulkCopyColumnMapping In bulkCopy.ColumnMappings
' Debug.Print(Col.SourceColumn.ToString)
'Next
bulkCopy.WriteToServer(Tabla)
Catch ex As Exception
_sError = "BD.ImportarExcel() : " & ex.Message
End Try
End Sub
'Public Function CrearDSet(StrSql As String) As DataSet
' Dim adapter As New SqlDataAdapter()
' adapter.TableMappings.Add("Table", "TEMP_PELICULAS")
' SqlComm = New SqlCommand(StrSql)
' SqlComm.Connection = Conn
' adapter.SelectCommand = SqlComm
' CrearDSet = New DataSet("TEMP_PELICULAS")
' adapter.Fill(CrearDSet)
'End Function
#End Region
#Region "MÉTODOS PRIVADOS"
Private Function DameVersionSQL() As String
On Error GoTo TrataError
Dim StrVersion As String
Dim oRs As SqlDataReader
Abrir()
_Sql = "Select @@version"
oRs = CrearDReader(_Sql)
oRs.Read()
StrVersion = oRs(0)
CerrarDReader(oRs)
Cerrar()
Return StrVersion
Exit Function
TrataError:
_sError = "BD.DameVersionSQL() : " & Err.Description
Return _sError
End Function
#End Region
#Region "TEST"
Private Sub test()
'Dim temp_RS As SqlDataReader
' DyC_RS.Add(0,
End Sub
#End Region
End Class
#Region "REFERENCIAS"
'******************************** SQL BULKCOPY ************************************************
'http://msdn.microsoft.com/es-es/library/System.Data.SqlClient.SqlBulkCopy%28v=vs.110%29.aspx
'**************************** FICHERO DE FORMATO FMT *******************************************
'8.0
'39
'1 SQLCHAR 0 50 "\t" 1 ID_OT Modern_Spanish_CI_AS
'2 SQLCHAR 0 50 "\t" 2 LUPA Modern_Spanish_CI_AS
'3 SQLCHAR 0 50 "\t" 3 MATRICULA_AUTOR Modern_Spanish_CI_AS
'4 SQLCHAR 0 255 "\t" 4 USUARIO_AUTOR Modern_Spanish_CI_AS
'5 SQLCHAR 0 500 "\t" 5 GRUPO_AUTOR Modern_Spanish_CI_AS
'6 SQLCHAR 0 50 "\t" 6 CIF Modern_Spanish_CI_AS
'7 SQLCHAR 0 255 "\t" 7 NOMBRE_CLIENTE Modern_Spanish_CI_AS
'8 SQLCHAR 0 500 "\t" 8 DESCRIPCION Modern_Spanish_CI_AS
'9 SQLCHAR 0 7000 "\t" 9 OBSERVACIONES Modern_Spanish_CI_AS
'10 SQLCHAR 0 500 "\t" 10 ESTADO Modern_Spanish_CI_AS
'11 SQLCHAR 0 500 "\t" 11 PRIORITARIO Modern_Spanish_CI_AS
'12 SQLCHAR 0 500 "\t" 12 MACROLAN Modern_Spanish_CI_AS
'13 SQLCHAR 0 500 "\t" 13 VPN_IP Modern_Spanish_CI_AS
'14 SQLCHAR 0 500 "\t" 14 N_CONEXIONES Modern_Spanish_CI_AS
'15 SQLCHAR 0 500 "\t" 15 FASE_ARGOS Modern_Spanish_CI_AS
'16 SQLCHAR 0 500 "\t" 16 TAREAS_ARGOS_PTES Modern_Spanish_CI_AS
'17 SQLCHAR 0 500 "\t" 17 FASE_ODPV Modern_Spanish_CI_AS
'18 SQLCHAR 0 500 "\t" 18 TAREAS_RTB_PTES Modern_Spanish_CI_AS
'19 SQLCHAR 0 500 "\t" 19 FASE_CAV Modern_Spanish_CI_AS
'20 SQLCHAR 0 500 "\t" 20 TAREAS_CAV_PTES Modern_Spanish_CI_AS
'21 SQLCHAR 0 500 "\t" 21 MATRICULA_RESPONSABLE Modern_Spanish_CI_AS
'22 SQLCHAR 0 500 "\t" 22 USUARIO_RESPONSABLE Modern_Spanish_CI_AS
'23 SQLCHAR 0 500 "\t" 23 GRUPO_RESPONSABLE Modern_Spanish_CI_AS
'24 SQLCHAR 0 500 "\t" 24 PFI Modern_Spanish_CI_AS
'25 SQLCHAR 0 500 "\t" 25 PFA Modern_Spanish_CI_AS
'26 SQLCHAR 0 500 "\t" 26 OFERTA Modern_Spanish_CI_AS
'27 SQLCHAR 0 500 "\t" 27 SOLICITUD_PEDIDO Modern_Spanish_CI_AS
'28 SQLCHAR 0 500 "\t" 28 FECHA_ALTA Modern_Spanish_CI_AS
'29 SQLCHAR 0 500 "\t" 29 FECHA_ENTRADA Modern_Spanish_CI_AS
'30 SQLCHAR 0 500 "\t" 30 FECHA_EN_ELABORACION Modern_Spanish_CI_AS
'31 SQLCHAR 0 500 "\t" 31 FECHA_PTE_INFRAESTRUCTURA Modern_Spanish_CI_AS
'32 SQLCHAR 0 500 "\t" 32 DIAS_PTE_INFRAESTRUCTURA Modern_Spanish_CI_AS
'33 SQLCHAR 0 500 "\t" 33 FECHA_PTE_RFS Modern_Spanish_CI_AS
'34 SQLCHAR 0 500 "\t" 34 DIAS_PTE_RFS Modern_Spanish_CI_AS
'35 SQLCHAR 0 500 "\t" 35 FECHA_PTE_VENDEDOR Modern_Spanish_CI_AS
'36 SQLCHAR 0 500 "\t" 36 FECHA_ANULADO Modern_Spanish_CI_AS
'37 SQLCHAR 0 500 "\t" 37 FECHA_FINALIZADO Modern_Spanish_CI_AS
'38 SQLCHAR 0 500 "\t" 38 F_CONV_SIMPLE Modern_Spanish_CI_AS
'39 SQLCHAR 0 5000 "\n" 39 ULTIMO_COMENTARIO Modern_Spanish_CI_AS
''**************** SQL PROCEDIMIENTO ALMACENADO PARA GENERAR FICHERO DESDE TABLA ********************
'-- SELECT * FROM syscolumns WHERE ID = 738101670
'--EXEC SYS_TABLA_FICHERO_FMT 'A_MIDAS_COMPLEJOS_PEDIDOS'
'CREATE PROCEDURE SYS_TABLA_FICHERO_FMT
' @TABLA VARCHAR(100)
'AS
'DECLARE @nColumnas INT
'DECLARE @iCOLUMNA INT
'DECLARE @ID_TABLA INT
'--DECLARE @TABLA CHAR(200)
'DECLARE @VERSION CHAR(5)
'DECLARE @sFILA VARCHAR(500)
'DECLARE @SEPARADOR CHAR(5)
'-- *********** VARIABLES PARA LOS CAMPOS DE LA TABLA
'DECLARE @CAMPO CHAR(150)
'DECLARE @LONGITUD INT
'SELECT @VERSION = '8.0'
'--SELECT @TABLA = 'A_MIDAS_COMPLEJOS_PEDIDOS'
'SELECT @ID_TABLA = id FROM sysobjects WHERE name = @TABLA
'SELECT @nColumnas = COUNT(*) FROM SysColumns WHERE id = @ID_TABLA
'--PRINT @ID_TABLA
'PRINT @VERSION
'PRINT @nColumnas
'-- ******************** CURSOR PARA RECORRER LOS CAMPOS
'DECLARE RS CURSOR
'FOR SELECT Name,LENGTH FROM SysColumns WHERE ID = @ID_TABLA
'OPEN RS
'SELECT @iCOLUMNA = 1
'FETCH NEXT FROM RS
'INTO @CAMPO,@LONGITUD;
'WHILE @@FETCH_STATUS = 0
'BEGIN
' IF @iCOLUMNA = @nColumnas SELECT @SEPARADOR = '"\n"' ELSE SELECT @SEPARADOR = '"\t"'
' SELECT @sFILA = LTRIM(RTRIM(CONVERT(CHAR,@iCOLUMNA))) + CHAR(9)
' SELECT @sFILA = @sFILA + 'SQLCHAR' + CHAR(9)
' SELECT @sFILA = @sFILA + '0' + CHAR(9)
' SELECT @sFILA = @sFILA + LTRIM(RTRIM(CONVERT(CHAR,@LONGITUD))) + CHAR(9)
' SELECT @sFILA = @sFILA + @SEPARADOR + CHAR(9)
' SELECT @sFILA = @sFILA + LTRIM(RTRIM(CONVERT(CHAR,@iCOLUMNA))) + CHAR(9)
' SELECT @sFILA = @sFILA + LTRIM(RTRIM(@CAMPO)) + CHAR(9)
' SELECT @sFILA = @sFILA + 'Modern_Spanish_CI_AS'
' PRINT @sFILA
' --PRINT CONVERT(CHAR,@iCOLUMNA) + CHAR(9) + 'SQLCHAR' 0 50 "\t" 1 CODIGO Modern_Spanish_CI_AS
' SELECT @iCOLUMNA = @iCOLUMNA + 1
'FETCH NEXT FROM RS
'INTO @CAMPO,@LONGITUD;
'END
'CLOSE RS;
'DEALLOCATE RS;
#End Region
Comments