0

I am migrating c# application to support Npgsql/postgres. The back end of the application uses SQL Server. Now we need to support Postgres. I am using third party library which is Npgsql (4.0.10). I had converted most of the items in the code but I am stuck with migrating the following code to use Npgsql library.

            List<int> cidList = <filled list>;

            List<SqlDataRecord> contList = new List<SqlDataRecord>();
            SqlMetaData[] tvpDef = { new SqlMetaData("n", SqlDbType.Int) };

            foreach (int i in cidList)
            {
                SqlDataRecord rec = new SqlDataRecord(tvpDef);
                rec.SetInt32(0, i);
                contList.Add(rec);
            }

            ...

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@CIDList", SqlDbType.Structured);
            cmd.Parameters["@CIDList"].Direction = ParameterDirection.Input;
            cmd.Parameters["@CIDList"].TypeName = "IntKeyTable";
            cmd.Parameters["@CIDList"].Value = contList;

I am not finding the equivalent of SqlDataRecord and SqlMetaData in Npgsql. Does npgsql has support to these SqlDataRecord and SqlMetaData class?. Any help is really appreciated.

User12111111
  • 1,179
  • 1
  • 19
  • 41
  • You had asked wrong question as *Can someone help with this* is not a real question ... more seriouosly: you had asked wrong question ... instead asking to "translate this code" you should ask how to pass table parameters with npgsql ... which was already asked on StackOverflow (no - i will not search for the links for you) – Selvin Dec 19 '19 at 13:36
  • 1
    There's not an exact match for a SqlServer Table Valued Parameter (TVP) but [NpgsqlDbType.Composite](https://stackoverflow.com/q/47466801/314291) is pretty close, and you can pass these as an array to a PG function. – StuartLC Dec 19 '19 at 13:45
  • If you're looking to import lots of rows into a database, you should probably take a look at the [Npgsql COPY API](http://www.npgsql.org/doc/copy.html), somewhat analogous to SqlBulkCopy. Otherwise post a bit more context on what you're trying to achieve. – Shay Rojansky Dec 27 '19 at 14:28
  • I am trying to call a function which accepts custom datatype 'IntKeyTable' with List. – User12111111 Jan 02 '20 at 10:58
  • @StuartLC, I am using Npgsql 4.0.10 version, I don't see NpgsqlDBType.Composite. can you please check – User12111111 Jan 06 '20 at 09:39

0 Answers0