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

Question

I want to pass object or list to sql server stored procedure. My purpose is to insert multiple records at a time via store procedure.

So, is there any way to do it?

Answer 1

You can use sqlParameter. Like:

SqlParameter param1 = new Sqlparameter(SPVariablename , ValueofVariablewhichYouWantToPass)

Any as many parameter you want param2, param3 and in end

cmd.Parameter.add(param1)
Execute the command

Answer 2

There Number of Ways like using XML and List. But if you want to do bulk insert, you can use

BULK INSERT

Answer 3

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

BULK INSERT is a good suggestion for just inserting large numbers of rows and it is much quicker than single inserts.

For inserting hierarchical object data eg Parent 1 has 2 child records, Parent 2 has 3 child records, Parent 3 has 1 child record... I have in the past used XML to serialize the object structure in .Net before passing to SQLServer and shredding it out into tables. Of course if you are doing stuff like this a lot you would probably be better served to be using Entity Framework or NHibernate etc...

Answer 4

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

You can find good start from this blog.

And this old question of stackoverflow also help you.