ado.net using same sqlcommand object to execute multiple commands

Question

Can any one tell about Using same SqlCommand object to execute multiple commands in .net applicaton.

Situation: I have a Truncate table command which is executed first. then I will perform SqlBulkcopy operations then i want the same command object to execute another stored procedure which will perform some updation or moving the data to different tables.

I dont want to create a new Command object.

One more thing all the three operations are in a transaction.

Thanks in advance.

dandan78 · Accepted Answer · 2011-06-20 07:16:35Z

5

You don't have to create a new object. Just specify a new

command.CommandText = "SELECT FROM WHERE...";

with the neccesary parameters before executing each command.

answered Jun 20, 2011 at 7:16

dandan78

14.1k13 gold badges68 silver badges82 bronze badges

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

Comments

alwayslearning · Accepted Answer · 2011-06-20 07:25:57Z

3

You can use the same SqlCommand object to execute more commands. Just be sure to reset the following properties to appropriate values

SqlCommand.CommandText
SqlCommand.CommandType - set this to CommandType.StoredProcedure in case you need to execute a stored procedure next.
SqlCommand.Parameters - You can use SqlCommandBuilder.DeriveParameters to overwrite existing parameter information with required parameters, although this would entail an additional call to the server.

answered Jun 20, 2011 at 7:25

alwayslearning

4,6936 gold badges37 silver badges49 bronze badges

Comments

Akram Shahda · Accepted Answer · 2011-06-20 07:20:51Z

2

you can do this, I believe...

SqlCommand command = new SqlCommand();
command.CommandText = 
    "SELECT FROM WHERE...; SELECT FROM WHERE...; SELECT FROM WHERE...;";

edited Jun 20, 2011 at 7:20

Akram Shahda

14.7k5 gold badges46 silver badges67 bronze badges

answered Jun 20, 2011 at 7:19

Isaac Fife

1,69013 silver badges15 bronze badges

Comments

abatishchev · Accepted Answer · 2011-06-20 07:30:30Z

0

Can't you include all operations in one command text "GO" statement between them if you need query segments to be run in separate batches you can use the transactions also with this

TRUNCATE TABLE X;
GO

/* do SqlBulkcopy code*/
GO

/* EXECUTE STORED PROC*/
GO

edited Jun 20, 2011 at 7:30

abatishchev

101k88 gold badges303 silver badges443 bronze badges

answered Jun 20, 2011 at 7:23

DSharper

3,2279 gold badges32 silver badges47 bronze badges

Collectives™ on Stack Overflow

ado.net using same sqlcommand object to execute multiple commands

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related