Unified management of database objects using VS2010 databases project templates

Unified management of database objects using VS2010 databases project templates

Visual Studio has a database project template:Visual Studiodatabase project (hereinafter referred to as VSDP), VS 2003/2005/2008 has similar projects. On the VS2010 has been greatly strengthened, and now also has intelligent awareness, build time verification and automatic deployment function, VSDP is designed for the typical database development tasks, can reverse engineering the original database, add tables, stored procedures and other database projects, Optionally, the modifications are deployed to the target database. His main features are:

1. Model contrast (Schema Compare)

During the maintenance and upgrade of the project, we need to maintain the product database (the database that is being run officially), test the database, develop the database three databases, how to synchronize to the other two databases after the development database structure changes? The model comparison function can solve this problem well. If you are developing a database structure change, you can filter the list by selecting different Objects, Missing Objects or New Objects in the Data→schema compare→filter of the menu. This filter allows us to quickly see which changes need to be updated to the target database. We can then save the modified script to a file and write it to the target database in the T-SQL Editor or directly to the modification fine.

2, data comparison (Compare)

Used to compare tables or views in two databases for the same data, which can be compared if the database name is consistent, the table has the same primary key, unique index, or UNIQUE constraint. You can save the modifications to the target database or script after you have finished comparing them. This feature can be used to import data from a product database into a development database or test database for easy development and testing.

3. Data generation plan (Generation plans)

In the project, the development team submits the version to the test environment at least once a week, and how quickly the tester generates the test base data for the new data structure? The data generation plan resolves this issue. In the data generation plan plan, we define the tables that generate the data, the number of rows to generate for each table, and the type of data to insert. Numeric types are easier to generate, and for special data such as email and phone numbers can be done through regular expression regex.

4. Refactoring (Refactor)

It is primarily used to rename database objects, such as database names, table names, and view names. When the object you want to rename changes, the reference to the object changes accordingly.

Let's take a look at the basics of how this project template is used:

Add projects to your solution, select database templates under Databases, select SQL Server 2008 DB Project, enter the name of a project, or you can choose a SQL Server.

After clicking the OK button, we joined the database project in the head of our solution:

We can now work on database development through our database engineering, such as the objects that manage database tables through the Tables node:

Add a database table, for example, we are now creating a table called applications, right click on the Tables node, choose to add a table

Enter the name of the table, click the Add button, a database table script is created, by modifying the table script for database table development, currently can only use T-SQL, there is no visual interface:

Now we need to add a primary key to the table, right-click the keys node under the Tables node, and select Add a Primiary key

Enter the name of the key and click the Add button, and the script for the primary key of the database table is created.

Now we're going to deploy the created tables to our development database:

By build our database project, VS2010 validates our database script and writes the DB schema to the. dbschema extension file:

Deploy script to database, right-click Project, select menu item DEPLOY,VS will generate deployment script:

You can then run the deployment script on your development database. But if you want to make sure that your development database is always synchronized with your development process, you can set the properties of your database project so that any changes to your database will automatically be deployed to the development database, double-click the properties of the database project, and select the Deploy option:

Select Deploy action, select Create a deployment script (. sql) and deploy to the database. Then use the target connection settings of the Edit button to connect to your development database. When you run deploy, you will deploy the database script to your development database.