Beispiel #1
0
        //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);
        }
Beispiel #3
0
        //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);
        }
Beispiel #5
0
        //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);
        }
Beispiel #7
0
        // 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);
        }
Beispiel #8
0
        //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);
        }