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