Pages

Wednesday 26 September 2012

DB operations using LINQ

Get data from table tblEmpDetail using edmx object TestMVCAppEntities2:


TestMVCAppEntities2 _db = new TestMVCAppEntities2("metadata=res://*/TestMVCAppEntity.csdl|res://*/TestMVCAppEntity.ssdl|res://*/TestMVCAppEntity.msl;provider=System.Data.SqlClient;provider connection string=\"Data Source=SERVERNAME;Initial Catalog=DBNAME;Persist Security Info=True;User ID=USERID;Password=PWD;Connect Timeout=0;MultipleActiveResultSets=True\"");

IEnumerable<tblEmpDetail> temp = (from x in _db.tblEmpDetails
                                                             select x).ToList();




Add or update data in table tblEmpDetail:


 tblEmpDetail temp = new tblEmpDetail();          
           
TestMVCAppEntities2 _db = new TestMVCAppEntities2("metadata=res://*/TestMVCAppEntity.csdl|res://*/TestMVCAppEntity.ssdl|res://*/TestMVCAppEntity.msl;provider=System.Data.SqlClient;provider connection string=\"Data Source=SERVERNAME;Initial Catalog=DBNAME;Persist Security Info=True;User ID=USERID;Password=PWD;Connect Timeout=0;MultipleActiveResultSets=True\"");

            temp.EmpId = entity.Id;
            temp.Address1 = entity.Address1;
            temp.Address2 = entity.Address2;
            temp.CompanyName = entity.CompanyName;
            if (entity.Id == 0)
            {
                _db.ObjectStateManager.ChangeObjectState(temp, EntityState.Added);
                _db.tblEmpDetails.AddObject(temp);
            }
            else
            {
                if ((temp.EntityState == EntityState.Detached))
                    _db.tblEmpDetails.Attach(temp);
                _db.ObjectStateManager.ChangeObjectState(temp, EntityState.Modified);

            }
            _db.SaveChanges();


Delete data from table tblEmpDetail:

TestMVCAppEntities2 _db = new TestMVCAppEntities2("metadata=res://*/TestMVCAppEntity.csdl|res://*/TestMVCAppEntity.ssdl|res://*/TestMVCAppEntity.msl;provider=System.Data.SqlClient;provider connection string=\"Data Source=SERVERNAME;Initial Catalog=DBNAME;Persist Security Info=True;User ID=USERID;Password=PWD;Connect Timeout=0;MultipleActiveResultSets=True\"");


             tblEmpDetail item = _db.tblEmpDetails.Single(x => x.EmpId== 1);
            _db.tblEmpDetails.DeleteObject(item);
             _db.SaveChanges();