/// <summary>
/// Method - Get list of all users
/// </summary>
/// <returns>Employee</returns>
public List<RideRequest> GetActiveRideRequests()
{
try
{
using (connection)
{
requestList = new List<RideRequest>();
connection = new SqlConnection(DAL.ConnectionString);
string sqlSelectString = "SELECT userid, fromAddress, toAddress, pickupDate, pickupTime, status, recID from [RideRequests] where status = 1";
command = new SqlCommand(sqlSelectString, connection);
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
RideRequest rideRequest = new RideRequest();
User user = new User();
rideRequest.UserID = reader[0].ToString();
rideRequest.OriginAddress = reader[1].ToString();
rideRequest.DestinationAddress = reader[2].ToString();
rideRequest.PickupDate = Convert.ToString(reader[3]);
rideRequest.PickupTime = Convert.ToString(reader[4]);
rideRequest.Status = Convert.ToInt32(reader[5].ToString());
rideRequest.RecID = Convert.ToInt64(reader[6]);
user = userDAL.GetUser(rideRequest.UserID);
rideRequest.Requestor = user;
requestList.Add(rideRequest);
}
command.Connection.Close();
return requestList;
}
}
catch (Exception ex)
{
// err.ErrorMessage = ex.Message.ToString();
throw;
}
}
public List<RideOffer> GetActiveOffers(Int64 requestRecID)
{
try
{
using (connection)
{
rideOffersList = new List<RideOffer>();
connection = new SqlConnection(DAL.ConnectionString);
string sqlSelectString = "SELECT RideRequestRecID, DriverUserID, Notes, Status from [RideOffers] where Status in (1, 2) and RideRequestRecID = " + requestRecID;
command = new SqlCommand(sqlSelectString, connection);
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
RideOffer rideOffer = new RideOffer();
User user = new User();
rideOffer.RideRequestRecID = Convert.ToInt64(reader[0]);
rideOffer.DriverUserID = reader[1].ToString();
rideOffer.Notes = reader[2].ToString();
rideOffer.Status = Convert.ToInt32(reader[3]);
user = userDAL.GetUser(rideOffer.DriverUserID);
rideOffer.Driver = user;
rideOffersList.Add(rideOffer);
}
command.Connection.Close();
return rideOffersList;
}
}
catch (Exception ex)
{
// err.ErrorMessage = ex.Message.ToString();
throw;
}
}
public bool insert(User user)
{
bool isInserted;
try
{
using (connection)
{
userList = new List<User>();
connection = new SqlConnection(DAL.ConnectionString);
string sqlSelectString = "INSERT into [User] (firstname, lastname, userid, password, phone, streetaddress, city, zipcode, state) values('"
+ user.FirstName + "','"
+ user.LastName + "','"
+ user.UserID + "','"
+ user.Password + "','"
+ user.Phone + "','"
+ user.StreetAddress + "','"
+ user.City + "','"
+ user.ZipCode + "','"
+ user.State+ "')";
command = new SqlCommand(sqlSelectString, connection);
command.Connection.Open();
int totalRowsAffected = command.ExecuteNonQuery();
command.Connection.Close();
isInserted = totalRowsAffected > 0;
return isInserted;
}
}
catch (Exception ex)
{
// err.ErrorMessage = ex.Message.ToString();
throw;
}
}
/// <summary>
/// Database SELECT - Get a User
/// </summary>
/// <param name="ID"></param>
/// <returns></returns>
public User GetUser(string userId)
{
User user = new User();
try
{
using (connection)
{
connection = new SqlConnection(DAL.ConnectionString);
string sqlSelectString = "SELECT firstname, lastname, userid, phone, streetaddress, zipcode, city, state, password from [User] where userid = '" + userId + "'";
command = new SqlCommand(sqlSelectString, connection);
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
user.FirstName = reader[0].ToString();
user.LastName = reader[1].ToString();
user.UserID = reader[2].ToString();
user.Phone = reader[3].ToString();
user.StreetAddress = reader[4].ToString();
user.ZipCode = reader[5].ToString();
user.City = reader[6].ToString();
user.State = reader[7].ToString();
user.Password = reader[8].ToString();
}
command.Connection.Close();
return user;
}
}
catch (Exception ex)
{
// err.ErrorMessage = ex.Message.ToString();
throw;
}
}
/// <summary>
/// Method - Get list of all users
/// </summary>
/// <returns>Employee</returns>
private List<User> GetUsers()
{
try
{
using (connection)
{
userList = new List<User>();
connection = new SqlConnection(DAL.ConnectionString);
string sqlSelectString = "SELECT firstname, lastname, userid, phone from [User]";
command = new SqlCommand(sqlSelectString, connection);
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
User user = new User();
user.FirstName = reader[0].ToString();
user.LastName = reader[1].ToString();
user.UserID = reader[2].ToString();
user.Phone = reader[3].ToString();
userList.Add(user);
}
command.Connection.Close();
return userList;
}
}
catch (Exception ex)
{
// err.ErrorMessage = ex.Message.ToString();
throw;
}
}