VB.NET Basic Database Query Examples

Here is my compilation of example code for a variety of database operations written in VB.NET These range from:

1. Using ExecuteScalar to run a database command and get a single result back
2. Using ExecuteNonQuery() to run a command where you don’t want or care for any return communication from your database
3. Using ExecuteReader() to quickly loop through a database query result set
4. Using a SqlDataAdapter to get back one or more data sets that you can further manipulate in code or bind directlyl to a GridView control

ExecuteScalar

ExecuteScalar will retrieve a single value, which is the first column of the first row of the query run in your database. As an example, you could use this to return an identity value from an insert statement in your stored procedure.

Dim strId As String = hdnId.Text
Dim strResult As String = ""
Dim strConn As String = ConfigurationManager.ConnectionStrings("TestDbConn").ConnectionString
Using conn As New SqlConnection(strConn)
Dim cmd As New SqlCommand()
cmd.Connection = conn
cmd.CommandText = "testStoredProcedure"
cmd.Parameters.AddWithValue("TableId", strId)
cmd.CommandTimeout = 30
cmd.CommandType = CommandType.StoredProcedure
conn.Open()
strResult = CStr(cmd.ExecuteScalar)
End Using

ExecuteNonQuery

In the case of ExecuteNonQuery, you are not really interested in any sort of return result from your database. You just want to run a command and thats it. You might do this if you are updating, inserting, or deleting a database record based on information that a user has filled out on your Web page.

Dim tmphdnId As String = hdnId.Text
Dim tmphdnActionId As String = hdnActionId.Text
Dim tmphdnTelephoneId As String = hdnTelephoneId.Text
Dim strUserId As String = hdnUserId.Text
Dim strConn As String = ConfigurationManager.ConnectionStrings("TestDbConn").ConnectionString
Using conn As New SqlConnection(strConn)
Dim cmdCommand As SqlCommand = conn.CreateCommand()
cmdCommand.CommandText = "TestStoredProcedure"
cmdCommand.CommandType = CommandType.StoredProcedure
cmdCommand.Parameters.AddWithValue("@TableId", tmphdnId)
cmdCommand.Parameters.AddWithValue("@TestActionId", tmphdnActionId)
cmdCommand.Parameters.AddWithValue("@PhoneId", tmphdnTelephoneId)
cmdCommand.Parameters.AddWithValue("@UserId", strUserId)
cmdCommand.Connection.Open()
cmdCommand.ExecuteNonQuery()
End Using

ExecuteReader

ExecuteReader is a lightweight operation that lets you quickly loop though the result of a query to your database.Use ExecuteReader preferentially to using the much more resource intensive SQLDataAdapter with SQLDataTables because it consumes significantly less server memory.

The SQLDataReader returned from ExecuteReader is a forward-only object with performance gains over using the much heavier SQLDataAdapter object, especially as the size of the result-set increases. The down-side is, of course, that the reader is forward-only and only has one record in memory at a time. But if you are just looking to read information from your query that returns a single table of data, then using ExecuteReader should impact performance much less than the alternative, especially under heavier server loads.

The example below shows how to hook up your DataReader results directly to a GridView control. If you’d rather loop through your results, you can instead use a while loop as:

While dr.Read
Dim strFirstResult As String = dr("TestValue")
End While

Here is the example DataReader code:

Dim strId As String = "001234"
Dim strConn As String
strConn = ConfigurationManager.ConnectionStrings("TestDbConn").ConnectionString
Using conn As New SqlConnection(strConn)
Dim cmdCommand As SqlCommand = conn.CreateCommand()
cmdCommand.CommandText = "TestStoredProcedure"
cmdCommand.CommandType = CommandType.StoredProcedure
Dim paramTestParameter As New SqlParameter("TestId", SqlDbType.Int, 32)
paramTestParameter.Value = CType(strId, Int32)
cmdCommand.Parameters.Add(paramTestParameter)
conn.Open()
Using dr As SqlDataReader = cmdCommand.ExecuteReader()
GridView1.DataSource = dr
GridView1.DataBind()
End Using
End Using

SqlDataAdapter

Use the SqlDataAdapter object if you want the full functionality of communicating with the database and the flexibility of working with the results in your code. Take a look at the ExecuteReader method instead if you only want to read once from your returned result-set. The SqlDataAdapter object takes much more server memory, especially as the resultset size increases, and you need to wait until your data is loaded into server memory before you can use the SqlDataAdapter object. The SqlDataAdapter object scales poorly under heavier server loads as compared to using a SQLDataReader.

However, once you have the SqlDataAdapter loaded, you are not limited to a single read-through of the data… you can look through the returned DataSet as often as you like and can further apply filters, including applying Linq filters. Also a nice feature is that you can return the results of multiple queries that you can use to populate one or more DataTables with. Encapsulating multiple result sets in a single call to your database is especially useful if you want to minimize the back and forth communication between your Web server and your database server.

The example code below shows how to run a stored procedure that returns the results of three separate select statements, and then how to populate the DataTable objects with the results of two of the queries, and to populate a GridView control with the results of the third result.

Dim strUserIdButtonAs String = "None"
Dim strUserAddressAddNewButton As String = "none"
Dim strId As String = "001234"
Dim dtUserIdButtonsAs DataTable
Dim dtAddNewButton As DataTable
Dim strConn As String = ConfigurationManager.ConnectionStrings("TestDbConn").ConnectionString
Using connection As New SqlConnection(strConn)
Dim objDataSet As New DataSet()
Dim cmd As New SqlCommand()
cmd.Connection = connection
cmd.CommandText = "TestStoredProcedure"
cmd.Parameters.AddWithValue("TestTableId", strId)
cmd.CommandTimeout = 30
cmd.CommandType = CommandType.StoredProcedure
Using adapter As New SqlDataAdapter()
adapter.SelectCommand = cmd
adapter.Fill(objDataSet)
dtUserIdButtons= objDataSet.Tables(0)
dtAddNewButton = objDataSet.Tables(1)
GridView1.DataSource = dtCycleButtons
GridView1.DataBind()
End Using
End Using

strUserIdButton= dtCycleButtons.Rows(0).Item("ShowUserButton")
strUserAddressAddNewButton = dtAddNewButton.Rows(0).Item("ShowAddressButton")