Imports System
Imports System.ComponentModel
Imports System.Collections
Imports System.Diagnostics
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class dbTools
      Implements IDisposable

  Private con As SqlConnection

  'Per l'esecuzione di stored procedures
  Public Function RunProcedure(ByVal procName As String) As Integer
    Dim cmd As SqlCommand = CreateCommand(procName, Nothing)
    cmd.ExecuteNonQuery()
    Me.CloseConnection()
    Return Convert.ToInt32(cmd.Parameters("ReturnValue").Value)
  End Function

  'Per l'esecuzione di stored procedures con parametri
  Public Function RunProcedure(ByVal procName As String, ByVal prams As SqlParameter()) As Integer
    Dim cmd As SqlCommand
    cmd = CreateCommand(procName, prams)
    cmd.ExecuteNonQuery()
    Me.CloseConnection()
    Return Convert.ToInt32(cmd.Parameters("ReturnValue").Value)
  End Function

  'Per l'esecuzione di stored procedures (restituisce un datareader)
  Public Sub RunProcedure(ByVal procName As String, ByRef dataReader As SqlDataReader)
    Dim cmd As SqlCommand = CreateCommand(procName, Nothing)
    dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
  End Sub

  'Per l'esecuzione di stored procedures con parametri(restituisce un datareader)
  Public Sub RunProcedure(ByVal procName As String, ByVal prams As SqlParameter(), ByRef dataReader As SqlDataReader)
    Dim cmd As SqlCommand = CreateCommand(procName, prams)
    dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
  End Sub

  'Crea un oggetto command
  Private Function CreateCommand(ByVal procName As String, ByVal prams As SqlParameter()) As SqlCommand
    OpenConnection()
    Dim cmd As SqlCommand = New SqlCommand(procName, con)
    cmd.CommandType = CommandType.StoredProcedure
    If Not IsNothing(prams) Then
      For Each parameter As SqlParameter In prams
        cmd.Parameters.Add(parameter)
      Next
    End If
    cmd.Parameters.Add(New SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, False, 0, 0, String.Empty,     DataRowVersion.Default, Nothing))
    Return cmd
  End Function

  'apre la connessione
  Private Sub OpenConnection()
    If IsNothing(con) Then
      con = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
      con.Open()
    End If
  End Sub
  
  'chiude la connessione
  Private Sub CloseConnection()
    If IsNothing(con) Then
      con.Close()
    End If
  End Sub

  Public Function MakeInParam(ByVal ParamName As String, ByVal DbType As SqlDbType, ByVal Size As Integer, ByVal Value As Object) As     SqlParameter
    Return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value)
  End Function

  Public Function MakeOutParam(ByVal ParamName As String, ByVal DbType As SqlDbType, ByVal Size As Integer) As SqlParameter
    Return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, Nothing)
  End Function

  Public Function MakeParam(ByVal ParamName As String, ByVal DbType As SqlDbType, ByVal Size As Integer, ByVal Direction As     ParameterDirection, ByVal Value As Object) As SqlParameter
    Dim param As SqlParameter
    If (Size > 0) Then
      param = New SqlParameter(ParamName, DbType, Size)
    Else
      param = New SqlParameter(ParamName, DbType)
    End If
    param.Direction = Direction
    If (Not (Direction = ParameterDirection.Output And IsNothing(Value))) Then
      param.Value = Value
    End If
    Return param
  End Function

  Public Function GetDataTable(ByVal Sql As String, ByVal ConnectString As String) As DataTable
    Dim da As SqlDataAdapter = New SqlDataAdapter(Sql, ConnectString)
    Dim dt As DataTable = New DataTable
    Dim rows As Integer = da.Fill(dt)
    Return dt
  End Function

  Public Sub Dispose() Implements System.IDisposable.Dispose
    If Not IsNothing(con) Then
      con.Dispose()
      con = Nothing
    End If
  End Sub

End Class