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()

First check to make sure f is equal to 1. Next, you are not doing anything with your command. If you want to execute it use cmd.ExecuteNonQuery() — Stephen Brickner, Dec 08 '15 at 12:04
[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 — marc_s, Dec 08 '15 at 13:00

Vadim Martynov · Answer 1 · 2015-12-08T12:14:15.007

3

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.

edited Dec 08 '15 at 12:14

answered Dec 08 '15 at 12:05

Vadim Martynov

8,602
5
31
43

I have include your commands, But every time it throws an error stating that InvalidOperationalExceptionHandle at cmd.ExecuteNonQuery(); Do i need to include any header and even cmd.ExecuteNonQuery(); is not changing its colour :P – Nithin Veer Reddy Kankanti Dec 08 '15 at 12:23
1

`InvalidOperationalExceptionHandle` ?... do you mean `InvalidOperationException`? And what is the `.Message`? Is it perhaps that the connection isn't open? – Marc Gravell Dec 08 '15 at 12:32
It can be types problem. Try change AddWithValue-strings to ` cmd.Parameters.Add("@IsAuthenticated", SqlDbType.Bit).Value = true; cmd.Parameters.Add("@AuthenticationTime", SqlDbType.DateTime).Value = now;` And write here exception message. – Vadim Martynov Dec 08 '15 at 12:35
Yes that is the message and the db connection is open , so then i am able to get OTP value from it – Nithin Veer Reddy Kankanti Dec 08 '15 at 12:35
@NithinVeerReddyKankanti Look at this article: http://stackoverflow.com/questions/18383076/how-can-i-create-two-commands-in-one-connection – Vadim Martynov Dec 08 '15 at 12:56

score 2 · Answer 2 · answered Dec 08 '15 at 12:06

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 });

SQL update command in c#

2 Answers2