コード例 #1
0
        public static List <OrderId> GetOrderIds()
        {
            // empty list to store the retrieved values from database
            List <OrderId> ids = new List <OrderId>();
            OrderId        id;

            // create connection
            using (SqlConnection connection = NorthwindCon.GetConnection())
            {
                // select query
                string query = "SELECT orderid FROM orders";

                using (SqlCommand cmd = new SqlCommand(query, connection))
                {
                    // run the command and process results
                    connection.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            // process next record from data reader
                            id         = new OrderId();
                            id.orderId = (int)reader["orderid"];
                            ids.Add(id);
                        }
                    } // closes reader and recycles object
                }     // cmd object recycled
            }         // connection object recycled
            return(ids);
        }
コード例 #2
0
        public static Orders GetOrders(int OrderId)
        {
            Orders order = null;

            using (SqlConnection connection = NorthwindCon.GetConnection())
            {
                // query with parameter selection to extract one order data
                string query = "SELECT OrderID, CustomerID, OrderDate, RequiredDate, Shippeddate " +
                               "FROM Orders " +
                               "WHERE OrderID = @OrderID";
                using (SqlCommand cmd = new SqlCommand(query, connection))
                {
                    cmd.Parameters.AddWithValue("@OrderID", OrderId);
                    connection.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read()) // reading one row from the database based on given order id
                        {
                            order              = new Orders();
                            order.OrderId      = (int)reader["OrderID"];
                            order.CustomerID   = reader["CustomerID"].ToString();
                            order.OrderDate    = (reader["orderdate"] == DBNull.Value) ? null : (DateTime?)reader["orderdate"];
                            order.RequiredDate = (reader["RequiredDate"] == DBNull.Value) ? null : (DateTime?)reader["RequiredDate"];
                            order.ShippedDate  = (reader["Shippeddate"] == DBNull.Value)? null : (DateTime?)reader["Shippeddate"];
                        }
                    }
                }
            }
            return(order);
        }
コード例 #3
0
        public static List <OrderDetails> GetOrderDetails(int OrderId)
        {
            List <OrderDetails> datadetail = new List <OrderDetails>(); // list to store orders table data

            using (SqlConnection connection = NorthwindCon.GetConnection())
            {
                // query to extract columns from orders table
                string query1 = "SELECT OrderID, ProductID, UnitPrice, Quantity, Discount " +
                                "FROM [Order Details] " +
                                "WHERE OrderID = @OrderID";
                using (SqlCommand cmd1 = new SqlCommand(query1, connection))
                {
                    cmd1.Parameters.AddWithValue("@OrderID", OrderId);
                    connection.Open();
                    using (SqlDataReader reader = cmd1.ExecuteReader())
                    {
                        while (reader.Read()) // while loop to read all the rows associated with one order id
                        {
                            OrderDetails orderdata = new OrderDetails();

                            orderdata.OrderID   = Convert.ToInt32(reader["OrderID"]);
                            orderdata.ProductID = Convert.ToInt32(reader["ProductID"]);
                            orderdata.UnitPrice = Convert.ToDouble(reader["UnitPrice"]);
                            orderdata.Quantity  = Convert.ToInt16(reader["Quantity"]);
                            orderdata.Discount  = Convert.ToDouble(reader["Discount"]);
                            datadetail.Add(orderdata);
                        }
                    }
                }
            }
            return(datadetail); // returning the list with all the data
        }
コード例 #4
0
 // Update method using same name but different signatures to accept null values
 public static void UpdateShippingDate(int id)
 {
     using (SqlConnection connection = NorthwindCon.GetConnection())
     {
         string updatedate = "update orders set shippeddate = @newShippedDate Where OrderID = @OrderID"; // to identify record
         using (SqlCommand cmd = new SqlCommand(updatedate, connection))
         {
             cmd.Parameters.AddWithValue("@newShippedDate", DBNull.Value);
             cmd.Parameters.AddWithValue("@OrderID", id);
             connection.Open();
             cmd.ExecuteNonQuery();
         }
     }
 }