How to code to get data from multiple tables using C# and SQL Server

Question

I have this code which returns the correct data from one table. But I have related data in other tables using INNER JOIN. So my question is how to code to return this in the result?

IList<Schedule> GetCurrentValues()
{
    var result = new List<Schedule>();

    using (var sqlConnection = new SqlConnection(_configuration["DefaultConnection"]))
    {
        sqlConnection.Open();

        using (var command = sqlConnection.CreateCommand())
        {
            command.CommandText = "SELECT Schedules.AppointmentHeading, Schedules.AppointmentDateStart, Schedules.AppointmentDateEnd, Bookers.Email, Rooms.Id AS Expr3, Rooms.Name " +
                                  "FROM Schedules " +
                                  "INNER JOIN Rooms ON Schedules.RoomId = Rooms.Id " +
                                  "INNER JOIN Bookers ON Schedules.BookerId = Bookers.Id";

            command.CommandType = CommandType.Text;

            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        // How should I code so I also get Bookers.Email and Room.Name?
                    }
                }
            }
        }
    }

    return result;
}

Your code is returning columns from three tables, so if the code is correct (as you say it is), then it is doing what you want. — Gordon Linoff
– Gordon Linoff, Commented Feb 29, 2020 at 13:00

Viresh Mathad · Accepted Answer · 2020-02-29 13:16:22Z

Since you need a mixed data from different tables, it is recommended to either create new Model which will consist of the properties you actually care like

AppointmentHeading, AppointmentDateStart, AppointmentDateEnd, BookingEmail, RoomName

or just add BookingEmail & RoomName to your existing Schedule Model.

using (var sqlConnection = new SqlConnection(_configuration["DefaultConnection"]))
    {
        sqlConnection.Open();

        using (var command = sqlConnection.CreateCommand())
        {
            command.CommandText = "SELECT Schedules.AppointmentHeading, Schedules.AppointmentDateStart, Schedules.AppointmentDateEnd, Bookers.Email as BookingEmail, Rooms.Id AS Expr3, Rooms.Name as RoomName" +
                                  "FROM Schedules " +
                                  "INNER JOIN Rooms ON Schedules.RoomId = Rooms.Id " +
                                  "INNER JOIN Bookers ON Schedules.BookerId = Bookers.Id";

            command.CommandType = CommandType.Text;

            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                          result.AppointmentHeading = Convert.ToString(reader["AppointmentHeading"]);
                          result.AppointmentStart = Convert.ToDateTime(reader["AppointmentDateStart"]);
                          result.AppointmentEnd = Convert.ToDateTime(reader["AppointmentDateEnd"]);
                          result.BookingEmail = COnvert.ToString(reader["BookingEmail"]);
                          result.RoomName = Convert.ToString(reader["RoomName"]);

                    }
                }
            }
        }
    }

Thank you @Viresh. I wanted to have those tables separated but as it seems to work to have the extra columns in the exisiting Schedule model, I'll go with that for now.

marc_s · Accepted Answer · 2020-02-29 12:59:38Z

2

Just reference the column names in the SqlDataReader - it returns a "flat" set of all the columns selected, and you can access these via the column name (or the column alias AS .... if one is given). Then store those values as needed, e.g. in a separate class or whatever works for you:

....
using (SqlDataReader reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        // read the individual values and store them as needed
        string appointmentHeading = reader["AppointmentHeading"];
        DateTime appointmentStart = Convert.ToDateTime(reader["AppointmentDateStart"]);
        DateTime appointmentEnd = Convert.ToDateTime(reader["AppointmentDateEnd"]);
        string email = reader["Email"];
        string roomId = reader["Expr3"];
        string roomName = reader["Name"];

        // possibly create a custom class here to hold these values
        // and store the multiple possible rows returned into a list of those classes 
        // - or do whatever you need to do with these values here.....
    }
}

answered Feb 29, 2020 at 12:59

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

2 Comments

Nicolas Lewentorp Over a year ago

Great! It seems to work if I add .ToString() at the end of the reader["x"]

Nicolas Lewentorp Over a year ago

I can still not get this new data to be updated on my webpage. the return-statement can only return the data for the IList<Schedule>. I get the 'Cannot implicitly convert type...' error.

Amit Singh Rawat · Accepted Answer · 2020-02-29 12:56:16Z

0

Create a new class containing all the columns of select statement.

answered Feb 29, 2020 at 12:56

Amit Singh Rawat

12 bronze badges

2 Comments

Hans Keﬆing Over a year ago

That doesn't answer the question about how to get from the reader to that class

Md Farid Uddin Kiron Over a year ago

This should be an comment not an answer!

Collectives™ on Stack Overflow

How to code to get data from multiple tables using C# and SQL Server

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related