/// <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); } }