private void InsertDefaultValues()
{
CountryInfo country = new CountryInfo();
country.Name = "International";
country.Code = "None";
CCountryGateway countryGateway = new CCountryGateway();
countryGateway.Create(country);
Upgrade();
}
internal override void GetVersion()
{
/*this version is for putting default values in db (the names of all existing countries)*/
/*-- get countries from text file --*/
if (File.Exists(_fileWithCountries))
{
List <CountryInfo> countries = new List <CountryInfo>();
using (StreamReader sr = new StreamReader(_fileWithCountries))
{
String ln;
while ((ln = sr.ReadLine()) != null)
{
String[] arr = ln.Split(' ');
List <String> arr2 = new List <string>();
foreach (var s in arr)
{
if (s != "")
{
arr2.Add(s);
}
}
StringBuilder str = new StringBuilder();
for (var i = 0; i < arr2.Count - 3; i++)
{
str.AppendFormat(arr2[i]);
str.AppendFormat(" ");
}
countries.Add(new CountryInfo
{
Code = arr2[arr2.Count - 2],
Name = str.ToString()
});
}
}
CCountryGateway countryGateway = new CCountryGateway();
foreach (var country in countries)
{
countryGateway.Create(country);
}
Upgrade();
}
else
{
Console.WriteLine($"Can't find file {_fileWithCountries}");
}
}
public ArticleInfo Get(Int32 id)
{
ArticleInfo article = new ArticleInfo();
String sqlExpression = "sp_GetArticleById";
CCountryGateway countryGateway = new CCountryGateway();
CTagGateway tagGateway = new CTagGateway();
using (SqlConnection conn = CDbConnection.GetConnection())
{
SqlCommand command = new SqlCommand(sqlExpression, conn);
command.CommandType = CommandType.StoredProcedure;
SqlParameter idParam = new SqlParameter
{
ParameterName = "@articleId",
Value = id
};
command.Parameters.Add(idParam);
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
Int32 countryId = (Int32)reader["CountryID"];
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[5];
article.Country = countryGateway.Get(countryId);
article.Tags = tagGateway.GetAllRelatedToArticle(article.ID).Select(x => tagGateway.Get(x)).ToArray();
}
}
}
}
return(article);
}
public List <ArticleInfo> GetByTag(CTag tag)
{
List <ArticleInfo> articles = new List <ArticleInfo>();
CCountryGateway countryGateway = new CCountryGateway();
String sqlExpression = "sp_GetArticleByTag";
using (SqlConnection conn = CDbConnection.GetConnection())
{
SqlCommand cmd = new SqlCommand(sqlExpression, conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter tagParam = new SqlParameter
{
ParameterName = "@tag",
Value = tag.ID
};
cmd.Parameters.Add(tagParam);
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
ArticleInfo article = new ArticleInfo();
article.Country = countryGateway.Get((Int32)reader["CountryID"]);
article.Date = (DateTime)reader["Date"];
article.Content = (String)reader["Content"];
article.Html = (byte[])reader["Html"];
article.Url = (String)reader["Url"];
article.Title = (String)reader["Title"];
article.ID = (Int32)reader["ID"];
articles.Add(article);
}
}
}
}
return(articles);
}
public CJob GetByQueryInfo(QueryInfo info)
{
CCountryGateway countryGateway = new CCountryGateway();
CJob job = new CJob();
String sqlExpression = "sp_GetJobByUserQuery";
// $@" WITH JobTable AS (SELECT Job.ID AS JobID, CurPairNamed.BaseCurrency, CurPairNamed.QuotedCurrency,Country.Name AS Country, Tag.Name AS Tag
// FROM [ONLOOKER].[dbo].[Job] AS Job
// JOIN [ONLOOKER].[dbo].[Country] AS Country
// ON Job.[CountryID] = Country.ID
// Join [ONLOOKER].[dbo].[Tag] AS Tag
// ON Job.TagID = Tag.ID
// JOIN(
//SELECT CurPair.ID, CurTypeBase.CurrencyCode AS BaseCurrency, CurTypeQuoted.CurrencyCode AS QuotedCurrency
//FROM [ONLOOKER].[dbo].[CurrencyPair] AS CurPair
//JOIN [ONLOOKER].[dbo].[CurrencyType] AS CurTypeBase
//ON CurPair.BaseCurrencyID = CurTypeBase.ID
//JOIN [ONLOOKER].[dbo].[CurrencyType] AS CurTypeQuoted
//ON CurPair.BaseCurrencyID = CurTypeQuoted.ID
//) AS CurPairNamed
//ON CurPairNamed.ID = Job.CurrencyPairID)
//SELECT * FROM JobTable
//WHERE BaseCurrency = '{info.CurrencyPair.BaseCurrency.Code}' AND QuotedCurrency = '{info.CurrencyPair.QuotedCurrency.Code}' AND Country = '{info.Country.Name}' AND Tag = '{info.Tag.Value}'";
using (SqlConnection conn = CDbConnection.GetConnection())
{
SqlCommand command = new SqlCommand(sqlExpression, conn);
command.CommandType = CommandType.StoredProcedure;
SqlParameter baseParam = new SqlParameter
{
ParameterName = "@base",
Value = info.CurrencyPair.BaseCurrency.Code
};
command.Parameters.Add(baseParam);
SqlParameter quotedParam = new SqlParameter
{
ParameterName = "@quoted",
Value = info.CurrencyPair.QuotedCurrency.Code
};
command.Parameters.Add(quotedParam);
SqlParameter countryParam = new SqlParameter
{
ParameterName = "@country",
Value = info.Country.Name
};
command.Parameters.Add(countryParam);
SqlParameter tagParam = new SqlParameter
{
ParameterName = "@tag",
Value = info.Tag.Value
};
command.Parameters.Add(tagParam);
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
job.CurrencyPair = new CCurrencyPair()
{
BaseCurrency = (CurrencyInfo)reader["BaseCurrency"], QuotedCurrency = (CurrencyInfo)reader["QuotedCurrency"]
};
job.ID = (Int32)reader["JobID"];
job.Tag = new CTag((String)reader["Tag"]);
job.Country = countryGateway.GetAll().FirstOrDefault(x => String.Compare(x.Name, (String)reader["Country"], StringComparison.InvariantCulture) == 0);
}
}
else
{
job = null;
}
}
}
return(job);
}