Execute multiple sql commands with one trip to the database.

Ive got a List<sqlCommand> and I want to execute all the commands in it, but only using one trip to the database. I dont need any return values from any of these commands. Most of the commands are update/insert/delete. Sometimes after a insert command there may be a command "set @.NEWID =identity_insert" to facilitate the next insert to a related table, but thats the only other type of command to insert/update/delete.

I am using microsofts enterprise data application block. I want to execute all the commands in the list with just one trip to the database. Would the following code achieve this :

db = DatabaseFactory.CreateDatabase("wlmAdmin");
foreach (SqlCommand sc in saveCommands)
{
db.ExecuteNonQuery(sc);
}

or would this make multiple trips to the database? Im not 100% on whats going on behind the db object. If I create the db Item in the constructor of my class using "db = DatabaseFactory.CreateDatabase("wlmAdmin");" would this mean that every instance of that class would maintain an open database connection throughout its scope/lifetime?

Ive created the class to retrieve all data using a single stored procedure and then when saving, the class builds a List of SqlCommands which as Ive mentioned I want to execute in one trip, to make the number of trips to the database as small as possible. Because of this I don't need the db object maintain an open connection to the database all the time. However It doesnt seem to have any dispose methods so Im not clear on how it operates.

In the case that the code above opens a connection for each command, how would I got about batch executing the queries in one trip?

Thanks,

C

the code sample you provided would make seperate trips to the database for each Command in your saveCommands list. If you need to batch them all together with only a single trip to the database, you might want to consider using a Stored Procedure.

|||

Try with stored procedure.... It is a best solution for your problem...

|||

You can include multiple sql commands just by separating them with semi-colons -- but then it's very hard to track down problems (where did it fail?). You will be much better off using a stored proc as the other posters suggest. Be sure to include good error handling at all steps by checking @.@.error. You will probably want to wrap the whole thing inside a transaction. Be sure also to do these 2 sets:

set nocount on -- this stops needless traffic between sql server an you telling you how many rows were affected, there are other, better ways to get this info

set xact abort on -- the only way to guarantee that a sql error will cause proc termination and a rollback (assuming you've done a begin tran)

|||

Ok, so how do I go about setting up a stored procedure which takes multiple (variable) sqlcommands as arguments.

One of the main reasons Im using sqlcommands is so I can use sqlParameter objects to avoid sql injection, so If possible I want to use the commands, rather than a lot of sql strings seperated by semi-colons.

Im not too advanced on Stored Procedures, so all details are much appreciated :-)

Thanks,

C

|||

Here's a generic "template" -- This examples here will take care of 95% of what you need to do. Note that I may have some typos so if you get a syntax error that's probably why. You don't send the commands as arguments, you put them inside the proc.

Note especially:
- Error handling -- use of @.@.error and @.@.rowcount to determine whether things went bad
- Wrapping everything in a transaction and rolling back in the event of a bad result -- most important!
- Use of OUTPUT variables -- you can use as many of these as you want to return data to the caller -- just be sure to set the parameter type to Output in the caller
- Use of PRINT statement (during debugging)

If you google "sql server stored procedure" you will get many, many examples.

You can write procs in VS or in Enterprise Manager or for that matter in notepad. Unless sql server debugging is turned on (and it isn't in many shops because of permissioning issues) there is no real debugging available

create proc MyProc (IntVar as int, Char50Var as Char(50), VarCharVar as varcahr(255), FloatVar as float ReturnVar1 as int OUTPUT, ReturnVar2 as char(10) OUTPUT) as
set nocount on
set xactabort on

begin tran
declare @.ErrNum int,
@.NumRows int,
@.ErrMsg varchar(255)

--do something--
/* Check error code -- be sure to save it to a local variable first so it does not get overwritten */
select @.ErrNum = @.error
if @.ErrNum <> 0
begin
RAISERROR ('Error occurred...blah, blah, blah',16,1) -- the 16,1 are just there because they have to be, you can use any value
ROLLBACK TRAN
RETURN
end

/* Use print statements liberally when you are debugging to find out what's happening'
/* print 'blah, blah, blah' */

--do something--
select @.ErrNum = @.error
if @.ErrNum <> 0
begin
RAISERROR ('Error occurred...blah, blah, blah',16,1) -- the 16,1 are just there because they have to be, you can use any value
ROLLBACK TRAN
RETURN
end
select @.NumRows = @.@.rowcount
if @.NumRows <> 1
begin
select @.ErrMsg = 'Error: Expected to update 1 row only but ' + convert(varchar(20),@.NumRows) + ' were updated instead!'
RAISERROR (@.ErrMsg ,16,1) -- the 16,1 are just there because they have to be, you can use any value
ROLLBACK TRAN
RETURN
end

etc.
...
...
...
commit tran
go

grant exec on dbo.MyProc to ....user or windows group......
go

NOTE: To change a proc later you can drop and recreate it or use Alter Proc instead of Create Proc.
Alter Proc retains all the permissions

|||

Hi,

Thanks again for the reply. Im still not 100% here. Lets say I have 15 sqlCommands in my List<sqlCommand>. Now I want to send all these to the stored procedure at once how do I do this? There is no way in .net to called a stored prcedure with sqlCommands as parameters, so thats the first thing I dont understand? Each sqlCommand has different Parameters, a different number of Parameters and each operates on different tables.

You say I dont call the procedures as commands, I put them inside the procedure. Does this mean I write the procedures dynamically in my code, call create on them, and then execute them, all from my .net application? I think I must have this wrong.

If I say for example my class has created 2 commands for me to execute :

delete from Table1 where id = @.id (1 Parameter of type int)

update Table1 set uname = @.uname, lname=@.lname where id = @.id (2 parameters, 2 nvarchar, 1 int)

How do I create one stored procedure that will accept both these in one trip to the database and execute them. Again thanks for the feedback so far.

C

|||

staplebottom:

Lets say I have 15 sqlCommands in my List<sqlCommand>.

Either:

1. Make 15 separate calls, in which case you need to use ado to manage your transaction (you said they should all go together). If no transaction issue then just make 15 calls

2. Create 1 proc with all 15 commands and pass in all the parms you need

3. Send 1 call with 15 different 'exec ...' commands, separated by commas -- but then it's very hard to figure out whether anything went wrong and if so, which one.

|||

With that example, I would say you are trying to re-invent the dataadapter class, the dataset class, or a combination of both.

|||

So Im doing things wrong so. My aim was to just hit the database once to select all the data, one stored procedure, which then saves all the data into a dataset with a number of tables.

Then I manually check which tables are changed and create a command for each insert / update and delete. I understand that the dataset has this functionality, but felt that I might be able to reduce the number of trips to the database by implementing this myself for my own classes. So I created a list of commands for the changes to the dataset and thought I might be able to execute them all with one trip to the database. If this isnt possible I suppose I have one more question before I revert to using a few stored procedures (one for each table).

If I convert the sql commands to one long sql string and send it to the database as one command :

1. If I put a value into a sqlParameter say as Varchar and then read it back into a string, will it be safe from causing Sql Injections?

2. Will one long string of sql text e.g (update table set f =2; insert into table1 (test) values ('x') ; etc etc ..), seperated by by semi-colons execute faster than executing the sqlcommands using a trip each time.

3. If I have a command in the semi-colon delimited string, set @.newid = identity_insert; will this actually work ok, storing the value in newid for the next few commands to use?

Thanks,

C

|||

I think you're worrying too much about making trips to the database. Keep things simple. Trying to do everything in one command will only get you into trouble.

staplebottom:

If I have a command in the semi-colon delimited string, set @.newid = identity_insert; will this actually work ok, storing the value in newid for the next few commands to use?

No, I don't believe it will because you aren't creating any context for @.newid to exist in

|||

Yeah I think your right. I think Ill just try use a few stored procedures. Well it was at least a learning experience. Thanks for the feedback.

|||

1. No

2. Yes

3. Yes, btw it'd be SET @.newid=identity_insert(); -- Notice the parenthesis

BTW, the dataadapter will batch up multiple commands into a batch for sqlserver to execute (Reducing the roundtrips). Unfortunately, I see no way of extending/allowing this functionality outside of the dataadapter as Microsoft has marked everything you'd need as notinheritable, etc. Shame.