Pass an array of complex objects to a stored procedure with dapper

Question

I am assisting someone on a dapper related integration and there is currently a scenario where we have some data that we need to get into a stored procedure. So we can write the stored procedure however we want, and can write the dapper bit however we want, but the object would look something like:

public class SomeComplexObject
{
   public string Something {get; set;}
   public string SomethingElse {get;set;}
}

This would be within an array with anywhere from 1-N amounts of these objects. So we need to pass these to a stored proc for it to use as part of its internal query stuffs.

So if I were to do something like:

new SqlParameter("arrayOfGoodies", arrayOfComplexObjects);

However it gives us the error:

No mapping exists from object type

This makes sense as how would it know how to convert this array of Pocos into something for the SQL world, but somehow I need to get this data into the stored procedure. So is there a way to do this, by either telling dapper about this type, or possibly using one of those table value parameter things or something?

score 2 · Answer 1 · answered Jan 22 '19 at 05:25

I found way for PostgreSQL.

For explain,select list of users with the specified ID and Login.

in DB:

create table user_apt
(
    id bigint,
    login varchar(100),
    owner_name varchar(500),
    password varchar(50),
    is_active boolean,
    date_edit timestamp default now()
);

create type  t_user_test AS
(
  id bigint,
  login varchar(100),
  owner_name varchar(500)
)

CREATE OR REPLACE FUNCTION test_pass_object_array(i_users t_user_test)
  RETURNS SETOF t_user_test
LANGUAGE plpgsql
AS $$
BEGIN
  return query
  SELECT
    ua.id,
    ua.login,
    ua.owner_name
  FROM user_apt ua
  inner join i_users inp ON ua.id=inp.id AND ua.login=inp.login;
END
$$;

In C#

using Dapper;
using Npgsql;
using System;
using System.Collections.Generic;
using System.Data;

namespace DapperSPWithClass
{
    public class User
    {
        public long ID { get; set; }
        public string Login { get; set; }
        public string Owner_Name { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var connectionString = "Server=127.0.0.1;Port=5432;Database=test;User     Id=My;Password=SuperSecretPass;Timeout=15;";
            var lsUser = new List<User> {
            new User{
                ID=13,
                Login="kos@test5"
            },
            new User{
                ID=29,
                Login="nsk@autotest1"
            }};

            using (var conn = new NpgsqlConnection(connectionString))
            {
                conn.Open();
                conn.TypeMapper.MapComposite<User>("t_user_test"); // Here is the magic we needed
                var res = conn.Query<User>("test_pass_object_array", commandType:     CommandType.StoredProcedure, param: new { i_users = lsUser });

            }

            Console.ReadKey();
        }
}

}

conn.TypeMapper.MapComposite<T>("t_user_test");

I found MapComposite in the article https://www.npgsql.org/doc/types/enums_and_composites.html

score 0 · Answer 2 · edited May 23 '17 at 10:32

0

You can pass the array as Table Valued Parameter. Unfortunately you would have to convert it into a DataTable first.

For example:

    conn.Execute("My_SP", new { values = myTable.AsTableValuedParameter("MyType") }, 
commandType: CommandType.StoredProcedure);

Some more examples here

edited May 23 '17 at 10:32

Community

1
1

answered Nov 18 '16 at 18:03

Void Ray

9,849
4
33
53

Pass an array of complex objects to a stored procedure with dapper

2 Answers2