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);
        }
예제 #4
0
        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);
        }
예제 #5
0
        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);
        }
예제 #8
0
        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();
        }
예제 #9
0
        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);
        }
예제 #11
0
        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);
                }
            }
        }
예제 #12
0
        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);
        }
예제 #14
0
        public void Execute(string connectionString, int commandTimeoutSecs)
        {
            string sql = GenerateCommand();

            if (!string.IsNullOrEmpty(sql))
            {
                using (SqlConnection c = DatabaseUtils.CreateConnection(connectionString))
                {
                    Execute(c, null, commandTimeoutSecs);
                }
            }
        }
예제 #15
0
        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);
            }
        }
예제 #16
0
파일: Program.cs 프로젝트: piltatnik/MFtp
        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();
        }
예제 #19
0
        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();
 }
예제 #21
0
 private SqlConnection CreateConnection()
 {
     return(DatabaseUtils.CreateConnection(_connectionString));
 }