//Set all Userinformation with Id that is given by the LoginHandler Script
public static void SetUserInfo(int ID)
{
//create connection and open it
MySqlConnection connection = DbInfo.Connection();
//try to connect to database
try
{
//Build Mysql command
MySqlCommand cmd = connection.CreateCommand();
cmd.CommandText =
"SELECT `ID`, `Firstname`, `Middlename`, `Lastname`, `Email`, `Password`, `Pin` FROM `users` WHERE `ID`=" + ID;
MySqlDataReader reader = cmd.ExecuteReader();
//if match is found
if (reader.Read())
{
//MessageBox.Show(reader["Firstname"] + " "+ reader["Middlename"].ToString());
Userinformation = new UserInfoClass(ID, reader["Firstname"].ToString(), reader["Middlename"].ToString(), reader["Lastname"].ToString(), reader["Email"].ToString());
}
}
//finally
finally
{
connection.Close();
}
}
//-------------------------Collections------------------------
//Convert a SnippetCollection Name To a ID from Database
public static int ToID(string SnippetCollectionName)
{
//create connection and open it
MySqlConnection connection = DbInfo.Connection();
//Build Mysql command
MySqlCommand cmd = connection.CreateCommand();
//Create and add Commandtext
cmd.CommandText = "SELECT `ID`, `Name` FROM `snippedcollection` WHERE `Name` = @Name";
cmd.Parameters.AddWithValue("@Name", SnippetCollectionName);
//Create reader
MySqlDataReader reader = cmd.ExecuteReader();
//if match is found
if (reader.Read())
{
return(int.Parse(reader["ID"].ToString()));
}
else
{
return(0);
}
}
//Convert a Language ID To a Programming Language from Database
public static string ToString(int ID)
{
//create connection and open it
MySqlConnection connection = DbInfo.Connection();
//Build Mysql command
MySqlCommand cmd = connection.CreateCommand();
//Create and add Commandtext
cmd.CommandText = "SELECT `ID`, `Name` FROM `proglanguage` WHERE `ID` = @ID";
cmd.Parameters.AddWithValue("@ID", ID);
//Create reader
MySqlDataReader reader = cmd.ExecuteReader();
//if match is found
if (reader.Read())
{
return(reader["Name"].ToString());
}
else
{
return("");
}
}
//-------------------------CRUD------------------------------
//Add new CodeLanguage
public static void AddNewCodeLanguage(CodeLanguageInfo Languageinfo)
{
//Create Connection
using (MySqlConnection connection = DbInfo.Connection())
{
//Create cmd
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = connection;
cmd.CommandType = CommandType.Text;
//Create CommandText
cmd.CommandText = "INSERT INTO `proglanguage`(`ID`, `Name`) VALUES (@ID, @Name)";
//Set Parameters
cmd.Parameters.AddWithValue("@ID", "");
cmd.Parameters.AddWithValue("@Name", Languageinfo.Name);
try
{
int recordsAffected = cmd.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
}
}
//-------------------------Get------------------------------
//Get all Programming Languages in Database
public static List <CodeLanguageInfo> GetallLanguages()
{
//Setup a temp list
List <CodeLanguageInfo> Temp = new List <CodeLanguageInfo>();
//create connection and open it
MySqlConnection connection = DbInfo.Connection();
//Build Mysql command
MySqlCommand cmd = connection.CreateCommand();
//Create and add Commandtext
cmd.CommandText = "SELECT `ID`,`Name` FROM `proglanguage`";
//Create reader
MySqlDataReader reader = cmd.ExecuteReader();
//if match is found
while (reader.Read())
{
Temp.Add(new CodeLanguageInfo(int.Parse(reader["ID"].ToString()), reader["Name"].ToString()));
}
//Return The temp list
return(Temp);
}
//Delete new CodeLanguage
public static void DeleteCodeLanguage(int ID)
{
//Create Connection
using (MySqlConnection connection = DbInfo.Connection())
{
//Create Cmd
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = connection;
cmd.CommandType = CommandType.Text;
//Set CommandText
cmd.CommandText = "DELETE FROM `proglanguage` WHERE `ID` = @ID";
//Add Parameters
cmd.Parameters.AddWithValue("@ID", ID);
try
{
int recordsAffected = cmd.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
}
}
//Update new CodeLanguage
public static void UpdateCodeLanguage(CodeLanguageInfo Languageinfo)
{
//Create Connection
using (MySqlConnection connection = DbInfo.Connection())
{
//Create Cmd
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = connection;
cmd.CommandType = CommandType.Text;
//Create CommandText
cmd.CommandText = "UPDATE `proglanguage` SET `Name`=@Name WHERE `ID`=@ID,";
//Set Parameters
cmd.Parameters.AddWithValue("@ID", Languageinfo.ID);
cmd.Parameters.AddWithValue("@Name", Languageinfo.Name);
try
{
int recordsAffected = cmd.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
}
}
//Get SnippetCollectionInfo related to CollectionID
private static SnippetCollectionInfo GetSnippetCollectionInfo(int CollectionID)
{
//Create Temp
SnippetCollectionInfo info = null;
//create connection and open it
MySqlConnection connection = DbInfo.Connection();
//Build Mysql command
MySqlCommand cmd = connection.CreateCommand();
//Add the CommandText
cmd.CommandText = "SELECT `ID`, `UserID`, `Name`, `CreateDate` FROM `snippedcollection` WHERE `ID` = @CollectionID";
//Add Parameter
cmd.Parameters.AddWithValue("@CollectionID", CollectionID);
//Create reader
MySqlDataReader reader = cmd.ExecuteReader();
//While reading
if (reader.Read())
{
info = new SnippetCollectionInfo(
int.Parse(reader["ID"].ToString()),
int.Parse(reader["UserID"].ToString()),
reader["Name"].ToString(),
DateTime.Parse(reader["CreateDate"].ToString())
);
}
//Return Value
return(info);
}
//Get all snippetsinfo related to CollectionID
private static List <SnippetInfo> GetSnippetsRelatedToCollection(int CollectionID)
{
/*
* if code snippetcollectionID = ID
* Get Snippet ID
* Get SnippetInfo from ID and add to list
*/
//Create Temp
List <SnippetInfo> Temp = null;
//create connection and open it
MySqlConnection connection = DbInfo.Connection();
//Build Mysql command
MySqlCommand cmd = connection.CreateCommand();
//Add the CommandText
cmd.CommandText = "SELECT `ID`, `CodeSnippetsID`, `SnippetCollectionID` FROM `snipped_codecollection` WHERE `SnippetCollectionID` = @CollectionID";
//Add Parameter
cmd.Parameters.AddWithValue("@CollectionID", CollectionID);
//Create reader
MySqlDataReader reader = cmd.ExecuteReader();
//While reading
while (reader.Read())
{
Temp.Add(DbSnippets.GetSnippetInfoFromID(int.Parse(reader["CodeSnippetsID"].ToString())));
}
//Return Value
return(Temp);
}
//-------------------------GetCollection------------------------------
//Get all Collections
public static List <CollectionCompleteInfo> GetAllCollections()
{
//Create Temp
List <CollectionCompleteInfo> Temp = null;
//create connection and open it
MySqlConnection connection = DbInfo.Connection();
//Build Mysql command
MySqlCommand cmd = connection.CreateCommand();
//Add the CommandText
cmd.CommandText = "SELECT `ID`, `UserID`, `Name`, `CreateDate` FROM `snippedcollection`";
//Create reader
MySqlDataReader reader = cmd.ExecuteReader();
//While reading
while (reader.Read())
{
int ID = int.Parse(reader["ID"].ToString());
Temp.Add(new CollectionCompleteInfo(GetSnippetCollectionInfo(ID), GetSnippetsRelatedToCollection(ID)));
}
//Return Value
return(Temp);
}
//-------------------------Get------------------------------
//Returns a list with all Tags in Database
public static List <TagInfo> GetAllTags()
{
//Create temp List
List <TagInfo> Temp = new List <TagInfo>();
//create connection and open it
MySqlConnection connection = DbInfo.Connection();
//Build Mysql command
MySqlCommand cmd = connection.CreateCommand();
//Create and add Commandtext
cmd.CommandText = "SELECT `ID`, `TagName` FROM `tags`";
//Create reader
MySqlDataReader reader = cmd.ExecuteReader();
//While reading
while (reader.Read())
{
Temp.Add(new TagInfo(int.Parse(reader["ID"].ToString()), reader["TagName"].ToString()));
}
return(Temp);
}
//Update new Snippet
public static void UpdateSnippet(SnippetInfo snippetInfo)
{
using (MySqlConnection connection = DbInfo.Connection())
{
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = connection;
cmd.CommandType = CommandType.Text;
cmd.CommandText =
"UPDATE `codesnippets` SET " +
"`TagCollectionID`= @TagCollectionID," +
"`Name`= @Name," +
"`Code`= @Code," +
"`CodeEditDate`= @CodeEditDate," +
"`UsageExample`= @UsageExample," +
"`UsageEditDate`= @UsageEditDate," +
"`Description`= @Description," +
"`DescriptionEditDate`= @DescriptionEditDate," +
"`LanguageID`= @LanguageID " +
"WHERE `ID`= @ID";
cmd.Parameters.AddWithValue("@ID", snippetInfo._ID);
cmd.Parameters.AddWithValue("@TagCollectionID", snippetInfo._TagCollectionID);
cmd.Parameters.AddWithValue("@Name", snippetInfo._Name);
cmd.Parameters.AddWithValue("@Code", snippetInfo._Code);
cmd.Parameters.AddWithValue("@CodeEditDate", snippetInfo._CodeEditDate.ToString());
cmd.Parameters.AddWithValue("@UsageExample", snippetInfo._UsageExample);
cmd.Parameters.AddWithValue("@UsageEditDate", snippetInfo._UsageEditDate.ToString());
cmd.Parameters.AddWithValue("@Description", snippetInfo._Description);
cmd.Parameters.AddWithValue("@DescriptionEditDate", snippetInfo._DescriptionEditDate.ToString());
cmd.Parameters.AddWithValue("@LanguageID", snippetInfo._LanguageID);
try
{
int recordsAffected = cmd.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
}
}
//Get SnippetInfo from ID
public static SnippetInfo GetSnippetInfoFromID(int ID)
{
//Create temp
SnippetInfo Temp = null;
//create connection and open it
MySqlConnection connection = DbInfo.Connection();
//Build Mysql command
MySqlCommand cmd = connection.CreateCommand();
//Create and add Commandtext
cmd.CommandText = "SELECT `ID`, `UserID`, `TagCollectionID`, `Name`, `Code`, `CodeEditDate`, `UsageExample`, `UsageEditDate`, `Description`, `DescriptionEditDate`, `LanguageID`, `CreateDate` FROM `codesnippets` WHERE `ID` = @ID";
cmd.Parameters.AddWithValue("@ID", ID);
//Create reader
MySqlDataReader reader = cmd.ExecuteReader();
//if match is found
if (reader.Read())
{
//Save a new Temporary Snippet
return(new SnippetInfo(
int.Parse(reader["ID"].ToString()),
int.Parse(reader["UserID"].ToString()),
int.Parse(reader["TagCollectionID"].ToString()),
reader["Name"].ToString(),
reader["Code"].ToString(),
DateTime.Parse(reader["CodeEditDate"].ToString()),
reader["UsageExample"].ToString(),
DateTime.Parse(reader["UsageEditDate"].ToString()),
reader["Description"].ToString(),
DateTime.Parse(reader["DescriptionEditDate"].ToString()),
int.Parse(reader["LanguageID"].ToString()),
DateTime.Parse(reader["CreateDate"].ToString())
));
}
//Return value
return(Temp);
}
//-------------------------CRUD------------------------------
//Add new Snippet
public static void AddNewSnippet(SnippetInfo snippetInfo)
{
using (MySqlConnection connection = DbInfo.Connection())
{
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = connection;
cmd.CommandType = CommandType.Text;
cmd.CommandText =
"INSERT INTO `codesnippets`(`ID`, `UserID`, `TagCollectionID`, `Name`, `Code`, `CodeEditDate`, `UsageExample`, `UsageEditDate`, `Description`, `DescriptionEditDate`, `LanguageID`, `CreateDate`) " +
"VALUES (@ID,@UserID,@TagCollectionID,@Name,@Code,@CodeEditDate,@UsageExample,@UsageEditDate,@Description,@DescriptionEditDate,@LanguageID,@CreateDate)";
cmd.Parameters.AddWithValue("@ID", "");
cmd.Parameters.AddWithValue("@UserID", UserInfo.Userinformation.ID);
cmd.Parameters.AddWithValue("@TagCollectionID", snippetInfo._TagCollectionID);
cmd.Parameters.AddWithValue("@Name", snippetInfo._Name);
cmd.Parameters.AddWithValue("@Code", snippetInfo._Code);
cmd.Parameters.AddWithValue("@CodeEditDate", snippetInfo._CodeEditDate.ToString());
cmd.Parameters.AddWithValue("@UsageExample", snippetInfo._UsageExample);
cmd.Parameters.AddWithValue("@UsageEditDate", snippetInfo._UsageEditDate.ToString());
cmd.Parameters.AddWithValue("@Description", snippetInfo._Description);
cmd.Parameters.AddWithValue("@DescriptionEditDate", snippetInfo._DescriptionEditDate.ToString());
cmd.Parameters.AddWithValue("@LanguageID", snippetInfo._LanguageID);
cmd.Parameters.AddWithValue("@CreateDate", snippetInfo._CreateDate.ToString());
try
{
int recordsAffected = cmd.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
}
}
//-------------------------Filter------------------------------
//Filter out all codesnippets
public static List <SnippetInfo> GetFilteredSnippeds(string name, string CodeLanguage, DateFilter dateFilter, TypeFilter typeFilter)
{
//Create Temp List
List <SnippetInfo> Temp = new List <SnippetInfo>();
//create connection and open it
MySqlConnection connection = DbInfo.Connection();
//Build Mysql command
MySqlCommand cmd = connection.CreateCommand();
//The Default cmd String
string cmdString = "SELECT `ID`, `UserID`, `TagCollectionID`, `Name`, `Code`, `CodeEditDate`, `UsageExample`, `UsageEditDate`, `Description`, `DescriptionEditDate`, `LanguageID`, `CreateDate` FROM `codesnippets` ";
//If filter not is all add where string
if (CodeLanguage != "All")
{
cmdString += "WHERE `LanguageID` = @LanguageID";
}
//Add the CommandText
cmd.CommandText = cmdString;
//Add Parameter
cmd.Parameters.AddWithValue("@LanguageID", DbCodeLanguage.ToID(CodeLanguage));
//Create reader
MySqlDataReader reader = cmd.ExecuteReader();
//While reading
while (reader.Read())
{
//Save a new Temporary Snippet
SnippetInfo TempSnippet = (new SnippetInfo(
int.Parse(reader["ID"].ToString()),
int.Parse(reader["UserID"].ToString()),
int.Parse(reader["TagCollectionID"].ToString()),
reader["Name"].ToString(),
reader["Code"].ToString(),
DateTime.Parse(reader["CodeEditDate"].ToString()),
reader["UsageExample"].ToString(),
DateTime.Parse(reader["UsageEditDate"].ToString()),
reader["Description"].ToString(),
DateTime.Parse(reader["DescriptionEditDate"].ToString()),
int.Parse(reader["LanguageID"].ToString()),
DateTime.Parse(reader["CreateDate"].ToString())
));
//If User is owner of this CodeSnippet
if (int.Parse(reader["UserID"].ToString()) == UserInfo.Userinformation.ID)
{
//Add snipped Based on TypeFilter
switch (typeFilter)
{
case TypeFilter.SnippedName:
if (TempSnippet._Name.ToLower().Contains(name.ToLower()))
{
Temp.Add(TempSnippet);
}
break;
case TypeFilter.Code:
if (TempSnippet._Code.ToLower().Contains(name.ToLower()))
{
Temp.Add(TempSnippet);
}
break;
case TypeFilter.Description:
if (TempSnippet._Description.ToLower().Contains(name.ToLower()))
{
Temp.Add(TempSnippet);
}
break;
case TypeFilter.Usage:
if (TempSnippet._UsageExample.ToLower().Contains(name.ToLower()))
{
Temp.Add(TempSnippet);
}
break;
}
//Filter List Based on DateFilter
switch (dateFilter)
{
case DateFilter.Newest:
SortAscending(Temp);
break;
case DateFilter.Oldest:
SortDescending(Temp);
break;
}
}
}
//Close Connection
connection.Close();
//Return Temp List
return(Temp);
}
