p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   BOOK: Stephens' C# Programming with Visual Studio 2010 24-Hour Trainer (http://p2p.wrox.com/forumdisplay.php?f=574)
-   -   Underlying SQL Query (http://p2p.wrox.com/showthread.php?t=91207)

John S October 18th, 2013 08:11 PM

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?


Rod Stephens October 19th, 2013 01:16 PM

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.

All times are GMT -4. The time now is 02:15 PM.

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