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 Class

And 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 42

Any idea on how to solve this?

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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)