Is something like this possible:

int MyParameter = 3356;

            DataTable x = new DataTable("CommandParams");
            x.Columns.Add("ParamName");
            x.Columns.Add("ParamType");
            x.Columns.Add("Object");

            DataRow y = x.NewRow();
            y["ParamName"] = "@SomeParamName";
            y["ParamType"] = MyParameter.GetType();
            y["Object"] = MyParameter;

            SqlCommand z = new SqlCommand();
            z.Parameters.Add(new SqlParameter(x.Rows[0]["ParamName"], (x.Rows[0]["ParamType"])x.Rows[0]["Object"]);

I'm trying to come up with a method that can loop through a datatable (or something) and add parameters to an SqlCommand instead of typing out hundreds of lines of code if there are hundreds of parameters.

Thoughts?

You would need to add a reference to System.Reflection in your code.

Once you've done that, you can create an instance of the class using Assembly.CreateInstance("insert class name here"); This will return an object of that type. However, you will still need to cast it if you want to use it for anything other than passing to a different method, in which case, you would still need to know which object type it is, so you may as well cast it anyway ^^

I'm not sure there's a method to cast using a string. Not 100% certain though.

Personally, I feel what you should think about though, is what are you going to do once you've pulled them all out of the database?

You can't cast them to their individual class types, you can't return them or store them in a list/dictionary.

The only thing you *can* do is cast them to an interface that they all share in common.

I think what you're probably best doing, is, leaving it as I mentioned above (as a load of "Object" type) and then use your own knowledge as to what end type it should be when you actually use it and use a safer if(myObject is ObjectType) before trying to cast it. But if there are hundreds of different types, you should reconsider your design to make it more type safe.

Edited 6 Years Ago by Ketsuekiame: n/a

Thanks for the information.

I fear you might be correct. However, I was only keeping the information in a datatable temporarily to loop through it to create SQL parameters.

There aren't hundreds of data types, there are just a bunch of parameters and it's a maintenance nightmare to have to edit the parameter lists over and over when I can just loop through either all the form controls and get information and assign them all random parameter names and then loop through all that and create parameters from it.

I think I'm stuck though.

You could do something like this.

private class paramData
        {
            public paramData(string name, SqlDbType type, object value)
            {
                Name = name;
                Type = type;
                Value = value;
            }
            public string Name { get; set; }
            public SqlDbType Type { get; set; }
            public object Value { get; set; }
        }
                
        private paramData[] CommandParams;

        private void CreateParamList()
        {
            CommandParams = new paramData[2];
            CommandParams[0] = new paramData("@Param1", SqlDbType.Int, 2);
            CommandParams[1] = new paramData("@Param2", SqlDbType.NVarChar, "String Data");
            // the array could also be populated using a loop 
            // getting the data from somewhere else
        }

        private void CreateCommand()
        {
            SqlCommand cmd = new SqlCommand("Put your query here");
            CreateParamList();
            for (int i = 0; i < CommandParams.Length; i++)
            {
                SqlParameter sqlParam = new SqlParameter(CommandParams[i].Name, CommandParams[i].Type);
                sqlParam.Value = CommandParams[i].Value;
                cmd.Parameters.Add(sqlParam);
            }
            // execute command
        }

Note: this snippet has not been tested but it does compile.

Comments
Excellent suggestion! Thanks!
This question has already been answered. Start a new discussion instead.