Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Creating a data project with SQL Project

From: Visual Studio 2010 Essential Training

Video: Creating a data project with SQL Project

Here is a problem that torments developer teams everywhere. If you've ever worked with database changes and multiple developers, you know that keeping your database schema in sync is a never-ending task. If someone makes a change to the database schema, you must ensure that that change propagates to each developer's computer, makes it to the test team, and gets updated on all your production servers. The trouble is compounded when debugging previous versions of your product. Now you have to ensure that the database matches the code that is stored in the source control.

Creating a data project with SQL Project

Here is a problem that torments developer teams everywhere. If you've ever worked with database changes and multiple developers, you know that keeping your database schema in sync is a never-ending task. If someone makes a change to the database schema, you must ensure that that change propagates to each developer's computer, makes it to the test team, and gets updated on all your production servers. The trouble is compounded when debugging previous versions of your product. Now you have to ensure that the database matches the code that is stored in the source control.

Some teams attempt to solve this problem by having a folder full of SQL scripts that contain the information to update the schema. These scripts can quickly get out of sync with the real database schema, however. Believe me, I know this from first-hand experience. To solve these problems, Microsoft created a special database project known as SQL project. Before I continue, I should add that these features are only available in Visual Studio Premium edition or Ultimate edition. If you do not have either of these editions, you will not be able to follow along with my demonstrations.

I'm inside Visual Studio, and I've opened a solution called SqlProjects. This has a project called UseExisting that I'm going to show you in about two minutes. But first, I want to show you how to create a brand-new SQL project. I'm going to right-click on this solution and choose Add > New Project. The projects I'm looking for are down here near the bottom of the Installed Templates section, in the Database node, underneath SQL Server. As you can see, there are several types of projects here.

The one that I want to use today is called the SQL Server 2008 Wizard. I'm going to come down here and have it generate a new BookSeller database, also in V1, like that, and then click OK. Now the wizard will launch and I get the Welcome screen, which I'm going to skip. The next type is what kind of project do I want to create? I'll create a user-defined database project, and I'm going to choose to store the script files by type of object.

Then I'm going to click on Next. These are the standard settings for the SQL Server database, so I'm going to leave them as is. Then I'll click on Next. At this point, I'm creating a brand-new databases, so I would be responsible for creating all of the scripts for creating the tabled, the start procedures, the triggers, and everything. I bet that you already have some databases in your organization. If that's the case, you could click on Import existing schema, go over here and choose your database, make connections to the server, tell it which database you want, and then when you clicked on Next, Visual Studio will go out and import all the information from your existing database.

Now since we don't have a full-blown version of SQL Server installed in this machine, I am not going to demonstrate this. Later, I'll show you a schema I did get from an existing database. At this point though, I'm going to uncheck this Import existing schema and then click on Next, and then verify that everything looks the way I want and click Finish and click Finish one more time. At this point, I have created a Visual Studio project that contains all the information about creating a brand-new database.

This can be stored in source control. Think about this for a second. This becomes very beneficial. Imagine this scenario: Each developer in your team works with his own local server instance of that database. When one of your developers makes the schema change--let's say he adds a new column to the table--he'll do this not in the real database, but inside this project. He'll go over here to Schema Objects > Tables. We're going to add a table here called Books.

So the change would be made here. You would write the SQL script here to modify the name of the column, or change the name of the column. Then I can check this in the source control. All the other developers can check out this item from source control and then they can run the Update Database feature that's inside this project. That will update their local copy of the database with the changes, and every developer can do that. Also, your SQL Server administrators can do the same thing on your production and your test servers.

Rather than walk through creating a bunch of tables and start procedures, I'm going to show you the second project that I have here, called UseExisting. I ran this against the NorthWnd database. So if I come down here and show you the table section here, you'd see that it contains a number of table schemas. I'll double-click on this Shippers.table.sql. And this shows how to create the table inside your local copy the database. There are plenty of other interesting tools.

There is a Data menu up here that has something called Schema Compare. What I can do with this is check two different databases. I can look at this project's schema, and this UseExisting project's schema, and when I compare the two of them, what's happening is it's looking at the differences between the tables and all the other entities in this database and showing me what's missing. For instance, if I look down here you'll see that I have a Books table in the BookSeller, but I don't have it in the using existing. So then I can say, create that table and then the next time when we run this script against the database, it's going to add that table to the UseExisting database.

There is also another tool called Data Compare. What this does is you point it at two separate databases, and it looks at all the actual data that's in the database and make sure that they match up. And there is also a Create Test Data feature, where I can tell it to generate fictitious data that I can use on our testing applications. Well, now that we've got this project created, it's time to see how to create design time data and modify database objects. That's the topic for another movie in this chapter.

Show transcript

This video is part of

Image for Visual Studio 2010 Essential Training
Visual Studio 2010 Essential Training

86 video lessons · 31864 viewers

Walt Ritscher
Author

 
Expand all | Collapse all
  1. 2m 3s
    1. Welcome
      1m 2s
    2. Using the exercise files
      1m 1s
  2. 7m 19s
    1. Understanding the Visual Studio versions
      3m 51s
    2. Setting up your developer computer
      3m 28s
  3. 58m 2s
    1. Creating a Visual Studio project
      4m 58s
    2. Working with Solution Explorer
      6m 32s
    3. Working with big projects
      3m 53s
    4. Taking a tour of the Integrated Developer Environment (IDE)
      8m 36s
    5. Introducing drag-and-drop UI design
      7m 38s
    6. Working with the Properties window
      6m 44s
    7. Looking at Server Explorer
      7m 4s
    8. Exploring the new Help engine
      6m 41s
    9. Setting options for the IDE
      5m 56s
  4. 39m 25s
    1. Creating a simple WPF application
      1m 32s
    2. Building the UI with the editors
      9m 14s
    3. Working with the application code
      3m 37s
    4. Communicating with the web site
      7m 15s
    5. Connecting your data
      8m 4s
    6. Binding to an RSS feed
      5m 4s
    7. Packaging and deploying the application
      4m 39s
  5. 39m 46s
    1. What languages are supported in Visual Studio 2010?
      1m 17s
    2. Exploring basic settings for the Code Editor
      5m 35s
    3. Writing a C# program
      6m 48s
    4. Writing a VB program
      6m 29s
    5. Working with C++
      6m 38s
    6. Working with F Sharp
      6m 9s
    7. Font and color options
      6m 50s
  6. 1h 5m
    1. Formatting your code
      6m 43s
    2. Navigating your code
      7m 44s
    3. Using the Task List
      2m 26s
    4. Commenting your code
      2m 45s
    5. Documenting your code
      8m 26s
    6. Using IntelliSense effectively
      7m 0s
    7. Working with code snippets
      6m 25s
    8. Refactoring your code
      5m 15s
    9. Understanding code generation
      2m 10s
    10. Generating code with T4
      6m 29s
    11. Using the Class View, Class Designer, and Class Diagram tools
      5m 51s
    12. Refactoring VB with CodeRush Xpress
      4m 33s
  7. 1h 11m
    1. Working with project and item templates
      8m 38s
    2. Creating a console application
      7m 5s
    3. Creating a class library
      6m 26s
    4. Creating a web site with ASP.NET
      7m 37s
    5. Creating a rich internet application with Silverlight
      6m 57s
    6. Creating a classic Windows application with Windows Forms
      10m 31s
    7. Creating a dramatic Windows application with Windows Presentation Foundation (WPF)
      4m 41s
    8. Creating a WCF service
      9m 1s
    9. Using an existing WCF service
      6m 38s
    10. Navigation UI designs with the Document Outline view
      3m 41s
  8. 33m 18s
    1. Creating a data project with SQL Project
      6m 24s
    2. Clarifying the confusion on .NET Data
      3m 31s
    3. Using ADO.NET in your application
      6m 50s
    4. Creating typed datasets
      7m 55s
    5. Using the data binding tools
      8m 38s
  9. 30m 13s
    1. Debugging code
      9m 32s
    2. Working with the Watch and other debug windows
      7m 46s
    3. Other debugging techniques
      6m 50s
    4. IntelliTrace historical debugging in Visual Studio Ultimate
      6m 5s
  10. 17m 56s
    1. Understanding Visual Studio editions and test tools
      2m 22s
    2. Verifying your code with unit tests
      8m 58s
    3. Running performance and load tests
      6m 36s
  11. 34m 5s
    1. Building your application
      4m 19s
    2. Customizing the build process with MSBuild
      6m 36s
    3. Setting assembly information
      2m 12s
    4. Deploying a basic Windows application
      2m 19s
    5. Creating an installer with Visual Studio
      7m 39s
    6. Creating a ClickOnce application
      5m 13s
    7. Setting up IIS for deploy
      2m 9s
    8. Deploying a Silverlight or ASP.NET application
      3m 38s
  12. 14m 0s
    1. Understanding source control
      2m 9s
    2. Setting up Team Foundation Server source control
      3m 5s
    3. Using Team Foundation Server source control
      8m 46s
  13. 17m 31s
    1. Understanding the .NET Office integration
      4m 16s
    2. Making a Word 2010 application
      7m 54s
    3. Making an Excel 2010 add-in
      5m 21s
  14. 31m 34s
    1. Understanding the extensibility model in Visual Studio
      2m 17s
    2. Adding external tools to the Tools menu
      4m 42s
    3. Creating macros
      7m 16s
    4. Using the Extension Manager
      5m 1s
    5. Creating an MEF add-in
      7m 9s
    6. Deploying and installing an add-in with VSIX
      5m 9s
  15. 25m 34s
    1. Working with configuration files
      5m 37s
    2. Using the Settings Editor
      7m 30s
    3. Using the Resources Editor
      6m 59s
    4. Localizing your resources
      5m 28s
  16. 1m 17s
    1. Goodbye
      1m 17s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ .

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Join now Already a member? Log in

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Visual Studio 2010 Essential Training.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member ?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferences from the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Learn more, save more. Upgrade today!

Get our Annual Premium Membership at our best savings yet.

Upgrade to our Annual Premium Membership today and get even more value from your lynda.com subscription:

“In a way, I feel like you are rooting for me. Like you are really invested in my experience, and want me to get as much out of these courses as possible this is the best place to start on your journey to learning new material.”— Nadine H.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.