在 C# 中执行更新存储过程 [英] Executing an update stored procedure in C#

问题描述

我需要知道，我是否正确编写了存储过程以及执行的 C# 代码是否正确.出于某种原因，返回的错误是c16b"附近的语法不正确.旧错误

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

现在的新错误是:过程或函数sptimeupdate"需要未提供的参数@date".

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

ClientID 列中用于验证和更新的 nvarchar 字符串为 3fc8ffa1-c16b-4d7b-9e55-1e88dfe15277，但粗体部分仅显示在调试测试中 intel sense in error processing

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

 }

}

推荐答案

当您从代码中调用存储过程时，您需要创建一个命令并将其命令类型设置为 StoredProcedure，否则引擎会尝试使用您的命令文本作为它是一个像 SELECT INSERT 等的 sql 文本......但最重要的是你需要在命令的参数集合中传递存储过程所需的参数

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

注意两件事.using 语句是创建连接时要遵循的最佳实践，以确保正确关闭和处理连接，其次，sp 期望的 DateTime 参数应作为 DateTime 而不是字符串传递 - 当然这意味着您应该确定 toolDate 的内容可以转换为 DateTime 值.

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.

这篇关于在 C# 中执行更新存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！