If you are using a DataSet or DataTable, couldn't you define an interface for doing updates/inserts/deletes, and generate the SQL at runtime, based on the contents of the DataSet/DataTable.
I guess what I'm saying is to reproduce SqlCommandBuilder, but for other databases. Because your command builder is specific to your application, you could force it to use stored procedures, or any other db specific function. The select command becomes the only difficult part. For that, I think your 3 options would all work, plus #4, use ANSI SQL 92 :) Erick ----- Original Message ----- From: "Murphy, James" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, April 30, 2002 3:35 PM Subject: Re: [DOTNET] How are you supporting multiple DB vendors? > > -----Original Message----- > > From: Joel Mueller [mailto:[EMAIL PROTECTED]] > > Sent: Tuesday, April 30, 2002 6:24 PM > > > > I'm sure there is variation, but the architecture I described isolates > > that variation from the rest of the application, while still allowing > > one to code specifically for the database one is targeting. > > For example, > > one might support Oracle with OleDb at first, then switch to using the > > native Oracle provider when it comes out of beta - the rest > > of the code in the application doesn't have to know or care. > > 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.
