CODEWITHSUNDEEP
c#sql-server-2005stored-proceduresc#-2.0
RKh
RKh

Reputation: 14161

Passing numeric parameters to stored procedure in appropriate and easy way

I have a stored procedure in SQL Server 2005 which accepts few parameters of type BigInt and Number(18,2).

There is a column ApprovedAmount in the database table of type: Number(18,2).

While calling this stored procedure from C# (VS-2005), I am using code like:

SQLCommand cmd = new SQLCommand(Query, CN)

cmd.Parameters.AddWithValue("ApprovedAmount",txtApprovedAmount.Text);

The above parameter line gives error: Cannot convert from String to Numeric..

I find it painful to write the above line as below because there are so many parameters to pass:

cmd.Parameters.AddWithValue("ApprovedAmount", Convert.ToDouble(txtApprovedAmount.Text));

Is there any easy way to pass parameters so that they are automatically converted to the appropriate database table type in either Stored Procedure itself or with C# code?

I also want to handle a condition that if the TextBox was left blank, the parameter value should be passed as null.

Upvotes: 1

Views: 3809

Answers (4)

Binary Worrier
Binary Worrier

Reputation: 51711

You need to tell the parameter what type it is somewhere, you could write a generic method, but you'll need to specify the type for each call, it's not a huge improvement on what you have

private static void AddParameter<T>(SqlCommand cmd, string paramName, string value) where T : IConvertible
{
    cmd.Parameters.AddWithValue(paramName, Convert.ChangeType(value, typeof(T)));
}

...

AddParameter<double>(cmd, "ApprovedAmount", txtApprovedAmount.Text);

Alternatively, you could subclass TextBox so it has a type, or create new user controls with a Type property e.g. Subclassed

public class TypedText<T>
    : TextBox 
    where T : IConvertible
{
    public object Value
    {
        get { return Convert.ChangeType(Text, typeof(T)); }
    }
}

Use these instead of standard TextBoxes, then your add parameter code becomes

cmd.Parameters.AddWithValue("ApprovedAmount", txtApprovedAmount.Value);

Actually, you could knock yourself out and have an ISPParameter interface that your typed controls implement, this then covers you for booleans from check boxes, dates from datepickers etc.

public interface ISPParameter
{
    string Name { get; }
    object Value { get; }
}
public class TypedText<T>
    : TextBox, ISPParameter
    where T : IConvertible
{
    private string parameterName;
    public TypedText(string parameterName)
    {
        this.parameterName = parameterName;
    }

    public object TypedValue
    {
        get { return Convert.ChangeType(Text, typeof(T)); }
    }

    string ISPParameter.Name
    {
        get { return parameterName; }
    }

    object ISPParameter.Value
    {
        get { return TypedValue; }
    }
}

with an AddParameter method

public void AddParam(SqlCommand cmd, ISPParameter param)
{
    cmd.Parameters.AddWithValue(param.Name, param.Value);
}

Then adding parameters becomes

AddParameter(cmd, txtApprovedAmount);

Hope these give you a few ideas to work on.

Upvotes: 1

Tim Hayes
Tim Hayes

Reputation: 26

Instead of cmd.Parameters.AddWithValue(), use

cmd.Parameters.Add(new SqlParameter(/*overload that accepts dbType and value*/));

Upvotes: 1

Red Taz
Red Taz

Reputation: 4179

The whole point of a type-safe language is that you must explicitly cast objects of one type to another as & when required.

With regard to your last point I would read up on c#'s conditional operator (?:) http://msdn.microsoft.com/en-us/library/ty67wk28%28v=vs.80%29.aspx

Which lets you do things like;

(txtApprovedAmount.Text == String.Empty) ? null : txtApprovedAmount.Text;

Upvotes: 0

Michael Holman
Michael Holman

Reputation: 961

I wouldn't recommend it, but I believe that you can have the stored procedure take varchar as a parameter, and convert it to a double in the stored procedure. I know in Oracle some types can be implicitly cast, so if SQL Server does the same, you might be able to ignore casting all together (depending on what your stored procedures do).

Upvotes: 2

Related Questions