Assigning SqlParameter.Value without knowing type or using mixed type array

Question

I've got a data layer I'm working on that calls into a database three times with three different stored procedures. I initially created three different functions to retrieve three dimensionalities of results. The first returns a single value, the second an entire row, and the third a table. They also take in different parameters. The first takes two varchars, the second two ints, and the last three varchars. If I try to get fancy and merge it all down as shown below, am I going to have problems?

public void CallStoredProcedure(string[] astrParams, string strConnectionString, string strStoredProcedure)
{
    int nParams = 0;
    SqlParameter[] asqlParams;
    asqlParams = SqlHelperParameterCache.GetSpParameterSet(strConnectionString, strStoredProcedure);
    foreach (string strParam in astrParams)
    {
        asqlParams[nParams].Value = strParam;
        nParams++;
    }
}

Alternately, can I use an array of mixed data types without know what is in there, and can I assign different types into the same array, replacing elements?

object[] aParams;
string strName = "Joe";
long lngHeight = 180;
object[0] = strName;
object[1] = lngHeight;
CallStoredProcedure(aParams, strConnectionString, "StoredProcedure1")

long lngWeight = 3;
string strItemName = "Bookend";
object[0] = lngWeight;
object[1] = strItemName;
CallStoredProcedure(aParams, strConnectionString, "StoredProcedure2")

And then change the code inside that function to:

foreach (object oParam in astrParams)
{
    asqlParams[nParams].Value = oParam;
    nParams++;
}

Would I need to use ToString() in either or both of these cases? And if so, does that essentially turn them into the same thing? Like I said, right now I've got three functions that take in all the parameters correctly typed, but I'm trying to get rid of duplicate code if possible.

look up Parameter.AddWithValue

MethodMan
– MethodMan

2014-10-17 19:38:33 +00:00
Commented Oct 17, 2014 at 19:38 — MethodMan
– MethodMan, Commented Oct 17, 2014 at 19:38

mgrenier · Accepted Answer · 2014-10-17 19:58:19Z

1

You cannot have an array of different data types, you can have an array of objects and cast the individual elements to a specific data type when saving the values to typed variable though.

Not sure where you want to use ToString() but if its when you are saving a string into an Object array there is no point but if you are saving an object to a string variable then yes you would need to do this:

string str = objectArray[0].ToString();

I would avoid this whole mess though, and follow what DJ KRAZE said and add your parameters via Parameter.AddWithValue though.

edited Oct 17, 2014 at 19:58

answered Oct 17, 2014 at 19:48

mgrenier

1,4673 gold badges23 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MethodMan Over a year ago

thanks mgrenier to explain my comment a little bit more, using the AddWithValue, lets the database resolve the datatype if this makes more sense.. also here is a good article Joe M Create array Objects of different datatypes there are plenty other good ones out there as well..

adrianm Over a year ago

@DJKRAZE, The type of the parameter is determined from the value you assign with .AddWithValue, not the database. i.e. strings will always be NVarchar(length of string). If used in queries it will pollute the plan cache and might cause implicit conversions preventing index use.

MethodMan Over a year ago

adrianm the database can still resolve the datatype being passed in via AddWithValue.. what I am essentially saying is why add the datatype in the c# code when you can just pass in the parameter i think that it's quite obvious that when passing in the type whether it's string, int, decimal etc.. that the database can resolve the type for example if you pass a string value of 3.49 the database side of things can still tell that you're implicitly and or explicitly passing a decimal etc.. it's all semantic at this point.. but I appreciate your insight

MethodMan Over a year ago

Adrianm here is some documentation on AddWIthValue for some further understanding Sql ParameterCollection.AddWIthValue

Joe M · Accepted Answer · 2014-10-21 16:49:06Z

0

I was fairly confused about what I needed to do with my initial question. What I realized is that I don't need to worry about C# types when adding the parameters, since GetSpParameterSet pulls all the information I need into the SqlParameter array, and resets the array every time I call it. So my initial code:

foreach (string strParam in astrParams)
{
    asqlParams[nParams].Value = strParam;
    nParams++;
}

...will just work for my needs. There won't be any problem assigning a C# string type into an SQL int type, which was my main concern, since the languages don't have identical types anyway. I know that I won't have to worry about anything like an "x" going into an int type because all the values I'm using are being pulled from the properly typed columns in other SQL queries already. So I won't need any object arrays.

answered Oct 21, 2014 at 16:49

Joe M

3,5904 gold badges45 silver badges67 bronze badges

1 Comment

David A. Gray Over a year ago

When all else fails, there is always the mother of all .NET types, Object. Used sparingly, there is nothing wrong with the Object type, per se. Indeed, a good many widely used methods take arguments of type Object (e. g., StringBuilder.Append, Console.WriteLine, String.Format.

Collectives™ on Stack Overflow

Assigning SqlParameter.Value without knowing type or using mixed type array

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related