Having a connection class that only does connection work

  • Thread starter Thread starter Jim
  • Start date Start date
J

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.GetProducts 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.
 
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?
Click to expand...

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.GetProducts 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.
Click to expand...

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
 
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_AddNewProduct?

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

private void frm_AddNewProduct_Load(object sender, System.EventArgs e)

{ ...

DataAccess DataAccess = new DataAccess();

DS_Products = DataAccess.GetProducts();

.... }

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

public class DataAccess

{ ....

public DataSet GetProducts()
{
string mySql = "Select itemid, Description, Quantity, Price from
products where producttype = 'P'";
DBConnection myConn = new DBConnection("SQLServer", mySql);
DataSet DS_MyProducts = new DataSet();
DS_MyProducts = myConn.Connection();
return DS_MyProducts;
}
}
----------------
public class DBConnection
{
public DataSet MyClientsDS;
public SqlDataAdapter MyDataAdapterClients;
string strThisConn, strThisQuery, strTable;
public DBConnection(string strDataBase, string strQuery)
{
strThisConn = strDataBase;
strThisQuery = strQuery;
strTable = strMainTable;
}
public DataSet Connection()
{
MyClientsDS = new DataSet();
SqlConnection SQLConn = new SqlConnection("Data Source=localhost;
Integrated Security=SSPI;" +
"Initial Catalog=invoicing");
MyDataAdapterClients = new SqlDataAdapter (strThisQuery, SQLConn);
MyDataAdapterClients.Fill(MyClientsDS);
SQLConn.Close();
return MyClientsDS;
}
}

Thanks for your help.
 
Back
Top