C# Discussion Boards - CodeProject

Using a database link in a C# program

29-Dec-03 5:18

This is the behavior of the ComboBox common control, which the System.Windows.Forms.ComboBox class wraps. In order to override this sort of functionality, you should read about the ComboBox common control (in the "User Interface Design and Development" section of MSDN, under "Windows Controls") and extend the ComboBox class in .NET in order to override the WndProc method and handle the notification messages yourself. Some of this might be possible using managed events, but not everything always is.

-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++
-----END GEEK CODE BLOCK-----

extremeg28-Dec-03 21:49

extremeg

28-Dec-03 21:49

I'm a newbee in C# (barely 3 wks old). The problem is that I have two tables in two different databases running on different machines, I need to join these two tables in a query using a database link. Haven searched my libraries thoroughly. The best I came up with was to write the query in a stored procedure, which I called from the program (this is a very slow solution like you'll probaly guess).
The VC#.NET documentation has extensive support for database programming which I have explored...but where is the database link object. Please help needed urgently. Rose | [Rose]

...the mind is not a vessel to be filled but a fire to ignited

Heath Stewart29-Dec-03 5:12

29-Dec-03 5:12

There's no such thing as a database link object. There is connection classes like SqlConnection, but unless you plan on joining these results yourself, you can only use one in certain objects like a SqlDataAdapter. There is ways to link databases together in some RDBMS's like SQL Server. You can then use a stored proc or a view to query both databases using a syntax like "database.owner.object". In most cases, leaving this on the server provides faster access and centralized management of the query and the database links. You could then write a view like so:

SELECT A.Name, A.Birthday, B.AccountID
FROM Table1 A JOIN db2.dbo.Table2 B ON A.ID = B.ID

where db2 is a linked database.

You can find more information about this in the documentation for your RDBMS like SQL Server. I also suggest you move this to the SQL forum if you continue this way.

Otherwise, you'll need two SqlConnection objects and two pairs of SqlCommand and SqlDataReader objects (note, I'm assuming SQL here because many lower-end RDBMS's don't support database linking that would use System.Data.OleDb classes). Then you'll have to read the results and programmatically join these together. This will most likely be much slower and more risky that allowing the database server to do so.

extremeg30-Dec-03 21:20

extremeg

30-Dec-03 21:20

Thanks. I have already tried the first approach (joining the tables in a query that resides in a procedure on the server-side)- this turned out to be quite slow anyway. What I need is how to do this using either the OleDb or SqlClient (programmatically). Help still needed.

...the mind is not a vessel to be filled but a fire to ignited

Heath Stewart31-Dec-03 3:40

31-Dec-03 3:40

The client-side programmatic way is not going to be easy, and most likely will even be slower (think about it - an RDBMS server is typically beefed up to handle many queries where client machines usually aren't - and they both have to go through roughly the same procedure to join the results; the RDBMS is also better optimized for such operations).

The easiest way (and similar to an action plan that an RDBMS would generate) would be to use two SqlDataAdapters (I'll be using classes from the System.Data.SqlClient namespace, but you most likely can do this in the other similar namespaces like System.Data.OleDb) with two different SqlConnections to get a couple of DataSets. Then enumerate the rows in one of them, find rows to join in the other (see DataTable.Select), and put them in a different table (which you can add programmatically - see the documentation on the DataSet class). You could also make a new DataSet to hold both tables - or the cross product, whatever suits your needs.

You can search CP and google for examples, but I doubt you're going to find much because an RDBMS on a decent server is going to handle this a lot better than a client.

extremeg6-Jan-04 20:59

extremeg

6-Jan-04 20:59

Heath, thanks for being helpful on this. I hope some database programmer out there has the way around this problem. I have a project in which the tables and every other database objects must be created programmatically(including the database link) and then written into two different databases and also queried later within the program. For now I'll just keep trying. Once again THANKS.

...the mind is not a vessel to be filled but a fire to ignited

Heath Stewart6-Jan-04 21:11

developing applications - MDI and SDI ?

6-Jan-04 21:11

You're not going to find a magic bullet to solve this. I've already given you the answer. Check my history and profile - I know what I'm talking about. I also do A LOT of development with databases. That's the entire backend - both directly and indirectly using .NET Remoting - for our monstrous application that I designed.

Creating a database completely through code is provider-specific. Yes, you can execute CREATE TABLE, CREATE VIEW, etc. commands using a DbCommand class (like OleDbCommand or SqlCommand, etc.) but actually creating the database itself is entirly specific to the database.

And there is no such thing as a "database link". If you're talking about joining two databases, this is again specific to the database engine. Instead - through code - you can make multiple connections, execute table, view, procedure, etc. statements on each one and copy data into all of them in a loop or something.

There just isn't a pre-canned solution that's going to do this.

Actually read the .NET SDK (especially being a newbie - don't guess about code), specifically the System.Data, System.Data.OleDb, and System.Data.SqlClient namespaces. Read the topics about ADO.NET to understand what happens behind the scenes. If that still isn't enough, read about OLE DB prividers and how they provide generic functionality for ADO and ADO.NET, but anything specific to that provider is not always exposed through abstract layers, which ADO and ADO.NET are (they abstract the specific details away from the client code).

As I also mentioned before, if you use the MSDE (free download, royalty free if you own the right products like VS.NET) or SQL Server, you can create a "database link" as you call it and perform replication, which is basically what you're describing. This can copy both database objects (like tables, views, etc.) and data from database to database, even across disparate networks.

jeffreylreed28-Dec-03 21:27

jeffreylreed

28-Dec-03 21:27

I am new to developing applications and I’m trying to figure out the best way to design my application. I have read many books and whitepapers but have been unsuccessful in learning what seams to be a basic design scenario.

What I want to do is design my application so that the toolbar and main form are consistently the same but have the body of the application change. An example would be MS Outlook, when switching between Mail, Calendar Contacts, etc… How is this done?

I have experimented with MDI and SDI applications but my knowledge of these types is limited. MDI applications seem to be what I want - minus the ability to resize, minimize and close the child window.

Can someone provide some insight into this type of application design, possibly direct me to an example?

Thanks

Re: developing applications - MDI and SDI ?

Heath Stewart29-Dec-03 5:03

Re: developing applications - MDI and SDI ?

29-Dec-03 5:03

Applications like Outlook are neither MDI or SDI - those terms really only apply to document-type applications. Outlook uses a component-based view where the toolbars are queried on the active components and merged with any standard ones. This is pretty much a classic case of an ActiveX container. You should look into that technology if this is what you want. After all, .NET is supposed to be a natural progression from COM and it does borrow many of the ideas - borrowing such ideas from various COM techniques / applications (even using the interfaces themselves in order to interop with COM components) is pretty common. I did so when developing our app.

So, certain actions cause new components to fill some canvas. Upon that happening, information is queried on the component, such as toolbars, menus, etc., and merged with the container application. That container application is also responsible for serializing changes by using a stream or storage object and passing that down the hierarchy of components (this doesn't necessary have to be document-related information - it could also be settings to persist to a file or the registery, or any number of things).

jeffreylreed29-Dec-03 8:45

jeffreylreed

29-Dec-03 8:45

Heath,

Thanks for your reply. However, I don't really understand what the technology you are refering to. I'll research ActiveX Containers, even though I will develop with .NET.

What do you think about this article at MSDN?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskcreatingintricateuserinterfacewithwindowsapplicationobjects.asp

Re: developing applications - MDI and SDI ?

Heath Stewart29-Dec-03 9:32

PrintDocument PrintPage event

29-Dec-03 9:32

Looks okay, but keep in mind that you're just using some of the same concepts as an ActiveX container because there's a lot of good ideas in there. If you even declare many of the same interfaces, you could even interop your components with other COM containers (like Word), or host other COM objects.

Basically, having an understanding of how containers work will be very helpful in your quest to create such a UI.

Yaron K.28-Dec-03 20:36

Yaron K.

28-Dec-03 20:36

Hi
When previewing a document using the PrintDocument class: when i receive a PrintPage event, how can I tell if it's for printing on the screen or the printer? I want a slightly different output when printing to the printer.

Thanks

Re: PrintDocument PrintPage event

Heath Stewart29-Dec-03 4:58

29-Dec-03 4:58

No, because the whole idea of a print preview is to preview what will be printed! Why would you do anything different? Not only does it not make sense, it inconsistent with every other program your users will use.

If you really must, you'll have to keep some boolean flag or something similar. Your app should know when the Print and Print Preview buttons were clicked (or whatever you use) so you can determine what to draw based on the state of that flag.

In any case, there's just really no good way to tell based on the PrintPageEventArgs, or the PrintEventArgs for that matter. The properties would be the same for both screen and print, since the Graphics object is supposed to be created to match the printer on which the PrintDocument will be printed (i.e., scaling, twips, etc.).

Bryan White29-Dec-03 14:58

Bryan White

29-Dec-03 14:58

Do not presume to know the mind of someone asking an apparently stupid question. There is a school of thought that says that there are no stupid questions, only stupid answers.

I beg to differ about:
- there being no good reason to act differently.
- there being no good way to tell the difference.

Now, why would I want Print Preview to be different? Because my graphics lines @ 1200 dpi are TOO THIN to show even at 500% Zoom ! So I want to use thicker lines in print preview (and it's also quicker generating a page of bitmapped graphics @ 200dpi rather than 1200dpi).

I don't like flags. The idea of OO stuff is encapsulation.

Good News:
The PrintDocument passed as object sender to XX_PrintPage contains a member 'PrintController' which has a member 'underlyingController' which has a type of {System.Drawing.Printing.PreviewPrintController} or {System.Drawing.Printing.StandardPrintController}.

Bad News:
This is supposed to be hidden from you (thank you Bill and your people who can't think outside the square and mutter "Why would you do anything different").

Good News:
Use Reflection!
//using System.Reflection;
PrintDocument PD = sender as PrintDocument;
PrintControllerWithStatusDialog PC = PD.PrintController as PrintControllerWithStatusDialog;
FieldInfo FI = PC.GetType().GetField("underlyingController", BindingFlags.NonPublic | BindingFlags.Instance);
PreviewPrintController PPC = FI.GetValue(PC) as PreviewPrintController;

Now, if PPC is not null, it's PrintPreview.
If PPC is null, it's not PrintPreview.

Bad News:
It's ugly, brittle, hardly polymorphic, uses undocumented features of dotNet Framework and might stop working in a future release.
Maybe it's not a good way, but it's better than no way.

Good News:
It works for me, and I hope it does for you, too.

Regards

Brewman

Yaron K.29-Dec-03 19:57

Yaron K.

29-Dec-03 19:57

Worked like a charm. many thanks!!!

Heath Stewart30-Dec-03 3:24

30-Dec-03 3:24

Bryan White wrote:
There is a school of thought that says that there are no stupid questions, only stupid answers.

...and this would be a stupid school to attend. There is stupid questions, and I never said that this original question was stupid. Stupid questions are ones that we get in this - and every other forum - on a somewhat regular basis, like "What does method X do?" This question could easily be answered by simply typing it in the index of your help system, either the MSDN Library that is installed (or through an MSDN Library subscription or higher), or with MSDN Online. It's not so much that the question itself is stupid, just that asking it is stupid because developers should at least be able to answer such simple questions for themselves.

Bryan White wrote:
I don't like flags. The idea of OO stuff is encapsulation.

Is there any reason that part of such encapsulation is not using some sort of state flags? MANY of the implementations in the class library - especially those that wrap the Windows Common Controls - use state flags / variables and act accordingly. It's just part of the encapsulation.

As far as the rest of your reply goes, you have discovered a much better way of determining the output device. While reflection can often times be ugly, it many cases it is necessary. Kudos! Smile | :)

Bryan White4-Jan-04 9:47

Bryan White

4-Jan-04 9:47

You wrote
"
Bryan White wrote:
I don't like flags. The idea of OO stuff is encapsulation.

Is there any reason that part of such encapsulation is not using some sort of state flags? MANY of the implementations in the class library - especially those that wrap the Windows Common Controls - use state flags / variables and act accordingly. It's just part of the encapsulation.
"

I have no problem with flags describing an objects INTERNAL state. You are correct, and I agree. I should probably have said "I don't like flags holding the state of, or describing, OTHER objects". This is hinted at by my following sentence (encapsulation).

If (as in this case) an object(PrintDocument) is being activated from another one(either PreviewPrintController/PrintPreviewDialog or StandardPrintController/PrintDialog), then rather than storing a flag about which one activated it (and causing headaches if you decide Preview = !Standard and a third one is added), I feel that it is better to just store the controller/dialog object as a 'parent object'. Then, Microsoft decided to specifically hide this 'parent object', which I find VERY FRUSTRATING (as you can probably guess from the tone of my earlier response. BTW my own technique for defining an item as "this is public but you probably shouldn't be using it" is to prefix the name with an underscore "_". This may not be part of any standard, but hopefully makes people (and myself in a week's time!) think twice before using it.)

If this hidden member did not exist, then rather than create a flag I would still prefer to:
- create a derived class from PrintDocument (called PrintDocumentWithActivator?) with a member of type object called something like 'parent' or 'activator'.
- store the PrintPreviewDialog(or other activator) object in it
- test this object in the PrintPage event.

I feel that this follows the Open-Closed Principle(OCP) better than a flag. Once the 'activator' object is defined, there need be no further changes in the class if, say, a PrintTo3DEngraverDialog came along later, and the programmer wanted a registered trade mark engraved bottom left rather than a copyright notice printed bottom right.

If the flag was held in the app class, then it would be impossible to decide to how to print the PrintDocument object by just referring to that particular instance; you would need TWO objects to decide (difficult when just one object is passed in an event handler), and in danger of violating the Dependency Inversion Principle(DIP); why should the app know or care how an object is being printed?

This in itself would violate the Interface Segregation Principle(ISP) by polluting the app class with potentially dozens/hundreds of flags for each type of operation that might happen 'lower down'.

Should the PrintDocument be implemented in another application - surprise! The new app needs a flag defined! We now have run into Granularity issues of the Reuse/Release Equivalence Principle(REP) & Common Reuse Principle(CRP).

Yes, flags may be used for INTERNAL states, but NOT for states of OTHER objects. Putting a flag in the app, as suggested by yourself, is NOT a good way to go; the app should neither know nor care which button (Print or PrintPreview) was pressed.

Regards

Brewman

Heath Stewart4-Jan-04 9:54

Using IActiveDesktop in C#

4-Jan-04 9:54

I never said it was a good way! Smile | :)

I agree with you that it isn't good for determining the state of other objects, but it was just a quick and dirty idea that could work if managed correctly (i.e., good synchronization and exception handling).

As I was helping someone else with a similar problem (actually, it might've been the same guy), I did find a way to determine which controller is being used without resorting to reflection. The PrintDocument class has a PrintController property. This could be used in the event handlers (or in OnEventName methods if you encapsulate the PrintDocument) to determine which PrintController - like the PreviewPrintController - is being used as the output device.

Okeno Palmer28-Dec-03 19:01

Okeno Palmer

28-Dec-03 19:01

Greets all,

I'm trying to write an application in C# that will allow me to change to the desktop wallpaper. I would also like to place a calendar control on the Desktop using this application. I tried adding a reference to shell32.dll in Visual Studio 2003 but the IActiveDesktop interface is not listed by VS when I examine the reference.

Any suggestions as to how I may go about getting access to the IActiveDesktop from C#??

.:. Keno .:.

Heath Stewart29-Dec-03 4:48

29-Dec-03 4:48

There's a much simpler way: http://www.c-sharpcorner.com/Code/2002/Sept/ChangeWallpaper.asp[^]

Using the IActiveDesktop interface, you'd have to declare it manually using various attributes from System.Runtime.InteropServices and go to a lot of trouble creating the instance from CLSID_ActiveDesktop (defined in shlguid.h) when only a simple function call is needed. See the link above for an example. This was the 3rd result when I googled for "C# desktop wallpaper".

Okeno Palmer29-Dec-03 5:02

Okeno Palmer

29-Dec-03 5:02

Heath,

Thanks for the reply! I did see that link and it does work for changing hte wallpaper. However I think I may have to go the long way as I would like to have an effect like WebShots where I can have a Calnedar control displayed on the desktop.

Would you recommend I go with C# or C++ to implement this control display? I'd really love to do it with C# to see what is involved though so If you can point me to any useful resources and how to start that would be appreciated.

.:. Keno .:.

Heath Stewart29-Dec-03 5:51

29-Dec-03 5:51

First of all, read the documentation for the System.Runtime.InteropServices elements in the .NET Framework SDK documentation. It's important to understand that. There's also a couple articles about COM interop in .NET that you should read in the articles section of the .NET Framework SDK documentation.

Nick Parker has a pretty good one dealing with some of the issues: http://www.codeproject.com/dotnet/nettocom.asp[^]

You should also be familiar with some basic COM, since you'll be using a lot of this to use the IActiveDesktop interfaces. There are several articles here on CP that should be useful.

Honestly, though, the WebShots calendar is nothing more than a calendar drawn onto an image that is set as wallpaper. It is not an desktop item (at least, not last time I checked). It merely loads an image, paints a calendar on that, saves that image to a temporary spot and sets that as the wallpaper. Again, using the SystemParametersInfo method would be much easier. Just use the System.Drawing classes to load an image (Bitmap class) and draw on it (Graphics class), then save it and use the method. Simple.