C# Corner
Tech
News
Videos
Forums
Jobs
Books
Events
More
Interviews
Live
Learn
Training
Career
Members
Blogs
Challenges
Certification
Contribute
Article
Blog
Video
Ebook
Interview Question
Collapse
Feed
Dashboard
Wallet
Learn
Achievements
Network
Refer
Rewards
SharpGPT
Premium
Contribute
Article
Blog
Video
Ebook
Interview Question
Register
Login
Join Using LINQ to SQL
WhatsApp
Siddhartha Mohanty
9y
7.8
k
0
1
25
Blog
project docs.rar
REQUREMENTS(DataBase Logic)
Table1-Invoice Table
Table2-Credit Notes(Crdeitedamount COls)
Table3-Recepiept Table (ReceivedAmount COLs)
Logic For Grid Calculation:
Total Amount=Sum(Invoice AMount -Tot CreditedNotes Amount) group by invoice ID Left join Invoice Table.
Received=Sum(total Received) group by Invoice ID left join Invoice Table.
Outstanding Amount=Total amount-Received.
using
System;
using
System.Configuration;
using
System.Data;
using
System.Linq;
using
System.Web;
using
System.Web.Security;
using
System.Web.UI;
using
System.Web.UI.HtmlControls;
using
System.Web.UI.WebControls;
using
System.Web.UI.WebControls.WebParts;
using
System.Xml.Linq;
using
System.Data.SqlClient;
public
partial
class
_Default: System.Web.UI.Page {
static
SqlConnection con =
new
SqlConnection(ConfigurationManager.ConnectionStrings[
"RealConnectionString"
].ConnectionString);
protected
void
Page_Load(
object
sender, EventArgs e) {
DataClasses2DataContext db =
new
DataClasses2DataContext();
db.Connection.ConnectionString = con.ConnectionString;
//from invoice main
var invoicemain = from c
in
db.Customerinvoices
where c.status ==
"closed"
select c;
//from Received
var receivedmain = from c
in
db.invoicereceiveds
where c.status ==
"posted"
group c by c.invoiceid into g
select
new
{
receivedid = g.Key,
receivedamount = g.Sum(x => x.recevedamount)
};
//total received amount group by invoiceid
//sum of credited amount group by invoice id
var sumcredited = from x
in
db.creditnotes
group x by x.invoiceid
into g
select
new
{
invoiceid = g.Key,
creditedamount = g.Sum(x => x.creditamount)
};
//***** ********* List of totalinvoiceamount**** ***** ************
var totinvoiceamnt = from x1
in
invoicemain
join x2
in
sumcredited
on x1.invoiceid equals x2.invoiceid
into g
from c
in
g.DefaultIfEmpty()
join x3
in
receivedmain
on x1.invoiceid equals x3.receivedid
into g1
from c1
in
g1.DefaultIfEmpty()
select
new
{
invoiceid = x1.invoiceid,
TotalInvoiceAmount = x1.invoicamount - c.creditedamount,
Recevied = c1.receivedamount,
invoiceno = x1.invoiceno,
invoicedate = x1.invoicedate
};
grid1.Datasourec(totinvoiceamnt);
grid1.databind();
}
}
Join Using LINQ to SQL
People also reading
Membership not found