Visual Studio.NET Database Projects
- May 23, 2003
- Sample Chapter is provided courtesy of Addison Wesley
This chapter is from the book
Database Access with Visual Basic® .NET, 3rd Edition
Command Files
Now that you have created all these scripts to create and modify the different database objects, wouldn't it be nice if you could organize multiple scripts into a logical group to be run as a single unit? Yes it would be, and VS.NET can create command files to do just that. These command files, which have the .cmd extension, are meant to be used on the Windows 2000 or Windows XP operating systems, which recognize and can execute such files. These files can also load a newly created table with data that we exported from an existing database.
NOTE
The ability to easily and automatically create a script that loads table data in addition to creating database schema and objects is a VS.NET feature not found in
Let's say that we want to create a single command file that will automatically run all the Create Scripts that we need to create a brand new version of our Novelty database on another computer. Although this new system will have its own customers, employees, and orders, the inventory information in tblInventory will be the same. We therefore want to populate the new database's tblInventory table with the data currently in our existing tblInventory table.
Because you will want to have the command file load the inventory data from the existing database to the newly created one, you must first export the data and then continue with the process of creating the command file, as follows.
In the Server Explorer, right-click on tblInventory and select the Export Data menu item from the context menu displayed.
The Browse for Folder dialog box appears and defaults to the Create Scripts folder of the database project. Click on the OK button to accept this folder.
After proceeding through the SQL Server Login dialog, the script dbo.tblInventory.dat is created.
Decide which folder in the database project to use to store the new command file. Again use the Create Scripts folder.
In the Solution Explorer, right-click on the Create Scripts folder and select the Create Command File menu item from the pop-up menu. Doing so displays the Create Command File dialog box shown in Figure 8.11.
The Available Scripts list of the Create Command File dialog box contains all the SQL scripts in the selected folder that can be included in the command file. You can add all the scripts, or just individual ones, to the list of Scripts to be added to the command file. Click on the Add All button to add all the Create Scripts to the command file.
Because at least one Create Table Script (with the .tab extension) was included in the list of scripts to be added to the command file—and there is at least one exported data file in the folder—the Add Data button is enabled on the Create Command File dialog box.
Click on the Add Data button to display the Add Data dialog box shown in Figure 8.12. This dialog lists all the Create Table Scripts that were selected to be added to the command file and allows choosing the corresponding data file for each script.
Figure
8.12 The Add Data dialog boxThe dialog recognizes and automatically matches the tblInventory data file with the script that creates the tblInventory table. Click on OK to return to the Create Command File dialog box.
Now that the scripts and the exported data files have been specified, click on the OK button to generate the command file. The Create Scripts.cmd command file is added to the Create Scripts folder and its contents are displayed, as shown in Listing 8.1.
)
Figure
8.11 The Create Command File dialog box for the Novelty database.
)
Figure
8.12LISTING 8.1 The contents of the Create Scripts.cmd command file
@echo off REM: Command File Created by Microsoft Visual Database Tools REM: Date Generated: 08-Feb-02 REM: Authentication type: Windows NT REM: Usage: CommandFilename [Server] [Database] if '%1' == '' goto usage if '%2' == '' goto usage if '%1' == '/?' goto usage if '%1' == '-?' goto usage if '%1' == '?' goto usage if '%1' == '/help' goto usage osql -S %1 -d %2 -E -b -i "dbo.tblCustomer.tab" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblDepartment.tab" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblEmployee.tab" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblInventory.tab" if %ERRORLEVEL% NEQ 0 goto errors bcp "%2.dbo.tblInventory" in "dbo.tblInventory.dat" -S %1 -T -k -n -q if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblOrder.tab" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblOrderItem.tab" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblCustomer.kci" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblDepartment.kci" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblEmployee.kci" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblInventory.kci" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblOrder.kci" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblOrderItem.kci" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblCustomer.fky" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblDepartment.fky" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblEmployee.fky" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblInventory.fky" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblOrder.fky" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblOrderItem.fky" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblCustomer.ext" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblDepartment.ext" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblEmployee.ext" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblInventory.ext" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblOrder.ext" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.tblOrderItem.ext" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.Employee_view.viw" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.EmployeeDepartment_view.viw" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.DeleteEmployee.prc" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.GetCustomerFromID.prc" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.InsertEmployee.prc" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.InsertEmployeeOrg.prc" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.LastNameLookup.prc" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.procEmployeesSorted.prc" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.SelectEmployees.prc" if %ERRORLEVEL% NEQ 0 goto errors osql -S %1 -d %2 -E -b -i "dbo.UpdateEmployee.prc" if %ERRORLEVEL% NEQ 0 goto errors goto finish REM: How to use screen :usage echo. echo Usage: MyScript Server Database echo Server: the name of the target SQL Server echo Database: the name of the target database echo. echo Example: MyScript.cmd MainServer MainDatabase echo. echo. goto done REM: error handler :errors echo. echo WARNING! Error(s) were detected! echo ———————————————— echo Please evaluate the situation and, if needed, echo restart this command file. You may need to echo supply command parameters when executing echo this command file. echo. pause goto done REM: finished execution :finish echo. echo Script execution is complete! :done @echo on
NOTE
The command file makes use of the osql and bcp command line utilities that are part of the SQL Server installation. The osql utility allows you to execute SQL statements, system procedures, and script files. The bcp is a bulk copy program that copies data to and from a data file and an instance of SQL Server.
You can run this command file from within the Solution Explorer by right-clicking on it and then selecting the Run menu item. You can also invoke it externally, independent of Visual Studio, so long as all the scripts exist together with the command file.
NOTE
Remember that running this command file against a database will delete all the data that currently exists in that database!
Account Sign In
View your cart