Exemple #1
0
        public static List<Post> getPostsByCat_MC(string MC, int Cat)
        {
            ListPosts = new List<Post>();

            try
            {
                string query = @"
                                BEGIN
                                    SELECT
                                    Base.[ID]
                                    ,Base.[Post]
                                    ,Base.[Fk_User]
                                    ,Cat.[Categorie]
                                    ,Base.[CreatedDate]
                                    FROM
                                    (
                                    SELECT
                                    P.[ID]
                                    ,P.[Post]
                                    ,P.[Fk_User]
                                    ,P.[Fk_Categorie]
                                    ,P.[CreatedDate]
                                    FROM
                                    [dbo].[Posts] as P,
                                    [dbo].[Users] as U
                                    WHERE
                                    Fk_Categorie = @Cat
                                    AND
                                    Post like '%'+@MC+'%'
                                    and U.ID = P.Fk_User
                                    and U.IsValid = 1
                                    ) as Base
                                    left join
                                    [dbo].[Categories] as Cat
                                    on
                                    Base.Fk_Categorie = Cat.ID
                                    order by Base.CreatedDate desc
                                END
                            ";

                SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["db_SE"].ConnectionString);
                SqlCommand command = new SqlCommand(query, connection);
                SqlDataAdapter adapter = new SqlDataAdapter(command);

                command.CommandTimeout = 0;
                DataSet result = new DataSet();
                result.Locale = CultureInfo.InvariantCulture;

                command.Parameters.AddWithValue("@Cat", Cat);
                command.Parameters.AddWithValue("@MC", MC);

                adapter.Fill(result);

                if (result != null && result.Tables.Count > 0 && result.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow reader in result.Tables[0].Rows)
                    {
                        Post P = new Post(Convert.ToInt32(reader["ID"]), Convert.ToString(reader["Post"]), Convert.ToInt32(reader["Fk_User"]), Convert.ToString(reader["Categorie"]), Convert.ToDateTime(reader["CreatedDate"]));
                        ListPosts.Add(P);
                    }
                    return ListPosts;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }
Exemple #2
0
        public static List<Post> LoadUserPostsQuery(int user)
        {
            ListPosts = new List<Post>();

            try
            {
                string query = @"
                                BEGIN

                                SELECT
                                Base.[ID]
                                ,Base.[Post]
                                ,Base.[Fk_User]
                                ,Cat.[Categorie]
                                ,Base.[CreatedDate]
                                FROM
                                (
                                SELECT
                                [ID]
                                ,[Post]
                                ,[Fk_User]
                                ,[Fk_Categorie]
                                ,[CreatedDate]
                                FROM [dbo].[Posts]
                                Where Fk_User = @User
                                )  as Base
                                left join
                                [dbo].[Categories] as Cat
                                on
                                Base.Fk_Categorie = Cat.ID
                                order by Cat.[Categorie], Base.CreatedDate desc

                                END
                            ";

                SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["db_SE"].ConnectionString);
                SqlCommand command = new SqlCommand(query, connection);
                SqlDataAdapter adapter = new SqlDataAdapter(command);

                command.CommandTimeout = 0;
                DataSet result = new DataSet();
                result.Locale = CultureInfo.InvariantCulture;

                command.Parameters.AddWithValue("@User", user);

                adapter.Fill(result);

                if (result != null && result.Tables.Count > 0 && result.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow reader in result.Tables[0].Rows)
                    {
                        Post P = new Post(Convert.ToInt32(reader["ID"]), Convert.ToString(reader["Post"]), Convert.ToInt32(reader["Fk_User"]), Convert.ToString(reader["Categorie"]), Convert.ToDateTime(reader["CreatedDate"]));
                        ListPosts.Add(P);
                    }

                    return ListPosts;
                }
                else
                {
                    return null;
                }

            }
            catch (Exception ex)
            {
                return null;
            }
        }
Exemple #3
0
        public static Post GetPostById(int postId)
        {
            try
            {
                string query = @"
                                BEGIN
                                    SELECT
                                    [ID]
                                    ,[Post]
                                    ,[Fk_User]
                                    ,[Fk_Categorie]
                                    ,[CreatedDate]
                                    FROM
                                    [dbo].[Posts]
                                    where [ID] = @postId
                                END
                            ";

                SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["db_SE"].ConnectionString);
                SqlCommand command = new SqlCommand(query, connection);
                SqlDataAdapter adapter = new SqlDataAdapter(command);

                command.CommandTimeout = 0;
                DataSet result = new DataSet();
                result.Locale = CultureInfo.InvariantCulture;

                command.Parameters.AddWithValue("@postId", postId);

                adapter.Fill(result);

                if (result != null && result.Tables[0].Rows.Count > 0)
                {
                    Post P = new Post(Convert.ToInt32(result.Tables[0].Rows[0]["ID"]), Convert.ToString(result.Tables[0].Rows[0]["Post"]), Convert.ToInt32(result.Tables[0].Rows[0]["Fk_User"]), Convert.ToString(result.Tables[0].Rows[0]["Fk_Categorie"]), Convert.ToDateTime(result.Tables[0].Rows[0]["CreatedDate"]));
                    return P;
                }
                else
                {
                    return null;
                }

            }
            catch (Exception ex)
            {
                return null;
            }
        }