How to Run Multiple SQL Commands in a Single SQL Connection

C# Using one SqlConnection for multiple queries

In short don't do it

---

Creating a new instance of the class SqlConnection does not create a new network connection to SQL Server, but leases an existing connection (or creates a new one). .NET handles the physical connection pooling for you.

When you have finished with your connection (through which you can send multiple queries) just Close() or Dispose() (or use a using{} block preferably).

There is no need, and not good practise, to cache instances of the SqlConnection class.

Update

This is a better pattern for your method, you dont have to worry about the connections state

static void SqlQuery(string cmdString)
{
    using (var connection = new SqlConnection(connString))
    using (var cmd = connection.CreateCommand(cmdString, connection))
    { 
        connection.Open();    
        // query        
        cmd.ExecuteNonQuery();
    }
}

Execute multiple SQL commands in one round trip

The single multi-part command and the stored procedure options that you mention are the two options. You can't do them in such a way that they are "parallelized" on the db. However, both of those options does result in a single round trip, so you're good there. There's no way to send them more efficiently. In sql server 2005 onwards, a multi-part command that is fully parameterized is very efficient.

Edit: adding information on why cram into a single call.

Although you don't want to care too much about reducing calls, there can be legitimate reasons for this.

• I once was limited to a crummy ODBC driver against a mainframe, and there was a 1.2 second overhead on each call! I'm serious. There were times when I crammed a little extra into my db calls. Not pretty.
• You also might find yourself in a situation where you have to configure your sql queries somewhere, and you can't just make 3 calls: it has to be one. It shouldn't be that way, bad design, but it is. You do what you gotta do!
• Sometimes of course it can be very good to encapsulate multiple steps in a stored procedure. Usually not for saving round trips though, but for tighter transactions, getting ID for new records, constraining for permissions, providing encapsulation, blah blah blah.

How can I run two SQL commands with a single connection?

I can see a few issues here

1. Always, always, always use parameterized queries (props to @broots-waymb) and never, ever concatenate user input into a SQL command
2. Use the using keyword to automatically clean up any object with a Dispose() method, which includes SqlConnection and SqlCommand - this ensures proper cleanup in the presence of exceptions; also it just easier to write correctly
3. Use ExecuteNonQuery() if you're not expecting a recordset to be returned. As @jdweng pointed out the only query that returns a recordset is a SELECT statement (stored procedures might also). The meaning of Read() is this code is unclear, my guess is that it will always return false
4. Be careful when your database schema contains one table (Inventory) whose state is dependent on the state of another table (Rent). Consider strategies to avoid this, but if you can't, then you should consider wrapping the update to both tables in a database transaction to make sure the state of your system is consistent

Executing Multiple SQL statements in a single command against SQLServer Database in vb6

The only available solution to my problem is to move the statements to a stored procedure.
So I check if the procedure exists in the targeted db, if not I will create the procedure; else, I execute the procedure with the proper parameters.
I have also included the logic for checking the temp file if it exists in the db.

How to execute multiple SQL commands at once in pd.read_sql_query?

The issues you face are:

1. You need to pass the MULTI_STATEMENTS flag to PyMySQL, and
2. read_sql_query assumes that the first result set contains the data for the DataFrame, and that may not be true for an anonymous code block.

You can create your own PyMySQL connection and retrieve the data like this:

import pandas as pd
import pymysql
from pymysql.constants import CLIENT

conn_info = {
    "host": "localhost",
    "port": 3307,
    "user": "root",
    "password": "toot",
    "database": "mydb",
    "client_flag": CLIENT.MULTI_STATEMENTS,
}

cnxn = pymysql.connect(**conn_info)
crsr = cnxn.cursor()

sql = """\
CREATE TEMPORARY TABLE tmp (id int primary key, txt varchar(20)) 
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO tmp (id, txt) VALUES (1, 'foo'), (2, 'ΟΠΑ!');
SELECT id, txt FROM tmp;
"""
crsr.execute(sql)

num_tries = 5
result = None
for i in range(num_tries):
    result = crsr.fetchall()
    if result:
        break
    crsr.nextset()

if not result:
    print(f"(no result found after {num_tries} attempts)")
else:
    df = pd.DataFrame(result, columns=[x[0] for x in crsr.description])
    print(df)
    """console output:
       id   txt
    0   1   foo
    1   2  ΟΠΑ!
    """

(Edit) Additional notes:

Note 1: As mentioned in another answer, you can use the connect_args argument to SQLAlchemy's create_engine method to pass the MULTI_STATEMENTS flag. If you need a SQLAlchemy Engine object for other things (e.g., for to_sql) then that might be preferable to creating your own PyMySQL connection directly.

Note 2: num_tries can be arbitrarily large; it is simply a way of avoiding an endless loop. If we need to skip the first n empty result sets then we need to call nextset that many times regardless, and once we've found the non-empty result set we break out of the loop.

---

Related Topics