WHERE to open connection object
-
You shouldn't be writing database access code in the Page_Load function (or anywhere in that class) at all. You should be creating a class (or set of classes if your application is big) to handle the database. Keep the data layer separate from the logic and presentation layers. You can create a class that has the appropriate methods for accessing the database, at the start you open a connection, do the processing and then close the connection. It is better to close the connection in a
finallyblock to ensure the connection is closed in case an error happens. Here is a small sample class to show what I meanclass DataAccessLayer
{
private SqlConnection Connection
{
get
{
string connectionString = ConfigurationSettings.AppSetting["ConnectionString"];
SqlConnection connection = new SqlConnection(connectionString);
return connection;
}
}public void UpdateData(int someKey, string someValue) { SqlConnection conn = this.Connection; try { conn.Open(); // Code that performs the query is here } finally { // Ensure that the connection is always closed, even if an error happens conn.Close(); } }}
Does this help?
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
Tip: I use a using block instead of try ... finally. As the using block always disposes of the object, and as disposing a connection also closes it, the code gets really neat:
public void UpdateData(int someKey, string someValue) { SqlConnection conn; using (conn = new SqlConnection(ConfigurationSettings.AppSetting["ConnectionString"])) { conn.Open(); // Code that performs the query is here } }--- b { font-weight: normal; } -
Tip: I use a using block instead of try ... finally. As the using block always disposes of the object, and as disposing a connection also closes it, the code gets really neat:
public void UpdateData(int someKey, string someValue) { SqlConnection conn; using (conn = new SqlConnection(ConfigurationSettings.AppSetting["ConnectionString"])) { conn.Open(); // Code that performs the query is here } }--- b { font-weight: normal; }Sure, that would be a good solution also.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
Sure, that would be a good solution also.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
thanks for all the suggestions. how about if I defined this connection string as a public object, just above Page_load function. Public myconn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("OnlineToolkitv2")) And with page_load all I am doing is "myConn.Open" - not performing any database operations. In a whole bunch of function I am pretty much using sp(stored procedure) - which is pretty much selects and updates statements. And in the end of the functions that is being performed in the last one, closing the connection oject and disposing it. Is this ok? Thanks. Vani
-
thanks for all the suggestions. how about if I defined this connection string as a public object, just above Page_load function. Public myconn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("OnlineToolkitv2")) And with page_load all I am doing is "myConn.Open" - not performing any database operations. In a whole bunch of function I am pretty much using sp(stored procedure) - which is pretty much selects and updates statements. And in the end of the functions that is being performed in the last one, closing the connection oject and disposing it. Is this ok? Thanks. Vani
VK-Link wrote: Is this ok? Not really - It sounds pretty ugly to me. But, so long as I don't have to maintain the code what ever floats your boat, I suppose. Okay, being a bit more serious: 1. public fields (aka member variables) are a real no-no. They completely blow all concepts of encapsulation out of the water. You don't explain why you want it public anyway, you don't say where you are using this outside the class it is defined in. 2. myConn.Open() is a database operation - you are opening a connection to the database. Basically, anything involving classes in System.Data.SqlClient should be kept out of the presentation layer (i.e. the class where your Page_Load is) You should not be performing database operations in the presentation layer. Period! I know .NET provides lots of functions for you to do this. You can drag all sorts of goodies onto your form, but they are a really bad idea that were implemented to sell .NET to people who can't design software. 3. You should, as I've already explained, create a DAL class (or classes) to hold the database code. That class opens the connection and closes it and pretty much manages everything to do with the database.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
VK-Link wrote: Is this ok? Not really - It sounds pretty ugly to me. But, so long as I don't have to maintain the code what ever floats your boat, I suppose. Okay, being a bit more serious: 1. public fields (aka member variables) are a real no-no. They completely blow all concepts of encapsulation out of the water. You don't explain why you want it public anyway, you don't say where you are using this outside the class it is defined in. 2. myConn.Open() is a database operation - you are opening a connection to the database. Basically, anything involving classes in System.Data.SqlClient should be kept out of the presentation layer (i.e. the class where your Page_Load is) You should not be performing database operations in the presentation layer. Period! I know .NET provides lots of functions for you to do this. You can drag all sorts of goodies onto your form, but they are a really bad idea that were implemented to sell .NET to people who can't design software. 3. You should, as I've already explained, create a DAL class (or classes) to hold the database code. That class opens the connection and closes it and pretty much manages everything to do with the database.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
We are building an application using ASP.NET. In this I have defined multiple panels, and each panel does certain database functions. Like panel 1, has a select statement, panel 2 hs update statement and panel 3 has some results, calculations based on information entered in the previous panels. My question - do we have to perform a open and close for each panels? I was hoping to write connection.open in page_load and using this connection for the selects and updates. Thanks.
-
We are building an application using ASP.NET. In this I have defined multiple panels, and each panel does certain database functions. Like panel 1, has a select statement, panel 2 hs update statement and panel 3 has some results, calculations based on information entered in the previous panels. My question - do we have to perform a open and close for each panels? I was hoping to write connection.open in page_load and using this connection for the selects and updates. Thanks.
VK-Link wrote: I was hoping to write connection.open in page_load and using this connection for the selects and updates. It really doesn't matter - The connection is pooled anyway. Open and Close it for each operation if you want, the overhead is negligable. Just don't put the database code in the presentation layer because you'll regret it when you come to maintain your application. See my first response to you for a small example of a DAL class. If you want to open once and close at the end have your DAL (Data Abstraction Layer) class do that. Do not do this in GUI code. Do you understand what I am trying to say?
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
VK-Link wrote: I was hoping to write connection.open in page_load and using this connection for the selects and updates. It really doesn't matter - The connection is pooled anyway. Open and Close it for each operation if you want, the overhead is negligable. Just don't put the database code in the presentation layer because you'll regret it when you come to maintain your application. See my first response to you for a small example of a DAL class. If you want to open once and close at the end have your DAL (Data Abstraction Layer) class do that. Do not do this in GUI code. Do you understand what I am trying to say?
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
I think there is a communication block here - say what he wants to hear. I agree with the DAL approach - you should do all of you data access in another class and it is not difficult to do that with the different panels - create one DAL class with 3 functions in it - one to do the select, one to update, and one to return results or whatever. In each of those functions do a .open and .close. Cleako
-
I think there is a communication block here - say what he wants to hear. I agree with the DAL approach - you should do all of you data access in another class and it is not difficult to do that with the different panels - create one DAL class with 3 functions in it - one to do the select, one to update, and one to return results or whatever. In each of those functions do a .open and .close. Cleako
cleako wrote: say what he wants to hear Maybe. But I don't like giving the answer to the question they asked if that answer will lead them to pick up bad habits. I am hoping that some people learn good habits early on and don't have to waste the amount of time that I did early on in my carear by having to maintain awful applications.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
cleako wrote: say what he wants to hear Maybe. But I don't like giving the answer to the question they asked if that answer will lead them to pick up bad habits. I am hoping that some people learn good habits early on and don't have to waste the amount of time that I did early on in my carear by having to maintain awful applications.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
I agree totally with teaching and helping but when you tell the person 4 times the exact same thing and they reply with the exact same reply I dont know - I would say do whatever you want then - but maybe Im just not as patient. Im fairly new to a lot of these things myself but I try to be open to suggestions and try to understand the code I copy or modify before I use it. There is a post from a guy on another forum where the poster wants someone to do his programming homework for him b/c he doesnt understand it and claims that it is ok b/c the department is corrupt and says the same thing no matter what the person replying says. Ill tell you that one of the hardest concepts to grasp is the object-oriented idea - some people and myself included when I was just starting to learn this in school - feel much more comfortable writing straight code in a giant class. It took me some real hard thinking to understand object-oriented so until someone feels comfortable with OO then they will never feel comfortable with a DAL. You are correct though - I just read through the thread and saw him reply the same way 4 times and thought it was ridiculous. Cleako
-
I agree totally with teaching and helping but when you tell the person 4 times the exact same thing and they reply with the exact same reply I dont know - I would say do whatever you want then - but maybe Im just not as patient. Im fairly new to a lot of these things myself but I try to be open to suggestions and try to understand the code I copy or modify before I use it. There is a post from a guy on another forum where the poster wants someone to do his programming homework for him b/c he doesnt understand it and claims that it is ok b/c the department is corrupt and says the same thing no matter what the person replying says. Ill tell you that one of the hardest concepts to grasp is the object-oriented idea - some people and myself included when I was just starting to learn this in school - feel much more comfortable writing straight code in a giant class. It took me some real hard thinking to understand object-oriented so until someone feels comfortable with OO then they will never feel comfortable with a DAL. You are correct though - I just read through the thread and saw him reply the same way 4 times and thought it was ridiculous. Cleako
cleako wrote: Ill tell you that one of the hardest concepts to grasp is the object-oriented idea I totally agree with that. Many years ago I read "Design Patterns" and I realised that I was already using a few but not realising it. Most of the other patterns in the book, at the time, went over the top of my head. I learn best with an explantaion of an example that I can apply in the real world - The examples here were mostly academic in nature. Anyway, recently I read Head First Design Pattens (which gives Java examples - but I can read Java as it is close to C# and the examples are easy to understand). That is a fantastic book. Some of it was a bit weird, but if you ignore the weird stuff it really explains everything in a very easy to understand way, giving examples with lots of explanations, and even crosswords to fill in at the end of each chapter (which, for me, is a bit weird)
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More