CODEWITHSUNDEEP
c#sqldatabaset-sqlvariables
jaspernorth
jaspernorth

Reputation: 415

Returning multiple variables from SQL query

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?

Upvotes: 0

Views: 7520

Answers (3)

xanatos
xanatos

Reputation: 111860

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.

Upvotes: 3

Christian Phillips
Christian Phillips

Reputation: 18759

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().

Upvotes: 1

Andomar
Andomar

Reputation: 238086

declare @col1 int
declare @col2 varchar(42)

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

Upvotes: 1

Related Questions