发送参数到存储过程 [英] Sending paramters to store procedure

问题描述

我在Sql server中有存储过程，它有很多字段，其中一些在参数列表中初始化。那么我需要从asp.net传递这些参数值还是绕过它们？看下面我的代码来了解我的意思。



商店程序：

  ALTER   PROCEDURE  [dbo]。[ADD_SMS_InQueue] 
（
  @ ToMobNo   varchar （ 15 ），
  @ Message   nvarchar （ 2000 ）， 
 
  @ encoding_Type  int = 0，
  @ IsAlertSMS  bit = 0，
 
  @ priority  int = 2，
  @ SendByUser  int = 1，
 
  @ MessageType  tinyint = 0 
 
 
）
  AS  
  BEGIN  TRY 
  BEGIN   TRANSACTION  
  INSERT   INTO  MessageQueue（[QueueDateTime]，Client_ID， ToMobileNo，
 [Message]，LongMessageType_ID，EncodingType_ID，AlertMessage，LocalSMS，Priority_ID，Attempts，TotalAttempts，Status_ID，SendByUser，MessageType）
  values （ GETDATE（）， 1 ， @ ToMobNo ， @ Message ， 3 ， @ encoding_Type ， @ IsAlertSMS ， 1 ， @ priority ， 0 ， 0 ， 1 ， @ SendByUser ， @ MessageType ）
 
 
  COMMIT   TRANSACTION  
  END  TRY 
  BEGIN  CATCH 
  ROLLBACK   TRANSACTION  
 结束 CATCH 

发送电子邮件的C＃代码：

  public   class  MessageQueueBizz 
 {
  public   string  ToMobileNo { get ;  set ; } 
  public   string 消息{ get ;  set ; } 
 
  public  MessageQueueBizz（ string  ToMobileNo，字符串消息）
 {
  this  .ToMobileNo = ToMobileNo; 
  this  .Message = Message; 
} 
} 
  public   class  ManageQueueBizz 
 { 
  public   int  Insert（MessageQueueBizz MessageQueueBizz）
 {
 SqlCommand cmd =  new  SqlCommand（  ADD_SMS_InQueue， DataBaseConnectionSMSDB.OpenConnection（））; 
 cmd.CommandType = CommandType.StoredProcedure; 
 SqlParameter pToMobileNo =  new  SqlParameter（  @ ToMobNo，MessageQueueBizz.ToMobileNo）; 
 cmd.Parameters.Add（pToMobileNo）; 
 SqlParameter pMessage =  new  SqlParameter（  @消息，MessageQueueBizz.Message）; 
 cmd.Parameters.Add（pMessage）; 
  int  result = Convert.ToInt32（cmd.ExecuteNonQuery（））; 
 DataBaseConnectionSMSDB.CloseConnection（）; 
 返回结果; 
} 

解决方案

您的程序有7个参数，其中5个具有默认值。您不必提供参数值 - 尽管您可以使用特定值而不是默认值。



您必须发送的唯一两个参数是 ToMobNo 和消息，它们没有默认值。如果您不提供这些，您将收到错误。



但是，您可以使您的代码更清洁：

< pre lang =c＃> SqlCommand cmd = new SqlCommand（ ADD_SMS_InQueue，DataBaseConnectionSMSDB.OpenConnection（））;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue（ @ ToMobNo，MessageQueueBizz.ToMobileNo）;
cmd.Parameters.AddWithValue（ @ Message，MessageQueueBizz.Message）;
int result = cmd.ExecuteNonQuery（）;

您不必将存储过程中带有默认值的参数添加到参数列表中以供执行。



但是如果你确实在参数列表中添加了一个参数，那么你必须分配一个值。



如果你把这个值保留为C＃ null然后它将抛出异常。



如果要将空值传递给存储过程，则必须使用 DBNull.Value

I have store procedure in Sql server, which has many fields and some of them are initialized in parameters list. So do i need to pass those parameters a value from asp.net or bypass them ? see my code below to get my meaning.

Store Procedure:

ALTER PROCEDURE [dbo].[ADD_SMS_InQueue]
(
@ToMobNo varchar(15),
@Message nvarchar(2000),

@encoding_Type int=0,
@IsAlertSMS bit=0,

@priority int=2,
@SendByUser int=1,

@MessageType tinyint=0


)
AS
BEGIN TRY
    BEGIN TRANSACTION
        INSERT INTO MessageQueue ([QueueDateTime] ,Client_ID,ToMobileNo,
            [Message],LongMessageType_ID,EncodingType_ID,AlertMessage,LocalSMS,Priority_ID,Attempts,TotalAttempts,Status_ID,SendByUser,MessageType)
        values (GETDATE(),1,@ToMobNo,@Message,3,@encoding_Type,@IsAlertSMS,1,@priority,0,0,1,@SendByUser,@MessageType)


    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH

C# code for sending vales:

public class MessageQueueBizz
    {
        public string ToMobileNo { get; set; }
        public string Message { get; set; }

        public MessageQueueBizz(string ToMobileNo, string Message) 
        {
            this.ToMobileNo = ToMobileNo;
            this.Message = Message;
        }
    }
    public class ManageQueueBizz 
    {
        public int Insert(MessageQueueBizz MessageQueueBizz)
        {
            SqlCommand cmd = new SqlCommand("ADD_SMS_InQueue", DataBaseConnectionSMSDB.OpenConnection());
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter pToMobileNo = new SqlParameter("@ToMobNo", MessageQueueBizz.ToMobileNo);
            cmd.Parameters.Add(pToMobileNo);
            SqlParameter pMessage = new SqlParameter("@Message", MessageQueueBizz.Message);
            cmd.Parameters.Add(pMessage);
            int result = Convert.ToInt32(cmd.ExecuteNonQuery());
            DataBaseConnectionSMSDB.CloseConnection();
            return result;
        }

解决方案

Your procedure has 7 parameters, of which 5 have default values. You do not have to provide parameter values foe those - although you can if you want a specific value instead of the default.

The only two parameters you must send are ToMobNo and Message which do not have default values. If you do not supply these, you will get an error.

However, you can probably make your code a lot cleaner:

SqlCommand cmd = new SqlCommand("ADD_SMS_InQueue", DataBaseConnectionSMSDB.OpenConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ToMobNo", MessageQueueBizz.ToMobileNo);
cmd.Parameters.AddWithValue("@Message", MessageQueueBizz.Message);
int result = cmd.ExecuteNonQuery();

You do not have to add parameters with default values in stored procedures to the parameter list for execution.

But if you do add a parameter to the parameters list then you must assign a value.

If you leave the value as a C# null then it will throw an exception.

If you want to pass a null value to the stored procedure you have to use DBNull.Value

这篇关于发送参数到存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！