Create ASP.net data storage layer (1)

Asp.net| Create | Data summary: Learn how to create a data storage tier for a DotNetKB asp.net solution using SQL Server 2000 and Visual Studio. NET 2003. Also discussed are security issues with SQL Server, IIS, and asp.net.

Brief introduction

In the ASP. NET application planning and design, we discussed the basics of planning and design for a asp.net solution called DotNetKB. This section discusses in detail how to use Microsoft? SQL Server? 2000 and Microsoft Visual Studio. NET 2003 to create a data storage tier. This includes creating a database (adding tables, indexes, constraints, and relationships) and writing stored procedures for accessing data. We will also discuss security issues related to SQL Server, Internet Information Server (IIS), and asp.net. By the end of this section, we will have a fully functional and secure data storage system for the DotNetKB solution.

Create a database diagram using Visual Studio. NET 2003

One of the many advantages of Visual Studio. NET 2003 is that users can use it as a primary SQL Server editor to accomplish most tasks. Once you have the appropriate permissions for the target database server, you can easily create various databases, tables, indexes, constraints, relationships, views, stored procedures, and features by using Visual Studio. NET 2003. Visual Studio. NET provides a default database project for you to do this, as well as a large number of templates for creating tables, triggers, stored procedures, and so on, and is very easy to use. Finally, because you use Visual Studio. NET as an editing environment for SQL Server, you can also use it to store all SQL Server scripts in Microsoft Visual SourceSafe? In This is convenient for shared projects and other situations that require long-term maintenance.

Referencing user Scenarios

For the project DotNetKB in this series, I created more than 30 user scenarios to identify the tasks required to support the applications outlined in part 1th of this article series. We will use these user scenarios to identify the data stored in the table and the stored procedures that are executed at run time to manage the data. The following is a partial list of user scenarios. The complete list can be found from the related support site User scenarios for DotNetKB Project (English).

1. View the list of questions by keyword search (in reverse order by date)

2. View list of issues sorted by date (in reverse order by date)

3, view the list of issues sorted by topic (alphabetical order of the topic/date of issue in reverse order)

4. View a list of issues for a specific topic (in reverse order by date)

5. See the list of questions without any answers (sorted by date reverse order)

6, check the problem count

7. View Unanswered Questions count

8. View the count of questions for a particular topic

9. View the count of questions answered by an expert

10, view a question and its answer list (by the answer date in reverse order)

11, add new problems

12, edit existing problems

13, delete existing problems and related solutions

As you can see, there are a number of tasks in the list that involve only the problem record. And you also need to deal with answers, topics, and expert records. And in practical work, this is just the beginning. First, you must identify the data elements (questions, answers, topics, and experts) that you need to store for each record, and you need to organize the results in a tabular format into a database in SQL Server.

Create a database project

First, you open Visual Studio. NET 2003 and create a new database project. The type of Visual Studio. NET database project is not yet certain, because it is hidden in the list of options when you start a new project. But after you start using, I think you'll find that the database project type has many advantages, so it's worth the effort to master them.

To create a new database project using Visual Studio. NET, you need to complete the following tasks:

Start Visual Studio. NET, and if the New Project dialog box does not appear automatically, choose File (file)-> new (new)-> Project (project) from the main menu.

When the New Project dialog box is displayed, expand the other Projects folder in the left tree view list, and then click the Database Projects folder. The database project template is displayed to the right.
Now, type the project name in the name: (Name:) input box. In my example, you type Dotnetkb_database, but you can type anything you want.

Then click the OK button to create the project and open it in Visual Studio. NET.

A dialog box appears asking you to select the database that you want to associate with the project. At this point, click Cancel (cancel) first. The next step is to create a new database and add it to your project.

Figure 1 shows how the project will look in Visual Studio. NET When you create the project.


Figure 1: Creating a new Database project

After you create a new database to use, you can establish a connection between the database and your project to manipulate it in Visual Studio. NET 2003. To do this, you need to complete the following tasks:

In the Solution Explorer (Solution Explorer) window, expand your Dotnetkb_database project to display the database References (DB Reference) item.

Right-click on the database References item, and then select New Database Reference from the context-related menu ... (New Database Reference ... , open the Data Link Properties dialog box.
Enter the name of the database server in which you added the DotNetKB database, and 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 complete.


Figure 2:data Link Properties (Data Link Property) dialog box

At this point, the database is created and added as a reference to your project. Next, define the tables that are required to store the DotNetKB solution data.