Asp.Net keeping connection to db
-
I am beginner in ASP.Net and have following question: I have a class and inside of this class i load some information from database. It is very slow to connect in each instance of this class to database again.
class DataNode
{
SqlConnection DB;
public DataNode()
{
DB = new SqlConnection("connection string");
DB.Open();
}
public DataNode[] ProcessAllSubNodes()
{
// query to get all nodes
DataNode subNode = new DataNode();
subNode.GetSomeDataFromDB();
}
}I have tried following to avoid it: 1) make SqlConnection DB - static. Problem: if many users connect simultaniosly - i get error that datareader already used somewhere else. 2) to store DB instance in HttpContext.Current.Session and reuse it each time. It has helped, but i am not sure that it is ideal way and has no bad effects. I know about caching objects in HttpContext.Cache, but the first time (when there is no cache) it connects to database so often (because my objects are recursive), that database stops responding and i get DB connection timeout exception :(. So if someone knows a good pattern for my problem, please share :) Thanks.
-
I am beginner in ASP.Net and have following question: I have a class and inside of this class i load some information from database. It is very slow to connect in each instance of this class to database again.
class DataNode
{
SqlConnection DB;
public DataNode()
{
DB = new SqlConnection("connection string");
DB.Open();
}
public DataNode[] ProcessAllSubNodes()
{
// query to get all nodes
DataNode subNode = new DataNode();
subNode.GetSomeDataFromDB();
}
}I have tried following to avoid it: 1) make SqlConnection DB - static. Problem: if many users connect simultaniosly - i get error that datareader already used somewhere else. 2) to store DB instance in HttpContext.Current.Session and reuse it each time. It has helped, but i am not sure that it is ideal way and has no bad effects. I know about caching objects in HttpContext.Cache, but the first time (when there is no cache) it connects to database so often (because my objects are recursive), that database stops responding and i get DB connection timeout exception :(. So if someone knows a good pattern for my problem, please share :) Thanks.
The rule of thumb for database access is to open the connection as late as possible and close it as soon as possible. The slowness may simply be caused by the connection to your database, say if it is located on another server. If the data is static, i.e. doesn't change often, then caching it may be an option, depending on the amount of data. There are an enormous amount of examples of constructing data access layers, I would suggest you try searching for one as an example.
only two letters away from being an asset