Create ASP.net data storage layer (7)

Asp.net| Create | Data generation source code and Setup script

Another important feature of Visual Studio. NET is its ability to generate a complete build script for an existing database. In fact, you can use Visual Studio. NET to generate source code for the entire database tier, including making tables and indexes, authorizations, stored procedures, and so on, and to generate a command-line script that can be used to install these database objects on an existing SQL Server.

Building the installation script is easy, it includes two steps: First, you need to generate T-SQL scripts to create database objects (tables, indexes, procedures, and so on). Then, a command-line script is generated that executes the T-SQL script for the target SQL Server.

Generating T-SQL scripts

Before generating the installation script, you need to generate a collection of scripts, including creating all objects in the database (tables, indexes, constraints, users, and so on).


Figure 11: Generating a Script collection

Here are the steps to generate a T-SQL script:

In Server Explorer, right-click on the selected database node (DotNetKB), and then select Generate Create Script from the context-related menu ... (Generate Create Script ...) , open the Generate create Scripts (Generate Creation Script) dialog box.

On the General tab, select the Script all objects (write all object script) check box.

On the Formatting tab, select all the check boxes except the Last check box (features that are compatible with version 7.0 scripts only). The last item is required only if your target server is SQL Server 7.0 instead of SQL Server 2000.

On the Options tab, in the Security Scripting Options section, select all options except the script SQL Server logins (compose SQL Server logon script). Make sure that all of the check boxes in the Table scripting Options (Tables script option) section are selected. The default radio button for both the file format and files to Generate (generated files) is preserved. Finally, when all settings are set correctly, click the OK button to start the script generation process.
　
You will be prompted to specify a file location. By default, Visual Studio. NET points to the Create Scripts folder in an existing database project. Click the OK button to accept this default location.

When the process is complete, you will get a list of files that hold the objects in the database. The script collection also contains scripts for creating the appropriate users and granting them the correct permissions. You can even save this information to Visual SourceSafe for later version issues. Finally, you can transfer these files to other people as needed so that they can update or change them directly. At this point, the complete source code for the database layer has been completed.

Build the installation Script

The final step is to have Visual Studio. NET 2003 generate a command-line script that reads all T-SQL scripts and runs them against the target SQL Server. To do this, you need to complete the following steps.

In Solution Explorer (Solution Explorer), right-click the project name (DotNetKB) and select Create Command File from the context-related menu ... (Create command file ...) Opens the Create Command File dialog box.

If you want, you can update the name of command file (name) input box, and then select the appropriate authentication scheme (unless you need a remote connection server, use Microsoft Windows?). NT?). Finally, click the Add All button to include all T-SQL scripts in the installation operation.

Then, click the OK button to generate the script. This allows the complete script to be loaded into the editor window (see Figure 12), where you can examine the script and the changes will be saved when you close the window.


Figure 12: Build the installation script

Using this script and the T-SQL file set, you can now install the new database tier on any target SQL Server 2000 that you have appropriate permissions for.

Summary

This section discusses a lot of things. Includes how to create a database project using Visual Studio. NET 2003, how to create a new database, and a database diagram that defines tables, indexes, constraints, and relationships, and also describes using Visual Studio. NET 2003 Add stored procedures and custom functions that can access data stored in the table. With this article, you also learned how to use a custom Windows account and Web.config file settings in IIS and WEB applications to establish a reliable trust relationship between SQL Server and your asp.net solution. This article also concludes with an example of a test Web form for validating data-tier programming and how to generate T-SQL script and command-line scripts that can be used to install the completed data tier on any target SQL Server.

You may have noticed that the storage and re-invocation procedures for expert records are not covered in the relevant discussions of the database layer. This is because I decided to replace it with an XML file. In this way, we can take this opportunity to learn how to use XML as a data source and how to combine this data format with SQL Server data to create a complete solution. In the next section, we will define the XML storage format and the component layer that reads and writes this data, and will also learn about XML serialization and the built-in data caching services in asp.net.