Click here to Skip to main content
14,695,505 members
Articles » General Programming » Programming Tips » General
Posted 14 Feb 2011

Tagged as


4 bookmarked

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

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
15 Feb 2011CPOL
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)

    int nbrRowsAffected = this.Adapter.Fill(dataTable);

    return nbrRowsAffected;


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Amund Gjersøe
Norway Norway
No Biography provided

Comments and Discussions

-- There are no messages in this forum --