using dapper to insert with stored procedure ( maybe tvp )

Question

I have a model PersonalInfo like so:

public string FirstName { get; set;}
public string LastName { get; set;}
public string Email { get; set; }
public IEnumerable<Addresses> Addresses{ get; set; }

And my Addresses model include something like:

public int AddressId { get; set; }
public string StreedAddress { get; set; }
public string State { get; set; }

I need to use stored procedure to insert these into the database, but I need to use dapper to do that .. Sorry I am new.. I tried something like below and it did not work:

  var Id =  connection.Execute("usp_Insert", personalInfo, commandType: CommandType.StoredProcedure);
    foreach (Addresses address in personalInfo.Addresses){
        connection.Execute("Insert", new {address, Id}, null, commandType: CommandType.StoredProcedure);
        };

I maybe doing a lot of things wrong here, I could use a table valuded param but I don't know how it works with dapper.

UPDATE:

So I guess if I don't want to pass the Addresses in the first one, can I exclude that from sending first? I still want to pass the whole model not one prop at a time.

Thanks

What didn't work? is Insert in the second call a stored procedure? — Simon, Jan 21 '16 at 14:18
@Simon Now it says "No mapping exists from object type Addresses .Data.Models.Addresses to a known managed provider native type. — NoviceDeveloper, Jan 21 '16 at 16:16

score 2 · Accepted Answer · answered Jan 21 '16 at 17:46

2

The problem is that dapper can't tell - with stored procedures - what parameters are needed. There are database APIs to query that, but it is hugely costly to do that all the time. For regular queries, it can understand well enough what is needed, but for stored procedures: they are mysteries. Consequently dapper just tries to include everything it can see.

Your best bet is a "new" projection that selects the properties you need for your parameters.

answered Jan 21 '16 at 17:46

Marc Gravell

1,026,079
266
2,566
2,900

True, how would you handle in the above code Addresses since it is an Enum? – NoviceDeveloper Jan 21 '16 at 18:10
1

@Novice I would just pick you the properties I want. So at the parent level probably `new { pi.FirstName, pi.LastName, pi.Email }`, and at the child level `new { addr.AddressId, addr.StreetAddress, addr.State }`. Hmm, formatting isn't working well on that... – Marc Gravell Jan 21 '16 at 20:14
definitely something is going on with formatting. Instead of looping through Addresses, can I use TVP... how do you go about doing that if so? – NoviceDeveloper Jan 25 '16 at 19:12
@Novice yes, but you need to populate a DataTable to do it at the moment. Is that workable? – Marc Gravell Jan 25 '16 at 19:36

using dapper to insert with stored procedure ( maybe tvp )

1 Answers1