[RESOLVED] Connection.open - is it a must?

[ovi_gm]

Hello,

I have this app with database and I have a question regarding the connection. Please note that I am a beginner and this is my first database app.
I created my db in SQL Management Studio (I am using 2008 R2). I am using the next method to connect to my db:

Dim con As New SqlConnection
Public db As New nameDataContext(con)

con.ConnectionString = "Data Source=.\INSTANCE;Database=dbName;Integrated Security=True;User Instance=False"
con.Open()

And it works all ok. But then I found the app.config file and I entered my connection string there and I noticed that my app works fine without using the con.open() command. I can delete the whole con variable and it still works. I am using this code to write to the database:

Dim newRec As New tableName With {.......}

db.tableNames.InsertOnSubmit(newRec)
db.SubmitChanges()

First I created my DB inside vb but then I had some problems and I created it again in Management Studio and it worked fine.

Now my question is. Do I really need the con.open() command and if so, why?

If I have to use it I was thinking to read the connection string directly from the app.config file and so I can change the string depending on the computer and network I am connecting to.

[FunkyDexter]

Do I really need the con.open() command and if so, why?

Yes. It opens the connection. Without it you would have a connection but you couldn't communicate across it because it was closed. Think of the connection as a door - even though the door exists you can't go through it without opening it first.

As for why your app continue to work even if you don't open the connection... that's a mystery. I suspect the connection is actually still being opened elsewhere in your code or calls to db are actually using a different connection.

[ovi_gm]

Hm. I'll check the code and see where it happens and come back... When should I use the con.close() command?

[KGComputers]

Based from MSDN,
You might wanna try implementing "Using" on SQL connections. Since the connection is automatically closed at the end of the "USING" block.
SQL connection Close()

KG

[techgnome]

It depends... there are some actions that will automatically open and close the connection for you... such as when you call the get or fill method of typed datasets/datatables... the adaptor will check the connection and open it if it needs to. I think .ExecuteReader will also do the same thing, but I don't recall. As a matter of habit, and since I rarely use typed sets, I always open and close the connections manually.

As for the close... if you opened it, close it. Even if you didn't open it, if you used it, close it. Again, this is one of the reasons I do both manually, that way it's clear when it is open and when its not, and it's also explicitly clear that it is being taken care of (in case something needs to be added to the process).

-tg

[ovi_gm]

Thanks techgnome for your answer. So, from what i understand, your suggestion is to use something like this:

[CODE]
Dim newRec As New tableName With {.......}

Con.open()
db.tableNames.InsertOnSubmit(newRec)
db.SubmitChanges()
Con.close()

[CODE]

Also, every before and after filling a datagridview or deleting or modifying rows, i should open and close my connection...

Why can't I let it opened and just close it after exit? I am asking this just to learn more about the mechanism behind sql database.

One more question. You said you rarely use typed sets? What does that mean? Is there a simpler way to work with db?

Thank you very much for your support. I appreciate it.

[FunkyDexter]

something like this

Yes, that's exactly right.

Why can't I let it opened and just close it after exit?

You can... but you shouldn't. An open connection is using up resource on the database server and you want to free them up as quickly as possible.

You said you rarely use typed sets? What does that mean? Is there a simpler way to work with db?

There are other ways but they're not necessarily simpler. It's largely a matter of taste and what you're trying to achieve in the moment.

You can, for example, simply issue a query using DataAdapter.Fill and work with the dataset that comes back. It doesn't need to conform to any particular schema.

In fact, that's all a typed dataset really is, a schema you expect your dataset to conform to. It's handy because, once you've defined that schema, the ide can infer from it to give you intellisense, type checking etc. but it can also be a bit restrictive and they do tend to clutter up the toolbox.

I'm with TG on this, I rarely use them. But only because I'm not in the habit, not because they're in any way bad.

[techgnome]

yeah... usually in my case it's actually faster for me to create a query, then use it to fill a generic datatable with my data... I don't have to futz around with objects, worrying about changes to the schema (fields being added/deleted). They do have their places and they are handy, I worked in a shop where they were used exclusively... and for what we were doing, it was very handy ... they just don't happen to be the first thing I reach for. And now with EF (which I still have yet the opportunity to really try out) the need for typed datasets/tables becomes even further obscured. It's really a style and standards issue. Under the hood, typed datasetsare really nothing more than classes which implement data interfaces inhereited from datatables and datasets, and expose the fields as properties of the datarow... that's all... it hides some of the nuts and bolts of things. The intent was to make it easier/faster to just write code with objects rather than having to worry about database details... I'm a bit old school in that regard, and have trouble giving up control of my data, and I grew up through the ranks by hand coding things like that, so this old dog just keeps chasing those same cars.


-tg

[ovi_gm]

Ok. I got it now. Well I really appreciate your help, thx a lot. I will make some changes now to my code accordingly and see how it works. My app is almost done, I just have to make it work ok over a network of computers. I'll come back if I'll have other questions.

Cheers!

[ovi_gm]

One more quick question. As I understood, it is possible that certain operations over the database could not accomplished right away. So as I read on msdn, they say: "The Close method rolls back any pending transactions. It then releases the connection to the connection pool, or closes the connection if connection pooling is disabled."

So how about those pending transactions? Is it possible that after a user inserts some data and I after I submitchanges and close the connection the data isn't written anymore?

Or is this


Dim newRec As New tableName With {.......}

Con.open()
db.tableNames.InsertOnSubmit(newRec)
db.SubmitChanges()
Con.close()


100% safe

[techgnome]

Only if you're using transactions... be it in code or in the SQL... if you BEGIN TRANSACTION in your SQL, insert some data, then close hte connection you get an implied rollback...

-tg

[ovi_gm]

Hello,

I have to come back with a question although I marked it resolved. As Funky Dexter said it's a mystery how it works. Well I want to ask you where should I look and see why it works? The fact is that I am not opening any connection anywhere and it seems to work just fine. I want to know why because I need to follow techgnome advice into opening/closing every time because it makes more sense.

Now, the only place I am having a connection string is in the app.config file and it looks like this:

"Data Source=.\PMA;Database=dbname;Integrated Security=True;User Instance=False"

Then I declared this:

Public db As New databaseDataContext()

I have a dbml and a xsd files also available.

Now my first operation with the database is as follows (I inserted a message box to see the connection state of the database):

MsgBox(db.Connection.State)
Dim newRecord As New tableName With {.....values.......}
MsgBox(db.Connection.State)
db.tableNames.InsertOnSubmit(newRec)
MsgBox(db.Connection.State)
db.SubmitChanges()
MsgBox(db.Connection.State)

for each message box I get "0" which I suppose means connection state = closed, but the data is written to the database.

Now please explain me what should I look for. where could my connection be automatically opened / closed? Shouldn't it not work?

[techgnome]

code like you posted tells me you're using a typed dataset... you made a connection in the server explorer, and dragged tables/views into your project... as a result, the templates/designer took care of that code for opening and closing... that's why you don't see it. It's happening behind the scenes... presumably based on what you're saying about the state, the SubmitChanges maybe opening and closing the connection for you. I don't use this method anymore so I can't say with any absolute certainty...

-tg

[ovi_gm]

Well, then I'll leave it like this. It seems to work very well. I get no errors until now. Thank you very much for your support.

Cheers!