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?
Usage: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
Best of luck,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
~Nic




Reply With Quote
