Re: Postgres database connection - C# Discussion Boards

1-Mar-11 9:32

I am new to databases and C#. In my main program in C#, I can open a connection to postgresdb , do some queries and close connection afterwards. But, I don't like to open it up every time I query the db, is there a way where the db stays opened for the life of the application, so I can query things anytime through out the life of the program and only close when I kill the app.... How is it normally done in big apps that query db quite often?

Is this the right way of doing it, or am i missing something here?

Thanks

Orcun Iyigun1-Mar-11 9:38

Orcun Iyigun

1-Mar-11 9:38

You can do it by database pooling.

See this link to get more info about it;

http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx[^]

In fact closing the connection is recommended. Here is a quote from the documentation:

It is recommended that you always close the Connection when you are finished using it in order for the connection to be returned to the pool. This can be done using either the Close or Dispose methods of the Connection object. Connections that are not explicitly closed might not be added or returned to the pool. For example, a connection that has gone out of scope but that has not been explicitly closed will only be returned to the connection pool if the maximum pool size has been reached and the connection is still valid.

Software20071-Mar-11 9:45

1-Mar-11 9:45

Thanks for the article. It sounds a bit expensive and kinda boring though, so if my app has to access the db 100 times, then I would have 100 connections and closes! Yikes!

Dave Kreskowiak1-Mar-11 9:46

Dave Kreskowiak

1-Mar-11 9:46

So? If you're hogging a $1,000 license and not using it, your manager goes "Yikes!", not you.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

Dave Kreskowiak1-Mar-11 9:45

Dave Kreskowiak

1-Mar-11 9:45

Software2007 wrote:
I can open a connection to postgresdb , do some queries and close connection
afterwards. But, I don't like to open it up every time I query the db

Tough. That's the "best practice" method to use.

Software2007 wrote:
is there a way where the db stays opened for the life of the application, so I
can query things anytime through out the life of the program and only close when
I kill the app

Bad idea. Database connection licenses are expensive. So, to minimize costs, you are expected to open the connection, use it, then close it as soon as possible so another application can use the connection license on the server.

Think about it. You open a connection for the life of your app, then your app crashes or the network connection goes down, but the SQL connection is never released. Now you've got a connection license being used on the server, but never being used until it's cleared out manually or the connection times out on the server for inactivity.

Software2007 wrote:
How is it normally done in big apps

Open the connection, query it, close it.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

Software20071-Mar-11 9:56

1-Mar-11 9:56

thanks for the clarification. Again, I am a newbie to db stuff, but in my little brain, I was thinking may be they have soft/virtual connections or something like that where it wouldn't be so expensive and dramatic. I must have been living in fantasy land!
Thanks again

Pete O'Hanlon1-Mar-11 9:56

Pete O'Hanlon

1-Mar-11 9:56

Software2007 wrote:
am i missing something here?

Big style. It's called scalability. Database connections are expensive resources, and should be treated as precious resources.

Software2007 wrote:
But, I don't like to open it up every time I query the db

So, laziness is a virtue in your world?

Software2007 wrote:
In my main program in C#, I can open a connection to postgresdb , do some queries and close connection afterwards.

Is it a desktop application? If it's a web application then you can't do this anyway, so you might as well get used to the idea of acquiring the connection at the last minute and releasing it as soon as possible.

Here's a poser for you. Suppose you open your connection at the start of your application and your code relies on this connection being open. Now, suppose that the database crashes and restarts between you querying it. What do you think will happen the next time you issue a query? If you guessed that the code will fail, go to the top of the class. If you think you can rely on the connection always being available, slap yourself around the head with a pink halibut and call yourself Ethel.

I'm not a stalker, I just know things. Oh by the way, you're out of milk.

Forgive your enemies - it messes with their heads

My blog | My articles | MoXAML PowerToys | Onyx

Software20071-Mar-11 10:26

1-Mar-11 10:26

Good answers. I knew the whole costly issue and the concequences of crashing while executing, but I was hoping that I was wrong and there was a more efficient way.

Pete O'Hanlon1-Mar-11 10:34

Pete O'Hanlon

1-Mar-11 10:34

Write yourself a wrapper class that opens up the connection for you when you execute your SQL if you're worried about remembering to do this.

I'm not a stalker, I just know things. Oh by the way, you're out of milk.

Forgive your enemies - it messes with their heads

My blog | My articles | MoXAML PowerToys | Onyx

Software20071-Mar-11 10:40

1-Mar-11 10:40

Thanks, but, No, I'll just try to suffer like the rest of us. Laugh | :laugh:

PIEBALDconsult1-Mar-11 13:55

PIEBALDconsult

1-Mar-11 13:55

To clarify more (I hope); you can hold the Connection instance for the duration of your application, but only open it when you need it then close it. That's what I do, even with Windows Services that run 7x24 forever. And also use connection pooling.

It's really very easy, if you think it's difficult then you must be doing it wrong. You can check out this fine article[^], or use a DataAdapter (which I don't recommend).

File.Move() method

Orcun Iyigun1-Mar-11 9:30

Orcun Iyigun

1-Mar-11 9:30

I am trying to rename a file with File.Move() method. I get an exception on my File.Move() code. the exception is like "The file is already exists|r\n"

I have checked if the file exists or not. The file exists.

string fileToRenamePath = RMAConfig.PDFPath + this.rmaObjSum.RMANum + RMAConfig.Extension;
string fileRenamedPath = RMAConfig.PDFPath + "[Return Documentation" + rmaObjSum.RMANum + "]" + RMAConfig.Extension;
File.Move(fileToRenamePath, fileRenamedPath);

I worked it around instead of using File.Move() method;

I have used:

File.Copy(fileToRenamePath, fileRenamedPath);
File.Delete(fileToRenamePath);

But i am curious why did i get an exception while i was trying to rename the file by using the File.Move() method.

Re: File.Move() method

Dave Kreskowiak1-Mar-11 9:39

Dave Kreskowiak

1-Mar-11 9:39

"The file already exists" means that the filename you're trying to rename the file TO already exists, not the file your renaming.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

Re: File.Move() method

Orcun Iyigun1-Mar-11 10:05

Orcun Iyigun

1-Mar-11 10:05

oh yeah you are right, carelessness.

Access asymbly version

jashimu1-Mar-11 3:02

jashimu

1-Mar-11 3:02

Hi All, I need to display my assembly version on about form. I am not quite sure how get it and display on a label. if know please let me know.

thaks,

musefan1-Mar-11 3:15

musefan

1-Mar-11 3:15

Google says...

System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString();

...you can determine the version of your application in the AssemblyInfo.cs file which may be found in the Properties folder of your solution (VS 2010)

EDIT: Slight correction of Google found code

Illogical thoughts make me ill

jashimu1-Mar-11 3:22

jashimu

1-Mar-11 3:22

Thanks for your reply. I already have gotten it, google is the best friends of mine since I started programming. I still don't get it why I am getting asm version
1.0.0.0 where I see is 1.0.7. I have a installer what does it means project and installer will have two different assembly version.

thanks,

musefan1-Mar-11 3:26

musefan

1-Mar-11 3:26

In your solution you will find a file called AssemblyInfo.cs in the properties folder. This file will contain an "AssemblyVersion" property which determines the actual version of the assembly (this will be the value you get if you use my suggested code)

I don't use VS for installers but I imagine the installer version is simply there as a sort of "release version" value - i.e. it is up to you to pick this, and would probably make sense for you to set it the same as your assembly version (this paragraph is speculation as I said I do not use VS installer wizard thingy)

e.g. you may choose to have the following...

Assembly Version = 1.2.3.4
Installer Version = 1.2 (don't need to show the rest is you only ever release minor build changes)

Illogical thoughts make me ill