public IList <Car> FetchAllByCompany(int CompanyID) { List <Car> cars = new List <Car>(); dBConnection.OpenConnection(); SqlCommand cmd = new SqlCommand(); SqlDataReader reader = null; try { string condition = "WHERE [sirketID] = @CompanyID"; // creating a select query with util cmd.CommandText = DBCommandCreator.SELECT(new string[] { "aracID", "model", "ehliyetYasi", "minYas", "anlikKm", "airbag", "bagajHacmi", "gunlukFiyat", "sirketID", "markaID", "aktiflik" }, DBTableNames.Cars, condition); // adding companyID parameter to the query. DBCommandCreator.AddParameter(cmd, "@CompanyID", DbType.Int32, ParameterDirection.Input, CompanyID); reader = dBConnection.DataReader(cmd); if (reader.HasRows) { while (reader.Read()) { // creating car instances for every row in the response table var entity = new Car { CarID = reader.GetInt32(0), CarModel = reader.GetString(1), RequiredDriverLicenceExp = reader.GetInt32(2), RequiredAge = reader.GetInt32(3), KmInfo = reader.GetInt32(4), HasAirbag = reader.GetBoolean(5), BaggageCapacity = reader.GetString(6), DailyCost = (decimal)reader.GetSqlMoney(7), Company = new CompanyRepository().FetchById(reader.GetInt32(8)), Brand = new BrandRepository().FetchById(reader.GetInt32(9)), isActive = reader.GetBoolean(10) }; cars.Add(entity); } } return(cars); } catch (Exception ex) { throw new Exception("An error occured while executing FetchAll() in SpiceApp.DataAccessLayer.CarRepository", ex); } finally { if (reader != null) { reader.Close(); } dBConnection.CloseConnection(); } }
public Car FetchById(int CarID) { // this func. helps us fetching a car by a specified carID Car entity = null; // open connection dBConnection.OpenConnection(); SqlCommand cmd = new SqlCommand(); SqlDataReader reader = null; try { //condition to be looked for. string condition = "WHERE aracID = @CarID"; // concating query with the help of DBCommandCreator utility class cmd.CommandText = DBCommandCreator.SELECT(new string[] { "aracID", "model", "ehliyetYasi", "minYas", "anlikKm", "airbag", "bagajHacmi", "gunlukFiyat", "sirketID", "markaID", "gunlukFiyat", "aktiflik" }, DBTableNames.Cars, condition); DBCommandCreator.AddParameter(cmd, "@CarID", DbType.Int32, ParameterDirection.Input, CarID); //execute the query reader = dBConnection.DataReader(cmd); if (reader.HasRows) { while (reader.Read()) { // creating car instances for every row in the response table entity = new Car() { CarID = reader.GetInt32(0), CarModel = reader.GetString(1), RequiredDriverLicenceExp = reader.GetInt32(2), RequiredAge = reader.GetInt32(3), KmInfo = reader.GetInt32(4), HasAirbag = (bool)reader.GetBoolean(5), BaggageCapacity = reader.GetString(6), Company = new CompanyRepository().FetchById(reader.GetInt32(8)), Brand = new BrandRepository().FetchById(reader.GetInt32(9)), DailyCost = (decimal)reader.GetSqlMoney(7), isActive = reader.GetBoolean(11) }; } } return(entity); } catch (Exception ex) { throw new Exception("An error occured while executing FetchAll() in SpiceApp.DataAccessLayer.CarRepository", ex); } finally { if (reader != null) { reader.Close(); } dBConnection.CloseConnection(); } }
public RentDetail FetchById(int id) { // responsible for getting a rent detail with given id from the db. //open connection SqlCommand cmd = new SqlCommand(); dBConnection.OpenConnection(); SqlDataReader reader = null; try { RentDetail entity = null; // will select all the fields in rent detail table. cmd.CommandText = DBCommandCreator.SELECT(new string[] { "*" }, DBTableNames.RentDetail, "WHERE kiraID = @kiraID"); DBCommandCreator.AddParameter(cmd, "@kiraID", DbType.Int32, ParameterDirection.Input, id); reader = dBConnection.DataReader(cmd); if (reader.HasRows) { while (reader.Read()) { // creating rent detail instance from fetched db values. entity = new RentDetail() { StartingDate = reader.GetDateTime(2), EndDate = reader.GetDateTime(3), KmUsed = reader.GetInt32(4), RentID = reader.GetInt32(5), Cost = reader.GetInt32(6), isCarRecievedBack = reader.GetBoolean(7), RecievedBackAt = reader.IsDBNull(8) ? new DateTime(1111, 11, 11) : reader.GetDateTime(8) }; // we have ids of corresponding car and user so get them by using their repositories entity.Car = new CarRepository().FetchById(reader.GetInt32(0)); entity.User = new UserRepository().FetchById(reader.GetInt32(1)); } } return(entity); } catch (Exception ex) { throw new Exception("An error occured in FetchById() function in SpiceApp.DataAccessLayer.RentDetailRepository", ex); } finally { if (reader != null) { reader.Close(); } dBConnection.CloseConnection(); } }
public User FetchByUsername(string Username) { // responsible for getting user from db by given username // it will be mainly used in login operation to check if the user exists with given username. dBConnection.OpenConnection(); SqlCommand cmd = new SqlCommand(); SqlDataReader reader = null; User entity = null; try { // defining commandtext property of command with the help of DBCommandCreator utility class cmd.CommandText = DBCommandCreator.SELECT(new string[] { "kullaniciAd", "sifre", "kisiID", "rolID", "kullaniciID" }, DBTableNames.User, "WHERE kullaniciAd = @kullaniciAd"); DBCommandCreator.AddParameter(cmd, "@kullaniciAd", DbType.String, ParameterDirection.Input, Username); reader = dBConnection.DataReader(cmd); if (reader.HasRows) { while (reader.Read()) { // creating user instance entity = new User() { Username = reader.GetString(0), Password = reader.GetString(1), Person = FetchPersonByID(reader.GetInt32(2)), Role = new Role() { Name = reader.GetInt32(3) == 1 ? "Calisan" : "Musteri", RoleID = reader.GetInt32(3) }, UserID = reader.GetInt32(4) }; } } // return user instance. return(entity); } catch (Exception ex) { throw new Exception("An error occured in FetchByUsername() in SpiceApp.DataAccessLayer.UserRepository", ex); } finally { if (reader != null) { reader.Close(); } dBConnection.CloseConnection(); } }
//USER DETAILS private Person FetchPersonByID(int PersonID) { // responsible for bringing person with given id from the db. Person entity = null; dBConnection.OpenConnection(); SqlCommand cmd = new SqlCommand(); SqlDataReader reader = null; try { cmd.CommandText = DBCommandCreator.SELECT(new string[] { "ad", "soyad", "adres", "cepTel", "email", "ehliyetVerilisTarihi", "dogumTarih", "sirketID", "kisiID" }, DBTableNames.Person, "WHERE kisiID = @kisiID"); DBCommandCreator.AddParameter(cmd, "@kisiID", DbType.Int32, ParameterDirection.Input, PersonID); reader = dBConnection.DataReader(cmd); if (reader.HasRows) { while (reader.Read()) { // create person instance entity = new Person() { Name = reader.GetString(0), Surname = reader.GetString(1), Address = reader.GetString(2), Phone = reader.GetString(3), Email = reader.GetString(4), DriverLicenseDate = reader.GetDateTime(5), Birthday = reader.GetDateTime(6), Company = new CompanyRepository().FetchById(reader.GetInt32(7)), PersonID = reader.GetInt32(8) }; } } // return newly fetched person instance return(entity); } catch (Exception ex) { throw new Exception("An error occured in FetchPersonByID function, SpiceApp.DataAccessLayer.UserRepository", ex); } finally { if (reader != null) { reader.Close(); } dBConnection.CloseConnection(); } }
public List <User> FetchAllCustomers() { // responsible for fetching all customers from the db. // it is planned to be used in employee's screen while making reservation. // Firstly, employee should choose a customer to make reservation. This func. can be used right there. List <User> list = new List <User>(); dBConnection.OpenConnection(); SqlCommand cmd = new SqlCommand(); SqlDataReader reader = null; try { cmd.CommandText = DBCommandCreator.SELECT(new string[] { "kullaniciAd", "kisiID", "rolID", "kullaniciID" }, DBTableNames.User, "WHERE rolID = 1000"); reader = dBConnection.DataReader(cmd); if (reader.HasRows) { while (reader.Read()) { // create user instance with the data brought from the db var entity = new User() { Username = reader.GetString(0), Role = new Role() { Name = reader.GetInt32(2) == 1 ? "Calisan" : "Musteri", RoleID = reader.GetInt32(2) }, UserID = reader.GetInt32(3), Person = FetchPersonByID(reader.GetInt32(1)) }; // add user instance to the list list.Add(entity); } } // return the list which is full of customers return(list); } catch (Exception ex) { throw new Exception("An error occured in FetchAllCustomers() in SpiceApp.DataAccessLayer.UserRepository", ex); } finally { if (reader != null) { reader.Close(); } dBConnection.CloseConnection(); } }
public User FetchById(int UserID) { SqlCommand cmd = new SqlCommand(); SqlDataReader reader = null; dBConnection.OpenConnection(); // responsible for getting user by given user id User entity = null; try { // find user with given userID and get "kullaniciAd", "sifre", "kisiID", "rolID", "kullaniciID" fields from db cmd.CommandText = DBCommandCreator.SELECT(new string[] { "kullaniciAd", "sifre", "kisiID", "rolID", "kullaniciID" }, DBTableNames.User, "WHERE kullaniciID = @kullaniciID"); DBCommandCreator.AddParameter(cmd, "@kullaniciID", DbType.Int32, ParameterDirection.Input, UserID); reader = dBConnection.DataReader(cmd); if (reader.HasRows) { while (reader.Read()) { // create user instance with the data brought from db entity = new User() { Username = reader.GetString(0), Person = FetchPersonByID(reader.GetInt32(2)), Role = new Role() { Name = reader.GetInt32(3) == 1 ? "Calisan" : "Musteri", RoleID = reader.GetInt32(3) }, UserID = reader.GetInt32(4) }; } } //return user instance return(entity); } catch (Exception ex) { throw new Exception("An error occured in FetchById() in SpiceApp.DataAccessLayer.UserRepository", ex); } finally { if (reader != null) { reader.Close(); } dBConnection.CloseConnection(); } }
public List <Brand> FetchAllBrands() { // responsible for getting all brands from the db. List <Brand> brands = new List <Brand>(); //open connection dBConnection.OpenConnection(); SqlCommand cmd = new SqlCommand(); SqlDataReader reader = null; try { // there won't be a condition in this query so we give String.Empty value to the last parameter. cmd.CommandText = DBCommandCreator.SELECT(new string[] { "markaID", "marka" }, DBTableNames.Brand, String.Empty); //execute query and get values reader = dBConnection.DataReader(cmd); if (reader.HasRows) { while (reader.Read()) { // create brand instance var entity = new Brand() { BrandID = reader.GetInt32(0), BrandName = reader.GetString(1) }; //add it to the brand list brands.Add(entity); } } return(brands); } catch (Exception ex) { throw new Exception("An error occured in FetchAllBrands in SpiceApp.DataAccessLayer.BrandRepository", ex); } finally { if (reader != null) { reader.Close(); } dBConnection.CloseConnection(); } }
public Brand FetchById(int BrandID) { // responsible for getting the brand with the given id. Brand entity = null; //open connection dBConnection.OpenConnection(); SqlCommand cmd = new SqlCommand(); SqlDataReader reader = null; try { // creating command text with DBCommandCreator utility class cmd.CommandText = DBCommandCreator.SELECT(new string[] { "markaID", "marka" }, DBTableNames.Brand, "WHERE markaID = @BrandID"); DBCommandCreator.AddParameter(cmd, "@BrandID", DbType.Int32, ParameterDirection.Input, BrandID); // execute the query reader = dBConnection.DataReader(cmd); if (reader.HasRows) { while (reader.Read()) { // creating brand instance with fetched values entity = new Brand() { BrandID = reader.GetInt32(0), BrandName = reader.GetString(1) }; } } return(entity); } catch (Exception ex) { throw new Exception("An error occured while executing FetchByID() in SpiceApp.DataAccessLayer.BrandRepository", ex); } finally { if (reader != null) { reader.Close(); } dBConnection.CloseConnection(); } }
public Company FetchById(int CompanyID) { //responsible for getting company info with given company id. Company entity = null; // we will execute 2 queries so we need another conn. DBConnection tempDB = new DBConnection(); tempDB.OpenConnection(); dBConnection.OpenConnection(); SqlCommand cmd = new SqlCommand(); SqlCommand cmdForScore = new SqlCommand(); SqlDataReader reader = null; try { cmd.CommandText = DBCommandCreator.SELECT(new string[] { "sirketID", "sirketAd", "tel", "sehir", "adres", "aracSayisi", "sirketPuan" }, DBTableNames.Company, "WHERE sirketID = @CompanyID"); DBCommandCreator.AddParameter(cmd, "@CompanyID", DbType.Int32, ParameterDirection.Input, CompanyID); reader = dBConnection.DataReader(cmd); if (reader.HasRows) { while (reader.Read()) { // create company instance with fetched info entity = new Company() { CompanyID = reader.GetInt32(0), CompanyName = reader.GetString(1), Phone = reader.GetString(2), City = reader.GetString(3), Address = reader.GetString(4), CarCount = reader.GetInt32(5), }; // getting score value from a pre-defined stored procedure cmdForScore.CommandText = DBCommandCreator.EXEC(new string[] { "sirketID" }, "SP_puan"); DBCommandCreator.AddParameter(cmdForScore, "@sirketID", DbType.Int32, ParameterDirection.Input, entity.CompanyID); entity.Score = Convert.ToString(tempDB.ExecuteScalar(cmdForScore)); } } return(entity); } catch (Exception ex) { throw new Exception("An error occured while executing FetchByID() in SpiceApp.BusinessLayer.CompanyRepository", ex); } finally { if (reader != null) { reader.Close(); } dBConnection.CloseConnection(); tempDB.CloseConnection(); } }
public bool Insert(User entity) { // responsible for adding new users to the db. Takes a parameter which is type of User. SqlCommand cmd = new SqlCommand(); DBConnection tempDB = new DBConnection(); SqlDataReader reader = null; dBConnection.OpenConnection(); tempDB.OpenConnection(); // clean the attribute. otherwise values left from previous operations may cause conflict _rowsAffected = 0; try { // first add the person object to the db. It holds detailed information of user. InsertPerson(entity.Person); dBConnection.OpenConnection(); int id = 0; // will hold the last added person's id // the last added person will have the greatest id value so bring it from the db. cmd.CommandText = DBCommandCreator.SELECT(new string[] { "kisiID" }, DBTableNames.Person, "WHERE kisiID = (SELECT MAX(kisiID) FROM tblKisi)"); reader = dBConnection.DataReader(cmd); if (reader.HasRows) { while (reader.Read()) { id = reader.GetInt32(0); } } SqlCommand cmdUser = new SqlCommand(); // call a stored proc. to add newly created user to the db. cmdUser.CommandText = DBCommandCreator.EXEC(new string[] { "kullaniciAd", "sifre", "kisiID" }, "SP_kullaniciKayit"); DBCommandCreator.AddParameter(cmdUser, "@kullaniciAd", DbType.String, ParameterDirection.Input, entity.Username); DBCommandCreator.AddParameter(cmdUser, "@sifre", DbType.String, ParameterDirection.Input, entity.Password); DBCommandCreator.AddParameter(cmdUser, "@kisiID", DbType.Int32, ParameterDirection.Input, id); _rowsAffected = tempDB.ExecuteQueries(cmdUser); // if added, affected rows will be greater than 0 return(_rowsAffected > 0); } catch (Exception ex) { throw new Exception("An error occured while executing Insert() in SpiceApp.DataAccessLayer.UserRepository", ex); } finally { if (reader != null) { reader.Close(); } dBConnection.CloseConnection(); tempDB.CloseConnection(); } }