LINQ
File:
Linq.docx (25,1 kB)
Linq.doc (50,5 kB)
Linq
Crystal Report using Linq:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
public partial class Sales_Agent_Report : System.Web.UI.Page
{
MyOfficeDataContext OMyOfficeDataContext = new MyOfficeDataContext();
ReportDocument OReportDocument = new ReportDocument();
SqlCommand cmd;
static DataTable dataTable = new DataTable();
SqlDataAdapter adapter;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
dataTable.Clear();
MyCtl1.DoAll();
}
try
{
if (dataTable.Rows.Count > 0)
{
OReportDocument.Load(Server.MapPath("Sales_Agent_Report.rpt"));
OReportDocument.SetDataSource(dataTable);
this.crvSalesAgent.ReportSource = OReportDocument;
this.crvSalesAgent.DataBind();
}
}
catch { }
}
protected void btnSearchAgentName_Click(object sender, EventArgs e)
{
try
{
dataTable.Clear();
var Return_Date = from Date in OMyOfficeDataContext.Tbl_Sales_Agent_Msts
where Date.Tbl_Sales_Agent_Name.Equals(txtAgentName.Text.Trim())
select Date;
cmd = OMyOfficeDataContext.GetCommand(Return_Date) as SqlCommand;
adapter = new SqlDataAdapter(cmd);
adapter.Fill(dataTable);
OReportDocument.Load(Server.MapPath("Sales_Agent_Report.rpt"));
OReportDocument.SetDataSource(dataTable);
this.crvSalesAgent.ReportSource = OReportDocument;
this.crvSalesAgent.DataBind();
}
catch
{
}
}
Another Method For Crystal Report Using Linq:
Note: Use Flags instead of Datatable on pageloading
var Sales_customer = (from customer in OMyOfficeDataContext.Tbl_Sales_Customer_Msts
where customer.Tbl_Sales_Customer_Name.Equals(txtCustName.Text.Trim())
select customer).ToList();
OReportDocument.Load(Server.MapPath("Sales_Customer_Report.rpt"));
OReportDocument.SetDataSource(Sales_customer);
this.crvSalesCustomer.ReportSource = OReportDocument;
this.crvSalesCustomer.DataBind();
Finding Last Inserted Id Using Linq:
Tbl_Inventory_PO_Trn OTbl_Inventory_PO_Trn = new Tbl_Inventory_PO_Trn
{
Tbl_Inventory_PO_DeliverAddress = txtAdd.Text,
Tbl_Inventory_PO_ExpectedDate = txtExpDate.Text,
Tbl_Inventory_PO_LastUpdatedBy = "mohankumar",
Tbl_Inventory_PO_LastUpdatedOn = DateTime.Now.ToString()
};
if (!OMyOfficeDataContext.Tbl_Inventory_PO_Trns.Where
(I => I.Tbl_Inventory_PO_Code.Trim() == txtPurchaseCde.Text.Trim()).Any())
{
OMyOfficeDataContext.Tbl_Inventory_PO_Trns.InsertOnSubmit(OTbl_Inventory_PO_Trn);
OMyOfficeDataContext.SubmitChanges();
//Get Last Inserted PK value from PO Table
int id = OTbl_Inventory_PO_Trn.Tbl_Inventory_PO_Trn_PK;
Linq to Sql Top Concept:
var Login = from LoginDetails in OMyOfficeDataContext.Tbl_Office_Loginhistory_Trns.Take(5)
orderby LoginDetails.Tbl_Office_Loginhistory_PK descending
select new
{
LoginDetails.Tbl_Office_Loginhistory_LoginName,
Tbl_Office_Loginhistory_SignIn = LoginDetails.Tbl_Office_Loginhistory_SignIn + " To " + LoginDetails.Tbl_Office_Loginhistory_SignOut
};
foreach(var Logininfo in Login)
{
ltlLoginName.Text += Logininfo.Tbl_Office_Loginhistory_LoginName +"<br>"+ Logininfo.Tbl_Office_Loginhistory_SignIn + "<br>"+"<br>";
}
Linq to Sql Case Concept:
var Mydiary = from Diary in OMyOfficeDataContext.Tbl_MyDiary_Trns
orderby Diary.Tbl_MyDiary_Task_Priority descending
where Diary.Tbl_MyDiary_Task_Status.Equals("InCompleted") && Diary.Tbl_MyDiary_Task_Lastupdatedby.Equals("Mohan")
select new
{
Diary.Tbl_MyDiary_Task_Name,Diary.Tbl_MyDiary_Task_TaskDate, Diary.Tbl_MyDiary_Task_Time,Diary.Tbl_MyDiary_Trn_PK,Diary.Tbl_MyDiary_Task_ SendMail,Diary.Tbl_MyDiary_Task_SendSms,Diary.Tbl_MyDiary_Task_Status,Diary. Tbl_MyDiary_Task_Date,
Tbl_MyDiary_Task_Priority=Diary.Tbl_MyDiary_Task_Priority.Trim() == "3" ? "High" : Diary.Tbl_MyDiary_Task_Priority == "2" ? "Medium" : "Low"
};
gvMyDiary.DataSource = Mydiary;
gvMyDiary.DataBind();
Linq To Sql Alias:
var Supplier = from Supplier_Det in OMyOfficeDataContext.Tbl_Inventory_Supplier_Msts
select new
{
Supplier_Det.Tbl_Inventory_Supplier_Mst_PK,
//Tbl_Inventory_Supplier_Name Is Alias
Tbl_Inventory_Supplier_Name = Supplier_Det.Tbl_Inventory_Supplier_Name + " [" + Supplier_Det.Tbl_Inventory_Supplier_Code + "]"
};
ddlSupplier_al.DataSource = Supplier;
ddlSupplier_al.DataTextField = "Tbl_Inventory_Supplier_Name";
ddlSupplier_al.DataValueField = "Tbl_Inventory_Supplier_Mst_PK";
ddlSupplier_al.DataBind();
Linq To Sql Distinct:
var POCode = from Code in OMyOfficeDataContext.Tbl_Inventory_PO_Trns
select new
{
Code.Tbl_Inventory_PO_Trn_PK,
Code.Tbl_Inventory_PO_Code };
ddlPOCode_al.DataSource = POCode.Distinct();
ddlPOCode_al.DataTextField = "Tbl_Inventory_PO_Code";
ddlPOCode_al.DataValueField = "Tbl_Inventory_PO_Trn_PK";
ddlPOCode_al.DataBind();
Linq To Sql Sum:
Tbl_Inventory_Invoice_Trn OTbl_Inventory_Invoice_Trn=new Tbl_Inventory_Invoice_Trn();
var amount =from amt in OMyOfficeDataContext.Tbl_Inventory_Invoice_Trns
where amt.Tbl_Inventory_Invoice_Trn_PK.Equals(ddlInvoiceCode_al.SelectedValue)
select amt.Tbl_Inventory_Invoice_Grandtotal;
var paid = from paidtotal in OMyOfficeDataContext.Tbl_Inventory_Invoice_Payment_Trns
where paidtotal.Tbl_Inventory_Invoice_Trn_FK.Equals(Convert.ToInt32(ddlInvoiceCode_al.SelectedValue))
group paidtotal by paidtotal.Tbl_Inventory_Invoice_Trn_FK into gpaid
select new
{
Tbl_Inventory_Invoice_Payment_Paid = gpaid.Sum(paidtotal => paidtotal.Tbl_Inventory_Invoice_Payment_Paid)
};
foreach (var billamt in amount)
{
txtBillAmt.Text = billamt.Value.ToString();
}
Linq To Sql Count:
//Getting Count From Inventory Invoice Return Table
var Invoice_Return = (from InvoiceReturn in OMyOfficeDataContext.Tbl_Inventory_Invoice_Return_Trns
where.Tbl_Inventory_Item_Mst_FK.Equals(Convert.ToInt32(lblItemFK.Text)) &&
InvoiceReturn.Tbl_Inventory_Invoice_Trn_FK.Equals(InvoicePK)
select InvoiceReturn).Count();
Checking Duplication using Linq:
//Checking Duplication
if(!OMyOfficeDataContext.Tbl_Payroll_LeaveType_Msts.Where
( P=> P.Tbl_Payroll_Leave_Code== txtLveCde.Text || P.Tbl_Payroll_Leave_Name==txtLveNme.Text).Any())
{
……
}
Table Join – Using Linq
Linq To Sql Join:
var result = from EmpDesi in omyoffice.Tbl_Employee_Desgination_Msts
join EmpMst in omyoffice.Tbl_Employee_Msts
on EmpDesi.Tbl_Emp_Designation_Mst_PK equals EmpMst.Tbl_Emp_Designation_Mst_FK
select new
{
EmpDesi.Tbl_Emp_Designation_Name,
EmpDesi.Tbl_Emp_Designation_Code,
EmpMst.Tbl_Emp_DOB,
EmpMst.Tbl_Emp_Email
};
GridView1.DataSource = result.Distinct();
GridView1.DataBind();
Linq To Sql Multiple Table Joins:
var result = from invoice_item in omyoffice.Tbl_inventory_Invoice_Items_Trns
join item_mst in omyoffice.Tbl_Inventory_Item_Msts
on
invoice_item.Tbl_Inventory_Item_Mst_FK equals item_mst.Tbl_Inventory_Item_Mst_PK
join
item_Category in omyoffice.Tbl_Inventory_Item_Category_Msts
on
item_mst.Tbl_Inventory_Item_Category_Mst_FK equals item_Category.Tbl_Inventory_Item_Category_Mst_Pk
select new
{
item_mst.Tbl_Inventory_Item_Code,
item_mst.Tbl_Inventory_Item_Reorder_Qty,
item_Category.Tbl_Inventory_Item_Category_Name,
item_Category.Tbl_Inventory_Item_Category_Code
};
GridView1.DataSource = result;
GridView1.DataBind();
gvIncidents.DataSource = from i in db.Incidents
from it in db.IncidentTypes
select new { i, it };
gvIncidents.DataSource =
from i in db.Incidents
from it in db.IncidentTypes
where (i.IncidentTypeId == it.IncidentTypeId)
orderby i.IncidentId descending
select new { IncidentId = i.IncidentId,
DateEntered = i.DateEntered,
EnteredBy = i.EnteredBy,
SchoolId = i.SchoolId,
CallerName = i.CallerName,
ParentIds = i.ParentIds,
StudentIds = i.StudentIds,
IncidentTypeName = it.IncidentTypeName };
gvIncidents.DataSource =
from i in db.Incidents
from it in db.IncidentTypes
where i.IncidentTypeId == it.IncidentTypeId
select i).ToList();
gvIncidents.DataSource = from i in db.Incidents
from it in db.IncidentTypes
where (i.IncidentTypeId == it.IncidentTypeId)
orderby it.IncidentTypeName descending
group i by it.IncidentTypeName into grouping
select new { CountOfIncidents = grouping.Count(),
IncidentTypeName = grouping.Key };
Check Login – Using Linq
int ch1 = 1;
int ch2 = 1;
//Get User account from account master table
var chk1 = from Log in OMyOfficeDataContext.Tbl_Office_Account_Msts
where
Log.Tbl_Office_Account_Userid.Trim().Contains(txtUsername.Text.Trim()) &&
Log.Tbl_Office_Account_Password.Trim().Contains(txtPassword.Text.Trim()) &&
Log.Tbl_Office_Account_Role.Trim().Contains(ddlRole_al.SelectedValue.Trim())
select Log;
foreach (var Check in chk1)
{
//string comparision -> string.Compare(string1,string2,bool IgnoreCase)
ch1 = string.Compare(Check.Tbl_Office_Account_Userid, txtUsername.Text, true);
ch2 = string.Compare(Check.Tbl_Office_Account_Password, txtPassword.Text, false);
}
//0 means equal 1 and -1 greater and smaller values in string comparision
if(ch1==0 && ch2==0)
{
//assign user name to session
Session["User"] = txtUsername.Text;
Response.Redirect("Office_Mst.aspx");
}
else
{
lblDisplay.Text = "Invalid Username or Password"; }