How do I write a specific sql command in C#?

Question

I am new to C# and somewhat new to sql commands. I know how to write sql commands directly in the database manager but I haven't been able to figure out how to get it to work as a C# SqlCommand object. Here's an example record of my table:

+----+----------+----------+----------+
| ID | UserId   | SiteName | Date     |
+----+----------+----------+----------+
|  1 | 7698479  | Site1    |  NULL    |
|  2 | 7698479  | Site2    |  NULL    |
|  3 | 7698479  | Site3    |  NULL    |
|  4 | 7698479  | Site4    |  NULL    |
+----+----------+----------+----------+

In my application I have a button. When the user clicks the button, I want the entire Date row associated with a specific user id to update to the current date. "extractedId" is actually a variable in my program that stores the user id. So my question is, what is the correct syntax for this sql command in C#, specifically considering that "extractedId" is a variable? Here is one of the variations of code that I have tried.

SqlCommand myCommand = new SqlCommand("UPDATE myTable SET Date = getdate() WHERE UserId = extractedId", myConnection);

See [SqlCommand.ExecuteScalar](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(v=vs.110).aspx) to get started. — Igor, Apr 01 '16 at 17:11
Wouldn't he want to use [SqlCommand.ExecuteNonQuery](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx) instead? ExecuteScalar is more for returning values with SELECT or an id when you insert a row. With ExecuteNonQuery you can just run the SqlCommand provided. — Dresden, Apr 01 '16 at 17:18

devlin carnate · Answer 1 · 2016-04-01T17:21:46.393

3

You need to handle the parameter by adding it to the command:

  var commandText = "UPDATE myTable SET Date = getdate() WHERE UserId = @extractedId";
  SqlCommand myCommand = New SqlCommand(commandText);
  myCommand.CommandType = CommandType.Text;
  myCommand.CommandTimeout = 300;
  myCommand.Connection = New SqlConnection(myConnection);
  myCommand.Parameters.AddWithValue("@extractedId", extractedId)
  try 
  {
      var result = myCommand.ExecuteNonQuery();
  }
  catch(Exception e)
  {
      //do something with exception
  }
  finally
  {
     myCommand.Connection.Close()
  }

edited Apr 01 '16 at 17:21

answered Apr 01 '16 at 17:15

devlin carnate

8,309
7
48
82

You can actually do some error handling if your update fails. I like this example. Some people have had certain issues with catching errors when using ExecuteNonQuery so just be aware of that [Exception not caught](http://stackoverflow.com/questions/27383472/executenonquery-exception-not-caught-by-try-catch) (@ H. NetIrrigate) – Dresden Apr 01 '16 at 17:27

score 1 · Answer 2 · answered Apr 01 '16 at 17:15

Here are a few examples you could use:

For non return use:

Con.Open();
Cmd.CommandText = SQL_command;
Cmd.ExecuteNonQuery();
Con.Close();

For one return value use:

Con.Open();
Cmd.CommandText = SqlCommand;
object Ret = Cmd.ExecuteScalar();
Con.Close();

For multiple return values use:

Con.Open();
Cmd.CommandText = SqlCommand;
SqlDataReader Reader = Cmd.ExecuteReader();
List<object[]> Ret = new List<object[]>();
int Columns = Reader.FieldCount;
while (Reader.Read())
{
    object[] RetItem = new object[Columns];
    for (int i = 0; i < Columns; i++)
    {
        RetItem[i] = Reader[i];
    }
    Ret.Add(RetItem);
}
Reader.Close();
Con.Close();

How do I write a specific sql command in C#?

2 Answers2