Seperate Database and Data

[shadow.demon]

G'day,

I've separated my database calls into a separate dll, and return
results from any database calls as a SqlDataReader (because of SQL
Server use, it's nice and fast). However I'd like to be database
independent by replacing the dll with whatever database is being
supported. This would mean instead of returning SqlDataReaders another
way to return results would have to be used.

Is the best method to return database neutral ArrayLists of objects
(this would mean you'd have to cycle through the results twice, one to
add to the arraylist from the database results, and once more to
retrieve the objects from the arraylist) Or to use something like a
DataSet (which isn't as straightforward as a SqlDataReader)?

Have looked around Google for a bit but didn't find info that was
totally relevant.

Thanks,
-Mark

[Wessel Troost]

> Is the best method to return database neutral ArrayLists of objects

(this would mean you'd have to cycle through the results twice, one to
add to the arraylist from the database results, and once more to
retrieve the objects from the arraylist) Or to use something like a
DataSet (which isn't as straightforward as a SqlDataReader)?

Just an idea: would it make sense to use System.Data.Odbc or
System.Data.OleDb? These are compatible with most major databases.

Greetings,
Wessel

[Dan Bass]

I'm not sure how you can have a database separated code relying on a ready
which requires an open database connection.

If you want to move the database related code into another library then the
library needs to take care of opening the connection, utilising the reader,
and closing the connection again.

This then allows you to change databases, as you've said, or even change it
so that any data input library could "feed" your core code. For example, you
should be able to have a text file reader class which gives you back the
data in the required format, and switching between SQL Server, Oracle, a
text file, message queue, Xml etc etc should be down to configuration after
the application is compiled.

In terms of what the data should look like, this depends on the criteria for
the core code, and then it is up to each "plug in" to normalize the data
into what it knows the core is expecting.
If the core is expecting a single value, for example, this is different to
whether it deals with an Xml structure, data table, and so on.

If you provide more details on what the data is, and what the application is
doing with it, perhaps we can suggest a few ways that may help you on your
way.

Dan.
<sh**********@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

G'day,

I've separated my database calls into a separate dll, and return
results from any database calls as a SqlDataReader (because of SQL
Server use, it's nice and fast). However I'd like to be database
independent by replacing the dll with whatever database is being
supported. This would mean instead of returning SqlDataReaders another
way to return results would have to be used.

Is the best method to return database neutral ArrayLists of objects
(this would mean you'd have to cycle through the results twice, one to
add to the arraylist from the database results, and once more to
retrieve the objects from the arraylist) Or to use something like a
DataSet (which isn't as straightforward as a SqlDataReader)?

Have looked around Google for a bit but didn't find info that was
totally relevant.

Thanks,
-Mark

[shadow.demon]

True, that'd be cool but then you wouldn't get to use the nice sql
server stuff. Using oledb and Datasets might be the way to go.

[shadow.demon]

e.g

DatabaseDll n = new DatabaseDll();
SqlDataReader t = n.SomeMethodThatReturnsReader();

while(t.Read())
{
Something.Add(t["DataField1"].Tostring());
}
t.Close();

Would be the basic idea, the read is just a quicker means to pass the
data.

If an arraylist was used, you'd get something like

DatabaseDll n = new DatabaseDll();
ArrayList t = n.SomeMethodThatReturnsReader();

ForEach(object e in t)
{
Something.Add(e.NameMe.ToString());
}

If you get my reasoning, it's just a means of feeding the data back.
Obviously an SqlReady isn't the best idea, but the Arraylist would mean
two cycles. DataSet the answer? The arraylist would allow a plug-able
style of programing i think?
thanks for the ideas guys,
-mark

[Brinkie]

Mark,

In your situation I'd go for an 'interfaced' approach. Instead of returning
SqlDataReader objects from your methods, they could return IDataReader (the
interface implemented by SqlDataReader, amongst others)compatible objects.
This could be a SqlDataReader, but also MySqlDataReader or an implementation
of this interface for any other database (Oracle, DB2, etc.) dependend on
the database you're using.

IDbConnection conn = null;
string connStr = "my connection string";
switch (connType)
{
case SQL:
conn = new SqlConnection(connStr);
break;
case MySQL:
conn = new MySqlConnection(connStr);
break;
}

if(connType == Connection.SQL)
conn = new SqlConnection(connStr);

IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM TABLE";
IDataReader dr = cmd.ExecuteReader();
while(dr.Read())
{
}

Hope this helps,
Raymond

<sh**********@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

G'day,

I've separated my database calls into a separate dll, and return
results from any database calls as a SqlDataReader (because of SQL
Server use, it's nice and fast). However I'd like to be database
independent by replacing the dll with whatever database is being
supported. This would mean instead of returning SqlDataReaders another
way to return results would have to be used.

Is the best method to return database neutral ArrayLists of objects
(this would mean you'd have to cycle through the results twice, one to
add to the arraylist from the database results, and once more to
retrieve the objects from the arraylist) Or to use something like a
DataSet (which isn't as straightforward as a SqlDataReader)?

Have looked around Google for a bit but didn't find info that was
totally relevant.

Thanks,
-Mark

[shadow.demon]

Ah yes this is more what i was thinking of, Thanks :D

[Dan Bass]

DatabaseDll n = new DatabaseDll();
SqlDataReader t = n.SomeMethodThatReturnsReader();

while(t.Read())
{
Something.Add(t["DataField1"].Tostring());
}
t.Close();

This wouldn't work because you don't have a database connection open...

If an arraylist was used, you'd get something like

DatabaseDll n = new DatabaseDll();
ArrayList t = n.SomeMethodThatReturnsReader();

ForEach(object e in t)
{
Something.Add(e.NameMe.ToString());
}

If you get my reasoning, it's just a means of feeding the data back.
Obviously an SqlReady isn't the best idea, but the Arraylist would mean
two cycles. DataSet the answer? The arraylist would allow a plug-able
style of programing i think?
thanks for the ideas guys,
-mark

If you're going to make an extensible solution then the plug in would need
to normalize the data (I.E, parse it at least once) extracting the data and
pushing it into the required structure.
Any generic structure would do the trick. ArrayList, SortedList, your own
defined struct, etc...

Your plugin would have something like this:

// declare and initialise variables containing connection string
// stored procedure info etc up here...

public ArrayList RetrieveData()
{
ArrayList myData = new ArrayList();
SqlConnection dbConnection = new SqlConnection();

try
{
dbConnection.Open();

SqlCommand spCommand = new SqlCommand();
// initialise the command here

SqlDataReader dbReader = spCommand.ExecuteQuery();

while ( dbReader.Read() )
{
myData.Add ( dbReader[0].ToString() );
}

dbReader.Close();

}
catch ( Exception ex )
{
// do something with the error here, throw it again if necessary...
}
finally
{
// call even if an exception occurs
dbConnection.Close();
}
return myData;
}

Your core code would then simply call this method.

// initialise the object instance here...

ArrayList myData = myDatabaseObjectInstance.RetrieveData();

// do what you will with your Data...

To make this extensible, you'd inherit from an interface which ensures that
all derived classes have public RetrieveData() instantiated... Then an
oracle library would work by simply creating the same sort of class, using
the Oracle client and syntaxes instead.

Hope that helps.

[Dan Bass]

Why catch a steam train when you've a more modern electric one instead?

<sh**********@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

True, that'd be cool but then you wouldn't get to use the nice sql
server stuff. Using oledb and Datasets might be the way to go.

[shadow.demon]

Er yeah man i already suggested this, my worry with doing it this way
is doing a cycle of the results twice, and the overhead that would
cause. I guess if you a "plugable" type struct that's the best to do
it.
Dan Bass wrote:

DatabaseDll n = new DatabaseDll();
SqlDataReader t = n.SomeMethodThatReturnsReader();

while(t.Read())
{
Something.Add(t["DataField1"].Tostring());
}
t.Close();

This wouldn't work because you don't have a database connection open...

This does work by setting the connection to stay open till the
sqldatareader is closed. Bad open connections? Probably but it works
nicely.

Thanks,
-Mark

If an arraylist was used, you'd get something like

DatabaseDll n = new DatabaseDll();
ArrayList t = n.SomeMethodThatReturnsReader();

ForEach(object e in t)
{
Something.Add(e.NameMe.ToString());
}

If you get my reasoning, it's just a means of feeding the data back.
Obviously an SqlReady isn't the best idea, but the Arraylist would mean
two cycles. DataSet the answer? The arraylist would allow a plug-able
style of programing i think?
thanks for the ideas guys,
-mark

If you're going to make an extensible solution then the plug in would need
to normalize the data (I.E, parse it at least once) extracting the data and
pushing it into the required structure.
Any generic structure would do the trick. ArrayList, SortedList, your own
defined struct, etc...

Your plugin would have something like this:

// declare and initialise variables containing connection string
// stored procedure info etc up here...

public ArrayList RetrieveData()
{
ArrayList myData = new ArrayList();
SqlConnection dbConnection = new SqlConnection();

try
{
dbConnection.Open();

SqlCommand spCommand = new SqlCommand();
// initialise the command here

SqlDataReader dbReader = spCommand.ExecuteQuery();

while ( dbReader.Read() )
{
myData.Add ( dbReader[0].ToString() );
}

dbReader.Close();

}
catch ( Exception ex )
{
// do something with the error here, throw it again if necessary...
}
finally
{
// call even if an exception occurs
dbConnection.Close();
}
return myData;
}

Your core code would then simply call this method.

// initialise the object instance here...

ArrayList myData = myDatabaseObjectInstance.RetrieveData();

// do what you will with your Data...

To make this extensible, you'd inherit from an interface which ensures that
all derived classes have public RetrieveData() instantiated... Then an
oracle library would work by simply creating the same sort of class, using
the Oracle client and syntaxes instead.

Hope that helps.