Click here to Skip to main content
15,881,139 members
Articles / Programming Languages / SQL
Tip/Trick

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 17.1K   4  
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 '?'.

C#
/// <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;
}

License

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


Written By
Engineer
Norway Norway
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
-- There are no messages in this forum --