How to combine multiple SQL Commands into one?

Question

Is it possible to optimize the following queries by merging them inside a single SQLCommand?

SqlCommand cmd = new SqlCommand
{
    CommandType = CommandType.Text,
    CommandText = "DELETE FROM cbu_naslovi WHERE [ID]='" + CurrentID + "'",
    Connection = con
};
SqlCommand cmd1 = new SqlCommand
{
    CommandType = CommandType.Text,
    CommandText = "DELETE FROM cbu_deli WHERE [IDX]='" + CurrentID + "'",
    Connection = con
};
cmd.ExecuteNonQuery();
cmd1.ExecuteNonQuery();

EDIT: Working solution, as suggested by the community answers bellow

SqlCommand cmd = new SqlCommand
{
    CommandType = CommandType.Text,
    CommandText = "DELETE FROM cbu_naslovi WHERE [ID] = @CurrentID; DELETE FROM cbu_deli WHERE [IDX] = @CurrentID",
    Connection = con
};
cmd.Parameters.AddWithValue("@CurrentID", CurrentID);
cmd.ExecuteNonQuery();

you could use a stored procedure that requires one database call? — James Cooke, Oct 24 '18 at 11:18
You will gain almost nothing by executing them in one query. What you can do - is run them asynchronously then if queries take long time your execution will takes only time of longest query. — Fabio, Oct 24 '18 at 11:22
Deleting from two different tables is two different logical operations. You can't optimize those just by stuffing the commands together, because parsing them isn't the bottleneck. Issuing two commands is perfectly fine. (But, as has been mentioned, use parameters to prevent SQL injection. Micro-ORMs like Dapper make this so easy that there's no excuse not to.) — Jeroen Mostert, Oct 24 '18 at 11:22
@JeroenMostert, Microsoft's `SqlParameter` class is simple to use as well ;) — Fabio, Oct 24 '18 at 11:23
@Fabio: yes, but with Dapper you can't even argue that you need an extra line of code. We all know how expensive those are... — Jeroen Mostert, Oct 24 '18 at 11:25
@Arvo Your remark was hard to understand as someone with little background in programming. I am using Parameters where user input is possible. CurrentID is an internally defined Int, I get it by converting the [ID] from the current index position `int.TryParse(dtAddress.Rows[dg_address.SelectedIndex].ItemArray[0].ToString()`. Is SQL injection still possible? — Adephx, Oct 24 '18 at 11:51
@Adephx Likely in such case injection is not possible, but building sql command by hand is usually not good practice. You didn't tell about CurrentID type either, but you enclosed its value in apostrophes - this implied some textual value... If my comment was hard to understand, but you read it and understood - then maybe my comment had some value for you after all :) — Arvo, Oct 24 '18 at 12:00
@Arvo, Technically injection with `integer` is possible, because integer converted to the string based on current culture format settings. For example if I can define negative number sign `-` as `'DELETE FROM ... --`injected query will be included in the query. — Fabio, Oct 24 '18 at 19:36

score 3 · Accepted Answer · edited Apr 16 '19 at 16:39

Yes, you can just separate them with a semicolon. For example I have code that executes the following in a single call

SET NOCOUNT ON;
DECLARE @decimalDate DECIMAL(12,0);
DECLARE @charDate CHAR(12);
DECLARE @utcDate DATETIMEOFFSET;
DECLARE date_cursor CURSOR FOR SELECT {1} FROM {0} WHERE ISNULL({1},0)!=0;
OPEN date_cursor;
FETCH NEXT FROM date_cursor INTO @decimalDate;
WHILE @@FETCH_STATUS=0
BEGIN
    BEGIN TRY SET @charDate=CONVERT(CHAR(12),@decimalDate);
        SET @utcDate=SwitchOffset(
                CONVERT(DATETIME,'20'
                    +SUBSTRING(@charDate,1,2)+'-'+SUBSTRING(@charDate,3,2)+'-'
                    +SUBSTRING(@charDate,5,2)+' '+SUBSTRING(@charDate,7,2)+':'
                    +SUBSTRING(@charDate,9,2)+':'+SUBSTRING(@charDate,11,2)
                ,121) AT TIME ZONE '{3}',0);
    END
    TRY BEGIN CATCH
        SET @utcDate=SysUtcDateTime();
    END CATCH;
    BEGIN
        TRY UPDATE {0} SET {2}=@utcDate WHERE CURRENT OF date_cursor;
    END TRY
    BEGIN CATCH END CATCH;
    FETCH NEXT FROM date_cursor INTO @decimalDate;
END;
CLOSE date_cursor;
DEALLOCATE date_cursor;

There are exceptions. For instance the "create procedure" statement must be the first statement of a block. But most DML can be batched like this.

Note that SQL Server supports the recent innovation known as the "line break", which can be proficiently employed even if statements are also separated by semicolons. Even if it takes two bytes more. — Jeroen Mostert, Oct 24 '18 at 11:28
You definitely write code here for other people, not for the machine ;) — Fabio, Oct 24 '18 at 19:39

score 1 · Answer 2 · answered Oct 24 '18 at 11:24

1

You can write it like this:

SqlCommand cmd = new SqlCommand
{
    CommandType = CommandType.Text,
    CommandText = $"DELETE FROM cbu_naslovi WHERE [ID]='{CurrentID}';DELETE FROM cbu_deli WHERE [IDX]='{CurrentID}'",
    Connection = con
};

answered Oct 24 '18 at 11:24

smolchanovsky

1,775
2
15
29

What does the "$" stands for? I assume that {string} is a replacement for +string+? – Adephx Oct 24 '18 at 11:40
2

It's string interpolation in C#, it's basically more readable than string.format(). But still use parameters as opposed to this. – Stephen Wilson Oct 24 '18 at 11:41
1

$ is string interpolation. https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/tokens/interpolated – smolchanovsky Oct 24 '18 at 11:41
@smolchanovsky I am safe to assume that this is the correct approach? `CommandText = "DELETE FROM cbu_naslovi WHERE [ID] = @CurrentID; DELETE FROM cbu_deli WHERE [IDX] = @CurrentID",` with `cmd.Parameters.AddWithValue("@CurrentID", CurrentID);` – Adephx Oct 24 '18 at 11:47
2

This shouldn't be accepted answer only because of possible SQL Injection vulnerability. – Fabio Oct 24 '18 at 19:38

score 0 · Answer 3 · answered Oct 24 '18 at 20:12

if you need to run Non Query operation, you could try to execute bunch of commands by using Server object. benefit: you could use GO in SQL statement. Command does not allow to use GO.

server.ConnectionContext.ExecuteNonQuery("your SQL statement -- could be 100 statements with hundrends of GO commands", ExecutionTypes.Default)

server variable has type Server

How to combine multiple SQL Commands into one?

3 Answers3