VB.NET - Simple Database Connection
This simple class helps in running commands in a MS SQL database. It requires a connection string, and RunCommand and RunCommandAsync require a string parameter to use as the command.
What's my connection string?
Code:
''' <summary>
''' Allows easy access to a database.
''' </summary>
Public Class DatabaseManager
Private SqlConnection As SqlClient.SqlConnection
Private SqlCommand As SqlClient.SqlCommand
Private SqlReader As SqlClient.SqlDataReader
''' <summary>
''' Creates a new connection to a database using the provided connection string.
''' </summary>
Public Sub New(ConnectionString As String)
SqlConnection = New SqlClient.SqlConnection(ConnectionString)
End Sub
''' <summary>
''' Synchronously runs an SQL command.
''' </summary>
Public Function RunCommand(CommandString As String) As List(Of List(Of Object))
SqlConnection.Open()
SqlCommand = New SqlClient.SqlCommand(CommandString, SqlConnection)
SqlReader = SqlCommand.ExecuteReader()
Dim Returns As New List(Of List(Of Object))
While SqlReader.Read()
Returns.Add(New List(Of Object))
For i As Integer = 0 To SqlReader.FieldCount - 1 Step 1
Returns(Returns.Count - 1).Add(SqlReader.Item(i))
Next
End While
SqlConnection.Close()
Return Returns
End Function
''' <summary>
''' Asynchronously runs an SQL command.
''' </summary>
Public Async Function RunCommandAsync(CommandString As String) As Task(Of List(Of List(Of Object)))
Await SqlConnection.OpenAsync()
SqlCommand = New SqlClient.SqlCommand(CommandString, SqlConnection)
SqlReader = Await SqlCommand.ExecuteReaderAsync()
Dim Returns As New List(Of List(Of Object))
While SqlReader.Read()
Returns.Add(New List(Of Object))
For i As Integer = 0 To SqlReader.FieldCount - 1 Step 1
Returns(Returns.Count - 1).Add(SqlReader.Item(i))
Next
End While
SqlConnection.Close()
Return Returns
End Function
End Class
Usage:
Code:
Private Sub SomeSqlFunction()
DBM = New DatabaseManager("Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;")
DBM.RunCommand("CREATE TABLE People (LName varchar(255), FName varchar(255), MInitial varchar(255));")
DBM.RunCommand("INSERT INTO People (LName, FName, MInitial) VALUES ('Johnson', 'Bill', 'H');")
DBM.RunCommand("INSERT INTO People (LName, FName) VALUES ('Miller', 'Alex');")
Dim Result As List(Of List(Of Object)) = DBM.RunCommand("SELECT * FROM People")
' Displaying the contents.
Dim MessageString As String = ""
For Each R In Result
For Each C In R
MessageString &= $"{C.ToString()} "
Next
MessageString &= Environment.NewLine
Next
MessageBox.Show(MessageString)
End Sub
Best of luck,
~Nic
Re: VB.NET - Simple Database Connection
Any data access code that doesn't provide the facility to insert values as parameters is fatally flawed. It means that you need to mess around with date formatting and escaping single quotes and it also leaves you open to SQL injection unless you write extra, possibly error-prone sanitation code.