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

Wouldn't he want to use SqlCommand.ExecuteNonQuery 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
– Dresden, Commented Apr 1, 2016 at 17:18

devlin carnate · Accepted Answer · 2016-04-01 17:21:46Z

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 1, 2016 at 17:21

answered Apr 1, 2016 at 17:15

devlin carnate

8,6617 gold badges55 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dresden Over a year ago

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 (@ H. NetIrrigate)

Fruchtzwerg · Accepted Answer · 2016-04-01 17:15:43Z

1

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

answered Apr 1, 2016 at 17:15

Fruchtzwerg

11.4k12 gold badges44 silver badges57 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related