public bool ChangePassword(int UserID, string NewPassword)
{
_rowsAffected = 0;
dBConnection.OpenConnection();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = "UPDATE tblKullanici SET sifre = @sifre WHERE kullaniciID = @kullaniciID";
DBCommandCreator.AddParameter(cmd, "@sifre", DbType.String, ParameterDirection.Input, NewPassword);
DBCommandCreator.AddParameter(cmd, "@kullaniciID", DbType.Int32, ParameterDirection.Input, UserID);
_rowsAffected = dBConnection.ExecuteQueries(cmd);
return(_rowsAffected > 0);
}
catch (Exception ex)
{
throw new Exception("An error occured in ChangePassword() in ", ex);
}
finally
{
dBConnection.CloseConnection();
}
}
public bool DeleteById(int CarID)
{
// This function is responsible for deleting a car by carID
_rowsAffected = 0;
//open connection
dBConnection.OpenConnection();
SqlCommand cmd = new SqlCommand();
try
{
// create the query
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "aracID" }, "SP_aracPasif");
DBCommandCreator.AddParameter(cmd, "@aracID", DbType.Int32, ParameterDirection.Input, CarID);
//execute the query
_rowsAffected = dBConnection.ExecuteQueries(cmd);
return(_rowsAffected > 0);
}
catch (Exception ex)
{
throw new Exception("An error occured while executing DeleteById() in SpiceApp.DataAccessLayer.CarRepository", ex);
}
finally
{
dBConnection.CloseConnection();
}
}
public bool Update(User entity)
{
// responsible for updating a user's info. Takes a param that is type of User
// only address, phone and email infos can be changed. So the other given info won't be cared
// Even if the stated fields didn't change, their original values must be passed to the param of this function.
// clean the attribute. otherwise values left from previous operations may cause conflict
_rowsAffected = 0;
dBConnection.OpenConnection();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = "UPDATE tblKisi SET adres = @adres, cepTel = @cepTel, email = @email WHERE kisiID = @kisiID";
DBCommandCreator.AddParameter(cmd, "@adres", DbType.String, ParameterDirection.Input, entity.Person.Address);
DBCommandCreator.AddParameter(cmd, "@cepTel", DbType.String, ParameterDirection.Input, entity.Person.Phone);
DBCommandCreator.AddParameter(cmd, "@email", DbType.String, ParameterDirection.Input, entity.Person.Email);
DBCommandCreator.AddParameter(cmd, "@kisiID", DbType.Int32, ParameterDirection.Input, entity.Person.PersonID);
_rowsAffected = dBConnection.ExecuteQueries(cmd);
// if updated, affected rows will be greater than 0
return(_rowsAffected > 0);
}
catch (Exception ex)
{
throw new Exception("An error occured in Update() func. in SpiceApp.DataAccessLayer.UserRepository", ex);
}
finally
{
dBConnection.CloseConnection();
}
}
public bool Insert(Brand entity)
{
// responsible for inserting new brands to the db.
// clean the attribute. otherwise values left from previous operations may cause conflict
_rowsAffected = 0;
//open connection
SqlCommand cmd = new SqlCommand();
dBConnection.OpenConnection();
try
{
cmd.CommandText = DBCommandCreator.INSERT(new string[] { "marka" }, DBTableNames.Brand);
DBCommandCreator.AddParameter(cmd, "@marka", DbType.String, ParameterDirection.Input, entity.BrandName);
//execute query
_rowsAffected = dBConnection.ExecuteQueries(cmd);
// if added, affected rows will be greater than 0
return(_rowsAffected > 0);
}
catch (Exception ex)
{
throw new Exception("An error occured in Insert() function in SpiceApp.DataAccessLayer.BrandRepository", ex);
}
finally
{
dBConnection.CloseConnection();
}
}
public bool ReActivateCarById(int CarID)
{
// brings back the deleted car (incase a car is not available for a short period for the reasons like fixing issues so then it can be available and active again).
_rowsAffected = 0;
SqlCommand cmd = new SqlCommand();
dBConnection.OpenConnection();
try
{
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "aracID" }, "SP_aracAktif");
DBCommandCreator.AddParameter(cmd, "@aracID", DbType.Int32, ParameterDirection.Input, CarID);
_rowsAffected = dBConnection.ExecuteQueries(cmd);
return(_rowsAffected > 0);
}
catch (Exception ex)
{
throw new Exception("An error occured in ReActivateCarById() in SpiceApp.DataAccessLayer.CarRepository", ex);
}
finally
{
dBConnection.CloseConnection();
}
}
public bool Insert(RentDetail entity)
{
// responsible for adding new rent detail to the db.
// clean the attribute. otherwise values left from previous operations may cause conflict
_rowsAffected = 0;
//open connection
SqlCommand cmd = new SqlCommand();
dBConnection.OpenConnection();
try
{
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "rezID" }, "SP_anahtarTeslim");
DBCommandCreator.AddParameter(cmd, "@rezID", DbType.Int32, ParameterDirection.Input, entity.RentID);
// execute the query
_rowsAffected = dBConnection.ExecuteQueries(cmd);
// if added, affected rows will be greater than 0
return(_rowsAffected > 0);
}
catch (Exception ex)
{
throw new Exception("An error occured in ReservaationRepository in SpiceApp.DataAccessLayer.ReservationRepository ", ex);
}
finally
{
dBConnection.CloseConnection();
}
}
public bool ReturnCarToCompany(int RentID, int KmInfo, int Score)
{
// responsible for completing the whole rent process. When the customer return the car to company, rent process ends.
// clean the attribute. otherwise values left from previous operations may cause conflict
_rowsAffected = 0;
dBConnection.OpenConnection();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "kiraID", "anlikKm", "puan" }, "SP_aracIade");
DBCommandCreator.AddParameter(cmd, "@kiraID", DbType.Int32, ParameterDirection.Input, RentID);
DBCommandCreator.AddParameter(cmd, "@anlikKm", DbType.Int32, ParameterDirection.Input, KmInfo);
DBCommandCreator.AddParameter(cmd, "@puan", DbType.Int32, ParameterDirection.Input, Score);
_rowsAffected = dBConnection.ExecuteQueries(cmd);
// if succesful, affected rows will be greater than 0
return(_rowsAffected > 0);
}
catch (Exception ex)
{
throw new Exception("An error occured in ReturnCarToCompany() in SpiceApp.DataAccessLayer.RentDetailRepository", ex);
}
finally
{
dBConnection.CloseConnection();
}
}
public bool DeleteById(int ReservationID)
{
dBConnection.OpenConnection();
SqlCommand cmd = new SqlCommand();
// responsible for cancelling reservation with the given res. id.
_rowsAffected = 0;
try
{
// create the query
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "rezID" }, "SP_rezIptal");
DBCommandCreator.AddParameter(cmd, "@rezID", DbType.Int32, ParameterDirection.Input, ReservationID);
//execute the command
_rowsAffected = dBConnection.ExecuteQueries(cmd);
return(_rowsAffected > 0);
}
catch (Exception ex)
{
throw new Exception("An error occured in DeleteById() func. in SpiceApp.DataAccessLayer.ReservationRepository", ex);
}
finally
{
// close connection
dBConnection.CloseConnection();
}
}
public bool Insert(Reservation entity)
{
/* Car.CarID, User.UserID, DateTime startingDate, DateTime endDate fields need to be given in the parameter */
SqlCommand cmd = new SqlCommand();
dBConnection.OpenConnection();
_rowsAffected = 0;
try
{
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "aracID", "kullaniciID", "basTarih", "bitisTarih" }, "SP_rezYap");
DBCommandCreator.AddParameter(cmd, "@aracID", DbType.Int32, ParameterDirection.Input, entity.Car.CarID);
DBCommandCreator.AddParameter(cmd, "@kullaniciID", DbType.Int32, ParameterDirection.Input, entity.User.UserID);
DBCommandCreator.AddParameter(cmd, "@basTarih", DbType.String, ParameterDirection.Input, DateConverter.ToDatabase(entity.StartingDate));
DBCommandCreator.AddParameter(cmd, "@bitisTarih", DbType.String, ParameterDirection.Input, DateConverter.ToDatabase(entity.EndDate));
_rowsAffected = dBConnection.ExecuteQueries(cmd);
return(_rowsAffected > 0);
}
catch (Exception ex)
{
throw new Exception("An error occured while executing MakeReservation() function in SpiceApp.DataAccessLayer.CarRepository", ex);
}
finally
{
dBConnection.CloseConnection();
}
}
public bool Update(Brand entity)
{
// resposible for changing name of the brand in case it was given wrong.
// clean the attribute. otherwise values left from previous operations may cause conflict
_rowsAffected = 0;
//open connection
dBConnection.OpenConnection();
SqlCommand cmd = new SqlCommand();
try
{
//create query
cmd.CommandText = "UPDATE tblMarka SET marka = @marka WHERE markaID = @markaID";
DBCommandCreator.AddParameter(cmd, "@marka", DbType.String, ParameterDirection.Input, entity.BrandName);
DBCommandCreator.AddParameter(cmd, "@markaID", DbType.Int32, ParameterDirection.Input, entity.BrandID);
//execute
_rowsAffected = dBConnection.ExecuteQueries(cmd);
// if updated, affected rows will be greater than 0
return(_rowsAffected > 0);
}
catch (Exception ex)
{
throw new Exception("An error occured in Update() func. in SpiceApp.DataAccessLayer.BrandRepository", ex);
}
finally
{
dBConnection.CloseConnection();
}
}
public bool Update(Car entity)
{
dBConnection.OpenConnection();
SqlCommand cmd = new SqlCommand();
_rowsAffected = 0;
try
{
cmd.CommandText = "UPDATE tblArac SET ehliyetYasi = @ehliyetYasi, minYas = @minYas, airbag = @airbag, gunlukFiyat = @gunlukFiyat WHERE aracID = @aracID";
DBCommandCreator.AddParameter(cmd, "@ehliyetYasi", DbType.Int32, ParameterDirection.Input, entity.RequiredAge);
DBCommandCreator.AddParameter(cmd, "@minYas", DbType.Int32, ParameterDirection.Input, entity.RequiredAge);
DBCommandCreator.AddParameter(cmd, "@airbag", DbType.Boolean, ParameterDirection.Input, entity.HasAirbag);
DBCommandCreator.AddParameter(cmd, "@gunlukFiyat", DbType.Decimal, ParameterDirection.Input, entity.DailyCost);
DBCommandCreator.AddParameter(cmd, "@aracID", DbType.Int32, ParameterDirection.Input, entity.CarID);
_rowsAffected = dBConnection.ExecuteQueries(cmd);
return(_rowsAffected > 0);
}
catch (Exception ex)
{
throw new Exception("An error occured in Update() func. SpiceApp.DataAccessLayer.CarRepository", ex);
}
finally
{
dBConnection.CloseConnection();
}
}
public List <RentDetail> FetchAllRentDetail(int UserID)
{
// check for out dated reservations, if there is then cancel them
DBAdjuster.AdjustReservations();
//responsible for fetching all rent details from db according to given userID.
// if userID belongs to a customer, stored procedure will return customer's rent details
// if userID belongs to a employee, stored procedure will return employee's company's rent details
dBConnection.OpenConnection();
SqlCommand cmd = new SqlCommand();
SqlDataReader reader = null;
try
{
List <RentDetail> rents = new List <RentDetail>();
// define command text with the help of DBCommandCreator utility class then give user ıd parameter to the query.
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "kullaniciID" }, "SP_kiraGoruntule");
DBCommandCreator.AddParameter(cmd, "@kullaniciID", DbType.Int32, ParameterDirection.Input, UserID);
reader = dBConnection.DataReader(cmd);
if (reader.HasRows)
{
while (reader.Read())
{
// create rent detail instance
var entity = new RentDetail()
{
StartingDate = reader.GetDateTime(3),
EndDate = reader.GetDateTime(4),
KmUsed = reader.GetInt32(5),
Cost = reader.GetInt32(6),
RentID = reader.GetInt32(0),
isCarRecievedBack = reader.GetBoolean(7),
RecievedBackAt = reader.IsDBNull(8) ? new DateTime(1111, 11, 11) : reader.GetDateTime(8)
};
entity.Car = new CarRepository().FetchById(reader.GetInt32(1));
entity.User = new UserRepository().FetchById(reader.GetInt32(2));
// add the instance to the list
rents.Add(entity);
}
}
return(rents);
}
catch (Exception ex)
{
throw new Exception("An error occured in FetchAllRentDetail() func. in SpiceApp.DataAccessLayer.RentDetailRepository", ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
dBConnection.CloseConnection();
}
}
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 List <Car> FetchAvailableCarsForResv(int UserID, DateTime startingDate, DateTime endDate)
{ // This function is responsible for fetching all available cars by userID for available reservation
// will hold valid cars for reservation criterias (startingdate, endtime, user's driver license exp. and user's age)
List <Car> cars = new List <Car>();
SqlDataReader reader = null;
SqlCommand cmd = new SqlCommand();
dBConnection.OpenConnection();
//first, adjust previous reservations and discard them.
DBAdjuster.AdjustReservations();
try
{
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "kisiID", "basTarih", "bitisTarih" }, "SP_uygunArac");
DBCommandCreator.AddParameter(cmd, "@kisiID", DbType.Int32, ParameterDirection.Input, UserID);
DBCommandCreator.AddParameter(cmd, "@basTarih", DbType.String, ParameterDirection.Input, DateConverter.ToDatabase(startingDate));
DBCommandCreator.AddParameter(cmd, "@bitisTarih", DbType.String, ParameterDirection.Input, DateConverter.ToDatabase(endDate));
reader = dBConnection.DataReader(cmd);
if (reader.HasRows)
{
while (reader.Read())
{
// creating car instances for every row in the response table
var car = new Car()
{
CarID = reader.GetInt32(0),
CarModel = reader.GetString(1),
RequiredDriverLicenceExp = reader.GetInt32(2),
RequiredAge = reader.GetInt32(3),
KmInfo = reader.GetInt32(5),
HasAirbag = (bool)reader.GetBoolean(6),
BaggageCapacity = reader.GetString(7),
DailyCost = (decimal)reader.GetSqlMoney(8),
Company = new CompanyRepository().FetchById(reader.GetInt32(9)),
Brand = new BrandRepository().FetchById(reader.GetInt32(10))
};
cars.Add(car);
}
}
return(cars);
}
catch (Exception ex)
{
throw new Exception("An error occured in FetchAllByUserForResv() 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 List <Reservation> FetchAllByUserId(int UserID)
{
// check for out dated reservations, if there is then cancel them.
DBAdjuster.AdjustReservations();
//this function will be used for showing reservations either for user or employee. If the given ID belongs to employee, function will show companies reservations.
List <Reservation> list = new List <Reservation>();
SqlCommand cmd = new SqlCommand();
SqlDataReader reader = null;
try
{
dBConnection.OpenConnection();
// defining the command text and giving input parameter's value
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "kullaniciID" }, "SP_rezGoruntule");
DBCommandCreator.AddParameter(cmd, "@kullaniciID", DbType.Int32, ParameterDirection.Input, UserID);
using (reader = dBConnection.DataReader(cmd))
{
if (reader.HasRows)
{
while (reader.Read())
{
// create reservation instance
var entity = new Reservation()
{
Car = new CarRepository().FetchById(reader.GetInt32(1)),
User = new UserRepository().FetchById(UserID),
StartingDate = reader.GetDateTime(3),
EndDate = reader.GetDateTime(4),
ReservationID = reader.GetInt32(0),
ReservationMadeAt = reader.GetDateTime(5),
};
// To define reservation state, we have 3 different boolean values in database.
// So ConvertResvState function composes these 3 values to one to make client's work easier.
entity.ReservationState = ResvAttrConverter.ConvertResvState(reader.GetBoolean(6), reader.GetBoolean(7), reader.GetBoolean(8));
entity.Company = entity.Car.Company;
list.Add(entity);
}
}
}
return(list);
}
catch (Exception ex)
{
throw new Exception("An error occured in FetchAllByUserId() func. in SpiceApp.DataAccessLayer.ReservationRepository", 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();
}
}
//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 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();
}
}
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 List <DailyKmInfo> DailyKmReport(int userID)
{
List <DailyKmInfo> data = new List <DailyKmInfo>();
SqlDataReader reader = null;
try
{
dBConnection.OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "kullaniciID" }, "SP_gunlukKmListele");
DBCommandCreator.AddParameter(cmd, "@kullaniciID", DbType.Int32, ParameterDirection.Input, userID);
reader = dBConnection.DataReader(cmd);
if (reader.HasRows)
{
while (reader.Read())
{
var temp = new DailyKmInfo()
{
RentID = reader.GetInt32(0),
BrandName = reader.GetString(1),
CarModel = reader.GetString(2),
DailyKm = reader.GetInt32(3),
Date = reader.GetDateTime(4),
State = "Bilgi Yok"
};
data.Add(temp);
}
}
return(data);
}
catch (Exception ex)
{
throw new Exception("An error occured in DailyKmReport() func. in SpiceApp.DataAccessLayer.ReportRepository", 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 List <RentRate> MonthlyRentRate(int UserID, DateTime Term)
{
List <RentRate> data = new List <RentRate>();
SqlDataReader reader = null;
try
{
dBConnection.OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "kullaniciID", "term" }, "SP_aylikAracOran");
DBCommandCreator.AddParameter(cmd, "@kullaniciID", DbType.Int32, ParameterDirection.Input, UserID);
DBCommandCreator.AddParameter(cmd, "@term", DbType.DateTime, ParameterDirection.Input, DateConverter.ToDatabase(Term));
reader = dBConnection.DataReader(cmd);
if (reader.HasRows)
{
while (reader.Read())
{
var temp = new RentRate()
{
CarID = reader.GetInt32(0),
BrandName = reader.GetString(1),
CarModel = reader.GetString(2),
MonthRate = reader.GetDecimal(3),
Term = reader.GetString(4)
};
data.Add(temp);
}
}
return(data);
}
catch (Exception ex)
{
throw new Exception("An error occured in MonthlyRentRate() func. in SpiceApp.DataAccessLayer.ReportRepository", ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
dBConnection.CloseConnection();
}
}
public bool Insert(Car entity)
{
_rowsAffected = 0;
// this func. helps us adding a new car to a specified company.
// open connection
dBConnection.OpenConnection();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = DBCommandCreator.INSERT(new string[] { "model", "ehliyetYasi",
"minYas",
"anlikKm", "airbag",
"bagajHacmi", "gunlukFiyat",
"sirketID", "markaID", "gunlukKm" },
DBTableNames.Cars);
// adding parameter values
DBCommandCreator.AddParameter(cmd, "@model", DbType.String, ParameterDirection.Input, entity.CarModel);
DBCommandCreator.AddParameter(cmd, "@ehliyetYasi", DbType.Int32, ParameterDirection.Input, entity.RequiredDriverLicenceExp);
DBCommandCreator.AddParameter(cmd, "@minYas", DbType.Int32, ParameterDirection.Input, entity.RequiredAge);
DBCommandCreator.AddParameter(cmd, "@airbag", DbType.Boolean, ParameterDirection.Input, entity.HasAirbag);
DBCommandCreator.AddParameter(cmd, "@bagajHacmi", DbType.String, ParameterDirection.Input, entity.BaggageCapacity);
DBCommandCreator.AddParameter(cmd, "@gunlukFiyat", DbType.Decimal, ParameterDirection.Input, entity.DailyCost);
DBCommandCreator.AddParameter(cmd, "@anlikKm", DbType.Int32, ParameterDirection.Input, entity.KmInfo);
DBCommandCreator.AddParameter(cmd, "@sirketID", DbType.Int32, ParameterDirection.Input, entity.Company.CompanyID);
DBCommandCreator.AddParameter(cmd, "@markaID", DbType.Int32, ParameterDirection.Input, entity.Brand.BrandID);
DBCommandCreator.AddParameter(cmd, "@gunlukKm", DbType.Int32, ParameterDirection.Input, entity.DailyKm);
_rowsAffected = dBConnection.ExecuteQueries(cmd);
return(_rowsAffected > 0);
}
catch (Exception ex)
{
throw ex;
}
finally
{
dBConnection.CloseConnection();
}
}
public List <OverKmInfo> OverKmInfo(int UserID)
{
SqlDataReader reader = null;
List <OverKmInfo> data = new List <OverKmInfo>();
try
{
dBConnection.OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "kullaniciID" }, "SP_gunlukAsimOran ");
DBCommandCreator.AddParameter(cmd, "@kullaniciID", DbType.Int32, ParameterDirection.Input, UserID);
reader = dBConnection.DataReader(cmd);
if (reader.HasRows)
{
while (reader.Read())
{
var temp = new OverKmInfo()
{
CompanyName = reader.GetString(0),
CompanyBalance = reader.GetDecimal(1),
Score = reader.GetDecimal(2),
OverKmRate = reader.GetDecimal(3),
Term = reader.GetString(4)
};
data.Add(temp);
}
}
return(data);
}
catch (Exception ex)
{
throw new Exception("An error occured in OverKmInfo() func. in SpiceApp.DataAccessLayer.ReportRepository", ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
dBConnection.CloseConnection();
}
}
public List <CompanyBalanceInfo> CompanyBalanceInfo(int UserID)
{
List <CompanyBalanceInfo> data = new List <CompanyBalanceInfo>();
SqlDataReader reader = null;
try
{
dBConnection.OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "kullaniciID" }, "SP_sirketDurum");
DBCommandCreator.AddParameter(cmd, "@kullaniciID", DbType.Int32, ParameterDirection.Input, UserID);
reader = dBConnection.DataReader(cmd);
if (reader.HasRows)
{
while (reader.Read())
{
var temp = new CompanyBalanceInfo()
{
CarCount = reader.GetInt32(0),
TotalIncome = reader.GetDecimal(1),
TotalExpenses = reader.GetDecimal(2),
NetValue = reader.GetDecimal(3)
};
data.Add(temp);
}
}
return(data);
}
catch (Exception ex)
{
throw new Exception("An error occured in CompanyBalanceInfo() func. in SpiceApp.DataAccessLayer.ReportRepository", ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
dBConnection.CloseConnection();
}
}
private bool InsertPerson(Person entity)
{
_rowsAffected = 0;
SqlCommand cmd = new SqlCommand();
dBConnection.OpenConnection();
// responsible for adding new person to the db
try
{
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "ad", "soyad", "adres", "cepTel", "email", "ehliyet", "dogTarih" }, "SP_kisiKayit");
// give input parameters' values to the query.
DBCommandCreator.AddParameter(cmd, "@ad", DbType.String, ParameterDirection.Input, entity.Name);
DBCommandCreator.AddParameter(cmd, "@soyad", DbType.String, ParameterDirection.Input, entity.Surname);
DBCommandCreator.AddParameter(cmd, "@email", DbType.String, ParameterDirection.Input, entity.Email);
DBCommandCreator.AddParameter(cmd, "@adres", DbType.String, ParameterDirection.Input, entity.Address);
DBCommandCreator.AddParameter(cmd, "@cepTel", DbType.String, ParameterDirection.Input, entity.Phone);
DBCommandCreator.AddParameter(cmd, "@ehliyet", DbType.Date, ParameterDirection.Input, DateConverter.ToDatabase(entity.DriverLicenseDate));
DBCommandCreator.AddParameter(cmd, "@dogTarih", DbType.Date, ParameterDirection.Input, DateConverter.ToDatabase(entity.Birthday));
// execute the stored procedure.
_rowsAffected = dBConnection.ExecuteQueries(cmd);
return(_rowsAffected > 0);
}
catch (Exception ex)
{
throw new Exception("An error occured in InsertPerson function, SpiceApp.DataAccessLayer.UserRepository", ex);
}
finally
{
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();
}
}
