0

I have stored procedure in database that I try to call from C#. It looks like this:

        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            try
            {
                SqlCommand cmd = new SqlCommand("dbo.NameOfMyProcedure", connection);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                SqlParameter firstParam = new SqlParameter("@FirstParam", new
                {
                    Message_ID = message.MessageId,
                    Created = createdTime,
                    Type = 0,
                    ...
                });
                SqlParameter secondParam = new SqlParameter("@SecondParam", null);

                cmd.Parameters.AddRange(new SqlParameter[] { firstParam, secondParam });
                cmd.ExecuteNonQuery();
            }
            finally
            {
                connection.Close();
            }

The procedure expects two arguments, a nullable varchar (@SecondParam) and complex type (@FirstParam) that is defined inside DB (Programmability -> Types -> User-Defined Table Types).

I tried to match the type inside DB exactly and create anonymous type, in hope that it'll convert my type to DB type and everything will work. However, this didn't work. I get ArgumentException: No mapping exists from object type (...) to a known managed provider native type. How can I pass complex type into DB procedure without having to use EF and generate all those types into C# classes?

SoptikHa
  • 437
  • 4
  • 19
  • 2
    Note that the try/finally is completely unnecessary in your code. Since you create the connection in a using statement, it will be automatically disposed when it leaves the using block, thus making the manual .Close() and the try/finally redundant. – mason Aug 27 '19 at 13:27
  • 1
    Have you read https://stackoverflow.com/questions/4719507/classic-ado-net-how-to-pass-udt-to-stored-procedure ? – mjwills Aug 27 '19 at 13:28
  • @mjwills thanks, this looks exactly like something I'm looking for. I didn't read it before, I couldn't find anything like this. – SoptikHa Aug 27 '19 at 13:39
  • 1
    I found that by Googling for `ADO.net pass UDT`. There may be other useful links behind that search. – mjwills Aug 27 '19 at 13:40

0 Answers0