Parameter Value For Stored Procedure

[Joe]

I want to update my source data using a stored procedure. I
need to know the correct method for passing parameters to that stored
procedure. My problem is with the .Value property. I want to assign
it the name of the column from the datatable, but not sure how to do
it. The field name that I am passing in this example is FirstName

prmFirstName.ParameterName = "@FirstName"
prmFirstName.SqlDBType = SqlDBType.Varchar
prmFirstName.Direction = ParameterDirection.Input
prmFirstName.Value = ????


I could use dsTest.dtDetail2.Item(i).FirstName - but this is at
the row level and it seems cumbersome that I would have to manually
loop through each row of the datatable to assign the correct value.

There must be a simpler way to apply updates to the entire
table without manually looping through each row. Does anyone know
what it is?

Any help appreciated.

J

 

[JT]

Joe,
The complete answer would take a book. Using the approach you are on would
indeed mean a row-by-row setting of the parameter and calling
..executenonquery for each row. Instead, you could assign your insert or
update stored procedure to be the insert or update command for either a
tableAdapter or a DataAdapter, and then call that object's update method.
You do have to be sure that your TableMappings are ok for this to work. The
easiest thing to do is just add a table adapter to your dataset and use the
configuration wizard to assign the appropriate select, update, insert, and
delete commands.
A great book to get you through this is David Sceppa's "Programming ADO.NET
2.0, Core Reference"

 

[Alec MacLean]

In essence, you're right - you do have to loop through each modified value
and send the new value to the database on a row-by-row basis and your method
would work fine if doing it this way. The limitation (to my knowledge and
I'm happy to be corrected!) is not with .Net, but with SQL Server and how it
receives individual values from parameters in a stored procedure (sproc).

The MS Enterprise Pattern & Practices team have a library block (DAAB) that
will support abstracted use of update, select, insert and delete methods on
the database server.
http://msdn2.microsoft.com/en-gb/practices/default.aspx
http://msdn2.microsoft.com/en-us/library/aa480453.aspx
and http://www.codeplex.com/entlib

John Papa wrote a great article on the subject for MSDN Mag in his Data
Points column back in 2005:

http://msdn.microsoft.com/msdnmag/find/default.aspx?type=Ti&phrase=Data Points&words=exact
Look for July, Aug and Oct 2005. While this applies to the older version of
the library, the methods are very similar. Updated methods are documented
in the Practices site.

In particular, the method that matches what you are looking for is discussed
here:
http://msdn.microsoft.com/msdnmag/issues/05/10/DataPoints/#S5

Basically, this will still do the loop of the rows in the table in your
dataset and call the appropriate SQL sproc, but hides the loop behind a
library method, so your code is easier to read.

Is there an advantage to using the DAAB? Well, I am a sole developer for a
small company - it helps me by providing ready-built abstractions for the
above type of use. Sure, I could roll my own, but the DAAB takes care of so
much it saved me loads of coding time - even just the connection management
is a benefit. Bigger coding teams will no doubt have different and deeper
views on it.

Al