protected void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e) { using (SampleDataContext dbContext = new SampleDataContext()) { dbContext.Log = Response.Output; switch (RadioButtonList1.SelectedValue) { case "Permanent": GridView1.DataSource = dbContext.Employees2LINQ.OfType <PermanentEmployee>().ToList(); GridView1.DataBind(); break; case "Contract": GridView1.DataSource = dbContext.Employees2LINQ.OfType <ContractEmployee>().ToList(); GridView1.DataBind(); break; case "All": GridView1.DataSource = ConvertEmployeesForDisplay(dbContext.Employees2LINQ.ToList()); GridView1.DataBind(); break; default: //Employees2LINQ jest abstrakt GridView1.DataSource = dbContext.Employees2LINQ.ToList(); GridView1.DataBind(); break; } } }
private void GetDataEagerLoading(int choice) { //wczytuje dodatkowe wraz z wczytaniem wybranych //left join jest //tracimy miejsce w pamieci ale mniej zapytan (lepszy performence) using (SampleDataContext dbContext = new SampleDataContext()) { dbContext.Log = Response.Output; switch (choice) { case 1: DataLoadOptions loadOptions = new DataLoadOptions(); loadOptions.LoadWith <DepartmentsLINQ>(d => d.EmployeesLINQ); //wraz z dept wczytaj emp dbContext.LoadOptions = loadOptions; GridView2.DataSource = dbContext.DepartmentsLINQ; break; case 2: GridView2.DataSource = from DepartmentsLINQ in dbContext.DepartmentsLINQ select new { Name = DepartmentsLINQ.Name, EmployeesLINQ = DepartmentsLINQ.EmployeesLINQ }; break; } GridView2.DataBind(); } }
private void GetData() { SampleDataContext dbContext = new SampleDataContext(); // GridView1.DataSource = dbContext.EmployeesLINQ;//all /* * var linqQuery = from EmployeesLINQ in dbContext.EmployeesLINQ * select EmployeesLINQ; * GridView1.DataSource = linqQuery; */ Log(dbContext, 0); /* * GridView1.DataSource = from EmployeesLINQ in dbContext.EmployeesLINQ * where EmployeesLINQ.Gender == "Male" * orderby EmployeesLINQ.Salary descending * select EmployeesLINQ; */ //linq query //Linq to sql zamienia na jezyk sql i potem zamienia wyniki na objecty GridView1.DataSource = dbContext.GetEmployeesLINQ();//procedura GridView1.DataBind(); }
protected void btnExecute_Click(object sender, EventArgs e) { //nie używamy using (SampleDataContext dbContext = new SampleDataContext()) { int count = dbContext.ExecuteCommand("Update EmployeesLINQ set FirstName='Jack' where ID=100"); Response.Write("<script>alert('Wykonano na " + count + " rekordach.')</script>"); } }
protected void btnGetEmpByDep_Click(object sender, EventArgs e) { using (SampleDataContext dbContext = new SampleDataContext()) { string deptName = string.Empty; GridView1.DataSource = dbContext.GetEmployeesByDepartmentLINQ(1, ref deptName); GridView1.DataBind(); lblDept.Text = "Department Name = " + deptName; } }
protected void btnDelete_Click(object sender, EventArgs e) { using (SampleDataContext dbContext = new SampleDataContext()) { EmployeesLINQ emp = dbContext.EmployeesLINQ.SingleOrDefault(x => x.ID == 1003); dbContext.EmployeesLINQ.DeleteOnSubmit(emp); dbContext.SubmitChanges(); } GetData(); }
protected void btnUpdate_Click(object sender, EventArgs e) { using (SampleDataContext dbContext = new SampleDataContext()) { EmployeesLINQ emp = dbContext.EmployeesLINQ.SingleOrDefault(x => x.ID == 1003); emp.Salary = 65000; dbContext.SubmitChanges(); } GetData(); }
protected void btnDirect_Click(object sender, EventArgs e) { using (SampleDataContext dbContext = new SampleDataContext()) { IEnumerable <EmployeesLINQ> emp = dbContext.ExecuteQuery <EmployeesLINQ>("Select * from EmployeesLINQ where ID ={0}", 2); foreach (EmployeesLINQ em in emp) { Response.Write("<script>alert('Employee with ID 2 is " + em.FirstName + "')</script>"); } } }
private void GetDataLazyLoading() { //nie wczytuje poki nie dojdziemy do nich //select emp where id 1,2,3 //n+1 problem select * dept, select * emp from dept 1,2,...,n //wiecej zapytan (gorszy performence), mniej miejsca tracimy using (SampleDataContext dbContext = new SampleDataContext()) { dbContext.Log = Response.Output; GridView2.DataSource = dbContext.DepartmentsLINQ; GridView2.DataBind(); } }
protected void btnCompQuery_Click(object sender, EventArgs e) { //Compiled Query, szybsze var compliedQuery = CompiledQuery.Compile( (SampleDataContext dataContext, int EmpID) => (from s in dataContext.EmployeesLINQ where s.ID == EmpID select s).Single()); using (SampleDataContext dbContext = new SampleDataContext()) { EmployeesLINQ emp = compliedQuery(dbContext, 1); Response.Write("<script>alert('Employee with ID 1 is " + emp.FirstName + "')</script>"); } }
protected void btnInsert_Click(object sender, EventArgs e) { using (SampleDataContext dbContext = new SampleDataContext()) { EmployeesLINQ emp = new EmployeesLINQ { FirstName = "Tim", LastName = "T", Salary = 55000, DepartmentId = 1 }; dbContext.EmployeesLINQ.InsertOnSubmit(emp); dbContext.SubmitChanges(); } GetData(); }
protected void Page_Load(object sender, EventArgs e) { // 1. create a new item called linq to sql //2. Connect to your SQL database and drag and drop table //3 MUST reference the sampledatacontext - this can be found in sample.designer.cs SampleDataContext dataContext = new SampleDataContext(); //in memeory objects //int[] numbers = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }; //GridView1.DataSource = from number in numbers // where (number % 2) == 0 // select number; GridView1.DataSource = from student in dataContext.tblStudents where student.Gender == "Male" select student; GridView1.DataBind(); }
protected void btnCache_Click(object sender, EventArgs e) { using (SampleDataContext dbContext1 = new SampleDataContext()) { using (SampleDataContext dbContext2 = new SampleDataContext()) { EmployeesLINQ E1 = dbContext1.EmployeesLINQ.FirstOrDefault(x => x.ID == 1); EmployeesLINQ E2 = dbContext2.EmployeesLINQ.FirstOrDefault(x => x.ID == 1); Response.Write("<script>alert('E1 name " + E1.FirstName + " E2 name " + E2.FirstName + "')</script>"); E1.FirstName = "Steve"; dbContext1.SubmitChanges(); Response.Write("<script>alert('Zmiana imienia')</script>"); E2 = dbContext2.EmployeesLINQ.FirstOrDefault(x => x.ID == 1); Response.Write("<script>alert('E1 name " + E1.FirstName + " E2 name " + E2.FirstName + "')</script>"); dbContext2.Refresh(RefreshMode.OverwriteCurrentValues, E2); Response.Write("<script>alert('Reload cachu')</script>"); Response.Write("<script>alert('E1 name " + E1.FirstName + " E2 name " + E2.FirstName + "')</script>"); } } }
protected void btnAddEmp_Click(object sender, EventArgs e) { using (SampleDataContext dbContext = new SampleDataContext()) { PermanentEmployee permanentEmployee = new PermanentEmployee { Name = "Emma", Gender = "Female", AnuualSalary = 65000 }; ContractEmployee contractEmployee = new ContractEmployee { Name = "Kristie", Gender = "Female", HourlyPay = 50, HoursWorked = 80 }; dbContext.Employees2LINQ.InsertOnSubmit(permanentEmployee); dbContext.Employees2LINQ.InsertOnSubmit(contractEmployee); dbContext.SubmitChanges(); } }
private void Log(SampleDataContext dbContext, int choice) { switch (choice) { case 1: dbContext.Log = Response.Output; //wyrzuca komende na strone //dbContext.Log = Console.Out; //na konsole break; case 2: var linqQuery = from EmployeesLINQ in dbContext.EmployeesLINQ select EmployeesLINQ; Response.Write(dbContext.GetCommand(linqQuery).CommandText); break; case 3: var linqQuery2 = from EmployeesLINQ in dbContext.EmployeesLINQ select EmployeesLINQ; string sqlQuetry = linqQuery2.ToString(); Response.Write(sqlQuetry); break; //jest jeszcze profiler w sql serverze } }