public static Order GetLastOrder()
{
Order order = new Order();
SqlCommand command = null;
string selectStatement =
"SELECT OrderId, MAX(OrderDate), Username, Firstname, Lastname, Address, City, State, PostalCode, Country, Phone, Email, Total " +
"FROM Orders " +
"GROUP BY OrderId, Username, Firstname, Lastname, Address, City, State, PostalCode, Country, Phone, Email, Total";
try
{
command = new SqlCommand(selectStatement, MusicStoreDb.GetConnection());
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
int orderIdOrdinal = reader.GetOrdinal("OrderId");
int usernameOrdinal = reader.GetOrdinal("Username");
int firstNameOrdinal = reader.GetOrdinal("Firstname");
int lastNameOrdinal = reader.GetOrdinal("Lastname");
int addressOrdinal = reader.GetOrdinal("Address");
int cityOrdinal = reader.GetOrdinal("City");
int stateOrdinal = reader.GetOrdinal("State");
int postalCodeOrdinal = reader.GetOrdinal("PostalCode");
int countryOrdinal = reader.GetOrdinal("Country");
int phoneOrdinal = reader.GetOrdinal("Phone");
int emailOrdinal = reader.GetOrdinal("Email");
int totalOrdinal = reader.GetOrdinal("Total");
while (reader.Read())
{
order.OrderId = reader.GetInt32(orderIdOrdinal);
order.UserName = reader.GetString(usernameOrdinal);
order.FirstName = reader.GetString(firstNameOrdinal);
order.LastName = reader.GetString(lastNameOrdinal);
order.Address = reader.GetString(addressOrdinal);
order.City = reader.GetString(cityOrdinal);
order.State = reader.GetString(stateOrdinal);
order.PostalCode = reader.GetString(postalCodeOrdinal);
order.Country = reader.GetString(countryOrdinal);
order.Phone = reader.GetString(phoneOrdinal);
order.Email = reader.GetString(emailOrdinal);
order.Total = Convert.ToDouble(reader.GetDecimal(totalOrdinal));
}
reader.Close();
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
command.Connection.Close();
}
return(order);
}
public static IList <Album> GetAlbums()
{
IList <Album> albumList = new List <Album>();
SqlCommand command = null;
string selectStament = "SELECT AlbumId, GenreId, ArtistId, Title, Price, AlbumArtUrl " +
"FROM ALBUM";
try
{
command = new SqlCommand(selectStament, MusicStoreDb.GetConnection());
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
int albumIdOrdinal = reader.GetOrdinal("AlbumId");
int genreIdOrdinal = reader.GetOrdinal("GenreId");
int artistIdOrdinal = reader.GetOrdinal("ArtistId");
int titleOrdinal = reader.GetOrdinal("Title");
int priceOrdinal = reader.GetOrdinal("Price");
int albumArtUrlOrdinal = reader.GetOrdinal("AlbumArtUrl");
while (reader.Read())
{
Album album = new Album();
album.AlbumId = reader.GetInt32(albumIdOrdinal);
album.GenreId = reader.GetInt32(genreIdOrdinal);
album.ArtistId = reader.GetInt32(artistIdOrdinal);
album.Title = reader.GetString(titleOrdinal);
album.Price = reader.GetSqlDecimal(priceOrdinal).ToDouble();
album.AlbumArtUrl = reader.GetString(albumArtUrlOrdinal);
albumList.Add(album);
}
reader.Close();
}
catch (SqlException ex)
{
Console.Write(ex.Message);
}
finally
{
command.Connection?.Close();
}
return(albumList);
}
public static IList <OrderDetail> GetOrderDetailsByOrderId(int orderId)
{
IList <OrderDetail> orderDetailList = new List <OrderDetail>();
SqlCommand command = null;
string selectStatement =
"SELECT OrderDetailId, OrderId, AlbumId, Quantity, UnitPrice " +
"FROM OrderDetail " +
"WHERE OrderId=@orderId";
try
{
command = new SqlCommand(selectStatement, MusicStoreDb.GetConnection());
command.Parameters.Add(new SqlParameter("@orderId", orderId));
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
int orderDetailIdOrdinal = reader.GetOrdinal("OrderDetailId");
int orderIdOrdinal = reader.GetOrdinal("OrderId");
int albumIdOrdinal = reader.GetOrdinal("AlbumId");
int quantityOrdinal = reader.GetOrdinal("Quantity");
int unitpriceOrdinal = reader.GetOrdinal("UnitPrice");
while (reader.Read())
{
OrderDetail orderDetail = new OrderDetail();
orderDetail.OrderDetailId = reader.GetInt32(orderDetailIdOrdinal);
orderDetail.OrderId = reader.GetInt32(orderIdOrdinal);
orderDetail.AlbumId = reader.GetInt32(albumIdOrdinal);
orderDetail.Quantity = reader.GetInt32(quantityOrdinal);
orderDetail.UnitPrice = Convert.ToDouble(reader.GetSqlDecimal(unitpriceOrdinal));
orderDetailList.Add(orderDetail);
}
reader.Close();
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
command.Connection?.Close();
}
return(orderDetailList);
}
public static int GetHighestOrderId()
{
int highestOrderId = 0;
SqlCommand command = null;
string selectStatement = "SELECT MAX(OrderId) " +
"FROM Orders";
try
{
command = new SqlCommand(selectStatement, MusicStoreDb.GetConnection());
command.Connection.Open();
highestOrderId = Convert.ToInt32(command.ExecuteScalar());
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
command.Connection?.Close();
}
return(highestOrderId);
}
public static int UpdateOrderAndOrderDetail(Order order, OrderDetail orderDetail)
{
int orderResult = 0;
int orderDetailResult = 0;
int result = 0;
SqlCommand orderCommand = null;
SqlCommand orderDetailCommand = null;
SqlConnection connection = MusicStoreDb.GetConnection();
SqlTransaction transaction = null;
SqlParameter orderId = new SqlParameter("@orderId", SqlDbType.Int);
orderId.Direction = ParameterDirection.Output;
string orderInsertStatement = "InsertOrder";
string orderDetailInsertStatement = "INSERT INTO ORDERDETAIL(OrderId, AlbumId, Quantity, UnitPrice) " +
"VALUES(@orderId, @albumId, @quantity, @unitPrice)";
try
{
connection.Open();
transaction = connection.BeginTransaction();
orderCommand = new SqlCommand(orderInsertStatement, connection, transaction);
orderCommand.CommandType = CommandType.StoredProcedure;
orderDetailCommand = new SqlCommand(orderDetailInsertStatement, connection, transaction);
orderCommand.Parameters.Add(orderId);
orderCommand.Parameters.Add(new SqlParameter("@orderDate", order.OrderDate));
orderCommand.Parameters.Add(new SqlParameter("@username", order.UserName));
orderCommand.Parameters.Add(new SqlParameter("@firstName", order.FirstName));
orderCommand.Parameters.Add(new SqlParameter("@lastName", order.LastName));
orderCommand.Parameters.Add(new SqlParameter("@address", order.Address));
orderCommand.Parameters.Add(new SqlParameter("@city", order.City));
orderCommand.Parameters.Add(new SqlParameter("@state", order.State));
orderCommand.Parameters.Add(new SqlParameter("@postalCode", order.PostalCode));
orderCommand.Parameters.Add(new SqlParameter("@country", order.Country));
orderCommand.Parameters.Add(new SqlParameter("@phone", "00/123456789"));
orderCommand.Parameters.Add(new SqlParameter("@email", "*****@*****.**"));
orderCommand.Parameters.Add(new SqlParameter("@total", order.Total));
orderResult = orderCommand.ExecuteNonQuery();
orderDetailCommand.Parameters.Add(new SqlParameter("@orderId", orderId.Value));
orderDetailCommand.Parameters.Add(new SqlParameter("@albumId", orderDetail.AlbumId));
orderDetailCommand.Parameters.Add(new SqlParameter("@quantity", orderDetail.Quantity));
orderDetailCommand.Parameters.Add(new SqlParameter("@unitPrice", orderDetail.UnitPrice));
orderDetailResult = orderDetailCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
transaction.Rollback();
}
finally
{
connection?.Close();
}
if (orderResult > 0 && orderDetailResult > 0)
{
result = 1;
}
return(result);
}
