Populate Datatable - Row with Data from Sql-Command with several selectquery

Question

I have the following function:

private static DataTable GetDataFromDatabase(DateTime myDate)
{
    DataTable table = new DataTable();
    table.Columns.AddRange(new DataColumn[]
    { 
       new DataColumn("Event"), new DataColumn("Room"),
       new DataColumn("From"), new DataColumn("To"), new DataColumn("Persons"),
       new DataColumn("Equipment"), new DataColumn("Catering")
    });

    string strSQL = "SELECT distinct ID FROM VIEW_RAUMBUCHUNG_DISPO " + 
                    "WHERE BOOKSTATUS >= 1 AND convert(char, von,104) = '" + 
                    BookITVbSQL.to_104(myDate) + "'";
    SqlDataReader objRS;
    objRS = SQLrunReaderDP(strSQL);

    while (objRS.Read())
    {
        using (SqlConnection con = new SqlConnection(GetConnectionString()))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                con.Open();
                cmd.CommandText = "SELECT distinct EVENT, ROOM, CONVERT(char(5), from, 108) " + 
                "FROM, CONVERT(char(5), to, 108) TO, PERSONS FROM VIEW_RAUMBUCHUNG_DISPO " + 
                "WHERE ID = '" + objRS["ID"] + "'; " +
                "SELECT EQUIPMENTNAME FROM EQUIPMENT WHERE BUCHUNG_ID = '" + objRS["ID"] + 
                "' and STATUS = '2'; " +
                "SELECT CATERINGNAME FROM CATERING WHERE BUCHUNG_ID = '" + objRS["ID"] + 
                "' and STATUS = '1';";

                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    do
                    {
                        while (rdr.Read())
                        {
                            table.Rows.Add(
                                rdr["EVENT"],
                                rdr["ROOM"],
                                rdr["FROM"],
                                rdr["TO"],
                                rdr["PERSONS"] );


                        }
                    }
                    while (rdr.NextResult());
                    rdr.Close();
                }
                con.Close();
            }
        }
    }
    return table;
}

This works fine for the first Statement. I can get all the Data from the five Columns. But how can i Add the Columns from the other two Querys to the same Row?

If i try:

 while (rdr.Read())
 {
     table.Rows.Add(
             rdr["EVENT"],
             rdr["ROOM"],
             rdr["FROM"],
             rdr["TO"],
             rdr["PERSONS"],
             rdr["EQUIPMENTNAME"]);
  }

so iam getting an IndexOutOfRange - Exception. Can someone help me with that please?

Do you need `CATERINGNAME` column? – Stoleg Jun 14 '13 at 13:30 — Stoleg, Jun 14 '13 at 13:30
Two columns named `To`? I suppose it is a typo. – Steve Jun 14 '13 at 13:43 — Steve, Jun 14 '13 at 13:43

score 0 · Answer 1 · edited May 23 '17 at 10:32

SqlDataReader class:

Provides a way of reading a forward-only stream of rows from a SQL Server database.

You can use this class make a single statement with 2 joins. It will return 7 columns

"SELECT distinct EVENT, ROOM, CONVERT(char(5), from, 108) FROM, CONVERT(char(5), to, 108) TO, PERSONS, EQUIPMENTNAME, CATERINGNAME FROM VIEW_RAUMBUCHUNG_DISPO T1"

+ "INNER JOIN (SELECT EQUIPMENTNAME FROM EQUIPMENT) T2 ON T1.ID = T2.BUCHUNG_ID"

+ "INNER JOIN (SELECT CATERINGNAME FROM CATERING) T3 ON T1.ID = T3.BUCHUNG_ID" +

+ "WHERE T1.ID = '" + objRS["ID"] + "' AND T2.STATUS = '2' AND T3.STATUS = '1' "

UPDATE

Use NextResult method:

Advances the data reader to the next result, when reading the results of batch Transact-SQL statements

If you need to access whole recordset, rather than row at a time you can use SqlDataAdapter (SqlDataAdapter vs SqlDataReader).

Yes i can make a Single Statement, but my question is how can it be done with several querys. Querys where i cannot work with joins — Paks, Jun 14 '13 at 13:35

Populate Datatable - Row with Data from Sql-Command with several selectquery

1 Answers1