示例#1
0
        /// <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);
            }
        }
示例#2
0
        /// <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);
            }
        }
示例#3
0
        /// <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);
            }
        }
示例#4
0
        /// <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);
            }
        }
示例#5
0
        /// <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);
            }
        }
示例#6
0
        /// <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);
            }
        }