Creating dynamic SQL DbParameter values

Question

First time user - hoping this is in the right format:

I am wanting to know if I can create SQL DbParameter values, esp on the ParamaterName.

My current code is:

DbCommand dbCommand = SqlDb.GetStoredProcCommand(uspCommand);
DbParameter ProcessedFileName = dbCommand.CreateParameter();
ProcessedFileName.DbType = DbType.String;
ProcessedFileName.ParameterName = "@FileName";
ProcessedFileName.Value = pstrProcessedFileName;
dbCommand.Parameters.Add(ProcessedFileName);

I am wanting to add:

ProcessedFileName.ParameterName = "@FileName1";
ProcessedFileName.ParameterName = "@FileName2";
ProcessedFileName.ParameterName = "@FileName3";
ProcessedFileName.ParameterName = "@FileName4";

with the @FileNames coming from an array.

Answer 1

Something like this should work:

DbCommand dbCommand = SqlDb.GetStoredProcCommand(uspCommand);

foreach(String param in MyParameters)
{
   DbParameter ProcessedFileName = dbCommand.CreateParameter();
   ProcessedFileName.DbType = DbType.String;
   ProcessedFileName.ParameterName = param;
   ProcessedFileName.Value = pstrProcessedFileName;
   dbCommand.Parameters.Add(ProcessedFileName);
}

Answer 2

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);

Answer 3

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! :)