Executing an update stored procedure in C#

Question

I need to know, if I am writing the stored procedure correctly and If the C# code for executing is correct. for some reason the error being returned as is Incorrect syntax near 'c16b'. Old Error

The new error now is: Procedure or function 'sptimeupdate' expects parameter '@date', which was not supplied.

the nvarchar string for validating and updating in the column by ClientID is 3fc8ffa1-c16b-4d7b-9e55-1e88dfe15277, but the part in bold is only showing in the debug test intel sense in error handling

ALTER PROCEDURE sptimeupdate
    @id nvarchar(50),
    @date datetime
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE ClientTable
    SET Today_Date=(@date)
    WHERE ClientID=(@id)
END

//--------------above stored procedure--------------------------------
//--------------Executing the stored procedure in C#

 IEnumerable<XElement> searchClientID =
 from clientid in main.XPathSelectElements("Network/ClientID")
 where (string)clientid.Attribute("id") == IntializedPorts[i].ToString()
 select clientid;

foreach (string clientid in searchClientID)
  {
     for (int up = 0; up < IntializedPorts.Count(); up++)
     {
          //Update the current time in the clientid tble.
          //Renames the table copy for groups
      try
       {
         string[] Clientid; //client id array
         Clientid = new string[IntializedPorts.Count()]; //Intialization of the array
         Clientid[up] = clientid.ToString();
         DateTime td = Convert.ToDateTime(toolDate.Text); //Just added a datetime object withdate
 SqlConnection sqlConnectionCmdString = new SqlConnection(@"Data=.\SQLEXPRESS;AttachDbFilename=C:\Users\Shawn\Documents\Visual Studio 2010\Projects\Server\database\ClientRegit.mdf;Integrated Security=True;User Instance=True");

   //EXECUTE THE STORED PROCEDURE sptimedate
   // string UpdateCommand = "sptimeupdate" + Clientid[up].ToString() + toolDate.Text;
   string UpdateCommand = "sptimeupdate" + "'" + Clientid[up].ToString() + "'" + "'" +td.ToString()+ "'"; //this is the new UpdateCommand string as to pass parameters to stored procedure 
    SqlCommand sqlRenameCommand = new SqlCommand(UpdateCommand, sqlConnectionCmdString);

    sqlConnectionCmdString.Open();
    sqlRenameCommand.ExecuteNonQuery();
    sqlConnectionCmdString.Close();
  }
  catch(DataException ex)
  {                                                                                                 MessageBox.Show("Failed to UpdateCurrentTime","DataError",MessageBoxButtons.OK,MessageBoxIcon.Error);
  }

 }

}

Answer 1

When you call a stored procedure from code you need to create a command with its command type set to StoredProcedure, otherwise the engine tries to use your command text as it was an sql text like SELECT INSERT etc... But the mose important thing is that you need to pass the parameters required by the stored procedure in the Parameters collection of the command

So this could be the code to replace the actual one

string UpdateCommand = "sptimeupdate";
using(SqlConnection sqlConnectionCmdString = new SqlConnection(......))
using(SqlCommand sqlRenameCommand = new SqlCommand(UpdateCommand, sqlConnectionCmdString))
{
    DateTime td = Convert.ToDateTime(toolDate.Text);
    sqlRenameCommand.CommandType = CommandType.StoredProcedure;    
    sqlRenameCommand.Parameters.Add("@id", SqlDbType.NVarChar).Value = Clientid[up].ToString();
    sqlRenameCommand.Parameters.Add("@date", SqlDbType.DateTime).Value = td;
    sqlConnectionCmdString.Open();
    sqlRenameCommand.ExecuteNonQuery();
}

Notice two things. The using statement is the best practice to follow when you create a connection to ensure the correct closing and disposing of the connection, second, the parameter for the DateTime expected by the sp should be passed as a DateTime not as a string- Of course this means that you should be certain that the content of toolDate is convertible to a DateTime value.

Answer 2

Your error is originating from this line of code:

string UpdateCommand = "sptimeupdate" + Clientid[up].ToString() + toolDate.Text;

There you are just concatenating the Clientid[up].ToString() as a string into the other string, same with the toolDate.Text, both without and sql markup.
Your resulting SQL query would look like this (assuming toolDate.Text is '2014-10-23'):

sptimeupdate3fc8ffa1-c16b-4d7b-9e55-1e88dfe152772014-10-23

which as you can see is not a proper SQL command.

You should always use parametrized command statements when calling simple SQL commands.
However in your case, you are actually calling a stored procedure.

So change your code to handle it like a stored procedure, example below.

// Create the connection object once
using (SqlConnection sqlConnectionCmdString = new SqlConnection(@"Data=.\SQLEXPRESS;AttachDbFilename=C:\Users\Shawn\Documents\Visual Studio 2010\Projects\Server\database\ClientRegit.mdf;Integrated Security=True;User Instance=True"))
{
  // Same with the SqlCommand object and adding the parameters once also
  SqlCommand sqlRenameCommand = new SqlCommand("sptimeupdate", sqlConnectionCmdString);
  sqlRenameCommand.CommandType = CommandType.StoredProcedure;
  sqlRenameCommand.Parameters.Add("@id", SqlDbType.NVarChar);
  sqlRenameCommand.Parameters.Add("@datetime", SqlDbType.DateTime);

  // Open the connection once only
  sqlConnectionCmdString.Open();

  foreach (string clientid in searchClientID)
  {
    for (int up = 0; up < IntializedPorts.Count; up++)
    {
      try
      {
        // The below three lines seem redundant.
        // Clientid[up] will be equal to clientid after it all, so just use clientid
        //string[] Clientid;
        //Clientid = new string[IntializedPorts.Count];
        //Clientid[up] = clientid.ToString();

        sqlRenameCommand.Parameters["@id"].Value = clientid;
        sqlRenameCommand.Parameters["@datetime"].Value = toolDate.Text;
        sqlRenameCommand.ExecuteNonQuery();
      }
      // Might want to move this try..catch outside the two loops,
      // otherwise you will get this message each time an error happens
      // which might be alot, depending on the side of searchClientID
      catch (SqlException)
      {
        MessageBox.Show("Failed to UpdateCurrentTime", "DataError", MessageBoxButtons.OK, MessageBoxIcon.Error);
      }
    }
  }
}

NOTE: Please read the comments inside the code above for additional advice and suggestions. Recreating a SqlConnection and SqlCommand for each iteration will have a performance impact on your application. So rather create them once and reuse them until you are done.

Further reading can be done here:

• SqlCommand (There are nice examples at the bottom of that page)
• dotnetperls version of SqlCommand

P.S. your sql procedure's code looks fine, you could remove the SET NOTCOUNT ON since that does not really do much in this scenario