Usage:
Public db As New SimpleDataAccess
Public RS As New DataTable
db.ConnectionStringName = "DEV"
db.ConnectDatabase()
db.Execute_Query(RS, "SELECT * FROM whatever WHERE IntColumn = " & tools.parseint(EmployeeID, 0) & " or TextColumn = '" & db.Escape("bla'blabla") & "'")
For Each DB_Row As DataRow In RS.Rows
response.write(DB_Row("IntColumn"))
Next
db.CloseDatabase()
Class:
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Net.Mail
Public Class SimpleDataAccess
Private m_db As New SqlConnection
Private m_db_Command As New SqlCommand
Private m_db_DataAdapter As New SqlDataAdapter
Private m_db_ConnectionStringName As String
Private m_db_ConnectionServerName As String
Private m_db_ConnectionDatabaseName As String
Private m_db_ConnectionUserName As String
Private m_db_ConnectionPassword As String
Private m_ErrorDesc As String
Private m_EmailSender As New SendEmail
Private m_InvokingWebPage As String
Private m_SQLstr As String
Property ConnectionStringName() As String
Get
Return m_db_ConnectionStringName
End Get
Set(ByVal Value As String)
m_db_ConnectionStringName = Value
End Set
End Property
Property SqlQueryToRun() As String
Get
Return m_SQLstr
End Get
Set(ByVal value As String)
m_SQLstr = value
End Set
End Property
Property InvokingWebPage() As String
Get
Return m_InvokingWebPage
End Get
Set(ByVal value As String)
m_InvokingWebPage = value
End Set
End Property
ReadOnly Property GetError() As String
Get
Return m_ErrorDesc
End Get
End Property
ReadOnly Property ConnectionStatus() As System.Data.ConnectionState
Get
Return m_db.State
End Get
End Property
Public Sub SetConnectionDetails(ByVal ServerName As String, ByVal DatabaseName As String, ByVal UserName As String, ByVal Password As String)
m_db_ConnectionStringName = ""
m_db_ConnectionServerName = ServerName
m_db_ConnectionDatabaseName = DatabaseName
m_db_ConnectionUserName = UserName
m_db_ConnectionPassword = Password
End Sub
Public Sub CloseDatabase()
'#### Closes the DB connection in the application pool if its open
Try
If m_db.State = ConnectionState.Open Then
m_db.Close()
End If
Catch ex As Exception
End Try
End Sub
Public Function Escape(ByVal StrValue As String) As String
Return Replace(StrValue, "'", "''")
End Function
Public Function PrepDate(ByVal StrDate As String, Optional ByVal DateType As String = "DATE") As String
If Trim(StrDate) = "" Then
Return "NULL"
Else
Return "CONVERT(" & DateType & ", '" & Escape(Trim(StrDate)) & "', 103)"
End If
End Function
Public Function ConnectDatabase() As Boolean
'#### Check the connection is not already open
If m_db.State = ConnectionState.Closed Then
'#### Do we have a valid connection string?
If m_db_ConnectionStringName = "" Then
'#### The connection string name has not been set, have bespoke paramaters been provided?
If m_db_ConnectionServerName = "" Or m_db_ConnectionDatabaseName = "" Or m_db_ConnectionUserName = "" Then
m_ErrorDesc = "ERROR: When leaving ConnectionStringName() blank, you must set ConnectionDetails() instead."
Return False
Else
m_db.ConnectionString = "Data Source=" & m_db_ConnectionServerName & ";Initial Catalog=" & m_db_ConnectionDatabaseName & ";User Id=" & m_db_ConnectionUserName & ";Password=" & m_db_ConnectionPassword & ";"
End If
Else
'#### Check the connection string exists
Try
m_db.ConnectionString = ConfigurationManager.ConnectionStrings(m_db_ConnectionStringName).ConnectionString
Catch ex As Exception
m_ErrorDesc = "Error setting connection string from ConfigurationManager.ConnectionStrings(" & m_db_ConnectionStringName & ").ConnectionString: " & ex.Message
EmailError(m_ErrorDesc)
Return False
End Try
End If
'#### Its closed, attempt to open
Try
m_db.Open()
Return True
Catch ex As Exception
m_ErrorDesc = "Error connecting database:<br />" & ex.Message & "<br /><br />Connection String:<br />" & m_db.ConnectionString
EmailError(m_ErrorDesc)
Return False
End Try
Else
'#### state is not closed, check its open
If m_db.State = ConnectionState.Open Then
Return True
Else
'#### its not closed, and not open suggesting some bad mid way status, alert and fail:
m_ErrorDesc = "Error connecting database: The Connection state was neither Open nor Closed, This confused the ConnectDatabase() method. State was '" & m_db.State.ToString '"
EmailError(m_ErrorDesc)
Return False
End If
End If
End Function
Public Function Execute_NonQuery(Optional ByVal StrQuery As String = "", Optional ByRef RowsAffected As Integer = 0) As Boolean
If StrQuery <> "" Then
m_SQLstr = StrQuery
End If
If m_SQLstr = "" Then
m_ErrorDesc = "Error running query: Query text was blank"
EmailError(m_ErrorDesc)
Return False
Else
Try
m_db_Command.Connection = m_db
m_db_Command.CommandText = m_SQLstr
RowsAffected = m_db_Command.ExecuteNonQuery()
Return True
Catch ex As Exception
m_ErrorDesc = "Error running query: " & ex.Message & "<br /><br />Query:<hr />" & m_SQLstr
EmailError(m_ErrorDesc)
Return False
End Try
End If
End Function
Public Function Execute_Query(ByRef TargetDataTable As DataTable, Optional ByVal StrQuery As String = "") As Boolean
If StrQuery <> "" Then
m_SQLstr = StrQuery
End If
If m_SQLstr = "" Then
m_ErrorDesc = "Error running query: Query text was blank"
EmailError(m_ErrorDesc)
Return False
Else
Try
NukeDataTable(TargetDataTable)
m_db_Command.CommandText = m_SQLstr
m_db_Command.Connection = m_db
m_db_DataAdapter.SelectCommand = m_db_Command
m_db_DataAdapter.Fill(TargetDataTable)
m_db_DataAdapter.Dispose()
Return True
Catch ex As Exception
m_ErrorDesc = "Error running query: " & ex.Message & "<br /><br />Query:<hr />" & m_SQLstr
EmailError(m_ErrorDesc)
Return False
End Try
End If
End Function
Public Function GetDataTable(ByVal StrQuery As String) As DataTable
Dim RS_ToReturn As New DataTable
Execute_Query(RS_ToReturn, StrQuery)
Return RS_ToReturn
End Function
Public Sub NukeDataTable(ByRef DataTable As DataTable)
Try
DataTable.Rows.Clear()
DataTable.Columns.Clear()
DataTable.Clear()
DataTable.Reset()
Catch ex As Exception
End Try
End Sub
Private Sub EmailError(ByVal MessageStr As String)
m_EmailSender.AddRecipient("")
m_EmailSender.SendEmailMessage("whatever@whatever.com", "Data Error", "An Error Occured in " & m_InvokingWebPage & "<br /><br />The Error was:<br />" & MessageStr & ".")
End Sub
End Class
Edited by admin on 01-03-2013 16:37
Jordon Pilling | Heavencore Administrator
|