public void CarregarAluno(NpgsqlDataReader dr)
{
Model.Aluno objAluno = new Model.Aluno();
if (dr.Read())
{
objAluno.Nome = dr.IsDBNull(dr.GetOrdinal("nm_usuario")) ? string.Empty : dr.GetString(dr.GetOrdinal("nm_usuario"));
objAluno.Email = dr.IsDBNull(dr.GetOrdinal("email_usuario")) ? string.Empty : dr.GetString(dr.GetOrdinal("email_usuario"));
objAluno.Senha = dr.IsDBNull(dr.GetOrdinal("senha_usuario")) ? string.Empty : dr.GetString(dr.GetOrdinal("senha_usuario"));
objAluno.Id = dr.IsDBNull(dr.GetOrdinal("id_usuario")) ? 0 : dr.GetInt32(dr.GetOrdinal("id_usuario"));
Model.Session.Session.Aluno = objAluno;
}
else { throw new ExceptionDAL("Não foi possível logar usuário"); }
}
public string NewID()
{
string i = "";
string sQuery = "select '" + clsGlobal.pstrservercode + "'||nextval('tbm_carrepair_nextid') as id;";
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi);
cmd.CommandText = sQuery;
try
{
Npgsql.NpgsqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
if (!rdr.IsDBNull(rdr.GetOrdinal("id")))
{
i = rdr.GetValue(0).ToString();
}
else
{
i = "";
};
}
rdr.Close();
}
catch (Npgsql.NpgsqlException Ex)
{
System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!");
return("");
}
return(i);
}
public static Int64?GetInt64OrNull(this READER reader, int ordinal)
{
if (reader.IsDBNull(ordinal))
{
return(null);
}
else
{
return(reader.GetInt64(ordinal));
}
}
public static Decimal?GetDecimalOrNull(this READER reader, int ordinal)
{
if (reader.IsDBNull(ordinal))
{
return(null);
}
else
{
return(reader.GetDecimal(ordinal));
}
}
private string GetSafeString(NpgsqlDataReader reader, int colindex)
{
if(reader.IsDBNull(colindex))
{
return string.Empty;
}
else
{
return reader.GetString(colindex);
}
}
public static DateTime?GetDateTimeOrNull(this READER reader, int ordinal)
{
if (reader.IsDBNull(ordinal))
{
return(null);
}
else
{
return(reader.GetDateTime(ordinal));
}
}
public static Boolean?GetBooleanOrNull(this READER reader, int ordinal)
{
if (reader.IsDBNull(ordinal))
{
return(null);
}
else
{
return(reader.GetBoolean(ordinal));
}
}
public static DateTime GetDateTimeOrDefault(this READER reader, int ordinal) => reader.IsDBNull(ordinal) ? default : reader.GetDateTime(ordinal);
public static Boolean GetBooleanOrDefault(this READER reader, int ordinal) => reader.IsDBNull(ordinal) ? default : reader.GetBoolean(ordinal);
public Curso carregarCursoXAluno(NpgsqlDataReader data_reader)
{
Curso curso = new Curso();
curso.Tl_curso = data_reader.IsDBNull(data_reader.GetOrdinal("TL_CURSO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("TL_CURSO"));
curso.Ds_curso = data_reader.IsDBNull(data_reader.GetOrdinal("DS_CURSO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("DS_CURSO"));
curso.Duracao_curso = data_reader.IsDBNull(data_reader.GetOrdinal("DUR_CURSO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("DUR_CURSO"));
curso.Dt_cadastro = data_reader.IsDBNull(data_reader.GetOrdinal("DT_CADASTRO")) ? DateTime.MinValue : data_reader.GetDateTime(data_reader.GetOrdinal("DT_CADASTRO"));
return curso;
}
/// <summary>
/// A helper function that takes the current row from the NpgsqlDataReader
/// and hydrates a MembershipUser from the values. Called by the
/// MembershipUser.GetUser implementation.
/// </summary>
/// <param name="reader">NpgsqlDataReader object</param>
/// <returns>MembershipUser object</returns>
private MembershipUser GetUserFromReader(NpgsqlDataReader reader)
{
object providerUserKey = reader.GetValue(0);
string username = reader.GetString(1);
string email = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
string passwordQuestion = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);
string comment = reader.IsDBNull(4) ? string.Empty : reader.GetString(4);
bool isApproved = reader.IsDBNull(5) ? false : reader.GetBoolean(5);
bool isLockedOut = reader.IsDBNull(6) ? false : reader.GetBoolean(6);
DateTime creationDate = reader.IsDBNull(7) ? DateTime.MinValue : reader.GetDateTime(7);
DateTime lastLoginDate = reader.IsDBNull(8) ? DateTime.MinValue : reader.GetDateTime(8);
DateTime lastActivityDate = reader.IsDBNull(9) ? DateTime.MinValue : reader.GetDateTime(9);
DateTime lastPasswordChangedDate = reader.IsDBNull(10) ? DateTime.MinValue : reader.GetDateTime(10);
DateTime lastLockedOutDate = reader.IsDBNull(11) ? DateTime.MinValue : reader.GetDateTime(11);
return new MembershipUser(this.Name,
username,
providerUserKey,
email,
passwordQuestion,
comment,
isApproved,
isLockedOut,
creationDate,
lastLoginDate,
lastActivityDate,
lastPasswordChangedDate,
lastLockedOutDate);
}
private UserCommandEvent GetUserCommandEventFromRecord(NpgsqlDataReader reader)
{
var userCommandEvent = new UserCommandEvent
{
Command = (CommandType) reader.GetValue(8),
StockSymbol = (reader.IsDBNull(9)) ? null : reader.GetString(9),
Funds = (reader.IsDBNull(10)) ? null : (decimal?) reader.GetDecimal(10)
};
FillBaseEventPropertiesFromRecord(userCommandEvent, reader);
return userCommandEvent;
}
public bool GetByPrimaryKey(string pKey)
{
string sQuery = "select * from tbm_carrepair WHERE repairid='" + pKey + "'";
Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi);
cmd.CommandText = sQuery;
Npgsql.NpgsqlDataReader rdr = cmd.ExecuteReader();
try
{
if (rdr.Read())
{
if (!rdr.IsDBNull(rdr.GetOrdinal("repairid")))
{
m_repairid = rdr.GetString(rdr.GetOrdinal("repairid"));
}
else
{
m_repairid = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("carid")))
{
m_carid = rdr.GetString(rdr.GetOrdinal("carid"));
}
else
{
m_carid = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("opadd")))
{
m_opadd = rdr.GetString(rdr.GetOrdinal("opadd"));
}
else
{
m_opadd = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("pcadd")))
{
m_pcadd = rdr.GetString(rdr.GetOrdinal("pcadd"));
}
else
{
m_pcadd = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("luadd")))
{
m_luadd = rdr.GetDateTime(rdr.GetOrdinal("luadd"));
}
else
{
m_luadd = System.DateTime.MinValue;
};
if (!rdr.IsDBNull(rdr.GetOrdinal("opedit")))
{
m_opedit = rdr.GetString(rdr.GetOrdinal("opedit"));
}
else
{
m_opedit = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("pcedit")))
{
m_pcedit = rdr.GetString(rdr.GetOrdinal("pcedit"));
}
else
{
m_pcedit = "";
};
if (!rdr.IsDBNull(rdr.GetOrdinal("luedit")))
{
m_luedit = rdr.GetDateTime(rdr.GetOrdinal("luedit"));
}
else
{
m_luedit = System.DateTime.MinValue;
};
m_dlt = rdr.GetBoolean(rdr.GetOrdinal("dlt"));
}
return(true);
}
catch (Npgsql.NpgsqlException Ex)
{
System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!");
return(false);
}
finally
{
if (rdr != null)
{
rdr.Close();
}
}
}
public Usuario carregar(NpgsqlDataReader data_reader)
{
Usuario consulta = new Usuario();
consulta.Id_usuario = data_reader.IsDBNull(data_reader.GetOrdinal("ID_USUARIO")) ? 0 : data_reader.GetInt32(data_reader.GetOrdinal("ID_USUARIO"));
int id_tipo_usuario = consulta.Id_usuario = data_reader.IsDBNull(data_reader.GetOrdinal("ID_TIPO_USUARIO")) ? 0 : data_reader.GetInt32(data_reader.GetOrdinal("ID_TIPO_USUARIO"));
if (id_tipo_usuario == (int)Tipo_usuario.ADMINISTRADOR)
{
consulta.Tipo = Tipo_usuario.ADMINISTRADOR;
}
else if (id_tipo_usuario == (int)Tipo_usuario.ALUNO)
{
consulta.Tipo = Tipo_usuario.ALUNO;
}
else
{
consulta.Tipo = Tipo_usuario.NONE;
}
consulta.Nm_usuario = data_reader.IsDBNull(data_reader.GetOrdinal("NM_USUARIO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("NM_USUARIO"));
consulta.Email_usuario = data_reader.IsDBNull(data_reader.GetOrdinal("EMAIL_USUARIO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("EMAIL_USUARIO"));
consulta.Senha_usuario = data_reader.IsDBNull(data_reader.GetOrdinal("SENHA_USUARIO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("SENHA_USUARIO"));
consulta.Link_imagem_usuario = data_reader.IsDBNull(data_reader.GetOrdinal("LINK_IMAGEM_USUARIO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("LINK_IMAGEM_USUARIO"));
consulta.Dt_cadastro = data_reader.IsDBNull(data_reader.GetOrdinal("DT_CADASTRO")) ? DateTime.MinValue : data_reader.GetDateTime(data_reader.GetOrdinal("DT_CADASTRO"));
return consulta;
}
// Инициализирует запись данными из DataReader
protected Record(NpgsqlDataReader reader)
{
for (int i = 0; i < reader.FieldCount; i++)
if( !reader.IsDBNull(i) )
this.GetType().GetProperty(reader.GetName(i)).SetValue(this, reader.GetValue(i), null);
}
private Questao carregar(NpgsqlDataReader data_reader)
{
Questao questao = new Questao();
questao.Id_questao = data_reader.IsDBNull(data_reader.GetOrdinal("ID_QUESTAO")) ? 0 : data_reader.GetInt32(data_reader.GetOrdinal("ID_QUESTAO"));
questao.Ds_questao = data_reader.IsDBNull(data_reader.GetOrdinal("DS_QUESTAO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("DS_QUESTAO"));
questao.Curso_obj.Id_curso = data_reader.IsDBNull(data_reader.GetOrdinal("ID_CURSO")) ? 0 : data_reader.GetInt32(data_reader.GetOrdinal("ID_CURSO"));
questao.Dt_cadastro = data_reader.IsDBNull(data_reader.GetOrdinal("DT_CADASTRO")) ? DateTime.MinValue : data_reader.GetDateTime(data_reader.GetOrdinal("DT_CADASTRO"));
return questao;
}
Aula carregar(NpgsqlDataReader data_reader)
{
Aula aula = new Aula();
aula.Id_aula = data_reader.IsDBNull(data_reader.GetOrdinal("ID_AULA")) ? 0 : data_reader.GetInt32(data_reader.GetOrdinal("ID_AULA"));
aula.Tl_aula = data_reader.IsDBNull(data_reader.GetOrdinal("TL_AULA")) ? "" : data_reader.GetString(data_reader.GetOrdinal("TL_AULA"));
aula.Ds_aula = data_reader.IsDBNull(data_reader.GetOrdinal("DS_AULA")) ? "" : data_reader.GetString(data_reader.GetOrdinal("DS_AULA"));
aula.Curso_obj.Id_curso = data_reader.IsDBNull(data_reader.GetOrdinal("ID_CURSO")) ? 0 : data_reader.GetInt32(data_reader.GetOrdinal("ID_CURSO"));
aula.Link_aula = data_reader.IsDBNull(data_reader.GetOrdinal("LINK_AULA")) ? "" : data_reader.GetString(data_reader.GetOrdinal("LINK_AULA"));
aula.Dt_cadastro = data_reader.IsDBNull(data_reader.GetOrdinal("DT_CADASTRO")) ? DateTime.MinValue : data_reader.GetDateTime(data_reader.GetOrdinal("DT_CADASTRO"));
return aula;
}
private DebugEvent GetDebugEventFromRecord(NpgsqlDataReader reader)
{
var debugEvent = new DebugEvent
{
Command = (CommandType)reader.GetValue(8),
StockSymbol = (reader.IsDBNull(9)) ? null : reader.GetString(9),
Funds = (reader.IsDBNull(10)) ? null : (decimal?) reader.GetDecimal(10),
FileName = (reader.IsDBNull(11)) ? null : reader.GetString(11),
DebugMessage = (reader.IsDBNull(12)) ? null : reader.GetString(12)
};
FillBaseEventPropertiesFromRecord(debugEvent, reader);
return debugEvent;
}
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);
*/
}
}
}
private void FillBaseEventPropertiesFromRecord(TransactionEvent te, NpgsqlDataReader reader)
{
te.LoggedAt = reader.GetDateTime(1);
te.OccuredAt = reader.GetDateTime(2);
te.TransactionId = reader.GetInt32(4);
te.UserId = (reader.IsDBNull(5)) ? null : reader.GetString(5);
te.Service = reader.GetString(6);
te.Server = reader.GetString(7);
}
public static T GetEnumOrDefault <T>(this READER reader, int ordinal) where T : Enum
{
if (reader.IsDBNull(ordinal))
{
return(default);
public static string GetStringOrDefault(this READER reader, int ordinal) => reader.IsDBNull(ordinal) ? default : reader.GetString(ordinal);
///<summary>
///
/// This method is reponsible to derive the command parameter list with values obtained from function definition.
/// It clears the Parameters collection of command. Also, if there is any parameter type which is not supported by Npgsql, an InvalidOperationException will be thrown.
/// Parameters name will be parameter1, parameter2, ...
/// For while, only parameter name and NpgsqlDbType are obtained.
///</summary>
/// <param name="command">NpgsqlCommand whose function parameters will be obtained.</param>
public static void DeriveParameters(NpgsqlCommand command)
{
// 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)
String query = null;
string procedureName = null;
string schemaName = null;
string[] fullName = command.CommandText.Split('.');
if (fullName.Length > 1 && fullName[0].Length > 0)
{
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
{
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;
String[] types = null;
using (NpgsqlDataReader rdr = c.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult))
{
if (rdr.Read())
{
if (!rdr.IsDBNull(0))
{
names = rdr.GetValue(0) as String[];
}
if (!rdr.IsDBNull(1))
{
types = rdr.GetString(1).Split();
}
}
}
if (types == null)
{
throw new InvalidOperationException(
String.Format(resman.GetString("Exception_InvalidFunctionName"), command.CommandText));
}
command.Parameters.Clear();
for (Int32 i = 0; i < types.Length; i++)
{
// skip parameter if type string is empty
// empty parameter lists can cause this
if (!string.IsNullOrEmpty(types[i]))
{
NpgsqlBackendTypeInfo typeInfo = null;
if (!c.Connector.OidToNameMapping.TryGetValue(int.Parse(types[i]), out typeInfo))
{
command.Parameters.Clear();
throw new InvalidOperationException(String.Format("Invalid parameter type: {0}", types[i]));
}
if (names != null && i < names.Length)
{
command.Parameters.Add(new NpgsqlParameter(":" + names[i], typeInfo.NpgsqlDbType));
}
else
{
command.Parameters.Add(new NpgsqlParameter("parameter" + (i + 1).ToString(), typeInfo.NpgsqlDbType));
}
}
}
}
}
public static Int64 GetInt64OrDefault(this READER reader, int ordinal) => reader.IsDBNull(ordinal) ? default : reader.GetInt64(ordinal);
public Curso carregar(NpgsqlDataReader data_reader)
{
Curso curso = new Curso();
curso.Id_curso = data_reader.IsDBNull(data_reader.GetOrdinal("ID_CURSO")) ? 0 : data_reader.GetInt32(data_reader.GetOrdinal("ID_CURSO"));
curso.Tl_curso = data_reader.IsDBNull(data_reader.GetOrdinal("TL_CURSO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("TL_CURSO"));
curso.Ds_curso = data_reader.IsDBNull(data_reader.GetOrdinal("DS_CURSO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("DS_CURSO"));
curso.Obj_curso = data_reader.IsDBNull(data_reader.GetOrdinal("OBJ_CURSO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("OBJ_CURSO"));
curso.Topicos_curso = data_reader.IsDBNull(data_reader.GetOrdinal("TOPICOS_CURSO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("TOPICOS_CURSO"));
curso.Pre_req_curso = data_reader.IsDBNull(data_reader.GetOrdinal("PRE_REQ_CURSO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("PRE_REQ_CURSO"));
curso.Duracao_curso = data_reader.IsDBNull(data_reader.GetOrdinal("DURACAO_CURSO")) ? "" : data_reader.GetString(data_reader.GetOrdinal("DURACAO_CURSO"));
curso.Dt_cadastro = data_reader.IsDBNull(data_reader.GetOrdinal("DT_CADASTRO")) ? DateTime.MinValue : data_reader.GetDateTime(data_reader.GetOrdinal("DT_CADASTRO"));
return curso;
}
public static Decimal GetDecimalOrDefault(this READER reader, int ordinal) => reader.IsDBNull(ordinal) ? default : reader.GetDecimal(ordinal);
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();
// The pg_temp pseudo-schema is special - it's an alias to a real schema name (e.g. pg_temp_2).
// We get the real name with pg_my_temp_schema().
if (schemaName == "pg_temp")
{
using (var c = new NpgsqlCommand("SELECT nspname FROM pg_namespace WHERE oid=pg_my_temp_schema()", command.Connection))
{
schemaName = (string)c.ExecuteScalar();
}
}
}
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;
int[] types = null;
string[] 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 int[];
}
if (!rdr.IsDBNull(3))
{
modes = rdr.GetValue(3) as String[];
}
}
if (types == null)
{
if (rdr.IsDBNull(1) || rdr.GetString(1) == "")
{
return; // Parameterless function
}
types = rdr.GetString(1).Split().Select(int.Parse).ToArray();
}
}
else
{
throw new InvalidOperationException(String.Format(resman.GetString("Exception_InvalidFunctionName"), command.CommandText));
}
}
command.Parameters.Clear();
for (var i = 0; i < types.Length; i++)
{
var param = new NpgsqlParameter();
NpgsqlBackendTypeInfo typeInfo = null;
if (!c.Connector.OidToNameMapping.TryGetValue(types[i], out typeInfo))
{
throw new InvalidOperationException(String.Format("Invalid parameter type: {0}", types[i]));
}
param.NpgsqlDbType = typeInfo.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);
}
}
}
private Resposta carregar(NpgsqlDataReader data_reader)
{
Resposta resposta = new Resposta();
resposta.id_resposta = data_reader.IsDBNull(data_reader.GetOrdinal("ID_RESPOSTA")) ? 0 : data_reader.GetInt32(data_reader.GetOrdinal("ID_RESPOSTA"));
resposta.Ds_resposta = data_reader.IsDBNull(data_reader.GetOrdinal("DS_RESPOSTA")) ? "" : data_reader.GetString(data_reader.GetOrdinal("DS_RESPOSTA"));
resposta.FL_correta = data_reader.IsDBNull(data_reader.GetOrdinal("FL_CORRETA")) ? false : data_reader.GetBoolean(data_reader.GetOrdinal("FL_CORRETA"));
resposta.Questao_obj.Id_questao = data_reader.IsDBNull(data_reader.GetOrdinal("ID_QUESTAO")) ? 0 : data_reader.GetInt32(data_reader.GetOrdinal("ID_QUESTAO"));
resposta.Dt_cadastro = data_reader.IsDBNull(data_reader.GetOrdinal("DT_CADASTRO")) ? DateTime.MinValue : data_reader.GetDateTime(data_reader.GetOrdinal("DT_CADASTRO"));
return resposta;
}
