Pass datatable as parameter in PostgreSQL

Question

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

Answer 1

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.

• How to convert datatable to json string using json.net?

• Convert datatable to JSON in C#

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.

Answer 2

I don't believe that it is possible to do that in PostgreSQL the same way that you can with SQL Server. That being said, when you look at what is going on under the hood it isn't actually that difficult to simulate. A common misconception is that the data table is passed "whole" to the stored procedure. That is not true, the data table is reconstructed on the SQL Server side using the data within the data table. Here is what actually happens when you perform this action in SQL Server:

• A variable of the specified table-type is created in memory.
• An insert is made into the variable for each record in the data table.
• A reference to the variable is passed to the stored procedure.

It looks a little like this:

DECLARE @p1 [dbo].[table_type_name];
INSERT INTO @p1 VALUES(...);
INSERT INTO @p1 VALUES(...);
-- continue until all rows have been inserted
EXEC [dbo].[sp_something] @dataTable = @p1;

You see, nothing special. It would be easy to simulate this using temporary tables or staging tables and generating the SQL yourself. The only major difference is that you cannot pass a reference to the table to the stored procedure.

As Evan mentioned, there are other ways of passing data to PostgreSQL but if you are looking for a cross platform solution then you will need to ensure that you only choose ANSI-compliant methods - unfortunately this does not include the SQL Server table-valued parameters.

This is how I would implement a solution that require some kind of staging table:

• Implement a sequence that can be used to return a session id
• Create a staging table which contains the required columns plus a column for the session id
• Create a stored procedure that takes data out of the staging table which matches the given session id and merges it with the target table.

Example:

SQL

CREATE SEQUENCE seq_get_session_id START 1;

CREATE TABLE stg_table (
   session_id bigint not null,
   -- remainder of columns
);

CREATE OR REPLACE FUNCTION insert_data(_session_id BIGINT)
RETURNS void AS
$BODY$
BEGIN
    INSERT INTO my_table (...)
    SELECT /* columns */
      FROM stg_table
     WHERE session_id = _session_id;

    DELETE FROM stg_table
    WHERE session_id = _session_id;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;

C#

var phonebooks = GetPhoneBooks();

using (var connection = GetConnection())
{
    long sessionId;
    connection.Open();
    var transaction = connection.BeginTransaction();

    // get the session id
    using(var command = connection.CreateCommand())
    {
        command.Transaction = transaction;
        command.CommandText = "SELECT nextval('seq_get_session_id')";
        using (var reader = command.ExecuteReader())
        {
            if (!reader.Read())
                throw new InvalidOperationException("Unable to obtain session id");
            sessionId = reader.GetInt64(0);
        }       
    }

    // insert data into staging table
    using (var insertCommand = connection.CreateCommand())
    {
        insertCommand.CommandText = 
            "INSERT INTO stg_table (session_id, ...)" + 
            " VALUES (_session_id, ...)";
        insertCommand.Transaction = transaction;
        insertCommand.Parameters.AddWithValue("_session_id", sessionId);

        var p1 = insertCommand.CreateParameter();
        p1.ParameterName = "_p1";
        p1.DbType = DbType.DateTime;
        p1.Value = DateTime.Now;
        insertCommand.Parameters.Add(p1);

        // repeat above for each property that needs to be persisted...

        foreach(var phonebook in phonebooks)
        {
            p1.Value = phonebook.Date;
            // set values for any other parameters you have declared...

            insertCommand.ExecuteNonQuery();
        }    
    }

    // execute final stored procedure to merge the data
    using (var insertDataCommand = connection.CreateCommand())
    {
        insertDataCommand.CommandText = "insert_data";
        insertDataCommand.CommandType = CommandType.StoredProcedure;
        insertDataCommand.Parameters.AddWithValue("_session_id", sessionId);

        insertDataCommand.Transaction = transaction;
        insertDataCommand.ExecuteNonQuery();
    }  

    transaction.Commit();
}

However, the above is major overkill if you do not need a staging table, if you are able to insert the data straight into the target table you can omit most of the above code and just do this:

using (var connection = GetConnection())
{
    connection.Open();
    var transaction = connection.BeginTransaction();

    using (var insertCommand = connection.CreateCommand())
    {
        insertCommand.CommandText = 
            "INSERT INTO my_table (...)" + 
            " VALUES (...)";
        insertCommand.Transaction = transaction;

        var p1 = insertCommand.CreateParameter();
        p1.ParameterName = "_p1";
        p1.DbType = DbType.DateTime;
        p1.Value = DateTime.Now;
        insertCommand.Parameters.Add(p1);

        // repeat above for each property that needs to be persisted...

        foreach(var phonebook in phonebooks)
        {
            p1.Value = phonebook.Date;
            // set values for any other parameters you have declared...

            insertCommand.ExecuteNonQuery();
        }         
    }

    transaction.Commit();
}

Answer 3

HOW TO PASS

DATA-TABLE

AS PARAMETER IN POSTGRESQL

=================================================

STEP-1

CREATE TYPE TABLE WHICH YOU WANT TO PASS AS DATATABLE IN POSTGRE

Example…

I create student type in postgresql like

Create Type Student As ( id integer, name text );

--==Create physical table for testing purpose. Create Table Teacher ( id integer, name text );

Step :-2

CREATE OR REPLACE FUNCTION public.usp_InsertStudent( ref1 refcursor, p_str_string json ) RETURNS refcursor AS $BODY$ BEGIN

Insert into  Teacher (id,name)
select id,name from json_populate_recordset(null::Student,p_str_string);

OPEN REF1 FOR
select * from  Teacher;

return  ref1;

END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100;

Step :-3

Call from C#

DataSet ds = new DataSet(); DataTable table = new DataTable(); table.Columns.Add("id", typeof(int)); table.Columns.Add("name", typeof(string)); table.Rows.Add(25, "ajay"); table.Rows.Add(25, "rajesh"); table.Rows.Add(25, "jayesh");

        string json = JsonConvert.SerializeObject(table, Formatting.Indented);
        string conStr = "put connection string here..";
        NpgsqlConnection conn = new NpgsqlConnection(conStr);
        conn.Open();
        string query = string.Empty;
        query = "select * from usp_InsertStudent('ref1'";
        query = query + ",p_str_string:='"+json+"'"+
         ");fetch all in " + "\"ref1\";";

         NpgsqlDataAdapter da = new NpgsqlDataAdapter(query, conn);
         da.Fill(ds);
         System.Data.DataTable dt = ds.Tables[1];
         //// connect grid to DataTable
         dataGridView1.DataSource = dt;
         dataGridView1.DataBind();