How to run multiple SQL commands in a single SQL connection?

Question

I am creating a project in which I need to run 2-3 SQL commands in a single SQL connection. Here is the code I have written:

SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\project.mdf;Integrated Security=True");
con.Open();
SqlCommand cmd = new SqlCommand("select *  from " + mytags.Text + " ", con);
SqlDataReader rd = cmd.ExecuteReader();
if (rd.Read())
{
    con.Close();
    con.Open();
    SqlCommand cmd1 = new SqlCommand("insert into " + mytags.Text + " values ('[email protected]','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "','"+mytags.Text+"')", con);
    cmd1.ExecuteNonQuery();
    label.Visible = true;
    label.Text = "Date read and inserted";
}
else
{
    con.Close();
    con.Open();
    SqlCommand cmd2 = new SqlCommand("create table " + mytags.Text + " ( session VARCHAR(MAX) , Price int , Description VARCHAR(MAX), Date VARCHAR(20),tag VARCHAR(10))", con);
    cmd2.ExecuteNonQuery();
    con.Close();
    con.Open();
    SqlCommand cmd3 = new SqlCommand("insert into " + mytags.Text + " values ('" + Session + "','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "','" + mytags.Text + "')", con);
    cmd3.ExecuteNonQuery();
    label.Visible = true;
    label.Text = "tabel created";
    con.Close();
}

I have tried to remove the error and I got that the connection is not going to else condition. Please review the code and suggest if there is any mistake or any other solution for this.

Solution 1

Just change the SqlCommand.CommandText instead of creating a new SqlCommand every time. There is no need to close and reopen the connection.

// Create the first command and execute
var command = new SqlCommand("<SQL Command>", myConnection);
var reader = command.ExecuteReader();

// Change the SQL Command and execute
command.CommandText = "<New SQL Command>";
command.ExecuteNonQuery();

Solution 2

The following should work. Keep single connection open all time, and just create new commands and execute them.

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (SqlCommand command1 = new SqlCommand(commandText1, connection))
    {
    }
    using (SqlCommand command2 = new SqlCommand(commandText2, connection))
    {
    }
    // etc
}

Solution 3

Just enable this property in your connection string:

sqb.MultipleActiveResultSets = true;

This property allows one open connection for multiple datareaders.

Solution 4

I have not tested , but what the main idea is: put semicolon on each query.

SqlConnection connection = new SqlConnection();
SqlCommand command = new SqlCommand();
connection.ConnectionString = connectionString; // put your connection string
command.CommandText = @"
     update table
     set somecol = somevalue;
     insert into someTable values(1,'test');";
command.CommandType = CommandType.Text;
command.Connection = connection;

try
{
    connection.Open();
}
finally
{
    command.Dispose();
    connection.Dispose();
}

Update: you can follow Is it possible to have multiple SQL instructions in a ADO.NET Command.CommandText property? too

Solution 5

This is likely to be attacked via SQL injection by the way. It'd be worth while reading up on that and adjusting your queries accordingly.

Maybe look at even creating a stored proc for this and using something like sp_executesql which can provide some protection against this when dynamic sql is a requirement (ie. unknown table names etc). For more info, check out this link.

View more solutions

Comments

[user1831272]

var cmd = new SqlCommand("select * from " + mytags.Text + " ", con); var rd = cmd.ExecuteReader(); if (rd.Read()) { cmd.CommandText="insert into " + mytags.Text + " values ('[email protected]','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "','"+mytags.Text+"')"; cmd.ExecuteNonQuery();

[Alex Gordon]

my first command is to create a temp table. my next command needs to use that temp table. in your example, the temp table disappears. when i did a trace, the trace showed me "exec CreateTableSproc ". how would i get the temp table to be seen by the next command?

[interesting-name-here]

This I believe should be the answer.

[Joe Zack]

And if you plan on enabling MARS make sure you read up on the "special considerations" here: msdn.microsoft.com/en-us/library/h32h3abf(v=vs.110).aspx

[Hamed_gibago]

I found my answer.and answered it above

[abatishchev]

@l--''''''---------'''''''''''': wrap the whole block with TransactionScope. Sorry for a latest response :))

[czifro]

I believe parameterized queries are used to mitigate this issue.

[SteveC]

This is correct except the entry in the connection string should be: MultipleActiveResultSets = true;

[Banana Cake]

Thanks, I needed this.

[Filipe Madureira]

Anyone reading this, ignore the reply and just google "parametrized query". This post is just confusing.

[Mr Moose]

@filipe, the create table statement isn't really going to work with parameterized queries. When building dynamic sql like the original poster is doing, it is prudent to use something like sp_executesql to protect against misuse or abuse. This is especially true if you are ally DDL as well as DML for the user with the active connection.

[Martin]

This has the additional advantage that there will only be one round trip to the database, because there is only one command that is executed. However, be advised that the number of parameters for an SqlCommand is limited to 2100.