C# Service.OrmliteConnection类代码示例

        public ActionResult AddUserToGroup(int id, string data)
        {
            IDbConnection db = new OrmliteConnection().openConn();
            try
            {
                // Delete All User in Role
                db.Delete<Auth_UserInRole>(p => p.RoleID == id);

                // Add User Role
                if (!string.IsNullOrEmpty(data))
                {
                    string[] arr = data.Split(',');
                    foreach (string item in arr)
                    {
                        var detail = new Auth_UserInRole();
                        detail.UserID = item;
                        detail.RoleID = id;
                        detail.RowCreatedAt = DateTime.Now;
                        detail.RowCreatedBy = currentUser.UserID;
                        db.Insert<Auth_UserInRole>(detail);
                    }
                }
                return Json(new { success = true });
            }
            catch (Exception e) { return Json(new { success = false, message = e.Message }); }
            finally { db.Close(); }
        }

        public ActionResult CancelSalesOrder(string data)
        {
            var dbConn = new OrmliteConnection().openConn();
            if (userAsset.ContainsKey("Insert") && userAsset["Insert"])
            {
                try
                {
                    string[] separators = { "@@" };
                    var listdata = data.Split(separators, StringSplitOptions.RemoveEmptyEntries);
                    foreach (var item in listdata)
                    {
                        if (dbConn.Select<SOHeader>("Select * from SOHeader where Status <> N'Mới' AND SONumber = '"+item+"'").Count() > 0)
                        {
                            return Json(new { success = false, message = "Chỉ hủy được các mẫu tin có trạng thái Mới" });
                        }

                        dbConn.Update<SOHeader>(set: "Status = N'Hủy'", where: "SONumber = '" + item + "'");
                    }
                }
                catch (Exception e)
                {
                    return Json(new { success = false, message = e.Message });
                }
                return Json(new{success = true});
            }
            else{
                return Json(new { success = false, message = "Bạn không có quyền hủy." });
            }
        }

        public ActionResult Create([DataSourceRequest]DataSourceRequest request, [Bind(Prefix = "models")]IEnumerable<Models.Master_Calendar> lst)
        {
            IDbConnection dbConn = new OrmliteConnection().openConn();

            try
            {
                foreach (var item in lst)
                {
                    if (userAsset.ContainsKey("Update") && userAsset["Update"] && dbConn.GetByIdOrDefault<Master_Calendar>(item.Date) != null)
                    {
                        if (string.IsNullOrEmpty(item.Holiday))
                        {
                            item.Holiday = "";
                        }
                        item.RowUpdatedAt = DateTime.Now;
                        item.RowUpdatedBy = currentUser.UserID;
                        dbConn.Update<Master_Calendar>(item);
                    }
                    else
                        return Json(new { success = false, message = "You don't have permission" });
                }
                return Json(new { success = true });
            }
            catch (Exception ex)
            {
                log.Error("AdminMasterHoliday - Create - " + ex.Message);
                return Json(new { success = false, message = ex.Message });
            }
            finally
            {
                dbConn.Close();
            }
        }

        public ActionResult AddUserToGroup(string id, string data)
        {
            IDbConnection db = new OrmliteConnection().openConn();
            try
            {
                // Delete All Group of User
                if (string.IsNullOrEmpty(id))
                {
                    return Json(new { success = false, message  = "Chọn người dùng trước khi thêm nhóm."});
                }
                db.DeleteById<Auth_UserInRole>(id);

                // Add User Group
                if (!string.IsNullOrEmpty(data))
                {
                    string[] arr = data.Split(',');
                    foreach (string item in arr)
                    {
                        var detail = new Auth_UserInRole();
                        detail.UserID = id;
                        detail.RoleID = int.Parse(item);
                        detail.RowCreatedAt = DateTime.Now;
                        detail.RowCreatedBy = currentUser.UserID;
                        db.Insert<Auth_UserInRole>(detail);
                    }
                }
                return Json(new { success = true });
            }
            catch (Exception e) { return Json(new { success = false, message = e.Message }); }
            finally { db.Close(); }
        }

 public ActionResult AddTranporterToContract(string id, string data)
 {
     IDbConnection db = new OrmliteConnection().openConn();
     try
     {
         db.Delete<DC_LG_Contract_Transporter>(p => p.ContractID == id);
         if (!string.IsNullOrEmpty(data))
         {
             string[] arr = data.Split(',');
             foreach (string item in arr)
             {
                 var detail = new DC_LG_Contract_Transporter();
                 detail.ContractID = id;
                 detail.TransporterID = int.Parse(item);
                 detail.Note = "";
                 detail.UpdatedAt = DateTime.Now;
                 detail.CreatedAt = DateTime.Now;
                 detail.CreatedBy = currentUser.UserID;
                 detail.UpdatedBy = currentUser.UserID;
                 db.Insert<DC_LG_Contract_Transporter>(detail);
             }
         }
         return Json(new { success = true });
     }
     catch (Exception e) { return Json(new { success = false, message = e.Message }); }
     finally { db.Close(); }
 }

 public List<CustomerHirerachy> GetAllCustomerHirerachy()
 {
     IDbConnection db = new OrmliteConnection().openConn();
     var lst = db.Select<CustomerHirerachy>().Where(p => p.Status == true).ToList();
     db.Close();
     return lst;
 }

        public ActionResult Create(Auth_User item)
        {
            IDbConnection db = new OrmliteConnection().openConn();
            try
            {
                if (!string.IsNullOrEmpty(item.UserID) &&
                    !string.IsNullOrEmpty(item.DisplayName) &&
                    !string.IsNullOrEmpty(item.FullName))
                {
                    var isExist = db.GetByIdOrDefault<Auth_User>(item.UserID);
                    item.Phone = !string.IsNullOrEmpty(item.Phone) ? item.Phone : "";
                    item.Email = !string.IsNullOrEmpty(item.Email) ? item.Email : "";
                    item.Note = !string.IsNullOrEmpty(item.Note) ? item.Note : "";
                    if (userAsset.ContainsKey("Insert") && userAsset["Insert"] && item.RowCreatedAt == null && item.RowCreatedBy == null)
                    {
                        if(isExist != null)
                            return Json(new { success = false, message = "Người dùng đã tồn tại." });
                        item.Password = SqlHelper.GetMd5Hash("123456");
                        item.RowCreatedAt = DateTime.Now;
                        item.RowCreatedBy = currentUser.UserID;
                        db.Insert<Auth_User>(item);
                        return Json(new { success = true, UserID = item.UserID, RowCreatedAt = item.RowCreatedAt, RowCreatedBy = item.RowCreatedBy });
                    }
                    else if (userAsset.ContainsKey("Update") && userAsset["Update"] && isExist != null)
                    {
                        item.Password = isExist.Password;
                        item.RowUpdatedAt = DateTime.Now;
                        item.RowUpdatedBy = currentUser.UserID;

                        if (isExist.RowCreatedBy != "system")
                        {
                            db.Update<Auth_User>(item);
                        }
                        else
                        {
                            return Json(new { success = false, message = "Dữ liệu này không cho chỉnh sửa liên hệ admin để biết thêm chi tiết" });
                        }
                        return Json(new { success = true });
                    }
                    else
                        return Json(new { success = false, message = "Bạn không có quyền" });
                }
                else
                {
                    return Json(new { success = false, message = "Chưa nhập giá trị" });
                }
            }
            catch (Exception e)
            {
                log.Error("AD_User - Create - " + e.Message);
                return Json(new { success = false, message = e.Message });
            }
            finally { db.Close(); }
        }

 public ActionResult Create(DC_LG_Discountion item)
 {
     IDbConnection db = new OrmliteConnection().openConn();
     try
     {
         if (!string.IsNullOrEmpty(item.DiscountionID) &&
             !string.IsNullOrEmpty(item.DiscountionName)
             )
         {
             var isExist = db.SingleOrDefault<DC_LG_Discountion>("DiscountionID={0}", item.DiscountionID);
             item.FromDate = item.FromDate != null ? item.FromDate : DateTime.Now;
             item.EndDate = item.EndDate != null ? item.EndDate : DateTime.Now;
             item.EndDate = item.EndDate != null ? item.EndDate : DateTime.Now;
             if (item.FromDate > item.EndDate)
             {
                 return Json(new { success = false, message = "Ngày kết thúc không thể lớn hơn " + item.FromDate });
             }
             item.Note = !string.IsNullOrEmpty(item.Note) ? item.Note : "";
             item.DiscountionType = !string.IsNullOrEmpty(item.DiscountionType) ? item.DiscountionType : "";
             if (userAsset.ContainsKey("Insert") && userAsset["Insert"] && item.CreatedAt == null && item.CreatedBy == null)
             {
                 if (isExist != null)
                     return Json(new { success = false, message = "Mã chương trình chiết khấu đã tồn tại" });
                 item.DiscountionName = !string.IsNullOrEmpty(item.DiscountionName) ? item.DiscountionName : "";
                 item.CreatedAt = DateTime.Now;
                 item.UpdatedAt = DateTime.Now;
                 item.CreatedBy = currentUser.UserID;
                 db.Insert(item);
                 return Json(new { success = true, DiscountionID = item.DiscountionID, CreatedBy = item.CreatedBy, CreatedAt = item.CreatedAt });
             }
             else if (userAsset.ContainsKey("Update") && userAsset["Update"] && isExist != null)
             {
                 item.DiscountionName = !string.IsNullOrEmpty(item.DiscountionName) ? item.DiscountionName : "";
                 item.CreatedAt = item.CreatedAt;
                 item.UpdatedAt = DateTime.Now;
                 item.CreatedBy = currentUser.UserID;
                 db.Update(item);
                 return Json(new { success = true });
             }
             else
                 return Json(new { success = false, message = "Bạn không có quyền" });
         }
         else
         {
             return Json(new { success = false, message = "Chưa nhập đủ giá trị" });
         }
     }
     catch (Exception e)
     {
         log.Error("DeliveryDiscountion - Create - " + e.Message);
         return Json(new { success = false, message = e.Message });
     }
     finally { db.Close(); }
 }

 //
 // GET: /DeliveryManage/Create
 public ActionResult Create(DC_LG_Transporter item)
 {
     IDbConnection db = new OrmliteConnection().openConn();
     try
     {
         if (//!string.IsNullOrEmpty(item.TransporterID) &&
             !string.IsNullOrEmpty(item.TransporterName)
             )
         {
             int n;
             var isExist = db.SingleOrDefault<DC_LG_Transporter>("TransporterID={0}",item.TransporterID);
             item.Weight = int.TryParse(item.Weight.ToString(),out n) ? item.Weight : 0;
             item.Note = !string.IsNullOrEmpty(item.Note) ? item.Note : "";
             if (userAsset.ContainsKey("Insert") && userAsset["Insert"] && item.CreatedAt == null && item.CreatedBy == null)
             {
                 if(isExist != null)
                     return Json(new { success = false, message = "Mã đơn vị vận chuyển đã tồn tại!" });
                 item.TransporterName = !string.IsNullOrEmpty(item.TransporterName) ? item.TransporterName : "";
                 item.CreatedAt = DateTime.Now;
                 item.UpdatedAt = DateTime.Now;
                 item.CreatedBy = currentUser.UserID;
                 item.UpdatedBy = currentUser.UserID;
                 db.Insert(item);
                 return Json(new { success = true, TransporterID = item.TransporterID, CreatedBy = item.CreatedBy, CreatedAt = item.CreatedAt });
             }
             else if (userAsset.ContainsKey("Update") && userAsset["Update"] && isExist != null)
             {
                 item.TransporterName = !string.IsNullOrEmpty(item.TransporterName) ? item.TransporterName : "";
                 item.CreatedAt = item.CreatedAt;
                 item.UpdatedAt = DateTime.Now;
                 item.CreatedBy = currentUser.UserID;
                 item.UpdatedBy = currentUser.UserID;
                 db.Update(item);
                 return Json(new { success = true });
             }
             else
                 return Json(new { success = false, message = "Bạn không có quyền" });
         }
         else
         {
             return Json(new { success = false, message = "Chưa nhập giá trị" });
         }
     }
     catch (Exception e)
     {
         log.Error("Transporter - Create - " + e.Message);
         return Json(new { success = false, message = e.Message });
     }
     finally { db.Close(); }
 }

 public ActionResult Partial()
 {
     if (userAsset.ContainsKey("View") && userAsset["View"])
     {
         IDbConnection dbConn = new OrmliteConnection().openConn();
         var dict = new Dictionary<string, object>();
         dict["asset"] = userAsset;
         dict["activestatus"] = new CommonLib().GetActiveStatus();
         ViewData["listConfig_Announcement"] = new Master_Announcement().GetAllSort("", "CreatedAt", "DESC");
         dbConn.Close();
         return PartialView("_Home", dict);
     }
     else
         return RedirectToAction("LogOn", "Account");
 }

 public List<ActiveStatus> GetActiveStatus()
 {
     IDbConnection dbConn = new OrmliteConnection().openConn();
     var list = new List<ActiveStatus>();
     try
     {
         list = dbConn.Select<ActiveStatus>("SELECT StatusValue, StatusName,IsAllMerchant,IsAllDistric FROM vw_List_Active_Status");
     }
     catch (Exception)
     {
         throw;
     }
     finally { dbConn.Close(); }
     return list;
 }

        public ActionResult GetSystemDate()
        {
            //var data = DateTime.Now;
            //return Json(new { success = true, data = data });

            IDbConnection db = new OrmliteConnection().openConn();
            try
            {
                var data = new CustomModel().GetSystemDate();
                return Json(new { success = true, data = data });
            }
            catch (Exception e)
            {
                return Json(new { success = false, message = e.Message });
            }
            finally { db.Close(); }
        }

 public FileResult Export_DeliveryPackage([DataSourceRequest]DataSourceRequest request)
 {
     ExcelPackage pck = new ExcelPackage(new FileInfo(Server.MapPath("~/ExportTemplate/GoiCuocVanChuyen.xlsx")));
     ExcelWorksheet ws = pck.Workbook.Worksheets["Data"];
     if (userAsset["Export"])
     {
         string whereCondition = "";
         if (request.Filters.Count > 0)
         {
             whereCondition = " AND " +new KendoApplyFilter().ApplyFilter(request.Filters[0]);
         }
         IDbConnection db = new OrmliteConnection().openConn();
         var lstResult = new DC_LG_DeliveryFee().GetListDeliveryFee(request, whereCondition);
         int rowNum = 2;
         foreach (var item in lstResult)
         {
             ws.Cells["A" + rowNum].Value = item.DeliveryFeeID;
             ws.Cells["B" + rowNum].Value = item.Name;
             ws.Cells["C" + rowNum].Value = item.TransporterID;
             ws.Cells["D" + rowNum].Value = item.DeliveryName;
             ws.Cells["E" + rowNum].Value = item.Descr;
             ws.Cells["F" + rowNum].Value = item.MinDay;
             ws.Cells["G" + rowNum].Value = item.MaxDay;
             ws.Cells["H" + rowNum].Value = item.MinTime;
             ws.Cells["I" + rowNum].Value = item.MaxTime;
             ws.Cells["J" + rowNum].Value = item.MinWeight    ;
             ws.Cells["K" + rowNum].Value = item.MaxWeight;
             ws.Cells["L" + rowNum].Value = item.Price;
             ws.Cells["M" + rowNum].Value = item.Note;
             ws.Cells["N" + rowNum].Value = item.Status ? "Đang hoạt động" : "Ngưng hoạt động";
             rowNum++;
         }
         db.Close();
     }
     else
     {
         ws.Cells["A2:E2"].Merge = true;
         ws.Cells["A2"].Value = "You don't have permission to export data.";
     }
     MemoryStream output = new MemoryStream();
     pck.SaveAs(output);
     return File(output.ToArray(), //The binary data of the XLS file
                 "application/vnd.ms-excel", //MIME type of Excel files
                 "GoiCuocVanChuyen" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx");     //Suggested file name in the "Save as" dialog which will be displayed to the end user
 }

 //
 // GET: /DeliveryManage/Create
 public ActionResult Create(DC_Reason item)
 {
     IDbConnection db = new OrmliteConnection().openConn();
     try
     {
         if (!string.IsNullOrEmpty(item.ReasonID) && item.ReasonType!="None")
         {
             var isExist = db.GetByIdOrDefault<DC_Reason>(item.ReasonID);
             item.Description = !string.IsNullOrEmpty(item.Description) ? item.Description : "";
             if (userAsset.ContainsKey("Insert") && userAsset["Insert"] && item.RowCreatedAt == null && item.RowCreatedBy == null)
             {
                 if (isExist != null)
                     return Json(new { success = false, message = "Mã lý do đã tồn tại!" });
                 item.ReasonType = !string.IsNullOrEmpty(item.ReasonType) ? item.ReasonType : "";
                 item.RowCreatedAt = DateTime.Now;
                 item.RowUpdatedAt = DateTime.Now;
                 item.RowCreatedBy = currentUser.UserID;
                 db.Insert<DC_Reason>(item);
                 return Json(new { success = true, ReasonID = item.ReasonID, RowCreatedBy = item.RowCreatedBy, RowCreatedAt = item.RowCreatedAt });
             }
             else if (userAsset.ContainsKey("Update") && userAsset["Update"] && isExist != null)
             {
                 item.ReasonType = !string.IsNullOrEmpty(item.ReasonType) ? item.ReasonType : "";
                 item.RowCreatedAt = item.RowCreatedAt;
                 item.RowUpdatedAt = DateTime.Now;
                 item.RowCreatedBy = currentUser.UserID;
                 db.Update<DC_Reason>(item);
                 return Json(new { success = true });
             }
             else
                 return Json(new { success = false, message = "Bạn không có quyền" });
         }
         else
         {
             return Json(new { success = false, message = "Chưa nhập giá trị" });
         }
     }
     catch (Exception e)
     {
         log.Error("DeliveryUOMManage - Create - " + e.Message);
         return Json(new { success = false, message = e.Message });
     }
     finally { db.Close(); }
 }

        public ActionResult CompletePicking(string data)
        {
            var dbConn = new OrmliteConnection().openConn();
            if (userAsset.ContainsKey("Update") && userAsset["Update"])
            {
                try
                {
                    string[] separators = { "@@" };
                    var listdata = data.Split(separators, StringSplitOptions.RemoveEmptyEntries);
                    foreach (var item in listdata)
                    {
                        if (dbConn.Select<DC_AD_Picking_Header>(s => s.Status == "Hoàn thành" && s.PickingNumber == item).Count() > 0)
                        {
                            return Json(new { success = false, message = item + " đã được hoàn thành trước đó." });
                        }

                        if (dbConn.Select<DC_AD_Picking_Header>(s =>s.Status != "Đang giao hàng" && s.PickingNumber==item).Count() > 0)
                        {
                            return Json(new { success = false, message = item + " vui lòng nhập kho trước khi hoàn thành." });
                        }

                        dbConn.Update<DC_AD_Picking_Header>(set: "Status = N'Hoàn thành', UpdatedBy ='" + currentUser.UserID + "', UpdatedAt= '"+DateTime.Now+"'", where: "PickingNumber = '" + item + "'");
                        foreach (var so in dbConn.Select<DC_AD_Picking_Detail>(s => s.PickingNumber == item).ToList())
                        {
                            dbConn.Update<SOHeader>(set: "Status = N'Hoàn thành'", where: "SONumber = '" + so.SONumber + "'");
                        }
                        //dbConn.Update<DC_AD_SO_Header>(set: "Status = N'Hoàn thành'", where: "SONumber = '" + item + "'");
                    }
                }
                catch (Exception e)
                {
                    return Json(new { success = false, message = e.Message });
                }
                return Json(new { success = true });
            }
            else
            {
                return Json(new { success = false, message = "Bạn không có quyền hủy." });
            }
        }