Hot File

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

View: 2035    Dowload: 0   Comment: 0   Post by: naruto  
Author: none   Category: PostgreSQL   Fields: Computers - Technology

0 point/5 review File has been tested

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.

Introduction

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

Multiple parameter "FillBy" method in table adapter using ODBC and PostgreSQL Posted on 21-03-2014  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. 5/10 2035

Comment:

To comment you must be logged in members.

Files with category

  • Accessing: MySQL Database using MySQL C API

    View: 2332    Download: 2   Comment: 0   Author: none  

    Accessing: MySQL Database using MySQL C API

    Category: PostgreSQL
    Fields: Other

    0.75/6 review
    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

    View: 2418    Download: 0   Comment: 0   Author: none  

    Full Database Abstraction Layer Generator

    Category: PostgreSQL
    Fields: Other

    0/23 review
    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

    View: 1956    Download: 0   Comment: 0   Author: none  

    Code .NET 2.0; Build Java; Run Linux

    Category: PostgreSQL
    Fields: Other

    1.5/3 review
    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#

    View: 2818    Download: 2   Comment: 0   Author: none  

    Postgres Database Backup/Restore From C#

    Category: PostgreSQL
    Fields: Other

    0/9 review
    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

    View: 5265    Download: 0   Comment: 0   Author: none  

    Installing Redmine on Windows in production

    Category: PostgreSQL
    Fields: Other

    0/9 review
    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,...

    View: 3076    Download: 0   Comment: 0   Author: none  

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

    Category: PostgreSQL
    Fields: Other

    0.5625/8 review
    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

    View: 4164    Download: 5   Comment: 0   Author: none  

    Creating an Interactive Map in ASP.NET 2.0 Using SharpMap

    Category: PostgreSQL
    Fields: Other

    0/2 review
    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

    View: 867    Download: 0   Comment: 0   Author: none  

    DevShot - Database Snapshots for Developers

    Category: PostgreSQL
    Fields: Other

    0/2 review
    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...

 
Newsletter Email

File suggestion for you

File top downloads

logo codetitle
Codetitle.com - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.com Develope by Vinagon .Ltd