CODEWITHSUNDEEP
c#sqlstored-procedures
Silver Qui.
Silver Qui.

Reputation: 59

How can I send a C# model list to a stored procedure in SQL?

I want to send a list of this model and receive it into a stored procedure SQL:

public class Worker
{
    public int Id { get; set; } 
    public string Name { get; set; } 
    public string LastName { get; set; }
}

List:

List<Worker> worker = new List<Worker>({ 
    new Worker {
        Id = 1, 
        Name = "Lois", 
        LastName= "Smith"
    },
    new Worker {
        Id = 2, 
        Name = "Peter", 
        LastName= "Smith"
    }
});

Stored Procedure: ???????????

Upvotes: 1

Views: 1442

Answers (1)

jkramer
jkramer

Reputation: 91

I would go ahead and reference this page. https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017. You can create a User Defined Table Type and set up a stored procedure to take you user defined table type as input.

If you're using SqlCommand you then just add your list of objects under comm.Parameters before you call ExecuteNonQuery. Note that your object's names and types must match your user defined table type.

I see that other have recommended doing the calls individually. This is often fine so long as you can manage your connections. If the operation is inserting a lot of data in a loop and you call a procedure to add records 1 at a time you can get connection issues.

Upvotes: 2

Related Questions