Reputation: 137

Creating dynamic SQL DbParameter values

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.

Upvotes: 6

Answers (5)

Amol Khandagale

Reputation: 121

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

Upvotes: 2

user205036

Reputation:

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

Upvotes: 0

HaySeed

Reputation: 137

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

Upvotes: 0

Senad Meškin

Reputation: 13756

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

Upvotes: 2

Abe Miessler

Reputation: 85126

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

Upvotes: 9

Creating dynamic SQL DbParameter values

Answers (5)

Related Questions