problem on adding or removing a parameter in sp using DAAB, plz help and guide me.

Topics: Data Access Application Block
Feb 12, 2010 at 4:44 AM

Hi,

thanks for your attention and time.

I am using DAAB with stored procedures. A stored procedure is  called by different functions where no. of parameters passed by functions may differ. For example stored procedure p_get_product it returns all products or one product.

Initially when I use it for getting all products in function, I was not required to pass product id so I just passed @Mode (which means when  @Mode=1, I will check with if-else and a special part of code in sp will be executed) but later when I need to pass productID in second function, first part start throwning error that no of paramerters are not same.

It is a problem as we are working in team, when ever someone add a new parameter previous no of parameters do not remain same and consequently there appears errors in previous work.

How this should be managed, please guide and help.

haansi

<input id="gwProxy" type="hidden" /><!--Session data--><input id="jsProxy" onclick="jsCall();" type="hidden" />

Feb 12, 2010 at 6:51 AM

Hi,

Can you please try passing a null for the ProductID parameter. Like the code shown below, I have a stored procedure which has 2 parameters, @Mode and @ProductID, both must accept a value, so if you want to ignore the @ProductID parameter, just pass a null.

 

const int ALL = 1;

using (IDataReader reader = db.ExecuteReader("GetProducts", new object[] { ALL, null }))

Valiant Dudan
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Feb 12, 2010 at 8:39 AM

Hi,

thanks for replaying.

I am doing exectly the same thing but it has a problem. suppose a stored procedure is being called by 4 functions. If in 5th function I add 2 parameters, I have to add parameters in all previous work other wise it shows error. This is the problem that I want to avoid. Please  advice.

thanks

haansi

 

Feb 15, 2010 at 3:40 AM

Hi,

Can you please try the solution of implementing a optional parameter in your stored procedure as described in this blog. http://weblogs.asp.net/stanleygu/archive/2010/02/08/solution-5-implementing-optional-parameters-in-t-sql-stored-procedures.aspx

Valiant Dudan
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

 

Feb 15, 2010 at 4:56 AM

Thanks a lot for your kind attention and support.

Actually I tried this before but I am not getting why it is not working (sure it because of my lack of knowledge and experience).

What I did in stored procedure is set default valus as null for optional parameters. Furthemore I am doing it in last parameter.

in c# code I am doing like:

public int UpdateCorporateAccountConfirmation(CorporateAccount obj)
        {
            int result = 1;
            
                object objresult = db.ExecuteNonQuery("p_corporateaccount_Confirmations", 2,obj.Id,
                obj.StatementBy, obj.AutoEmail, obj.Email, obj.FaxConfirm, obj.DocsChecked, obj.InfoReceived, obj.TraccrApproved,
                obj.Login, obj.Password, UserStatus.Pending, 0);
                result = Int32.Parse(objresult.ToString());
           
            return result;
        }

Here I have not passed a parameter that was set to optional. but it still say no of parameters is not equal to no of values. can you plz advice ?

thanks for your time and support

Feb 15, 2010 at 6:42 AM

Hi,

I think you have to modify the other calling function to explicitly add the parameters that are required and just ignore the optional parameter. So if ever you would again add another parameter in your stored procedure, you wont have to return to the other function and modify them again.

So if you have 2 parameter in your stored procedure @Name, @Age. In your previous function you would have to do this:

            DbCommand cmd = db.GetStoredProcCommand("InsertTest");
            db.AddInParameter(cmd, "@Name", System.Data.DbType.String, "Name");
            db.AddInParameter(cmd, "@Age", System.Data.DbType.Int32, 23);
            db.ExecuteNonQuery(cmd);

Then, if you'll be adding a third parameter in your stored procedure, say @Address which is optional. Then in your new function you would do:

            DbCommand cmd = db.GetStoredProcCommand("InsertTest");
            db.AddInParameter(cmd, "@Name", System.Data.DbType.String, "Name");
            db.AddInParameter(cmd, "@Age", System.Data.DbType.Int32, 23);
            db.AddInParameter(cmd, "@Address", System.Data.DbType.String, "The Address");
            db.ExecuteNonQuery(cmd);

Now, even if you add more and more parameters that are optional, you wouldn't have to worry about your previous function.

Please see if this is an option to you, as of now this is the only workaround that i can think of.

Valiant Dudan
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

 

Feb 15, 2010 at 7:53 AM

Thanks so nice of,

This is really helping.