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