public List <CountryInfo> GetAll()
{
String sqlExpression = "sp_GetAllCountries";
List <CountryInfo> countryList = new List <CountryInfo>();
using (SqlConnection conn = CDbConnection.GetConnection())
{
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);
}
} //todo: add 'else' condition
}
}
return(countryList);
}
public void AddRelation(Int32 tagid, Int32 articleid)
{
String sqlExpression = "sp_InsertArticleTag";
using (SqlConnection conn = CDbConnection.GetConnection())
{
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 GetByLoginId(Int32 id)
{
Int32 userId = 0;
var insertCredentials = "sp_GetUserByLoginId";
using (SqlConnection conn = CDbConnection.GetConnection())
{
SqlCommand command = new SqlCommand(insertCredentials, conn);
command.CommandType = CommandType.StoredProcedure;
SqlParameter idParam = new SqlParameter
{
ParameterName = "@id",
Value = id
};
command.Parameters.Add(idParam);
try
{
var reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
userId = (Int32)reader["UserID"];
}
}
}
catch (Exception e)
{
//SLogger.Log.Fatal($"CCountryGateway.Create method. Exception: {e.Message}");
throw new Exception("Unhendeled exception" + e.Message);
}
return(userId);
}
}
public UserInfo Get(Int32 id)
{
UserInfo user = new UserInfo();
String sqlExpression = "sp_GetUserById";
using (SqlConnection conn = CDbConnection.GetConnection())
{
SqlCommand command = new SqlCommand(sqlExpression, conn);
command.CommandType = CommandType.StoredProcedure;
SqlParameter idParam = new SqlParameter
{
ParameterName = "@Id",
Value = id
};
command.Parameters.Add(idParam);
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
user.Name = (String)reader["Name"];
user.Email = (String)reader["Email"];
user.ID = (Int32)reader["ID"];
}
}
else
{
user = null;
}
}
}
return(user);
}
public List <string> GetAllNames()
{
String sqlExpression = "sp_GetTags";
List <String> tags = new List <String>();
using (SqlConnection conn = CDbConnection.GetConnection())
{
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 List <CTag> GetAll()
{
String sqlExpression = "sp_GetTags";
List <CTag> tags = new List <CTag>();
using (SqlConnection conn = CDbConnection.GetConnection())
{
SqlCommand command = new SqlCommand(sqlExpression, conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
var reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var tag = new CTag();
tag.Value = (String)reader["Name"];
tag.ID = (Int32)reader["ID"];
tags.Add(tag);
}
}
}
return(tags);
}
public List <CCurrencyPair> GetAll()
{
String sqlExpression = "sp_GetAllCurrencyPairs";
List <CCurrencyPair> pairs = new List <CCurrencyPair>();
CCurrencyGateway curInfoGateway = new CCurrencyGateway();
using (SqlConnection conn = CDbConnection.GetConnection())
{
SqlCommand command = new SqlCommand(sqlExpression, conn);
command.CommandType = CommandType.StoredProcedure;
var reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
CCurrencyPair pair = new CCurrencyPair();
pair.BaseCurrency = curInfoGateway.Get((Int32)reader["BaseCurrencyID"]);
pair.QuotedCurrency = curInfoGateway.Get((Int32)reader["QuotedCurrencyID"]);
pair.ID = (Int32)reader["ID"];
pairs.Add(pair);
}
}
reader.Close();
}
return(pairs);
}
public int Create(CCurrencyPair entity)
{
Int32 id;
var sqlExspression = "sp_InsertCurrencyPair";
using (SqlConnection conn = CDbConnection.GetConnection())
{
SqlCommand command = new SqlCommand(sqlExspression, conn);
command.CommandType = CommandType.StoredProcedure;
SqlParameter baseCur = new SqlParameter
{
ParameterName = "@base",
Value = entity.BaseCurrency.ID
};
command.Parameters.Add(baseCur);
SqlParameter quotedCur = new SqlParameter
{
ParameterName = "@quoted",
Value = entity.QuotedCurrency.ID
};
command.Parameters.Add(quotedCur);
try
{
var result = command.ExecuteScalar();
Console.WriteLine("Id добавленного объекта: {0}", result);
Int32.TryParse(result.ToString(), out id);
}
catch (Exception e)
{
throw new Exception("Unhandeled exception" + e.Message);
}
return(id);
}
}
public CurrencyInfo Get(Int32 id)
{
String sqlExpression = "sp_GetCurrencyById";
CurrencyInfo currency = new CurrencyInfo();
using (SqlConnection conn = CDbConnection.GetConnection())
{
SqlCommand command = new SqlCommand(sqlExpression, conn);
command.CommandType = CommandType.StoredProcedure;
SqlParameter urlParam = new SqlParameter
{
ParameterName = "@id",
Value = id
};
command.Parameters.Add(urlParam);
var reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
currency.Code = (String)reader["CurrencyCode"];
currency.Name = (String)reader["CurrencyName"];
currency.ID = (Int32)reader["ID"];
}
}
reader.Close();
}
return(currency);
}
public List <CurrencyInfo> GetAll()
{
var sqlExpression = "sp_GetCurrencies";
var currencies = new List <CurrencyInfo>();
using (var conn = CDbConnection.GetConnection())
{
SqlCommand command = new SqlCommand(sqlExpression, conn);
command.CommandType = CommandType.StoredProcedure;
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
var currencyInfo = new CurrencyInfo();
currencyInfo.Name = (String)reader["CurrencyName"];
currencyInfo.Code = (String)reader["CurrencyCode"];
currencies.Add(currencyInfo);
}
}
}
}
return(currencies);
}
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())
{
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 void Update(CTag tag)
{
String sqlExpression = "sp_UpdateTag";
using (SqlConnection conn = CDbConnection.GetConnection())
{
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);
SqlParameter idParam = new SqlParameter
{
ParameterName = "@id",
Value = tag.ID
};
command.Parameters.Add(idParam);
var result = command.ExecuteScalar();
Console.WriteLine("Id обновлённого в Tag объекта: {0}", tag.ID);
}
}
public CTag Get(Int32 id)
{
String sqlExspression = "sp_GetTagById";
CTag tag = new CTag();
using (SqlConnection conn = CDbConnection.GetConnection())
{
SqlCommand command = new SqlCommand(sqlExspression, conn);
command.CommandType = CommandType.StoredProcedure;
SqlParameter tagIdParam = new SqlParameter
{
ParameterName = "@tagId",
Value = id
};
command.Parameters.Add(tagIdParam);
var reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
tag.Value = (String)reader["Name"];
tag.ID = (Int32)reader["ID"];
}
reader.Close();
}
}
return(tag);
}
internal override void GetVersion()
{
/*this version is for putting default values in db (the names of all existing currencies)*/
/*-- get currencies from json file --*/
using (SqlConnection conn = CDbConnection.GetConnection())
{
SqlCommand cmd = new SqlCommand(_createProcedureInsertScript, conn);
SqlCommand cmd2 = new SqlCommand(_createProcedureGetScript, conn);
try
{
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception("Unhandled exception " + e.Message);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
CDbConnection.IsCreating = false;
}
InsertDefaultValues();
}
}
public Int32[] GetAllRelatedToArticle(Int32 id)
{
String sqlExpression = "sp_GetTagsRelatedToArticle";
List <Int32> tags = new List <Int32>();
using (SqlConnection conn = CDbConnection.GetConnection())
{
SqlCommand command = new SqlCommand(sqlExpression, conn);
command.CommandType = CommandType.StoredProcedure;
SqlParameter articleParam = new SqlParameter
{
ParameterName = "@articleId",
Value = id
};
command.Parameters.Add(articleParam);
var reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
tags.Add((Int32)reader["TagID"]);
}
}
reader.Close();
}
return(tags.ToArray());
}
public Int32 Create(AVersion entity)
{
Int32 id;
String insertVersion = "sp_InsertVersion";
using (SqlConnection conn = CDbConnection.GetConnection())
{
SqlCommand command = new SqlCommand(insertVersion, conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter major = new SqlParameter
{
ParameterName = "@major",
Value = entity.Major
};
command.Parameters.Add(major);
SqlParameter minor = new SqlParameter
{
ParameterName = "@minor",
Value = entity.Minor
};
command.Parameters.Add(minor);
SqlParameter filenumber = new SqlParameter
{
ParameterName = "@filenumber",
Value = entity.FileNumber
};
command.Parameters.Add(filenumber);
SqlParameter comment = new SqlParameter
{
ParameterName = "@comment",
Value = entity.Comment
};
command.Parameters.Add(comment);
SqlParameter date = new SqlParameter
{
ParameterName = "@date",
Value = DateTime.Now
};
command.Parameters.Add(date);
try
{
var result = command.ExecuteScalar();
Console.WriteLine("Id добавленного объекта: {0}", result);
var value = result.ToString();
Int32.TryParse(value, out id);
id = 1;
}
catch (Exception e)
{
//SLogger.Log.Fatal($"CMigrationGateway.Create method. Exception: {e.Message}");
id = -1;
throw new Exception("Unhendeled exception" + e.Message);
}
return(id);
}
}
public List <CJob> GetAll()
{
List <CJob> jobs = new List <CJob>();
String sqlExpression = "sp_GetAllJobs";
// @"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.CurrencyName AS BaseCurrency, CurTypeQuoted.CurrencyName 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
//";
using (SqlConnection conn = CDbConnection.GetConnection())
{
SqlCommand command = new SqlCommand(sqlExpression, conn);
command.CommandType = CommandType.StoredProcedure;
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
CJob job = new CJob();
job.CurrencyPair = new CCurrencyPair()
{
BaseCurrency = (CurrencyInfo)reader["BaseCurrency"], QuotedCurrency = (CurrencyInfo)reader["QuotedCurrency"]
};
job.ID = (Int32)reader["JobID"];
job.Tag = new CTag((String)reader["Tag"]);
jobs.Add(job);
}
}
else
{
jobs = null;
}
}
}
return(jobs);
}
public void Update(ArticleInfo article)
{
String sqlExpression = "sp_UpdateArticle";
using (SqlConnection conn = CDbConnection.GetConnection())
{
SqlCommand command = new SqlCommand(sqlExpression, conn);
command.CommandType = CommandType.StoredProcedure;
SqlParameter idParam = new SqlParameter
{
ParameterName = "@id",
Value = article.ID
};
command.Parameters.Add(idParam);
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 countryParam = new SqlParameter
{
ParameterName = "@countryid",
Value = article.Country.ID
};
command.Parameters.Add(countryParam);
var result = command.ExecuteNonQuery();
Console.WriteLine("Количество обновлённых объектов: {0}", result);
}
}
public void Delete(Int32 id)
{
CUserInfoGateway userInfoGateway = new CUserInfoGateway();
var userId = userInfoGateway.GetByLoginId(id);
using (SqlConnection connection = CDbConnection.GetConnection())
{
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
command.Transaction = transaction;
try
{
// выполняем две отдельные команды
command.CommandText = "sp_DeleteLogin";
command.CommandType = CommandType.StoredProcedure;
SqlParameter idParam = new SqlParameter
{
ParameterName = "@id",
Value = id
};
command.Parameters.Add(idParam);
var result = command.ExecuteNonQuery();
command.CommandText = "sp_DeleteUser";
command.Parameters.Clear();
SqlParameter idParameter = new SqlParameter
{
ParameterName = "@id",
Value = userId
};
command.Parameters.Add(idParameter);
var result2 = command.ExecuteNonQuery();
transaction.Commit();
Console.WriteLine("Количество удалённых из UserLogin объектов: {0}", result);
Console.WriteLine("Количество удалённых из UserLogin объектов: {0}", result2);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
transaction.Rollback();
}
}
}
public CCurrencyPair Get(Int32 id)
{
String sqlExpression = "sp_GetCurrencyPairById";
CCurrencyPair pair = new CCurrencyPair();
CCurrencyGateway curInfoGateway = new CCurrencyGateway();
using (SqlConnection conn = CDbConnection.GetConnection())
{
SqlCommand command = new SqlCommand(sqlExpression, conn)
{
CommandType = CommandType.StoredProcedure
};
SqlParameter idParam = new SqlParameter
{
ParameterName = "@id",
Value = id
};
command.Parameters.Add(idParam);
try
{
var reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
pair.BaseCurrency = curInfoGateway.Get((Int32)reader["BaseCurrencyID"]);
pair.QuotedCurrency = curInfoGateway.Get((Int32)reader["QuotedCurrencyID"]);
pair.ID = (Int32)reader["ID"];
}
}
else
{
pair = null;
}
reader.Close();
}
catch (Exception e)
{
throw new Exception(e.Message + e.StackTrace);
}
}
return(pair);
}
public void Delete(Int32 id)
{
String sqlExpression = "sp_DeleteTag";
using (SqlConnection conn = CDbConnection.GetConnection())
{
SqlCommand command = new SqlCommand(sqlExpression, conn);
command.CommandType = CommandType.StoredProcedure;
SqlParameter idParam = new SqlParameter
{
ParameterName = "@aId",
Value = id
};
command.Parameters.Add(idParam);
var result = command.ExecuteNonQuery();
Console.WriteLine("Id удалённого из Tag объекта: {0}", id);
}
}
public CVersion GetLastVersion()
{
CVersion lastVersion = null;
var sqlExpression = "SELECT * FROM MigrationHistory WHERE DateApplied = (SELECT MAX(DateApplied) FROM MigrationHistory)";
using (SqlConnection conn = CDbConnection.GetConnection())
{
var cmd = new SqlCommand(sqlExpression, conn);
try
{
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
var major = (String)reader["MajorVersion"];
var minor = (String)reader["MinorVersion"];
var fileNumber = (String)reader["FileNumber"];
var comment = (String)reader["Comment"];
lastVersion = new CVersion(major, minor, fileNumber, comment);
}
}
else
{
Console.WriteLine(
"Migration history is empty or some error occured in SqlServer while reading it");
}
}
}
catch (Exception e)
{
throw new Exception("Unhendled exception" + e.Message);
}
}
return(lastVersion);
}
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 void Update(UserInfo entity)
{
var insertUser = "******";
using (SqlConnection conn = CDbConnection.GetConnection())
{
SqlCommand command = new SqlCommand(insertUser, conn);
command.CommandType = CommandType.StoredProcedure;
SqlParameter name = new SqlParameter
{
ParameterName = "@name",
Value = entity.Name
};
command.Parameters.Add(name);
SqlParameter id = new SqlParameter
{
ParameterName = "@id",
Value = entity.ID
};
command.Parameters.Add(id);
SqlParameter emailParam = new SqlParameter
{
ParameterName = "@email",
Value = entity.Email
};
command.Parameters.Add(emailParam);
try
{
var result = command.ExecuteScalar();
Console.WriteLine("Количество обновлённых объектов в UserInfo: {0}", result);
}
catch (Exception e)
{
//SLogger.Log.Fatal($"CCountryGateway.Create method. Exception: {e.Message}");
throw new Exception("Unhendeled exception" + e.Message);
}
}
}
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 void Update(CCurrencyPair entity)
{
var sqlExspression = "sp_UpdateCurrencyPair";
using (SqlConnection conn = CDbConnection.GetConnection())
{
SqlCommand command = new SqlCommand(sqlExspression, conn);
command.CommandType = CommandType.StoredProcedure;
SqlParameter idParam = new SqlParameter
{
ParameterName = "@id",
Value = entity.ID
};
command.Parameters.Add(idParam);
SqlParameter baseCur = new SqlParameter
{
ParameterName = "@base",
Value = entity.BaseCurrency.ID
};
command.Parameters.Add(baseCur);
SqlParameter quotedCur = new SqlParameter
{
ParameterName = "@quoted",
Value = entity.QuotedCurrency.ID
};
command.Parameters.Add(quotedCur);
try
{
var result = command.ExecuteNonQuery();
Console.WriteLine("Id обновлённого объекта: {0}", entity.ID);
}
catch (Exception e)
{
throw new Exception("Unhandeled exception" + e.Message);
}
}
}
public int Create(UserAuthInfo entity)
{
Int32 id;
var insertCredentials = "sp_InsertUserLogin";
using (SqlConnection conn = CDbConnection.GetConnection())
{
SqlCommand command = new SqlCommand(insertCredentials, conn);
command.CommandType = CommandType.StoredProcedure;
SqlParameter loginParam = new SqlParameter
{
ParameterName = "@login",
Value = entity.Login
};
command.Parameters.Add(loginParam);
SqlParameter passParam = new SqlParameter
{
ParameterName = "@password",
Value = entity.Password
};
command.Parameters.Add(passParam);
try
{
var result = command.ExecuteScalar();
Console.WriteLine("Id добавленного объекта: {0}", result);
Int32.TryParse(result.ToString(), out id);
}
catch (Exception e)
{
//SLogger.Log.Fatal($"CCountryGateway.Create method. Exception: {e.Message}");
throw new Exception("Unhendeled exception" + e.Message);
}
return(id);
}
}
public Int32 Create(CurrencyInfo entity)
{
Int32 id;
var insertCurrency = "sp_InsertCurrency";
using (SqlConnection conn = CDbConnection.GetConnection())
{
SqlCommand command = new SqlCommand(insertCurrency, 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);
}
catch (Exception e)
{
//SLogger.Log.Fatal($"CCurrencyGateway.Create method. Exception: {e.Message}");
throw new Exception("Unhandeled exception" + e.Message);
}
return(id);
}
}
public Int32 Create(CTag tag)
{
String sqlExpression = "sp_InsertTag";
using (SqlConnection conn = CDbConnection.GetConnection())
{
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 Int32 Create(UserInfo entity, UserAuthInfo login)
{
Int32 userId = 0;
CUserLoginGateway loginGateway = new CUserLoginGateway();
using (SqlConnection connection = CDbConnection.GetConnection())
{
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
command.Transaction = transaction;
command.CommandType = CommandType.StoredProcedure;
try
{
command.CommandText = "sp_InsertUserLogin";
SqlParameter loginParam = new SqlParameter
{
ParameterName = "@login",
Value = login.Login
};
command.Parameters.Add(loginParam);
SqlParameter passParam = new SqlParameter
{
ParameterName = "@password",
Value = login.Password
};
command.Parameters.Add(passParam);
var result = command.ExecuteScalar();
Int32.TryParse(result.ToString(), out Int32 loginId);
command.CommandText = "sp_InsertUser";
command.Parameters.Clear();
SqlParameter name = new SqlParameter
{
ParameterName = "@name",
Value = entity.Name
};
command.Parameters.Add(name);
SqlParameter emailParam = new SqlParameter
{
ParameterName = "@email",
Value = entity.Email
};
command.Parameters.Add(emailParam);
SqlParameter loginIdParam = new SqlParameter
{
ParameterName = "@loginId",
Value = loginId
};
command.Parameters.Add(loginIdParam);
result = command.ExecuteScalar();
Int32.TryParse(result.ToString(), out userId);
transaction.Commit();
Console.WriteLine($"Id добавленного объекта: {loginId}");
Console.WriteLine($"Id добавленного объекта: {userId} ");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
transaction.Rollback();
}
return(userId);
}
}
