//=============== For borrowing ===============
// form get data: same param @idBorrower
public List <TradingDetail> getDataBorrower(int idBorrower, int status)
{
List <TradingDetail> tradings = new List <TradingDetail>();
SqlCommand cmd = null;
try
{
string sqlCommand = "SELECT t.id, t.idBook, b.title, t.createDate, t.statusBook, t.idOwner, u.username"
+ " FROM Trading t"
+ " INNER JOIN Book b on b.id = t.idBook"
+ " INNER JOIN [User] u on u.id = t.idOwner"
+ " where t.statusBook = @statusBook and t.idBorrower = @idBorrower";
cmd = new SqlCommand(sqlCommand, connection);
cmd.Parameters.Add("@statusBook", SqlDbType.Int).Value = status;
cmd.Parameters.Add("@idBorrower", SqlDbType.Int).Value = idBorrower;
connection.Open(); // open connect
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
TradingDetail t = new TradingDetail();
t.id = reader.GetInt32(0);
t.bookID = reader.GetInt32(1);
t.title = reader.GetString(2);
t.createDate = reader.GetDateTime(3).ToString("MM/dd/yyyy");
t.status = reader.GetInt32(4);
t.userID = reader.GetInt32(5);
t.username = reader.GetString(6);
tradings.Add(t);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
cmd.Dispose();
}
return(tradings);
}
//================================= For get data trading =================================
//=============== For lending ===============
// form get data: same param @idOwner
public List <TradingDetail> getDataOwner(int idOwner, int status, string sqlCommand)
{
List <TradingDetail> tradings = new List <TradingDetail>();
SqlCommand cmd = null;
try
{
cmd = new SqlCommand(sqlCommand, connection);
cmd.Parameters.Add("@statusBook", SqlDbType.Int).Value = status;
cmd.Parameters.Add("@idOwner", SqlDbType.Int).Value = idOwner;
connection.Open(); // open connect
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
TradingDetail t = new TradingDetail();
t.id = reader.GetInt32(0);
t.bookID = reader.GetInt32(1);
t.title = reader.GetString(2);
t.createDate = reader.GetDateTime(3).ToString("MM/dd/yyyy");
t.status = reader.GetInt32(4);
t.userID = reader.GetInt32(5);
t.username = reader.IsDBNull(6) ? "N/A" : reader.GetString(6);
tradings.Add(t);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
cmd.Dispose();
}
return(tradings);
}
