SqlConnection: 1 long or many short...

[Danny]

Hi,

I was wondering if it is better/worse to have 1 SqlConnection as a
public object and initalized after i.e. the login to the application,
or to reconnect each time before some database action is needed in a
piece of code, and of course to deconnect directly after having
executed the sql-statement.


I imagine that having the connection setup at the start of the
application would consume a license on the sql-server for the life-
time of the application, where disconnecting/reconnection at each
sql-
task would limit this.


But, if licences aren't an issue, would there be other pro's/con's to
have/not have the connection setup at the start and use it for all
action within the application?


Points to considder:
- transactions
- performance
- licences
- good practice
- logging of ...
- ...


Thanks

 

[sloan]

The DAAB and the EnterpriseLibrary.Data are build on the premise of:

Instantiate LATE
Use QUICKLY
Close AS SOON AS POSSIBLE.

I think that makes the most sense.

...............

In fact, I would suggest going to the EnterpriseLibrary.Data because it has
so very many "best practices" built into it already.
And you avoid reinventing the wheel.

That's one opinion. Hopefully you get some more.


http://www.google.com/search?hl=en&q=enterpriselibrary+download&aq=1&oq=enterpriselibrary+dow


Check this out:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!176.entry

esp the CustomerSqlServerData class as well, as an example of how "clean"
your code can be using the EnterpriseLibrary.


public override IDataReader CustomersGetAllReader()
{
IDataReader returnReader = null;
try
{
Database db = this.GetDatabase();// simple encapsulated
method to return a Microsoft.Practices.EnterpriseLibrary.Data.Database
object
DbCommand dbc =
db.GetStoredProcCommand(this.PROC_CUSTOMERS_GET_ALL); //<< constant value
for "dbo.uspCustomerGetAll"
returnReader = db.ExecuteReader(dbc);
return returnReader;
}
finally
{
}
}

Very clean.

 

[Danny]

The DAAB and the EnterpriseLibrary.Data are build on the premise of:

Instantiate LATE
Use QUICKLY
Close AS SOON AS POSSIBLE.

I think that makes the most sense.

..............

In fact, I would suggest going to the EnterpriseLibrary.Data because it has
so verymany"best practices" built into it already.
And you avoid reinventing the wheel.

That's one opinion.  Hopefully you get some more.

http://www.google.com/search?hl=en&q=enterpriselibrary+download&aq=1&...

Check this out:http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!176.entry

esp the CustomerSqlServerData class as well, as an example of how "clean"
your code can be using the EnterpriseLibrary.

        public override IDataReader CustomersGetAllReader()
        {
            IDataReader returnReader = null;
            try
            {
                Database db = this.GetDatabase();// simple encapsulated
method to return a Microsoft.Practices.EnterpriseLibrary.Data.Database
object
                DbCommand dbc =
db.GetStoredProcCommand(this.PROC_CUSTOMERS_GET_ALL); //<< constant value
for "dbo.uspCustomerGetAll"
                returnReader = db.ExecuteReader(dbc);
                return returnReader;
            }
            finally
            {
            }
        }

Very clean.











- Tekst uit oorspronkelijk bericht weergeven -

If I understand well, this sample code limits the time the datareader
is busy with the connection, but the connection/database object used
is a copy of the probably already instanciated object somewhere else.
I describe again wit a small sample:

module Main

public myConn as new SqlConnection("Database=.......")

myConn.open()

end module

Private Sub LoadData()

Dim sqlCmd As SqlCommand
Dim lvwX As ListViewItem, i As Integer

sqlCmd = New SqlCommand("Select * from contacts", myConn)
Dim dr As SqlDataReader = sqlCmd.ExecuteReader()
While dr.Read
i += 1
lvwX = lvwContacts.Items.Add(i)
lvwX.Tag = dr("ID").ToString
lvwX.SubItems.Add(dr("Name").ToString)
lvwX.SubItems.Add(dr("Address").ToString)
lvwX.SubItems.Add(dr("Zip").ToString)
lvwX.SubItems.Add(dr("City").ToString)
lvwX.SubItems.Add(dr("Country").ToString)
lvwX.SubItems.Add(dr("Tel").ToString)
End While
dr.Close()

lvwContacts.AutoResizeColumns
(ColumnHeaderAutoResizeStyle.HeaderSize)

End Sub

 

[sloan]

As a followup, you can look at connection pooling as well.

The
Instantiate LATE
Use QUICKLY
Close AS SOON AS POSSIBLE.

can return a connection object back to the "pool", so its kinda closed, but
its still available for quick-getting.

Here is a config file connection string entry:


<add name="MyInstanceName"
connectionString="server=MySERVER;database=Northwind;User
ID=nwuser;password=nwpassword;Pooling=true;Min Pool Size=2;Max Pool
Size=225;Application Name='Northwind Client1'"
providerName="System.Data.SqlClient"/>

Don't use "use it", go and research it a little bit.

 

[sloan]

Kinda:

Issue #1
cmd.ExecuteReader(CommandBehavior.CloseConnection)

Issue #2
You are mixing your Presentation and DataLayer code into one method. :<
The objects ListViewItem and DataReader should never appear in the same
method.

Issue #3
You are not using a finally block with your code. If your code raises an
exception, the IDataReader.Close method will NOT run, and thus you orphan
the connection.
(Imagine there is no Address column in the DataReader, what would happen?
(dr("Address") ...pretend it doesn't exist)

Advice #1. Adopt the EnterpriseLibrary.Data. Most of the best practices is
coded up for you already.
Advice #2 Layer your code correctly. Use the example I gave you (the blog
entry with CustomerSqlServerData) to learn layered development.

The DAAB and the EnterpriseLibrary.Data are build on the premise of:

Instantiate LATE
Use QUICKLY
Close AS SOON AS POSSIBLE.

I think that makes the most sense.

..............

In fact, I would suggest going to the EnterpriseLibrary.Data because it
has
so verymany"best practices" built into it already.
And you avoid reinventing the wheel.

That's one opinion. Hopefully you get some more.

http://www.google.com/search?hl=en&q=enterpriselibrary+download&aq=1&...

Check this
out:http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!176.entry

esp the CustomerSqlServerData class as well, as an example of how "clean"
your code can be using the EnterpriseLibrary.

public override IDataReader CustomersGetAllReader()
{
IDataReader returnReader = null;
try
{
Database db = this.GetDatabase();// simple encapsulated
method to return a Microsoft.Practices.EnterpriseLibrary.Data.Database
object
DbCommand dbc =
db.GetStoredProcCommand(this.PROC_CUSTOMERS_GET_ALL); //<< constant value
for "dbo.uspCustomerGetAll"
returnReader = db.ExecuteReader(dbc);
return returnReader;
}
finally
{
}
}

Very clean.











- Tekst uit oorspronkelijk bericht weergeven -

If I understand well, this sample code limits the time the datareader
is busy with the connection, but the connection/database object used
is a copy of the probably already instanciated object somewhere else.
I describe again wit a small sample:

module Main

public myConn as new SqlConnection("Database=.......")

myConn.open()

end module

Private Sub LoadData()

Dim sqlCmd As SqlCommand
Dim lvwX As ListViewItem, i As Integer

sqlCmd = New SqlCommand("Select * from contacts", myConn)
Dim dr As SqlDataReader = sqlCmd.ExecuteReader()
While dr.Read
i += 1
lvwX = lvwContacts.Items.Add(i)
lvwX.Tag = dr("ID").ToString
lvwX.SubItems.Add(dr("Name").ToString)
lvwX.SubItems.Add(dr("Address").ToString)
lvwX.SubItems.Add(dr("Zip").ToString)
lvwX.SubItems.Add(dr("City").ToString)
lvwX.SubItems.Add(dr("Country").ToString)
lvwX.SubItems.Add(dr("Tel").ToString)
End While
dr.Close()

lvwContacts.AutoResizeColumns
(ColumnHeaderAutoResizeStyle.HeaderSize)

End Sub

 

[William Vaughn \(MVP\)]

We've discussed this several times so be sure to check the archives.
The answer is, "it depends". While ASP.NET and Web Services architectures
depend on the connection pool and connect, query, close connection
management, Windows Forms (client/server) applications do not. That is, it's
often conducive to hold a connection for the lifetime of the application.
This permits intimate management of the server state, server-side cursors
and other #temp functionality. I discuss this strategy in great detail in my
book and in the articles on connecting published on my blog.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com/blog/billva
http://betav.com
____________________________________________________________________________________________

 

[Danny]

We've discussed this several times so be sure to check the archives.
The answer is, "it depends". While ASP.NET and Web Services architectures
depend on the connection pool and connect, query, close connection
management, Windows Forms (client/server) applications do not. That is, it's
often conducive to hold a connection for the lifetime of the application.
This permits intimate management of the server state, server-side cursors
and other #temp functionality. I discuss this strategy in great detail inmy
book and in the articles on connecting published on my blog.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205  (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)http://betav.com/blog/billvahttp://betav.com
___________________________________________________________________________­_________________











- Tekst uit oorspronkelijk bericht weergeven -

I have the impression tha Sloane was/is more thinking in terms of
'disconnected' (web) where I was clearly talking about a client/server
configuration, but apparently only 'clearly' to me, my fault.
So having established this, I suppose that my stating of the fact from
the client/server configuration point of view wasn't that far off:
there may be a little performance difference, but other elements may
be more decicive in chosing between 'reconnecting' or 'keeping the
connection' between calls to the database.
The issues Sloane mentioned of course are to be taken into account,
but I wrote a small snippet just to show the situation, not to
document my incompetence....;-)

Thanks

Case Closed