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

Converting csharp datatype to SqlDbType

I am trying to create a generic method that will populate a dataset from a sproc.  The method should detect the parameters and assign its values based on an object array being passed into the method.  I have performed a simple test with a sproc that accepts 1 int parameter.  When I pass in the int from csharp code I get the following error:   Failed to convert parameter value from a Int32[] to a Int32.

I tried this:   parameter.Value = Convert.ToInt32(paramValues[index]);  but got this error: "Unable to cast object of type 'System.Int32[]' to type 'System.IConvertible'."

There must a function to convert whatever paramValues contains to the appropriate SqlDbType.
public static DataSet PopulateDatset(string sprocName, params object[] paramValues)
        {
            DataSet ds = new DataSet();
            try
            {
                var db = GetSQLDatabaseDev();
                DbCommand dbCommand = db.GetStoredProcCommand(sprocName);
                db.DiscoverParameters(dbCommand);
                var index = 0;
                foreach (SqlParameter parameter in dbCommand.Parameters)
                {
                    if (parameter.Direction == ParameterDirection.Input ||
                        parameter.Direction == ParameterDirection.InputOutput)
                    {
                        parameter.Value = paramValues[index];
                        index++;
                    }
                }

                

                ds = db.ExecuteDataSet(dbCommand);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return ds;

        }

Open in new window

0
JackDev68
Asked:
JackDev68
  • 5
  • 4
  • 2
1 Solution
 
p_davisCommented:
have you tried just casting instead of converting?

parameter.Value = (int) paramValues[index];
0
 
käµfm³d 👽Commented:
Your first error indicates that you are trying to pass an array of integers to the stored procedure. Is this what you are after?

Because you declared paramValues using the params keyword, the function is expecting a list of values--so basically your function call will have one string followed by x number of objects, where x >= 0. The params keyword modifying an array type is basically like declaring a two-dimensional array. So you need an additional indexer to access the individual integer elements:
parameter.Value = paramValues[0][index];

Open in new window

0
 
JackDev68Author Commented:
Yes and I get this error: "Specified cast is not valid."
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
p_davisCommented:
then i think that kaufmed has hit it on the head.
0
 
käµfm³d 👽Commented:
P.S.

I would probably ditch the params keyword unless you are planning on passing multiple arrays to this procedure. If you do that, then your previous code should work.
0
 
JackDev68Author Commented:
kaufmed:

I took your advice and dropped the params keyword.  I then tested the code all ways. Still, same errors persist:
    parameter.Value = Convert.ToInt32(paramValues[index]); ERROR: "'System.Int32[]' to type 'System.IConvertible"
    parameter.Value = (int)paramValues[index]; ERROR: "Specified cast is not valid"
    parameter.Value = paramValues[index]; ERROR: "Failed to convert parameter value from a Int32[] to a Int32."


0
 
JackDev68Author Commented:
1st Error message actually reads:  "Unable to cast object of type 'System.Int32[]' to type 'System.IConvertible"
0
 
käµfm³d 👽Commented:
Disregard the params comment. The issue is that you declared the parameter as an object array (object[]). An array is still an object in .NET, so that is the reason for the invalid cast exception. Your function is expecting an array of objects, an since you passed in an array, your parameter basically looks like this:

    paramValues.Length = 1;
    paramValues[0] = int[];

My apologies for the incorrect advice regarding params. However, I still don't think you need it.
0
 
käµfm³d 👽Commented:
If you are trying to create a function that will take in either an array of objects or a list of objects (via the params keyword), the perhaps the following will suit you:
public static DataSet PopulateDatset(string sprocName, params object paramValues)
        {
            DataSet ds = new DataSet();

            var db = GetSQLDatabaseDev();
            DbCommand dbCommand = db.GetStoredProcCommand(sprocName);
            db.DiscoverParameters(dbCommand);
            var index = 0;

            try
            {
                if (paramValues is object[])
                {
                    foreach (SqlParameter parameter in dbCommand.Parameters)
                    {
                        if (parameter.Direction == ParameterDirection.Input ||
                            parameter.Direction == ParameterDirection.InputOutput)
                        {
                            parameter.Value = paramValues[0][index];
                            index++;
                        }
                    }
                }
                else
                {
                    foreach (SqlParameter parameter in dbCommand.Parameters)
                    {
                        if (parameter.Direction == ParameterDirection.Input ||
                            parameter.Direction == ParameterDirection.InputOutput)
                        {
                            parameter.Value = paramValues[index];
                            index++;
                        }
                    }
                }

                ds = db.ExecuteDataSet(dbCommand);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return ds;

        }

Open in new window

0
 
käµfm³d 👽Commented:
Ok. So I did some actual testing (go figure) and the last post is garbage. Disregard it.

When you pass in an array (and nothing else following it) to a params-decorated parameter, it seems .NET will expand the object out inside the callee. If you pass in an array followed by anything else to the params parameter, then you will have an array object followed by the most recently-passed object (the "anything else").

That said, for the former, you can simple iterate through the parameter as a normal array. With the latter, you will have an array, whose first index contains an array--meaning you will have to index that object to access the individual elements of the originally passed array. Then, of course, your second index in the array (parameter) will be the "anything else".
0
 
JackDev68Author Commented:
Got it!  The problem was I was casting the params object like this:
int[] paramInput = new int[1];
paramInput[0] = 23;

when I cast the input parameter like this:
object[] paramInput = new[] {23};

it worked.  Not sure if you were suggesting this in your other posts, but thanks for the quick responses.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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