public List <CountryInfo> GetAll()
        {
            String             sqlExpression = "sp_GetAllCountries";
            List <CountryInfo> countryList   = new List <CountryInfo>();

            using (SqlConnection conn = CDbConnection.GetConnection())
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sqlExpression, conn);
                command.CommandType = CommandType.StoredProcedure;
                using (var reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            CountryInfo country = new CountryInfo();
                            country.Name = (String)reader["Name"];
                            country.ID   = (Int32)reader["ID"];
                            country.Code = (String)reader["Code"];
                            countryList.Add(country);
                        }
                    }
                }
            }

            return(countryList);
        }
        public CountryInfo Get(Int32 id)
        {
            String      sqlExpression = "sp_GetCountryById";
            CountryInfo country       = new CountryInfo();

            using (SqlConnection conn = CDbConnection.GetConnection())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(sqlExpression, conn);
                command.CommandType = System.Data.CommandType.StoredProcedure;

                SqlParameter urlParam = new SqlParameter
                {
                    ParameterName = "@id",
                    Value         = id
                };
                command.Parameters.Add(urlParam);
                var reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        country.Name = (string)reader["Name"];
                        country.Code = (string)reader["Code"];
                        country.ID   = (int)reader["ID"];
                    }
                }

                reader.Close();
            }

            return(country);
        }
Exemple #3
0
        public void AddRelation(Int32 tagid, Int32 articleid)
        {
            String sqlExpression = "sp_InsertArticleTag";

            using (SqlConnection conn = CDbConnection.GetConnection())
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sqlExpression, conn);
                command.CommandType = System.Data.CommandType.StoredProcedure;

                SqlParameter articleIdParam = new SqlParameter
                {
                    ParameterName = "@articleId",
                    Value         = articleid
                };
                command.Parameters.Add(articleIdParam);
                SqlParameter tagIdParam = new SqlParameter
                {
                    ParameterName = "@tagId",
                    Value         = tagid
                };
                command.Parameters.Add(tagIdParam);

                var result = command.ExecuteScalar();

                Console.WriteLine("Id добавленного объекта: {0}", result);
            }
        }
Exemple #4
0
        public List <string> GetAllNames()
        {
            String        sqlExpression = "sp_GetTags";
            List <string> tags          = new List <string>();

            using (SqlConnection conn = CDbConnection.GetConnection())
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sqlExpression, conn);

                command.CommandType = System.Data.CommandType.StoredProcedure;
                var reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        String value = (string)reader["Name"];
                        tags.Add(value);
                    }
                }

                reader.Close();
            }

            return(tags);
        }
Exemple #5
0
        public Int32 GetByName(String name)
        {
            String        sqlExpression = "sp_GetTagByName";
            List <string> tags          = new List <string>();
            Int32         value         = 0;                                  //todo: come up with something more clever with value

            using (SqlConnection conn = CDbConnection.GetConnection())
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sqlExpression, conn);
                command.CommandType = System.Data.CommandType.StoredProcedure;

                SqlParameter nameParam = new SqlParameter
                {
                    ParameterName = "@name",
                    Value         = name
                };
                command.Parameters.Add(nameParam);

                var reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        value = (int)reader["ID"];
                    }
                }

                reader.Close();
            }

            return(value);
        }
Exemple #6
0
        public Int32 Create(CTag tag)
        {
            String sqlExpression = "sp_InsertTag";

            using (SqlConnection conn = CDbConnection.GetConnection())
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sqlExpression, conn);
                command.CommandType = System.Data.CommandType.StoredProcedure;

                SqlParameter urlParam = new SqlParameter
                {
                    ParameterName = "@name",
                    Value         = tag.Value
                };
                command.Parameters.Add(urlParam);

                var result = command.ExecuteScalar();

                Console.WriteLine("Id добавленного объекта: {0}", result);
                String value = result.ToString();
                Int32  id    = int.Parse(value);
                return(id);
            }
        }
Exemple #7
0
        static void Main(string[] args)
        {
            _currentDomain.ProcessExit        += CurrentDomain_ProcessExit;
            _currentDomain.UnhandledException += ExceptionsHandler;
            Console.WriteLine(@"Hello! This is OnLooker App!");

            try
            {
                CDbDeploy.ConnectDataBase();
                CDbDeploy.Update();
                var t = new Thread(Go);

                t.Start();
                t.Join();
            }
            catch (Exception e)
            {
                SLogger.Log.Fatal("Unexpected termination: " + e.Message);
                Console.WriteLine($"Error: {e.Message}\nPress any key to close application");
            }
            finally
            {
                CDbConnection.ConnectionClose();
            }

            Console.ReadLine();
        }
        public List <ArticleInfo> GetAll()
        {
            String             sqlExpression  = "sp_GetArticles";
            CCountryGateway    countryGateway = new CCountryGateway();
            List <ArticleInfo> articles       = new List <ArticleInfo>();
            CTag articleTag = new CTag();

            using (SqlConnection conn = CDbConnection.GetConnection())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(sqlExpression, conn);
                command.CommandType = CommandType.StoredProcedure;

                using (var reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            articleTag.Value = (String)reader["Tag"];
                            Int32       countryId = (Int32)reader["CountryID"];
                            ArticleInfo article   = new ArticleInfo();
                            article.ID      = (Int32)reader["ID"];
                            article.Url     = (String)reader["URL"];
                            article.Title   = (String)reader["Title"];
                            article.Content = (String)reader["Content"];
                            article.Html    = (byte[])reader["HTML"];
                            article.Date    = (DateTime)reader[6];              //todo: fix the problem with Date  -> rename column?
                            article.Country = countryGateway.Get(countryId);
                            article.Tag     = articleTag;

                            articles.Add(article);
                        }
                    }
                }
            }

            return(articles);
        }
        public Int32 Create(CountryInfo entity)
        {
            Int32       id;
            var         insertCountry = "sp_InsertCountry";
            CountryInfo country       = new CountryInfo();

            using (SqlConnection conn = CDbConnection.GetConnection())
            {
                conn.Open();
                SqlCommand command = new SqlCommand(insertCountry, conn);
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter name = new SqlParameter
                {
                    ParameterName = "@name",
                    Value         = entity.Name
                };
                command.Parameters.Add(name);
                SqlParameter code = new SqlParameter
                {
                    ParameterName = "@code",
                    Value         = entity.Code
                };
                command.Parameters.Add(code);
                try
                {
                    var result = command.ExecuteScalar();
                    Console.WriteLine("Id добавленного объекта: {0}", result);
                    var value = result.ToString();
                    Int32.TryParse(value, out id);
                    return(id);
                }
                catch (Exception e)
                {
                    SLogger.Log.Fatal($"CCountryGateway.Create method. Exception: {e.Message}");
                    return(-1);
                }
            }
        }
        public Int32 Create(ArticleInfo article)
        {
            String sqlExpression = "sp_InsertArticle";

            using (SqlConnection conn = CDbConnection.GetConnection())
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sqlExpression, conn);
                command.CommandType = CommandType.StoredProcedure;

                SqlParameter urlParam = new SqlParameter
                {
                    ParameterName = "@url",
                    Value         = article.Url
                };
                command.Parameters.Add(urlParam);
                SqlParameter titleParam = new SqlParameter
                {
                    ParameterName = "@title",
                    Value         = article.Title
                };
                command.Parameters.Add(titleParam);
                SqlParameter contentParam = new SqlParameter
                {
                    ParameterName = "@content",
                    Value         = article.Content
                };
                command.Parameters.Add(contentParam);
                SqlParameter htmlParam = new SqlParameter
                {
                    ParameterName = "@html",
                    Value         = article.Html
                };
                command.Parameters.Add(htmlParam);
                SqlParameter dateParam = new SqlParameter
                {
                    ParameterName = "@date",
                    Value         = article.Date
                };
                command.Parameters.Add(dateParam);
                SqlParameter countrieParam = new SqlParameter
                {
                    ParameterName = "@countryid",
                    Value         = article.Country.ID
                };
                command.Parameters.Add(countrieParam);
                try
                {
                    var result = command.ExecuteScalar();
                    Console.WriteLine("Id добавленного объекта: {0}", result);
                    var value = result.ToString();
                    Int32.TryParse(value, out id);
                    return(id);
                }
                catch (Exception e)
                {
                    SLogger.Log.Fatal($"CArticleGateway.Create method. Exception: {e.Message}");
                    return(-1);
                }
            }
        }
Exemple #11
0
 static void CurrentDomain_ProcessExit(object sender, EventArgs e)
 {
     SLogger.Log.Debug($"Application terminated {e}");
     CDbConnection.ConnectionClose();
 }