Check if column value in SQL Server table is null in ASP.NET using C#

Question

I am working in ASP.NET using C# and I am struggling to finish the piece of code.

A dropdownlist is displayed on my page on which the user can give a rating for a book on the page.

First, I want to check if the user already gave a rating or not (if the rating column has a value).

If it doesn't have a value, the user can then do the rating by selecting from the dropwdownlist.

Here is my code so far. I am unsure what to write within the if()

// CRUD statement 
SqlCommand cmdCheck = ratingconn.CreateCommand();
cmdCheck.CommandText = "SELECT bookRating FROM tbl_ratingInfo WHERE userID = '" + Session["userID"] + "'";

if()
{
    // reading the information from the database
    SqlDataReader reader = cmdCheck.ExecuteReader();

    if (reader.Read())
    {
        // setting the label text values
        ddl_BookName.Text = reader.GetInt32(0).ToString();
    }
}
else
{
    // creating CRUD statement
    SqlCommand cmdRating = ratingconn.CreateCommand();
    cmdRating.CommandText = "INSERT INTO tbl_ratingInfo (bookRating) VALUES('"
                    + ddl_Rating.Text + "') WHERE userID = " + Session["userID"] + "' ";
}

Here is my database. This table is an intersection table in SQL code.

-- Create the rating info table
CREATE TABLE tbl_ratingInfo
(
    -- Add Foreign Keys from members and class tables
    bookTitle VARCHAR (100) NOT NULL REFERENCES tbl_bookInfo(bookTitle),
    userID INT NOT NULL REFERENCES tbl_userInfo(userID),
    bookRating INT NOT NULL DEFAULT 5 CHECK (bookRating <= 5),
    -- Composite Primary Key
    PRIMARY KEY (bookTitle, userID)
)
GO

Please do not use string concatination to create an SQL command. Learn and use parametrized statements. If you don't, you leave yourself open to Sql Injection attacks. — gunr2171, Jul 21 '21 at 00:00
`userID = " + Session["userID"] + "' ";` That is a clear typo. But **do not** fix the typo. Read https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection instead. — mjwills, Jul 21 '21 at 00:41
See also https://bobby-tables.com. Also, these two commands could be done in one batch, something like `SELECT bookRating from tbl_ratingInfo WHERE userID = @userId; IF (@@ROWCOUNT = 0) INSERT ... `. Also, you should dispose connection, command and reader objects with `using` blocks — Charlieface, Jul 21 '21 at 00:56
What sql injections you are talking about? From the session object? You will put sql injection anywhere soon. Half of SO posts about sql injections. — Serge, Jul 21 '21 at 01:37
asp.net? But you are immediately inserting a rating? Before the user has submitted a form or triggered an ajax request? — Jeremy Lakeman, Jul 21 '21 at 05:29

score 0 · Accepted Answer · answered Jul 21 '21 at 05:34

Inline query is not good practice. Please use stored procedure instead.

You can use SqlDataReader's HasRows in your if condition:

SqlCommand cmdCheck = ratingconn.CreateCommand();
            cmdCheck.CommandText = "SELECT bookRating FROM tbl_ratingInfo WHERE userID = '" + Session['userID'] + "'";

            //reading the information from the database
            SqlDataReader reader = cmdCheck.ExecuteReader();

            if (reader.HasRows) // true if the SqlDataReader contains one or more rows otherwise false.
            {
                if (reader.Read())
                {
                    // setting the label text values
                    ddl_BookName.Text = reader.GetInt32(0).ToString();
                }
            }
            else
            {
                // creating CRUD statement
                SqlCommand cmdRating = ratingconn.CreateCommand();
                cmdRating.CommandText = "INSERT INTO tbl_ratingInfo (bookRating) VALUES('"
                                + ddl_Rating.Text + "') WHERE userID = " + Session["userID"] + "' ";
            }

Check if column value in SQL Server table is null in ASP.NET using C#

1 Answers1