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

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

inse开发者_StackOverflow中文版rt 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!

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.

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);
        }
    }
}