The only things that ever get returned are a DataSet, or a IDbDataReader, or a domain-specific class that's been populated by the data layer. In other words, I do something like this:
CustomersDB myCustomers = CustomersDB.Create(); // actually returns CustomersDB_Sql or CustomersDB_Oracle subclass // depending on config. DataSet customers = myCustomers.GetAllCustomers(); So the data access classes are responsible for getting the data from the database, and the rest of the code doesn't know or care about the details. I just tell the data access classes what I want from them. So to answer your question, I don't attempt to isolate the SQL syntax differences - I embrace them. I could spend hours coming up with a server-neutral SQL query, or conditionally generating SQL, but I'm not interested in that. Instead, I write a stored procedure for SQL Server, and an equivalent for Oracle, and call each from the appropriate subclass. > -----Original Message----- > From: dotnet discussion [mailto:[EMAIL PROTECTED]] > On Behalf Of Murphy, James > Sent: Tuesday, April 30, 2002 5:36 PM > To: [EMAIL PROTECTED] > Subject: Re: [DOTNET] How are you supporting multiple DB vendors? > > > > I see how your design isolates the use of one set of class > from another but how does it isolate the syntax differences > in the SQL strings used to parameterize the generalized > objects returned. > > For Example: > > /// this is great since it is independent of RDBMS > IDBConnection dbConnection = MyDBFactory.CreateConnection(); > IDbCommand cmd = dbConnection.CreateCommand(); > > /// now the DBMS specifics start creeping in... > cmd.CommandText = "Some RDBMS specific SQL statement" > > How do you NOT sprinkle RDMS specific SQL dialects in your code? > > Option 1: Store all the strings in some resource and swap in > the version you need at runtime Option 2: Build the SQL > string from a "PhraseBook" by mapping your version of SQL to > your DBMS version of SQL. Option 3: Use stored Procs for > cause the differences are fewer? > > Others? > > Thanks again, > Jim > > You can read messages from the DOTNET archive, unsubscribe > from DOTNET, or subscribe to other DevelopMentor lists at > http://discuss.develop.com. > > You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com.
