private KeyLookup GetKeys(Int32 tableOid)
{
string getKeys = "select a.attname, ci.relname, i.indisprimary from pg_catalog.pg_class ct, pg_catalog.pg_class ci, pg_catalog.pg_attribute a, pg_catalog.pg_index i WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid AND a.attrelid=ci.oid AND i.indisunique AND ct.oid = :tableOid order by ci.relname";
KeyLookup lookup = new KeyLookup();
lookup.primaryKey = new ArrayList();
lookup.uniqueColumns = new ArrayList();
using (NpgsqlConnection metadataConn = _connection.Clone())
{
NpgsqlCommand c = new NpgsqlCommand(getKeys, metadataConn);
c.Parameters.Add(new NpgsqlParameter("tableOid", NpgsqlDbType.Integer)).Value = tableOid;
using (NpgsqlDataReader dr = c.ExecuteReader())
{
string previousKeyName = null;
string possiblyUniqueColumn = null;
string columnName;
string currentKeyName;
// loop through adding any column that is primary to the primary key list
// add any column that is the only column for that key to the unique list
// unique here doesn't mean general unique constraint (with possibly multiple columns)
// it means all values in this single column must be unique
while (dr.Read())
{
columnName = dr.GetString(0);
currentKeyName = dr.GetString(1);
// if i.indisprimary
if (dr.GetBoolean(2))
{
// add column name as part of the primary key
lookup.primaryKey.Add(columnName);
}
if (currentKeyName != previousKeyName)
{
if (possiblyUniqueColumn != null)
{
lookup.uniqueColumns.Add(possiblyUniqueColumn);
}
possiblyUniqueColumn = columnName;
}
else
{
possiblyUniqueColumn = null;
}
previousKeyName = currentKeyName;
}
// if finished reading and have a possiblyUniqueColumn name that is
// not null, then it is the name of a unique column
if (possiblyUniqueColumn != null)
{
lookup.uniqueColumns.Add(possiblyUniqueColumn);
}
}
}
return(lookup);
}
public static Boolean?GetBooleanOrNull(this READER reader, int ordinal)
{
if (reader.IsDBNull(ordinal))
{
return(null);
}
else
{
return(reader.GetBoolean(ordinal));
}
}
/// <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);
}
public static Boolean GetBooleanOrDefault(this READER reader, int ordinal) => reader.IsDBNull(ordinal) ? default : reader.GetBoolean(ordinal);
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();
}
}
}
private static User GetUser(NpgsqlDataReader reader)
{
return new User
{
Id = reader.GetInt64(0),
Name = reader.GetString(1),
Description = reader.GetString(2),
Photo = reader.GetByteArray(3),
Enabled = reader.GetBoolean(4),
CanChange = reader.GetBoolean(5),
Privilege = reader.GetNullableInt32(6)
};
}
private MembershipUser GetUserFromReader(string providerName, NpgsqlDataReader reader)
{
object providerUserKey = reader.GetValue(0);
string username = reader.GetString(1);
string email = reader.GetString(2);
string passwordQuestion = string.Empty;
if (reader.GetValue(3) != DBNull.Value)
passwordQuestion = reader.GetString(3);
string comment = string.Empty;
if (reader.GetValue(4) != DBNull.Value)
comment = reader.GetString(4);
bool isApproved = reader.GetBoolean(5);
bool isLockedOut = reader.GetBoolean(6);
DateTime creationDate = reader.GetDateTime(7);
DateTime lastLoginDate = new DateTime();
if (reader.GetValue(8) != DBNull.Value)
lastLoginDate = reader.GetDateTime(8);
DateTime lastActivityDate = reader.GetDateTime(9);
DateTime lastPasswordChangedDate = reader.GetDateTime(10);
DateTime lastLockedOutDate = new DateTime();
if (reader.GetValue(11) != DBNull.Value)
lastLockedOutDate = reader.GetDateTime(11);
MembershipUser u = new MembershipUser(
providerName,
username,
providerUserKey,
email,
passwordQuestion,
comment,
isApproved,
isLockedOut,
creationDate,
lastLoginDate,
lastActivityDate,
lastPasswordChangedDate,
lastLockedOutDate);
return u;
}
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;
}
