Skip to content

Instantly share code, notes, and snippets.

@cpereira7

cpereira7/SQLPostgres.cs

Last active January 24, 2025 07:50
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save cpereira7/eac42b405d5666e313b3278b3ccafb00 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save cpereira7/eac42b405d5666e313b3278b3ccafb00 to your computer and use it in GitHub Desktop.
Download ZIP
Select data from Postgres Database using Npgsql
Raw
SQLPostgres.cs
/*
* Created by SharpDevelop.
* User: cpereira7
* Date: 08-11-2015
* Time: 20:06
* C#, Npgsql
*/
using System;
using System.Data;
using System.Diagnostics;
using Npgsql;
using NpgsqlTypes;
using System.Linq;
namespace Biblioteca_2
{
/// <summary>
/// Select data from Postgres Database using Npgsql
/// </summary>
public static class SQLPostgres
{
static readonly NpgsqlConnection connection = new NpgsqlConnection("Server=127.0.0.1;Port=5432;CommandTimeout=5000;User Id=postgres;" +
"Password=pwd;Database=dbName;");
/// <summary>
/// Runs a query on the database. Warning: Don't use for user direct inputs!
/// </summary>
/// <param name="query">Query to execute.</param>
public static void RunQuery(string query)
{
connection.Open();
// Define a query
NpgsqlCommand cmd = new NpgsqlCommand(query, connection);
// Execute a query
NpgsqlDataReader dr = cmd.ExecuteReader();
connection.Close();
}
/// <summary>
/// Get data from a simple query. No params needed.
/// </summary>
/// <param name="query">Query to execute. Example: select * from sales</param>
/// <returns></returns>
public static DataTable SelectData(string query)
{
connection.Open();
using (var cmd = new NpgsqlCommand(query, connection))
{
cmd.Prepare();
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet _ds = new DataSet();
DataTable _dt = new DataTable();
da.Fill(_ds);
try
{
_dt = _ds.Tables[0];
}
catch (Exception ex)
{
Debug.WriteLine("Erro: ---> " + ex.Message);
}
connection.Close();
return _dt;
}
}
/// <summary>
/// Get data a DataTable from a query with params.
/// </summary>
/// <param name="query">Query to execute. Example: select * from sales where product = @prodId</param>
/// <param name="paramName">Param name. Example: "prodId"</param>
/// <param name="paramValue">Param value. Example: (int)15</param>
/// <returns></returns>
public static DataTable SelectData(string query, string paramName, object paramValue)
{
connection.Open();
using (var cmd = new NpgsqlCommand(query, connection))
{
cmd.Parameters.AddWithValue(paramName, paramValue);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet _ds = new DataSet();
DataTable _dt = new DataTable();
da.Fill(_ds);
try
{
_dt = _ds.Tables[0];
}
catch (Exception ex)
{
Debug.WriteLine("Error: ---> " + ex.Message);
}
connection.Close();
return _dt;
}
}
/// <summary>
/// Get a DataTable from a query with params.
/// </summary>
/// <param name="query">Query to execute. Example: select * from sales where product = @prodId</param>
/// <param name="paramName">Param name. Example: "prodId"</param>
/// <param name="paramType">Param type. Needed to enable prepare query.</param>
/// <param name="paramValue">Param value. Example: (int)15</param>
/// <returns></returns>
public static DataTable SelectData(string query, string paramName, NpgsqlDbType paramType, object paramValue)
{
connection.Open();
using (var cmd = new NpgsqlCommand(query, connection))
{
cmd.Parameters.AddWithValue(paramName, paramType ,paramValue);
//PREPARE creates a prepared statement.
//A prepared statement is a server-side object that can be used to optimize performance.
cmd.Prepare();
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet _ds = new DataSet();
DataTable _dt = new DataTable();
da.Fill(_ds);
try
{
_dt = _ds.Tables[0];
}
catch (Exception ex)
{
Debug.WriteLine("Error: ---> " + ex.Message);
}
connection.Close();
return _dt;
}
}
/// <summary>
/// Get data a DataTable from a query with multiple params.
/// </summary>
/// <param name="query">Query to execute. Example: select * from sales where product = @prodId and sale_date = @date</param>
/// <param name="paramName">Param name. Example: []{"prodId". "qtd"}</param>
/// <param name="paramValue">Param value. Example: []{(int)15,(DateTime)"2017-01-01"}</param>
/// <returns></returns>
public static DataTable SelectData(string query, string[] paramName, object[] paramValue)
{
connection.Open();
using (var cmd = new NpgsqlCommand(query, connection))
{
//Verify if the name's count equals the value's count
if (paramName.Count() != paramValue.Count())
{
Debug.WriteLine("ParamName Count != ParamValue Count");
return null;
}
//Add params in the arrays
for (int i = 0; i < paramName.Count() ; i++) {
cmd.Parameters.AddWithValue(paramName[i], paramValue[i]);
}
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet _ds = new DataSet();
DataTable _dt = new DataTable();
da.Fill(_ds);
try
{
_dt = _ds.Tables[0];
}
catch (Exception ex)
{
Debug.WriteLine("Error: ---> " + ex.Message);
}
connection.Close();
return _dt;
}
}
/// <summary>
/// Get data a DataTable from a query with multiple params.
/// </summary>
/// <param name="query">Query to execute. Example: select * from sales where product = @prodId and sale_date = @date</param>
/// <param name="paramName">Param name. Example: []{"prodId". "qtd"}</param>
/// <param name = "paramType">Param type. Example: []{NpgsqlDbType.Integrer, NpgsqlDbType.Date}</param>
/// <param name="paramValue">Param value. Example: []{(int)15,(DateTime)"2017-01-01"}</param>
/// <returns></returns>
public static DataTable SelectData(string query, string[] paramName, NpgsqlDbType[] paramType, object[] paramValue)
{
connection.Open();
using (var cmd = new NpgsqlCommand(query, connection))
{
if (paramName.Count() != paramValue.Count() || paramValue.Count() != paramType.Count())
{
Debug.WriteLine("ParamName Count != ParamValue Count");
return null;
}
for (int i = 0; i < paramName.Count(); i++) {
cmd.Parameters.AddWithValue(paramName[i], paramType[i], paramValue[i]);
}
cmd.Prepare();
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet _ds = new DataSet();
DataTable _dt = new DataTable();
da.Fill(_ds);
try
{
_dt = _ds.Tables[0];
}
catch (Exception ex)
{
Debug.WriteLine("Error: ---> " + ex.Message);
}
connection.Close();
return _dt;
}
}
}
}
@JuiZe89
Copy link

JuiZe89 commented Apr 28, 2023 •
edited
Loading

Thank you very much !

Good and clean work <3

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment