Example #1
0
        public static List <Customer> GetAllCustomersList()
        {
            string strConnectionString = DalUtils.GetConnectionString();

            List <Customer> list = new List <Customer>();

            using (SqlConnection connection =
                       new SqlConnection(strConnectionString))
            {
                connection.Open();
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT * from Customers");
                String sql = sb.ToString();

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            list.Add(new Customer
                            {
                                CustomersName         = reader.GetString(1),
                                CustomersSubscription = reader.GetString(2),
                                CustomersAge          = reader.GetInt32(3),
                            });
                        }
                    }
                }
            }
            return(list);
        }
Example #2
0
        public static List <Movie> GetAllMovieList()
        {
            string strConnectionString = DalUtils.GetConnectionString();

            List <Movie> list = new List <Movie>();

            using (SqlConnection connection =
                       new SqlConnection(strConnectionString))
            {
                connection.Open();
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT * from Movies");
                String sql = sb.ToString();

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            list.Add(new Movie
                            {  // רידר נקודה ומיקום מסמן על עמודה
                                MovieId       = reader.GetInt32(0),
                                MovieName     = reader.GetString(1),
                                MovieCategory = reader.GetString(2)
                            });
                        }
                    }
                }
            }
            return(list);
        }
        public static List <Rental> GetAllRentalsList()
        {
            string strConnectionString = DalUtils.GetConnectionString();

            List <Rental> list = new List <Rental>();

            using (SqlConnection connection =
                       new SqlConnection(strConnectionString))
            {
                connection.Open();
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT Customers.Name , Movies.Name, Rentals.Id FROM Rentals INNER JOIN Customers ON Rentals.Id= Customers.Id" +
                          " INNER JOIN Movies ON Rentals.Id = Movies.Id;");
                String sql = sb.ToString();

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            list.Add(new Rental
                            {
                                CostomerName = reader.GetString(0),
                                MovieName    = reader.GetString(1),
                                ID           = reader.GetInt32(2),
                            });
                        }
                    }
                }
            }
            return(list);
        }
        public static int GetMovieID(string MovieName)
        {
            int MovieID = 0;

            string strConnectionString = DalUtils.GetConnectionString();

            using (SqlConnection connection =
                       new SqlConnection(strConnectionString))
            {
                connection.Open();
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT Id from Movies");
                sb.Append(" where Name= '" + MovieName + "';");
                String sql = sb.ToString();

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            MovieID = reader.GetInt32(0);
                        }
                    }
                }
            }
            return(MovieID);
        }
        public static int GetCustomerID(string CustomerName)
        {
            int CustomerID = 0;

            string strConnectionString = DalUtils.GetConnectionString();

            using (SqlConnection connection =
                       new SqlConnection(strConnectionString))
            {
                connection.Open();
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT Id from Customers WHERE Customers.Name= '" + CustomerName + "';");
                String sql = sb.ToString();

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            CustomerID = reader.GetInt32(0);
                        }
                    }
                }
            }
            return(CustomerID);
        }
        public static int AddRentals(string CostomerName, string MovieName)
        {
            int    MovieID    = RentalsTableHelper.GetMovieID(MovieName);
            int    CustomerID = RentalsTableHelper.GetCustomerID(CostomerName);
            int    nRowsAffected;
            string strConnectionString = DalUtils.GetConnectionString();

            using (SqlConnection connection = new SqlConnection(strConnectionString))
            {
                connection.Open();
                StringBuilder sb = new StringBuilder();
                sb.Append("insert into Rentals (MovieID,CustomerID) VALUES (@MovieID,@CustomerID)");

                String sql = sb.ToString();
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@MovieID", MovieID);
                    command.Parameters.AddWithValue("@CustomerID", CustomerID);

                    nRowsAffected = command.ExecuteNonQuery();
                }
            }

            return(nRowsAffected);
        }
Example #7
0
        public static int AddMovie(string MovieName, string MovieCategory)
        {
            int    nRowsAffected;
            string strConnectionString = DalUtils.GetConnectionString();

            using (SqlConnection connection = new SqlConnection(strConnectionString))
            {
                connection.Open();
                StringBuilder sb = new StringBuilder();
                sb.Append("INSERT INTO Movies (Name,Category) ");
                sb.Append("VALUES (@MovieName, @MovieCategory);");
                String sql = sb.ToString();
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@MovieName", MovieName);
                    command.Parameters.AddWithValue("@MovieCategory", MovieCategory);
                    nRowsAffected = command.ExecuteNonQuery();
                }
            }

            return(nRowsAffected);
        }
Example #8
0
        public static int AddCustome(string CustomersName, string CustomersSubscription, int CustomersAge)
        {
            int    nRowsAffected;
            string strConnectionString = DalUtils.GetConnectionString();

            using (SqlConnection connection = new SqlConnection(strConnectionString))
            {
                connection.Open();
                StringBuilder sb = new StringBuilder();
                sb.Append("INSERT INTO Customers (Name,Subscription,Age) ");
                sb.Append("VALUES (@Name, @Subscription,@Age);");
                String sql = sb.ToString();
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@Name", CustomersName);
                    command.Parameters.AddWithValue("@Subscription", CustomersSubscription);
                    command.Parameters.AddWithValue("@Age", CustomersAge);
                    nRowsAffected = command.ExecuteNonQuery();
                }
            }

            return(nRowsAffected);
        }