Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old October 18th, 2013, 08:11 PM
Authorized User
Points: 179, Level: 3
Points: 179, Level: 3 Points: 179, Level: 3 Points: 179, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2013
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default Underlying SQL Query

Hello,

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:

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


Code:
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?

thanks
Reply With Quote
  #2 (permalink)  
Old October 19th, 2013, 01:16 PM
Rod Stephens's Avatar
Wrox Author
Points: 3,141, Level: 23
Points: 3,141, Level: 23 Points: 3,141, Level: 23 Points: 3,141, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 640
Thanks: 2
Thanked 96 Times in 95 Posts
Default

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

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

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


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 12: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



All times are GMT -4. The time now is 01:21 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.