Postgresql – Pass datatable as parameter in PostgreSQL

cpostgresql

I have one datatable in asp.net C#. And I want to pass that datatable into PostgreSQL function as a table parameter. How is it possible?

Below example is same but it is in SQL Server. I need same thing but issue is that I have PostgreSQL as back-end, not SQL Server.

Introduction

SQL Server Stored Procedures support System.Data.DataTable as a
parameter. We can pass the DataTable to the Stored Procedure using
ADO.Net in the same way as we provided using the
System.Data.SqlParameter class, but needs a few changes in the
datatype. Normally we provide DbType of SqlParameter for a normal
parameter like varchar, nvarchar, int and so on as in the following
code.

SqlParameter sqlParam= new SqlParameter();  
sqlParam.ParameterName = "@StudentName";  
sqlParam.DbType = DbType.String;  
sqlParam.Value = StudentName;

But in the case of a Table parameter, we do not need to provide a
DbType as the parameter data type. We need to provide SqlType rather
then DbType.

Example

SqlParameter Parameter = new SqlParameter;  
Parameter.ParameterName = "@PhoneBook";  
Parameter.SqlDbType = SqlDbType.Structured;  
Parameter.Value = PhoneTable;

The following example receives a list of phone books and stores them
in a database using ADO.Net. The example retrieves the phone book
details from the list and stores them into the DataTable and passes
this table to the Stored Procedure named NewPhoneBook as a parameter. 

//Phone book list    
List<PhoneBook> PhoneBooks    
 //CReating Table    
 DataTable PhoneTable = new DataTable();    

 // Adding Columns    
 DataColumn COLUMN=new DataColumn();    
 COLUMN.ColumnName="ID";    
 COLUMN.DataType= typeof(int);    
 PhoneTable.Columns.Add(COLUMN);    

 COLUMN = new DataColumn();    
 COLUMN.ColumnName = "ContactNumber";    
 COLUMN.DataType = typeof(string);    
 PhoneTable.Columns.Add(COLUMN);    

 COLUMN = new DataColumn();    
 COLUMN.ColumnName = "ContactName";    
 COLUMN.DataType = typeof(string);    
 PhoneTable.Columns.Add(COLUMN);    

 // INSERTING DATA    
 foreach (UserPhoneBook UPB in PhoneBooks)    
 {    
    DataRow DR = PhoneTable.NewRow();    
    DR[0] = UPB.UserName;    
    DR[1] = UPB.ContactNumber;    
    DR[2] = UPB.ContactName;    
    PhoneTable.Rows.Add(DR);    
 }    
 //Parameter declaration    
 SqlParameter[] Parameter = new SqlParameter[2];    
 Parameter[0].ParameterName = "@PhoneBook";    
 Parameter[0].SqlDbType = SqlDbType.Structured;    
 Parameter[0].Value = PhoneTable;    

 Parameter[1].ParameterName = "@Return_Value";    
 Parameter[1].Direction = ParameterDirection.ReturnValue;    
 //Executing Procedure  
 SqlHelper.ExecuteNonQuery(this.ConnectionString, CommandType.StoredProcedure, "[NewPhoneBook]", Parameter);

You can find code reference here

Best Answer

I would never use these objects. They simply don't seem that useful. That said, PostgreSQL does have a method of passing a table to a function via jsonb. Likely that would be the ideal solution for you if you're looking to get the functionality described in Passing a Table-Valued Parameter to a Stored Procedure. All you'd have to do is map the DataTable to JSON, and then pass the JSON to the function.

To convert a Datatable to JSON you can use JSON.net. See also these Stack Overflow answers.

If the Datatable is too big to serialize to JSON, you may have to create a Foreign Data Wrapper around something that exports the DataRow objects, this would start you down the path mentioned in "Streaming Rows with a DataReader". Though if you don't need a server/client model, you can always dump the DataTable to CSV and read it with a Foreign Data Wrapper: to go down that route check out file-fdw.

I actually tried this. I ran into a ton of problems.

  1. It wasn't even easy figuring out how to install a package with nuGet,
  2. Having installed nuGet, despite being the #1 package on nuGet, it doesn't yet work with .NET Core 2.x
  3. Installing the beta version of nuGet, I got it to work.

Converting a DataTable to JSON with .NET Core 2.x,

using System;
using System.Data;
using Newtonsoft.Json;

class Program
{
  static void Main()
  {
    // Get the DataTable.
    DataTable table = GetTable();
    // ... Use the DataTable here with SQL.
    string json = JsonConvert.SerializeObject(table, Formatting.Indented);
    Console.WriteLine(json);

  }

  static DataTable GetTable()
  {
    // Here we create a DataTable with four columns.
    DataTable table = new DataTable();
    table.Columns.Add("Dosage", typeof(int));
    table.Columns.Add("Drug", typeof(string));
    table.Columns.Add("Patient", typeof(string));
    table.Columns.Add("Date", typeof(DateTime));

    // Here we add five DataRows.
    table.Rows.Add(25, "Indocin", "David", DateTime.Now);
    table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
    table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
    table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
    table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);

    return table;
  }
}

You'd still have to connect to the db and drop the json into an INSERT statement, but that should be easily done.

cmd.CommandText = "SELECT * FROM myfunc(@json)";
cmd.Parameters.AddWithValue("json", json);

Define myfunc(jsonb) on the server, and you should be good: you've passed a DataTable into PostgreSQL.

Related Question