public List <T> Query(string query) { List <T> resultset = new List <T>(); using (NpgsqlCommand cmd = new NpgsqlCommand(query, Connection.Get())) using (Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { dynamic entry = new ExpandoObject(); for (int i = 0; i < reader.FieldCount; i++) { Type type = reader.GetFieldType(i); entry[reader.GetName(i)] = Convert.ChangeType(reader.GetFieldValue <Object>(i), type); } resultset.Add(entry); } return(resultset); } }
private static void DoDeriveParameters(NpgsqlCommand command) { // See http://www.postgresql.org/docs/9.3/static/catalog-pg-proc.html command.Parameters.Clear(); // Updated after 0.99.3 to support the optional existence of a name qualifying schema and case insensitivity when the schema ror procedure name do not contain a quote. // This fixed an incompatibility with NpgsqlCommand.CheckFunctionReturn(String ReturnType) var serverVersion = command.Connector.ServerVersion; String query = null; string procedureName = null; string schemaName = null; string[] fullName = command.CommandText.Split('.'); if (fullName.Length > 1 && fullName[0].Length > 0) { // proargsmodes is supported for Postgresql 8.1 and above if (serverVersion >= new Version(8, 1, 0)) { query = "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname"; } else { query = "select proargnames, proargtypes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname"; } schemaName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower(); procedureName = (fullName[1].IndexOf("\"") != -1) ? fullName[1] : fullName[1].ToLower(); } else { // proargsmodes is supported for Postgresql 8.1 and above if (serverVersion >= new Version(8, 1, 0)) { query = "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc where proname = :proname"; } else { query = "select proargnames, proargtypes from pg_proc where proname = :proname"; } procedureName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower(); } using (NpgsqlCommand c = new NpgsqlCommand(query, command.Connection)) { c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text)); c.Parameters[0].Value = procedureName.Replace("\"", "").Trim(); if (fullName.Length > 1 && !String.IsNullOrEmpty(schemaName)) { NpgsqlParameter prm = c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text)); prm.Value = schemaName.Replace("\"", "").Trim(); } string[] names = null; uint[] types = null; char[] modes = null; using (NpgsqlDataReader rdr = c.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult)) { if (rdr.Read()) { if (!rdr.IsDBNull(0)) { names = rdr.GetValue(0) as String[]; } if (serverVersion >= new Version("8.1.0")) { if (!rdr.IsDBNull(2)) { types = rdr.GetValue(2) as uint[]; } if (!rdr.IsDBNull(3)) { modes = rdr.GetValue(3) as char[]; } } if (types == null) { if (rdr.IsDBNull(1) || rdr.GetFieldValue <uint[]>(1).Length == 0) { return; // Parameterless function } types = rdr.GetFieldValue <uint[]>(1); } } else { throw new InvalidOperationException(String.Format(L10N.InvalidFunctionName, command.CommandText)); } } command.Parameters.Clear(); for (var i = 0; i < types.Length; i++) { var param = new NpgsqlParameter(); throw new NotImplementedException(); /* * // TODO: Fix enums, composite types * var npgsqlDbType = c.Connector.TypeHandlerRegistry.GetNpgsqlDbTypeFromOid(types[i]); * if (npgsqlDbType == NpgsqlDbType.Unknown) * throw new InvalidOperationException(String.Format("Invalid parameter type: {0}", types[i])); * param.NpgsqlDbType = npgsqlDbType; * * if (names != null && i < names.Length) * param.ParameterName = ":" + names[i]; * else * param.ParameterName = "parameter" + (i + 1); * * if (modes == null) // All params are IN, or server < 8.1.0 (and only IN is supported) * param.Direction = ParameterDirection.Input; * else * { * switch (modes[i]) * { * case 'i': * param.Direction = ParameterDirection.Input; * break; * case 'o': * param.Direction = ParameterDirection.Output; * break; * case 'b': * param.Direction = ParameterDirection.InputOutput; * break; * case 'v': * throw new NotImplementedException("Cannot derive function parameter of type VARIADIC"); * case 't': * throw new NotImplementedException("Cannot derive function parameter of type TABLE"); * default: * throw new ArgumentOutOfRangeException("proargmode", modes[i], * "Unknown code in proargmodes while deriving: " + modes[i]); * } * } * * command.Parameters.Add(param); */ } } }