Batch Operations of SQL Command.

Hi,

Im trying to write a class that compiles a list of SQLCommands and then executes them all at once. Im trying to reduce the amount of calls to the database.

Im also trying just to update the fields which have changed so I cannot use Stored Procedures as that would mean writing way too many stored procedures for every permutation on every table in my database.

So Ive decided to build sql commands and then execute them all with one call to the database. When I print the commandtext property of the sqlcommand to the page whilst debugging It shows something like insert into XXX (f1,f2) values (@.v1,@.v2). Is there any way to see the final sql string, with the @.v1 variables replaced by the actual values in the parameters I have added to the sql command?

Im building the commands by creating a new sqlCommand. Then I set the commandtext to "insert into XXX (f1,f2) values (@.v1,@.v2)". Then I Add Parameters to the sqlCommand. My Parameters count is showing the correct value.

I want to be sure now that when I go to send these commands to the database as part of one long string that it will work.

Thanks,

C

Hi ,

I think you are going right. For seeing the result with the value don't use the parameters. What you can do is directly substitute the values in the SqlCommand lkike this

CommandText="Insert into XXX(f1,f2) Value('" + Field1.Value +"','" + Field2.Value + "')"

and then you will be able to see the value. I think you will be able to texecute the multiple queries. Special care needs to be taken if you are returning multiple result sets i.e Select statements

|||

Hi Satya,

Thanks for the response. My class is creating commands for itself, and then calling a Save() method in all its member classes each to return a List<sqlCommand> of Sql commands. So at the end Ive a list of sql commands to execute which Ive got in the correct order for execution.

However at this stage I want to be able to see exactly what is being sent to the database to test it before i put it to use. Here is an example from the function that builds the sqlCommand objects.

switch (scb.TransactionType) {/* CREATE SQL INSERT STATEMENT */case"Insert" : first =true; sqlString.CommandText ="INSERT INTO " + scb.DBTableName +" (";foreach (string value in scb.changedFieldsArray) {if (first) first =false;else sqlString.CommandText +=","; sqlString.CommandText +=" " +value; } sqlString.CommandText +=" )"; sqlString.CommandText +=" VALUES ("; first =true;foreach (string value in scb.changedFieldsArray) {if (first) first =false;else sqlString.CommandText +=","; sqlString.CommandText +=" @." +value; } sqlString.CommandText +=" )";foreach (SqlParameter sqlParamin scb.changedParamArray) { sqlString.Parameters.Add(sqlParam); }break;

So as you can see Im first building the commandText using @.field for the fieldnames and then adding the parameters using a loop. I assume this is the correct way to build a command? This function then returns this command. What I want to know is when I finally have all my commands in a list, how can I then check the final SQL strings that will be executed. I dont want to replace the @.field at this stage, because I dont need to, and Im not sure if Im still using the escaping and size properties of the parameters ive created if i do.

So how, when I've the final list of sqlCommand objects can i preview the final sql string that will be executed by the SQL Server?

Thanks Again,

C

|||

Hi,

Actually, if you want to execute the command, then adding the parameters using a loop is ok. But in this way, you can't preview the final sql string. I suggest you to put the following code after yours.

Suppose your insert comand is in this format: Insert into tables ( Field1, Field2 ) values (@.p_a,@.p_b)

int i=1;foreach (SqlParameter sqlParamin scb.changedParamArray){if(i==1){ sqlString=sqlString.Replace("@.p_a",sqlParam) }else if(i==2){ sqlString=sqlString.Replace("@.p_b",sqlParam) } .... i++;}

And then you can preview the final sql command by sqlString variable.

Meantime, there's another method to achieve this.

Suppose your insert comand is in this format: Insert into tables ( Field1, Field2 ) values ( {0},{1})

string[] para_array =new string[para_num]/// in this sample, para_num=2int i=0;foreach (SqlParameter sqlParamin scb.changedParamArray){ para_array[i]=sqlParam; i++;}string sqlString_preview =string.Format(sqlString,para_array[0],para_array[1]);

In this way, sqlString_Preview also stands for the preview of sql command.

Thanks.