?
Solved

Converting csharp datatype to SqlDbType

Posted on 2009-12-30
12
Medium Priority
?
791 Views
Last Modified: 2013-12-17
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
Comment
Question by:JackDev68
  • 5
  • 4
  • 2
11 Comments
 
LVL 22

Expert Comment

by:p_davis
ID: 26145994
have you tried just casting instead of converting?

parameter.Value = (int) paramValues[index];
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 26146043
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
 

Author Comment

by:JackDev68
ID: 26146057
Yes and I get this error: "Specified cast is not valid."
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 22

Expert Comment

by:p_davis
ID: 26146061
then i think that kaufmed has hit it on the head.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 26146064
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
 

Author Comment

by:JackDev68
ID: 26146179
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
 

Author Comment

by:JackDev68
ID: 26146191
1st Error message actually reads:  "Unable to cast object of type 'System.Int32[]' to type 'System.IConvertible"
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 26146240
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
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 26146281
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
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 26146525
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
 

Accepted Solution

by:
JackDev68 earned 0 total points
ID: 26147106
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month17 days, 2 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question