How to execute SQL query in datatable.

Question

1.00/5 (2 votes)

See more:

i have some situation. in which i populate data from database to datatable and then i want to execute some conditon for sum and group by in datatable. so what is the peocedure to execute sql select query in datatable.

my following query generate a output in datatable. or is it possible to create temp table with following query using select * into........ statement.

What I have tried:

SQL

Select MasterCustomer.CustType as 'Identity Name', MasterCustomer.companyCollegeName as 'Contact Person Name', MasterCustomer.CustomerName as 'Customer Name', SUM(TempOrderDetailNew.BookQuantity) As 'OTS Total Book Quantity', Null As 'QuickBilling Total Book Quantity', Null As 'ReturnOrder Total Book Quantity'   
From MasterCustomer 
Inner Join MasterState On MasterCustomer.State = MasterState.StateID 
Inner Join MasterCity On MasterCustomer.CityID = MasterCity.CityID 
Inner Join TempOrderNew On MasterCustomer.CustomerCode = TempOrderNew.CustomerCode 
Inner Join TempOrderDetailNew On TempOrderNew.BillNumber = TempOrderDetailNew.BillNo  
Where (MasterCustomer.UniversityName = 'UU1' or MasterCustomer.UniversityName Like 'UU1-%' or MasterCustomer.UniversityName Like '%-UU1' or MasterCustomer.UniversityName Like '%-UU1-%')  and MasterCustomer.CustType = 'BookSeller'  
Group By MasterCustomer.CustType, MasterCustomer.companyCollegeName, MasterCustomer.CustomerName 

union All
Select MasterCustomer.CustType as 'Identity Name', MasterCustomer.companyCollegeName as 'Contact Person Name', MasterCustomer.CustomerName as 'Customer Name', Null As 'OTS Total Book Quantity', SUM(QuatOerDet.BookQuantity) As 'QuickBilling Total Book Quantity', Null As 'ReturnOrder Total Book Quantity' 
From MasterCustomer 
Inner Join MasterState On MasterCustomer.State = MasterState.StateID 
Inner Join MasterCity On MasterCustomer.CityID = MasterCity.CityID 
Inner Join [QuotOrder] On MasterCustomer.CustomerCode = [QuotOrder].CustomerCode 
Inner Join [QuatOerDet] On [QuotOrder].BillNumber = [QuatOerDet].BillNo  
Where (MasterCustomer.UniversityName = 'UU1' or MasterCustomer.UniversityName Like 'UU1-%' or MasterCustomer.UniversityName Like '%-UU1' or MasterCustomer.UniversityName Like '%-UU1-%')  and MasterCustomer.CustType = 'BookSeller'  and [QuotOrder].TypeOfBill != 'QUOT'
Group By MasterCustomer.CustType, MasterCustomer.companyCollegeName, MasterCustomer.CustomerName 

union All
Select SUM(BookReturnDetailNew.BookQty) As 'ReturnOrder Total Book Quantity'  
From MasterCustomer 
Inner Join MasterState On MasterCustomer.State = MasterState.StateID 
Inner Join MasterCity On MasterCustomer.CityID = MasterCity.CityID 
Inner Join ReturnBookNew On MasterCustomer.CustomerCode = ReturnBookNew.CustomerCode 
Inner Join BookReturnDetailNew On ReturnBookNew.CreditnoteNo = BookReturnDetailNew.CreditnoteNo  
Where (MasterCustomer.UniversityName = 'UU1' or MasterCustomer.UniversityName Like 'UU1-%' or MasterCustomer.UniversityName Like '%-UU1' or MasterCustomer.UniversityName Like '%-UU1-%')  and MasterCustomer.CustType = 'BookSeller'  
Group By MasterCustomer.CustType, MasterCustomer.companyCollegeName, MasterCustomer.CustomerName

Posted 8-May-17 2:04am

kantagrawal

Updated 8-May-17 2:24am

Maciej Los

v2

Add a Solution

Comments

CHill60 8-May-17 8:52am

As an aside, that enormous query will fail ... you cannot UNION queries unless they return exactly the same number of columns (and the types match)

kantagrawal 9-May-17 7:03am

Dear friend, my query is execute properly. and have same no of columns and datatype. it also fills datatable. but it generate 'Total book quantity' for the same CustomerType in different rows due to three union queries. and i just want these three 'Total book quantity' for the CustomerType in single row.

CHill60 9-May-17 7:15am

If it executes properly then you have not copied it into your question properly. The first query returns

Select MasterCustomer.CustType as 'Identity Name', MasterCustomer.companyCollegeName as 'Contact Person Name', MasterCustomer.CustomerName as 'Customer Name', SUM(TempOrderDetailNew.BookQuantity) As 'OTS Total Book Quantity', Null As 'QuickBilling Total Book Quantity', Null As 'ReturnOrder Total Book Quantity'

but the last part of the query only returns

Select SUM(BookReturnDetailNew.BookQty) As 'ReturnOrder Total Book Quantity'  
From MasterCustomer

To get further help I strongly suggest that you simplify the query down to the very minimum that will reproduce your problem. Give some sample data (for that query) and your actual and expected results

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2017-05-08T02:23:00

You can use DataTable.Select Method (String) (System.Data)[^]

[EDIT] I re-read you question and spotted the

Quote:
i want to execute some conditon for sum and group by in datatable

The Select on the datatable is only a filter so will not help you with group by or sum. Personally, I would just go back to the database for that information. Otherwise you will need to step through the results e.g.

var sum = 0;
foreach (DataRow dr in dt.Rows)
{
    sum += int.Parse(dr.ItemArray[3].ToString());
}

You can convert some of this to Linq e.g. the above is the same as

var sum = dt.Rows.Cast<DataRow>().Sum(dr => int.Parse(dr.ItemArray[3].ToString()));

Maciej Los · Answer 2 · 2017-05-08T02:23:00

Solution 2

Take a look here: How to: Execute a Stored Procedure that Returns Rows[^]

Posted 8-May-17 2:23am