Multiple queries on same connection using same T-SQL variables

Question

I'm trying to create something similar to SQL Server Management Studio.

I want the ability to run several queries on the same connection like one does in the same tab in SSMS, however from c# each SqlCommand is executed separately even with the same SqlConnection so they can't see each others DECLARE's

Example: If you run two queries:

DECLARE @ted varchar(100) = 'Ted A';
SELECT @ted as [Query1];

and the other

SELECT @ted as [Query2];

If you run these two, in order, separately in a SSMS tab, you should get two correct results, one from each query.

However the problem I have is if I have one SqlConnection and I create two SqlCommand objects then they don't see the @ted variable even though they are the same connection.

How can I create a connection to a sql server database so that it behaves like an SSMS tab in that each subsequent query is in the same scope, and therefore I can use @variables in all queries?

Note: the same behavior as in SSMS can also be found in the SQLCMD utility.

Edit: i have just realised what i have asked is not how SSMS works, this is actually impossible. you should be able to access #tables etc from the same connection but not @variables

If you run `SELECT @ted as [Query2];` this query without declaring `@ted` you will get an error — Pரதீப், Mar 16 '15 at 16:32
i want the ability to run the two queries `in order` however some time apart. — James Peach, Mar 16 '15 at 16:47
so you select one query to declare the variable. and you run it and when you select the second query to select the value you still have access to the @variable in the same tab — James Peach, Mar 16 '15 at 16:48
@EhsanSajjad this is for a university project. and once complete it will be an open source cross platform alternative to SSMS — James Peach, Mar 16 '15 at 17:12

Nick Bailey · Answer 1 · 2015-03-16T16:54:51.513

0

You should think of a single query window in SSMS as roughly equivalent to a single SQLCommand, and a single instance of SSMS as roughly equivalent to a single SqlConnection. TSQL Variables have batch scope not connection scope.

A SQLCommand executes a batch of TSQL, possibly including multiple executable statements. You can simple put both of your queries into a single command.

Now, if you want your command to return multiple values from multiple select statements, you would use the NextResult method of the SqlDataReader. This will move to the result set of the next statement. Process the results of the each statement, then use NextResult to interate through the result sets.

Simple example

queryText = "Declare @Ted Varchar(100) = 'Ted A'; SELECT @ted --Or some other query using @ted; Select @ted --or yet another query using @ted;"
command = new SqlCommand(queryText);
reader = command.ExecuteReader();
do 
{
    //Result set handling code goes here
    do 
    {
    //Record level handling code goes here
    } while reader.Read();
    //More result set handling code goes here
} while reader.NextResult();

Hope this helps

edited Mar 16 '15 at 16:54

answered Mar 16 '15 at 16:39

Nick Bailey

3,078
2
11
13

Yes I am aware of this, i require the behavure to run multiple queries on the same tab. hope this makes sense. – James Peach Mar 16 '15 at 16:44
Did you see the bit about NextResult? – Nick Bailey Mar 16 '15 at 16:45
yes i understand i can do `select 1 as a; select 2 as b;` this is not however what i want. – James Peach Mar 16 '15 at 16:46
Then my advice is to change project..:P – Ehsan Sajjad Mar 16 '15 at 17:13
I have just realised that the question asked is wrong that functionality does not exist. – James Peach Mar 16 '15 at 17:23

score 0 · Answer 2 · answered Mar 16 '15 at 16:58

The DECLARE is scoped to a command and not a connection. If you want to reuse declares then you will need to utilize a T-SQL parser.

Otherwise you can get started with something like this:

var commandText =
    "DECLARE @ted varchar(100) = 'Ted A';" +
    "SELECT @ted as [Query1];" +
    "SELECT @ted as [Query2];";
using(var connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (var command = new SqlCommand(commandText, connection))
    {
        using(var reader = command.ExecuteReader())
        {
            // There are two result sets and each result set has one result.
            do
            {
                // You will need to use the Schema Table to dynamically
                // generate the results view
                var schema = reader.GetSchemaTable();
                // "ColumnName" column will have the "Query1" and "Query2"
                var columnNameColumn = schema.Columns["ColumnName"];
                var row = schema.Rows[0][columnNameColumn];
                Console.WriteLine(row);

                // Now we write the results
                while(reader.Read())
                {
                    Console.WriteLine(reader.GetString(0));
                }
            }
            while(reader.NextResult());
        }
    }
}

Yes however this functionality exists in SSMS. I wish to know how I can emulate this behavior. I want some connection(tab) to be created and then I need a mechanism to submit queries to them but in the same scope. like in SSMS the scope is the tab and you can execute multiple statements separately in the same scope. — James Peach, Mar 16 '15 at 17:10
The default behavior in SSMS is to submit the whole tab and return the results. You can also select some text in the tab and submit that, but if you do not highlight the delcares with the query then there is an error. — Dustin Kingen, Mar 16 '15 at 17:16
ok and can you use the same #tables accross multiple connections, i am wrong about this you can't share @variables if you dont select them — James Peach, Mar 16 '15 at 17:19

Multiple queries on same connection using same T-SQL variables

2 Answers2