How to bind array data to SqlCommand.Parameter?

Question

It's possible to perform this operation without "foreach" ???

I would understand if there are library functions to associate a parameter a list of data and perform N insert all together

 String SQL_DETAIL = "INSERT INTO [ImportDetail] " +
                                                      " ([idMaster] " +
                                                      " ,[operation] " +
                                                      " ,[data]) " +
                                                 " VALUES " +
                                                      " (@a,@b,@c) ";

foreach (ImportDetail imp in this.lstImportDetail )
{

     SqlCommand dettCommand = new SqlCommand(SQL_DETAIL, myTrans.Connection);
     dettCommand.Transaction = myTrans;

     dettCommand.Parameters.Add("a", SqlDbType.Int).Value = imp.IdMaster;
     dettCommand.Parameters.Add("b", SqlDbType.NVarChar).Value = imp.Operation;
     dettCommand.Parameters.Add("c", SqlDbType.NVarChar).Value = imp.Data;


     i =i+ (int)dettCommand.ExecuteNonQuery();
 }

Thank for help

1) Is there any special reason not to use foreach? 2) You need to use @a, @b and @c in your .Add method either. 3) With foreach, you need to .Clear all parameters beginning on each iteration. 4) You don't need to cast int since ExecuteNonQuery already returns int. 5) Use using statement to dispose your database connections and objects. — Soner Gönül
– Soner Gönül, Commented Nov 13, 2014 at 13:08
It's a fragment of long transaction, anyway hand me a link to correct this. @SonerGönül — daniele3004
– daniele3004, Commented Nov 13, 2014 at 13:14
I would understand if there are library functions to associate a parameter a list of data and perform N insert all together @SonerGönül — daniele3004
– daniele3004, Commented Nov 13, 2014 at 13:15

Matt G. · Accepted Answer · 2014-11-13 14:01:23Z

1

If you were feeling frisky, you could create a user-defined table type and put the data from all your ImportDetail objects into a DataTable. This wouldn't totally alleviate the need for a loop, though -- it just makes it more efficient since you're only transforming data in memory instead of passing it to a remote database. Once you have your DataTable populated to match the schema of your table type, you can pass it as a parameter to a stored procedure to handle your INSERTs. The procedure can be made transactional if you'd like, and since your input data will already be in a table (of sorts), the SQL for the stored procedure should be pretty simple, as well.

For more info on user-defined table types and how they're used in .NET, check out this link: http://msdn.microsoft.com/en-us/library/bb675163.aspx

Going a little further, if this is something you have to do quite often, it's not out of the realm of possibility to turn this into some sort of extension method on IEnumerable to keep things DRY.

Is this the sort of solution you were looking for? It's a little hard for me to tell given the wording of the question.

edited Nov 13, 2014 at 14:01

answered Nov 13, 2014 at 13:18

Matt G.

1,7722 gold badges11 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

daniele3004 Over a year ago

This is not what I was looking for but but thanks for the idea

dvIta · Accepted Answer · 2014-11-14 11:53:44Z

1

You can define two array list: the first will have the name of the parameter and the second its value:

        ArrayList arrayName = new ArrayList { };
        ArrayList arrayValue = new ArrayList { };

        arrayName.Add("@Parameter1");
        arrayName.Add("@Parameter2");
        arrayValue.Add(value1);
        arrayValue.Add(value2);

        for (int i = 0; i < arrayName.Count; i++)
        {
        cmd.Parameters.AddWithValue(arrayName[i].ToString(), arrayValue[i]);
        }

Then execute command

answered Nov 14, 2014 at 11:53

dvIta

1472 silver badges8 bronze badges

Collectives™ on Stack Overflow

How to bind array data to SqlCommand.Parameter?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related