How to pass a SQL command in C# to check record where date is equal to an input date from user

Question

I want to pass a SQL command so that I check for a record in the database which has same date as an input date by the user.

I have a table Rooms with columns

RoomID (PK)
StudentID (FK)
RoomType 
Date 
StartingTime
Duration
FinishingTime

I want to pass the SQL command to check the date a user will input in a dateTimePicker to the values in the Date column. I also want to check if the input roomtype is same as values in the RoomType column, so that if it is the same roomtype and date, then the code checks whether the times are not in conflict.

public bool reserveRoom() //function for reserving a room
{
    bool allow = true;
    con.Open();

    SqlCommand cmd = new SqlCommand("insert into Rooms (StudentID, RoomType, Date, StartingTime, DurationInHour, FinishingTime) values (@studentid, @roomtype, @date, @startingtime, @duration, @finishingtime)", con);

    SqlCommand cmd2 = new SqlCommand("select StudentID from Students where TPEmail = '" + StudentEmail + "'", con);

    SqlDataReader rd = cmd2.ExecuteReader();
    int id = 0;

    while (rd.Read())
    {
        id = rd.GetInt32(0);
    }

    rd.Close();

    MessageBox.Show(Date.Date.ToString());

    SqlCommand cmd3 = new SqlCommand("select StartingTime from Rooms where RoomType = '" + RoomType + "' and Date = '" + Date.Date.ToString("dd/MM/yyyy") + "'", con) ;

    SqlCommand cmd4 = new SqlCommand("select count(*) from Rooms where RoomType = '" + RoomType + "'" + " and Date ='" + Date.Date.ToString("dd/MM/yyyy") + "'", con);

    int count = Int32.Parse(cmd4.ExecuteScalar().ToString());

    SqlDataReader rd2 = cmd3.ExecuteReader();

    String[] startingTime = new string[count];
    //int ident = 0;
    int index = 0;

    while (rd2.Read())
    {
        startingTime[index] = rd2.GetTimeSpan(0).ToString();
        index += 1;
    }

    rd.Close();

    cmd.Parameters.AddWithValue("@studentid", id);
    cmd.Parameters.AddWithValue("@roomtype", RoomType);
    cmd.Parameters.AddWithValue("@date", Date.Date);

    string timeStart = (Time1 + ":" + Time2);
    cmd.Parameters.AddWithValue("@startingtime", TimeSpan.Parse(timeStart));

    double doubleDuration = Int32.Parse(DurationHour) + ((Double.Parse(DurationMinute)) / 60);
    cmd.Parameters.AddWithValue("@duration", doubleDuration);

    int hour = Int32.Parse(Time1) + Int32.Parse(DurationHour);
    int min = Int32.Parse(Time2) + Int32.Parse(DurationMinute);

    if (min >= 60)
    {
        min -= 60;
        hour += 1;
    }

    string timeFinish = (hour + ":" + min);
    cmd.Parameters.AddWithValue("@finishingtime", TimeSpan.Parse(timeFinish));

    int i = cmd.ExecuteNonQuery();

    if (i != 0)
    {
        MessageBox.Show("Reservation successful.");
    }
    else
        MessageBox.Show("Unable to reserve");

    foreach (string item in startingTime)
    {
        TimeSpan timeItem = TimeSpan.Parse(item);

        if (Int32.Parse((timeItem - TimeSpan.Parse(timeFinish)).ToString()) >= 0)
        {
            MessageBox.Show("Accept");
        }
    }

    con.Close();
    return allow;
}

Can someone help me please?

enter image description here

Help please

[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](http://bobby-tables.com/). Your `SqlCommand` should **ALL** be like the first one - properly **parametrized**! — marc_s, Jun 17 '21 at 09:20
[What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) | [SqlCommand Parameters Add vs. AddWithValue](https://stackoverflow.com/questions/21110001/sqlcommand-parameters-add-vs-addwithvalue) — , Jun 17 '21 at 09:20
Also, this `SqlDataReader rd2 = cmd3.ExecuteReader();` really goes against the *Principle of Least Surprise" - if you have `cmd`, `cmd2`, `cmd3` and `cmd4` - name your readers the same! Don't use **rd2** for **cmd3** - you're just **asking** for confusion later on! And of course - it would be **much better** to use a **descriptive** name rather than just `cmd2`, `cmd3` etc ..... — marc_s, Jun 17 '21 at 09:21
Issues with your current code: SQL injection, instead you should use parameters. You are using a cached connection, instead create the connection when you need it. Dispose connection, command and reader objects with `using` blocks. You could run all of this logic in a single SQL batch or procedure, there is no need to query data back to the application only to pass it back again. `ExecuteScalar()` can return an actual `int` if that is what the query is returning, don't mess about with `ToString` and `Parse` just cast it `(int)`. Don't mess about parsing `TimeSpan`, just use addition ...... — Charlieface, Jun 17 '21 at 17:06
..... and subtraction of actual integers. Don't block the thread with `MessageBox` while the connection is open. Don't use `AddWithValue`, specify parameter type and length explicitly. A `datetime` is not a `TimeSpan`. You are always returning `true` as you never set `allow`. — Charlieface, Jun 17 '21 at 17:06

How to pass a SQL command in C# to check record where date is equal to an input date from user

0 Answers0