I have two xsd files with the same exact schema. The first was generated from an SQL connection and the other was generated from a FoxPro OleDb connection by the Dataset Designer. I can't use the same XSD for generating my table adapters and tables because of SQL and OleDb providers generates different types. I can't use EF because it doesn't support FoxPro OleDb, or at least not officially.
I also have the problem where the SQL xsd file has table, field, and table adapter names in UPPERCASE and the FoxPro OleDb connection in lowercase. This creates a situation where I can't trick into casting them from a base type.
Is there a suitable work-around for this problem or do I have to have two sets of code in my DAL layer? I would hate to have to rewrite CRUD operations for all these tables.
For clarification, are you connecting to BOTH "servers" for data? Are you trying to copy data from one server to another? You could always have your query from each result in same column names and comparable data types (text vs char, integer works in both, memo vs blob, etc).
I've actually written a data querying wrapper object based on the INTERFACE types to perform querying and updating once per database connection, then subclassing from that for the respective individual table, so most of all the grunt work is done once.
As per the interfaces, I'm referring to that of IDbConnection (SQLConnection vs OleDbConnection), IDbCommand (SQLCommand vs OleDbCommand), etc and has worked well for me connecting to SQL, MySQL, Access, SQLite and Visual Foxpro databases.
All that being said, what/how are you using your XSD files. With a little more clarification, I might be able to offer a little more help.