0

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.

Improve this question
3
  • 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. Commented Jun 19, 2017 at 9: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. Commented Jun 19, 2017 at 9:41
  • For the record, I do not think that the linked post above (stackoverflow.com/questions/13677318/…) is a duplicate of this; that question is asking about how to run two queries separately; this question is asking how to issue multiple SELECTs in a single query. Totally different thing! The one added by @PaulF looks much better: stackoverflow.com/questions/12715620/… Commented Jun 19, 2017 at 9:46

2 Answers 2

Reset to default
6

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;";
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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...
why do I need the last ";" in the end of the last query?
@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?
Your absalutlly right ;) thanks alot for the explanation
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

Improve this answer

5 Comments

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.
@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
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 no you don't; and frankly it is not usually a good idea
@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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.