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