Having a connection class that only does connection work

[Jim]

Is it possible to have a Connection class that opens up connection to my DB
and leaves it open until I decide to close it?
I run many SQL statements so I was wondering if it's possible to call a
Class1.GetProdu cts that sets up an SQL statement and then calls another
Class2.Connect that actually opens up the connection Class2.Connect would
not receive anything (since the connections string is always the same) and
would not return anything.

Thanks.

[William Ryan]

> Is it possible to have a Connection class that opens up connection to my
DB

and leaves it open until I decide to close it?
In many instances, if you are using any of the 'connected' ado.net objects,
you don't have a choice, you have to open them manually and close them when
you are done which is determined by you.
I run many SQL statements so I was wondering if it's possible to call a
Class1.GetProdu cts that sets up an SQL statement and then calls another
Class2.Connect that actually opens up the connection Class2.Connect would
not receive anything (since the connections string is always the same) and
would not return anything.
Yes, you can do that, but if I understand you correctly, I don't understand
what you are getting by it. You can define a static property connection
somewhere and instantiate it in your class locally each time you need it.
Leaving a connection open just for leaving it's open sake is kind of
antithetical to everything ADO.NET, and unless you are firing A LOT of
sequential statements, I don't know that there's much value if the
connection string remains identical and everything is sitting tin the pool.

If you could describe your ultimate goal, I could probably be of more
assistance, but for the time being, I think theanswer is Yes to both of your
questions.

Cheers,

Bill
"Jim" <NA> wrote in message news:uI******** ******@TK2MSFTN GP11.phx.gbl...

Thanks.

[Jim]

I want my code to be as easy to maintain as possible. Basically, to get any
records from my DB, I use two classes: one that sets up the SQL statement,
and another that makes the connection (plus the class that contains the
windows form). Now, is this the best way to divide my tasks? And how can I
update the dataset in frm_AddNewProdu ct?

This is how it looks like:
public class frm_AddNewProdu ct : System.Windows. Forms.Form

private void frm_AddNewProdu ct_Load(object sender, System.EventArg s e)

{ ...

DataAccess DataAccess = new DataAccess();

DS_Products = DataAccess.GetP roducts();

.... }

----------------------------

public class DataAccess

{ ....

public DataSet GetProducts()
{
string mySql = "Select itemid, Description, Quantity, Price from
products where producttype = 'P'";
DBConnection myConn = new DBConnection("S QLServer", mySql);
DataSet DS_MyProducts = new DataSet();
DS_MyProducts = myConn.Connecti on();
return DS_MyProducts;
}
}
----------------
public class DBConnection
{
public DataSet MyClientsDS;
public SqlDataAdapter MyDataAdapterCl ients;
string strThisConn, strThisQuery, strTable;
public DBConnection(st ring strDataBase, string strQuery)
{
strThisConn = strDataBase;
strThisQuery = strQuery;
strTable = strMainTable;
}
public DataSet Connection()
{
MyClientsDS = new DataSet();
SqlConnection SQLConn = new SqlConnection(" Data Source=localhos t;
Integrated Security=SSPI;" +
"Initial Catalog=invoici ng");
MyDataAdapterCl ients = new SqlDataAdapter (strThisQuery, SQLConn);
MyDataAdapterCl ients.Fill(MyCl ientsDS);
SQLConn.Close() ;
return MyClientsDS;
}
}

Thanks for your help.
"William Ryan" <do********@nos pam.comcast.net > wrote in message
news:ef******** ******@TK2MSFTN GP09.phx.gbl...

Is it possible to have a Connection class that opens up connection to my DB

and leaves it open until I decide to close it?

In many instances, if you are using any of the 'connected' ado.net

objects, you don't have a choice, you have to open them manually and close them when you are done which is determined by you.

I run many SQL statements so I was wondering if it's possible to call a
Class1.GetProdu cts that sets up an SQL statement and then calls another
Class2.Connect that actually opens up the connection Class2.Connect would not receive anything (since the connections string is always the same) and would not return anything.
Yes, you can do that, but if I understand you correctly, I don't

understand what you are getting by it. You can define a static property connection
somewhere and instantiate it in your class locally each time you need it.
Leaving a connection open just for leaving it's open sake is kind of
antithetical to everything ADO.NET, and unless you are firing A LOT of
sequential statements, I don't know that there's much value if the
connection string remains identical and everything is sitting tin the pool.
If you could describe your ultimate goal, I could probably be of more
assistance, but for the time being, I think theanswer is Yes to both of your questions.

Cheers,

Bill
"Jim" <NA> wrote in message news:uI******** ******@TK2MSFTN GP11.phx.gbl...

Thanks.