C# DatabaseHelper.ConnectDatabase方法代码示例

        // End GetAll
        /// <summary>
        /// Kiểm tra trùng lập trước khi ADD
        /// </summary>
        /// <returns></returns>
        public bool Checkduplicaterows()
        {
            DatabaseHelper help = new DatabaseHelper();
            help.ConnectDatabase();
            bool has = help.ent.DM_Vat_Tu.Any(cus => cus.Ma_vat_tu == Ma_vat_tu);
            return has;
            ////Mở
            //m_dbConnection.Open();
            //DataTable dt = new DataTable();

            ////Chuẩn bị
            //string sql = "";
            //sql += "SELECT * FROM DM_Vat_Tu ";
            //sql += "WHERE [email protected]_vat_tu";

            //SqlCommand command = new SqlCommand(sql, m_dbConnection);

            //command.Parameters.Add("@Ma_vat_tu", SqlDbType.Int).Value = Ma_vat_tu;

            //command.CommandType = CommandType.Text;

            ////Run
            //SqlDataAdapter da = new SqlDataAdapter(command);
            //da.Fill(dt);

            ////Đóng
            //m_dbConnection.Close();

            //if (dt.Rows.Count > 0)
            //{
            //    return true;
            //}
            //return false;
        }

 public bool CheckTonTaiSoDK()
 {
     DatabaseHelper help = new DatabaseHelper();
     help.ConnectDatabase();
     bool has = help.ent.Dm_Kho_Muon_Ngoai.Any(cus => cus.Ten_kho_muon == Ten_kho);
     return has;
 }

        public int Insert()
        {
            DatabaseHelper help = new DatabaseHelper();
            help.ConnectDatabase();
            // insert
            using (var dbcxtransaction = help.ent.Database.BeginTransaction())
            {
                try
                {
                    var t = new Vat_Tu_Goi_Dau_Ky //Make sure you have a table called test in DB
                    {
                        ID_VT_Goi_Dau = this.ID_VT_Goi_Dau,
                        Ma_vat_tu = this.Ma_vat_tu,                   // ID = Guid.NewGuid(),
                        So_Luong = this.So_Luong,
                        ID_ky = this.ID_ky,
                        ID_chat_luong = this.ID_chat_luong,
                        ID_kho = this.ID_kho,
                    };

                    help.ent.Vat_Tu_Goi_Dau_Ky.Add(t);
                    help.ent.SaveChanges();
                    dbcxtransaction.Commit();
                    return 1;
                }
                catch (Exception ex)
                {
                    dbcxtransaction.Rollback();
                    return 0;

                }
            }
        }

        public static DataTable GetAll(string name)
        {
            DatabaseHelper help = new DatabaseHelper();
              help.ConnectDatabase();
              using (var dbcxtransaction = help.ent.Database.BeginTransaction())
              {
              var dm = (from d in help.ent.Kho_muon_vat_tu
                        join k in help.ent.DM_Kho on d.ID_Kho equals k.ID_kho
                        join c in help.ent.Chat_luong on d.Id_chat_luong equals c.Id_chat_luong
                        join v in help.ent.DM_Vat_Tu on d.Ma_vat_tu equals v.Ma_vat_tu

                        where k.Ten_kho .Contains(name) &&d.Da_tra == false

                        select new {
                        ID_kho_muon_vat_tu = d.ID_kho_muon_vat_tu,
                        ID_kho = d.ID_Kho,
                        ID_Kho_Muon  = d.ID_Kho_muon,
                        Ma_vat_tu = d.Ma_vat_tu,
                        Ten_vat_tu = v.Ten_vat_tu,
                        So_luong = d.So_luong,
                        Ma_phieu_xuat_tam = d.Ma_phieu_xuat_tam,
                        ID_chat_luong = d.Id_chat_luong,
                        Ten_chat_luong = c.Loai_chat_luong,
                       // Ten_kho  = k.Ten_kho,

                        }
                        ).ToList();
              dbcxtransaction.Commit();
              DataTable ds = Utilities.clsThamSoUtilities.ToDataTable(dm);
              return ds;
              }
        }

        /// <summary>
        /// [Bug] Xóa Item có Liên Kết khóa ngoại với bảng Vật Tư
        /// </summary>
        /// <returns>bool</returns>
        public int Delete(DM_Don_vi_tinh dvt)
        {
            DatabaseHelper help = new DatabaseHelper();
               help.ConnectDatabase();
               help.ent.DM_Don_vi_tinh.Attach(dvt);
               help.ent.DM_Don_vi_tinh.Remove(dvt);
               return help.ent.SaveChanges();

               //Mở
               //m_dbConnection.Open();

               ////Chuẩn bị
               //string sql = "";
               //sql += "Delete from DM_Don_vi_tinh ";
               //sql += "WHERE [email protected]_Don_vi_tinh";

               //SqlCommand command = new SqlCommand(sql, m_dbConnection);

               //command.Parameters.Add("@ID_Don_vi_tinh", SqlDbType.Int).Value = ID_Don_vi_tinh;

               //command.CommandType = CommandType.Text;

               ////Run
               //int result = command.ExecuteNonQuery();

               ////Đóng
               //m_dbConnection.Close();

               //return result;
        }

 public bool CheckTonTaiSoDK()
 {
     DatabaseHelper help = new DatabaseHelper();
     help.ConnectDatabase();
     bool has = help.ent.Vat_Tu_Goi_Dau_Ky.Any(cus => cus.Ma_vat_tu == Ma_vat_tu && cus.ID_chat_luong ==ID_chat_luong && cus.ID_kho == ID_kho);
     return has;
 }

 public bool CheckTonTaiSoDK(string maphieu ,string mavattu, int cl)
 {
     DatabaseHelper help = new DatabaseHelper();
       help.ConnectDatabase();
       bool has = help.ent.No_vat_tu.Any(cus => cus.Ma_phieu_xuat_tam == maphieu &&cus.Ma_vat_tu == mavattu && cus.Id_chat_luong == cl);
       return has;
 }

        /// <summary>
        /// hàm tìm kiếm vật tư theo kho và chất lượng
        /// </summary>
        /// <param name="_ID_kho"></param>
        /// <returns></returns>
        public static DataTable getAll(string TenKho, string TenChatLuong,string tenvt, string mavt)
        {
            DatabaseHelper help = new DatabaseHelper();
            help.ConnectDatabase();
            using (var dbcxtransaction = help.ent.Database.BeginTransaction())
            {
                var dm = (from d in help.ent.Ton_kho
                          join e in help.ent.DM_Vat_Tu on d.Ma_vat_tu equals e.Ma_vat_tu
                          join f in help.ent.DM_Kho on d.ID_kho equals f.ID_kho
                          join gl in help.ent.Chat_luong on d.Id_chat_luong equals gl.Id_chat_luong

                          where gl.Loai_chat_luong.Contains(TenChatLuong) && f.Ten_kho.Contains(TenKho)&& e.Ten_vat_tu .Contains(tenvt)
                          && e.Ma_vat_tu.Contains(mavt)  && (f.isKhoNgoai == false || f.isKhoNgoai ==null)
                          group d by new { d.Ma_vat_tu, e.Ten_vat_tu } into gs
                          let TotalPoints = gs.Sum(m => m.So_luong)
                          orderby TotalPoints descending

                          select new
                          {

                              Ma_vat_tu = gs.Key.Ma_vat_tu,
                              ten_vat_tu = gs.Key.Ten_vat_tu,
                              so_luong = TotalPoints
                          }).ToList();
                dbcxtransaction.Commit();
                return Utilities.clsThamSoUtilities.ToDataTable(dm);
            }
        }

        public static DataTable GetAll(string mavt)
        {
            // lấy tất cả các phiếu nhập đã xác nhận và loại là hoàn nhập hoac nhap goi dau  lên
            DatabaseHelper help = new DatabaseHelper();
            help.ConnectDatabase();
            using (var dbcxtransaction = help.ent.Database.BeginTransaction())
            {
                var dm = (from d in help.ent.Phieu_Nhap_Kho
                          join l in help.ent.Loai_Phieu_Nhap on d.ID_Loai_Phieu_Nhap equals l.ID_Loai_Phieu_Nhap
                          join ct in help.ent.Chi_Tiet_Phieu_Nhap_Vat_Tu on d.Ma_phieu_nhap equals ct.Ma_phieu_nhap
                          join k in help.ent.DM_Kho on d.ID_kho equals k.ID_kho
                          join v in help.ent.DM_Vat_Tu on ct.Ma_vat_tu equals v.Ma_vat_tu

                          where d.Da_phan_kho == true && (d.isGoiDau == true || l.Ma_loai_phieu_nhap.Contains("T"))
                          && ct.Ma_vat_tu .Contains(mavt)
                          select new
                          {
                              d.Ma_phieu_nhap,
                              ct.Ma_vat_tu ,
                              d.Ngay_lap,

                             so_luong= ct.So_luong_thuc_lanh,
                              d.ID_kho,
                              k.Ten_kho,
                              d.ID_Loai_Phieu_Nhap,
                              l.Ma_loai_phieu_nhap ,
                          }).ToList();
                dbcxtransaction.Commit();
                DataTable ds = Utilities.clsThamSoUtilities.ToDataTable(dm);
                return ds;
            }
        }

        public static string RandomMaPhieu()
        {
            DateTime today = DateTime.Today;
            string year = today.ToString("yy");
            string month = today.ToString("MM");
            string day = today.ToString("dd");
            //kiểm tra xem ngày hiện tại có mã số nào chưa ?\
            //nếu có thì dd.mm.yy.xx+1 vào
            //nếu chưa có thì tạo dd.mm.yy.00

            DatabaseHelper help = new DatabaseHelper();
            help.ConnectDatabase();
            var showPiece = (help.ent.Chi_Tiet_Phieu_Xuat_Tam
               .FirstOrDefault(p => p.Ma_phieu_xuat_tam == help.ent.Chi_Tiet_Phieu_Xuat_Tam.Max(x => x.Ma_phieu_xuat_tam)));
            if (showPiece != null && showPiece.Ma_phieu_xuat_tam.Contains(day + "." + month + "." + year))
            {
                string[] temp = showPiece.Ma_phieu_xuat_tam.Split('.');
                int value = int.Parse(temp[temp.Length]) + 1;
                return day + month + year + showPiece;
            }
            else
            {
                //   showPiece.Ma_phieu_xuat_tam.Split
                return (string)(day + "." + month + "." + year + "." + "0000");
            }
        }

        public int Insert()
        {
            DatabaseHelper help = new DatabaseHelper();
            help.ConnectDatabase();
            // insert
            using (var dbcxtransaction = help.ent.Database.BeginTransaction())
            {
                try
                {
                    var t = new User //Make sure you have a table called test in DB
                    {
                        User_name = this.User_name,
                        Password = this.Password,                   // ID = Guid.NewGuid(),
                    };

                    help.ent.Users.Add(t);
                    help.ent.SaveChanges();
                    dbcxtransaction.Commit();
                    return 1;
                }
                catch (Exception ex)
                {
                    dbcxtransaction.Rollback();
                    return 0;

                }
            }
        }

        //  SqlConnection m_dbConnection = new SqlConnection(clsThamSoUtilities.connectionString);
        public DataTable GetAll(string name, string mavattu, string tenvattu, string TenChatLuong)
        {
            DatabaseHelper help = new DatabaseHelper();
            help.ConnectDatabase();
            using (var dbcxtransaction = help.ent.Database.BeginTransaction())
            {
                var dm = (from d in help.ent.Vat_Tu_Goi_Dau_Ky
                          join e in help.ent.DM_Vat_Tu on d.Ma_vat_tu equals e.Ma_vat_tu
                          join f in help.ent.DM_Kho on d.ID_kho equals f.ID_kho
                          join gl in help.ent.Chat_luong on d.ID_chat_luong equals gl.Id_chat_luong

                          where gl.Loai_chat_luong.Contains(TenChatLuong) && f.Ten_kho.Contains(name)&& e.Ten_vat_tu .Contains(tenvattu)
                          && e.Ma_vat_tu.Contains(mavattu)
                          group d by new { d.Ma_vat_tu, e.Ten_vat_tu , f.Ten_kho,gl.Loai_chat_luong} into gs
                          let TotalPoints = gs.Sum(m => m.So_Luong)
                          orderby TotalPoints descending

                          select new
                          {
                              ten_kho = gs.Key.Ten_kho,

                              Chat_luong = gs.Key.Loai_chat_luong,

                              Ma_vat_tu = gs.Key.Ma_vat_tu,
                              ten_vat_tu = gs.Key.Ten_vat_tu,
                              so_luong = TotalPoints
                          }).ToList();
                dbcxtransaction.Commit();
                return Utilities.clsThamSoUtilities.ToDataTable(dm);
            }
        }

        public override int BoDuyet(int id)
        {
            DatabaseHelper help = new DatabaseHelper();
            help.ConnectDatabase();

            using (var dbcxtransaction = help.ent.Database.BeginTransaction())
            {
                PhieuNhap pn = new PhieuNhap();
                if (pn.BoDuyet(id,help) == 1)
                {

                    {

                        var pnk = (from d in help.ent.Phieu_Nhap_Kho
                                   where d.ID_phieu_nhap == id
                                   select d).FirstOrDefault();
                        if (pnk == null)
                            return 0;

                        var entryPointCTPN = (from d in help.ent.Chi_Tiet_Phieu_Nhap_Vat_Tu
                                              where d.Ma_phieu_nhap == pnk.Ma_phieu_nhap
                                              select d).ToList();
                        if (entryPointCTPN.Count == 0)
                            return 0;
                        for (int i = 0; i < entryPointCTPN.Count; i++)
                        {
                            string mavattu = entryPointCTPN[i].Ma_vat_tu;
                            int idcl = (int)entryPointCTPN[i].Id_chat_luong;
                            string maphieu = entryPointCTPN[i].Ma_phieu_nhap;
                            decimal sl = (decimal)entryPointCTPN[i].So_luong_thuc_lanh;
                            DateTime ngay_xuat = (DateTime)pnk.Ngay_lap;
                            string dien_giai = pnk.Ly_do;
                            var entryPointGD = (from d in help.ent.Vat_Tu_Goi_Dau_Ky

                                                where d.ID_kho == pnk.ID_kho && d.Ma_vat_tu == mavattu && d.ID_chat_luong == idcl
                                                select d).FirstOrDefault();
                            if (entryPointGD == null || entryPointGD.So_Luong < sl)
                            {
                                return 0;
                            }
                            entryPointGD.So_Luong = entryPointGD.So_Luong - sl;
                            help.ent.Vat_Tu_Goi_Dau_Ky.Attach(entryPointGD);
                            help.ent.Entry(entryPointGD).State = EntityState.Modified;
                            help.ent.SaveChanges();

                        }

                        dbcxtransaction.Commit();
                        return 1;
                    }
                }
            }
            return base.BoDuyet(id);
        }

 public static Chi_Tiet_Phieu_Nhap_Vat_Tu getChitiet(int id )
 {
     DatabaseHelper help = new DatabaseHelper();
     help.ConnectDatabase();
     using (var dbcxtransaction = help.ent.Database.BeginTransaction())
     {
         var dm = (from d in help.ent.Chi_Tiet_Phieu_Nhap_Vat_Tu
                   where d.ID_chi_tiet_phieu_nhap_vat_tu == id
                   select d).FirstOrDefault();
         return dm;
     }
 }

        //public static object getAll()
        //{
        //    DatabaseHelper help = new DatabaseHelper();
        //    help.ConnectDatabase();
        //    using (var dbcxtransaction = help.ent.Database.BeginTransaction())
        //    {
        //        var dm = from d in help.ent.Chi_Tiet_Ton_Kho
        //                 select new
        //                 {
        //                     d.ID_Chi_tiet_ton_kho,
        //                     d.ID_Ton_kho,
        //                     d.Ma_phieu,
        //                     d.So_luong,
        //                     d.Tang_Giam
        //                 };
        //        dbcxtransaction.Commit();
        //        return (object)dm.ToList();
        //    }
        //}
        //public bool CheckTonTaiSoDK(int idkho, string maVT)
        //{
        ////    DatabaseHelper help = new DatabaseHelper();
        ////    help.ConnectDatabase();
        ////    var temp = help.ent.Ton_kho.Where(
        ////i => i.Ma_vat_tu == maVT
        ////).ToList();
        ////    string name = "";
        ////    temp.ToList().ForEach((n) =>
        ////    {
        ////        name = n.Ten_vat_tu;
        ////    });
        ////    return name;
        //}
        public int Insert()
        {
            DatabaseHelper help = new DatabaseHelper();
            help.ConnectDatabase();
            // insert
            using (var dbcxtransaction = help.ent.Database.BeginTransaction())
            {
                try
                {
                    //var t = new Chi_Tiet_Ton_Kho //Make sure you have a table called test in DB
                    //{
                    //    ID_Ton_kho = this.ID_Ton_kho,
                    //    Ma_phieu = this.Ma_phieu,                   // ID = Guid.NewGuid(),
                    //    So_luong = this.So_luong,
                    //    Ngay_thay_doi = this.Ngay_thay_doi,
                    //    Tang_Giam = this.Tang_giam,

                    //};

                    //help.ent.Chi_Tiet_Ton_Kho.Add(t);
                    //help.ent.SaveChanges();
                    //dbcxtransaction.Commit();
                    return 1;
                }
                catch (Exception ex)
                {
                    dbcxtransaction.Rollback();
                    return 0;

                }
            }

            //DatabaseHelper help = new DatabaseHelper();
            //help.ConnectDatabase();
            //// insert
            //try
            //{
            //    var t = new DM_Kho //Make sure you have a table called test in DB
            //    {
            //        ID_kho = this.ID_kho,
            //        Ten_kho = this.Ten_kho,                   // ID = Guid.NewGuid(),
            //    };

            //    help.ent.DM_Kho.Add(t);
            //    help.ent.SaveChanges();
            //    return 1;
            //}
            //catch (Exception ex)
            //{
            //    return 0;

            //}
        }