Is it possible to have multiple SQL instructions in a ADO.NET Command.CommandText property?

Question

Summary

I'm currently writing an application where I have located my SQL instructions into a project's parameters.

Within the code, I get the value of my query which returns the query itself. Let's for instance say that my SQL query is like so:

select col1, col2, col3 from my_table

Besides, col1, col2 and col3 are from different tables and are migrated as foreign key into my_table. So, when it comes to the insert, I have to perform multiple INSERT statements to get the values from the other tables for these above-mentioned columns. Let's say as follows:

BEGIN TRANSACTION

insert into first_table (col_x, col_y) values ('col_x', 'col_y')
insert into second_table (col_z, col_a) values ('col_z', 'col_a')
insert into third_table (col_b, col_c) values ('col_b', 'col_c')

and finally:

insert into my_table (col1, col2, col3, col_v) values (@col1, @col2, @col3, 'col_v')

COMMIT

Take it that these col1, col2, col3 columns are auto-increment integers for tables first, second and third.

Questions

1. Could I write a complex SQL statement into the IDbCommand.CommandText property while each instruction would be separated by a semicolon (;)?

2. Is it possible to include a BEGIN TRANSACTION...COMMIT/ROLLBACK into this CommandText property?

3. In short, could I write something like this?

   Using cnx = New SqlConnection(connString)
       Using cmd = cnx.CreateCommand()
           cmd.CommandText = "BEGIN TRANSACTION " _
                     & "insert into first_table (col_x, col_y) values ('col_x', 'col_y');" _ 
                     & "insert into second_table (col_z, col_a) values ('col_z', 'col_a');" _
                     & "insert into third_table (col_b, col_c) values ('col_b', 'col_c');" _
                     & "insert into my_table (col1, col2, col3, col_v) values (@col1, @col2, @col3, 'col_v'); " _
                     & "COMMIT"
           cmd.ExecuterNonQuery()
       End Using
   End Using
   

EDIT #1

I should have mentioned it before... Mack's answer is the way I would like to go, except that I can't because of strange policies within the IT department of my client, except if I use their custom component which I rather avoid for simplicity sake. Notice that I upvoted Mack's answer anyway since it is a viable solution no matter what.

Thanks in advance for your precious help and time! This is crucial for me!

Giordano

Answer 1

If you can't use stored procedures then perhaps this code may meet your requirements:

SqlConnection cnx = new SqlConnection(connString);
SqlCommand cmd = cnx.CreateCommand();
cnx.Open();
string complexCommand = string.Concat(
"DECLARE @first_table AS TABLE(col1 int IDENTITY, col_x varchar(20), col_y varchar(20))"
, " DECLARE @second_table AS TABLE(col2 int IDENTITY, col_z varchar(20), col_a varchar(20))"
, " DECLARE @third_table AS TABLE(col3 int IDENTITY, col_b varchar(20), col_c varchar(20))"
, " DECLARE @my_table AS TABLE(col1 int, col2 int, col3 int, col_v varchar(20))"
, " DECLARE @col1 int"
, " DECLARE @col2 int"
, " DECLARE @col3 int"
, " BEGIN TRAN"
, " BEGIN TRY"
, "   insert into @first_table (col_x, col_y) values ('col_x', 'col_y')"
, "   SET @col1=@@IDENTITY"
, "   insert into @second_table (col_z, col_a) values ('col_z', 'col_a')"
, "   SET @col2=@@IDENTITY"
, "   insert into @third_table (col_b, col_c) values ('col_b', 'col_c')"
, "   SET @col3=@@IDENTITY"
, "   insert into @my_table(col1, col2, col3, col_v) values (@col1, @col2, @col3, 'col_v')"
, "   COMMIT"
, " END TRY"
, " BEGIN CATCH"
, "   ROLLBACK"
, " END CATCH");

cmd.CommandText = complexCommand;
cmd.ExecuteNonReader();

I have added table variables as necessary to get the example code running, obviously you can utilise your permanent tables.

Liberal

@WillMarcouiller C# supports multiline string literals. – Concessionaire

Answer 2

Try using transactions...

using (SqlConnection cnx = new SqlConnection(connString))

{
    cnx.Open();

    // Start a local transaction.
    SqlTransaction sqlTran = cnx.BeginTransaction();

    // Enlist a command in the current transaction.
    SqlCommand command = cnx.CreateCommand();
    command.Transaction = sqlTran;

    try
    {
        // Execute two separate commands.
        command.CommandText = "insert into first_table (col_x, col_y) values ('col_x', 'col_y')";
        command.ExecuteNonQuery();
        command.CommandText = "insert into second_table (col_z, col_a) values ('col_z', 'col_a')";
        command.ExecuteNonQuery();
        command.CommandText = "insert into third_table (col_b, col_c) values ('col_b', 'col_c')";
        command.ExecuteNonQuery();
        command.CommandText = "insert into my_table (col1, col2, col3, col_v) values (@col1, @col2, @col3, 'col_v')";
        command.ExecuteNonQuery();

        // Commit the transaction.
        sqlTran.Commit();
    }
    catch (Exception ex)
    {
        // Handle the exception if the transaction fails to commit.
        // do something with this (ex.Message);
        try
        {
            // Attempt to roll back the transaction.
            sqlTran.Rollback();
        }
        catch (Exception exRollback)
        {
            // Throws an InvalidOperationException if the cnx 
            // is closed or the transaction has already been rolled 
            // back on the server.
            // do something with this (exRollback.Message);
        }
    }
}

Liberal

+1 Your approach is definitely THE way to go, according to me. Unfortunately, I am unallowed to use transactions within the code itself except by using custom components which I shall avoid for simplicity sake. I should perhaps have mentioned it before, sorry. And thanks for your time answering the question. =) – Giordano

Sadly, I can't. It is somehow not within the "legal" practices, according to the architects team. As I am a simple consultant for the customer, no exception rules are allowed. – Giordano

I can use transactions in my C# code, so this solution was the solution for me. – Unseal