Search code examples
dapperdapper-fluentmap

using dapper to insert with stored procedure ( maybe tvp )

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

Solution

  • 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.