Fog Creek Software
Discussion Board




.Net without change the fonts for diferents DB's

Is it possible construct an application whithout depends a database?

For example: Construct an application in Dot Net and this application can execute with Oracle, SQL, Sybase without change the fonts.

Fabio Hayashi
Tuesday, May 07, 2002

I'll assume by "font" you actually mean "database connection" or something along those lines. "Font" actually means the form of characters displayed on the screen, like "Courier" or "Helvetica" or "Times New Roman."

So, to answer the question I think you asked: In the general case, no. You're probably going to end up with some database specific code in whatever app you write, since each DB varies slightly.

But don't feel too bad - that has nothing to do with .NET. You can't write true database server independent apps in anything else either. :-)

The best you can do is isolate your database access into it's own module, and never talk directly to the db anywhere else. Then, when you change databases, you only need to change that one module.

Chris Tavares
Tuesday, May 07, 2002

If you take a look at the source code for phpbb (an open source discussion board) they've made a decent attempt at doing this very thing.  I believe their software works with Oracle, MySql, MSSQL and Access.  Unfortunately, this also means that for the majority of their users (who are using MySQL) they can't optimize for that DBMS.  In addition, they have a lot of code in one of their modules for running the SQL statements that strip out code the MSSQL won't run, or stuff that MySQL chokes up on.

This is one of those reasons I give for having a good, solid interface set up for the data access code so you can plug in an Oracle componenet or a MSSQL component depending on what DBMS software you are running.

Phil Scott
Tuesday, May 07, 2002

Another meaning of 'font' is 'fountain', which in spanish (and maybe also in portuguese) is translated as 'fuente'. The word 'source' is also translated as 'fuente'. Consequently, I think he meant 'source code'.

Sergio Acosta
Wednesday, May 08, 2002

You can do this in various ways depending on how much interaction your application has with the db.

- If the interaction is fairly simple and limited then you might be able to get away with sticking to SQL-92 and using the OleDb namespace. DataSets can be your friend here since they can be considered a scaled down database abstraction ( ie. Get the data you need using simple SQL-92 statements and do your fancy stuff with the DataSet)

- If it gets more complicated then you may want to abstract the SQL statement into a class of it's own and have it 'render' itself differently for each particular db.

- Or you may need to do what I'm doing and write a full blown abstraction layer and have your app talk to the layer instead of the db. I've had to do this because I need to be able to access different types of dbs at the same time since some of my objects map to multiple legacy dbs. (It's quite a neat little C# component <pats himself on the back>)

Each step gets a little more abstract, slower, more flexible and requires a greater time investment so you have to ask yourself the typical ROI questions.

Paul
Wednesday, May 08, 2002

Maybe I'm not so clear! When I said "font", I'd like to say: "Source Code" or "the code that you write to do an application"

So, let's come back to the real question! If you know JAVA, you know that when you develop an application, you don't need to do stored procedures, for example. You only use EJB to access a database (select, insert, update, delete)

So, your JAVA application work with ORACLE, SQL Server or Sybase without change the source code. You don't need to change anything.

Is it possible to do the same thing in Dot Net??

Fabio Hayashi
Wednesday, May 08, 2002

>Another meaning of 'font' is 'fountain', which in spanish  (and maybe also in portuguese) is translated as 'fuente'. The word 'source' is also translated as 'fuente'. Consequently, I think he meant 'source code'.

-----

  In Portuguese font is translated as 'fonte', and source code is 'código-fonte'.  So I also think he meant 'source code'. :-)

  Ops, he just wrote that´s just what he meant. :-)

Ricardo Antunes da Costa
Wednesday, May 08, 2002

You can /try/ and keep your code more flexible by using the generic interfaces implemented by the various data access objects in System.Data, e.g.

Instead of:
  SqlConnection = new SqlConnection("Server=.;etc");
you could use:
  IDbConnection = new SqlConnection(..);
...so your code at least stands a /better chance/ of being switched to using an OleDbConnection, or an OdbcConnection.

What I think you are talking about is Java's "Container Managed Persistence"... are you? If so (and if my understanding of CMP is correct) then this isn't in .NET yet -- but something similar will be, in the form of "ObjectSpaces" in the future.

Duncan Smart
Wednesday, May 08, 2002

I'm a Java developer, and have been using an open source library called Torque for database independence.

    http://jakarta.apache.org/turbine/torque/

It's a persistence layer that maps objects to database tables, allowing me to treat records as objects and recordsets as vectors of objects.  It's really sped up my database development time.

More relevant to this thread, all interaction with the database is done through the Torque API, which is database independent.  Typically, a developer does not specify SQL directly but instead creates "Criteria" objects that indicate which records to select.    Torque comes with 'adapters' that automatically generate the SQL for Oracle, SQL Server, MySQL, PostGreSQL, etc.  (taking into account the differences between the platforms).  But if necessary, direct SQL can also be used.

This allowed me (for example) to recently develop a webapp running off MySQL on my desktop, then migrate it to SQL Server 2000.  To make this work I just needed to change a text configuration file & download some drivers from Microsoft.

Will Glass-Husain
Thursday, May 09, 2002

Don't those RDBMS <-> OO mapping layers like Torque and J2EE really destroy your scalability, though? Now, each method or property access to your object ends up in a round-trip to the database. And calls that could have been composited into a single SQL update statement end up getting spread out through multiple statements, since you can't update multiple properties in one shot.

I'm curious - care to share more details? Torque is something I wasn't aware of before.

Chris Tavares
Thursday, May 09, 2002

um. I wouldnt advise using torque. it is impossible to do simple joins without hitting the database 11 times or more on a single page.

you lose
Thursday, May 09, 2002

Store the name of the driver (e.g. System.Data.OleDb.OleDbConnection) and the connection string in the Registry. Load the driver
class using reflection and use the constructor that takes a string: the connection string.

That gives you a generic IDbConnection reference that points to the specific driver. Use the CreateCommand() method to create commands and the ExecuteReader() method to get IDataReader objects from the driver without knowing the real implementation.

The only snag with this approach is that you have to confine yourself to writing portable SQL (which may not be possible for all queries) and perhaps limit your support to drivers that have a sensible interpretation of escape codes.

Roland Kaufmann
Monday, May 13, 2002

Hi,

Don't want to get into a religious war here, just reporting on one successful experience as a user of the Torque library.  But a quick response:

Chris:
For single queries that return a set of records, there is only one call to the database.  The caller receives a List of objects, each one of which maps to a database record.  (and has been preloaded with the record data).

The exception is when a record references a key in another table.  Torque will automatically retrieve the cross-referenced record with another query when you ask for the related record.  (this is probably what "you lose" is referring to).

For a good description of how Torque works, read the <A href="http://jakarta.apache.org/turbine/torque/tutorial.html">Torque Tutorial</A>.    Skip all the configuration details for the moment, and note the clarity of the code that reads/writes to the database.  (this is what I like).

I'm sure there are other good systems out there... however I've gotten good productivity from this one.

On a separate note (no connection to Torque) Scott Ambler has written several excellent white papers on mapping objects to databases. 

<A href="http://www.ambysoft.com/persistenceLayer.pdf">The Design of a Robust Persistence Layer For Relational Databases </A>
<A href="http://www.ambysoft.com/mappingObjects.pdf">Mapping Objects to Relational Databases  </A>

As a minor point in the paper he claims that traversal queries, (which Torque implements as noted above), are actually more efficient than joins.


 

Will Glass-Husain
Thursday, May 16, 2002

oops, messed up on the hyperlinks.  anyone know if I can edit a post?

WGH

Will Glass-Husain
Thursday, May 16, 2002

> As a minor point in the paper he claims that traversal
> queries, (which Torque implements as noted above), are
> actually more efficient than joins.

This is not a minor point. First, in the paper it is said that "several small accesses are usually more efficient than one big join": rather than accept an unsubstantiated claim, you should measure the two methods for efficiency and then evaluate both for simplicity and clarity of implementation.

And then you should evaluate which approach (especially in a web application with connection pooling) gives you read-consistent images of your data...

Sebastiano Pilla
Tuesday, May 28, 2002

*  Recent Topics

*  Fog Creek Home