Wrox Programmer Forums
BOOK: Stephens' C# Programming with Visual Studio 2010 24-Hour Trainer
This is the forum to discuss the Wrox book Stephens' C# Programming with Visual Studio 2010 24-Hour Trainer by Rod Stephens; ISBN: 9780470596906
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Stephens' C# Programming with Visual Studio 2010 24-Hour Trainer section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old October 18th, 2013, 08:11 PM
Authorized User
Join Date: May 2013
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default Underlying SQL Query


I can start a New Project and connect to an existing .mdf database, and display the tables from the database in the Database Explorer and Data Source windows.

I can drag a table to the IDE, but how do I access the actual SQL query that is being displayed?

I would like to modify it to join my tables and then display them.

I already have the relationships made with primary and foreign keys by using SQL Management studio, but would like to modify the Query used in the IDE to add the INNER JOINs.

That is, I would like to change:

SELECT serial_number, location, inspection_date FROM items
that the IDE is using, to:

SELECT c.item_name, i.serial_number, c.manufacturer, i.location, i.inspection_date

FROM catalog_item c INNER JOIN item i
ON c.item_id = i.item_id
I can figure out the SQL code, but where do I insert it into Visual C# IDE?

Old October 19th, 2013, 01:16 PM
Rod Stephens's Avatar
Wrox Author
Join Date: Jan 2006
Posts: 647
Thanks: 2
Thanked 96 Times in 95 Posts

Hi John,

Dragging-and-dropping is really intended only to build a quick and easy database program. It's not really intended for complicated things like this (although I know it's not all that complicated). There are probably ways to do this but it may be tricky.

You could try this:
  • In the component tray below the form in the form designer, right-click the table adapter manager and select "Edit in DataSet designer."
  • Select the bottom entry, which should say "Fill, GetData()."
  • In the Properties window, click on the CommandText property. Right-click the ellipsis to the right to open the Query Builder.
  • Use the Query Builder to enter the new query.
  • Click OK. When it asks if you want to rebuild the commands, click Yes.
  • Close the DataSet designer. Click yes to save.

I'm not sure that will work but it's all I can think to try off hand.

Probably a better approach would be to add a view to the database to select the data that you want to display. Then you should be able to drag and drop the view onto the form.

You could also try using Entity Framework objects. I've only used them a little so I don't have any good information for you about them. Searching for information on using Entity Framework will probably get you better information than I can give you off hand.

One problem with any change of this kind is that the data adapter may be unable to figure out how to save changes to the database so you might end up with a read-only view.

Normally for "real" database applications, I don't use the drag and drop method. Instead I use my own DataAdapter and DataSet code to select, display, and update the data. It's more work but I have a better understanding of exactly what's happening.

For some examples that do something like that, see these examples:

Finally, if you need to a lot of this sort of thing, you should probably look for a good database programming book. The "24-Hour Trainer" book is really just to get you started and only covers database programming a little bit. It's a huge topic so you may want more in-depth information.

I hope that helps. Let me know if you find a good approach or if you're still stuck and I'll try to find a more concrete example for you.

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)

Similar Threads
Thread Thread Starter Forum Replies Last Post
The underlying connection was closed: jhouse .NET Web Services 33 September 21st, 2011 04:26 PM
Output Query to txt file from SQL Query everest SQL Server 2005 4 November 22nd, 2007 01:49 AM
Underlying connection was closed r_ganesh76 .NET Web Services 0 May 4th, 2006 04:49 AM
The underlying connection was closed: Unable to co anurag_singh02 .NET Web Services 1 August 17th, 2005 11:15 PM
Display subreport with no underlying data bph Access 4 April 11th, 2005 04:08 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.