database | class | reuse

[timothy]

I thought it would be nice to setup a database "accessor" (DBA) class where you could instantiate it, provide a connection string and use it's methods to do all of the database tricks. This is a sample of what I have. I'll describe the problem below...

This is a method that returns a datareader it's part of the DBA class.

.... omitted code ...
public OracleDataReader GetOraDataReader(string sqlStr)
  {
    OraConn = new OracleConnection();
    OraConn.ConnectionString=m_connStr;
  try
  {
    OraConn.Open();
    OraCmd = new OracleCommand(sqlStr,OraConn);
    return OraCmd.ExecuteReader();
  }
  catch(OracleException ex){
    throw(ex);
  }
  finally
  {
    OraConn.Close();
  }
}
....

This is a call to the method from another class.

...
OracleDataReader reader = m_dba.GetOraDataReader("SELECT PASSWORDHASH, SALT FROM WEBCZCS.CZCS_USERS WHERE USER_NAME='" + m_userName + "'");

string dbPasswordHash="";
string salt="";

// for lack of a better way to check for the existance of records in an Oracle reader.
bool hasRecords = false;
 while(reader.Read()){ 
  dbPasswordHash = reader.GetString(0);
  salt = reader.GetString(1);
  hasRecords=true;
 }
 if(!hasRecords) return false;
...

Pretty simple right? Well as you may have noticed; if I close the connection in the GetOraDataReader method I can no longer use the DataReader in the class that instantiated the DBA. I have to be able to close the connection to the database when the method is done.

So my question: is there a way to pass the DataReader (in this case) back to the calling the calling method and still be able to close the connection?

Am I stuck just putting database activities in the class, doesn’t this go against the “black box” practice in OOP?

I am still learning about OOP and C# maybe it's not meant to be done this way.

Thanks for your thoughts

 

[stsuing]

Why don't you have your data class return a datatable instead.

Here is the SQL Version of filling a datatable. You already have the command, and connection object



DataTable dataTable = new DataTable("tablename");
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
sqlDataAdapter.Fill(dataTable);
sqlDataAdapter.Dispose();
//close connection
return dataTable;

 

[octothorpe]

stsuing is right to suggest using a data adapter / dataset. The datareader requires a live connection to the database. If you need to pass that data back up, look into datasets or returning them as XML.

 

[obislavu]

It is a good ideea to put e.g (to encapsulate) in one class or in one dll the most common activities on a given database, as in your example for Oracle.
More than that you could desin Data Access Layer classes for a set of databases e.g
public DataReader GetDataReader(string sqlStr)
{
}
where DataReader is a new type that you can implement and the type of the returned value could be retrieved from a configuration file e.g. SqlDataReader or OracleDataReader.
For the Connection object you have to connect to the database when it is neeed for example when you perform a UNIT OF WORK and disconnect imediatelly.
Example:
-connect, query the databse, fill a DataSet , disconnect.
-connect , update a source table and disconnect.
Depending on the application maybe you get this approach:
Have a class to store a Connection object and keep it accessible for other classes. When you need to connect to the database you only check the State property. If it is not "Open" then call Open(). You still to close and open the connection many times.
-obislavu-

 

[timmoser]

I know what to do now. Thanks to everyone for your help!