CODEWITHSUNDEEP
c#
Kryle Quimpo
Kryle Quimpo

Reputation: 13

How to create optional parameters using object array

I am creating a method which MUST be use with or without parameters. I am using object array so that I can hold string, integer, binary types, etc at the same time.

Method: SQLDB_UsingReader(string strSQL_WithParam, params object[,] obj)

Error: The array must be a single dimensional array.

P.S This method SQLDB_UsingReader(string strSQL_WithParam, object[,] obj) is working, but when I add "params" as the solution I searched when creating optional parameter, the error occurs.

CODE

public void SQLDB_UsingReader(string strSQL_WithParam, params object[,] obj)
    {
        try
        {
            using (SqlCommand mCmd = new SqlCommand(strSQL_WithParam, mConn))
            {
                for (int i = 0; i < obj.Length / 2; i++)
                {
                    if (obj[i, 1] == null || obj[i, 1].ToString() == "" || obj[i, 1].ToString().Length == 0)
                    { mCmd.Parameters.Add(new SqlParameter(obj[i, 0].ToString(), DBNull.Value)); }
                    else
                    { mCmd.Parameters.Add(new SqlParameter(obj[i, 0].ToString(), obj[i, 1])); }
                }
                mConn.Open();
                mDataReader = mCmd.ExecuteReader();
                mConn.Close();
            }
        }
        catch (Exception ex) { ex.ToString(); }
    }

Upvotes: 1

Views: 163

Answers (1)

Ian Mercer
Ian Mercer

Reputation: 39277

Use Dictionary<string, object>. You have two values: a string and an object per parameter you wish to set. Either create a class that has one of each and use params for an array of that class or simply use the built in type for a collection of named values.

public void SQLDB_UsingReader(string strSQL_WithParam, IDictionary<string, object> obj)
{
    try
    {
        string where = obj.Any() ? ("where " + string.Join("AND", obj
                                   .Select(x => x.Key +"==@" + x.Key)) : "";
        using (SqlCommand mCmd = new SqlCommand(strSQL_WithParam + where, mConn))
        {
            foreach pair in obj
            {
                ... (use pair.Value and pair.Key)
            }
            ...
        }
    }
    ...
}

Upvotes: 0

Related Questions