/// <summary>
/// Supprime une table en base
/// </summary>
/// <param name="connection">Connexion à la base.</param>
/// <param name="obj">Objet générique.</param>
/// <returns>Retourne true si la requête a été exécutée en base, une exception si il y a une erreur.</returns>
/// <exception cref="Exception">Exception déclenchée quand la connexion échoue; l'objet existe déjà en base; quand les types des propriétés ne sont pas respectés; une limite de charactères dépassée. </exception>
public static bool DropTableNextGen <T>(ConnectionPostGre connection, T obj)
{
MappingObject objectMapping = new MappingObject();
objectMapping = MappingOperations.GetTypeOfProPostGre(obj);
string reqDropTable = $"DROP TABLE IF EXISTS {objectMapping.ObjectName}";
try
{
using (OdbcConnection conn = GetConnection(connection.Driver, connection.Server, connection.Port,
connection.DataBase, connection.User, connection.Password))
{
conn.Open();
using (OdbcCommand queryToDropTable = new OdbcCommand(reqDropTable, conn))
{
queryToDropTable.ExecuteNonQuery();
return(true);
}
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// Méthode Create
/// </summary>
/// <typeparam name="T">Paramètre générique</typeparam>
/// <param name="obj">Objet voiture</param>
/// <remarks>
/// obj créer et envoyer en paramètre à la fonction dans le fichier Program.cs
/// <see cref="TestApp.Program.Main"></see>
/// </remarks>
/// <remarks>
/// <c>reqCreateTable</c> Contient la syntaxe de la requête MySql
/// <c>objectMapping</c>Contient les information de l'objet voiture
/// <c>conn</c>Contient les informations de connexion -> (nom de la base, identifiant, mot de passe..)
/// </remarks>
/// <remarks>
/// On récupère grâce à objectMapping les informations de l'objet que l'on ajoute à la requête MySql
/// <c>reqCreateTable += $"{objectMapping.PropertiesAttributes[i].NameInfo} {objectMapping.PropertiesAttributes[i].TypeInfo},";</c>
/// Le nom de l'attribut est récupérer ainsi que le type de sa valeur
/// </remarks>
/// <returns>True si la requête s'est executer correctement, false si une exception est détecté</returns>
public static bool CreateTableNextGen <T>(ConnectionMySql connection, T obj)
{
MappingObject objectMapping = new MappingObject();
objectMapping = MappingOperations.GetTypeOfProMySQL(obj);
string reqCreateTable =
$"CREATE TABLE IF NOT EXISTS {objectMapping.ObjectName}(ID int NOT NULL AUTO_INCREMENT,";
for (int i = 0; i < objectMapping.PropertiesAttributes.Count(); i++)
{
reqCreateTable +=
$"{objectMapping.PropertiesAttributes[i].NameInfo} {objectMapping.PropertiesAttributes[i].TypeInfo},";
}
reqCreateTable += "PRIMARY KEY(ID))";
try
{
using (OdbcConnection conn = GetConnection(connection.Driver, connection.Server,
connection.DataBase, connection.User, connection.Password))
{
conn.Open();
using (OdbcCommand queryToCreateTable = new OdbcCommand(reqCreateTable, conn))
{
queryToCreateTable.ExecuteNonQuery();
return(true);
}
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// Retourne l objet MappingObject pour MySQL.
/// </summary>
internal static MappingObject GetTypeOfProMySQL <T>(T c)
{
MappingObject mappingObject = new MappingObject();
mappingObject.ObjectName = c.GetType().Name;
mappingObject.PropertiesAttributes = new List <PropertyAttributes>();
foreach (PropertyInfo propertyInfoObject in c.GetType().GetProperties())
{
PropertyAttributes propertyAttributes = new PropertyAttributes
{
NameInfo = propertyInfoObject.Name,
ValueInfo = propertyInfoObject.GetValue(c)
};
switch (propertyInfoObject.PropertyType.Name.ToString())
{
case "Int32":
{
propertyAttributes.TypeInfo = "INT";
break;
}
case "String":
{
propertyAttributes.TypeInfo = "MEDIUMTEXT";
break;
}
case "DateTime":
{
propertyAttributes.TypeInfo = "DATETIME";
break;
}
case "Boolean":
{
propertyAttributes.TypeInfo = "BIT";
break;
}
case "Single":
{
propertyAttributes.TypeInfo = "FLOAT";
break;
}
case "Double":
{
propertyAttributes.TypeInfo = "DOUBLE";
break;
}
}
mappingObject.PropertiesAttributes.Add(propertyAttributes);
}
return(mappingObject);
}
/// <summary>
/// Retourne l objet MappingObject pour Post Gre.
/// </summary>
internal static MappingObject GetTypeOfProPostGre <T>(T c)
{
MappingObject mappingObject = new MappingObject();
mappingObject.ObjectName = c.GetType().Name;
mappingObject.PropertiesAttributes = new List <PropertyAttributes>();
foreach (PropertyInfo propertyInfoObject in c.GetType().GetProperties())
{
PropertyAttributes propertyAttributes = new PropertyAttributes
{
NameInfo = propertyInfoObject.Name,
ValueInfo = propertyInfoObject.GetValue(c)
};
switch (propertyInfoObject.PropertyType.Name.ToString())
{
case "Int32":
{
propertyAttributes.TypeInfo = "int";
break;
}
case "String":
{
propertyAttributes.TypeInfo = "text";
break;
}
case "DateTime":
{
propertyAttributes.TypeInfo = "date";
break;
}
case "Boolean":
{
propertyAttributes.TypeInfo = "boolean";
break;
}
case "Single":
{
propertyAttributes.TypeInfo = "real";
break;
}
case "Double":
{
propertyAttributes.TypeInfo = "double precision";
break;
}
}
mappingObject.PropertiesAttributes.Add(propertyAttributes);
}
return(mappingObject);
}
/// <summary>
/// Modifie le ou les élément(s) d'une table en base
/// </summary>
/// <param name="connection">Connexion à la base.</param>
/// <param name="id">Id des propriétés à modifier </param>
/// <param name="table">Objet générique table</param>
/// <returns>Retourne true si la requête a été exécutée en base, une exception si il y a une erreur.</returns>
/// <exception cref="Exception">Exception déclenchée quand la connexion échoue; l'objet existe déjà en base; quand les types des propriétés ne sont pas respectés; une limite de charactères dépassée. </exception>
public static bool UpdateElementNextGen <T>(ConnectionPostGre connection, int id, T table)
{
if (table.GetType().Name == null)
{
Console.WriteLine("obj not found");
return(false);
}
MappingObject objectMapping = new MappingObject();
objectMapping = MappingOperations.GetTypeOfProPostGre(table);
string reqUpdate = $"UPDATE {table.GetType().Name.ToString()} SET ";
for (int i = 0; i < objectMapping.PropertiesAttributes.Count(); i++)
{
reqUpdate += $"{objectMapping.PropertiesAttributes[i].NameInfo}= ?";
if (i != objectMapping.PropertiesAttributes.Count() - 1)
{
reqUpdate += ",";
}
}
reqUpdate += $" WHERE id = ?";
try
{
using (OdbcConnection conn = GetConnection(connection.Driver, connection.Server, connection.Port,
connection.DataBase, connection.User, connection.Password))
{
conn.Open();
using (OdbcCommand queryUpdate = new OdbcCommand(reqUpdate, conn))
{
for (int i = 0; i < objectMapping.PropertiesAttributes.Count(); i++)
{
PropertyAttributes infoFormapping = objectMapping.PropertiesAttributes[i];
queryUpdate.Parameters.AddWithValue($"{infoFormapping.NameInfo}", infoFormapping.ValueInfo);
}
queryUpdate.Parameters.AddWithValue($"id", id);
queryUpdate.Prepare();
queryUpdate.ExecuteNonQuery();
return(true);
}
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
/*
* public static void Select()
* {
*
* // 1. Instantiate the connection
* OdbcConnection conn = new OdbcConnection(
* "Driver={PostgreSQL ODBC Driver(UNICODE)};Server=localhost;Port=5432;Database=orm;UID=mickaël;PWD=170514");
* // 1. Instantiate the connection
*
*
* OdbcDataReader rdr = null;
*
* try
* {
* // 2. Open the connection
* conn.Open();
*
* // 3. Pass the connection to a command object
* OdbcCommand cmd = new OdbcCommand("select * from customers", conn);
*
* //
* // 4. Use the connection
* //
*
* // get query results
* rdr = cmd.ExecuteReader();
*
* // print the CustomerID of each record
* while (rdr.Read())
* {
* Console.WriteLine(rdr["name"]);
* }
* Console.ReadLine();
*
* }
* finally
* {
* // close the reader
* if (rdr != null)
* {
* rdr.Close();
* }
*
* // 5. Close the connection
* if (conn != null)
* {
* conn.Close();
* }
* }
* }
*
* public static void Insert()
* {
* Console.WriteLine("Que voulez-vous insérer ?");
* Console.WriteLine("Entrez le nom de la propriété");
* string propertyname = Console.ReadLine();
*
*
* // 1. Instantiate the connection
* OdbcConnection conn = new OdbcConnection(
* "Driver={PostgreSQL ODBC Driver(UNICODE)};Server=localhost;Port=5432;Database=orm;UID=mickaël;PWD=170514");
*
*
* try
* {
* // 2. Open the connection
* conn.Open();
*
* // 3. Pass the connection to a command object
* OdbcCommand cmd = new OdbcCommand("insert into customers2 (surname) values (?)", conn);
*
*
* cmd.Parameters.Add("@PropertyName", OdbcType.NVarChar).Value = propertyname;
*
*
* cmd.ExecuteNonQuery();
*
*
*
* }
* finally
* {
*
* // 5. Close the connection
* if (conn != null)
* {
* conn.Close();
* }
* }
*
* }
*
* public static void Update()
* {
* Console.WriteLine("Que voulez-vous modifier ?");
* Console.WriteLine("Entrez la valeur à modifier");
* string oldvalue = Console.ReadLine();
* Console.WriteLine("Entrez la nouvelle valeur");
* string newvalue = Console.ReadLine();
*
*
* // 1. Instantiate the connection
* OdbcConnection conn = new OdbcConnection(
* "Driver={PostgreSQL ODBC Driver(UNICODE)};Server=localhost;Port=5432;Database=orm;UID=mickaël;PWD=170514");
*
*
* try
* {
* // 2. Open the connection
* conn.Open();
*
* // 3. Pass the connection to a command object
* OdbcCommand cmd = new OdbcCommand("update customers set prenom = ? where prenom = ?", conn);
*
*
* cmd.Parameters.Add("@NewValue", OdbcType.NVarChar).Value = newvalue;
* cmd.Parameters.Add("@OldValue", OdbcType.NVarChar).Value = oldvalue;
*
* cmd.ExecuteNonQuery();
*
*
*
* }
* finally
* {
*
* // 5. Close the connection
* if (conn != null)
* {
* conn.Close();
* }
* }
*
* }
*
* public static void Delete()
* {
* Console.WriteLine("Que voulez-vous supprimer ?");
* Console.WriteLine("Entrez le nom de la propriété");
* string propertyname = Console.ReadLine();
*
*
* // 1. Instantiate the connection
* OdbcConnection conn = new OdbcConnection(
* "Driver={PostgreSQL ODBC Driver(UNICODE)};Server=localhost;Port=5432;Database=orm;UID=mickaël;PWD=170514");
*
*
* try
* {
* // 2. Open the connection
* conn.Open();
*
* // 3. Pass the connection to a command object
* OdbcCommand cmd = new OdbcCommand("delete from customers2 where surname = ?", conn);
*
* cmd.Parameters.Add("@PropertyName", OdbcType.NVarChar).Value = propertyname;
*
*
* cmd.ExecuteNonQuery();
*
*
*
* }
* finally
* {
*
* // 5. Close the connection
* if (conn != null)
* {
* conn.Close();
* }
* }
*
* }
*
* public static void CreateTable()
* {
* Console.WriteLine("Entrez le nom de la table à créer");
* string tablename = Console.ReadLine();
* Console.WriteLine("Entrez le nom de la colonne à créer");
* string columnname = Console.ReadLine();
* Console.WriteLine("Entrez le type de donnée de la colonne");
* string columntype = Console.ReadLine();
*
*
* // 1. Instantiate the connection
* OdbcConnection conn = new OdbcConnection(
* "Driver={PostgreSQL Unicode ;Server=localhost;Port=5432;Database=orm;UID=mickaël;PWD=170514");
*
*
* try
* {
* // 2. Open the connection
* conn.Open();
*
* // 3. Pass the connection to a command object
* OdbcCommand cmd = new OdbcCommand("create table ? ( ? ? )", conn);
*
* cmd.Parameters.Add("@TableName", OdbcType.NVarChar).Value = tablename;
* cmd.Parameters.Add("@ColumnName", OdbcType.NVarChar).Value = columnname;
* cmd.Parameters.Add("@ColumnType", OdbcType.NVarChar).Value = columntype;
*
*
* cmd.ExecuteNonQuery();
*
*
*
* }
* finally
* {
*
* // 5. Close the connection
* if (conn != null)
* {
* conn.Close();
* }
* }
*
* }
*
* public static void DropTable()
* {
* Console.WriteLine("Entrez le nom de la table à supprimer");
* string tablename = Console.ReadLine();
*
*
* // 1. Instantiate the connection
* OdbcConnection conn = new OdbcConnection(
* "Driver={PostgreSQL ODBC Driver(UNICODE)};Server=localhost;Port=5432;Database=orm;UID=mickaël;PWD=170514");
*
*
* try
* {
* // 2. Open the connection
* conn.Open();
*
* // 3. Pass the connection to a command object
* OdbcCommand cmd = new OdbcCommand("DROP TABLE IF EXISTS ?", conn);
*
* cmd.Parameters.Add("@TableName", OdbcType.NVarChar).Value = tablename;
*
*
*
* cmd.ExecuteNonQuery();
*
*
*
* }
* finally
* {
*
* // 5. Close the connection
* if (conn != null)
* {
* conn.Close();
* }
* }
*
* }
*/
#endregion
#region Insert
/// <summary>
/// Insertion d'élément(s) dans une table en base
/// </summary>
/// <param name="connection">Connexion à la base</param>
/// <param name="obj">Objet générique</param>
/// <returns>Retourne true si la requête a été exécutée en base, une exception si il y a une erreur.</returns>
/// <exception cref="Exception">Exception déclenchée quand la connexion échoue; l'objet existe déjà en base; quand les types des propriétés ne sont pas respectés; une limite de charactères dépassée. </exception>
public static bool InsertNextGen <T>(ConnectionPostGre connection, T obj)
{
MappingObject objectMapping = new MappingObject();
objectMapping = MappingOperations.GetTypeOfProPostGre(obj);
string reqInsertElement = $" INSERT INTO {objectMapping.ObjectName} VALUES(DEFAULT,";
for (int i = 0; i < objectMapping.PropertiesAttributes.Count(); i++)
{
if (i == objectMapping.PropertiesAttributes.Count() - 1)
{
reqInsertElement += "?";
}
else
{
reqInsertElement += "?,";
}
}
reqInsertElement += ")";
try
{
using (OdbcConnection conn = GetConnection(connection.Driver, connection.Server, connection.Port,
connection.DataBase, connection.User, connection.Password))
{
conn.Open();
using (OdbcCommand queryToInsert = new OdbcCommand(reqInsertElement, conn))
{
for (int i = 0; i < objectMapping.PropertiesAttributes.Count(); i++)
{
PropertyAttributes infoFormapping = objectMapping.PropertiesAttributes[i];
queryToInsert.Parameters.AddWithValue($"{infoFormapping.NameInfo}",
infoFormapping.ValueInfo);
}
queryToInsert.Prepare();
queryToInsert.ExecuteNonQuery();
return(true);
}
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// Méthode Create
/// </summary>
/// <typeparam name="T">Paramètre générique</typeparam>
/// <param name="table">Table</param>
/// <typeparam name="ConnectionSqlServer">Connection SQL Server</typeparam>
/// <param name="connection">Objet voiture</param>
/// <remarks>
/// Si la table existe bel et bien, elle sera supprimée
/// </remarks>
/// <returns>True si la requête s'est executer correctement, false si une exception est détecté</returns>
public static bool CreateTableNextGen <T>(ConnectionSqlServer connection, T obj)
{
MappingObject objectMapping = new MappingObject();
objectMapping = MappingOperations.GetTypeOfProSQLServer(obj);
string reqCreateTable = $"CREATE TABLE {objectMapping.ObjectName}(ID INT IDENTITY NOT NULL PRIMARY KEY,";
for (int i = 0; i < objectMapping.PropertiesAttributes.Count(); i++)
{
reqCreateTable +=
$"{objectMapping.PropertiesAttributes[i].NameInfo} {objectMapping.PropertiesAttributes[i].TypeInfo}";
if (i != objectMapping.PropertiesAttributes.Count() - 1)
{
reqCreateTable += ",";
}
}
reqCreateTable += ")";
try
{
using (SqlConnection conn = GetConnection(connection.Server, connection.DataBase, connection.User,
connection.Password))
{
conn.Open();
using (SqlCommand queryToCreateTable = new SqlCommand(reqCreateTable, conn))
{
queryToCreateTable.ExecuteNonQuery();
return(true);
}
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
