use stored procedure to store an indeterminate amount of values

[Chris Cummings]

It's easy to do this with a data adapter.

Create stored procedures for each case: sp_insertSpice
sp_updateSpice, sp_deleteSpice

Then create SqlCommands for each that invoke the stored
procedures:
e.g. insert:
SqlCommand insertCmd = new SqlCommand
("sp_insertSpice",conn,tran);
insertCmd.CommandType = CommandType.StoredProcedure;

For each column of the table, map the column to a stored
procedure parameter:
insertCmd.Parameters.Add("@spiceName, SqlDbType.VarChar, 0,
"name")

For insert, make the primary key parameter an output
parameter if it's an autoincrement.

Then setup the data adapter to fire these commands for
each row in the table:

try
{
da.InsertCommand = <insert command>
da.UpdateCommand = <update command>
da.DeleteCommand = <delete command>
da.Update(ds.SpiceTable);
tran.Commit();
} catch (Exception e)
tran.Rollback();
}
finally
{
conn.Close();
}

 

[Daniel]

Hi Chris,



thanks for your answer. I think though that I didn't explain exactly what I'
d like to do. My problem is that I might have to insert two or more spices
is my database but can only put one at the time if using parameters of a
stored procedure. This is the problem troubling me!



Greetings



Daniel

 

[William \(Bill\) Vaughn]

It's not a good idea to name your stored procedures with "sp_". This tells
SQL Server to look in the Master database first for the SPs which slows down
their execution.

--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________