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);
}
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);
}
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);
}
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);
}
