none
OLEDB Oracle vs. SQL Server SQL Statements RRS feed

  • Question

  • I use OLEDB and TableAdapters to generate code for both database engines (Oracle & SQL Server). 

    The SQL that's getting generated in the *.Designer.cs file encloses table and column names in brackets. i.e. DELETE FROM [MyTable] ... 

    Which works fine against SQL Server but not Oracle. Can anyone shed some light on this? Is there a way I can get VS to not put the brackets in? Perhaps this is an Oracle db configuration issue - but I used to use DataAdapters and never had this problem.

    I wrote a macro to fix the generated code, but it's easy to forget to run it - and I'd rather not need it anyway.

    Thanks,
    Dan Holt
    Thursday, February 25, 2010 5:25 PM

Answers

  • Dan,

    I'd suggest taking a step back to look at the goal of using the OLE DB Provider.  It sounds like your goal is to generate code that can be used with both SQL Server and Oracle as possible back-ends with only minimal changes required to the code.  In theory, you could accomplish this with System.Data.OleDb by changing only the connection string.  If you stick with just the simplest database data types and the simplest queries, this is possible.  However, I still wouldn't recommend this approach.  Most developers I've worked with over the years wind up needing to branch the code more than they'd expected.

    Your better bet is to generate separate objects (Connections, Commands, DataAdapters, etc.) using Microsoft's and Oracle's ADO.NET Data Providers.  For each type of object, there is a common base class (DbConnection, DbCommand, DbDataAdapter) that you can use to connect, fetch rows, submit updates, etc.  You can also use the DbProviderFactories class to programmatically determine if the desired ADO.NET provider is installed and access the provider if it is.

    If you still want to focus on the OLE DB approach and want to get more information on the way the tables (and likely columns) are delimited in the queries that Visual Studio is generating, your best bet is to call out which version of Visual Studio you're using and how you're creating your TableAdapters or DataAdapters.

    I hope this information proves helpful.

    David Sceppa

    David Sceppa
    Thursday, March 11, 2010 10:58 PM
    Moderator

All replies

  • Hello Dan,

     

    Welcome to ADO.NET Data Providers forum!

     

    Could you please tell us what data provider are you using for Oracle database?   I think how the SQL statements are generated is determined by the data provider we used.  

     

    Besides, I don’t think VS export such a setting to let us control the [] in the query command. 

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, February 26, 2010 5:28 AM
    Moderator
  • You may want to use ODP.NET instead. The Designer is rather generic and I've encountered problems before with SQL syntax between the different systems. As a matter of fact in some instances you can't even run the SQL in the Designer, although it can be saved and executed through the Class.




    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, February 26, 2010 1:22 PM
  • Hi Lingzhi, we're using Provider=OraOLEDB.Oracle.1; or Provider=OraOLEDB.Oracle; in our connection strings.

    We've switched back and forth between using the MS & Oracle provider but are using Oracle for - reasons I can no longer remember.

    To further complicate matters, we use SQL Server databases to generate the SQL (via OLEDB, not native) - simply because using Oracle upper-cases all the generated table/column/variable names.

    Thanks,
    Dan Holt
    Monday, March 1, 2010 3:58 PM
  • Hello Dan,

     

    I second Paul's suggestion.  It is recommended to use Oracle's data provider instead of OracleClient, because OracleClient will be deprecated in VS2010.  http://blogs.msdn.com/adonet/archive/2009/06/15/system-data-oracleclient-update.aspx
     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, March 2, 2010 9:12 AM
    Moderator
  • Hello Dan,

     

     

    How are you?   How is the problem now?   

    If you have any questions, please feel free to let me know.
     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, March 9, 2010 2:39 AM
    Moderator
  • Dan,

    I'd suggest taking a step back to look at the goal of using the OLE DB Provider.  It sounds like your goal is to generate code that can be used with both SQL Server and Oracle as possible back-ends with only minimal changes required to the code.  In theory, you could accomplish this with System.Data.OleDb by changing only the connection string.  If you stick with just the simplest database data types and the simplest queries, this is possible.  However, I still wouldn't recommend this approach.  Most developers I've worked with over the years wind up needing to branch the code more than they'd expected.

    Your better bet is to generate separate objects (Connections, Commands, DataAdapters, etc.) using Microsoft's and Oracle's ADO.NET Data Providers.  For each type of object, there is a common base class (DbConnection, DbCommand, DbDataAdapter) that you can use to connect, fetch rows, submit updates, etc.  You can also use the DbProviderFactories class to programmatically determine if the desired ADO.NET provider is installed and access the provider if it is.

    If you still want to focus on the OLE DB approach and want to get more information on the way the tables (and likely columns) are delimited in the queries that Visual Studio is generating, your best bet is to call out which version of Visual Studio you're using and how you're creating your TableAdapters or DataAdapters.

    I hope this information proves helpful.

    David Sceppa

    David Sceppa
    Thursday, March 11, 2010 10:58 PM
    Moderator
  • David, I'm sure you're correct - unfortunately, I work at one of those companies that steadfastly refuses to invest in either infrastructure maintenance or setting goals for the various groups involved in software development. Picture the Keystone Cops (you're probably too young - google it) now replace the robbers with rapidly shrinking project opportunities and the cops with hapless middle managers desperate to appear to be doing something, all the while doing as little as possible. The software dept. is the shuddering jalopy careening clumsily from one missed opportunity after another - backfiring and making funny horn sounds all the while.

    I'll mark this thread as 'answered' so as not to take any more time. I'm hoping that next week we'll have an opportunity to try the driver as mentioned above. 

    But just to wrap it up cleanly: VS 2008. We used DataAdapters in the past but I started using TableAdapters since that seemed to be the preference in VS08. I create the TA in the VS dataset designer from an existing SQL Server db table.

    At least all that db-access code is kept in a common assembly (rather than scattered throughout the code) so there is an opportunity to replace it without effecting client code - someday!

    Sincerely - thanks to all,
    Dan Holt

    Friday, March 12, 2010 4:19 PM
  • Dan,

    I am old enough to have seen some old Keystone Kops. ;-)  And I can definitely understand how guidelines can restrict your approach.  Good luck working on those guidelines.  In the meantime, this might help...

    Visual Studio relies on the ADO.NET CommandBuilder classes under the covers to generate the updating logic.  I'm pretty sure that Visual Studio is setting some additional properties that cause the table and column names to be delimited in the updating logic.  While the delimiters cause problems in this particular case, they're helpful when table or column names contain spaces or reserved words.

    If you do have to use the brute force approach, you could theoretically write some code to manually edit the queries in the XSD file prior to compiling the application.  Here's some code I used to simulate what Visual Studio does under the covers when building the updating logic.  If you go this route, make sure you're avoiding reserved words and spaces in table and column names.

    string connectionString = "Provider=SQLOLEDB;Data Source=.;Trusted_Connection=Yes;" +
                              "Initial Catalog=Northwind;";
    string commandText = "SELECT CustomerID, CompanyName FROM Customers";
    OleDbDataAdapter da = new OleDbDataAdapter(commandText, connectionString);
    OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
    Console.WriteLine(cb.GetInsertCommand().CommandText);
    cb.RefreshSchema();
    cb.QuotePrefix = "[";
    cb.QuoteSuffix = "]";
    Console.WriteLine(cb.GetInsertCommand().CommandText);
    

    One other option would be to use reflection within the application to get into the TableAdapter's internals to use the CommandBuilder class at run-time.  Even though the thread's marked as answered, feel free to reply if you want to try this approach and I'll put together a code snippet for it.

    Both of these approaches are ugly and I strongly recommend going with the separate ADO.NET providers if you can convince folks to go that route.

    I hope this information proves helpful.

    David Sceppa
    Friday, March 12, 2010 10:14 PM
    Moderator