Running queries on a PostgreSQL DB asynchronously using Npgsql <C#>

0

I am trying to execute a series of queries to a PostgreSQL database. I was previously using OdbcConnection synchronously, but seeing that it is taking up so much time I searched online to come across Npgsql.

Even using NpgsqlConnection synchronously proved to be quite faster than its Odbc counterpart.

I tried to hack together an asynchronous version using Npgsql, but it doesn't work. (See details below the code.)


Odbc version (synchronous):

using System.Data.Odbc;

public partial class InputForm : Form
{
    private OdbcConnection con;

    private void btn_Get_Click(object sender, EventArgs e)
    {
    
        private OdbcConnection con;
        con = new OdbcConnection("Dsn=" + DSN_Name +
                                 ";id=" + ID +
                                 ";Pwd=" + PWD +
                                 ";DataBase=" + DB_Name + ";");
        try
        {
            con.Open();
            
            var arlst_TargetTables = new ArrayList();
                    //Prepare list...               
            DataTable dt_data = new DataTable();
            dt_data = GetMergedDataTable(arlst_TargetTables, arlst_AllTables);
            
            var arlst_TargetTables2 = new ArrayList();
                    //Prepare list...               
            DataTable dt_data2 = new DataTable();
            dt_data2 = GetMergedDataTable(arlst_TargetTables2, arlst_AllTables);
            //...more such tables are prepared
            //Then processing is done based on their data

        }
        catch (OdbcException ex)
        {
            Debug.WriteLine("Exception caught: {0}", ex);
        }
        finally
        {
            con.Close();
        }
    }

    private DataTable GetMergedDataTable(ArrayList lst_subset, ArrayList lst_main)
    {
        DataTable DataTable_final = new DataTable();
    
        foreach (var table_i in lst_subset)
        {
            if (lst_main.Contains(table_i))
                {
                DataTable DataTable_temp = new DataTable();
                    
                OdbcCommand oc = new OdbcCommand("SELECT * FROM " + table_i + "...", con);
                OdbcDataReader odr = oc.ExecuteReader();
                DataTable_temp.Load(odr);  //Get data as a DataTable
                DataTable_final.Merge(DataTable_temp);  //Merge multiple DataTables into one
                
                odr.Close();
                }
        }

        return DataTable_final;     
    }
}

Npgsql version (asynchronous):

using Npgsql;

public partial class InputForm : Form
{
    private NpgsqlConnection con;

    private async void btn_Get_ClickAsync(object sender, EventArgs e)
    {
    
        NpgsqlConnectionStringBuilder sb = new NpgsqlConnectionStringBuilder(true);
        sb.Host = "12.34.567.89";
        sb.Port = 5432;
        sb.Database = DB_Name;
        sb.Username = "postgres";
        sb.Password = "postgres";
        sb.Enlist = true;

        con = new NpgsqlConnection(sb.ConnectionString);
        try
        {
            await con.OpenAsync();
            
            var arlst_TargetTables = new ArrayList();
                    //Prepare list...               
            DataTable dt_data = new DataTable();            
            Task<DataTable> task = GetMergedDataTableAsync(arlst_TargetTables, arlst_AllTables);
            
            var arlst_TargetTables2 = new ArrayList();
                    //Prepare list...               
            DataTable dt_data2 = new DataTable();
            Task<DataTable> task2 = GetMergedDataTableAsync(arlst_TargetTables2, arlst_AllTables);
            //...more such tables are there
            
            var results = await Task.WhenAll(new List<Task<DataTable>> { task, task2, ... });
            dt_data = results[0];
            dt_data2 = results[1];
            //Save them all
            
            //Then processing is done based on their data

        }
        catch (NpgsqlException ex)
        {
            Debug.WriteLine("Exception caught: {0}", ex);
        }
        finally
        {
            await con.CloseAsync();
        }
    }

    private async Task<DataTable> GetMergedDataTableAsync(ArrayList lst_subset, ArrayList lst_main)
    {
        DataTable DataTable_final = new DataTable();
    
        foreach (var table_i in lst_subset)
        {
            if (lst_main.Contains(table_i))
            {
                DataTable DataTable_temp = new DataTable();
                    
                NpgsqlCommand oc = new NpgsqlCommand("SELECT * FROM " + table_i + "...", con);
                using (var odr = await oc.ExecuteReaderAsync())
                {
                    await odr.ReadAsync();
                    DataTable_temp.Load(odr);  //Get data as a DataTable
                    DataTable_final.Merge(DataTable_temp);  //Merge multiple DataTables into one
                    await odr.CloseAsync();
                }               
            }
        }

        return DataTable_final;     
    }
}

In the asynchronous version I face exceptions like below:

Exception thrown: 'Npgsql.NpgsqlOperationInProgressException' in mscorlib.dll Exception thrown: 'Npgsql.NpgsqlOperationInProgressException' in mscorlib.dll Exception thrown: 'Npgsql.NpgsqlOperationInProgressException' in mscorlib.dll Exception thrown: 'Npgsql.NpgsqlOperationInProgressException' in mscorlib.dll Exception thrown: 'Npgsql.NpgsqlOperationInProgressException' in mscorlib.dll Exception thrown: 'Npgsql.NpgsqlOperationInProgressException' in mscorlib.dll Exception thrown: 'Npgsql.NpgsqlOperationInProgressException' in mscorlib.dll Exception thrown: 'Npgsql.NpgsqlOperationInProgressException' in mscorlib.dll Exception thrown: 'Npgsql.NpgsqlOperationInProgressException' in mscorlib.dll Exception caught: Npgsql.NpgsqlOperationInProgressException (0x80004005): A command is already in progress: SELECT * FROM public.view_report_226 where sys_update_time>= (current_date - 90); 場所 Npgsql.NpgsqlConnector.g__DoStartUserAction|233_0(<>c__DisplayClass233_0& ) 場所 Npgsql.NpgsqlConnector.StartUserAction(ConnectorState newState, NpgsqlCommand command, CancellationToken cancellationToken, Boolean attemptPgCancellation) 場所 Npgsql.NpgsqlCommand.d__105.MoveNext() --- 直前に例外がスローされた場所からのスタック トレースの終わり --- 場所 System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() 場所 Npgsql.NpgsqlCommand.d__105.MoveNext() --- 直前に例外がスローされた場所からのスタック トレースの終わり --- 場所 System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) 場所 System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) 場所 System.Runtime.CompilerServices.TaskAwaiter`1.GetResult() 場所 iReporter_View.InputForm.d__4.MoveNext() 場所 C:\Users\P616515\Documents\iReporter_View\iReporter_View\InputForm.cs:行 895 --- 直前に例外がスローされた場所からのスタック トレースの終わり --- 場所 System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) 場所 System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) 場所 System.Runtime.CompilerServices.TaskAwaiter`1.GetResult() 場所 iReporter_View.InputForm.<btn_Get_ClickAsync>d__2.MoveNext() 場所 C:\Users\P616515\Documents\iReporter_View\iReporter_View\InputForm.cs:行 192

I am a complete noob in regards to asynchronous programming, and this is my first attempt at it; hence, I would appreciate your kind help in solving this problem.

Thanks!

c#
postgresql
asynchronous
async-await
npgsql
asked on Stack Overflow Dec 22, 2020 by Govinda • edited Dec 22, 2020 by Useme Alehosaini

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0