/// <summary>
/// Méthodes de la classe Affectation
/// </summary>
#region
/// <summary>
/// Permet de sélectionner des enregistrements suivant un critère de recherche et de trier ces résultats
/// </summary>
/// <param name="connection">Objet de connexion à la base de données</param>
/// <param name="strWhere">Critère de recherche</param>
/// <param name="strSort">Critère de trie</param>
/// <returns></returns>
public bool Select(OracleConnection connection, string strWhere, string strSort)
{
OracleCommand sqlCommand = null;
OracleDataReader sqlReader = null;
string sql = "SELECT id_user, id_profile, admin, date_creation, date_modification, commentary, activation, date_beginning, date_end FROM MOU01.AFFECTATION WHERE " +
strWhere + " ORDER BY " + strSort;
bool bResult = false;
try
{
sqlCommand = new OracleCommand(sql, connection);
sqlReader = sqlCommand.ExecuteReader();
//lecture des données
sqlReader.Read();
m_dIdUser = sqlReader.GetInt64(0);
m_dIdProfile = sqlReader.GetInt64(1);
m_bAdmin = sqlReader.GetBoolean(2);
m_dtCreation = sqlReader.GetDateTime(3);
m_dtModification = sqlReader.GetDateTime(4);
m_strCommentary = sqlReader.GetString(5);
m_bActivation = sqlReader.GetBoolean(6);
m_dtBeginning = sqlReader.GetDateTime(7);
m_dtEnd = sqlReader.GetDateTime(8);
bResult = true;
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
finally
{
try
{
// Fermeture de la base de données
if (sqlReader != null)
{
sqlReader.Close();
sqlReader.Dispose();
}
if (sqlCommand != null)
{
sqlCommand.Dispose();
}
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
}
return(bResult);
}
public IActionResult PickUser(int magicId, int cultId)
{
HttpContext.Session.SetInt32("magicId", magicId);
HttpContext.Session.SetInt32("cultId", cultId);
var users = new List <User>();
using (OracleConnection connection = new OracleConnection(configuration["OracleConnectionString"]))
{
using (OracleCommand command = connection.CreateCommand())
{
connection.Open();
command.CommandText =
$@"SELECT * FROM FANTASYRPG.""User""";
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
if (reader.RowSize > 0)
{
users.Add(new Models.User()
{
IsAdmin = reader.GetBoolean(2),
UserId = reader.GetInt32(0),
UserName = reader.GetString(1)
});
}
}
reader.Dispose();
connection.Close();
}
}
return(View(users));
}
public IActionResult Login(LoginViewModel loginViewModel)
{
int userID = -1;
bool isAdmin = false;
using (OracleConnection connection = new OracleConnection(configuration["OracleConnectionString"]))
{
using (OracleCommand command = connection.CreateCommand())
{
connection.Open();
command.CommandText = $"SELECT UserID, ISADMIN From FANTASYRPG.\"User\" Where UserName = '******'";
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
isAdmin = reader.GetBoolean(1);
userID = reader.GetInt32(0);
}
;
reader.Dispose();
connection.Close();
}
}
if (userID >= 0)
{
HttpContext.Session.SetInt32("UserID", userID);
HttpContext.Session.SetString("UserName", loginViewModel.UserName);
HttpContext.Session.SetString("Role", isAdmin ? "admin" : "user");
return(RedirectToAction("Index", "Home"));
}
return(RedirectToAction("Index", new { invalid = true }));
}
public void run()
{
Exception exp = null;
OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
#if !JAVA
// PostgreSQL ODBC treats Type BOOL as String, so we don't run it on .NET
if ((ConnectedDataProvider.GetDbType(con) == DataBaseServer.PostgreSQL))
{
return;
}
#endif
//Do not test with oracle or DB2, because boolean does not exist in their types.
if ((ConnectedDataProvider.GetDbType(con) == DataBaseServer.Oracle) || (ConnectedDataProvider.GetDbType(con) == DataBaseServer.DB2))
{
return;
}
con.Open();
string fieldName = "t_bit";
if (ConnectedDataProvider.GetDbType(con) == DataBaseServer.PostgreSQL)
{
fieldName = "t_bool";
}
OracleCommand cmd = new OracleCommand("Select " + fieldName + " From TYPES_SIMPLE Where id = '0'", con);
OracleDataReader rdr = cmd.ExecuteReader();
rdr.Read();
try
{
BeginCase("GetBoolean true");
Boolean blnValue;
Compare(rdr.IsDBNull(0), false);
Compare("System.Boolean", rdr.GetValue(0).GetType().FullName);
blnValue = rdr.GetBoolean(0);
Compare(blnValue, true);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
rdr.Close();
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
public static bool GetBool(string strNomeCampo, OracleDataReader objDataReader)
{
try
{
return(objDataReader.GetBoolean(_IndexCampo(strNomeCampo, objDataReader)));
}
catch { }
return(false);
}
/// <summary>
/// 获取表内容实体
/// </summary>
/// <returns></returns>
private List <tableContent> GetTableContentObjects(string tableName)
{
string connstr = "data source=192.168.1.1/orcl;User Id=abc;Password=abc;";
string sql = @"select t1.column_name,data_type,data_default,nullable,comments from (
select table_name, column_name, data_type, data_default, nullable from user_tab_cols where Table_Name = 'T_POL_CUSTOMER_NEW'
)t1
RIGHT JOIN
(
select column_name, comments from user_col_comments where Table_Name = '{0}'
)t2
on t1.column_name = t2.column_name; ";
sql = string.Format(sql, tableName);
using (OracleConnection conn = new OracleConnection(connstr))
{
conn.Open();
using (OracleCommand cmd = new OracleCommand())
{
cmd.CommandText = sql;
//cmd.Parameters.AddRange(parameters);
OracleDataReader reader = cmd.ExecuteReader();
List <tableContent> list = new List <tableContent>();
while (reader.Read())
{
tableContent obj = new tableContent();
obj.ColumnName = reader.GetString(0);
obj.DataType = reader.GetString(1);
obj.IsNullable = reader.GetBoolean(2);
obj.Comment = reader.GetString(3);
list.Add(obj);
}
return(list);
}
}
}
public bool GetBoolean(int i)
{
return(reader.GetBoolean(i));
}
internal static bool GetBoolean(OracleDataReader reader, int index, bool defaultValue = false)
{
return (reader.IsDBNull(index) || !(reader.GetValue(index) is bool)) ? defaultValue : reader.GetBoolean(index);
}
