Back End Development (Agency) Aquent US-IL-Chicago Post A Job | Post A Resume

Published: Wednesday, July 13, 2005

In Part 1 we examined how to add a new Database project to an existing Visual Studio .NET Solution. In this second and final part we'll look at how to take the objects from an existing SQL Server database and quickly add them to the Database project within the Visual Studio .NET IDE.

Quickly Adding the Database Objects from SQL Server to the Database Project
At this point we have created the Database project, but have yet to add anything. What we want is this database project to have create scripts for the set of objects that makeup the ASP.NET application's associated database - the stored procedures, views, tables, UDFs, jobs, and so on. I am assuming that you already have these database entities created, and that you created them through SQL Enterprise Manager or T-SQL scripts. Moving the objects over from SQL Enterprise Manager to the Database project is pretty easy thanks to SQL Enterprise Manager's 'Generate SQL Script' tool.

Launch SQL Enterprise Manager and right-click on the database whose objects you want to import to the Database project created in Part 1 of this article. From the context-menu, choose All Tasks --> Generate SQL Script. This will display the Generate SQL Scripts dialog box (see the screenshot below). Click on the Show All button to list all of the database objects in the database. Next, move those objects you want scripted over to the 'Objects to be scripted' list by selecting those objects and clicking the Add >> button, or by checking the appropriate 'Objects to script' checkboxes.

Before generating the script, be certain to go to the Options tab and, under the Files to Generate section, opt to 'Create one file per object.' This will create a separate file for each object being scripted; the default option, 'Create one file,' will instead create just one file that contains the script for all of the objects. If you are scripting tables, you may also want to check the checkboxes in the Table Scripting Options section. If you have any special permissions for the various objects you might also want to check the 'Script object-level permissions' option as well.

Finally, click the OK button to generate the SQL script. This will create one file for each object selected from the General tab. The final step, then, is to import these script files into the Database project. To do that, return to Visual Studio .NET, right-click on the Database project folder you want the object to reside and choose to Add Existing Item. Then browse to the directory where you saved the scripted objects and add the appropriate script files.

When you need to create new database objects you have the option of either creating a new file in the Database project and writing the necessary T-SQL or creating the object through SQL Enterprise Manager and then importing it into the Visual Studio .NET Database project through the techniques discussed above. Personally I find myself creating tables and jobs through SQL Enterprise Manager, but Views, UDFs, and stored procedures through the Database project directly.

Consider Scripting the Database Files as Windows Text (ANSI)

Helpful reader Mary H. writes in to share: After setting up the database project and making a change to one of my script files, I tried to do a "compare versions", but VSS told me that the "binary files differ". It wouldn't do a text compare of the two files. The way I got this to work was to go back and recreate the scripts using the "Windows Text (ANSI)" file format option instead of the default unicode format. Don't know if there is a better way to do this or not, but I really can't live without the "compare versions" functionality in VSS and thought I would let you know what I ran across so that you could use the information how you see fit. Thanks again for the helpful info.

Using the Database Project
Now that you have your database's objects included in the Database project, you can edit the objects through Visual Studio .NET, getting the advantages of source control, an integrated development environment, and Visual Studio .NET's superior editing experience. Once you have made changes to a database object you can apply those changes to the underlying database by clicking on the database object in the Solution Explorer and choosing Run or Run On. Run will execute the T-SQL script on the default Database Reference while the Run On option will allow you to choose which database connection to have the script executed. Additionally, from the editor you can select a chunk of T-SQL syntax, right-click on it and choose 'Run Selection' to execute just the selected text.

Conclusion
In this article we saw how to add a Database project to an existing Visual Studio .NET Solution, along with how to import an existing database's objects into the Database project. Managing your database through a Visual Studio .NET Database project affords the following advantages: source control support on database objects; an improved editing experience; the ability to deploy a database's objects to a different database with the click of a mouse; and a centralized development experience, where managing both the ASP.NET pages and the associated database objects can be done through a single IDE.

I learned about Database projects nearly two years ago and have been using them ever since. Now I don't know how I'd survive without them! If you've yet to explore Visual Studio .NET's Database projects, I heartily encourage you to check them out and give them a whirl.

Happy Programming!