I wish to produce a Data Access Layer will be able to reuse with any kind of SQL provider. What I'm not going is to possess a switch statement that identifies various ways to get the id from the last produced record for every known provider. Does DbConnection provide a method to access the the id from the last produced record inside a generic way?
I would not be positive relating to this. Some databases provide automation for creating ID values (SQL Server, MySQL, for instance). The bond does not instantly supply the value back, though you need to request for this. In SQL Server,
INSERT INTO FOO (...) VALUES(...); SELECT SCOPE_IDENTITY;
Oracle does not have the idea of a name column. Producing a name value means either using the MAX + 1 of existing identity values, or utilizing a SEQUENCE along with a TRIGGER to include the identity value. In either case, you'd need to wrap everything inside a transaction to ensure correctness, but for the SEQUENCE solution, you'd need to operate a separate Choose to obtain the sequence value back (included in the transaction). You'd most likely need to do by using the MAX + 1 approach, too, however i haven't gone that direction in almost any application and so i can't say definitively. Databases which have automated identity solutions do not require a transaction to wrap each place-with-identity-request.
Generally, databases often support dialects of SQL, not really a single "standard", plus they vary much more when it comes to access methodology. Attempting to support a "common subset" often means that you simply narrow your options too much to become helpful. Taking a strategy of DbConnection, DbCommand, etc., you would be restricted to producing GUIDs in application code, because the only alternative to get away from the identity column mess.
Another option would be an ORM, which maps one onto a fundamental (and well-hidden) access layer. Entity Framework attempts to make it happen, but producing connection strings is problematic because of EF's interdependence between model and connection string. The IQToolkit project on Codeplex requires a different approach of mapping LINQ onto databases, however it still needs plug-in companies that offer not just the initial connection implementation, but additionally SQL formatters and language policy modules that shape the produced SQL towards the constraints of specific database implementations.
My feeling about this is the fact that DbConnection may be the wrong spot to go searching for a starting point your commonality. Make sure to comprehend the eccentricities of every database you need to support -- meaning you need to be very comfortable programming them before you begin wrapping them in keeping access layers. Take a look at ORMs enough to know the way they work the actual commonality they are able to provide begins with "get us a referrer record with this particular identity" instead of "produce an associationInch. They hide not just connections and instructions, but additionally provide command machines, sometimes in tight collaboration using the database provider. Most provide results as classes planned either to tables, models, or specific queries (according to LINQ, for instance).
If you want to cover the particulars of databases in the relaxation of the application, you need to hide everything. The abstract data access classes in ADO.Internet will not be adequate.
Or select from ODBC, but that is another group of problems by itself, additionally to being virtually obsolete.