public Category GetCategoryByID(int id)
{
var category = new Category();
using (SqlConnection cn = new SqlConnection(_connectionString))
{
var p = new DynamicParameters();
p.Add("CategoryID", id);
category = cn.QuerySingle<Category>("GetCategory", p,
commandType: CommandType.StoredProcedure);
}
return category;
}
/**
* Check if person exists in person table, if
* personal_NO correspond in one from the table
* we do not insert person entity in table
* */
private bool isPersonInDb(string personal_NO)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(@"Data Source=.;Initial Catalog=checkedbythepolice;Integrated Security=True"))
{
connection.Open();
using (IDbTransaction transaction = connection.BeginTransaction())
{
try
{
int hasDuplicates = connection.QuerySingle <int>($"select count(personal_NO) from person where personal_NO = '{personal_NO}'", transaction: transaction);
return(hasDuplicates == 0 ? false : true);
}
catch (Exception ex)
{
throw ex;
}
}
}
}
public User GetUserByNameAndPassword(string username, string password)
{
User authorizedUser = new User();
var parameters = new { UserName = username, Password = password };
const string sql = "SELECT * FROM Users WHERE UserName = @UserName AND Password = @Password";
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(DatabaseHelper.CnnVal("NeptuneDB")))
{
try
{
var result = connection.QuerySingle <User>(sql, parameters);
authorizedUser.UserId = result.UserId;
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Oops! There was an exception!", MessageBoxButton.OK);
throw;
}
}
return(authorizedUser);
}
public StaticPage GetStaticPageById(int id)
{
var page = new StaticPage();
using (SqlConnection cn = new SqlConnection(_connectionString))
{
var p = new DynamicParameters();
p.Add("StaticPageID", id);
page = cn.QuerySingle<StaticPage>("GetStaticPage", p,
commandType: CommandType.StoredProcedure);
}
return page;
}
