Database Connection Management Question

[Nick N.]

Hi all,

I just started to play around with VB.Net.

I currenlty do Powerbuilder work and was wondering how ADO.net database
connection
management works in VB.net. (I've done some reading on ADO.net but could not
find what I was looking for)

My applications typically pop-up a login window, attempts to connect to the
database (Sybase or MSSQL). Once the connection is established, it will open
a MDI Frame and the application will do whatever it is meant to do. (Mostly
reporting by SQL Query...in PB there is something called the Datawindow)

In Powerbuilder, there is a "global" variable called SQLCA which holds the
connection to the database and is accessible from any point in the
application given its global nature.

For instance, to connect to the database, I will set properties for the
SQLCA object then issue the following command:

connect using SQLCA;

then wherever I am in the application, I can reference the SQLCA object for
a valid database connection.

How is something like this done in VB.Net.
I managed to create a login window. I have the connection logic in the
clicked event of the OK button and it does connect to MS SQLServer 2K. Do I
have to then Pass the connection object to the MDI frame and other child
windows etc?

Or is there a cleaner way to do this?
I don't know if declaring a global oledbconnection object is the correct
thing to do.

Please advise!
Thanks
Nick

 

[Miha Markic]

Hi Nick,

You should create connection objects when needed.
And get rid of them or at least close them ASAP.
Since ADO.NET uses connection pooling there is no performance hit on the
client.

 

[Mike]

Nick,

Just out curiousity how is Power Builder? How do you compare it to
mainstream lagnuages? Also, I was wondering what you thought of Firebird
the open source db of Sybase?

Thanks

 

[Nick N.]

I see...

Just curious though...when you are navigating through different windows,
screens, etc...how do you keep the userid, password, connection string, etc.
following you? For instance, after the login window, do I disconnect from
the database? Then when I need something from the db, like to populate a
tree view, do I reconnect? Then disconnect until I need something else like
a report in another window?

Do you have a class that stores that information so that when you need to
connect, you can do so right then and there?

Sorry about these beginner's questions.
I've been doing PB for so long and I'm so used to doing things 1 way.

Thanks!
Nick N.

 

[Nick N.]

I think Powerbuilder is great for what it is meant to do...which is
developing database centric applications very quickly.

The single most powerful object is the Datawindow and I still do not see an
equivalent to that in any other language.

PB have been a fully Object Oriented environment...long before VB/VB.Net.
It's really a no non-sense IDE...just get the job done.

They are also keeping up with the latest technologies like web services,
xml, etc...and they are even talking about being able to deploy to .Net.
Interesting.

But, the market is shrinking...well already shrunk...so career wise, it is
very questionable at best.

Hence I am currently reading up on .Net (I've done some mini VB.Net projects
at my other job a few years back). I'm trying to learn how things are done
in the Client/Server environment.

I don't know anything about Firebird.
I do like Sybase...more so than Oracle...it's beauty is in its
simplicity...unlike Oracle.

 

[Miha Markic]

I see...

Just curious though...when you are navigating through different windows,
screens, etc...how do you keep the userid, password, connection string, etc.
following you? For instance, after the login window, do I disconnect from
the database? Then when I need something from the db, like to populate a
tree view, do I reconnect? Then disconnect until I need something else like
a report in another window?

Yes, this is what connection string is for.
You might store it in a static variable (C#) - (a global variable).
And yes, you connect when need and then disconnect.
You might check http://www.connectionstrings.com/ on how to build a
connection string.

Do you have a class that stores that information so that when you need to
connect, you can do so right then and there?

You just create a new conneciton object. For example, if you are dealing
with SqlServer:
SqlConnection conn = new SqlConnection(connectionString);

Sorry about these beginner's questions.
I've been doing PB for so long and I'm so used to doing things 1 way.

Yes, I understand that. .NET is a switch from some things we were used to do

 

[Jeff Nesler]

I think PB is slated to release a .Net version of the datawindow/datastore
with version 10, sometime next year. I think public beta for this will be
starting soon. A .Net compiler is scheduled for PB version 11. I'm anxious
for this because I'm in the same situation, a PB developer getting started
with .Net.

 

[Nick N.]

Jeff,

It's great to meet someone in the same situation.
2 years ago the company I was working for merged and I had to find another
job, which I did within a few weeks. (paycut and all!!!)

They used to be a C++ shop, then a Sybase/PB shop. Before the merger there
were talks about moving over to Microsoft products b/c at that time Sybase
was going through the sewers and the company didn't trust them any more...it
was going to be Oracle/MSSQL + VB and eventually .Net. So I worked on a
VB.Net project...something to do with Financial market data etc...showing
prices in a GUI app. Anyways, I had a lot of fun doing that...that was 2001.
As soon as I found out about the merger, I was trying to make a decision
whether to continue PB/Sybase, do the Java thing having gone through a few
mini-projects there, or .Net...

I needed to find a job quick so I continued the path of PB/Sybase and that's
what I am doing right now. We still use PB6.5 and I feel very tired of this.
So I'm trying to play around with VB.Net "secretly", and without
permission...I have MSDE and VS.net loaded up...I got the upgrade to VS.Net
2003 directly frmo Microsoft.

The problem is that the current job already mentioned that they will not
embrace .Net...this is a Java shop...BEA Weblogic, JBuilder...all that. I'm
not sure I'm into Java as much as I was a few years ago. I like .Net...and I
don't have this particular hate for MS that many people seem to have.

Call it a dilemma...not sure what the future holds for me!

PB10 does sound exciting...not that I will be using any of it's exciting
features...heck, I'm still waiting for us to get to PB8!

 

[Jeff]

Nick,

I'd say there are a quite a few people in our situation from looking at the
PB newsgroups. Hopefully, some of them will hang around here. It would be
good to have someone to compare notes with, sort of like you did in your
question, comparing SQLCA to the connectionobject. Seems kinda silly these
days to tie yourself to 1 programming tool, even if it is a good one like
PB. Our company was merged 3 years ago. Previously, we were a total
PB/Sybase shop. The company that bought us, no one in their IT department
had ever even heard of PB, they were mainly COBOL but some VB. Management
decided last year that .Net and MSSql Server was the direction they wanted
to go for the future. We still have all our main systems written in PB
(we've migrated our parent company's accounts from their COBOL system to
ours), but we're beginning to rewrite one of our apps in VB.Net. I think
the .Net stuff is kinda cool, so far. We have one of our apps in PB5 (some
proprietary framework that we can't figure out how to migrate), everything
else in PB9 and soon, PB10. You're still on PB6.5, eh? I'd talk to the
Sybase folks about their update subscription plan, they'll probably cut you
a good deal to upgrade. I'm anxious for the .Net datawindow/datastore and
the .Net Compiler. Seems like in the next couple of years, we'll be able
get the best of both worlds, using PB for the stuff it does best and .Net
for what it does best.

Good luck,
Jeff

 

[Nick N.]

Sounds like you lucked out in some ways being able to stay there and make a
pretty good transition from PB to .Net or doing a combination of both.

That wasn't the case for me.
They offered me a job doing MS Office support or a lot of money to quit...I
took the money and put a down payment on a house...that worked out well, but
now I'm stuck in this joint doing PB6.5.

We do have PB8 and 9 via usp, but we haven't had the time to convert
things...time constraints, politics, you name it...

They already made it clear they are not doing .net so I'm kind of scr*wed
since PB developers are expected to do just that...develop PB apps. They
really kind of give you the dirty eye when you try to do stuff outside your
expertise, even though it may be programming...so I do VB.Net is relative
secrecy although I know someone's ALWAYS watching here!

They used scare tactics to get people to obey...so I can't be reading a VB
book or a MS SQLServer book even though they use the same SQL standard,
T-SQL. I feel like I'm in jail!

Maybe .net support in PB10 will alter their state of mind some...but them
Java folks are aweful stubborn and are very defensive about "their" java.

 

[Jeff]

Yikes! Doesn't sound like too much fun. Yeah, I did luck out. We were
taken over by a much better company in most ways. The one drawback is that
their idea of training is "Buy a book and learn it yourself". We have some
guys that have been doing .Net stuff for a year or so, so I can get help
from them when I really need it. But no one else in the entire company
knows PB, and that is what all our enterprise apps are written in, so wahoo
for me!

 

[Nick N.]

Hey that's a great gig you got going over there!
It's great to be the "one"...it's tough but at least you have a lot of job
security!
....and you get to do .net stuff...man I'm jealous!

I don't know how I'll be able to switch...unless I find just the right place
where they are "phasing out" PB...but that'd be kinda hard to find.

It's too bad my old company merged. They were talks about moving over to
MSSQLServer and VB.net would have been a perfect platform...

It's not so bad, at least not so much worse than other places.
It's just that I've been unable tojump off the PB sinking ship...there are
many things at play here...money, mortgage, learning vb.net...how to market
myself I have no idea!

Hey, thanks for listening...I think I needed to vent some!