SQL update command in c#

Question

I have a SQL Server table with columns like this:

Mobile No  <>  OTP   <> GenTime <> AuthTime <> IsAuth
9632587410 <> 256389 <> ******  <> ******** <> False
9876543210 <> 258963 <> *****   <> ******   <> False

so on ...

using (SqlConnection conn = new SqlConnection())
{
    string inputn = Console.ReadLine();
    long mobileNo;
    long.TryParse(inputn, out mobileNo);

    string inputo = Console.ReadLine();

    int OTP;
    Int32.TryParse(inputo, out OTP);

    DateTime now = DateTime.Now;

    conn.ConnectionString = "Data Source=10.0.0.98;Initial Catalog=TeletextCMS_Dev;User ID=Testteam;Password=Cognigent33#";
    conn.Open();

    //long r = 8947052876;
    SqlCommand command = new SqlCommand("SELECT * FROM CustomerAuthOTP WHERE MobileNum=" + mobileNo, conn);
    int f = 0;

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            //int OTP = reader[1];
            int OTPGen = int.Parse(string.Format("{0}", reader[1]));
            int a = now.Hour;
            int b = now.Minute;
            int e = now.Day;

            DateTime then = DateTime.Parse(string.Format("{0}", reader[2]));

            int c = then.Hour;
            int d = then.Minute;
            int g = then.Day;

            if (e == g)
            {
                int t = (a - c) * 60 + b - d;

                if (OTP == OTPGen && e == g && t <= 15)
                {
                    Console.WriteLine("Hi");
                    f = 1;                           
                }
                else
                {
                    Console.WriteLine("No");
                }
            }

            if (e > g)
            {
                int t = (a + 24 - c) * 60 + b - d;

                if (OTP == OTPGen && e == g && t <= 15)
                {
                    Console.WriteLine("Hi");
                    f = 1;
                }
                else
                {
                    Console.WriteLine("No");
                }
            }
        }
    }

    if(f == 1)
    {
        SqlCommand cmd = new SqlCommand("UPDATE CustomerAuthOTP  SET IsAuthenticated=True, AuthenticationTime=" + now, conn);
        Console.WriteLine("Hi");
    }
}

Now at the bottom I have an Update command. I tried to execute it but it is not doing anything.

There is no error in the code. Kindly some one help me out if f== 1 then in the CustomerAuthOTP table update the IsAuthenticated value to be true and also set the authentication time to now.DateTime()

Answer 1

First of all you should execute your commnd:

SqlCommand cmd = new SqlCommand("UPDATE CustomerAuthOTP  SET IsAuthenticated=True, AuthenticationTime=" + now, conn);
cmd.ExecuteNonQuery();

I recommend to use SqlCommand.Parameters

var commandText = "UPDATE CustomerAuthOTP  SET IsAuthenticated=@IsAuthenticated, AuthenticationTime=@AuthenticationTime";
SqlCommand cmd = new SqlCommand(commandText, conn);
cmd.Parameters.AddWithValue("@IsAuthenticated", true);
cmd.Parameters.AddWithValue("@AuthenticationTime", now);
cmd.ExecuteNonQuery();

It'll help SQL provider to determine parameter types and protects against SQL-injections.

Answer 2

DateTime now = DateTime.Now;
...
SqlCommand cmd = new SqlCommand(
    "UPDATE CustomerAuthOTP  SET IsAuthenticated=True, AuthenticationTime="
    + now, conn);

Note that this will be a string concatenation, and (depending on your locale, etc), the following is not valid TSQL:

UPDATE CustomerAuthOTP  SET IsAuthenticated=True, AuthenticationTime=08/12/2015 12:08:32

The immediate problem is formatting (both the datetime and the boolean are wrong), but it is best fixed by parameterization - you should almost never be concatenating values into TSQL:

SqlCommand cmd = new SqlCommand(
"UPDATE CustomerAuthOTP SET IsAuthenticated=1, AuthenticationTime=@now", conn);
cmd.Parameters.AddWithValue("now", now);
cmd.ExecuteNonQuery();

Or with a tool like "dapper":

conn.Execute("UPDATE CustomerAuthOTP  SET IsAuthenticated=1, AuthenticationTime=@now",
    new { now });