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.)
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;
}
}
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!
User contributions licensed under CC BY-SA 3.0