Answered by:
Async DB connections

Question
-
User1738843376 posted
Hi,
I've been trying to access my database using the async/await keywords alongside with OpenAsync and ExecuteReaderAsync.
Here is my code:
Database.vb:
Imports System.Data Imports System.Data.SqlClient Imports System.Threading Imports System.Threading.Tasks Imports Microsoft.VisualBasic Public Class Database Private _errorDetail As String = "" Public ReadOnly Property ErrorDetail() As String Get Return _errorDetail End Get End Property Public Sub New() End Sub Public Shared ReadOnly Property dbConnString() As String Get Return System.Configuration.ConfigurationManager.ConnectionStrings("SQLConnStr").ToString() End Get End Property Public Async Function ReadSQLDataWithCommandAsync(ByVal cmd As SqlCommand) As Task(Of DataSet) Dim dataSet As New DataSet Dim dataAdapter As New SqlDataAdapter(cmd) Dim myConnection As New SqlClient.SqlConnection(dbConnString) If myConnection.State = ConnectionState.Open Then myConnection.Close() End If Try Dim i As Integer = 0 await myConnection.OpenAsync() Using dataReader As SqlDataReader = Await cmd.ExecuteReaderAsync() While Await dataReader.ReadAsync dataSet.Tables(i).Load(dataReader) i += 1 End While End Using Catch sqlError As SqlTypes.SqlTypeException Logger.LogError("Database.ReadSQLDataWithCommandAsync", sqlError) _errorDetail = cmd.CommandText.ToString() & "<br />" & sqlError.Message.ToString() & "<br />" & sqlError.InnerException.ToString() Catch ex As Exception Logger.LogError("Database.ReadSQLDataWithCommandAsync", ex) _errorDetail = cmd.CommandText.ToString() & "<br />" & ex.Message.ToString() & "<br />" Finally myConnection.Close() dataAdapter.Dispose() End Try Return dataSet End Function End Class
Default.aspx.vb:
Imports System.Data Imports System.Data.SqlClient Imports System.Threading.Tasks Imports System.Web.Routing <Serializable> Public Class HomeProduct Public Property ProductId() As String Public Property Title() As String Public Property ImageUrl() As String Public Property LinkUrl() As String End Class Partial Class _Default Inherits System.Web.UI.Page <System.Web.Services.WebMethod()> Public Shared Async Function LoadProducts(ByVal catId As Integer) As Task(Of String) Dim propertiesList As New Dictionary(Of String, String) Try Logger.LogDebug("default.LoadProducts - catId : " & catId ) Dim sqlClass As New Database Dim sqlCmd As New SqlCommand Dim dataSet As New DataSet Dim sqlString As String = "" Dim productsList As New List(Of HomeProduct) With sqlCmd .CommandType = CommandType.StoredProcedure .CommandText = "mySP" .Parameters.Add("@catId ", SqlDbType.Int).Value = catId End With dataSet = Await Task(Of DataSet).Run(Function() sqlClass.ReadSQLDataWithCommandAsync(sqlCmd)) Logger.LogDebug("sqlClass.ErrorDetail: " & sqlClass.ErrorDetail) If sqlClass.ErrorDetail = "" Then If Not IsNothing(dataSet) Then If dataSet.Tables.Count > 0 Then If dataSet.Tables(0).Rows.Count > 0 Then For Each row As DataRow In dataSet.Tables(0).Rows() Dim currentProduct As New HomeProduct With currentProduct
.Title = row("title") & "" 'some more data actions End With productsList.Add(currentProduct) Next propertiesList.Add("productsList", Newtonsoft.Json.JsonConvert.SerializeObject(productsList, Newtonsoft.Json.Formatting.None)) propertiesList.Add("success", True) propertiesList.Add("error", False) propertiesList.Add("errorDetail", "") Else propertiesList.Add("success", False) propertiesList.Add("error", True) propertiesList.Add("errorDetail", "No Products located") End If Else propertiesList.Add("success", False) propertiesList.Add("error", True) propertiesList.Add("errorDetail", "No Products located") End If Else propertiesList.Add("success", False) propertiesList.Add("error", True) propertiesList.Add("errorDetail", "No Products located") End If Else propertiesList.Add("success", False) propertiesList.Add("error", True) propertiesList.Add("errorDetail", sqlClass.ErrorDetail) End If Catch ex As Exception Logger.LogError("default.LoadProducts", ex) propertiesList.Add("success", False) propertiesList.Add("error", True) propertiesList.Add("errorDetail", ex.Message.ToString()) End Try Return Newtonsoft.Json.JsonConvert.SerializeObject(propertiesList) End Function End ClassAnd i'm always getting the following error:
System.InvalidOperationException: Invalid operation. The connection is closed.
at System.Data.SqlClient.SqlCommand.RegisterForConnectionCloseNotification[T](Task`1& outterTask)
at System.Data.SqlClient.SqlCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at Database.VB$StateMachine_6_ReadSQLDataWithCommandAsync.MoveNext() in C:\Users\MYUSER\source\repos\AsyncTests\AsyncTests\App_Code\Database.vb:line 42Any idea on how to solve this?
Thursday, June 18, 2020 2:36 PM
Answers
-
User475983607 posted
There's no connection between the command the the connection object.
Imports System.Data.SqlClient Public Class Database Private Connectionstring As String Public Sub New() Connectionstring = ConfigurationManager.ConnectionStrings("DefaultConnection").ToString() End Sub Public Sub New(ConnectionString As String) Me.Connectionstring = ConnectionString End Sub Public Async Function FillDatasetByCommandAsync(ByVal cmd As SqlCommand) As Threading.Tasks.Task(Of DataSet) Return Await FillDatasetByCommandAsync(cmd, "Table1") End Function Public Async Function FillDatasetByCommandAsync(ByVal cmd As SqlCommand, ByVal TableName As String) As Threading.Tasks.Task(Of DataSet) Dim data As New DataSet Dim table As New DataTable(TableName) Using conn As New SqlConnection(Connectionstring) cmd.Connection = conn Await conn.OpenAsync() Using reader As SqlDataReader = Await cmd.ExecuteReaderAsync() table.Load(reader) End Using conn.Close() data.Tables.Add(table) End Using Return data End Function End Class
Imports System.Data.SqlClient Public Class DefaultAsync Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load RegisterAsyncTask(New PageAsyncTask(Function() PopulateGridView())) End Sub Private Async Function PopulateGridView() As Threading.Tasks.Task Dim db As New Database() Dim sqlCmd As New SqlCommand Dim queryString As String = "SELECT s.IncomeSourceId, s.[Name] AS SourceName, s.[Address], t.IncomeTypeId, t.[Name] AS TypeName FROM dbo.IncomeSource AS s INNER JOIN dbo.IncomeType AS t ON s.IncomeTypeId = t.IncomeTypeId" With sqlCmd .CommandType = CommandType.Text .CommandText = queryString End With GridView1.DataSource = Await db.FillDatasetByCommandAsync(sqlCmd) GridView1.DataBind() End Function End Class
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, June 18, 2020 4:29 PM -
User1738843376 posted
hi Mgebhard,
Once again, thx for your input.
I've tried adapting your code to my situation (i'm calling a webmethod from the HTML via JQuery AJAX, that in turn calls the LoadProducts, so i had to adapt from your example)
Imports System.Data Imports System.Data.SqlClient Imports System.Threading Imports System.Threading.Tasks Imports Microsoft.VisualBasic Public Class Database Private _errorDetail As String = "" Private dbConnString As String Public ReadOnly Property ErrorDetail() As String Get Return _errorDetail End Get End Property Public Sub New() Me.dbConnString = System.Configuration.ConfigurationManager.ConnectionStrings("SQLConnStr").ToString() End Sub Public Sub New(ByVal conn As String) Me.dbConnString = conn End Sub Public Async Function FillDatasetByCommandAsync(ByVal cmd As SqlCommand) As Threading.Tasks.Task(Of DataSet) Return Await FillDatasetByCommandAsync(cmd, "Table1") End Function Public Async Function FillDatasetByCommandAsync(ByVal cmd As SqlCommand, ByVal TableName As String) As Threading.Tasks.Task(Of DataSet) Dim data As New DataSet Dim table As New DataTable(TableName) Using conn As New SqlConnection(dbConnString) cmd.Connection = conn Await conn.OpenAsync() Using reader As SqlDataReader = Await cmd.ExecuteReaderAsync() table.Load(reader) End Using conn.Close() data.Tables.Add(table) End Using Return data End Function End Class
Imports System.Data Imports System.Data.SqlClient Imports System.Threading.Tasks Imports System.Web.Routing <Serializable> Public Class HomeProduct Public Property ProductId() As String Public Property Title() As String Public Property ImageUrl() As String Public Property LinkUrl() As String Public Property DiscountTag() As String Public Property PriceTag() As String Public Property InStock() As String Public Property Buyable() As Boolean End Class Partial Class _Default Inherits System.Web.UI.Page <System.Web.Services.WebMethod()> Public Shared Async Function LoadProducts(ByVal superCategoryId As Integer) As Task(Of String) Logger.LogDebug("STARTED WEBMETHOD") Dim propertiesList As New Dictionary(Of String, String) Dim sqlClass As New Database Dim sqlCmd As New SqlCommand Dim dataSet As New DataSet Dim sqlString As String = "" Dim productsList As New List(Of HomeProduct) With sqlCmd .CommandType = CommandType.StoredProcedure .CommandText = "wec_sp_website_get_most_recently_bought" .Parameters.Add("@SupercategoryId", SqlDbType.Int).Value = superCategoryId End With dataSet = Await sqlClass.FillDatasetByCommandAsync(sqlCmd) If sqlClass.ErrorDetail = "" Then If Not IsNothing(dataSet) Then If dataSet.Tables.Count > 0 Then If dataSet.Tables(0).Rows.Count > 0 Then For Each row As DataRow In dataSet.Tables(0).Rows() Dim currentProduct As New HomeProduct With currentProduct Dim urlTitle As String = "" Dim objRegEx As New Regex("[^0-9a-zA-Z\-]+?") Dim fullTitle As String = row("title") & "" Dim mainDescription As String = row("MainDescription") & "" Dim variantDescription As String = row("VariantDescription") & "" End With productsList.Add(currentProduct) Next propertiesList.Add("productsList", Newtonsoft.Json.JsonConvert.SerializeObject(productsList, Newtonsoft.Json.Formatting.None)) propertiesList.Add("success", True) propertiesList.Add("error", False) propertiesList.Add("errorDetail", "") Else propertiesList.Add("success", False) propertiesList.Add("error", True) propertiesList.Add("errorDetail", "No Products located") End If Else propertiesList.Add("success", False) propertiesList.Add("error", True) propertiesList.Add("errorDetail", "No Products located") End If Else propertiesList.Add("success", False) propertiesList.Add("error", True) propertiesList.Add("errorDetail", "No Products located") End If Else propertiesList.Add("success", False) propertiesList.Add("error", True) propertiesList.Add("errorDetail", sqlClass.ErrorDetail) End If Return Newtonsoft.Json.JsonConvert.SerializeObject(propertiesList) End Function End Class
<head runat="server"> <title>Test</title> <script src="scripts/jquery-3.4.1.js"></script> <script type="text/javascript"> function loadProducts(catId) { var data = {}; var options = {}; data.superCategoryId = catId; options.url = "default.aspx/LoadProducts"; options.type = "POST"; options.data = JSON.stringify(data); options.contentType = 'application/json; charset=utf-8'; options.processData = false; return $.ajax(options).done(function (response) { var data = response.d; data = $.parseJSON(data); if (data.success == "True") { console.log("Loaded data " + cat); console.log("data: " + data); } }); } $( document ).ready(function() { loadProducts(1); //loadProducts(2); //loadProducts(3); //loadProducts(4); }); </script> </head> <body> <form id="form1" runat="server"> <div> TEST <%=Date.UtcNow.ToString %> </div> </form> </body>
I can track the code running until it starts FillDatasetByCommandAsync, but from there on, the system hangs
What am i doing wrong?
P.S. I've also tested adding "Asynchronous Processing=True;" to the connection string, but with the same result.
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, June 18, 2020 6:21 PM -
User1738843376 posted
I was finally able to make it work. It was missing .ConfigureAwait(False) on all await commands. Ex:
Using reader As SqlDataReader = Await cmd.ExecuteReaderAsync().ConfigureAwait(False)
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Friday, June 19, 2020 4:24 PM
All replies
-
User1738843376 posted
this is the error im getting on prefix:
System.UnauthorizedAccessException: Access to the path 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\Temporary ASP.NET Files\vs\055612f5\696e8f9b\App_Web_qtf3gjg1.dll' is denied. at System.IO.__Error.WinIOError at System.IO.File.InternalDelete at System.IO.File.Delete at System.Web.Compilation.DiskBuildResultCache.CheckAndRemoveDotDeleteFile
Thursday, June 18, 2020 3:23 PM -
User475983607 posted
There's no connection between the command the the connection object.
Imports System.Data.SqlClient Public Class Database Private Connectionstring As String Public Sub New() Connectionstring = ConfigurationManager.ConnectionStrings("DefaultConnection").ToString() End Sub Public Sub New(ConnectionString As String) Me.Connectionstring = ConnectionString End Sub Public Async Function FillDatasetByCommandAsync(ByVal cmd As SqlCommand) As Threading.Tasks.Task(Of DataSet) Return Await FillDatasetByCommandAsync(cmd, "Table1") End Function Public Async Function FillDatasetByCommandAsync(ByVal cmd As SqlCommand, ByVal TableName As String) As Threading.Tasks.Task(Of DataSet) Dim data As New DataSet Dim table As New DataTable(TableName) Using conn As New SqlConnection(Connectionstring) cmd.Connection = conn Await conn.OpenAsync() Using reader As SqlDataReader = Await cmd.ExecuteReaderAsync() table.Load(reader) End Using conn.Close() data.Tables.Add(table) End Using Return data End Function End Class
Imports System.Data.SqlClient Public Class DefaultAsync Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load RegisterAsyncTask(New PageAsyncTask(Function() PopulateGridView())) End Sub Private Async Function PopulateGridView() As Threading.Tasks.Task Dim db As New Database() Dim sqlCmd As New SqlCommand Dim queryString As String = "SELECT s.IncomeSourceId, s.[Name] AS SourceName, s.[Address], t.IncomeTypeId, t.[Name] AS TypeName FROM dbo.IncomeSource AS s INNER JOIN dbo.IncomeType AS t ON s.IncomeTypeId = t.IncomeTypeId" With sqlCmd .CommandType = CommandType.Text .CommandText = queryString End With GridView1.DataSource = Await db.FillDatasetByCommandAsync(sqlCmd) GridView1.DataBind() End Function End Class
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, June 18, 2020 4:29 PM -
User1738843376 posted
hi Mgebhard,
Once again, thx for your input.
I've tried adapting your code to my situation (i'm calling a webmethod from the HTML via JQuery AJAX, that in turn calls the LoadProducts, so i had to adapt from your example)
Imports System.Data Imports System.Data.SqlClient Imports System.Threading Imports System.Threading.Tasks Imports Microsoft.VisualBasic Public Class Database Private _errorDetail As String = "" Private dbConnString As String Public ReadOnly Property ErrorDetail() As String Get Return _errorDetail End Get End Property Public Sub New() Me.dbConnString = System.Configuration.ConfigurationManager.ConnectionStrings("SQLConnStr").ToString() End Sub Public Sub New(ByVal conn As String) Me.dbConnString = conn End Sub Public Async Function FillDatasetByCommandAsync(ByVal cmd As SqlCommand) As Threading.Tasks.Task(Of DataSet) Return Await FillDatasetByCommandAsync(cmd, "Table1") End Function Public Async Function FillDatasetByCommandAsync(ByVal cmd As SqlCommand, ByVal TableName As String) As Threading.Tasks.Task(Of DataSet) Dim data As New DataSet Dim table As New DataTable(TableName) Using conn As New SqlConnection(dbConnString) cmd.Connection = conn Await conn.OpenAsync() Using reader As SqlDataReader = Await cmd.ExecuteReaderAsync() table.Load(reader) End Using conn.Close() data.Tables.Add(table) End Using Return data End Function End Class
Imports System.Data Imports System.Data.SqlClient Imports System.Threading.Tasks Imports System.Web.Routing <Serializable> Public Class HomeProduct Public Property ProductId() As String Public Property Title() As String Public Property ImageUrl() As String Public Property LinkUrl() As String Public Property DiscountTag() As String Public Property PriceTag() As String Public Property InStock() As String Public Property Buyable() As Boolean End Class Partial Class _Default Inherits System.Web.UI.Page <System.Web.Services.WebMethod()> Public Shared Async Function LoadProducts(ByVal superCategoryId As Integer) As Task(Of String) Logger.LogDebug("STARTED WEBMETHOD") Dim propertiesList As New Dictionary(Of String, String) Dim sqlClass As New Database Dim sqlCmd As New SqlCommand Dim dataSet As New DataSet Dim sqlString As String = "" Dim productsList As New List(Of HomeProduct) With sqlCmd .CommandType = CommandType.StoredProcedure .CommandText = "wec_sp_website_get_most_recently_bought" .Parameters.Add("@SupercategoryId", SqlDbType.Int).Value = superCategoryId End With dataSet = Await sqlClass.FillDatasetByCommandAsync(sqlCmd) If sqlClass.ErrorDetail = "" Then If Not IsNothing(dataSet) Then If dataSet.Tables.Count > 0 Then If dataSet.Tables(0).Rows.Count > 0 Then For Each row As DataRow In dataSet.Tables(0).Rows() Dim currentProduct As New HomeProduct With currentProduct Dim urlTitle As String = "" Dim objRegEx As New Regex("[^0-9a-zA-Z\-]+?") Dim fullTitle As String = row("title") & "" Dim mainDescription As String = row("MainDescription") & "" Dim variantDescription As String = row("VariantDescription") & "" End With productsList.Add(currentProduct) Next propertiesList.Add("productsList", Newtonsoft.Json.JsonConvert.SerializeObject(productsList, Newtonsoft.Json.Formatting.None)) propertiesList.Add("success", True) propertiesList.Add("error", False) propertiesList.Add("errorDetail", "") Else propertiesList.Add("success", False) propertiesList.Add("error", True) propertiesList.Add("errorDetail", "No Products located") End If Else propertiesList.Add("success", False) propertiesList.Add("error", True) propertiesList.Add("errorDetail", "No Products located") End If Else propertiesList.Add("success", False) propertiesList.Add("error", True) propertiesList.Add("errorDetail", "No Products located") End If Else propertiesList.Add("success", False) propertiesList.Add("error", True) propertiesList.Add("errorDetail", sqlClass.ErrorDetail) End If Return Newtonsoft.Json.JsonConvert.SerializeObject(propertiesList) End Function End Class
<head runat="server"> <title>Test</title> <script src="scripts/jquery-3.4.1.js"></script> <script type="text/javascript"> function loadProducts(catId) { var data = {}; var options = {}; data.superCategoryId = catId; options.url = "default.aspx/LoadProducts"; options.type = "POST"; options.data = JSON.stringify(data); options.contentType = 'application/json; charset=utf-8'; options.processData = false; return $.ajax(options).done(function (response) { var data = response.d; data = $.parseJSON(data); if (data.success == "True") { console.log("Loaded data " + cat); console.log("data: " + data); } }); } $( document ).ready(function() { loadProducts(1); //loadProducts(2); //loadProducts(3); //loadProducts(4); }); </script> </head> <body> <form id="form1" runat="server"> <div> TEST <%=Date.UtcNow.ToString %> </div> </form> </body>
I can track the code running until it starts FillDatasetByCommandAsync, but from there on, the system hangs
What am i doing wrong?
P.S. I've also tested adding "Asynchronous Processing=True;" to the connection string, but with the same result.
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, June 18, 2020 6:21 PM -
User1738843376 posted
I was finally able to make it work. It was missing .ConfigureAwait(False) on all await commands. Ex:
Using reader As SqlDataReader = Await cmd.ExecuteReaderAsync().ConfigureAwait(False)
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Friday, June 19, 2020 4:24 PM