示例#1
0
        public IEnumerable <OrderClass> ViewOrders()
        {
            var result = new List <OrderClass>();

            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = connection.CreateCommand();
                command.CommandText = "select OrderID, CustomerID,EmployeeID,OrderDate,ShippedDate,Freight from Northwind.Orders";
                command.CommandType = CommandType.Text;;
                connection.Open();

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var Order = new OrderClass();
                        Order.OrderID = reader.GetInt32(0);
                        if (!reader.IsDBNull(1))
                        {
                            Order.CustomerID = reader.GetString(1);
                        }
                        if (!reader.IsDBNull(2))
                        {
                            Order.EmployeeID = reader.GetInt32(2);
                        }
                        if (!reader.IsDBNull(3))
                        {
                            Order.OrderDate = reader.GetDateTime(3);
                        }
                        if (reader.IsDBNull(3))
                        {
                            Order.OrderStatus = OrderClass.Status.New;
                        }
                        else if (reader.IsDBNull(4))
                        {
                            Order.OrderStatus = OrderClass.Status.InWork;
                        }
                        else
                        {
                            Order.OrderStatus = OrderClass.Status.Compl;
                        }
                        if (!reader.IsDBNull(5))
                        {
                            Order.Freight = (int)reader.GetSqlMoney(5);
                        }
                        result.Add(Order);
                    }
                }
            }
            return(result);
        }
示例#2
0
文件: DAL.cs 项目: Laetar/Epam
        public IEnumerable<OrderClass> ViewOrders()
        {
            var result = new List<OrderClass>();

            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = connection.CreateCommand();
                command.CommandText = "select OrderID, CustomerID,EmployeeID,OrderDate,ShippedDate,Freight from Northwind.Orders";
                command.CommandType = CommandType.Text; ;
                connection.Open();

                using (var reader = command.ExecuteReader())
                {

                    while (reader.Read())
                    {
                        var Order = new OrderClass();
                        Order.OrderID = reader.GetInt32(0);
                        if (!reader.IsDBNull(1)) Order.CustomerID = reader.GetString(1);
                        if (!reader.IsDBNull(2)) Order.EmployeeID = reader.GetInt32(2);
                        if (!reader.IsDBNull(3)) Order.OrderDate = reader.GetDateTime(3);
                        if (reader.IsDBNull(3))
                        {
                            Order.OrderStatus = OrderClass.Status.New;
                        }
                        else if (reader.IsDBNull(4))
                        {
                            Order.OrderStatus = OrderClass.Status.InWork;
                        }
                        else
                        {
                            Order.OrderStatus = OrderClass.Status.Compl;
                        }
                        if (!reader.IsDBNull(5)) Order.Freight = (int)reader.GetSqlMoney(5);
                        result.Add(Order);
                    }
                }
            }
            return result;
        }
示例#3
0
文件: DAL.cs 项目: Laetar/Epam
        public OrderClass GetOrdersById(int id)
        {
            OrderClass Order = new OrderClass();

            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand(
                    "select Northwind.Orders.OrderID, Northwind.Orders.CustomerID,Northwind.Orders.EmployeeID,OrderDate," +
                     "ShippedDate,ProductName,Northwind.[Order Details].UnitPrice,Quantity,Discount," +
                     "RequiredDate, ShipVia, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry from Northwind.Orders " +
                     "LEFT JOIN Northwind.[Order Details] ON Northwind.Orders.OrderID = Northwind.[Order Details].OrderID " +
                     "LEFT JOIN Northwind.Products ON Northwind.[Order Details].ProductID = Northwind.Products.ProductID", connection);
                connection.Open();

                using (var reader = command.ExecuteReader())
                {

                    while (reader.Read())
                    {
                        if (reader.GetInt32(0) == id)
                        {
                            if (!reader.IsDBNull(0)) Order.OrderID = reader.GetInt32(0);
                            if (!reader.IsDBNull(1))  Order.CustomerID = reader.GetString(1);
                            if (!reader.IsDBNull(2))  Order.EmployeeID = reader.GetInt32(2);
                            if (!reader.IsDBNull(3))  Order.OrderDate = reader.GetDateTime(3);
                            if (reader.IsDBNull(3))
                            {
                                Order.OrderStatus = OrderClass.Status.New;
                            }
                            else if (reader.IsDBNull(4))
                            {
                                Order.OrderDate = reader.GetDateTime(3);
                                Order.OrderStatus = OrderClass.Status.InWork;
                            }
                            else
                            {
                                Order.ShippedDate = reader.GetDateTime(4);
                                Order.OrderStatus = OrderClass.Status.Compl;
                            }
                            if (!reader.IsDBNull(5)) Order.ProductsName.Add(reader.GetString(5));
                            if (!reader.IsDBNull(6)&&(!reader.IsDBNull(7))&&(!reader.IsDBNull(8)))
                                Order.Price = (double)reader.GetSqlMoney(6).ToSqlInt32() * reader.GetInt16(7) -
                                (double)reader.GetSqlMoney(6).ToSqlInt32() * reader.GetInt16(7) * (double)reader.GetSqlSingle(8).ToSqlDouble();
                            if (!reader.IsDBNull(9)) Order.RequiredDate = reader.GetDateTime(9);
                            if (!reader.IsDBNull(10)) Order.ShipVia = reader.GetInt32(10);
                            if (!reader.IsDBNull(11)) Order.ShipName = reader.GetString(11);
                            if (!reader.IsDBNull(12)) Order.ShipAddress = reader.GetString(12);
                            if (!reader.IsDBNull(13)) Order.ShipCity = reader.GetString(13);
                            if (!reader.IsDBNull(14)) Order.ShipRegion = reader.GetString(14);
                            if (!reader.IsDBNull(15)) Order.ShipPostalCode = reader.GetString(15);
                            if (!reader.IsDBNull(16)) Order.ShipCountry = reader.GetString(16);
                        }
                    }
                }
            }
            return Order;
        }
示例#4
0
        public OrderClass GetOrdersById(int id)
        {
            OrderClass Order = new OrderClass();

            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand(
                    "select Northwind.Orders.OrderID, Northwind.Orders.CustomerID,Northwind.Orders.EmployeeID,OrderDate," +
                    "ShippedDate,ProductName,Northwind.[Order Details].UnitPrice,Quantity,Discount," +
                    "RequiredDate, ShipVia, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry from Northwind.Orders " +
                    "LEFT JOIN Northwind.[Order Details] ON Northwind.Orders.OrderID = Northwind.[Order Details].OrderID " +
                    "LEFT JOIN Northwind.Products ON Northwind.[Order Details].ProductID = Northwind.Products.ProductID", connection);
                connection.Open();

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (reader.GetInt32(0) == id)
                        {
                            if (!reader.IsDBNull(0))
                            {
                                Order.OrderID = reader.GetInt32(0);
                            }
                            if (!reader.IsDBNull(1))
                            {
                                Order.CustomerID = reader.GetString(1);
                            }
                            if (!reader.IsDBNull(2))
                            {
                                Order.EmployeeID = reader.GetInt32(2);
                            }
                            if (!reader.IsDBNull(3))
                            {
                                Order.OrderDate = reader.GetDateTime(3);
                            }
                            if (reader.IsDBNull(3))
                            {
                                Order.OrderStatus = OrderClass.Status.New;
                            }
                            else if (reader.IsDBNull(4))
                            {
                                Order.OrderDate   = reader.GetDateTime(3);
                                Order.OrderStatus = OrderClass.Status.InWork;
                            }
                            else
                            {
                                Order.ShippedDate = reader.GetDateTime(4);
                                Order.OrderStatus = OrderClass.Status.Compl;
                            }
                            if (!reader.IsDBNull(5))
                            {
                                Order.ProductsName.Add(reader.GetString(5));
                            }
                            if (!reader.IsDBNull(6) && (!reader.IsDBNull(7)) && (!reader.IsDBNull(8)))
                            {
                                Order.Price = (double)reader.GetSqlMoney(6).ToSqlInt32() * reader.GetInt16(7) -
                                              (double)reader.GetSqlMoney(6).ToSqlInt32() * reader.GetInt16(7) * (double)reader.GetSqlSingle(8).ToSqlDouble();
                            }
                            if (!reader.IsDBNull(9))
                            {
                                Order.RequiredDate = reader.GetDateTime(9);
                            }
                            if (!reader.IsDBNull(10))
                            {
                                Order.ShipVia = reader.GetInt32(10);
                            }
                            if (!reader.IsDBNull(11))
                            {
                                Order.ShipName = reader.GetString(11);
                            }
                            if (!reader.IsDBNull(12))
                            {
                                Order.ShipAddress = reader.GetString(12);
                            }
                            if (!reader.IsDBNull(13))
                            {
                                Order.ShipCity = reader.GetString(13);
                            }
                            if (!reader.IsDBNull(14))
                            {
                                Order.ShipRegion = reader.GetString(14);
                            }
                            if (!reader.IsDBNull(15))
                            {
                                Order.ShipPostalCode = reader.GetString(15);
                            }
                            if (!reader.IsDBNull(16))
                            {
                                Order.ShipCountry = reader.GetString(16);
                            }
                        }
                    }
                }
            }
            return(Order);
        }