Oracle DB Project on Visual studio 2010 – Part2 Refactor

This is a series of 3 Parts to talk about creating Oracle DB Project on Visual studio 2010, try to use it with some good features of the DB Project like refactor columns and lastly to use Automated Build with this project

Part2: Refactor your schema and see how visual studio handle it

I made the below example on Sample DB “HR” that comes with Oracle Enterprise 11g R2

Let’s Begin Smile

  1. Open Schema View
  2. Here I will refactor the column “Department ID” to “Dep ID”
    1. Schema View
  3. Right click on “Department ID” and choose Refactor –> Rename
  4. Change the name to Dep_ID
  5. if you are checking the check box of “Preview changes” then by clicking on OK will show up the “Preview Changes” screen
    1. preview changes
  6. Click on apply then rebuild the application but don’t deploy it yet
  7. Let’s see if we made a schema comparison between we we have now and what is on the server
  8. Click on Data Menu à Schema Compare à New Schema Comparison
    1. schema comparison
  9. After clicking on “OK” here is the layout you will get it
    1. schema comparison results1
  10. Click on the different dependencies to see its tree
    1. You will find an update action on the departments table
    2. Click on that row and it will show you the difference
    3. Try to expand the tree for this row
    4. schema comparison results2
  11. with schema comparison you can just click update and the DB server schema will be updated, but if I were you won’t use it except if my DB is free from Data
  12. Because with schema comparison you will find in its script is a (Drop and create) and this is bad if you already have data with forced relationship ( you will definitely get errors) but the deploy script is different because it uses ALTER in its script . (will see in a while)
  13. Now let’s deploy the project by right click on the project and choose deploy
    1. if you made the connection correctly like the example I showed in Part1 then this will be smooth
  14. if you look closely in the output screen you can see the output script for the deployment (that is you choose script and database as an output type when you made deploy DB connection), this files extension will be OSQL
    1. OSQL output
  15. As you see above the deployment script is different from the schema comparison because it use ALTER command.

About nasrahmed

I'm TFS/ALM Consultant, Agile Associate and Speaker, I love Innovation and Microsoft Team Foundation System because it's the best system for ALM (Application Life Cycle Management). I'm interested in anything related to AGILE because I believe it's the best way for making software. Hope you enjoy my blog.
This entry was posted in Oracle DB Project, Visual Studio 2010 and tagged , , , , , , , . Bookmark the permalink.

2 Responses to Oracle DB Project on Visual studio 2010 – Part2 Refactor

  1. Rafael Ruiz says:

    I am looking for a tool or extensión for Visual Studio that allow me do all integration with oracle databases, but It´s not possible download the Toad Extension for Visual Studio anymore. Do you know another extension or tool?, that I can you. Thank you for your help.

    • nasrahmed says:

      Hi Rafael,
      I was shocked by the news that the support of Toad Extension for visual studio will stop, it was really a nice tool.
      The other tool i know that you can use to make Oracle Project is the Oracle Developer tools for Visual studio, but it lacks many features

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s