ArrayList of SqlParameters not appearing correctly.

[tshad]

I have a ArrayList of SqlParameters that I created but when I try to access it, it says:

At least one element in the source array could not be cast down to the destination array type.

The code is:

            ArrayList s = new ArrayList();
            s.Add(new SqlParameter("@CompanyID", SqlDbType.Int).Value = 1);
            s.Add(new SqlParameter("@ClientID", SqlDbType.Int).Value = 2);

I try to return an SqlParameter[] with a procedure:

        public static SqlParameter[] GetSqlParameterArray(ArrayList al)
        {
            return (SqlParameter[])al.ToArray(typeof(SqlParameter));
        }

I get the error on the return.

What am I missing?

Thanks,

Tom

 

[tshad]

I simplified the test to get a better idea of the issue.

This works.

            ArrayList s = new ArrayList();
            s.Add(new SqlParameter("@CompanyID", SqlDbType.Int));
            s.Add(new SqlParameter("@ClientID", SqlDbType.Int));

            parameters = (SqlParameter[])s.ToArray(typeof(SqlParameter));
            parameters[0].Value = 1;
            parameters[1].Value = 2;

We end up with s having 2 SqlParameter objects the ArrayList. I then convert to an SqlParameter[]. I then assign my values. I am trying to get away from this as it is easy to make a mistake if I have many parameters.

What I am trying to do is what I can do with an SqlParameterCollection (which is not possible in this way).

            ArrayList s = new ArrayList();
            s.Add(new SqlParameter("@CompanyID", SqlDbType.Int).Value = 1);
            s.Add(new SqlParameter("@ClientID", SqlDbType.Int).Value = 2);

            parameters = (SqlParameter[])s.ToArray(typeof(SqlParameter));

This gets the error because the ArrayList doesn't have 2 SqlParameters but two values of 1 and 2.

Is there a way to do this?

Thanks,

Tom

 

[Rhys666]

s.Add(new SqlParameter("@CompanyID", 1));
s.Add(new SqlParameter("@ClientID", 2));

You are adding the SqlParameter.Value properties to the array, (1 and 2)

Rhys

"Technological progress is like an axe in the hands of a pathological criminal"
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe"
Albert Einstein

 

[tshad]

But the problem is that I want to be able to define type as well as the value.

s.Add(new SqlParameter("@ClientID", SqlDbType.Int).Value = 2);

instead of:

s.Add(new SqlParameter("@ClientID", SqlDbType.Int));
parameters[0].Value = 1;

This is fine but it gets a little more of a problem if there are 20 parameters.

Thanks,

Tom

 

[Rhys666]

If you want to define the type and value you're going to have to use one of the other constructors, or rather than an array list you could use a SqlParameter array in the first instance. Is there any particular reason you're using an ArrayList?



Rhys

"Technological progress is like an axe in the hands of a pathological criminal"
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe"
Albert Einstein

 

[tshad]

I didn't use an SqlParameter array since you can't do it dynamically. I can accomplish that using an ArrayList of SqlParameters. I want to be able to create the SqlParameter and set the type and value at the same time. I could have done that with an SqlParameterCollection but that won't work as there is no constructor. I could also have used a generic list of SqlParameters (List<SqlParameters>) but there seemed to be issues that the ArrayList didn't have.

So I created a method that would do it for me. The method has different overloads for each SqlDbType, such as:

        public static SqlParameter SetParameter(string name, SqlDbType type, int size, string value)
        {
            SqlParameter parameter = new SqlParameter(name, type, size);
            parameter.Value = value;

            return parameter;
        }

        public static SqlParameter SetParameter(string name, SqlDbType type, int value)
        {
            SqlParameter parameter = new SqlParameter(name, type);
            parameter.Value = value;

            return parameter;
        }

        public static SqlParameter SetParameter(string name, SqlDbType type, double value)
        {
            SqlParameter parameter = new SqlParameter(name, type);
            parameter.Value = value;

            return parameter;
        }

I can then create the parameters, similar to:

            ArrayList parameters = new ArrayList()
                {   
				    database.SetParameter("@CompanyID",SqlDbType.Int, companyID.Data), 
				    database.SetParameter("@FirstName",SqlDbType.VarChar,15, firstName.Data), 
				    database.SetParameter("@LastName",SqlDbType.VarChar,30, lastName.Data), 
				    database.SetParameter("@Title",SqlDbType.VarChar,50, title.Data), 
				    database.SetParameter("@Nickname",SqlDbType.VarChar,50, nickname.Data)
                }

The values are special types I created to carry the value of the variable as well as the initial value and whether it is null or not.

This works fine except I also want to be able to assign a null if necessary but can't get that to work. As you surmised in my other post, I can't use a conditional with a null and integer, such as:

database.SetParameter("@UserID", SqlDbType.Int, companyID.IsNull ? DBNull.Value : companyID.Data)

Thanks,

Tom

 

[tshad]

Actually, I have come up with a better way of doing this, using List<Sqlparameter> which I thought would be a problem. Apparently, this is not and issue.

So I have the following:

            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(database.SetParameter("@Friend",SqlDbType.Int,2));
            parms.Add(database.SetParameter("@Client",SqlDbType.VarChar,50,"Joes bar and grill"));

            parms.Find(c => c.ParameterName == "@Client").Value = "This is a test";

            parms.Find(c => c.ParameterName == "@Client").Value = null;

This allows me to assign values (but not nulls) when setting the parameter up. But using the Lamda expression, I can now assign the null (or another expression) with out using the "parms[1]" syntax which can cause errors if I make changes later and put a new parameter in the middle of the list (the indexes after the new parameter will increase by one).

I would still like to do the conditional expression when I set it up but can't figure a good way to do it.

Thanks,

Tom