We are using NHibernate with OpenSessionInView pattern for our AspNet webapp.
Using ADO connection (SqlServer) we want to log in a different database every acces to pages. For that, do we need to open a connection at every "page load", execute the insert, then close the connection, or can we keep the same connection shared among all requests?
What about locks and concurrent access? We do only insert on this database.
Yes, I'd go with open --> insert --> close. The reason being that SQL Connections -and most DB connections, depending on the driver- are pooled so opening a new connection really implies getting a connection from the pool, which is inexpensive (unless you are running out of connections in the pool). If on the other hand you hold on to an open connection, you'll end up with a TON of concurrency issues since you'll have to synchronize the access to this connection object for every request. A nightmare, in other words. In fact, you'll be blocking your request and slowing things down considerably.
Again, you are not really improving the performance -quite the contrary- and you are complicating your app.
