Visual Basic Code Snippet - Get First Row As Object Array From SqlDataReader
(VB) Visual Basic code snippet execute a SQL statement using open database connection and return all the columns data in SqlDataReader for first row. GetSqlDataReaderFirstRow execute and close SqlDataReader and returns first data row as a dictionary object array.
Bookmark:
Visual Basic Code Snippet - Get First Row As Object Array From SqlDataReader
This .Net Visual Basic code snippet execute a SQL statement using open database connection and return all the columns data in SqlDataReader for first row. Additional rows are ignored. This function is useful for query for one data record row, after querying function will convert data row into dictionary object array and close SqlDataReader. Conversion of SqlDataReader into dictionary object array and closing SqlDataReader allows to create multiple object arrays without having to worry about opening more than one SqlDataReader for SQL Server. To use this function simply provide open database connection and SQL statement. This function uses SqlClient name space to get data using SqlDataReader. Modify the exception handling section for your project requirements.
Public Function GetSqlDataReaderFirstRow(ByRef _SqlConnection As System.Data.SqlClient.SqlConnection, ByVal _SQL As String) As Dictionary(Of String, Object) ' temporary dictionary object array to hold SqlDataReader first row Dim _FirstRow As New Dictionary(Of String, Object)() ' Set temporary variable to create data reader Dim _SqlDataReader As System.Data.SqlClient.SqlDataReader = Nothing ' lets run the given SQL statement and get the data to SqlDataReader Try ' Pass the connection to a command object Dim _SqlCommand As New System.Data.SqlClient.SqlCommand(_SQL, _SqlConnection) ' get query results _SqlDataReader = _SqlCommand.ExecuteReader() Catch _Exception As Exception ' Error occurred while trying to execute reader ' send error message to console (change below line to customize error handling) Console.WriteLine(_Exception.Message) ' failed SQL execution, lets return null Return Nothing End Try ' SQL successfully executed, lets return the SqlDataReader First Data Row If _SqlDataReader IsNot Nothing AndAlso _SqlDataReader.HasRows AndAlso _SqlDataReader.Read() Then ' put the first data row in a temporary object array before close SqlDataReader For _Column As Integer = 0 To _SqlDataReader.FieldCount - 1 Try ' Add each colum to dictionary _FirstRow.Add(_SqlDataReader.GetName(_Column), _SqlDataReader(_Column)) Catch End Try Next _Column ' close SqlDataReader _SqlDataReader.Close() _SqlDataReader.Dispose() Return _FirstRow Else If _SqlDataReader IsNot Nothing Then ' close SqlDataReader _SqlDataReader.Close() _SqlDataReader.Dispose() End If ' no data found, return null Return Nothing End If End Function
Here is a simple example showing how to use above function (GetSqlDataReaderFirstRow) to connect to SQL database and get Dictionary Object Array for given SQL statement.
' set temporary variable for database connection Dim _SqlConnection As New System.Data.SqlClient.SqlConnection() ' assign database connection string _SqlConnection.ConnectionString = "Server=SERVERADDRESS;Database=DATABASENAME;Uid=USERID;Pwd=PASSWORD;" ' Connect to database Try _SqlConnection.Open() Catch _Exception As Exception ' Error occurred while trying to connect to database Console.WriteLine(_Exception.Message) End Try ' Check for valid open database connection before query database If _SqlConnection IsNot Nothing AndAlso _SqlConnection.State = ConnectionState.Open Then ' Lets call above function to create a new SqlDataReader ' using open database connection and SQL statement ' Pass open database connection to function ' Pass SQL statement to create SqlDataReader Dim _SqlDataReaderFirstRow As Dictionary(Of String, Object) = GetSqlDataReaderFirstRow(_SqlConnection, "SELECT NAME, PRICE, COST FROM INVENTORY") ' Check we have data If _SqlDataReaderFirstRow IsNot Nothing AndAlso _SqlDataReaderFirstRow.Count > 0 Then 'Console.WriteLine(_SqlDataReaderFirstRow("NAME").ToString()); End If ' close database connection _SqlConnection.Close(); End If
VB Keywords Used:
- SqlDataReader
- SqlConnection
- ConnectionString
- SqlCommand
- ExecuteReader
- Exception
- Dictionary
- Try, Catch
- For, Next
- GetName
Code Snippet Information:
- Applies To: .Net, VB, Visual Basic, CLI, SQL, SqlDataReader, SQL Server, SQL Client, Database Connection, SQL Data Reader, Object Array
- Programming Language : VB (Visual Basic)
External Resources: