Click here to Skip to main content
Click here to Skip to main content

Tagged as

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

, 15 Feb 2011 CPOL
Rate this:
Please Sign up or sign in to 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;
}

License

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

Share

About the Author

Amund Gjersøe
Engineer
Norway Norway
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web03 | 2.8.141015.1 | Last Updated 15 Feb 2011
Article Copyright 2011 by Amund Gjersøe
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid