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 
Rispondi quotando
