Reduce number of database calls

I have a stored-procedure which accepts five parameters and performing a update on a table

Update Table
Set field = @Field
Where col1= @Para1 and Col2=@Para and Col3=@Para3 and col4 =@aPara4

From the user prospective you can select multiple values for all the condition parameters. For example you can select 2 options which needs to match Col1 in database table (which need to pass as @Para1)

So I am storing all the selected values in separates lists.

At the moment I am using foreach loop to do the update

  foreach (var g in _list1)
            {
                foreach (var o in _list2)
                {
                    foreach (var l in _list3)
                    {
                        foreach (var a in _list4)
                        {
                           UpdateData(g, o, l,a);
                        }
                    }
                }
            }

I am sure this is not a good way of doing this since this will call number of database call. Is there any way I can ignore the loop and do a minimum number of db calls to achieve the same result?

Update

I am looking for some other approach than Table-Valued Parameters

---

You can bring query to this form:

Update Table Set field = @Field Where col1 IN {} and Col2 IN {} and Col3 IN {} and col4 IN {}

and pass parameters this way: https://stackoverflow.com/a/337792/580053

---

One possible way would be to use Table-Valued Parameters to pass the multiple values per condition to the stored procedure. This would reduce the loops in your code and should still provide the functionality that you are looking for.

If I am not mistaken they were introduced in SQL Server 2008, so as long as you don't have to support 2005 or earlier they should be fine to use.

---

Consider using the MS Data Access Application Block from the Enterprise Library for the UpdateDataSet command.

Essentially, you would build a datatable where each row is a parameter set, then you execute the "batch" of parameter sets against the open connection.

You can do the same without that of course, by building a string that has several update commands in it and executing it against the DB.

链接地址: http://www.djcxy.com/p/94680.html

上一篇: JPA更新加入

下一篇: 减少数据库调用次数