C# DB.Execute方法代码示例

        public List<OnDemandVideo> GetOnDemandAudioVideos()
        {
            DB db = new DB();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetOnDemandVideos";

            List<OnDemandVideo> medias = new List<OnDemandVideo>();
            DataTable dt = db.Execute(cmd);

            foreach (DataRow dr in dt.Rows)
            {
                OnDemandVideo media = new OnDemandVideo
                {
                    ID = Int32.Parse(dr["ID"].ToString()),
                    Location = dr["Location"].ToString(),
                    DateCreated = Convert.ToDateTime(dr["DateCreated"].ToString()),
                    PreviewImageURL = dr["PreviewImageURL"].ToString(),
                    Sort = Int32.Parse(dr["Sort"].ToString()),
                    Active = Convert.ToBoolean(dr["Active"]),
                    Message = dr["Message"].ToString(),
                    Url = dr["Url"].ToString(),

                };

                medias.Add(media);

            }

            return medias;
        }

        public bool SetCMEDCustomerPassword(string email, string password)
        {
            DB db = new DB();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SetCMEDCustomerPassword";
            cmd.Parameters.AddWithValue("@Email", email);
            cmd.Parameters.AddWithValue("@password", password);

            DataTable dt = db.Execute(cmd);

            return true;
        }

        public void UpdateCMEDShopProduct(CMEDShopProduct product)
        {
            DB db = new DB();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "UpdateCMEDShopProduct";

            cmd.Parameters.AddWithValue("@product_id", product.product_id);
            cmd.Parameters.AddWithValue("@product_title", product.product_title);
            cmd.Parameters.AddWithValue("@category_id", product.category_id);
            cmd.Parameters.AddWithValue("@supplier_id", product.supplier_id);
            cmd.Parameters.AddWithValue("@product_price", product.product_price);
            cmd.Parameters.AddWithValue("@product_status", product.product_status);
            cmd.Parameters.AddWithValue("@short_description", product.short_description);
            cmd.Parameters.AddWithValue("@long_description", product.long_description);
            cmd.Parameters.AddWithValue("@thumb_pic_url", product.thumb_pic_url);
            cmd.Parameters.AddWithValue("@full_pic_url", product.full_pic_url);
            cmd.Parameters.AddWithValue("@year", product.year);
            cmd.Parameters.AddWithValue("@location", product.location);
            cmd.Parameters.AddWithValue("@topic", product.topic);
            cmd.Parameters.AddWithValue("@active", product.active);
            cmd.Parameters.AddWithValue("@updated_by", product.updated_by);

            db.Execute(cmd);

            return;
        }

        public long SaveLunar(CMEDLunar lunar)
        {
            DB db = new DB();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SaveLunar";

            cmd.Parameters.AddWithValue("@ordernum", lunar.order_id);
            cmd.Parameters.AddWithValue("@bmonth", lunar.bmonth);
            cmd.Parameters.AddWithValue("@bday", lunar.bday);
            cmd.Parameters.AddWithValue("@byear", lunar.byear);
            cmd.Parameters.AddWithValue("@bhour", lunar.bhour);
            cmd.Parameters.AddWithValue("@bmin", lunar.bmin);
            cmd.Parameters.AddWithValue("@ampm", lunar.ampm);
            cmd.Parameters.AddWithValue("@bcity", lunar.bcity);
            cmd.Parameters.AddWithValue("@bstate", lunar.bstate);
            cmd.Parameters.AddWithValue("@bcountry", lunar.bcountry);
            cmd.Parameters.AddWithValue("@fname", lunar.fname);
            cmd.Parameters.AddWithValue("@lname", lunar.lname);
            cmd.Parameters.AddWithValue("@gender", lunar.gender);

            DataTable dt = db.Execute(cmd);

            DataRow row = dt.Rows[0];

            return long.Parse(row["order_id"].ToString());
        }

        public void SaveUserTransaction(UserTransaction transaction)
        {
            DB db = new DB();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "AddUserTransaction";

            cmd.Parameters.AddWithValue("@ProductID", transaction.ProductID);
            cmd.Parameters.AddWithValue("@FirstName", transaction.FirstName);
            cmd.Parameters.AddWithValue("@LastName", transaction.LastName);
            cmd.Parameters.AddWithValue("@Email", transaction.Email);
            cmd.Parameters.AddWithValue("@Password", transaction.Password);
            cmd.Parameters.AddWithValue("@Address", transaction.Address);
            cmd.Parameters.AddWithValue("@City", transaction.City);
            cmd.Parameters.AddWithValue("@Zip", transaction.Zip);
            cmd.Parameters.AddWithValue("@State", transaction.State);
            cmd.Parameters.AddWithValue("@Phone", transaction.Phone);
            cmd.Parameters.AddWithValue("@Amount", transaction.Amount);

            db.Execute(cmd);

            return;
        }

        public bool SaveCMEDRegisterCustomer(CMEDCustomer customer)
        {
            DB db = new DB();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SaveCMEDRegisterCustomer";

            cmd.Parameters.AddWithValue("@first_name", customer.first_name);
            cmd.Parameters.AddWithValue("@last_name", customer.last_name);
            cmd.Parameters.AddWithValue("@phone_number", customer.phone_number);
            cmd.Parameters.AddWithValue("@Email", customer.email);
            cmd.Parameters.AddWithValue("@Password", customer.password);
            cmd.Parameters.AddWithValue("@opt_in", customer.opt_in);

            db.Execute(cmd);

            return true;
        }

        public void SaveCMEDWorkshop(CMEDWorkshop worshop)
        {
            DB db = new DB();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SaveCMEDWorkshop";

            cmd.Parameters.AddWithValue("@ID", worshop.ID);
            cmd.Parameters.AddWithValue("@ProductID", worshop.ProductID);
            cmd.Parameters.AddWithValue("@Title", worshop.Title);
            cmd.Parameters.AddWithValue("@SubTitle", worshop.SubTitle);
            cmd.Parameters.AddWithValue("@Location", worshop.Location);
            cmd.Parameters.AddWithValue("@DateLine", worshop.DateLine);
            cmd.Parameters.AddWithValue("@Prerequisite", worshop.Prerequisite);
            cmd.Parameters.AddWithValue("@ShortDescription", worshop.ShortDescription);
            cmd.Parameters.AddWithValue("@LongDescription", worshop.LongDescription);
            cmd.Parameters.AddWithValue("@PurchaseDescription", worshop.PurchaseDescription);
            cmd.Parameters.AddWithValue("@RegisterURL", worshop.RegisterURL);
            cmd.Parameters.AddWithValue("@ThumbNailImageUrl", worshop.ThumbNailImageUrl);
            cmd.Parameters.AddWithValue("@FullImageUrl", worshop.FullImageUrl);
            cmd.Parameters.AddWithValue("@Cost", worshop.Cost);
            cmd.Parameters.AddWithValue("@MealsCost", worshop.MealsCost);
            cmd.Parameters.AddWithValue("@MealsCount", worshop.MealsCount);
            cmd.Parameters.AddWithValue("@Deposit", worshop.Deposit);
            cmd.Parameters.AddWithValue("@Active", worshop.Active);
            cmd.Parameters.AddWithValue("@Archive", worshop.Archive);
            cmd.Parameters.AddWithValue("@Sort", worshop.Sort);
            cmd.Parameters.AddWithValue("@DepositOn", worshop.DepositOn);
            cmd.Parameters.AddWithValue("@MealCostOn", worshop.MealCostOn);
            cmd.Parameters.AddWithValue("@CanPurchase", worshop.CanPurchase);

            db.Execute(cmd);

            return;
        }

        public List<WorkShop> GetWorkShops()
        {
            DB db = new DB();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetWorkShops";

            List<WorkShop> Workshops = new List<WorkShop>();
            DataTable dt = db.Execute(cmd);

            foreach (DataRow dr in dt.Rows)
            {
                WorkShop workshop = new WorkShop
                {
                    ID = Int32.Parse(dr["ID"].ToString()),
                    Location = dr["Location"].ToString(),
                    Title = dr["Title"].ToString(),
                    Sort = Int32.Parse(dr["Sort"].ToString()),
                    Active = Convert.ToBoolean(dr["Active"]),
                    ShortDescription = dr["ShortDescription"].ToString(),
                    Dates = dr["Dates"].ToString(),
                    DetailsURL = dr["DetailsURL"].ToString(),
                    ResortDescription = dr["ResortDescription"].ToString(),
                    ResortImageURL = dr["ResortImageURL"].ToString(),
                    CMyssImageURL = dr["CMyssImageURL"].ToString(),

                };

                Workshops.Add(workshop);

            }

            return Workshops;
        }

        public List<UserTransactionReport> GetWorkshopsPurchased(int ProductID)
        {
            DB db = new DB();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetWorkshopsPurchasedByProduct";
            cmd.Parameters.AddWithValue("@ProductID", ProductID);

            List<UserTransactionReport> Students = new List<UserTransactionReport>();
            DataTable dt = db.Execute(cmd);

            foreach (DataRow dr in dt.Rows)
            {
                UserTransactionReport Student = new UserTransactionReport
                {

                    Email = dr["Email"].ToString(),
                    ProductID = Int32.Parse(dr["ProductID"].ToString()),
                    ProductName = dr["ProductName"].ToString(),
                    OrderDate = DateTime.Parse(dr["OrderDate"].ToString()),
                    Status = dr["Status"].ToString(),
                    FullName = dr["FullName"].ToString(),
                    Amount = Decimal.Parse(dr["Amount"].ToString()),
                    Phone = dr["Phone"].ToString(),
                    Location = dr["Location"].ToString(),
                };

                Students.Add(Student);

            }

            return Students;
        }

        public MyssProduct getProduct(int ProductID)
        {
            DB db = new DB();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetProduct";

            cmd.Parameters.AddWithValue("@ProductID", ProductID);

            DataTable dt = db.Execute(cmd);

            DataRow dr = dt.Rows[0];
            MyssProduct product = new MyssProduct
            {
                LongDescription = dr["LongDescription"].ToString(),
                ShortDescrption = dr["ShortDescrption"].ToString(),
                ProductName = dr["ProductName"].ToString(),
                ProductID = Int32.Parse(dr["ProductID"].ToString()),
                ProductPrice = Double.Parse(dr["ProductPrice"].ToString()),

            };

            return product;
        }

        public List<CMEDTimeZone> GetTimeZones()
        {
            DB db = new DB();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetTimeZomes";

            DataTable dt = db.Execute(cmd);

            List<CMEDTimeZone> timezones = new List<CMEDTimeZone>();

            foreach (DataRow dr in dt.Rows)
            {
                CMEDTimeZone timezone = new CMEDTimeZone
                {
                    timezone_code = dr["timezone_code"].ToString(),
                    timezone_title = dr["timezone_title"].ToString(),
                };

                timezones.Add(timezone);

            }
            return timezones;
        }

        public List<OrderItem> getOrdersByEmail(string email)
        {
            DB db = new DB();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "getOrdersByEmail";

            cmd.Parameters.AddWithValue("@email", email);

            DataTable dt = db.Execute(cmd);

            List<OrderItem> OrderItems = new List<OrderItem>();

            foreach (DataRow dr in dt.Rows)
            {
                OrderItem orderitem = new OrderItem
                {
                    LineItemID = Int32.Parse(dr["LineItemNo"].ToString()),
                    OrderID = Int32.Parse(dr["OrderNo"].ToString()),
                    OrderDate = DateTime.Parse(dr["OrderDate"].ToString()).Date,
                    Name = dr["ProductName"].ToString(),
                    ItemType = dr["ProductType"].ToString(),
                    Status = dr["LineStatus"].ToString(),
                    UnitPrice = double.Parse(dr["UnitPrice"].ToString()),
                    TotalPrice = double.Parse(dr["TotalPrice"].ToString()),
                    Quantity = Int32.Parse(dr["Quantity"].ToString()),
                    Url = dr["Url"].ToString(),
                    ViewOrder = dr["ViewOrder"].ToString(),
                };

                OrderItems.Add(orderitem);

            }

            return OrderItems;
        }

        public List<OrderSummary> getOrders(string email)
        {
            DB db = new DB();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetOrders";

            cmd.Parameters.AddWithValue("@email", email);

            DataTable dt = db.Execute(cmd);

            List<OrderSummary> orders = new List<OrderSummary>();

            foreach (DataRow dr in dt.Rows)
            {
                OrderSummary ordersummary = new OrderSummary
                {
                    OrderID = Int32.Parse(dr["OrderNo"].ToString()),
                    OrderDate = DateTime.Parse(dr["OrderDate"].ToString()),
                    ShipDate = DateTime.Parse(dr["ShipDate"].ToString()).Date,
                    OrderStatus = dr["OrderStatus"].ToString(),
                    BillingStatus = dr["BillingStatus"].ToString(),
                    OrderType = dr["OrderType"].ToString(),
                    OrderAmount = double.Parse(dr["OrderAmount"].ToString())
                };

                orders.Add(ordersummary);

            }
            return orders;
        }

        public OrderSummary getOrder(int OrderID)
        {
            DB db = new DB();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetOrder";

            cmd.Parameters.AddWithValue("@OrderNo", OrderID);

            DataTable dt = db.Execute(cmd);

            DataRow dr = dt.Rows[0];

            OrderSummary ordersummary = new OrderSummary
            {
                OrderID = Int32.Parse(dr["OrderNo"].ToString()),
                OrderDate = DateTime.Parse(dr["OrderDate"].ToString()),
                ShipDate = DateTime.Parse(dr["ShipDate"].ToString()).Date,
                OrderStatus = dr["OrderStatus"].ToString(),
                BillingStatus = dr["BillingStatus"].ToString(),
                OrderType = dr["OrderType"].ToString(),
                OrderAmount = double.Parse(dr["OrderAmount"].ToString())
            };

            return ordersummary;
        }

        public void SaveCartItem(long product_id, decimal price, long cart_id, string email, string ip_address)
        {
            DB db = new DB();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SaveCartItemWithPrice";

            cmd.Parameters.AddWithValue("@product_id", product_id);
            cmd.Parameters.AddWithValue("@price", price);
            cmd.Parameters.AddWithValue("@cart_id", cart_id);
            cmd.Parameters.AddWithValue("@email", email);
            cmd.Parameters.AddWithValue("@ip", ip_address);

            db.Execute(cmd);

            return;
        }