OOP question and correct class usage for connecting to DB

[Jim]

I'm writing an Invoicing Windows app but I'm writing it to make the code 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 by using this structure, how would
I be able to update my dataset when I don't have direct access to the
dataadapter? Any suggestions on how this would be better organized would be
really appreciated.
This is how the code 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); //the
SQLServer parameter is not being used now
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.

[Simon Stewart]

Hi Jim

Firstly, as you seem to be using SQLServer, I'd really recommend that you
use stored procedures.
If for only one reason, they will give you better maintainability.

I've taken the following approach in a Win32 app that proved to be quite
effective, especially in terms of reusability and maintainability:
// hide all data access/business logic inside static classes that represent
business entities
DataTable dt= Student.FetchByFirstLast(txtFirstName.Text,txtLast Name.Text);
if(dt.Rows.Count>0)
{
// .......
Inside this "Student" class I call a data access class (very similar to the
DAAB from MS).
Using this approach, with an atomic class, I can concentrate on the business
logic.

For updates, I pass the individual params to an "Update" on the respective
classes.
HTH

Cheers,

Simon Stewart
Johannesburg, South Africa
"Jim" <NA> wrote in message news:ec*************@TK2MSFTNGP10.phx.gbl...

I'm writing an Invoicing Windows app but I'm writing it to make the code 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 by using this structure, how would I be able to update my dataset when I don't have direct access to the
dataadapter? Any suggestions on how this would be better organized would be really appreciated.
This is how the code 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); //the
SQLServer parameter is not being used now
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.

[decrypted]

Jim,
Its fine to seperate out your data access layer from the rest of your code
if you need or plan to have varying data sources (any exnterprise level
application would). What your doing here is a little off point though. A
data access layer tends to not only know how to connect to a database, but
know how to do the work as well (and know which tool in the shed to use).
Here, you are setting yourself up for one type of connection, a sql server
connection, so the only gain you have here is changing your database (not
really running multiple databases in parelles). Additionaly, your connection
method is always returning a DataSet. DataSets tend to be big and bulky and
should only be used when absolutly necessary. Most
of your application will likely benefit from a DataReader instead of a data
set (and btw, you don't have to close a connection if you didn't open it
outside of adapater). I am assuming from looking at this code, your client
classes know how to work with the data being returned in any way (be that a
data table, a data set, a reader, an xml flat file, whatever). So your not
getting much gain in terms of this class doing any real work. Pluss, your
creating a new object every time to want to make a call to your database
(this new class).
For maintainability, your on the right track. there isn't anything wrong
with wanting to have a data access layer that handles connections to various
database, the retriving of data and the modification of data. I would just
suggest you take a look at some design patterns that help you solve
particular problems in the data tier and build on that rather than having a
general knowledge of OOP and trying to solve problems that have been solved
many times over.
Wish i had a more specific answer for you, but without knowing more about
the app and what you already know regarding OOAD and patterns, this is the
best I can do.

-dec
"Jim" <NA> wrote in message news:ec*************@TK2MSFTNGP10.phx.gbl...

I'm writing an Invoicing Windows app but I'm writing it to make the code 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 by using this structure, how would I be able to update my dataset when I don't have direct access to the
dataadapter? Any suggestions on how this would be better organized would be really appreciated.
This is how the code 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); //the
SQLServer parameter is not being used now
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.