db connection pooling

Topics: Data Access Application Block
Jun 30, 2007 at 12:47 AM
hi there... i just want to know if i'm connection to db correctly.
i'm using DAAB v2 and in my class library project i have a created class called Common.cs and in that file i'm using something like this.

public class Common
{
DataSet ds = null; //new DataSet();
Database db = DatabaseFactory.CreateDatabase("ConnString");

public DataPersonalPage_Select( )
{
DbCommand dbCommand = db.GetStoredProcCommand("sp_datapersonal");
ds = db.ExecuteDataSet(dbCommand);
return ds;
}

public DataSet PendingbyDate()
{
DbCommand dbCommand = db.GetStoredProcCommand("PendingbyDate");
db.AddInParameter(dbCommand, "searchDate", DbType.DateTime, SelectedDate);
ds = db.ExecuteDataSet(dbCommand);
return ds;
}

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

i have lot of other classes which i'm doing the same thing... i declare "Database db = DatabaseFactory.CreateDatabase("ConnString");" at the top of the class

so my question is, am i'm doing correct?

thanks.
Jul 2, 2007 at 3:55 PM
Hi,

Your code and your question don't seem to be related to proper connection pooling. Is there something missing?

About your code, it should be fine assuming you fix the errors (like the missing return value for "DataPersonalPage_Select()" and the unknown variable "SelectedDate" on "PendingbyDate() "). There are simpler overloads you can use though; you can invoke Database.ExecuteDataSet(string storedProcedureName, params object[] parameterValues) and let the DAAB take care of the command and parameter handling for you. You should not use an instance variable for method temp variables (like the "ds" variable).

Regarding your question, you can manage the Database instances like you are describing. Be careful however and try to avoid spreading the knowledge about the connection string name across different classes as it would hurt maintenance; try to keep it on a single location, or better yet rely on the DAAB's default connection if it works for you.

Hope this helps,
Fernando


nisarkhan wrote:
hi there... i just want to know if i'm connection to db correctly.
i'm using DAAB v2 and in my class library project i have a created class called Common.cs and in that file i'm using something like this.

public class Common
{
DataSet ds = null; //new DataSet();
Database db = DatabaseFactory.CreateDatabase("ConnString");

public DataPersonalPage_Select( )
{
DbCommand dbCommand = db.GetStoredProcCommand("sp_datapersonal");
ds = db.ExecuteDataSet(dbCommand);
return ds;
}

public DataSet PendingbyDate()
{
DbCommand dbCommand = db.GetStoredProcCommand("PendingbyDate");
db.AddInParameter(dbCommand, "searchDate", DbType.DateTime, SelectedDate);
ds = db.ExecuteDataSet(dbCommand);
return ds;
}

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

i have lot of other classes which i'm doing the same thing... i declare "Database db = DatabaseFactory.CreateDatabase("ConnString");" at the top of the class

so my question is, am i'm doing correct?

thanks.

Jul 3, 2007 at 2:48 AM
Fernando, thank you so much for your reply to my question.

here is my answers:

>>>About your code, it should be fine assuming you fix the errors (like the missing return value >>>for "DataPersonalPage_Select()" and the unknown variable "SelectedDate" on "PendingbyDate() ").

sorry, i forgot to write those variables...


>>>There are simpler overloads you can use though; you can invoke Database.ExecuteDataSet(string >>>storedProcedureName, params object[] parameterValues)

can you show me a right way to use, a sample will be great.

>>>You should not use an instance variable for method temp variables (like the "ds" variable).

can you please give me an example what you mean by that?

>>>Regarding your question, you can manage the Database instances like you are describing. Be careful >>>however and try to avoid spreading the knowledge about the connection string name across different >>>classes as it would hurt maintenance; try to keep it on a single location, or better yet rely on the DAAB's >>>default connection if it works for you.

i have been struggling to find the best way to use and i appreciate if you can give me an example on how to share one instance all over the class

thanks again.
Jul 3, 2007 at 2:24 PM
Edited Jul 3, 2007 at 2:33 PM
Hi,

Answers inline



>>>There are simpler overloads you can use though; you can invoke Database.ExecuteDataSet(string >>>storedProcedureName, params object[] parameterValues)

can you show me a right way to use, a sample will be great.



It's not right or wrong, it's just that some overloads are simpler if you don't need the extra features. In your case, something like this should work:

DataSet ds = db.ExecuteDataSet("PendingbyDate", SelectedDate);



>>>You should not use an instance variable for method temp variables (like the "ds" variable).

can you please give me an example what you mean by that?



Your "ds" instance variable is not really keeping state that is necessary for your class' instances to work. A method temp is what you need there.



>>>Regarding your question, you can manage the Database instances like you are describing. Be careful >>>however and try to avoid spreading the knowledge about the connection string name across different >>>classes as it would hurt maintenance; try to keep it on a single location, or better yet rely on the DAAB's >>>default connection if it works for you.

i have been struggling to find the best way to use and i appreciate if you can give me an example on how to share one instance all over the class



I wasn't talking about sharing instances, but about spreading knowledge over your code. If you rely on the DAAB's default database (specified through configuration, please refer to the help for information on how to configure it) you won't need to specify a database name in the call to DatabaseFactory so that would be a piece of knowledge that will be would not be spread across different classes.

Hope this helps,
Fernando