Multiple SQL query in C#

Question

I'm having trouble to figure out how to get more then one SQL query to work in C#. I have something like this:

breakControlq.CommandText = 
            @"SELECT something as q1 From table" +
            "SELECT somethingelse as q2 FROM table where this = this";

breakControlq.CommandType = CommandType.Text;
breakControlq.Connection = hd01Connect;
try
{
    hd01Connect.Open();
    breakControlRead = breakControlq.ExecuteReader();
    while (breakControlRead.Read())
    {
        textBox1.AppendText(breakControlRead["q1"].ToString());
        textBox2.AppendText(breakControlRead["q2"].ToString());
    } 
    catch(System.Data.SqlClient.SqlException ex)
    {
        MessageBox.Show(ex.Message, "Connection Error");
    }

Is this possible to do? Do I have to repeat the connection/command to every single query?

I'm pretty new at this and some of you will tell that this has already been answered somewhere, but I searched so many posts that I'm more confused then when a started to search for the solution.

I would suggest you to run both the queries separately. You can reuse the command and connection object. In your case I do not see a need to run the query at once accept to avoid 2 DB calls. — Habeeb, Jun 19 '17 at 09:39
Alternative is to wrap the query in a stored procedure and execute it. You can then read the values as 2 tables. This is the case if you want to reduce the number of DB hits. — Habeeb, Jun 19 '17 at 09:41
For the record, I **do not** think that the linked post above (https://stackoverflow.com/questions/13677318/how-to-run-multiple-sql-commands-in-a-single-sql-connection) is a duplicate of this; that question is asking about how to run two queries **separately**; this question is asking how to issue multiple `SELECT`s in a **single** query. Totally different thing! The one added by @PaulF looks much better: https://stackoverflow.com/questions/12715620/how-do-i-return-multiple-result-sets-with-sqlcommand — Marc Gravell, Jun 19 '17 at 09:46

Marc Gravell · Accepted Answer · 2017-06-19T09:51:21.307

5

You are looking for .NextResult(). The .Read() method changes to the next row in the current grid; .NextResult() moves to the next grid:

while (breakControlRead.Read())
{
   // process rows from first grid
}
if(breakControlRead.NextResult()) {
    while (breakControlRead.Read())
    {
       // process rows from second grid
    }
}

Alternatively; "dapper" would expose this via .QueryMultiple():

using(var multi = conn.QueryMultiple(sql, args)) {
    var s = multi.Read<string>().AsList(); // items from first grid
    var i = multi.ReadSingle<int>(); // items from second grid
    // ...
}

Note! You do need to ensure that your two queries are separated by either whitespace or ;; in your case this would be fine:

    @"SELECT something as q1 From table
      SELECT somethingelse as q2 FROM table where this = this";

(note whitespace)

alternative and more correctly:

    @"SELECT something as q1 From table;
      SELECT somethingelse as q2 FROM table where this = this;";

or:

    @"SELECT something as q1 From table;SELECT somethingelse as q2 FROM table where this = this;";

edited Jun 19 '17 at 09:51

answered Jun 19 '17 at 09:37

Marc Gravell

1,026,079
266
2,566
2,900

Perfect, this is it. Just had to change the SQL to this: SELECT something as q1 From table" + "SELECT somethingelse as q2 FROM table where this = this"; and added your first option. It works just fine now. Thanks alot... – Hugo Jun 19 '17 at 09:49
why do I need the last ";" in the end of the last query? – Hugo Jun 19 '17 at 09:55
@Hugo good practice more than anything else; you would be correct to say that the query engine will cope with it even if omitted - but if you're getting into multi-operation commands: why not get the syntax 100% correct at the same time? – Marc Gravell Jun 19 '17 at 10:00
Your absalutlly right ;) thanks alot for the explanation – Hugo Jun 19 '17 at 10:04

Rahul · Answer 2 · 2017-06-19T09:38:46.493

0

I'm having trouble to figure out how to get more then one SQL query to work in C#

Well, wrap both your SQL statement in a stored procedure and call that procedure from your application code like

create procedure usp_data
as
begin
SELECT something as q1 From table;
SELECT somethingelse as q2 FROM table where this = this;
end

See How to: Execute a Stored Procedure that Returns Rows for more information

edited Jun 19 '17 at 09:38

answered Jun 19 '17 at 09:35

Rahul

76,197
13
71
125

2

You do not need a stored procedure to issue multiple queries, and it does not change how you interact with it from ADO.NET. I don't think this answer does *anything* to increase understanding of what to do here. – Marc Gravell Jun 19 '17 at 09:36
@MarcGravell, that's right but it's as well not advisable to send large amount of text over the wire by means of adhoc query – Rahul Jun 19 '17 at 09:38
I have to make an sp in the database just to have multiple querys in C#?? what about if I have to make more querys as i write the code, beacuse this is just a small part of what I want to do? Do I need to had all the querys in an sp and then call the sp in the app code? – Hugo Jun 19 '17 at 09:39
@Hugo no you don't; and frankly it is not usually a good idea – Marc Gravell Jun 19 '17 at 09:41
1

@Rahul that might apply if this was hundreds of lines; in the case shown here, however, it will make *exactly zero difference*; that query is going to fit in a single packet regardless of sp vs ad-hoc, and the query-plan-cache is going to cache it identically regardless of sp vs ad-hoc; stored procedures *have uses*, sure - but this **is not one of them** – Marc Gravell Jun 19 '17 at 09:43

Multiple SQL query in C#

2 Answers2

Linked