codice:
NameSpace Libreria
    Public Class SqlTools

        Private _StringaConnessione As String
        Public Property StringaConnessione() As String
            Get
                Return _StringaConnessione
            End Get

            Set(ByVal Value As String)
                _StringaConnessione = 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

        ' Proprietà Private
        Private internal_SQLCONN As SqlConnection
        Private internal_READER As SqlDataReader
        Private internal_SQLCOMMAND As SqlCommand
        Private internal_SQLADAPTER As SqlDataAdapter
        Private internal_DATASET As DataSet

        Public Sub New()
            ' Settaggio StringaConn
            Me.StringaConnessione = ConfigurationManager.AppSettings("nomeapplicazione")
        End Sub

        Private Sub Connetti()
            Try
                internal_SQLCONN = New SqlConnection(StringaConnessione)
                internal_SQLCONN.Open()
            Catch ex As SqlException
                internal_SQLCONN.Close()
                Me._SQL = ""
                Me._StringaConnessione = ""
                Throw ex
            End Try
        End Sub

        Public Function EstraiDataSet(ByVal strNomeDataTable As String) As DataSet
            Connetti()
            internal_DATASET = New DataSet
            internal_SQLADAPTER = New SqlDataAdapter(Me._SQL, internal_SQLCONN)
            Try
                internal_SQLADAPTER.Fill(internal_DATASET, strNomeDataTable)
                Return internal_DATASET
            Catch ex As SqlException
                Throw ex
                Return Nothing
            Finally
                internal_SQLCONN.Close()
            End Try
        End Function

        Public Function EstraiDataTable(ByVal strNomeDataTable As String) As DataTable
            Connetti()
            internal_DATASET = New DataSet
            internal_SQLADAPTER = New SqlDataAdapter(Me._SQL, internal_SQLCONN)
            Try
                internal_SQLADAPTER.Fill(internal_DATASET, strNomeDataTable)
                Return internal_DATASET.Tables(0)
            Catch ex As SqlException
                Throw ex
                Return Nothing
            Finally
                internal_SQLCONN.Close()
            End Try
        End Function

        Public Shared Function EstraiDataTable_AutoSelect(ByVal strNomeTabella As String) As DataTable
            Dim db As New LIBRERIA.Database.SqlTools
            db.Connetti()
            db.internal_DATASET = New DataSet
            db.internal_SQLADAPTER = New SqlDataAdapter("SELECT * FROM " & strNomeTabella, db.internal_SQLCONN)
            Try
                db.internal_SQLADAPTER.Fill(db.internal_DATASET, strNomeTabella)
                Return db.internal_DATASET.Tables(0)
            Catch ex As SqlException
                Throw ex
                Return Nothing
            Finally
                db.internal_SQLCONN.Close()
            End Try
        End Function

        ' Restituisce un SqlDataReader
        Public Function EstraiDataReader() As SqlDataReader
            Connetti()
            internal_SQLCOMMAND = New SqlCommand(Me._SQL, internal_SQLCONN)
            Try
                internal_READER = internal_SQLCOMMAND.ExecuteReader()
                Return internal_READER
            Catch ex As SqlException
                Throw ex
                internal_SQLCONN.Close()
                Return Nothing
            Finally
                internal_SQLCOMMAND.Dispose()
            End Try
        End Function

        ' INSERT, UPDATE, DELETE
        Public Function ModificaDatabase() As Boolean
            Connetti()
            internal_SQLCOMMAND = New SqlCommand(Me._SQL, internal_SQLCONN)
            Try
                Dim affected As Integer
                ' Execute non query è per comandi non-select
                affected = internal_SQLCOMMAND.ExecuteNonQuery()
                If affected > 0 Then
                    Return True
                End If
            Catch ex As SqlException
                Throw ex
                Return False
            Finally
                internal_SQLCONN.Close()
                internal_SQLCOMMAND.Dispose()
            End Try
        End Function

        ' Restituisce un DataTable che contiene lo schema della tabella
        Public Function Schema() As DataTable
            Connetti()
            internal_SQLCOMMAND = New SqlCommand(Me._SQL, internal_SQLCONN)
            Dim internal_DataTable As DataTable
            Try
                internal_READER = internal_SQLCOMMAND.ExecuteReader(CommandBehavior.KeyInfo Or CommandBehavior.SchemaOnly)
                internal_DataTable = internal_READER.GetSchemaTable()
                Return internal_DataTable
            Catch ex As SqlException
                Return Nothing
            Finally
                internal_READER.Close()
                internal_SQLCONN.Close()
                internal_SQLCOMMAND.Dispose()
            End Try
        End Function

        ' ---------------------------------------------
        ' Esegue una Stored Procedure SENZA PARAMETRI
        ' ---------------------------------------------
        Public Sub EseguiStored(ByVal nomeSP As String)
            Dim ds As New DataSet
            Dim dt As New DataTable
            Dim cn As SqlConnection = New SqlConnection(Me._StringaConnessione)
            Dim cmd As SqlCommand = New SqlCommand(nomeSP, cn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = nomeSP
            Try
                cn.Open()
                cmd.ExecuteNonQuery()
            Catch dataex As SqlException
                Throw dataex
            Catch ex As Exception
                Throw ex
            Finally
                cn.Close()
                cmd.Dispose()
            End Try
        End Sub


        ' ------------------------------------------------------------------------
        ' Esegue una Stored Procedure SENZA PARAMETRI e restituisce un Datatable
        ' ------------------------------------------------------------------------
        Public Function EseguiStored_Datatable(ByVal nomeSP As String) As DataTable
            Dim ds As New DataSet
            Dim dt As New DataTable
            Dim cn As SqlConnection = New SqlConnection(Me._StringaConnessione)
            Dim cmd As SqlCommand = New SqlCommand(nomeSP, cn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = nomeSP
            Dim adapter As New SqlDataAdapter(cmd)
            Try
                cn.Open()
                adapter.Fill(ds)
                dt = ds.Tables(0)
                Return dt
            Catch dataex As DataException
                Throw dataex
            Catch ex As Exception
                Throw ex
            Finally
                cn.Close()
                cmd.Dispose()
            End Try
        End Function

        ' ---------------------------------------------
        ' Esegue una Stored Procedure CON PARAMETRI
        ' ---------------------------------------------
        Public Sub EseguiStoredConParametri(ByVal nomeSP As String, ByVal pars As SqlParameter())
            Dim cn As SqlConnection = New SqlConnection(Me._StringaConnessione)

            Dim cmd As SqlCommand = New SqlCommand
            cmd.CommandText = nomeSP ' Il commandtext è la SP ricevuta
            cmd.Connection = cn ' Assegna la connessione

            cmd.CommandType = CommandType.StoredProcedure
            Dim ad As SqlDataAdapter
            For Each p As SqlParameter In pars
                cmd.Parameters.Add(p)
            Next
            Try
                cn.Open()
                cmd.ExecuteNonQuery()
            Catch dataex As DataException
                Throw dataex
            Catch ex As Exception
                Throw ex
            Finally
                cn.Close()
                cmd.Dispose()
            End Try
        End Sub

        ' ------------------------------------------------------------------------
        ' Esegue una Stored Procedure CON PARAMETRI e restituisce un DTable
        ' ------------------------------------------------------------------------
        Public Function EseguiStoredConParametri_Datatable(ByVal nomeSP As String, ByVal pars As SqlParameter()) As DataTable
            Dim ds As New DataSet
            Dim dt As New DataTable
            Dim cn As SqlConnection = New SqlConnection(Me._StringaConnessione)
            Dim cmd As SqlCommand = New SqlCommand(nomeSP, cn)

            cmd.CommandType = CommandType.StoredProcedure

            For Each p As SqlParameter In pars
                cmd.Parameters.Add(p)
            Next

            Dim adapter As New SqlDataAdapter(cmd)
            Try
                cn.Open()
                adapter.Fill(ds)
                dt = ds.Tables(0)
                Return dt
            Catch dataex As DataException
                Throw dataex
            Catch ex As Exception
                Throw ex
            Finally
                cn.Close()
                cmd.Dispose()
            End Try
        End Function
End Class
End NameSpace
e' un vecchio codice che avrà anche delle imperfezioni, ma tutto sommato funziona e fa il suo sporco lavoro