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);
        }
Example #5
0
        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);
        }