Sending paramters to store procedure

Question

0.00/5 (No votes)

See more:

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:

SQL

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:

C#

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;
        }

Posted 20-May-14 0:02am

Hunain Hafeez

Add a Solution

Comments

Kornfeld Eliyahu Peter 20-May-14 6:05am

Nice code, but what is your question?

Thanks7872 20-May-14 6:10am

He already stated that see the code to get the meaning....:laugh:

DamithSL 20-May-14 6:17am

haven't you run and test yet? :-)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2014-05-20T00:10:00

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:

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();

Stephen Hewison · Answer 2 · 2014-05-20T00:11:00

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

Sending paramters to store procedure

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0