Working with Database Objects
• Database objects can be viewed, added, modified or deleted.
- This can be done in the Solution Explorer or Schema View.
• For example, you can add a stored procedure to the project by right-clicking a schema in Schema View
(or the Stored Procedure folder in Solution Explorer) and choose Add – Stored Procedure.
• In this example, you are adding a Stored Procedure to the Person Schema in Schema View.
- This ensures that it is part of this particular schema.
• Another dialog appears, allowing you to specify the type of the stored procedure and its name.
• This is a Programmability Stored Procedure and you can name it based on its functionality.
- Whatever you name the procedure, Visual Studio adds the parent schema to its name.
- The stored procedure above is called Person.GetMNAddresses.
• When you’ve named the procedure, you are given the source code that you can modify.
- Notice that this procedure assumes that are passing in parameters.
-
• You modify the stored procedure to look like the following:
CREATE PROCEDURE [Person].[GetMNAddresses]
AS
-- Returns all addresses in Minnesota
SELECT * FROM Person.Address
WHERE StateProvinceID = 36
• Once completed, you can save the stored procedure to your project.
- This modification has not been performed on the original database, nor has the database objects been
deployed to a new database.
- Note that you can verify the SQL code within Visual Studio before you save it. As a matter of fact, this
step cannot be avoided.
• You can see the new stored procedure in all other views of the database project, such as Solution
Explorer.
• If you attempt to test this stored procedure, you are prompted to choose a database connection – those
listed in your Server Explorer.
- Keep in mind that running this stored procedure against a live database will creates the stored procedure.
- This defeats the security of working with an offline representation of a database!
- You learn about deployment later on.
• Database objects can also be refactored in Schema View.
- This includes the name of the schemas, the objects and parameters.
- These changes can be immediately performed on all referencing objects within the database project.
• For example, assume you want to rename the column AddressLine1 to Address1 and AddressLine2 to
Address2 in the Person.Address table.
- In Schema View, find the Person.Address table, right-click AddressLine1 and choose Refactor –
Rename…
• In this dialog box, you can set the new name of the column.
- Notice that you even perform the changes to objects which may have errors.
- A database object may have an error because it is referencing a column that you haven’t refactored yet.
• A dialog box appears that showing you all parts to be renamed.
• Click on each of the highlighted items in the upper dialog box and watch the lower box to see what will
get changed.
- When you are satisfied with the results, you can click Apply.
- The change can be applied to AddressLine2 as well. It may take a while to perform. It’s going through a
lot of SQL scripts!
Building and Deploying a Database Project
• Once a database design has been completed, it can be added to source control.
- Once added, you can version it, granting team members access to any version of the database.
• Access to earlier versions can help developers discover the history of a database.
- Perhaps a developer is curious when a column changed or why a DAL component no longer works with
the latest version of the database.
• The database project can then be built using the Build menu.
• Notice that, not unlike other projects in Visual Studio, projects can be built at the Solution level or the
individual project level.
- Once it successfully builds, you can deploy it.
• Deployment occurs using the same Build menu.
- If successful, you should see something like this:
• When you go look at the list of stored procedures in your Server Explorer, you see GetMNAddresses
has been added!
- When you deployed the database project, you performed the changes to the database.
• Take a look at the Address table in your Solution Explorer.
- Why aren’t the two column names, AddressLine1 and AddressLine2, changed?
- What would happen to the data in the table if the column name was changed?
• The data for those two columns would be deleted!
- By default, Team DB Pro does not drop database objects from your deployment server because of the
possible loss of data.
• You can change this by checking the box in the picture below.
• WARNING: Be sure to back up the data in the tables or columns you are renaming before you select
this!
- Otherwise data is lost permanently.
• One possible solution would be to check the box above that to create the DROP statements in the SQL
script.
- Before deploying the database, change the drop/add statements in the deployment script to use
sp_rename.
sp_rename 'Person.Address.AddressLine1', 'Address1', 'COLUMN'
• Alternatively, another solution involves adding the Address1 and Address2 columns and then adding
the following SQL to the deployment script:
-- Move the data between the old and new columns.
UPDATE Person.Address SET Address1 = AddressLine1
UPDATE Person.Address SET Address2 = AddressLine2
-- Drop the old column manually.
ALTER TABLE Person.Address DROP COLUMN AddressLine1
ALTER TABLE Person.Address DROP COLUMN AddressLine2
• Database objects can be viewed, added, modified or deleted.
- This can be done in the Solution Explorer or Schema View.
• For example, you can add a stored procedure to the project by right-clicking a schema in Schema View
(or the Stored Procedure folder in Solution Explorer) and choose Add – Stored Procedure.
• In this example, you are adding a Stored Procedure to the Person Schema in Schema View.
- This ensures that it is part of this particular schema.
• Another dialog appears, allowing you to specify the type of the stored procedure and its name.
• This is a Programmability Stored Procedure and you can name it based on its functionality.
- Whatever you name the procedure, Visual Studio adds the parent schema to its name.
- The stored procedure above is called Person.GetMNAddresses.
• When you’ve named the procedure, you are given the source code that you can modify.
- Notice that this procedure assumes that are passing in parameters.
-
• You modify the stored procedure to look like the following:
CREATE PROCEDURE [Person].[GetMNAddresses]
AS
-- Returns all addresses in Minnesota
SELECT * FROM Person.Address
WHERE StateProvinceID = 36
• Once completed, you can save the stored procedure to your project.
- This modification has not been performed on the original database, nor has the database objects been
deployed to a new database.
- Note that you can verify the SQL code within Visual Studio before you save it. As a matter of fact, this
step cannot be avoided.
• You can see the new stored procedure in all other views of the database project, such as Solution
Explorer.
• If you attempt to test this stored procedure, you are prompted to choose a database connection – those
listed in your Server Explorer.
- Keep in mind that running this stored procedure against a live database will creates the stored procedure.
- This defeats the security of working with an offline representation of a database!
- You learn about deployment later on.
• Database objects can also be refactored in Schema View.
- This includes the name of the schemas, the objects and parameters.
- These changes can be immediately performed on all referencing objects within the database project.
• For example, assume you want to rename the column AddressLine1 to Address1 and AddressLine2 to
Address2 in the Person.Address table.
- In Schema View, find the Person.Address table, right-click AddressLine1 and choose Refactor –
Rename…
• In this dialog box, you can set the new name of the column.
- Notice that you even perform the changes to objects which may have errors.
- A database object may have an error because it is referencing a column that you haven’t refactored yet.
• A dialog box appears that showing you all parts to be renamed.
• Click on each of the highlighted items in the upper dialog box and watch the lower box to see what will
get changed.
- When you are satisfied with the results, you can click Apply.
- The change can be applied to AddressLine2 as well. It may take a while to perform. It’s going through a
lot of SQL scripts!
Building and Deploying a Database Project
• Once a database design has been completed, it can be added to source control.
- Once added, you can version it, granting team members access to any version of the database.
• Access to earlier versions can help developers discover the history of a database.
- Perhaps a developer is curious when a column changed or why a DAL component no longer works with
the latest version of the database.
• The database project can then be built using the Build menu.
• Notice that, not unlike other projects in Visual Studio, projects can be built at the Solution level or the
individual project level.
- Once it successfully builds, you can deploy it.
• Deployment occurs using the same Build menu.
- If successful, you should see something like this:
• When you go look at the list of stored procedures in your Server Explorer, you see GetMNAddresses
has been added!
- When you deployed the database project, you performed the changes to the database.
• Take a look at the Address table in your Solution Explorer.
- Why aren’t the two column names, AddressLine1 and AddressLine2, changed?
- What would happen to the data in the table if the column name was changed?
• The data for those two columns would be deleted!
- By default, Team DB Pro does not drop database objects from your deployment server because of the
possible loss of data.
• You can change this by checking the box in the picture below.
• WARNING: Be sure to back up the data in the tables or columns you are renaming before you select
this!
- Otherwise data is lost permanently.
• One possible solution would be to check the box above that to create the DROP statements in the SQL
script.
- Before deploying the database, change the drop/add statements in the deployment script to use
sp_rename.
sp_rename 'Person.Address.AddressLine1', 'Address1', 'COLUMN'
• Alternatively, another solution involves adding the Address1 and Address2 columns and then adding
the following SQL to the deployment script:
-- Move the data between the old and new columns.
UPDATE Person.Address SET Address1 = AddressLine1
UPDATE Person.Address SET Address2 = AddressLine2
-- Drop the old column manually.
ALTER TABLE Person.Address DROP COLUMN AddressLine1
ALTER TABLE Person.Address DROP COLUMN AddressLine2
Database Objects
Table of Contents
Copyright (c) 2008. Intertech, Inc. All Rights Reserved. This information is to be used exclusively as an
online learning aid. Any attempts to copy, reproduce, or use for training is strictly prohibited.
online learning aid. Any attempts to copy, reproduce, or use for training is strictly prohibited.
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
Courseware
Training Resources
Tutorials