Strategy of usage SqlConnection in Web-forms application
-
Hi, I would like to ask a general question about ASP.NET and SQL Server. I'm creating an Web-forms application used Microsoft SQL Server and included several ASP.NET-pages. I assume that there are two strategies when I should open a database connection. 1. "Open once during session start" strategy. This means that class System.Web.HttpApplication.Global contains SqlConnection object and the method "Session_Start" contains an invoking of SqlConnection.Open(). If I need to get a pointer to the existed SqlConnection, I will get a pointer to the current instance of class System.Web.HttpApplication.Global. So this method doesn't requires to re-open the database connection on the every page. When current session is finished, the database connection will be closed automatically since object System.Web.HttpApplication.Global will be deleted. 2. "Open and close on the every page" strategy. This means that I don't keep the existed SqlConnection opened. If I need a data from SqlServer (say, to fill DataGrid), I will create a new instance of SqlConnection object each times when it will need. What do you think? What method do you use? Yours sincerely, Alex Bash
-
Hi, I would like to ask a general question about ASP.NET and SQL Server. I'm creating an Web-forms application used Microsoft SQL Server and included several ASP.NET-pages. I assume that there are two strategies when I should open a database connection. 1. "Open once during session start" strategy. This means that class System.Web.HttpApplication.Global contains SqlConnection object and the method "Session_Start" contains an invoking of SqlConnection.Open(). If I need to get a pointer to the existed SqlConnection, I will get a pointer to the current instance of class System.Web.HttpApplication.Global. So this method doesn't requires to re-open the database connection on the every page. When current session is finished, the database connection will be closed automatically since object System.Web.HttpApplication.Global will be deleted. 2. "Open and close on the every page" strategy. This means that I don't keep the existed SqlConnection opened. If I need a data from SqlServer (say, to fill DataGrid), I will create a new instance of SqlConnection object each times when it will need. What do you think? What method do you use? Yours sincerely, Alex Bash
Go for 2. It's a lot easier, and I think ADO.NET connection pools anyway. It's the approach I use, and it seems to work well. -- Ian Darling If I was any more loopy, I'd be infinite.
-
Go for 2. It's a lot easier, and I think ADO.NET connection pools anyway. It's the approach I use, and it seems to work well. -- Ian Darling If I was any more loopy, I'd be infinite.
Same here. I agree with Ian on this...
-
Go for 2. It's a lot easier, and I think ADO.NET connection pools anyway. It's the approach I use, and it seems to work well. -- Ian Darling If I was any more loopy, I'd be infinite.
Yes the connection will be pooled if all parameters are the same. Store the connection string globally (web.config) and you will be set.
-
Go for 2. It's a lot easier, and I think ADO.NET connection pools anyway. It's the approach I use, and it seems to work well. -- Ian Darling If I was any more loopy, I'd be infinite.
I am doing it the same way and it works well but I dont know if it the best way. To be honest though, I haven't ever tried it the other way. It would be nice to compare the 2 methods by doing a stress test on the web application. theJazzyBrain Wise is he who asks good questions, not he who gives good answers
-
Hi, I would like to ask a general question about ASP.NET and SQL Server. I'm creating an Web-forms application used Microsoft SQL Server and included several ASP.NET-pages. I assume that there are two strategies when I should open a database connection. 1. "Open once during session start" strategy. This means that class System.Web.HttpApplication.Global contains SqlConnection object and the method "Session_Start" contains an invoking of SqlConnection.Open(). If I need to get a pointer to the existed SqlConnection, I will get a pointer to the current instance of class System.Web.HttpApplication.Global. So this method doesn't requires to re-open the database connection on the every page. When current session is finished, the database connection will be closed automatically since object System.Web.HttpApplication.Global will be deleted. 2. "Open and close on the every page" strategy. This means that I don't keep the existed SqlConnection opened. If I need a data from SqlServer (say, to fill DataGrid), I will create a new instance of SqlConnection object each times when it will need. What do you think? What method do you use? Yours sincerely, Alex Bash
Bash wrote: What do you think? What method do you use? Yep, #2. There is no reason to have a connection open if there is no business for it. A lot like leaving the door on your house open while you are at work because you do not want to wear out the hinge by opening and closing it all day. It may not seem so bad to have a few connections open. But since you are talking about in the Session, the Session can last quite some time even after the person has left your site. If you wait until the session finally exits and you happen to get hit by a few hundred people, you would have hundreds of open connections just sitting there not doing anything. This breaks scalablity of your web application. If you are only interested in a few visits per hour, there would be nothing hurting on your web application. But it is better to just create and close as needed. Rocky Moore <><
-
Yes the connection will be pooled if all parameters are the same. Store the connection string globally (web.config) and you will be set.
-
I am doing it the same way and it works well but I dont know if it the best way. To be honest though, I haven't ever tried it the other way. It would be nice to compare the 2 methods by doing a stress test on the web application. theJazzyBrain Wise is he who asks good questions, not he who gives good answers
Not needed at all. The first way (opening connection each time you need it) is the best one, since ASP.NET do connection pooling. But always CLOSE(!) your connection at the end. The connection pool works this way: It creates a connection for each unique connection string passed, gives it to you if you create one (
new SqlConnectionor such) and returns the connection to pool ONLY when you close it. If the connection remains open, it is not returned to the pool, thus next time you'll require a database connection, it will be created again ( a new one) Philip Patrick Web-site: www.stpworks.com "Two beer or not two beer?" Shakesbeer