Imports System.Data.SqlClient
Imports System
Imports System.Data
Public Class XSQL
Private rows As Int32, cols As Int16, retVal As Int64
Protected adoCon As New SqlConnection()
Protected adoCmd As New SqlCommand()
Protected adoDR As SqlDataReader 'if the user needs a data reader
Protected adoDA As New SqlDataAdapter() 'if the user needs a data set
Protected adoDS As New DataSet() 'if the user needs a data set
Private myTrans As SqlTransaction
Public Shared Function getConnString(ByVal AppId As String) As String
Dim userId As String = "XXXXX"
Dim pwd As String = "XXXXX"
Dim connStr1 As String
Dim connStr2 As String = "uid=" & userId & ";password=" & pwd & ";Connect Timeout=30"
Select Case AppId
Case "XXXXX"
connStr1 = "server=XXXXX;database=XXXXX;"
End Select
Return (connStr1 & connStr2)
End Function
Public Sub BeginTran()
myTrans = adoCon.BeginTransaction(IsolationLevel.ReadCommitted, "xTran")
adoCmd.Transaction = myTrans
End Sub
Public Sub CommitTran()
myTrans.Commit()
End Sub
Public Sub RollbackTran()
myTrans.Rollback("xTran")
End Sub
Sub New(ByVal AppId As String, Optional ByVal cmdType As Integer = 1, Optional ByVal rsType As Integer = 1)
'cmdType 1 = sqlcommand; cmdType 2 = stored procedure
'rsType 1 returns DataReader; rsType 2 returns dataset
adoCon.ConnectionString = getConnString(AppId)
If cmdType = 2 Then
adoCmd.CommandType = CommandType.StoredProcedure
Else
adoCmd.CommandType = CommandType.Text
End If
adoCmd.CommandTimeout = 120
adoCmd.Connection = adoCon
'if returning a dataset, dont ve to open the connection; the sqldataAdapter takes care of it
'open the connection only if returning dataReader
If rsType = 1 Then
adoCon.Open()
End If
End Sub
Public Sub AddDateParam(ByVal pName As String, ByVal pValue As DateTime)
Dim myParm As SqlParameter = adoCmd.Parameters.Add(pName, SqlDbType.DateTime)
myParm.Value = pValue
End Sub
Public Sub AddSmallDateParam(ByVal pName As String, ByVal pValue As DateTime)
Dim myParm As SqlParameter = adoCmd.Parameters.Add(pName, SqlDbType.SmallDateTime)
myParm.Value = pValue
End Sub
Public Sub AddIntParam(ByVal pName As String, ByVal pValue As Integer)
Dim myParm As SqlParameter = adoCmd.Parameters.Add(pName, SqlDbType.Int)
myParm.Value = pValue
End Sub
Public Sub AddBitParam(ByVal pName As String, ByVal pValue As Boolean)
Dim myParm As SqlParameter = adoCmd.Parameters.Add(pName, SqlDbType.Bit)
myParm.Value = pValue
End Sub
Public Sub AddStrParam(ByVal pName As String, ByVal pValue As String, ByVal pLen As Integer)
Dim myParm As SqlParameter = adoCmd.Parameters.Add(pName, SqlDbType.NVarChar, pLen)
myParm.Value = pValue
End Sub
Public Sub AddIntOutParam(ByVal pName As String)
Dim myParm As SqlParameter = adoCmd.Parameters.Add(pName, SqlDbType.Int)
myParm.Direction = ParameterDirection.Output
End Sub
Public Sub ClearCmdParam()
adoCmd.Parameters.Clear()
End Sub
'DataReader - execute query that returns rows
Public Function ExecuteQ(ByVal cmdTxt As String) As SqlDataReader
adoCmd.CommandText = cmdTxt
adoDR = adoCmd.ExecuteReader
ExecuteQ = adoDR
End Function
'DataReader - execute sql (non query) that does not return rows
Public Function ExecuteNQ(ByVal cmdTxt As String) As Integer
adoCmd.CommandText = cmdTxt
rows = adoCmd.ExecuteNonQuery
ExecuteNQ = rows
End Function
'DataReader - execute scalar query - returns 1 value
Public Function ExecuteSc(ByVal cmdTxt As String) As Object
adoCmd.CommandText = cmdTxt
ExecuteSc = adoCmd.ExecuteScalar
End Function
'DataSet - execute query that returns a data set
Public Function ExecuteDS(ByVal cmdTxt As String) As DataSet
adoCmd.CommandText = cmdTxt
adoDA.SelectCommand = adoCmd
adoDA.Fill(adoDS, "SQLDS")
rows = adoDS.Tables(0).Rows.Count
cols = adoDS.Tables(0).Columns.Count
ExecuteDS = adoDS
End Function
'Insert performing an insert and retrieving 1 output parameter
Public Function InsertWithReturn(ByVal cmdTxt As String, ByVal pName As String) As Integer
adoCmd.CommandText = cmdTxt
adoCmd.ExecuteNonQuery()
InsertWithReturn = Convert.ToInt32(adoCmd.Parameters(pName).Value)
End Function
Public Sub CleanUp()
If Not adoDR Is Nothing Then
If Not adoDR.IsClosed Then
adoDR.Close()
End If
End If
adoCon.Close()
adoDA = Nothing
adoDS = Nothing
adoDR = Nothing
adoCon = Nothing
adoCmd = Nothing
End Sub
End Class
