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 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 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
}
}
