Creating dynamic SQL DbParameter values

best way to do this is put them in Dictionary, because you will need value also

Dictionary<string, string> params = new Dictionary<string,string>();

and just add them many as you want

params.Add("@FileName1", "my_filename")

etc...

and then

foreach(var param in params)
   dbCommand.Parameters.AddWithValue(param.Key, param.Value);

Creating dynamic SQL DbParameter values

This is very helpful when you are going to create project where there is dynamic database, or may in future you are going to migrate / switch database .

Here is step by step solution

step 1) Create Parameter structure

  public struct Parameter
    {
        public string ParameterName { get; set; }
        public ParameterDirection Direction { get; set; }
        public DbType DbType { get; set; }
        public object Value { get; set; }
        public string SourceColumn { get; set; }
        public int Size { get; set; }
    }

Step 2) Create database handling class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Data.Common;
using MySql.Data.MySqlClient;
using MySql.Data;
using Oracle.DataAccess;
using Oracle.DataAccess.Client;

 public class DBManagement  
    {
        string connectionStr;
        DbConnection con;
        DbCommand cmd;
        DbDataAdapter AD;
        DataSet ds;
        DbParameter[] sp;
        IDBManagement Iobj = null;
        public DBManagement()
        {
            this.Initialize();
        }

 void Initialize()
        {
            try
            {

                switch (ConfigurationManager.AppSettings["ActiveDatabase"].ToUpper())
                {
                    case "MSSQL":
                        connectionStr = ConfigurationManager.ConnectionStrings["MSSQLConnectionString"].ConnectionString;
                        con = new SqlConnection();
                        cmd = new SqlCommand();
                        AD = new SqlDataAdapter();
                        break;
                    case "ORACLE":
                        connectionStr = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;
                        con = new OracleConnection();
                        cmd = new OracleCommand();
                        AD = new OracleDataAdapter();
                        break;
                    case "MYSQL":
                        connectionStr = ConfigurationManager.ConnectionStrings["MYSQLConnectionString"].ConnectionString;
                        con = new MySqlConnection();
                        cmd = new MySqlCommand();
                        AD = new MySqlDataAdapter();
                        break;
                    default:

                        break;
                }

                con.ConnectionString = connectionStr;
                cmd.Connection = con;
            }
            catch (Exception ex)
            {

            }
        }

        public DataSet ExecuteProcedure(string procName, CommandType cmdType, Parameter[] DBParameters = null)
        {
            try
            {
                cmd.CommandText = procName;
                cmd.CommandType = cmdType;

                cmd.Parameters.Clear();

                if (DBParameters != null && DBParameters.Length > 0)
                { 
                    sp = DBParameters.ToParamerArray(cmd);  
                    cmd.Parameters.AddRange(sp); 
                }
                ds = new DataSet();
                AD.SelectCommand = cmd;
                AD.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
}

Step 3) Convert parameter as per database

public static partial class GlobalExtensionFunctions 
    {
        public static DbParameter[] ToParamerArray(this Parameter[] parameters,DbCommand cmd)
        {
            DbParameter[] sp = new DbParameter[parameters.Length]; 
            int i = 0;
            foreach (Parameter parameter in parameters)
            {
               // DbParameter p = cmd.CreateParameter();
                sp[i] = cmd.CreateParameter();
                sp[i].ParameterName = parameter.ParameterName;
                sp[i].Value = parameter.Value;
                sp[i].Direction = string.IsNullOrEmpty(Convert.ToString(parameter.Direction))  || parameter.Direction==0 ? ParameterDirection.Input : parameter.Direction;
                sp[i].DbType = parameter.DbType;
                sp[i].SourceColumn = parameter.SourceColumn;
                sp[i].Size = parameter.Size;
                i++;
            }
            return sp;
        }
    }

Step 4) Get Data

 DBManagement c = new DBManagement();
public DataSet GetGetTestList(int testId)
        {
            Parameter[] p = new Parameter[1]; 
             
            p[0].ParameterName = "@TestId";
            p[0].Value = testId;
            p[0].DbType = DbType.Int32;
          
            return c.ExecuteProcedure(Procedures.TestDetails, CommandType.StoredProcedure,p);
        }

Now use dataset or datatable and enjoy! :)

Abe - thanks - you got me in the right direction. Here is what I ended up doing:

inside my foreach loop, I'm calling my method:

foreach (DataRow row in GlobalClass.NAVdataTable.Rows)
  {
    GlobalClass.AddToDbCommand(ref dBCommand, row["FieldName"].ToString(), row["Value"].ToString());

    connection.Open();
    SqlDb.ExecuteNonQuery(dBCommand);
    connection.Close();
    dBCommand.Parameters.Clear();
  }

and then my AddToDbCommand method contains:

public static void AddToDbCommand(ref DbCommand dbCommand, string FieldName, string FieldValue)
    {
        string FieldNameParameter = "@" + FieldName;
        DbParameter dbParameter = dbCommand.CreateParameter();
        dbParameter.ParameterName = FieldNameParameter; 
        dbParameter.Value = FieldValue;
        dbCommand.Parameters.Add(dbParameter);
    }

Refactored as an extension to DbCommand, also fieldName is left without @, so you need to pass @ or : prefixes and fieldValue is set to object type (not only string).

public static class DbCommandExtensions
{
    public static void AddParam(this DbCommand dbCommand, string fieldName, object fieldValue)
    {
        string fieldNameParameter = fieldName;

        DbParameter dbParameter = dbCommand.CreateParameter();
        dbParameter.ParameterName = fieldNameParameter;
        dbParameter.Value = fieldValue;
        dbCommand.Parameters.Add(dbParameter);
    }
}