/// <summary>
        /// Creates a new instance of the StoredProcedureInfo class.
        /// This constructor is private; call the static GetInfo() method instead.
        /// </summary>
        /// <param name="connectionName">Name of the database connection.</param>
        /// <param name="schema">Name of the database schema.</param>
        /// <param name="procedureName">Name of the stored procedure.</param>
        private StoredProcedureInfo(string connectionName, string schema, string procedureName)
        {
            this.ConnectionName = connectionName;
            this.Schema = schema;
            this.ProcedureName = procedureName;

            // query the database for the procedure's parameters;
            // if the procedure doesn't exist, zero parameters will be found, successfully (no error)
            var parametersQuery = new StoredProcedureQuery("SelectStoredProcedureParameters", connectionName);
            parametersQuery.Parameters.Add("Schema", this.Schema);
            parametersQuery.Parameters.Add("ProcedureName", this.ProcedureName);

            DataSet results = parametersQuery.ReturnAllTables();
            var parameters = results.Tables[0];   // no rows if the procedure has no parameters
            var columnNames = results.Tables[1];  // no rows if the procedure has no TVPs; all TVPs' columns otherwise

            foreach (DataRow parameter in parameters.Rows)
            {
                var param = new ParameterInfo
                {
                    Name = parameter["ParameterName"] as string,
                    TypeString = parameter["DataType"] as string,
                    UserDefinedTypeName = parameter["UserDefinedTypeName"] as string,
                    MaxLength = parameter["MaxLength"] as int?
                };

                // TODO: switch this logic around when we internationalize the db to use nvarchar columns
                if (param.TypeString == "varchar")
                    param.Type = SqlDbType.VarChar;
                else if (param.TypeString == "char")
                    param.Type = SqlDbType.Char;
                else if (param.TypeString == "nvarchar")
                    param.Type = SqlDbType.NVarChar;
                else if (param.TypeString == "nchar")
                    param.Type = SqlDbType.NChar;
                else if (param.TypeString == "table type")
                {
                    param.Type = SqlDbType.Structured;

                    // get the table type's column names
                    param.ColumnNames = columnNames.Select("ParameterName = '" + param.Name + "'")
                        .Select(dr => dr["ColumnName"] as string).ToArray();
                }

                this.Parameters.Add(param);
            }
        }
        /// <summary>
        /// Creates a StoredProcedureQuery object for the given procedure and connection,
        /// automatically parameters from the Arguments dictionary.
        /// </summary>
        /// <param name="procedureName">Name of the stored procedure to execute.</param>
        /// <param name="connectionName">Name of the connection to use.</param>
        protected StoredProcedureQuery CreateStoredProcedureQuery(string procedureName, string connectionName)
        {
            var query = new StoredProcedureQuery(procedureName, connectionName);

            // Initialize query parameters by trying to pull all parameters from the arguments passed
            int cacheTimeoutSeconds = Config.GetInt("API.CacheStoredProcedureInfoForSeconds", 0);
            var sprocInfo = StoredProcedureInfo.GetInfo(connectionName, procedureName, cacheTimeoutSeconds);

            foreach (StoredProcedureInfo.ParameterInfo param in sprocInfo.Parameters)
            {
                if (!this.Arguments.ContainsKey(param.Name)) continue;

                // Get the parameter value
                object paramValue = this.Arguments[param.Name];

                if (param.Type == SqlDbType.Structured && !string.IsNullOrEmpty(param.UserDefinedTypeName))
                {
                    // It's a table valued parameter (TVPs must be defined as user-defined types);
                    // create a new DataTable object from the value,
                    // which is expected to be an array of IDictionary<string, object> objects
                    var dt = new DataTable(param.Name);
                    foreach (var columnName in param.ColumnNames)
                        dt.Columns.Add(columnName);

                    if (paramValue != null && !(paramValue is string))
                    {
                        var isIdTableType = (param.UserDefinedTypeName == "IdTableType");
                        foreach (var item in paramValue as object[])
                        {
                            var dr = dt.NewRow();

                            if (isIdTableType)
                            {
                                // parameter is an IdTableType
                                dr["Id"] = item;
                            }
                            else
                            {
                                // parse a normal object
                                var itemDict = item as IDictionary<string, object>;

                                foreach (var columnName in param.ColumnNames)
                                {
                                    if (itemDict.ContainsKey(columnName))
                                        dr[columnName] = itemDict[columnName];
                                }
                            }

                            dt.Rows.Add(dr);
                        }
                    }

                    var sqlParam = new SqlParameter(param.Name, param.Type.Value)
                    {
                        Value = dt,
                        TypeName = param.UserDefinedTypeName
                    };

                    query.Parameters.Add(param.Name, sqlParam);
                }
                else if (param.Type.HasValue && param.MaxLength.HasValue)
                {
                    // It's a type we handle specially, rather than letting ADO infer the SQL type from the .NET object;
                    // see http://geekswithblogs.net/Rhames/archive/2008/10/29/why-you-should-always-specify-the-sqldbtype-for-an-ado.net.aspx
                    var sqlParam = new SqlParameter(param.Name, param.Type.Value, param.MaxLength.Value)
                    {
                        Value = paramValue ?? DBNull.Value
                    };

                    query.Parameters.Add(param.Name, sqlParam);
                }
                else
                {
                    // It's a type for which we let ADO infer the SQL type.
                    query.Parameters.Add(param.Name, paramValue);
                }
            }

            return query;
        }