Dataset problem - Northwind Database

R-Byter
R-Byter used Ask the Experts™
on
Hello fellow experts,

This is one problem I'm trying to solve to help my brother, but I'm out of ideas since I'm new to C#. He is trying to learn something and I'm trying to help him but with not much luck for now. I can only apply logic from another languages but obviously that's not enough.
We are talking about famous Northwind database in SQL server.
Here is the thing, in one form there is one combobox with the list of last names from table Employees and two date fields where you can select date range.
He wants to create Report, not Crystal one. In report he wants to display:

Orders.Orderdate
Customers.CompanyName
Products.ProductName
Products.UnitPrice
Order Details.Quantity

To sum it all, he wants to display every single order which is associated with selected employee and within  selected date range.
What he managed to do is to create dataset with these tables and associated relations: Orders, Customers, Products and Order Details.
Initially, I thought it is possible to create dataset and fill it with data from SQL query which would get all required data. However, while I browsed through examples on the internet I realized he needs to use foreach loops and that is the point where Im stumped.
Im sure that some of you find this easy, but since this is not my area of expertise I am not able to help my brother which is beginner in C#.
I hope that someone will be able to help us here.
Thanks in advance for any help.

Regards
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
What reporting tool does he want to use?

He could use a datagrid linked to the query to display the data.

mlmcc
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You may study the project:

Northwind.NET

and the nice reports within this, all created with the native report designer.

/gustav
AndyAinscowFreelance programmer / Consultant

Commented:
Just an aside:
Based on what you say I think it should be possible to get the information completely with SQL (no foreach loops).
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Top Expert 2010

Author

Commented:
@mlmcc and cactus_data:

Will review it and get back with the results. Thanks for your answers. I'm trying to help him since he is beginner and I do have very limited knwoledge about C#.

@AndyAinscow:

That was just my thinking, I didnt say it is possible, just a thought. Its quite possible that I didnt really understand what dataset means. I thought of it merely as a container which holds data to be used in application with no need for database connection.

It truly means something when I can have help from such experienced experts (looking at your badges and registration date). Thanks again guys.

Regards
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
My advice would be to leave the low-level SQL stuff and use at least Datatable Adapters and datasets or the Entity Framework (EF) as shown in the Northwind.NET project.

It of course takes something to begin with but it pays off in the end.

Don't look for code examples, look for tutorials. There are many of these, also as videos. That's what I did.

/gustav
Top Expert 2010

Author

Commented:
Could you point me to the right ones? The ones that are easy to follow. Thanks
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Here are enough to keep you busy for a while ...:

“How Do I?” Videos for Visual C#

/gustav
AndyAinscowFreelance programmer / Consultant

Commented:
>>I thought of it merely as a container which holds data to be used in application with no need for database connection.

That is pretty much what it is.

What I meant is that you can
either: Create multiple datsets, each based on part of the data you want and then loop through to extract it.  
or: Create one datset and let SQL collect all the data together for you.  (You could even view the results at the database to confirm it is working as you expect before writing the C# code).
Top Expert 2010

Author

Commented:
Thanks guys for your replies. Once again, give me a day to check all this. I know its good to give replies as soon as possible but day at least is as fast as I can.

AndyAinscow, Im very interested to see how to do this:

"Create one dataset and let SQL collect all the data together for you."

Im a little bit puzzled there on hot to do that exactly. First create SQL and then dataset or the other way around? Keep in mind that Im talking about one single query with data from multiple tables, just as I wrote in my question.

Once again, thanks to all, you already helped us to learn something.

Regards
Freelance programmer / Consultant
Commented:
You can create a query which is based upon many tables.
Eg. If a Customer has many orders you can join a customer table to the order table, use a where condition to select customer xyz and see a list of all the orders they have.
Further (eg. working from child to parent) if each order can have many products (a third table) you could create a query joining all three to view all the customers that have at some time purchased a FooBar
Top Expert 2010

Author

Commented:
SQL would be easy to write since I have experience in that. The problem started when i tried to find out how to fill Dataset with returned data. Is it possible for you to give me some piece of code?

Thanks
Top Expert 2010

Author

Commented:
Sorry guys for not replying sooner.
AndyAinscow, your answers gave the best way to find solution, and I'll give some points to others if you dont mind.

Regards

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial