/// <summary>
/// Returns the settings for the specified user.
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public static UserSettings GetSettingsForUser(User user)
{
if (user == null)
{
return new UserSettings();
}
UserSettings settings = new UserSettings();
string connectionString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(connectionString))
{
MySqlCommand command = new MySqlCommand();
try
{
con.Open();
command.Connection = con;
command.CommandText = "SELECT * FROM settings WHERE userId=@id";
command.Prepare();
command.Parameters.AddWithValue("@id", user.Id);
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
settings.Id = reader.GetInt32("id");
settings.CompanyName = reader.GetString("companyName");
settings.CompanyId = reader.GetString("companyId");
settings.CompanyVatId = reader.GetString("companyVatId");
settings.CompanyAddress = reader.GetString("companyAddress");
settings.CompanyPhone = reader.GetString("companyPhone");
settings.CompanyType = reader.GetInt16("companyType");
settings.OppositeAccountForBankPayment = reader.GetInt32("oppositeAccountForBankPayment");
settings.OppositeAccountForBillPayment = reader.GetInt32("oppositeAccountForBillPayment");
settings.OppositeAccountForCashPayment = reader.GetInt32("oppositeAccountForCashPayment");
settings.BankAccountId = reader.GetString("bankAccountId");
settings.BankAccountBic = reader.GetString("bankAccountBic");
settings.BankName = reader.GetString("bankName");
}
settings.UserId = user.Id;
}
catch (MySqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
return settings;
}
/// <summary>
/// Returns only one User-typed object with the given username.
/// Queries the database. If the user with specified name was not found
/// returns just new User().
/// </summary>
/// <param name="username"></param>
/// <returns>User</returns>
public static User GetUserByUserName(string username)
{
User user = new User();
string connectionString = @ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(connectionString))
{
MySqlCommand command = new MySqlCommand();
try
{
con.Open();
command.Connection = con;
command.CommandText = @"SELECT * FROM users WHERE username=@username";
command.Prepare();
command.Parameters.AddWithValue("@username", username);
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
user.Username = reader.GetString("username");
user.Password = reader.GetString("password");
user.Id = reader.GetInt32("id");
}
}
catch (MySqlException ex)
{
throw ex;
} finally
{
con.Close();
}
}
return user;
}
/// <summary>
/// Updates the given user to the database.
/// </summary>
/// <param name="document">The user to be updated.</param>
public static void UpdateUser(User user)
{
string connectionString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(connectionString))
{
try
{
con.Open();
MySqlCommand command = new MySqlCommand();
command.Connection = con;
command.CommandText = "UPDATE users SET username=@username, password=@password WHERE id=@id";
command.Parameters.AddWithValue("@id", user.Id);
command.Parameters.AddWithValue("@username", user.Username);
command.Parameters.AddWithValue("@password", user.Password);
command.ExecuteNonQuery();
}
catch (MySqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
}