/// <summary>
/// Méthode Drop
/// </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 DropTableNextGen <T>(ConnectionSqlServer connection, T obj)
{
MappingObject objectMapping = new MappingObject();
objectMapping = MappingOperations.GetTypeOfProPostGre(obj);
string reqDropTable = $"DROP TABLE IF EXISTS {objectMapping.ObjectName}";
try
{
using (SqlConnection conn = GetConnection(connection.Server,
connection.DataBase, connection.User, connection.Password))
{
conn.Open();
using (SqlCommand queryToDropTable = new SqlCommand(reqDropTable, conn))
{
queryToDropTable.ExecuteNonQuery();
return(true);
}
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// Méthode Select
/// </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>
/// <typeparam name="string">string</typeparam>
/// <param name="column">Une colonne</param>
/// <typeparam name="string">string</typeparam>
/// <param name="value">Une valeur</param>
/// <remarks>
/// Des vérifications sont faites avant le select, si aucune précisiion n'est ajouté, la table est renvoyée, si si une valeur ou colonne est donnée alors, un élement sera renvoyé
/// </remarks>
/// <returns>True si la requête s'est executer correctement, false si une exception est détecté</returns>
public static List <T> SelectTableNextGen <T>(ConnectionSqlServer connection, string column, string value,
T table)
{
string reqSelectElement;
if (table.GetType().Name == null)
{
Console.WriteLine("obj not found");
return(null);
}
bool isAProperty = false;
foreach (PropertyInfo item in table.GetType().GetProperties())
{
if (column.Equals(item.Name))
{
isAProperty = true;
}
}
if (isAProperty == false)
{
Console.WriteLine("property obj not found");
return(null);
}
if (value == null || column == null)
{
reqSelectElement = $"SELECT * FROM {table.GetType().Name.ToString()}";
}
else
{
reqSelectElement = $"SELECT * FROM {table.GetType().Name.ToString()} WHERE {column} LIKE @{column}";
}
try
{
using (SqlConnection conn = GetConnection(connection.Server,
connection.DataBase, connection.User, connection.Password))
{
conn.Open();
using (SqlCommand queryToSelectElement = new SqlCommand(reqSelectElement, conn))
{
queryToSelectElement.Parameters.AddWithValue(column, value);
SqlDataReader dr = queryToSelectElement.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
List <T> list = MappingOperations.MapList(dt, table);
return(list);
}
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// Méthode Select
/// </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>
/// <typeparam name="string">string</typeparam>
/// <param name="column">Une colonne</param>
/// <typeparam name="string">string</typeparam>
/// <param name="value">Une valeur</param>
/// <remarks>
/// Des vérifications sont faites avant l'exécution du programme, si tout est validé, l'élément séléctionné sera modifié
/// </remarks>
/// <returns>True si la requête s'est executer correctement, false si une exception est détecté</returns>
public static bool UpdateElementNextGen <T>(ConnectionSqlServer 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} = @{objectMapping.PropertiesAttributes[i].NameInfo}";
if (i != objectMapping.PropertiesAttributes.Count() - 1)
{
reqUpdate += ",";
}
}
reqUpdate += $" WHERE id LIKE @id";
try
{
using (SqlConnection conn = GetConnection(connection.Server,
connection.DataBase, connection.User, connection.Password))
{
conn.Open();
using (SqlCommand queryUpdate = new SqlCommand(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.ExecuteNonQuery();
return(true);
}
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// Méthode Delete
/// </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>
/// <typeparam name="string">string</typeparam>
/// <param name="column">Une colonne</param>
/// <typeparam name="string">string</typeparam>
/// <param name="value">Une valeur</param>
/// <remarks>
/// Des vérifications sont faites avant l'exécution du programme, si tout est validé, l'élément séléctionné sera supprimé
/// </remarks>
/// <returns>True si la requête s'est executer correctement, false si une exception est détecté</returns>
public static bool DeleteElemetFromTableNextGen <T>(ConnectionSqlServer connection, string column, string value,
T table)
{
if (table.GetType().Name == null)
{
Console.WriteLine("obj not found");
return(false);
}
bool isAProperty = false;
foreach (PropertyInfo item in table.GetType().GetProperties())
{
if (column.Equals(item.Name))
{
isAProperty = true;
break;
}
}
if (isAProperty == false)
{
Console.WriteLine("property obj not found");
return(false);
}
string reqDelete = $"DELETE FROM {table.GetType().Name.ToString()} WHERE {column} LIKE @{column}";
try
{
using (SqlConnection conn = GetConnection(connection.Server,
connection.DataBase, connection.User, connection.Password))
{
conn.Open();
using (SqlCommand queryToDeleteElement = new SqlCommand(reqDelete, conn))
{
queryToDeleteElement.Parameters.AddWithValue(column, value);
queryToDeleteElement.ExecuteNonQuery();
return(true);
}
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// Méthode Insert
/// </summary>
/// <typeparam name="T">Paramètre générique</typeparam>
/// <param name="obj">Objet</param>
/// <typeparam name="ConnectionSqlServer">Connection SQL Server</typeparam>
/// <param name="connection">Objet voiture</param>
/// <remarks>
/// on utilise une SQLCommand équipée de paramètre afin d'insérée une valeur dans la table
/// </remarks>
/// <returns>True si la requête s'est executer correctement, false si une exception est détecté</returns>
public static bool InsertNextGen <T>(ConnectionSqlServer connection, T obj)
{
MappingObject objectMapping = new MappingObject();
objectMapping = MappingOperations.GetTypeOfProSQLServer(obj);
string reqInsertElement = $" INSERT INTO {objectMapping.ObjectName} VALUES(";
for (int i = 0; i < objectMapping.PropertiesAttributes.Count(); i++)
{
PropertyAttributes infoFormapping = objectMapping.PropertiesAttributes[i];
reqInsertElement += $"@{infoFormapping.NameInfo}";
if (i != objectMapping.PropertiesAttributes.Count() - 1)
{
reqInsertElement += ",";
}
}
reqInsertElement += ")";
try
{
using (SqlConnection conn = GetConnection(connection.Server, connection.DataBase, connection.User,
connection.Password))
{
conn.Open();
using (SqlCommand queryToInsert = new SqlCommand(reqInsertElement, conn))
{
for (int i = 0; i < objectMapping.PropertiesAttributes.Count(); i++)
{
PropertyAttributes infoFormapping = objectMapping.PropertiesAttributes[i];
queryToInsert.Parameters.AddWithValue($"{infoFormapping.NameInfo}",
infoFormapping.ValueInfo);
}
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);
}
}