Multiple parameter "FillBy" method in table adapter using ODBC and PostgreSQL





0/5 (0 vote)
Visual Studio Query Builder does not work with PostgreSQL when you want to use filtering parameters. This tip show how to make your own "FillBy" methods.
I'm fetching data from a PostgreSQL 9.0 database, using the latest psqlODBC-driver. I add the database as a data source in Visual Studio, and auto-generate a
DataSet
of some tables. I get the 'Fill
' and 'Get
' methods by default, but I'd like to have methods with multiple filtering parameters. This is where the problem starts when not using a Microsoft SQL. Luckily, this turned out to be no problem when I first found out that in ODBC, the parameters are positional, and not named.
So the tip is: Write the new methods in a partial class of your auto-generated table adapter. Write your SQL with '?' where you want an input parameter. Then add the parameters to your ODBC-command according to the order of the inserted '?'.
/// <summary>
/// Fill a data table with data filtered by time
/// </summary>
/// <param name="dataTable">Data table to fill</param>
/// <param name="startTime">Start of time frame</param>
/// <param name="endTime">End of time frame</param>
/// <returns>Numbers of rows added to the data table</returns>
public int FillByTimeFrame(MyDataSet.MyDataTable dataTable,
DateTime startTime, DateTime endTime)
{
var cmd = new System.Data.Odbc.OdbcCommand();
cmd.Connection = Connection;
cmd.CommandText = "SELECT * FROM \"public\".\"my_view\"" +
"WHERE (\"timestamp\" > ?) AND (\"timestamp\" <= ?)";
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.AddWithValue("@startTime", startTime);
cmd.Parameters.AddWithValue("@endTime", endTime);
this.Adapter.SelectCommand = cmd;
if (this.ClearBeforeFill)
dataTable.Clear();
int nbrRowsAffected = this.Adapter.Fill(dataTable);
return nbrRowsAffected;
}