//Method executes 'Delete Article' proceedure:
public static bool deleteArticle(long articleId)
{
bool result = false;
SqlConnection cnctn = null;
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "deleteArticle";
cmd.Parameters.Add("@articleArticleid", System.Data.SqlDbType.BigInt).Value = articleId;
try
{
cnctn = new SqlConnection(GlobalFunctions.getConnectionString());
cmd.Connection = cnctn;
cnctn.Open();
int rc = cmd.ExecuteNonQuery();
if (rc == 1)
{
result = true;
}
}
catch
{
}
finally
{
if (cnctn != null)
{
cnctn.Close();
}
}
return(result);
}
// Method executes 'Add User' procedure:
public static int addUser(User user, out ConnectedUser connectedUser)
{
byte[] data = null;
int finalResult = 0;
ConnectedUser tempUser = null;
SqlConnection cnctn = null;
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "addUser";
if (null != user.imageFileName)
{
MemoryStream target = new MemoryStream();
user.imageFileName.InputStream.CopyTo(target);
data = target.ToArray();
cmd.Parameters.Add("@userUserpicture", System.Data.SqlDbType.VarBinary, -1).Value = data;
}
cmd.Parameters.Add("@userEmail", System.Data.SqlDbType.NVarChar, 254).Value = user.email;
cmd.Parameters.Add("@userActualname", System.Data.SqlDbType.NVarChar, 60).Value = user.realname;
cmd.Parameters.Add("@userPassword", System.Data.SqlDbType.VarBinary, 60).Value = GlobalFunctions.getEncriptedPassword(user.password);
try
{
cnctn = new SqlConnection(GlobalFunctions.getConnectionString());
cmd.Connection = cnctn;
cnctn.Open();
int rc = cmd.ExecuteNonQuery();
if (rc == 1)
{
finalResult = getUserNumber(user.email);
tempUser = new ConnectedUser(finalResult, user.email, user.realname);
if (null != data)
{
tempUser.userPicture = data;
}
}
else
{
tempUser = null;
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
if (cnctn != null)
{
cnctn.Close();
}
}
connectedUser = tempUser;
return(finalResult);
}
//Method search for articles by given criteria:
public static List <Article> searchForArticles(string searchCriterea)
{
List <Article> rslt = new List <Article>();
SqlConnection cnctn = null;
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "searchArticles";
cmd.Parameters.Add("@searchCriteria", System.Data.SqlDbType.VarChar, -1).Value = searchCriterea;
SqlDataReader rdr = null;
int i = 0;
try
{
cnctn = new SqlConnection(GlobalFunctions.getConnectionString());
cmd.Connection = cnctn;
cnctn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Article tempArticle = new Article
{
articleId = (Int64)rdr["Articleid"],
articleName = rdr["Articletitle"].ToString(),
articleContent = rdr["Articlecntnt"].ToString(),
date = (DateTime)rdr["Cngdt"],
userId = (int)rdr["Ownerid"],
isPublic = (bool)rdr["Articlepublic"]
};
rslt.Add(tempArticle);
i++;
}
if (i == 0)
{
rslt = null;
}
}
catch// (Exception prblm)
{
rslt = null;
}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (cnctn != null)
{
cnctn.Close();
}
}
return(rslt);
}
// Method executes 'Login' procedure:
public static int ifUserExists(string email, string password, out ConnectedUser connectedUser)
{
int result = 0;
ConnectedUser tempUser = null;
SqlConnection cnctn = null;
SqlCommand cmd = new SqlCommand();
SqlDataReader rdr = null;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "ifUserExists";
cmd.Parameters.Add("@userEmail", System.Data.SqlDbType.NVarChar, 254).Value = email;
try
{
cnctn = new SqlConnection(GlobalFunctions.getConnectionString());
cmd.Connection = cnctn;
cnctn.Open();
rdr = cmd.ExecuteReader();
if (rdr.Read())
{
//Check if passed password is right:
if (GlobalFunctions.checkPassword((byte[])rdr["Passowrd"], password))
{
tempUser = new ConnectedUser((int)rdr["Usernum"], email, rdr["Actualname"].ToString());
try
{
tempUser.userPicture = (byte[])rdr["Userpicture"];
}
catch { }
result = 1;
}
}
else
{
tempUser = null;
result = 0; // incorect username or password or the combination!
}
rdr.Close();
}
catch //(Exception prblm)
{
tempUser = null;
result = -1;
}
finally
{
cnctn.Close();
}
connectedUser = tempUser;
return(result);
}
//Method executes 'Select Article' procedure:
public static Article getArticleById(long id)
{
Article rslt = null;
SqlConnection cnctn = null;
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "selectArticleById";
cmd.Parameters.Add("@articleArticleid", System.Data.SqlDbType.BigInt).Value = id;
SqlDataReader rdr = null;
try
{
cnctn = new SqlConnection(GlobalFunctions.getConnectionString());
cmd.Connection = cnctn;
cnctn.Open();
rdr = cmd.ExecuteReader();
if (rdr.Read())
{
rslt = new Article
{
articleId = (Int64)rdr["Articleid"],
articleName = rdr["Articletitle"].ToString(),
articleContent = rdr["Articlecntnt"].ToString(),
date = (DateTime)rdr["Cngdt"],
userId = (int)rdr["Ownerid"],
isPublic = (bool)rdr["Articlepublic"]
};
}
}
catch// (Exception prblm)
{
}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (cnctn != null)
{
cnctn.Close();
}
}
return(rslt);
}
//Get User number:
public static int getUserNumber(string email)
{
int result = 0;
SqlConnection cnctn = null;
SqlCommand cmd = new SqlCommand();
SqlDataReader rdr = null;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "ifUserExists";
cmd.Parameters.Add("@userEmail", System.Data.SqlDbType.NVarChar, 254).Value = email;
try
{
cnctn = new SqlConnection(GlobalFunctions.getConnectionString());
cmd.Connection = cnctn;
cnctn.Open();
rdr = cmd.ExecuteReader();
if (rdr.Read())
{
result = (int)rdr["Usernum"];
}
else
{
result = 0; // incorect username or password or the combination!
}
rdr.Close();
}
catch //(Exception prblm)
{
result = -1;
}
finally
{
cnctn.Close();
}
return(result);
}
// Method executes 'Update Article' procedure:
public static bool updateArticle(Article article)
{
bool result = false;
SqlConnection cnctn = null;
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "editArticle";
cmd.Parameters.Add("@articleArticletitle", System.Data.SqlDbType.NVarChar, 128).Value = article.articleName;
cmd.Parameters.Add("@articleCngdt", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("@articleArticlecntnt", System.Data.SqlDbType.NVarChar, -1).Value = article.articleContent;
cmd.Parameters.Add("@articleArticleid", System.Data.SqlDbType.BigInt, -1).Value = article.articleId;
cmd.Parameters.Add("@articleArticlepublic", System.Data.SqlDbType.Bit, 1).Value = article.isPublic;
try
{
cnctn = new SqlConnection(GlobalFunctions.getConnectionString());
cmd.Connection = cnctn;
cnctn.Open();
int rc = cmd.ExecuteNonQuery();
if (rc == 1)
{
result = true;
}
}
catch
{
}
finally
{
if (cnctn != null)
{
cnctn.Close();
}
}
return(result);
}
//Method executes 'Get User ID' procedure:
private static int getUserID(string email)
{
SqlConnection cnctn = null;
SqlCommand cmd = new SqlCommand();
SqlDataReader rdr = null;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "getUserID";
cmd.Parameters.Add("@userEmail", System.Data.SqlDbType.VarChar, 254).Value = email;
int result = -777;
try
{
cnctn = new SqlConnection(GlobalFunctions.getConnectionString());
cmd.Connection = cnctn;
cnctn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
result = (int)rdr["Usernum"];
break;
}
}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (cnctn != null)
{
cnctn.Close();
}
}
return(result);
}