尝试在 Windows 窗体中使用 SQL 存储过程 [英] Trying to use a SQL stored procedure in Windows Forms

问题描述

以下代码导致我的 Windows 窗体应用出现问题:

The following code is causing problem in my Windows Forms app:

SqlConnection cnnDB = new SqlConnection(<connection string>);
try
{
    SqlCommand cmd = cnnDB.CreateCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "sp_ProcName";
    cmd.Parameters.AddWithValue("@int1", ComboBox1.SelectedValue);
    cmd.Parameters.AddWithValue("@int2", ComboBox2.SelectedValue);
    cmd.Parameters.AddWithValue("@int3", ComboBox3.SelectedValue);
    cmd.Parameters.AddWithValue("@int4", ComboBox4.SelectedValue);
    cmd.Parameters.AddWithValue("@varchar1", TextBox1.Text);
    cmd.Parameters.AddWithValue("@varchar2", TextBox2.Text);
    cmd.Parameters.AddWithValue("@varchar3", ComboBox5.SelectedValue);
    cmd.Parameters.AddWithValue("@varchar4", stringVariable);
    cnnDB.Open();
    cmd.ExecuteNonQuery();
    cnnDB.Close();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

这是我执行它时发生的情况:

This is what happens when I execute it:

1. 如果 stringVariable 是插入"或更新"，则来自 TextBox1 和 TextBox2 的数据不会传递给过程.
2. 如果 stringVariable 是Delete"，我会收到错误消息过程或函数‘sp_ProcName’需要参数‘@varchar3’，但未提供".

1. If stringVariable is either "Insert" or "Update", data from TextBox1 and TextBox2 isn't passed to procedure.
2. If stringVariable is "Delete", I get error "Procedure or function ‘sp_ProcName’ expects parameter ‘@varchar3’, which was not supplied".

代码有问题吗?

根据@Steve 和@MethodMan 的要求，下面是 SQL Server 中的存储过程.

As requested by @Steve and @MethodMan, below is the stored procedure in SQL Server.

ALTER PROCEDURE [dbo].[sp_ProcName]
(
      @int1 int,
      @int2 int,
      @int3 int,
      @int4 int,
      @varchar1 varchar(7),
      @varchar2 varchar(6),
      @varchar3 nvarchar(10),
      @varchar4 nvarchar(10) = ''
)
AS
BEGIN
     SET NOCOUNT ON;
     IF @varchar4 = 'Insert'
     BEGIN
          INSERT INTO Table1(int1,int2,int3,int4,varchar1,varchar2,varchar3)
          VALUES(@int1,@int2,@int3,@int4,@varchar1,@varchar2,@varchar3)
     END
     IF @varchar4 = 'Update'
     BEGIN
          UPDATE Table1
               SET int1=@int1, int2=@int2, int3=@int3, int4=@int4, varchar1=@varchar1, varchar2=@varchar2, varchar3=@varchar3
          WHERE varchar1 = @varchar1
     END
     IF @varchar4 = 'Delete'
     BEGIN
          DELETE FROM Table1
          WHERE varchar1 = @varchar1
     END

好吧，经过仔细检查，我发现存储过程中的更新部分是坏蛋".我需要另辟蹊径.也删除部分.

Well, after a closer examination, I see that the Update part in stored procedure is "bad egg". I'll need to find some other way. Delete part too.

附言抱歉问了很久.

推荐答案

您需要使用 DBNull.Value 而不是 null 或分配 默认值 可能丢失的存储过程参数:

You need to use DBNull.Value instead of null or assign default values to your stored procedure parameters that might be missing:

方法一:

CREATE PROCEDURE [dbo].[problemParam] 
    // Other parameters go here then
    @varchar1 NVARCHAR(50) = null, 
    @varchar1 NVARCHAR(50) = null,
    @varchar1 NVARCHAR(50) = null, 
AS  
BEGIN
  -- Procedure Logic go here
END

方法二:

object param1 = DBNull.Value;
object param2 = DBNull.Value;
object param3 = DBNull.Value;

if(!String.IsNullOrEmpty(TextBox1.Text))
    param1 = TextBox1.Text;

if(!String.IsNullOrEmpty(TextBox2.Text))
    param2 = TextBox2.Text;

if(ComboBox5.SelectedValue != null)
    param3 = TextBox1.Text;

cmd.Parameters.AddWithValue("@varchar1", param1 );
cmd.Parameters.AddWithValue("@varchar2", param2 );
cmd.Parameters.AddWithValue("@varchar3", param3 );

这篇关于尝试在 Windows 窗体中使用 SQL 存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！