Pass 2D array from C# to Postgresql function

Question

Here is postgresql running query with pg admin iii. (Below query working fine with pg admin and it is returning result set without any issue)

select * from  pg_sp_getmainrates_11(9,10,array[[5,10,10,10],[30,20,15,16]]);

Here is parameter declaration of pg function.

CREATE OR REPLACE FUNCTION pg_sp_getmainrates_11(
    IN fromcountryid integer,
    IN tocountryid integer,    
    IN alldimensions_we_le_he_wi double precision[]
   )

-- My logic is going here

But when passing array using C# code, it is returning error when executing the query.

Exeption attributes

Basemessage : syntax error at or near ","

ErrorSql : SELECT * FROM pg_sp_getmainrates_11(9,10,System.Double[,])

Here is my C# code.

   double[,] codes = new double[,]
    {
        { 5,10,10,10},{ 30,20,15,16}
    };

  string quy = "pg_sp_getmainrates_11(" + FromCountryId +
                               "," + ToCountryId +
                               "," + codes + ")";

        NpgsqlCommand command = new NpgsqlCommand(quy, conn);
        command.CommandType = CommandType.StoredProcedure;
        NpgsqlDataReader dr = command.ExecuteReader();

I need a small direction to pass an array (like above) to my postgresql function.

your c# array is: {{},{}} your postgresql array is: [[],[]] . No one will convert it for you if you won't tell the compiler to do such conversion. More over postgresql does not treat the same for array and int[] or numeric[]. you may call them the same but they are not the same. So your definition for array in c# as double[,] needs to be casted for the proper type in postgresql. — java
– java, Commented Mar 13, 2016 at 12:33

weeraa · Accepted Answer · 2016-03-14 08:01:17Z

1

Up to now I found a solution for this. (Don't know whether this is the optimum solution) The simple thing to send parameter as string. With my C# code I make some string like below.

String arr = "array[[5,10,10,10],[30,20,15,16]]";

This arr will be passed as query parameter.

string quy = "pg_sp_getmainrates_11(" + FromCountryId +
                               "," + ToCountryId +
                               "," + arr+ ")";

Above solution works fine.

answered Mar 14, 2016 at 8:01

weeraa

1,20510 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sawa · Accepted Answer · 2016-07-20 08:38:34Z

0

You can use using Newtonsoft.Json; and insert your array like the following:

string[][] arr = {{5, 10, 10, 10},{30, 20, 15, 16}};
var arrayOutput = JsonConvert.SerializeObject(arr);

try
{
    string sql1 = "INSERT INTO tbt(img, fcth, dev) VALUES (ARRAY'" + arrayOutput + "')";
    dbcmd.CommandText = sql1;
    cmd.ExecuteNonQuery();
}

catch (NpgsqlException ex)
{
    if (ex.Data == null)
    {
        throw;
    }

    else
    {
    }

}

This example Just for array column.

edited Jul 20, 2016 at 8:38

answered Jul 19, 2016 at 11:34

Sawa

399 bronze badges

Collectives™ on Stack Overflow

Pass 2D array from C# to Postgresql function

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related