执行顺序或 ExecuteScalar 问题

Question

首先，我将一个新成员插入成员表中。然后我查询表以获取会员 ID。我将数据存入表中，但它出现的速度不够快，无法在以下几行中执行查询。

我收到此异常“ExecuteScalar 需要一个打开且可用的连接。连接的当前状态已关闭。”我不明白这里出了什么问题。

 //This code works fine
 //Insert new members data
 InsertMembers insert = new InsertMembers();
 int age = Int32.Parse(txtAge.Text);
 insert.InsertNewMember(txtEmail.Text, Myguid, txtName.Text, txtCity.Text, txtState.Text, txtDescription.Text, age, gender);

 //This is the block thats failing
 //Get Member Id to Insert into Pictures table
 GetMemberInfo GetID = new GetMemberInfo();
 int UMemberId = GetID.GetMemberId(Myguid);
 Displayme.Text = UMemberId.ToString();



 public int GetMemberID(string guid)
   {
       string strConectionString = ConfigurationManager.AppSettings["DataBaseConnection"];
       string StrSql = "SELECT MemberID FROM MEMBERS WHERE (Guid = @GuidID)";

       int memberId;
       using (var connection = new SqlConnection(strConectionString))
       using (var command = new SqlCommand(StrSql, connection))
       {
           command.Parameters.Add("@GuidID", SqlDbType.VarChar).Value = guid; 
           memberId = (int)command.ExecuteScalar();
       }
       //returns 0 when it should be member id number
       return memberId; 

   }

Answer 1

在执行命令之前，您应该调用connection.Open()：

public int GetMemberID(string guid)
{
    string strConectionString = ConfigurationManager.AppSettings["DataBaseConnection"];
    string StrSql = "SELECT MemberID FROM MEMBERS WHERE (Guid = @GuidID)";

    int memberId;
    using (var connection = new SqlConnection(strConectionString))
    {
        connection.Open();
        using (var command = new SqlCommand(StrSql, connection))
        {
            command.Parameters.Add("@GuidID", SqlDbType.VarChar).Value = guid; 
            memberId = (int)command.ExecuteScalar();
        }
    }

    //returns 0 when it should be member id number
    return memberId; 
}

Answer 2

请仔细阅读错误消息。它与 ExecuteScalar 太快无关，也与操作顺序无关，除非有一个特定的操作缺失。 您尚未打开连接。

在之前的using块范围内添加connection.Open(); ExecuteScalar 调用，您应该会体验到不同的结果。

Answer 3

将这些代码行替换

  using (var connection = new SqlConnection(strConectionString))
       using (var command = new SqlCommand(StrSql, connection))
       {
           command.Parameters.Add("@GuidID", SqlDbType.VarChar).Value = guid; 
           memberId = (int)command.ExecuteScalar();
       }

为这些

   using (SqlConnection connection = new SqlConnection(
               strConectionString))
    {
        SqlCommand command = new SqlCommand(StrSql, connection);
         command.Parameters.Add("@GuidID", SqlDbType.VarChar).Value = guid;
        command.Connection.Open();
        memberId = (int)command.ExecuteScalar();
    }

using 语句用于自动处理连接，当您已经在 SqlConnection 上应用它时，我认为这里不需要应用 using with sql 命令。并且您错过了在执行命令之前打开连接。