为什么在c#中调用存储过程会在SSMS中正常运行时给出InvalidOperationException ?

本文关键字：运行时 InvalidOperationException SSMS 调用存储过程为什么 | 更新日期: 2023-09-27 17:53:53

我有以下方法从c#应用程序调用SP:

public bool CheckEmail(string email)
    {
        bool success = false;
        string name;

            using (SqlConnection con = new SqlConnection(Internal_Audit_Capture.Properties.Settings.Default.Intenal_AuditDB))
            {
                con.Open(); 
                try
                {
                    using (SqlCommand command = new SqlCommand("IntAudit.p_checkEmail", con))
                    {

                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.Add(new SqlParameter("@email", maskedTextBox1.Text.Trim()));
                        command.Parameters.Add("@success", SqlDbType.Bit).Direction = ParameterDirection.Output;
                        command.Parameters.Add("@name", SqlDbType.VarChar).Direction = ParameterDirection.Output;
                        command.ExecuteNonQuery();
                        success = (bool)command.Parameters["@success"].Value;
                        name = (string)command.Parameters["@name"].Value;
                    }
                    return success;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                    return success;
                }
            }
    }

p_checkEmail查询下表:

[IntAudit].[LOGINDETAILS](
[ID] [int] IDENTITY(1,1) NOT NULL,
[NAME] [varchar](30) NOT NULL,
[PASSWORD] [nvarchar](30) NOT NULL,
[ADMIN] [bit] NULL,
[EMAIL] [varchar](50) NOT NULL,PRIMARY KEY CLUSTERED ([ID] ASC))

它的代码是:

create procedure IntAudit.p_checkEmail
@email varchar(50),
@success bit OUTPUT,
@name varchar(30) OUTPUT
as
set nocount on;
if(exists(select 1 from [IntAudit].[LOGINDETAILS] where [EMAIL] = @email))
 begin
  select @name = [NAME] from [IntAudit].[LOGINDETAILS] where [EMAIL] = @email
  set @success = 1
  return
 end
else
 begin
  set @name = null
  set @success = 0
  return
 end
go

运行checkEmail方法给出了一个系统。InvalidOperationException: String[2]: size属性大小无效，错误值为0。不过，在SSMS中执行该过程运行良好。该方法用于检查表中的电子邮件地址，如果用户输入的地址存在，则返回用户名。我试过改变数据类型，但我仍然得到同样的错误。我还缺什么吗?

EDIT: Exception Details:

系统。InvalidOperationException: String[2]: Size属性的大小为无效的0.

在System.Data.SqlClient.SqlParameter

。Validate(Int32 index, Boolean isCommandProc)