Returning multiple variables from SQL query

Question

My table structure is as follows:

Session
--------------
SessionID (PK)
RoomID     
SessionDate
SessionTimeStart
SessionTimeEnd

I have a following query which will always return one row and display in DGV. I use DataAdapter for connection:

        DataTable queryResult = new DataTable();
        string ConnStr = "Data Source=DUZY;Initial Catalog=AutoRegSQL;Integrated Security=True";

        SqlConnection MyConn = new SqlConnection(ConnStr);
        MyConn.Open();

        //SQL query that returns todays sessions for the given roomID
        string query = @"SELECT SessionID, RoomID, SessionDate, SessionTimeStart, SessionTimeEnd" +
           " FROM [Session] " +
           " WHERE RoomID = @RoomID " +
           " AND SessionDate = cast(getdate() as date) ";

        SqlCommand command = new SqlCommand(query, MyConn);

        command.Parameters.Add("RoomID", SqlDbType.Char).Value = RoomID;


        SqlDataAdapter adapter = new SqlDataAdapter(command);

        adapter.Fill(queryResult);

I would like to save the query result into multiple strings representing table columns, i.e.

SessionIDstring = query result for SessionID column
RoomIDstring = query result for RoomID column
and so on...

Is it possible to achieve it using one query, or do I have to create 5 queries for each column?

When you say you want the query result into strings, are we talking c# variables? In that case, you could just use a simple DataReader to parse your query and .GetString() into each of the variables. — Dan
– Dan, Commented Aug 15, 2013 at 13:53
Yes, I'm after C# answer. Could you give an example of DataReader and parsing refering to the data in my question? — jaspernorth
– jaspernorth, Commented Aug 15, 2013 at 13:55
Check out @xanatos answer below. This does exactly what you want. — Dan
– Dan, Commented Aug 15, 2013 at 14:00

Andomar · Accepted Answer · 2013-08-15 14:32:00Z

3

Something similar to this, perhaps, using ADO.NET?

    //SQL query that returns todays sessions for the given roomID
string query = @"SELECT SessionID, RoomID, SessionDate, SessionTimeStart, SessionTimeEnd" +
                " FROM [Session] " +
                " WHERE RoomID = @RoomID " +
                " AND SessionDate = cast(getdate() as date) ";

using (var connection = new SqlConnection(ConnStr))
using (var command = new SqlCommand(query, connection))
{
    command.Parameters.Add("RoomID", SqlDbType.Char).Value = RoomID;

    try
    {
        connection.Open();

        using (SqlDataReader reader = command.ExecuteReader())
        {
            if (reader.Read())
            {
                // Note that reader[x] has the equivalent type to the type
                // of the returned column, converted using
                // http://msdn.microsoft.com/en-us/library/cc716729.aspx
                // .ToString() if the item isn't null is always ok
                string SessionIDstring = reader[0].ToString(); // it should be an int

                // reading it twice is ok
                int RoomID = (int)reader[1]; // it should be an int
                string RoomIDstring = reader[1].ToString(); // it should be an int

                if (reader.Read())
                {
                    throw new Exception("Too many rows");
                }
            }
            else 
            {
                throw new Exception("No rows");
            }
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

This code was adapted from MSDN ADO.NET Code Examples. I added some usings and made it single row. I don't even want to know why MSDN examples don't go the full length with using.

Note that SqlDataAdapter are built to recover multiple rows/big data and put them in a DataSet. You can use them for single row data, but it's much easier to simply use a SqlDataReader if you only want to fill some variables.

edited Aug 15, 2013 at 14:32

Andomar

239k55 gold badges387 silver badges412 bronze badges

answered Aug 15, 2013 at 13:55

xanatos

112k13 gold badges209 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jaspernorth Over a year ago

This is it! Thank you @xanatos. Your nothe that reader[x] refers to the columns, helped me a lot!

xanatos Over a year ago

@jaspernorth Updated the code to include your parameterized query. +1 because you are using parameters.

Andomar · Accepted Answer · 2013-08-15 13:50:42Z

1

declare @col1 int
declare @col2 varchar(42)

select  @col1 = col1
,       @col2 = col2
,       ....

answered Aug 15, 2013 at 13:50

Andomar

239k55 gold badges387 silver badges412 bronze badges

Comments

Christian Phillips · Accepted Answer · 2013-08-15 14:17:24Z

You could create a class like so...

public class SessionDto
{
    public string SessionID {get; set;}  
    public string RoomID {get; set;}     
    public string SessionDate {get; set;} 
    public string SessionTimeStart {get; set;} 
    public string SessionTimeEnd {get; set;} 
}

And then have a method that takes a Room ID and builds your session object

public SessionDto GetSessionData(int roomId)
{
    using (var cnn = new SqlConnection(ConnStr))
    {
        SessionDto sessionDto;

        string query = @"SELECT SessionID, RoomID, SessionDate, SessionTimeStart, SessionTimeEnd" +
       " FROM [Session] " +
       " WHERE RoomID = @RoomID " +
       " AND SessionDate = cast(getdate() as date) ";


        cnn.Open();
        using (var cmd = new SqlCommand(query,cnn))
        {
            cmd.Parameters.Add("@RoomID", SqlDbType.Char).Value = roomId;

            using (var rdr = cmd.ExecuteReader())
            {
                if (rdr.HasRows)
                {
                    while (rdr.Read())
                    {
                        sessionDto = new sessionDto{
                            SessionID = rdr.GetString(0),
                            RoomID = rdr.GetString(1),
                            SessionDate = rdr.GetString(2),
                            SessionTimeStart = rdr.GetString(3),
                            SessionTimeEnd = rdr.GetString(4)
                        };
                    }
                }
            }
        }
    }
    return sessionDto;
}

A lot of this is hand typed as I havent got access to VS right now, but you should get it to work.

Also, I have used rdr.GetString(), there are other methods for GetType().

@jaspernorth, with this you can return an object, and use the objects properties instead of using strings.

Collectives™ on Stack Overflow

Returning multiple variables from SQL query

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related