Loading...

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

View: 2957    Dowload: 0   Comment: 0   Post by: naruto   Category: PostgreSQL   Fields: Computers - Technology

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)

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

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.

Posted on 21-03-2014 

Comment:

To comment you must be logged in members.

Files with category

  • Accessing: MySQL Database using MySQL C API

    Accessing: MySQL Database using MySQL C API

    View: 2677    Download: 2   Comment: 0

    Category: PostgreSQL     Fields: Other

    This article was written for beginners, developers that do not know how to access MySql database using MySQL C API and would like to have a small introduction.

  • Full Database Abstraction Layer Generator

    Full Database Abstraction Layer Generator

    View: 2806    Download: 0   Comment: 0

    Category: PostgreSQL     Fields: Other

    This article presents yet another Data Abstraction Layer generator (popular topic). The DAL structure is directly inspired from the DotNetNuke, DAL document and the SQL generation is done using SQLpp. The generator takes care of pretty much...

  • Code .NET 2.0; Build Java; Run Linux

    Code .NET 2.0; Build Java; Run Linux

    View: 2286    Download: 0   Comment: 0

    Category: PostgreSQL     Fields: Other

    This article is in the Product Showcase section for our sponsors at CodeProject. These reviews are intended to provide you with information on products and services that we consider useful and of value to developers. This is a showcase review for...

  • Postgres Database Backup/Restore From C#

    Postgres Database Backup/Restore From C#

    View: 3327    Download: 3   Comment: 0

    Category: PostgreSQL     Fields: Other

    This article provides Postgres database backup/restore functionality. By using this application we can check whether a Postgres database is installed or not in a system. If a Postgres database is installed then the Postgres databases list is...

  • Installing Redmine on Windows in production

    Installing Redmine on Windows in production

    View: 7093    Download: 0   Comment: 0

    Category: PostgreSQL     Fields: Other

    This is a very simple step by step guide to install Redmine on Windows server in production. This guide is applicable to IIS 7+ based servers, which include Windows Server 2008, 2008 R2, Windows Vista and Windows 7. Latter two should not be used for...

  • A SQL Management Console for MSSQL 2000 & 2005, MySQL 5.0,...

    A SQL Management Console for MSSQL 2000 & 2005, MySQL 5.0,...

    View: 3612    Download: 2   Comment: 0

    Category: PostgreSQL     Fields: Other

    Since MSSQL is not holding the lion share of the market yet, integration/interaction with other RDBMS like PL/Oracle, MySQL, FireBird, DB2, is inevitable. With the class System.Data.Common in ADO .NET 2.0, different data providers can write the...

  • Creating an Interactive Map in ASP.NET 2.0 Using SharpMap

    Creating an Interactive Map in ASP.NET 2.0 Using SharpMap

    View: 4749    Download: 5   Comment: 0

    Category: PostgreSQL     Fields: Other

    Showing maps on the web is becoming more and more common. The most well-known ones are Google Maps and Microsoft's Virtual Earth. Creating your own websites with interactive maps require you to get hold on the map-data and convert this data to...

  • DevShot - Database Snapshots for Developers

    DevShot - Database Snapshots for Developers

    View: 1161    Download: 0   Comment: 0

    Category: PostgreSQL     Fields: Other

    A few weeks back, I got an idea of writing a simple tool for switching database states between coding cycles. I thought this would be a good tool for anyone who is working on database related projects and have to switch between database states since...

 
File suggestion for you
Loading...
File top downloads
Loading...
Loading...
Codetitle - library source code to share, download the file to the community
Copyright © 2018. All rights reserved. codetitle Develope by Vinagon .Ltd