Entering ASP.NET World - Part 2: Creating Data Storage Layers using SQL Server 2000
Mike Amundsen
October 2003
Suitable for: Microsoft? ASP.NET Microsoft? Visual Studio? Net Microsoft? SQL Server? 2000
Summary: Learn how to use SQL Server 2000 and Visual Studio .NET 2003 to create a data storage layer for the DotNetk ASP.NET solution. In addition, security issues related to SQL Server, IIS, and ASP.NET are also discussed.
Download jumppart2sample.msi. (Note that in the sample file, the programmer's annotation is used in English. In this article, it is translated into Chinese to facilitate the reader's understanding.)
table of Contents
Introduction Use Visual Studio .NET 2003 Creating Database Charts Using Visual Studio .NET 2003 to write stored procedures IIS, ASP.NET and SQL Server security issues Create an ASP.NET test page to generate source code and installation script summary
Introduction
In the beginning of "Entering ASP.NET World" (entering ASP.NET World - Part 1: Application Planning and Design), we discussed the basic planning and design knowledge called DotNetkb's ASP.NET solution. This section will discuss in detail how to create a data storage layer using Microsoft? SQL Server® 2000 and Microsoft Visual Studio .NET 2003. These include creating a database (add table, index, constraint conditions, and relationship) and writing a stored procedure for accessing data. At the same time, we will also discuss security issues related to SQL Server, Internet Information Server (IIS) and ASP.NET. At the end of this section, we will get a fully functional and secure data storage system for DotNetkb solutions.
Create a database diagram using Visual Studio .NET 2003
One of the many advantages of Visual Studio .NET 2003 is that users can use them as the primary SQL Server editor to complete most tasks. After obtaining the appropriate permissions of the target database server, you can easily create a variety of databases, tables, index, constraints, relationships, views, stored procedures, and feature of Visual Studio .NET 2003. Visual Studio .NET provides a default database project for you to complete the above operations, as well as a large number of templates for creating a table, a trigger, a stored procedure, etc., which is very easy to use. Finally, because Visual Studio .NET is used as the editing environment of SQL Server, you can also use it to store all SQL Server scripts to Microsoft Visual SourceSafe? This is very convenient for sharing projects and other situations that require long-term maintenance.
Reference user program
For item DotNetkb in this series, I created more than 30 user programs to identify the tasks required to support the application summarized by these Articles Part 1. We will use these user solutions to identify the data stored in the table and the stored procedure executed when it managed this data. Below is a list of some user solutions. A complete list can be found from the related support site User Scenarios for DotNetkb Project (English).
View problems lists in keyword (alignment by date) View the list of questions Sort by date (aligned by date) View a list of questions sorted by topic (alternatively sequential order in the subject's alphabetical order / problem) to view a certain Specific topic's problem list (aligned by date) View problems with no answers (aligned by date, alignment) View the problem count View unliquisounted problem count View a specific topic problem count View the problem of a signage View a question and its answer list (in the answer dates to reverse] Adding a new problem Editing an existing problem and its related answers If you see, there are many tasks involving just a problem in the list. And you also need to handle answers, topics, and expert records. In actual work, this is just beginning. First, you must identify data elements (problems, answers, subjects, and experts) that need to be stored for each record, also need to organize the results in the form of the SQL Server in the form of a table. Create a database project
First open Visual Studio .NET 2003 and create a new database project. The type of Visual Studio .Net database project is currently not sure, because it is hidden in the list of options when you start a new project. But after starting, I think you will find that there are many advantages in the type of database project, so it is worth a cost-effective to grasp them.
To create a new database project using Visual Studio .NET, you need to complete the following tasks:
Start Visual Studio .NET, if the new project dialog does not appear automatically, select File from the main menu -> New -> Project. When the New Project dialog is displayed, expand the Other Projects folder in the left tree view list, and then click the Database Projects folder. The Database Project template will be displayed on the right side. Type item name in Name: (Name :) Input box. In my example, type DOTNETKB_DATABASE, but you can type anything as needed. Then click the OK button to create an item and open it in Visual Studio .NET. A dialog will appear on the screen, ask where you want to be associated with the project. At this point, click Cancel (Cancel). The next step will create a new database and add it to your project.
Figure 1 shows the appearance of the item in Visual Studio .NET when you create a project.
Figure 1: Create a new database project
Once you have created the new database you want to use, you can create a connection between the database and your item to operate in Visual Studio .NET 2003. To this end, you need to complete the following tasks:
In the Solution Explorer window, expand your DotNetk_Database item to display the Database References item. Right-click on the Database References item and select New Database Reference ... (New Database Reference ...), open the Data Link Properties dialog box from the context-related menu. Enter the name of the database server you add DOTNETKB database, then enter your login credentials and select DotNetkb from the drop-down menu. Click the OK button to add the reference to your project. Figure 2 shows the appearance of the dialog box when the above operation is completed.
Figure 2: Data Link Properties dialog
At this point, the database is created and added to your project reference. Next, define the table needed to store DOTNETKB solution data.
Define database tables using Visual Studio .NET
The easiest way to define the database table in Visual Studio .NET is to create a database diagram. This way, you can define all details in an editor similar to "I have obtained". You only need to expand the corresponding tree in Server Explorer, right-click on the Database Diagrams item, then select New Database Diagram from the context-related menu (New Database .. , You can start a new blank figure. When you start a picture for the first time, the system will ask you to select a table from the database. Because you have not created any table, you can ignore the dialog. Now you can start defining your table.
The user program documentation generated in this series of Articles 1 contains the information required to define the table. Learning Definition How to add new records in the system, usually understand which data needed to store the best way to store. Sometimes you need to see other scenarios such as record updates or even reports to make sure there is no missing field. In this example, the "Add Record" scheme is a good reference solution.
For example, the following is a scheme for adding a problem:
Add new problem
Add a new problem record to the system, including title, date / time, indicating the topic ID and problem body of the category belonging to the issue. Sometimes you need to provide a problem name and its email address. After adding a new problem, returns a unique integer problem ID to the call function.
Important nouns are represented by bold. When reading a scheme, these nouns or table names (for example, "problem record" are a good example) often indicate which data need to be stored. With the above information, you can add a new table to the database diagram and define the columns required. The following example details how to add a table in the database diagram.
Right-click on the figure "Surface" and select New Table ... (New Table ...) from the context related menu. Enter Questions as a table name, then click OK to add it to the figure. In the Questions Table dialog box, type information provided in the scheme described above. For example, column name = ID, DATA TYPE = INT, LENGTH = 4, and deselects the Allow Nulls check box. The above operation is repeated to the rest of the table (see Figure 3). Figure 3: Questions Table You will see that there is a small gold key next to the first column (ID). It indicates that the field is the primary key field of the table. To set the primary key field, right-click on the column name in the list and select Primary Key from the context-related menu. In addition, this ID field should be set to automatically increase the identity column in an incremental manner. In this way, SQL Server can automatically generate a unique integer value for each record added to the table. To do this, click the mouse button on this column, select Properties from the context-related menu, and then select the ColumnS tab in the Property Pages dialog. Other operations are easy (see Figure 4).
Figure 4: Columns tab in the Property Pages dialog
Use the information in the Add Topic Record and Add Answer Record to create two other tables. Be sure to create an ID column for each table and mark these columns as identity columns and primary keys. Figure 5 below shows three completed tables.
Figure 5: Three completed tables
You will find that these tables are connected to the database diagram through the cable. These connecting lines indicate foreign key relationships between the tables. For example, the TopiciD column in the Questions table is associated with the ID column in the Topics table. By storing this relationship into the database, you can develop rules for preventing illegal data in the database. The relationship rules in this example are that the valid values allowed by the Questions.topiciD column can only be the value of a record that exists in the Topics.ID column.
You can define these relationships by dragging the TopiciD column in the Questions table to the ID column in the Topics table. A dialog box will appear, display the details of the rule definition, and ask you to press the OK button to confirm (see Figure 6).
Figure 6: CREATE RELATIONSHIP dialog
You may find that a relationship is also defined between responses.questionid and quintions.
Note: You may have noticed that we have not defined any tables for experts. I decided to store information about experts in an XML file instead of being stored in the database. The main reason for this is that we can discuss how to read and write XML data in order to fuse the XML data and relational data in the same application. We will discuss issues related to expert data in the next section. At this point, the database and table have been defined. The actual data stored procedure for the solution is described above. However, we also need to know how to read and write information in the table. To do this, we will define the stored procedures in SQL Server.
Write a stored procedure using Visual Studio .NET 2003
Data tables define how to store data in the database, but not explain how to access data. We also need to understand write records to call the details of the selected line and column again from the table. Developers typically write some special query statements in their code for reading and writing data. This will not only lead to low efficiency, but also provide security issues. In this application, all data access work will be processed through the SQL Server stored procedure (Stored Procedures, sometimes referred to as "Stored Procs" or "SPROCS"). Using a stored procedure can improve the performance of the solution and make it safer. In addition, the use of stored procedures can increase the abstraction level of the data layer, so that other parts of the solution are not affected by small data layout and format changes. This allows your solution more reliable and easier to maintain.
Why not use special query statements
We often see the following articles and code examples:
Private Function GetSomeData (ByVal ID As Integer) As SqlDataReader Dim strSQL As String strSQL = "SELECT * FROM MyTable WHERE ID =" & ID.ToString () cd = New SqlCommand With cd .CommandText = strSQL .CommandType = CommandType.Text .Connection = cn .connection.open () returnavior.closeconnection end with end function
There are several reasons why the above code does not meet the requirements. First, if you nested the SQL query statement in the code, then you must edit and recompile the code layer as long as the data layer changes. This will bring a lot of inconvenience. It may also cause other errors, and usually cause confusion between data services and code.
Secondly, if you use a string connection that is not entered ("... where id =" & id.tostring ()), you may make your application under hacking attack. More importantly, this will provide malicious users with opportunities to add other SQL keywords in your code. For example, according to your input mode, the malicious user can input 13 or 21 as a valid table ID, but also input 13; delete from users, or other statements that may cause hazards. Perfect input verification protects your system from the attack of most SQL insertion code, so it is best to completely delete all built-in SQL statements, so that attackers are difficult to abuse your application data.
Finally, the execution speed of the built-in SQL statement is much slower than the storage process. When you create a stored procedure and store it into a database, SQL Server evaluates them and stores them in an optimized form, making it easier to run for SQL Server at runtime. If you use a built-in special query statement, you must perform this evaluation before each run. For those applications used by users, you may need to conduct hundreds of evaluation of the same query statement per minute. Instead, the stored procedure can maintain the simplicity of the code, and additional security can be provided and can improve the performance of the solution. These are the reasons for discarding the built-in query statement and the use of stored procedures.
Add the stored procedure to the Visual Studio .NET database project
Creating a stored procedure using Visual Studio .NET 2003 is very simple. First, you need to open a database project. This operation has been completed in the first part of this article. You can then create a stored procedure using the Code Template, or you can use the Visual Studio .NET 2003 to edit new stored procedures using Visual Studio .NET 2003 for databases. This article focuses on how to directly edit the stored procedures directly for the connected database server. Take later how to generate all result scripts for future remote server installations.
Introduction Before using the Mechanism of Writing a stored procedure using Visual Studio .NET 2003, it is also important to emphasize several general issues related to creating a reliable stored procedure. First, it is best to see the entire process of creation and execute the stored procedure as a mature member of the multi-layer application model. The stored procedure provides a method of programming your data access. This allows you to better control the entire solution and improve its efficiency. That is, the stored procedure collection should be regarded as a separate layer in the application. Excellent data access policies should allow stored procedures to exist as separate components. That is, the details of security, error handling, and other constituting excellent component layers are required in the stored procedure. More importantly, the T-SQL language should be accessed like in other advanced programming environments, rather than just ways to generate a database query.
Note: Now, I suspect that some readers may think that they don't intend to program SQL Server, or that this work is best to remain to those DBAs. Although the experience of the database administrator will help, it must be a rocket scientist (here the skillful programming expert) can do SQL Server programming work well. Like other languages, this language also needs to spend a certain amount of time and mastery through certain practices, which is not too different from other languages. If you can program in Microsoft Visual Basic? NET, you can program in T-SQL.
Add a stored procedure using Visual Studio .NET
The following detailed describes how to add the stored procedure to the existing SQL Server database in Visual Studio .NET 2003. You need to open a new stored procedure template using the Server Explorer to edit, and then save it to the database. Here is an example of step-by-step implementation:
Open Visual Studio .NET and open an existing database item (such as projects starting in front of this article) or start a new project. In Server Explorer, expand the Data Connections tree, find the database you want to use, and then right-click on the Stored Procedures node to open the context-related menu. Select New Stored Procedure from the context-related menu, open a stored procedure template in the Visual Studio .NET editor space. Now you can type content. After completing the editor, simply close the page being edited in the editor, Visual Studio .NET saves the content to the database using the name of the stored procedure. If the type of content is incorrect, the editor reports these errors to you, and you can fix these errors before saving the stored procedure (see Figure 11). Below is a simple example of the stored procedure, which returns a topic list.
Create Procedure TopicsGetList As Set NoCount ON - Does not return a value of the affected line Select ID, Title, Description from Topics Order by title return @@ error
In this example, there is a need to point out. First, please pay attention to the Set NoCount ON line. It tells SQL Server to stop the number of rows that are affected to this query and stop to return to the calling function. This is an unnecessary additional work. Second, Return @@ Error at the end is important. This row code returns an integer value that occurs in SQL Server. You can use this code to complete other diagnostics and error handling operations in the call routine. You don't need to perform any action now, but they are two habits that should be followed when creating a stored procedure.
Here is a more complex stored procedure. This process is used to retrieve a single topic record from the database. You will find some additional items, including input parameters, return the output parameters of a particular value, and check the input parameters and return some of the program code that is incorrect when needed.
Create Procedure TopicsGETITEM (@admincode char (3), @id int User if @adminCode <> 'adm' begin return 100 - invalid admin error END - Check if the record is existing if (Select Count (ID) from topics where id = @ id) = 0 begin return 101 --- invalid ID code END - Continue and return to the record select @ title = title, @ description = description from topics where id = @ ID - Return to errors, return 0 return @@ error
In this example, there are still some need to point out. First, you will see a list of parameters at the top of the stored procedure. In addition to the first two parameters, other parameters are marked as OUTPUT parameters. These parameters are used to return the value of the selected record. The return value using a record is more efficient than the record collection with the returned with all fields.
Second, you will find the T-SQL data block used to check the @adminCode parameter value to ensure that the correct code is passed. If the passed the code is incorrect, the return code 100 is passed and the process is stopped. First, you will find the check @ID parameter to make sure it represents an existing record. If it is not an existing record, the return code 101 is transmitted and executed. Finally, if the input variable is valid, the stored procedure will attempt to select the record and return the corresponding value. If any errors occur, the last row code of the process will be processed.
Note: Usually, it is best to save custom error code and its meaning in a separate table in the database, or saved in a text file that can be accessed. This will easily update these error codes and share with other subsystems in the solution. Because this is just a short example, only two error codes are used, so I decided to create a document containing a lot of code and messages for other subsystems.
The stored procedure contained in this solution exceeds 25. This article only gives an example, and other code can be downloaded by links starting at this article. Last this example uses a custom built-in scalar function.
Use custom scalar functions
Sometimes a single stored procedure is not enough to solve the problem. For example, there is a program request to list a solution number of questions in our user program. One way to solve this problem is to generate a sub-query that counts questions. Another method is to generate a custom function, return scalar values and contain it in a problem query. This approach has a benefit, that is, we can use the scalar function again during other stored procedures.
The operation of adding custom functions is similar to the addition of stored procedures. In the Server Explorer tree, right-click on the Functions node of the selected database and select New Scalar-Valued Function from the context-related menu. The document is then edited in the editor and saves the document as saving the stored procedure.
The following is the code for custom functions:
CREATE FUNCTION dbo.fn_QuestionsGetResponseCount (@ID int) RETURNS int AS BEGIN DECLARE @ResponseCount int Set @ResponseCount = (SELECT COUNT (Responses.ID) FROM Responses WHERE Responses.QuestionID=@ID) RETURN @ResponseCount END
The following is a stored procedure using a custom function:
CREATE PROCEDURE QuestionsGetCountWithNoResponses (@Total int OUTPUT) AS SET NOCOUNT ON - does not return the value of the affected rows SELECT @ Total = Count (ID) FROM Questions WHERE dbo.fn_QuestionsGetResponseCount (Questions.ID) = 0 RETURN @@ ERROR
Understand how to write a stored procedure and custom functions, we will also discuss another problem when using Visual Studio .NET 2003, that is, security issues.
Security issues for IIS, ASP.NET and SQL Server
SQL Server, Internet Information Server and ASP.NET engines provide a solid security model that works well together. In order to ensure the security of user data and applications, Microsoft also sets a quite low value for the default settings for each service. Most developers face the challenges to set the appropriate trust level between applications and data between applications and data without leaving SQL Server, IIS, and ASP.NET, without leaving security vulnerabilities that can be easily saved by others. Since the three types of services (SQL Server, IIS and ASP.NET), three key steps need to be taken to ensure the security of the solution. This section discusses a more common (and reliable) method for setting sufficient permissions and trust levels for web applications. Note: This series of articles in this series is difficult to launch a sufficient discussion about security and Web solutions. To better understand this issue and possible solutions, see the Security ASP.NET application creation mode and practical series: Verify, Authorization, and Secure Communication.
Define DOTNETKB Custom IIS User Account
To ensure that the security of Web application security is to define a limited user, configure the IIS, enabling it to run as a custom user when performing your web application. This is quite easy to implement, ensuring that each visitor access to your web application has only the permissions you want to have.
The first step is to generate a new Windows user (hereinafter referred to as DOTNETKB), set a enhanced password for it, and then add it to the WINDOWS Group (Guest Windows Group). At the same time, make sure the Password Never Expires (password never expiration) and user cannot call password (user cannot change the password) check box. This will generate a limited user. When you run your web application in IIS, you can use it as an identity (see Figure 7).
Figure 7: User for generated permissions
Then, call the Internet Information Server Administrator and select the web application that hosts these pages. In this example, you can choose the web application (dotNetkb_website) that carries the test page generated by the above. Right-click on the web application in the tree view and select Properties ... (Properties ...) from the context-related menu. Then select Directory Security (Directory Security) and click the Edit button in the dialog Anonymous Access And Authentication Control. Finally, enter a custom username (DotNetkb), deselect the Allow IIS to Control Password check box, and enter the password of the custom user account. After all of these work, click the OK button to save these changes into the IIS Configuration Database (see Figure 8).
Figure 8: Authentication Methods dialog
At this point, IIS will run under a custom account for a limited permission. Any visitor accesses the web page of your application, it will run as this custom user, and only the authentication permission of the custom user.
Authorized DOTNETKB user account access SQL Server
You will then need to grant the custom user to access the corresponding permissions of the DOTNETKB. To do this, you can use the Microsoft SQL Server Enterprise Manager or write a custom script to create such a user and grant it to access the specific object. This article describes how to use the SQL Server Enterprise Manager to complete this. You can also see a script example from later. Note: Although Visual Studio .NET 2003 has many powerful integration functions compatible with SQL Server, users and user permissions are easily managed from Visual Studio .NET 2003. In large organizations and teams, these advanced tasks are usually done by the database administrator.
Therefore, after starting the SQL Server Enterprise Manager, you can add the custom user (see Figure 9) as follows:
In the tree view on the left, the node is expanded to display the DOTNETKB database. On my computer, the structure of the tree view is as follows: console root | SQL Server Group | (LOCAL) (Windows NT) | Databases | DotNetkb. Then, right-click on the User node under the database and select New Database User ... (New Database User ...). Show Database User Properties - New User When dialog box, select
Then you need to add execution permissions for all stored procedures and custom functions in the DotNetkb database. To do this, you just grant permissions for the public (public) role. You can grant permissions to DotNetkb users, which will make future logins (when these users get access to DotNetkb permissions), it is easier to perform stored procedures without having to add new permissions for each user.
Below is the step of granting execution permission for stored procedures and functions in the DOTNETKB database:
Highlight the Users node under the DotNetkb database in the tree view to display the user list of this database. Find DOTNETKB users and double-click on it, open the Database Users Properties dialog. Highlight (selected) PUBLIC (public) role, click the Properties ... button to open the Database Role Properties dialog. Then click the Permissions ... button, display the database object and permissions list. After selecting the public (public) role in the top of the dialog top Database Role, find all stored procedures and custom functions defined for this database (you may need to expand the dialog to see the full name), and make sure to select EXECUTE (execution) check box next to each item. You may find some other check boxes for some system objects, please do not change these options. Finally, after setting all the Execute permissions, click the OK button to save the changes and close the dialog. Click the OK button in turn until all dialogs are turned off. At this point, you have created a custom user for IIS and sets the corresponding permissions of the user in SQL Server. Now you need to make a configuration change in the ASP.NET Web project, make sure that ASP.NET performs all calls to SQL Server using the same user account.
Set your ASP.NET application to simulate DOTNETKB users
The last step in which the ASP.NET Web application running under IIS is: Configure the ASP.NET web application to accept Windows user IDs from IIS and can be used to access other operating system resources. To do this, you just need to enter a line of code in the web.config root file.
Note: Although we have not currently developed an ASP.NET web application that carries our pages, you can use this information to verify the functionality of the data access layer in the next section of the test page.
The modified web.config file is as follows:
Note that you only need to add
At this point, you have generated a custom user and set corresponding permissions to access SQL Server and IIS. Now let's create some test pages to make sure it works fine. From here you can see everything is normal.
Create an ASP.NET test page
Creating a test page is always a good way to access the SQL Server data layer and verify that the input and output parameters are properly processed. In fact, this is the only way to ensure that ASP.NET pages and components in future production solutions can work in accordance with expected ways. This is especially true for verification trust boundaries and security issues when calling other layers from a solution. In addition, do not stick to the creation of production interfaces when testing. You only need to test the target method. In fact, deliberately created some "ugly" test pages you don't want to finally produce the final production solution is a good strategy! In this article, I created some very simple ASP.NET pages, which contains a test list and a input form for adding, editing, and deleting test records.
For example, the following is a WebForm layout for testing the subject record. You will find that it contains the status label of the error message or other message, records the count tab, the data grid of the record list, the input box for inputting the record ID used in the search, and the patch that supports the addition, editing, and delete records (See Figure 10).
MarginWidth = "0" marginheight = "0" src = "/ china / msdn / archives / library / dnaspp / images / aspnet-jumpinto-part2-10.gif" frameborder = "0" width = "100%" height = " 596 ">
Figure 10: WebForm layout for test theme record
When you create a test page, it is best to make the code clear. I usually add a small code to each button to call the local method to process database operations. The following is the code of the Get Record button on the Topictest.aspx page.
Private Sub btnGetTopic_Click (_ ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnGetTopic.Click Try Dim ID As Integer = Int32.Parse (txQueryID.Text) GetItem (ID) 'database call txID.Text = TXQueryid.text txtitle.text = mtitle txdescription.text = mdescription lbstatus.text = "Success!" catch exception lbstatus.text = EX.MESSAGE END TRY END SUB
Note that the only operation actually executed in this method is to be processed by the GetItem (ID) method. It performs database calls and sets local variables using the returned values. The following is the code of the GetItem method. Note that it uses a lot of SQLParameter objects to process input and output values.
Private Sub GetItem (ByVal ID As Integer) Try pr = New SqlParameter ( "RETURN_VALUE", SqlDbType.Int) pr.Direction = ParameterDirection.ReturnValue Dim pTitle As SqlParameter = New SqlParameter With pTitle .Direction = ParameterDirection.Output .DbType = DbType. String .ParameterName = "@Title" .Size = 30 End With Dim pDescription As SqlParameter = New SqlParameter With pDescription .Direction = ParameterDirection.Output .DbType = DbType.String .ParameterName = "@Description" .Size = 500 End With cd = new SqlCommand With cd .CommandText = "TopicsGetItem" .CommandType = CommandType.StoredProcedure .Parameters.Add (new SqlParameter ( "@ AdminCode", "adm")) .Parameters.Add (new SqlParameter ( "@ ID", ID)) .Parameters.add (ptitle) .Parameters.add (pdescription) .Parameters.Add (pr) .connection = cn .connection.Open () .ExecutenonQuery () .connection.close () end with 'Check Return code if not Prism .Value is nothing the select copy int32.parse (pr.value) Case 100: throw new applicationException ("Access Violation") CASE 1 01: Throw New ApplicationException ( "Invalid ID") End Select End If 'return value is set mTitle = pTitle.Value.ToString () mDescription = pDescription.Value.ToString () Catch ex As Exception Throw New Exception (ex.Message, ex Another important aspect of the END TRY End SubgetItem method is to use the return value parameters. It performs a declaration in the first few lines and checks after performing the stored procedure. Please note that I checked the known error code 100 and 101. For other errors, we will introduce how to create a mature intermediate layer later. The problem is that I want to use the return value and throw a custom exception when needed.
For the example of this solution, I eventually generated six web forms and tested nearly 30 stored procedures and custom functions. You can find all of these completed forms in the download packages listed in this article.
Now we have defined a table, create a stored procedure and function and generate an ASP.NET Web form, so you can use Visual Studio .NET 2003 to generate a database layer installation script. Database administrators (sometimes yourself) can apply this script to the production server. Generate source code and installation scripts
Another important feature of Visual Studio .NET is that it can generate a complete generated script for existing databases. In fact, you can use Visual Studio .NET to generate source code for the entire database layer (including generating tables and indexes, authorization, stored procedures, etc.), and generate a command line that can be used to install these database objects on existing SQL Server script.
Generating installation scripts is very easy, it includes two steps: First, you need to generate a T-SQL script to create a database object (table, index, process, etc.). Then, generate a command line script for performing the T-SQL script for the target SQL Server.
Generate T-SQL scripts
Before you generate the installation script, you need to generate a script collection, including all objects (tables, indexes, constraints, users, etc.) in the database.
MarginWidth = "0" marginheight = "0" src = "/ china / msdn / archives / library / dnaspp / images / aspnet-jumpinto-part2-11.gif" frameborder = "0" width = "100%" height = " 596 ">
Figure 11: Generating script collection
The following is the step of generating a T-SQL script:
In Server Explorer, right-click on the selected database node (DotNetkB), then select Generate Create Script from the context related menu, open Generate. CREATE Scripts dialog box, Generate Create Scripts. On the General tab, select the Script All Objects check box. On the Formatting tab, select all check boxes other than the last check box (only a feature that is compatible with a version 7.0 script). The last item is required when your target server is SQL Server 7.0 instead of SQL Server 2000. On the Options tab, all options other than Script SQL Server Logins are selected in the Security Scripting Options section. Make sure that all check boxes in the Table Scripting Option section are selected. Also reserved the default radio button for File Format and Files to Generate. Finally, when all settings have been set correctly, click the OK button to start the script generation process. You will prompt you to specify the file location. By default, Visual Studio .NET will point to the CREATE Scripts folder in an existing database project. Click the OK button to accept this default location. Once this process is complete, you will get a list of files that save each object in the database. This script collection also contains scripts for creating a corresponding user and granting correct permissions. You can even save this information into the Visual SourceSafe to handle future version issues. Finally, you can transfer these files to others as needed, allowing them to update or change these files directly. At this point, the full source code of the database layer has been completed.
Generate installation scripts
The last step is to let Visual Studio .NET 2003 generate a command line script that reads all T-SQL scripts and runs these scripts based on the target SQL Server. To this end, you need to complete the following steps.
In the Solution Explorer (Solution Explorer), right-click on the project name (DotNetkb), then select Create Command File from the context related menu, turn on Create Command file. (Create a Command File) dialog. If you need, you can update the Name of Command File, then select the appropriate verification scheme (unless you need a remote connection server, please 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 a script. This allows the full script to the Editor window (see Figure 12), you can check the script in this window, and the changes will be saved when you close the window. MarginWidth = "0" marginheight = "0" src = "/ china / msdn / archives / library / dnaspp / images / aspnet-jumpinto-part2-12.gif" frameborder = "0" width = "100%" height = " 620> Figure 12: Generate the installation script Use this script and T-SQL file set, now you can install the new database layer to any target SQL Server 2000 with the appropriate permissions.
summary
This section discusses a lot of content. Including how to create database items using Visual Studio .NET 2003, how to create new databases, and definition tables, index, constraints, and relationships of database diagrams, also introduce stores that use Visual Studio .NET 2003 to add data stored in the table. Procedure and custom functions. Through this study, you have learned how to use the Web.config file settings in the IIS and web applications, build a reliable trust relationship between SQL Server and your ASP.NET solution. . This article finally introduces the test web form example used to verify the design of the data layer program, and explain how to generate the T-SQL script and command line script that can be used to install this data layer installed on any target SQL Server.
Maybe you have noticed that the database layer does not involve the storage and recall process of the expert record. This is because I decided to use the XML file instead of it. This way, we can 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 layers that read and write this data, and will learn about XML serialization and the built-in data cache services in the ASP.NET.
Note: You can access
DotNetkb (English) Web site is always available in this project's latest information. Before I publish parts on the MSDN?, I will post the feedback and other information of the update content, reader opinions at this site.
