public static int GetComputerCount()
{
var connection = DatabaseUtils.CreateConnection();
connection.Open();
var counter = 0;
const string query = "SELECT * FROM dbo.de_savedComputers";
var command = new SqlCommand(query, connection);
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
counter++;
}
}
reader.Close();
}
return(counter);
}
public static void AddComputer(Computer computer)
{
var connection = DatabaseUtils.CreateConnection();
connection.Open();
string query = "INSERT INTO dbo.de_savedComputers(typename, computerId, userId, username, part1, part2, part3, part4, part5) " +
"VALUES(@param1,@param2,@param3, @param4, @param5, @param6, @param7, @param8, @param9)";
using (connection)
{
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.Add("@param1", SqlDbType.VarChar, 10).Value = computer.ComputerType;
cmd.Parameters.Add("@param2", SqlDbType.Int).Value = computer.Id;
cmd.Parameters.Add("@param3", SqlDbType.Int).Value = computer.UserId;
cmd.Parameters.Add("@param4", SqlDbType.VarChar, 40).Value = computer.UserName;
cmd.Parameters.Add("@param5", SqlDbType.Text).Value = computer.Part1;
cmd.Parameters.Add("@param6", SqlDbType.Text).Value = computer.Part2;
cmd.Parameters.Add("@param7", SqlDbType.Text).Value = computer.Part3;
cmd.Parameters.Add("@param8", SqlDbType.Text).Value = computer.Part4;
cmd.Parameters.Add("@param9", SqlDbType.Text).Value = computer.Part5;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
connection.Close();
}
public static Computer GetComputerById(int computerId, string typeName)
{
var connection = DatabaseUtils.CreateConnection();
connection.Open();
var computer = new Computer();
string query = $"SELECT * FROM dbo.de_savedComputers WHERE computerId = '{computerId}' and typename = '{typeName}'";
var command = new SqlCommand(query, connection);
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
computer.ComputerType = reader.GetString(0);
computer.Id = reader.GetInt32(1);
computer.UserId = reader.GetInt32(2);
computer.UserName = reader.GetString(3);
computer.Part1 = reader.GetString(4);
computer.Part2 = reader.GetString(5);
computer.Part3 = reader.GetString(6);
computer.Part4 = reader.GetString(7);
computer.Part5 = reader.GetString(8);
}
}
reader.Close();
}
return(computer);
}
public static List <string> GetAllUsernames()
{
var usernames = new List <string>();
var query = $"SELECT username FROM dbo.de_user;";
var connection = DatabaseUtils.CreateConnection();
connection.Open();
var command = new SqlCommand(query, connection);
using (var reader = command.ExecuteReader())
{
if (!reader.HasRows)
{
return(usernames);
}
while (reader.Read())
{
usernames.Add(reader.GetString(0));
}
reader.Close();
}
return(usernames);
}
public static string RetrievePassword(string username)
{
var password = "";
var connection = DatabaseUtils.CreateConnection();
connection.Open();
using (connection)
{
var query = $"SELECT password FROM dbo.de_user WHERE username='******';";
var command = new SqlCommand(query, connection);
using (var reader = command.ExecuteReader())
{
if (!reader.HasRows)
{
return(password);
}
while (reader.Read())
{
password = reader.GetString(0);
}
}
}
return(password);
}
public static List <CartItem> GetCartItems(string username)
{
var connection = DatabaseUtils.CreateConnection();
connection.Open();
var cartItems = new List <CartItem>();
var query = $"SELECT * FROM dbo.de_onCart where username = '******'";
var command = new SqlCommand(query, connection);
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
var cartItem = new CartItem();
cartItem.UserName = reader.GetString(0);
cartItem.TypeName = reader.GetString(1);
cartItem.ComputerId = reader.GetInt32(2);
cartItem.Price = reader.GetInt32(3);
cartItems.Add(cartItem);
}
}
reader.Close();
}
return(cartItems);
}
public static CartItem GetCartItemByIdAndType(int computerId, string computerType)
{
var connection = DatabaseUtils.CreateConnection();
connection.Open();
var cartItem = new CartItem();
var query = $"SELECT * FROM dbo.de_onCart where computerId = '{computerId}' and typename ='{computerType}'";
var command = new SqlCommand(query, connection);
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
cartItem.UserName = reader.GetString(0);
cartItem.TypeName = reader.GetString(1);
cartItem.ComputerId = reader.GetInt32(2);
cartItem.Price = reader.GetInt32(3);
}
}
reader.Close();
}
return(cartItem);
}
public static void AddToOrdersHistory(int computerId, string computerType)
{
IOrder orderItem = CartController.GetCartItemByIdAndType(computerId, computerType);
var connection = DatabaseUtils.CreateConnection();
connection.Open();
var query = "INSERT INTO dbo.de_orders(username, typename, computerId, price) " +
"VALUES(@param1, @param2, @param3, @param4)";
using (connection)
{
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.Add("@param1", SqlDbType.VarChar, 40).Value = orderItem.UserName;
cmd.Parameters.Add("@param2", SqlDbType.VarChar, 10).Value = orderItem.TypeName;
cmd.Parameters.Add("@param3", SqlDbType.Int).Value = orderItem.ComputerId;
cmd.Parameters.Add("@param4", SqlDbType.Int).Value = orderItem.Price;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
connection.Close();
}
public static int GetUserIdByName(string username)
{
var connection = DatabaseUtils.CreateConnection();
connection.Open();
var userId = -1;
var query = $"SELECT userId FROM dbo.de_user WHERE username = '******'";
var command = new SqlCommand(query, connection);
using (var reader = command.ExecuteReader())
{
if (!reader.HasRows)
{
return(userId);
}
while (reader.Read())
{
userId = reader.GetInt32(0);
}
reader.Close();
}
return(userId);
}
public static List <ComputerParts> GetComputerPart(string typeName)
{
var connection = DatabaseUtils.CreateConnection();
connection.Open();
List <ComputerParts> parts = new List <ComputerParts>();
string query = $"SELECT * FROM dbo.de_parts where typename = '{typeName}'";
SqlCommand command = new SqlCommand(query, connection);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
ComputerParts curr = new ComputerParts();
curr.TypeName = reader.GetString(0);
curr.Id = reader.GetInt32(1);
curr.PartName = reader.GetString(2);
curr.Price = reader.GetInt32(3);
parts.Add(curr);
}
}
reader.Close();
}
return(parts);
}
protected void LogIn(object sender, EventArgs e)
{
if (IsValid)
{
var connection = DatabaseUtils.CreateConnection();
connection.Open();
var currentUsername = Username.Text;
var currentPassword = Password.Text;
var getUser = UserController.GetUserName(connection, currentUsername, currentPassword);
if ((getUser == "") || (getUser == null))
{
InvalidLogin.Text = "Invalid username and password combination";
}
else
{
Session["username"] = getUser;
FormsAuthentication.SetAuthCookie(Username.Text, true);
Response.Redirect("../Default.aspx", true);
}
}
}
public static int GetTotalusers()
{
var totalNumber = 0;
var connection = DatabaseUtils.CreateConnection();
connection.Open();
using (connection)
{
const string query = "SELECT username FROM dbo.de_user;";
var command = new SqlCommand(query, connection);
using (var reader = command.ExecuteReader())
{
if (!reader.HasRows)
{
return(totalNumber);
}
while (reader.Read())
{
totalNumber++;
}
}
}
return(totalNumber);
}
public void Setup()
{
dbUtils = new DatabaseUtils();
string dataSource = Configuration["datebaseDetails:testCompleteDetails:dataSource"];
string userId = Configuration["datebaseDetails:testCompleteDetails:userId"];
string password = Configuration["datebaseDetails:testCompleteDetails:password"];
string dbName = Configuration["datebaseDetails:testCompleteDetails:dbName"];
dbUtils.CreateConnection(dataSource, userId, password, dbName);
}
public void Execute(string connectionString, int commandTimeoutSecs)
{
string sql = GenerateCommand();
if (!string.IsNullOrEmpty(sql))
{
using (SqlConnection c = DatabaseUtils.CreateConnection(connectionString))
{
Execute(c, null, commandTimeoutSecs);
}
}
}
public void Upsert(
string sqlConnectionString,
int timeoutSecs,
IEnumerable <Row> stagingRows,
TableColumnMappings colMappings,
FixupCaches caches,
DataStoreConfiguration configuration,
bool modifyWeekNumbers = false)
{
using (var c = DatabaseUtils.CreateConnection(sqlConnectionString))
{
var tmpTableName = GenerateTempUpsertTable(c, timeoutSecs, stagingRows, colMappings, caches, configuration, modifyWeekNumbers);
var b = new SqlBuilder();
b.AppendFormat("MERGE {0} target", QualifiedName);
b.AppendFormat("using (select {0} from {1}) source", colMappings.GetPublicColNamesAsCsv(), tmpTableName);
b.Append("on (");
for (int n = 0; n < PrimaryKey.KeyPartsCount; ++n)
{
if (n > 0)
{
b.Append("and");
}
var keyPart = PrimaryKey[n];
b.AppendFormat("target.{0} = source.{0}", keyPart.ColumnName);
}
b.Append(")");
b.Append("when MATCHED then");
b.Append("UPDATE SET");
for (int n = 0; n < Columns.Count; ++n)
{
if (n > 0)
{
b.AppendNoSpace(", ");
}
var col = Columns[n];
b.AppendFormat("target.{0} = source.{0}", col.Name);
}
b.Append("when NOT MATCHED then");
b.AppendFormat("INSERT ({0}) values({1})", colMappings.GetPublicColNamesAsCsv(), GetSourceColsNamesAsCsv(colMappings));
b.Append(";");
DatabaseUtils.ExecuteSql(c, null, b.ToString(), timeoutSecs);
}
}
public static int Main(string[] args)
{
try
{
Console.WriteLine("Загрузка настроек");
_settings = (XftpSettings)DeSerializeObject(typeof(XftpSettings), _pathSettings);
if (!Directory.Exists(_settings.Report.Log))
{
Directory.CreateDirectory(_settings.Report.Log);
}
if (!Directory.Exists(_settings.Report.Path))
{
Directory.CreateDirectory(_settings.Report.Path);
}
_logPath = Path.Combine(_settings.Report.Log, $"logXFtp{DateTime.Now.ToString("ddMMyyyy_HHmmss")}.log");
WriteLog($"Открываем лог {_logPath}...");
if (args.Length != 1)
{
throw new Exception("В приложение ожидается передача только одного параметра.");
}
WriteLog($"Номер версии исполняемого файла {Assembly.GetExecutingAssembly().GetName().Version}");
_connection = DatabaseUtils.CreateConnection(_settings.Base.Source, _settings.Base.Login, _settings.Base.Password);
WriteLog("Соединение с базой установлено");
WriteMessages(decimal.Parse(args[0]));
WriteLog("Закрываем лог");
//Console.ReadKey();
return(0);
}
catch (Exception e)
{
Environment.ExitCode = 1;
if (!File.Exists(_logPath))
{
Console.WriteLine("Файл лога не смог быть создан.");
Console.ReadLine();
}
else
{
WriteLog($"Выполнение программы завершается ошибкой: \r\n{e.Message}");
}
Console.ReadLine();
}
return(666);
}
public static void RemoveFromCart(string computerId, string computerType)
{
var connection = DatabaseUtils.CreateConnection();
connection.Open();
var computerIdString = computerId;
using (connection)
{
var query =
$"DELETE FROM dbo.de_onCart WHERE typename = '{computerType}' and computerId = '{computerIdString}' ";
var command = new SqlCommand(query, connection);
command.ExecuteNonQuery();
}
connection.Close();
}
public static void AddToCart(CartItem cartItem)
{
var connection = DatabaseUtils.CreateConnection();
connection.Open();
var query = "INSERT INTO dbo.de_onCart(username, typename, computerId, price) " +
"VALUES(@param1, @param2, @param3, @param4)";
using (connection)
{
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.Add("@param1", SqlDbType.VarChar, 40).Value = cartItem.UserName;
cmd.Parameters.Add("@param2", SqlDbType.VarChar, 10).Value = cartItem.TypeName;
cmd.Parameters.Add("@param3", SqlDbType.Int).Value = cartItem.ComputerId;
cmd.Parameters.Add("@param4", SqlDbType.Int).Value = cartItem.Price;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
connection.Close();
}
public static void AddNewUser(string username, string password)
{
var newUserId = GetTotalusers() + 1;
var connection = DatabaseUtils.CreateConnection();
connection.Open();
var query = $"INSERT INTO dbo.de_user(userId, username, password) VALUES (@param1, @param2, @param3);";
using (connection)
{
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.Add("@param1", SqlDbType.Int).Value = newUserId;
cmd.Parameters.Add("@param2", SqlDbType.VarChar, 40).Value = username;
cmd.Parameters.Add("@param3", SqlDbType.VarChar, 40).Value = password;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
connection.Close();
}
public void Setup()
{
dbUtils = new DatabaseUtils();
dbUtils.CreateConnection();
}
private SqlConnection CreateConnection()
{
return(DatabaseUtils.CreateConnection(_connectionString));
}
