/// <summary>
/// Set the enabled state of an account
/// </summary>
/// <param name="username">account username</param>
/// <param name="enabled">new enabled state</param>
/// <returns>true=state updated, false=state did not changed</returns>
public static bool SetAccountEnabled(string username, bool enabled)
{
// Connect to the MSSQL database
using (MSSqlConnection connection = MSSqlConnection.GetConnection())
{
// Get the table
Table <AccountTable> accounts = connection.GetTable <AccountTable>();
// Get the account
AccountTable account = accounts.FirstOrDefault(x => x.Username == username);
if (account == null)
{
throw new InvalidOperationException("Account not found");
}
// Only continue if the new enabled state is different from the current enabled state
if (account.Enabled != enabled)
{
// Set the enabled state
account.Enabled = enabled;
// Submit the changes
connection.SubmitChanges();
// Success
return(true);
}
else
{
// No state change
return(false);
}
}
}
/// <summary>
/// Create a new account
/// </summary>
/// <param name="username">username</param>
/// <param name="email">email address</param>
/// <param name="password">password</param>
/// <returns></returns>
public static bool CreateAccount(string username, string email, string password)
{
// Connect to the MSSQL database
using (MSSqlConnection connection = MSSqlConnection.GetConnection())
{
// Get the table
Table <AccountTable> accounts = connection.GetTable <AccountTable>();
// Check if the username and e-mail address are free
if (accounts.Any(x => x.Username.ToLower() == username.ToLower()))
{
return(false);
}
if (accounts.Any(x => x.EmailAddress.ToLower() == email.ToLower()))
{
return(false);
}
// Insert the new user
accounts.InsertOnSubmit(new AccountTable()
{
Username = username.ToLower(),
EmailAddress = email.ToLower(),
Password = BCrypt.HashPassword(password, BCrypt.GenerateSalt()),
Enabled = true,
Role = (int)AccountRole.Editor,
});
// Submit the changes
connection.SubmitChanges();
// Success
return(true);
}
}
/// <summary>
/// Create a new link category
/// </summary>
/// <param name="title">category title</param>
/// <param name="createdBy">username of the creator</param>
/// <returns>external category id</returns>
public static Guid CreateLinkCategory(string title, string createdBy)
{
// Connect to the MSSQL database
using (MSSqlConnection connection = MSSqlConnection.GetConnection())
{
// Get the tables
Table <LinkCategoryTable> categories = connection.GetTable <LinkCategoryTable>();
Table <AccountTable> accounts = connection.GetTable <AccountTable>();
// Create the external id for the new category
Guid externalId = Guid.NewGuid();
// Insert the new category
categories.InsertOnSubmit(new LinkCategoryTable()
{
ExternalId = externalId,
Title = title,
CreatedBy = AccountsManager.GetAccountId(connection, createdBy),
DateCreated = DateTime.Now,
Deleted = false,
});
// Submit the changes
connection.SubmitChanges();
// Return the external id of the new category
return(externalId);
}
}
public Manager(MSSqlConnection connection)
{
_connection = connection;
_propertiesMapper = new PropertiesMapper();
_relationshipsMapper = new RelationshipsMapper();
_queries = new List <string>();
}
/// <summary>
/// Get all comments for a currency
/// </summary>
/// <param name="currencyId">currency id</param>
/// <returns>list of comments</returns>
public static List <CommentModel> GetComments(Guid currencyId, bool includeDeleted = false)
{
// Connect to the MSSQL database
using (MSSqlConnection connection = MSSqlConnection.GetConnection())
{
// Get the table
Table <CurrencyTable> currencies = connection.GetTable <CurrencyTable>();
Table <CurrencyCommentTable> comments = connection.GetTable <CurrencyCommentTable>();
Table <AccountTable> accounts = connection.GetTable <AccountTable>();
// Create the query
var query = from x in comments
where includeDeleted || !x.Deleted
join c in currencies on x.Currency equals c.Id
where c.ExternalId == currencyId
join a in accounts on x.CreatedBy equals a.Id
orderby x.DateCreated descending
select new CommentModel()
{
Id = x.ExternalId,
CreatedBy = a.Username,
Message = x.Message,
Vote = x.Vote,
DateCreated = x.DateCreated,
Deleted = x.Deleted,
DeletedBy = (x.DeletedBy == null) ? null : (accounts.First(a => a.Id == x.DeletedBy).Username)
};
// Execute the query
return(query.ToList());
}
}
/// <summary>
/// Delete a comment
/// </summary>
/// <param name="commentId">comment</param>
/// <param name="deletedBy">username of the deletor</param>
/// <returns>
/// true = deleted state changed
/// false = deleted state did not change
/// </returns>
public static bool DeleteComment(Guid commentId, string deletedBy)
{
// Connect to the MSSQL database
using (MSSqlConnection connection = MSSqlConnection.GetConnection())
{
// Get the table
Table <CurrencyCommentTable> comments = connection.GetTable <CurrencyCommentTable>();
// Try to get the comment entry
CurrencyCommentTable comment = comments.FirstOrDefault(x => x.ExternalId == commentId && !x.Deleted);
if (comment == null)
{
return(false); // Comment deleted state did not changed
}
// Set the comment deleted flag to true
comment.Deleted = true;
// Set the deleted by
comment.DeletedBy = AccountsManager.GetAccountId(connection, deletedBy);
// Submit the changes
connection.SubmitChanges();
// Comment deleted state changed, return true
return(true);
}
}
/// <summary>
/// Delete a currency
/// </summary>
/// <param name="currencyId">currency external id</param>
/// <param name="deletedBy">username of the deletor</param>
/// <returns>true=currency deleted,false=currency not found</returns>
public static bool DeleteCurrency(Guid currencyId, string deletedBy)
{
// Connect to the MSSQL database
using (MSSqlConnection connection = MSSqlConnection.GetConnection())
{
// Get the table
Table <CurrencyTable> currencies = connection.GetTable <CurrencyTable>();
// Get the currency
CurrencyTable currency = currencies.FirstOrDefault(x => x.ExternalId == currencyId && !x.Deleted);
if (currency == null)
{
return(false);
}
// Set the currency deleted state
currency.Deleted = true;
// Set the deleted by
currency.DeletedBy = AccountsManager.GetAccountId(connection, deletedBy);
// Submit the changes
connection.SubmitChanges();
// Currency set to deleted, return true
return(true);
}
}
/// <summary>
/// Create a new currency
/// </summary>
/// <param name="displayName">display name</param>
/// <param name="shortCode">short code</param>
/// <param name="createdBy">creator username</param>
/// <returns>currency external id</returns>
public static Guid CreateCurrency(string displayName, string shortCode, string createdBy)
{
// Connect to the MSSQL database
using (MSSqlConnection connection = MSSqlConnection.GetConnection())
{
// Check if the currency already exists
if (CurrencyExists(displayName, shortCode))
{
throw new InvalidOperationException("A currency with the same display name or shortcode already exists");
}
// Get the table
Table <CurrencyTable> currencies = connection.GetTable <CurrencyTable>();
// Create the external id for the currency
Guid externalId = Guid.NewGuid();
// Insert the new currency
currencies.InsertOnSubmit(new CurrencyTable()
{
ExternalId = externalId,
DisplayName = displayName,
Symbol = shortCode.ToUpper(),
Deleted = false,
CreatedBy = AccountsManager.GetAccountId(connection, createdBy),
DateCreated = DateTime.Now,
});
// Submit the changes
connection.SubmitChanges();
// Return the external id of the new currency
return(externalId);
}
}
static void Main(string[] args)
{
Console.WriteLine("Hello World!");
ConnConfiguration configuration = new ConnConfiguration("localhost", "TestDB", "SA", "Cezarypazura1");
var msSqlConnection = MSSqlConnection.GetInstance();
msSqlConnection.setConfiguration(configuration);
msSqlConnection.ConnectAndOpen();
SqlCommand cmd = msSqlConnection.execute("SELECT name FROM sys.Tables");
// SqlCommand cmd = new SqlCommand("SELECT name FROM sys.Tables", msSqlConnection.GetSqlConnection());
// SqlDataReader reader = cmd.ExecuteReader();
SqlDataReader reader = msSqlConnection.executeReader(cmd);
while (reader.Read())
{
Console.WriteLine(reader["name"].ToString());
}
PropertyInfo[] myPropertyInfo;
// Get the properties of 'Type' class object.
myPropertyInfo = Type.GetType("System.Type").GetProperties();
Console.WriteLine("Properties of System.Type are:");
for (int i = 0; i < myPropertyInfo.Length; i++)
{
Console.WriteLine(myPropertyInfo[i].ToString());
}
}
/// <summary>
/// Delete a link item
/// </summary>
/// <param name="categoryId">category id</param>
/// <param name="deletedBy">username of the deletor</param>
/// <returns>
/// true = deleted state changed
/// false = deleted state did not change
/// </returns>
public static bool DeleteLinkItem(Guid categoryId, string deletedBy)
{
// Connect to the MSSQL database
using (MSSqlConnection connection = MSSqlConnection.GetConnection())
{
// Get the table
Table <LinkItemTable> items = connection.GetTable <LinkItemTable>();
// Try to get the category item
LinkItemTable item = items.FirstOrDefault(x => x.ExternalId == categoryId && !x.Deleted);
if (item == null)
{
return(false); // The deleted state did not changed, return false
}
// Set the deleted flag to true
item.Deleted = true;
// Set the deletor of the item
item.DeletedBy = AccountsManager.GetAccountId(connection, deletedBy);
// Submit the changes
connection.SubmitChanges();
// The deleted state changed, return true
return(true);
}
}
/// <summary>
/// Get all currencies
/// </summary>
/// <returns>list of currencies</returns>
public static List <CurrencyInfo> GetCurrencies()
{
// Connect to the MSSQL database
using (MSSqlConnection connection = MSSqlConnection.GetConnection())
{
// Get the table
Table <CurrencyTable> currencies = connection.GetTable <CurrencyTable>();
Table <CurrencyCommentTable> comments = connection.GetTable <CurrencyCommentTable>();
Table <AccountTable> accounts = connection.GetTable <AccountTable>();
Table <CurrencyValueRatesTable> currencyRates = connection.GetTable <CurrencyValueRatesTable>();
// Get the threshold date for when a comment is "recent"
DateTime recentDate = DateTime.Now.AddDays(-7);
// Create the query
var query = from x in currencies
where !x.Deleted
join a in accounts on x.CreatedBy equals a.Id
join c in comments on x.Id equals c.Currency into allComments
let recentComments = (from c in allComments
where c.DateCreated >= recentDate
select c)
let score = (int?)allComments.Sum(c => c.Vote) ?? 0
join r in currencyRates on x.Id equals r.Currency into allRates
let rates = allRates.FirstOrDefault()
orderby score descending
select new CurrencyInfo()
{
Id = x.ExternalId,
DisplayName = x.DisplayName,
ShortCode = x.Symbol,
DateCreated = x.DateCreated,
CreatedBy = a.Username,
Score = score,
TotalComments = allComments.Count(),
RecentComments = recentComments.Count(),
ValueRates = (rates == null) ? null : new CurrencyValueRates()
{
Symbol = x.Symbol,
Rank = rates.Rank,
PriceUsd = rates.PriceUsd,
VolumeUsd24h = rates.VolumeUsd24h,
MarketCapUsd = rates.MarketCapUsd,
AvailableSupply = rates.AvailableSupply,
TotalSupply = rates.TotalSupply,
MaxSupply = rates.MaxSupply,
PercentChange1h = rates.PercentChange1h,
PercentChange24h = rates.PercentChange24h,
LastUpdated = rates.LastUpdated,
PriceEur = rates.PriceEur,
VolumeEur24h = rates.VolumeEur24h,
MarketCapEur = rates.MarketCapEur,
}
};
// Execute the query
return(query.ToList());
}
}
/// <summary>
/// Get if a currency with a given external id exists
/// </summary>
/// <param name="externalId">external id</param>
/// <returns>true=exists, false=does not exist</returns>
public static bool CurrencyExists(Guid externalId)
{
// Connect to the MSSQL database
using (MSSqlConnection connection = MSSqlConnection.GetConnection())
{
return(CurrencyExists(connection, externalId));
}
}
/// <summary>
/// Get if a currency with a given external id exists
/// </summary>
/// <param name="connection">MSSQL database connection</param>
/// <param name="externalId">external id</param>
/// <returns>true=exists, false=does not exist</returns>
private static bool CurrencyExists(MSSqlConnection connection, Guid externalId)
{
// Get the table
Table <CurrencyTable> currencies = connection.GetTable <CurrencyTable>();
// Check if a currency exist with the same external id
return(currencies.Any(x => x.ExternalId == externalId && !x.Deleted));
}
/// <summary>
/// Get if a currency with a given display name or short code exists
/// </summary>
/// <param name="connection">MSSQL database connection</param>
/// <param name="shortCode">short code</param>
/// <returns>true=exists, false=does not exist</returns>
private static bool CurrencyExists(MSSqlConnection connection, string shortCode)
{
// Get the table
Table <CurrencyTable> currencies = connection.GetTable <CurrencyTable>();
// Check if a currency exist with the same display name or short code
return(currencies.Any(x => !x.Deleted && x.Symbol == shortCode.ToUpper()));
}
/// <summary>
/// Get if a currency with a given display name or short code exists
/// </summary>
/// <param name="displayname">display name</param>
/// <param name="shortCode">short code</param>
/// <returns>true=exists, false=does not exist</returns>
public static bool CurrencyExists(string displayname, string shortCode)
{
// Connect to the MSSQL database
using (MSSqlConnection connection = MSSqlConnection.GetConnection())
{
return(CurrencyExists(connection, displayname, shortCode));
}
}
/// <summary>
/// Authenticate account credentials
/// </summary>
/// <param name="username">email address or username</param>
/// <param name="password">password</param>
/// <returns>sign-in result</returns>
public static SignInResult Authenticate(string username, string password)
{
if (string.IsNullOrWhiteSpace(username) || string.IsNullOrWhiteSpace(password))
{
return new SignInResult(false, username)
{
Reason = "Invalid Credentials"
}
}
;
// Connect to the MSSQL database
using (MSSqlConnection connection = MSSqlConnection.GetConnection())
{
// Get the table
Table <AccountTable> accounts = connection.GetTable <AccountTable>();
// Get the account for the username
AccountTable account = accounts.FirstOrDefault(x => x.Username.ToLower() == username.ToLower() || x.EmailAddress.ToLower() == username.ToLower());
// Return false if the account does not exist
if (account == null)
{
return(new SignInResult(false, username)
{
Reason = "Invalid Credentials"
});
}
if (!account.Enabled)
{
return(new SignInResult(false, account.Username)
{
Reason = "Account Disabled"
});
}
// Check the password hash
bool validPassword = BCrypt.CheckPassword(password, account.Password);
if (validPassword)
{
// Update the last logon time and save the changes
account.LastLogon = DateTime.Now;
connection.SubmitChanges();
return(new SignInResult(true, account.Username));
}
else
{
return(new SignInResult(false, username)
{
Reason = "Invalid Credentials"
});
}
}
}
/// <summary>
/// Get if a currency with a given short code exists
/// </summary>
/// <param name="shortCode">short code</param>
/// <returns>true=exists, false=does not exist</returns>
public static bool CurrencyExists(string shortCode)
{
// Return false if the shortcode is not set
if (string.IsNullOrWhiteSpace(shortCode))
{
return(false);
}
// Connect to the MSSQL database
using (MSSqlConnection connection = MSSqlConnection.GetConnection())
{
return(CurrencyExists(connection, shortCode));
}
}
/// <summary>
/// Gets if an account has a role of one of the higher role
/// </summary>
/// <param name="username">account username</param>
/// <param name="role">role</param>
/// <returns>true=has role or higher role, false=does not</returns>
public static bool HasRole(string username, AccountRole role)
{
// Connect to the MSSQL database
using (MSSqlConnection connection = MSSqlConnection.GetConnection())
{
// Get the table
Table <AccountTable> accounts = connection.GetTable <AccountTable>();
// Get the account for the username
AccountTable account = accounts.FirstOrDefault(x => x.Username == username);
return(account != null && account.Enabled && account.Role >= (int)role);
}
}
/// <summary>
/// Gets if an username exists
/// </summary>
/// <param name="username">username</param>
/// <returns>true=exists, false=does not exist</returns>
public static bool UsernameExists(string username)
{
// Connect to the MSSQL database
using (MSSqlConnection connection = MSSqlConnection.GetConnection())
{
// Remove the spaces before and after the name and change the username to lowercase
string safeUsername = username.Trim().ToLower();
// Get the table
Table <AccountTable> accounts = connection.GetTable <AccountTable>();
// Check if any account exists with the username
return(accounts.Any(x => x.Username == safeUsername));
}
}
/// <summary>
/// Gets if an email address exists
/// </summary>
/// <param name="email">email address</param>
/// <returns>true=exists, false=does not exist</returns>
public static bool EmailExists(string email)
{
// Connect to the MSSQL database
using (MSSqlConnection connection = MSSqlConnection.GetConnection())
{
// Remove the spaces before and after the email and change the email to lowercase
string safeEmailAddress = email.Trim().ToLower();
// Get the table
Table <AccountTable> accounts = connection.GetTable <AccountTable>();
// Check if any account exists with the email address
return(accounts.Any(x => x.EmailAddress == safeEmailAddress));
}
}
/// <summary>
/// Get the database id of an account
/// </summary>
/// <param name="connection">MSSQL database connection</param>
/// <param name="username">account username</param>
/// <returns>account database id</returns>
public static int GetAccountId(MSSqlConnection connection, string username)
{
// Get the table
Table <AccountTable> accounts = connection.GetTable <AccountTable>();
// Get the account
AccountTable account = accounts.FirstOrDefault(x => x.Username == username);
if (account == null)
{
throw new InvalidOperationException("Account not found");
}
// Return the account id
return(account.Id);
}
/// <summary>
/// Update the value rates of all registed currencies
/// </summary>
/// <param name="newRates">lsit of currency value rates</param>
public static void UpdateRates(List <CurrencyValueRates> newRates)
{
// Connect to the MSSQL database
using (MSSqlConnection connection = MSSqlConnection.GetConnection())
{
// Get the tables
Table <CurrencyTable> currencies = connection.GetTable <CurrencyTable>();
Table <CurrencyValueRatesTable> currencyRates = connection.GetTable <CurrencyValueRatesTable>();
// Delete all current rate entries
currencyRates.DeleteAllOnSubmit(currencyRates.ToList());
// Create a look-up dictionary to find the currency id for a symbol
Dictionary <string, int> currencyLookup = currencies.ToDictionary(x => x.Symbol, x => x.Id);
// Loop through the new value rates
foreach (CurrencyValueRates rates in newRates)
{
// Check if the currency is registered int he application
if (currencyLookup.ContainsKey(rates.Symbol))
{
// Insert the currency rates information
currencyRates.InsertOnSubmit(new CurrencyValueRatesTable()
{
Currency = currencyLookup[rates.Symbol],
Rank = rates.Rank,
PriceUsd = rates.PriceUsd,
VolumeUsd24h = rates.VolumeUsd24h,
MarketCapUsd = rates.MarketCapUsd,
AvailableSupply = rates.AvailableSupply,
TotalSupply = rates.TotalSupply,
MaxSupply = rates.MaxSupply,
PercentChange1h = rates.PercentChange1h,
PercentChange24h = rates.PercentChange24h,
LastUpdated = rates.LastUpdated,
PriceEur = rates.PriceEur,
VolumeEur24h = rates.VolumeEur24h,
MarketCapEur = rates.MarketCapEur,
});
}
}
// Submit the changes
connection.SubmitChanges();
}
}
public static Connection CreateFactory(CommonData.DbType dbType)
{
Connection connection = null;
switch (dbType)
{
case CommonData.DbType.MySql:
break;
case CommonData.DbType.Oracle:
break;
case CommonData.DbType.MSSQL:
connection = new MSSqlConnection();
break;
default:
break;
}
return connection;
}
/// <summary>
/// Create a new comment
/// </summary>
/// <param name="currencyId">currency id</param>
/// <param name="message">message</param>
/// <param name="vote">vote</param>
/// <param name="createdBy">username of the comment author</param>
/// <returns>comment id</returns>
public static Guid CreateComment(Guid currencyId, string message, int vote, string createdBy)
{
// Connect to the MSSQL database
using (MSSqlConnection connection = MSSqlConnection.GetConnection())
{
// Get the tables
Table <CurrencyTable> currencies = connection.GetTable <CurrencyTable>();
Table <CurrencyCommentTable> comments = connection.GetTable <CurrencyCommentTable>();
Table <AccountTable> accounts = connection.GetTable <AccountTable>();
// Get the currency
CurrencyTable currency = currencies.FirstOrDefault(x => x.ExternalId == currencyId && !x.Deleted);
if (currency == null)
{
throw new InvalidOperationException("Currency not found");
}
// Create the external id for the comment
Guid externalId = Guid.NewGuid();
// Insert the comment
comments.InsertOnSubmit(new CurrencyCommentTable()
{
ExternalId = externalId,
Currency = currency.Id,
Message = message,
Vote = vote,
CreatedBy = AccountsManager.GetAccountId(connection, createdBy),
DateCreated = DateTime.Now,
Deleted = false,
});
// Submit the changes
connection.SubmitChanges();
// Return the external id of the new comment
return(externalId);
}
}
/// <summary>
/// Create a new link item
/// </summary>
/// <param name="categoryId"></param>
/// <param name="title"></param>
/// <param name="url"></param>
/// <param name="createdBy"></param>
/// <returns></returns>
public static Guid CreateLinkItem(Guid categoryId, string title, string url, string createdBy)
{
// Connect to the MSSQL database
using (MSSqlConnection connection = MSSqlConnection.GetConnection())
{
// Get the tables
Table <LinkCategoryTable> categories = connection.GetTable <LinkCategoryTable>();
Table <LinkItemTable> items = connection.GetTable <LinkItemTable>();
// Try to get the category entry
LinkCategoryTable category = categories.FirstOrDefault(x => x.ExternalId == categoryId && !x.Deleted);
if (category == null)
{
throw new Exception("Category not found");
}
// Create the external id for the new link item
Guid externalId = Guid.NewGuid();
// Insert the new item
items.InsertOnSubmit(new LinkItemTable()
{
Category = category.Id,
ExternalId = externalId,
Title = title,
Url = url,
CreatedBy = AccountsManager.GetAccountId(connection, createdBy),
DateCreated = DateTime.Now,
Deleted = false,
});
// Submit the changes
connection.SubmitChanges();
// Return the external id of the new item
return(externalId);
}
}
/// <summary>
/// Get all accounts
/// </summary>
/// <returns></returns>
public static List <AccountInfo> GetAccounts()
{
// Connect to the MSSQL database
using (MSSqlConnection connection = MSSqlConnection.GetConnection())
{
// Get the table
Table <AccountTable> accounts = connection.GetTable <AccountTable>();
// Create the query
var query = from x in accounts
select new AccountInfo()
{
EmailAddress = x.EmailAddress,
Username = x.Username,
Enabled = x.Enabled,
Role = x.Role,
LastLogon = x.LastLogon,
};
// Execute the query
return(query.ToList());
}
}
/// <summary>
/// Get all categories and their items
/// </summary>
/// <returns>categories and their items</returns>
public static List <LinkCategoryModel> GetAllLinks()
{
// Connect to the MSSQL database
using (MSSqlConnection connection = MSSqlConnection.GetConnection())
{
// Get the tables
Table <LinkCategoryTable> categories = connection.GetTable <LinkCategoryTable>();
Table <LinkItemTable> items = connection.GetTable <LinkItemTable>();
Table <AccountTable> accounts = connection.GetTable <AccountTable>();
// Create the query
var query = from x in categories
where !x.Deleted
join a in accounts on x.CreatedBy equals a.Id
join i in items on x.Id equals i.Category into categoryItems
select new LinkCategoryModel()
{
Title = x.Title,
CreatedBy = a.Username,
DateCreated = x.DateCreated,
Items = (from i in categoryItems
join a in accounts on i.CreatedBy equals a.Id
where !i.Deleted
select new LinkModel()
{
Title = i.Title,
Url = i.Url,
CreatedBy = a.Username,
DateCreated = i.DateCreated
}).ToList()
};
// Execute the query
return(query.ToList());
}
}
/// <summary>
/// Set the password of an account
/// </summary>
/// <param name="username">account username</param>
/// <param name="password">new plain text password</param>
public static void SetAccountPassword(string username, string password)
{
// Connect to the MSSQL database
using (MSSqlConnection connection = MSSqlConnection.GetConnection())
{
// Get the table
Table <AccountTable> accounts = connection.GetTable <AccountTable>();
// Get the account
AccountTable account = accounts.FirstOrDefault(x => x.Username == username);
if (account == null)
{
throw new InvalidOperationException("Account not found");
}
string passwordHash = BCrypt.HashPassword(password, BCrypt.GenerateSalt());
// Update the password
account.Password = BCrypt.HashPassword(password, BCrypt.GenerateSalt());
// Submit the changes
connection.SubmitChanges();
}
}
static void Main(string[] args)
{
MSSqlConnection conn = MSSqlConnection.GetInstance();
// ConnConfiguration conf = new ConnConfiguration("localhost", "tmp", "SA", "Cezarypazura1");
ConnConfiguration conf = new ConnConfiguration("KAROLINA-PC\\SQLEXPRESS", "Test");
conn.setConfiguration(conf);
Manager mng = new Manager(conn);
// User's set-up.
Person person1 = new Person(1, "John", "Smith");
Dog dog1 = new Dog(10);
Bowl bowl1 = new Bowl(7);
dog1.setBowl(bowl1);
person1.setDog(dog1);
Cat cat1 = new Cat(11);
Cat cat2 = new Cat(12);
Bowl bowlCat1 = new Bowl(13);
Bowl bowlCat2 = new Bowl(14);
cat1.setBowl(bowlCat1);
cat2.setBowl(bowlCat2);
person1.addCat(cat1);
person1.addCat(cat2);
Woman woman = new Woman(15, "Anna", "Nowak", "blond");
Dog dogWoman = new Dog(11);
Bowl bowlWoman = new Bowl(21);
dogWoman.setBowl(bowlWoman);
woman.setDog(dogWoman);
Cat catWoman = new Cat(31);
Cat catWoman2 = new Cat(32);
Cat catWoman3 = new Cat(33);
Bowl bowlCatWoman = new Bowl(51);
Bowl bowlCatWoman2 = new Bowl(52);
Bowl BowlCatWoman3 = new Bowl(53);
catWoman.setBowl(bowlCatWoman);
catWoman2.setBowl(bowlCatWoman2);
catWoman3.setBowl(BowlCatWoman3);
woman.addCat(catWoman);
woman.addCat(catWoman2);
woman.addCat(catWoman3);
// Creating a Bowl Table.
mng.createTable(bowl1);
// Inserting an object into database.
// mng.insert(woman);
// mng.insert(person1);
// Selecting an object by Id.
Cat selectedCat = (Cat)mng.selectById(typeof(Cat), 32);
if (selectedCat == null)
{
Console.WriteLine("Such object doesn't exist.");
}
else
{
Console.WriteLine("Selected Item: ");
Console.WriteLine("catId = " + selectedCat.getId());
if (selectedCat.getBowl() != null)
{
Console.WriteLine("bowlId = " + selectedCat.getBowl().getId());
}
}
// Selecting an object from database and creating list of Criteria.
List <Criteria> myCriterias = new List <Criteria>();
myCriterias.Add(Criteria.greaterThan("id", 0));
IEnumerable <object> dogList = (IEnumerable <object>)mng.select(typeof(Dog), myCriterias);
if (dogList != null)
{
Console.WriteLine("");
Console.WriteLine("Selected Items:");
foreach (Dog itDog in dogList)
{
Console.WriteLine("dogId = " + itDog.getId());
//if (itDog.getBowl() != null) {
Console.WriteLine("bowlId = " + itDog.getBowl().getId());
//}
}
}
else
{
Console.WriteLine("Such objects don't exist.");
}
// Updating an object with the usage of criteria.
/*List<Criteria> updateCriterias = new List<Criteria>();
* updateCriterias.Add(Criteria.equals("id", 15));
*
*
* Dog dogUpdate = new Dog(9);
*
* var updateChanges = new List<Tuple<string, object>> {
* // new Tuple<string, object> ("nameOfColumn", value)
* new Tuple<string, object>("pies", dogUpdate),
* new Tuple<string, object>("wlosy", "czarne")
* };
* mng.update(typeof(Woman), updateChanges, updateCriterias);
*/
// Deleting an object with usage of criteria.
/*List<Criteria> deleteCriterias = new List<Criteria>();
* deleteCriterias.Add(Criteria.equals("id", 53));
*
* mng.delete(typeof(Bowl), deleteCriterias);
*/
}
