//=============== 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); }