Is there any way to pass an object or list of stored procedure to sql server?

Question

I want to pass an object or list to a sql server stored procedure. My goal is to insert multiple records at a time using the storage procedure.

So is there a way to do this?

Answer 1

You can use sqlParameter. How:

SqlParameter param1 = new Sqlparameter(SPVariablename , ValueofVariablewhichYouWantToPass) 

Any number of parameters you want param2, param3 and at the end

 cmd.Parameter.add(param1) Execute the command 

Answer 2

There are several ways to use XML and a list. But if you want to do volume insertion, you can use

BULK INSERT

Answer 3

I believe in SQLServer 2008, and later you can add a strongly typed DataTable parameter to your stored procedure that you can pass .Net DataTable to.

BULK INSERT is a good suggestion to just insert a large number of rows, and it is much faster than individual inserts.

To insert hierarchical object data, for example, parent 1 has 2 child records, parent 2 has 3 child records, parent 3 has 1 child record ... I used XML in the past to serialize the structure of the object in .Net before Go to SQLServer and shred it into tables. Of course, if you do so much, you will probably be better off using Entity Framework or NHibernate, etc.

Answer 4

You are looking for Table Value Parameters . It allows you to pass read-only table variables to a stored procedure.

You can find a good start from this blog.

And this old question fooobar.com/questions/68278 / ... will also help you.