通过代码创建存储过程-C＃

debugcn 发表于 Dev

开发人员

我在SQL Server数据库中创建存储过程时遇到问题。这就是我要做的：

public void selectMeetings(String PID)
{
    String connection = "Server=localhost;Database=master;Integrated security=true;";
    SqlConnection Connection = new SqlConnection();

    Connection = new SqlConnection(connection);
    Connection.Open();

    string script = "USE ["+PID+ "] GO CREATE PROCEDURE selectMeetings AS SELECT * FROM dbo.MEETING GO";

    SqlCommand command = new SqlCommand(script, Connection);

    try
    {
       command.ExecuteNonQuery();
    }
    catch (System.Exception ex)
    {
        throw ex;
    }
}

问题是这样的：

'GO'附近的语法不正确。
“ CREATE / ALTER PROCEDURE”必须是查询批处理中的第一条语句

我试图删除GO，但问题是：

“ CREATE / ALTER PROCEDURE”必须是查询批处理中的第一条语句。

如何解决呢？谢谢

Damien_The_Unbeliever

从您的客户端代码切换数据库，并且正如其他人所说的那样，GO因为它不是T-SQL命令，所以不要发送该数据库：

public void selectMeetings(String PID)
{
    String connection = "Server=localhost;Database=master;Integrated security=true;";
    SqlConnection Connection = new SqlConnection();

    Connection = new SqlConnection(connection);
    Connection.Open();

    Connection.ChangeDatabase(PID);

    string script = "CREATE PROCEDURE selectMeetings AS SELECT * FROM dbo.MEETING";
    SqlCommand command = new SqlCommand(script, Connection);

    //You're not fishing. There's no point in catch and release
    command.ExecuteNonQuery();
}

最佳实践的进一步修复：

public void selectMeetings(String PID)
{
    String connection = "Server=localhost;Database=master;Integrated security=true;";
    using(SqlConnection Connection = new SqlConnection(connection))
    {
      Connection.Open();

      Connection.ChangeDatabase(PID);

      string script = "CREATE PROCEDURE selectMeetings AS SELECT * FROM dbo.MEETING";
      using(SqlCommand command = new SqlCommand(script, Connection))
      {
        command.ExecuteNonQuery();
      }
    }
}

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。