none
Are DataSet and TableAdapters agnostic of database and vendor? RRS feed

  • Question

  • Hi all,

    We need the application we are building to be Database-agnostic. In particular, I need it to be able work with SQL Server, Oracle and MySQL. In the future, possibly other DB Vendors will be added to the list. Of course, whatever DB is to be used will have the same schema.

    In order to build a prototype to demonstrate the interface, we loaded the schema in an SQL Server DB, and then generated the DataSet and TableAdapters for that database. As the dataset was generated by a specific instance of a database, I suspect that the generated DataSet and TableAdapters are not vendor independent. Furthermore, I am not even sure whether they can be used for another SQL Server DB instance.

    My question is this:
     Is there a way to use the same auto-generated DataSet and TableAdapters with different databases (and possibly vendors)?

    Cheers,

    Markos
    Friday, June 19, 2009 8:24 AM

Answers

  • The DataTable and DataSet (a set of DataTable objects) are the same regardless of the original Data Source. As a matter of fact, each of the DataTable objects in a DataSet can come from different sources. That said, the TableAdapter generates strongly typed, ..NET Data Provider-specific code that IS dependent on the Data Source. Therefore, if you use untyped DataSets your approach will work fine.
     

    --
    _____________________________________________________________________ _____
    William R. Vaughn
    President and Founder Beta V Corporation
    Author, Mentor, Dad, Grandpa
    Microsoft MVP
    (425) 556-9205  (Pacific time)
    Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
    http://betav.com  http://betav.com/blog/billva
    ________________________________________________________________________ ____________________
     
     
    "deadlock_gr" wrote in message news:947ab7d9-ed67-4d9 4-90f5-8173004ac3a5...
    This is a long shot, but could I perhaps generate 3 datasets and their table adapters (one for each of Oracle, MySQL, SQL Server), and at runtime, depending on the database that needs to be used, select the appropriate one?

    Personally, I cannot see a way to do this. Let me illustrate with this pseudo-code:

    Dataset dataset;
    if (vendor = Oracle)
        dataset = oracleDataset;
    else if (vendor = mySQL)
        dataset = mySqlDataset;
    
    if (addCustomerButton_onClick) {
        dataset.CustomersTable.AddRow(row);    // won't compile; does not 
    know what dataset
    }

    in the commented line, the compiler does not know what dataset was eventually selected, and what table adapters this will have. For the same reason I won't be able to create bindings (to which dataset??).

    Any thoughts on making the application Database-vendor agnostic?

    Thank you


    Wednesday, June 24, 2009 2:42 PM
    Moderator
  • What I would do is build your data model by using te designers and creating the DataTables, columns, and relations. This would be a typedDataSet

    So a TableAdapter is code that has been created by the DataSet Designers, below is a link of how they are created

    http://msdn.microsoft.com/en-us/library/6sb6kb28(VS.80).aspx

    You will NEED to implement your own code to fill the tables. You cannot resuse the TableAdapters. When a table adapter is created I would go and look at the designer code that is created. You can see this by reviewing the code underneath the DataSet designer.xsd file. Essentially what happens is they review the DataSet model and produce basic create/retrieve/ update/ and delete (CRUD) statements for all the DataTables. Under the cover they use the DataAdapter. So the table adapter is just a nice layer over the top. The DataAdapter does most of the work.

    So you will need to create the code that leverages the DataAdapters to do the CRUD operations against each specific provider. You need to do this because specific conversions will have to be made to move from the provider types to CLR types.

    Now how would you implement them? You could use a similar pattern as the TableAdapters. Create one of these for each table. Then perhaps you can create a Factory pattern to create them per provider that you are using at the time. Or you could implement a parent class and then have all the provider specific logic in a child class, that might be a bit clunky give you have 3 different providers.

    As for the bindings, the bindings go against the DataSet itself, or the DataViews. I don't think there is much of a change here.

    So for your prototype program essentially what you will have to do is create a new adapter layer that can be used instead of the eexisting one.

    How would an entity model differ from the TypedDataSet data model that you have created?

    I hope this help clear things up.

    Thanks
    Chris Robinson
    Program Manager - DataSet

    1) You can resuse the TypedDataSet.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by deadlock_gr Monday, June 29, 2009 5:17 PM
    Monday, June 29, 2009 3:37 PM

All replies

  • The DataAdapters are tied more to the Database. DataSet is prety agnostic from a ddatbase. If you are trying to insulate the data model (DataSet) from the database you will likely have to hand create the table adapters yourself to convert the values the Database is handing back to convert them to CLR types. For example, Oracle has Number type, this is pretty board you will have to make the conversation to int, long etc within the adapter that you use to fill and update from. Then you will likely have a different adapter for SqlServer. These maybe similar of course though.

    Thanks
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, June 19, 2009 3:20 PM
  • This is a long shot, but could I perhaps generate 3 datasets and their table adapters (one for each of Oracle, MySQL, SQL Server), and at runtime, depending on the database that needs to be used, select the appropriate one?

    Personally, I cannot see a way to do this. Let me illustrate with this pseudo-code:

    Dataset dataset;
    if (vendor = Oracle)
        dataset = oracleDataset;
    else if (vendor = mySQL)
        dataset = mySqlDataset;
    
    if (addCustomerButton_onClick) {
        dataset.CustomersTable.AddRow(row);    // won't compile; does not know what dataset
    }

    in the commented line, the compiler does not know what dataset was eventually selected, and what table adapters this will have. For the same reason I won't be able to create bindings (to which dataset??).

    Any thoughts on making the application Database-vendor agnostic?

    Thank you


    Wednesday, June 24, 2009 10:27 AM
  • The DataTable and DataSet (a set of DataTable objects) are the same regardless of the original Data Source. As a matter of fact, each of the DataTable objects in a DataSet can come from different sources. That said, the TableAdapter generates strongly typed, ..NET Data Provider-specific code that IS dependent on the Data Source. Therefore, if you use untyped DataSets your approach will work fine.
     

    --
    _____________________________________________________________________ _____
    William R. Vaughn
    President and Founder Beta V Corporation
    Author, Mentor, Dad, Grandpa
    Microsoft MVP
    (425) 556-9205  (Pacific time)
    Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
    http://betav.com  http://betav.com/blog/billva
    ________________________________________________________________________ ____________________
     
     
    "deadlock_gr" wrote in message news:947ab7d9-ed67-4d9 4-90f5-8173004ac3a5...
    This is a long shot, but could I perhaps generate 3 datasets and their table adapters (one for each of Oracle, MySQL, SQL Server), and at runtime, depending on the database that needs to be used, select the appropriate one?

    Personally, I cannot see a way to do this. Let me illustrate with this pseudo-code:

    Dataset dataset;
    if (vendor = Oracle)
        dataset = oracleDataset;
    else if (vendor = mySQL)
        dataset = mySqlDataset;
    
    if (addCustomerButton_onClick) {
        dataset.CustomersTable.AddRow(row);    // won't compile; does not 
    know what dataset
    }

    in the commented line, the compiler does not know what dataset was eventually selected, and what table adapters this will have. For the same reason I won't be able to create bindings (to which dataset??).

    Any thoughts on making the application Database-vendor agnostic?

    Thank you


    Wednesday, June 24, 2009 2:42 PM
    Moderator
  • First of all, thank you for all the answers. William sorry, I unchecked our answer because there are still some unclear issues in my mind. I will re-check it as soon as everything is clear. So, let me make it extremely specific:

    I have a Windows Forms application, whose prototype was created on SQL Server (strongly-typed dataset). In its final version, the application must be able to work over SQL Server, MySQL or Oracle.

    Now I am wondering which parts (if any) can be reused from the prototype.
    1. Dataset (typed) ?
    2. TableAdapters? (probably not, they contain SQL Server-specific syntax)
    3. Bindings to DataGridViews

    Most importantly, if we need to re-implement all this, is there a way to do this at design-time? Or,
    1. do we need to programmatically create untyped-dataset?
    2. do we need to programmatically create its data adapters (or table adapters)? If yes, which of the two?
    3. do we need to programmatically create its bindings to the datagridviews of the interface?

    Perhaps irrelevant: if we create a entity model (AFAIK it provides db independence) from the existing db schema, could we use this somehow to create bindings to our datagridviews?

    Thank you again!
    Monday, June 29, 2009 11:26 AM
  • What I would do is build your data model by using te designers and creating the DataTables, columns, and relations. This would be a typedDataSet

    So a TableAdapter is code that has been created by the DataSet Designers, below is a link of how they are created

    http://msdn.microsoft.com/en-us/library/6sb6kb28(VS.80).aspx

    You will NEED to implement your own code to fill the tables. You cannot resuse the TableAdapters. When a table adapter is created I would go and look at the designer code that is created. You can see this by reviewing the code underneath the DataSet designer.xsd file. Essentially what happens is they review the DataSet model and produce basic create/retrieve/ update/ and delete (CRUD) statements for all the DataTables. Under the cover they use the DataAdapter. So the table adapter is just a nice layer over the top. The DataAdapter does most of the work.

    So you will need to create the code that leverages the DataAdapters to do the CRUD operations against each specific provider. You need to do this because specific conversions will have to be made to move from the provider types to CLR types.

    Now how would you implement them? You could use a similar pattern as the TableAdapters. Create one of these for each table. Then perhaps you can create a Factory pattern to create them per provider that you are using at the time. Or you could implement a parent class and then have all the provider specific logic in a child class, that might be a bit clunky give you have 3 different providers.

    As for the bindings, the bindings go against the DataSet itself, or the DataViews. I don't think there is much of a change here.

    So for your prototype program essentially what you will have to do is create a new adapter layer that can be used instead of the eexisting one.

    How would an entity model differ from the TypedDataSet data model that you have created?

    I hope this help clear things up.

    Thanks
    Chris Robinson
    Program Manager - DataSet

    1) You can resuse the TypedDataSet.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by deadlock_gr Monday, June 29, 2009 5:17 PM
    Monday, June 29, 2009 3:37 PM
  • I hope this help clear things up.

    I does, thanks man.

    I believe that this is a question many people have, and I had not found an answer as clear. I think many people will be helped by this post.

    For any problems I have, I will post here to keep a full record of the issue.
    Wednesday, July 1, 2009 7:52 AM