• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 725
  • Last Modified:

Dynamically Create SqlParameters collection


I am attempting to update my database through a stored procedure.  The stored procedure has several parameters.  I am populating the SqlParamters array using the following:

SqlParameter[] parameters =
            data.MakeInParam("@usrid", SqlDbType.VarChar, 55, logonid),
            data.MakeInParam("@title", SqlDbType.VarChar, 50, curVals[0].ToString()),
            data.MakeInParam("@bspy", SqlDbType.Int, 4, curVals[1].ToString()),
            data.MakeInParam("@ecbpy", SqlDbType.VarChar, 1, curVals[2].ToString()),
            data.MakeInParam("@cbpy", SqlDbType.Int, 4, curVals[3].ToString()),
            data.MakeInParam("@bs", SqlDbType.Int, 4, curVals[4].ToString()),
            data.MakeInParam("@ecb", SqlDbType.VarChar, 1, curVals[5].ToString()),
            data.MakeInParam("@eb", SqlDbType.Int, 4, curVals[6].ToString()),
            data.MakeInParam("@perg", SqlDbType.Decimal, 4, curVals[7].ToString()),
            data.MakeInParam("@msev", SqlDbType.Int, 4, curVals[8].ToString()),

My issue is, that when any of my text boxes which contain Integer values are left blank (so the Integer should be Null in the database) I receive an error message.

I updated my stored procedure to set the integers to null as follows:

CREATE PROCEDURE [dbo].[sp_insert_cash_component] @usrid varchar(50), @title varchar(50), @bspy int = Null, @ecbpy varchar(1), @cbpy int = Null, @bs int = Null, @ecb varchar(1), @eb int = Null , @perg decimal(18,4) = Null, @msev int = Null AS

I would now like to dynamically create the above Parameter Array depending on the value in my textbox.  For example, if curVals[1].ToString() is "", then do not add @bspy to the parameter array.

Is there a way to dynamically declare the SqlParameter array (without providing a size) and then adding parameters to it as necessary?


  • 2
1 Solution
drop it and recreate it
If you want to get this done without too many changes, do this:

ArrayList colList = new ArrayList(); //namespace to include is System.Collection

//now add any number of params based on any condition
if(curVals[0].ToString() != "")  
    ArrayList.Add(data.MakeInParam("@title", SqlDbType.VarChar, 50, curVals[0].ToString()));
//now extract an array from the arraylist
SqlParameter[] parameters = (SqlParameter[])(colList.ToArray(typeof(SqlParameter)));

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now