计算机外文翻译样品一SQL数据库

HTTP is a stateless protocol. To allow users save to state information across requests, ASP.NET provides Session storage. The session variables are stored on per-user basis. In ASP classic, you can store session variables only in the Web server's memory. However, this approach proves to be poor in terms of scalability and reliability. In ASP.NET 2.0, however, you can customize the session state store as per your requirement. This article will explore one of the scalable and reliable approaches for storing session variables—SQL Server.

As in classic ASP, by default the session state is maintained in the Web server's memory. However, this approach poses two problems:

It overburdens the server, affecting the Web site's scalability

It cannot be used effectively in Web farm scenarios

Let me discuss these problems in a bit of detail so that you can appreciate your choice of a session store.

Session variables are created on a per-user basis. By default, they are maintained in the Web server's memory. Imagine a Web site with thousands of users. Because of the huge number of users, the number of active sessions on the Web server also will be vary high. That means you are storing too much data in the Web server's memory. If the load on the server keeps of increasing, it may reach saturation and cause trouble for overall scalability of your application.

 

To tackle the issue of scalability mentioned above people, implement Web farms. A Web farm is a cluster of Web serves running in parallel. Each Web server in the cluster has a mirror of your Web site. The traffic of your Web site is equally distributed among the available servers, thus providing load balancing. Storing session variables in the Web server's memory can hamper the Web farm's architecture. Assume that there are three Web servers—S1, S2, and S3—connected in parallel and serving the incoming requests. A request R1 comes into the cluster and the load balancing logic decides that S2 and S3 are busy with some other task, but S1 is free to process your request. Naturally, the request gets forwarded to S1 for processing. Now, imagine that during the processing, you store a session variable in S1's memory. So far, so good. After some time, the same user gives another request, R2, that needs the session variable stored by the previous request, R1. However, this time S1 was occupied with some work and S2 and S3 are free. You would expect that as per the load-balancing rule, R2 should get forwarded to S2 or S3. But, if that happens, how will R2 get access to the session variables? After all, they are stored in the memory of the altogether-separate Web server S1. This means R2 still needs to wait for S1 to become free. This is, of course, a poor use of Web farm resources.

 

In the memory of the Web server (in process)

In the memory of a machine dedicated to storing session variables (state server)

In an SQL Server database

The first mode is the default. Modes 2 and 3 are often called "out-of-process" modes because the session store is independent of the Web site. In this article, you will restrict yourself to exploring the third mode.

 

Scalability: If you are looking for a highly scalable option to store your session variables, the SQL Server option is for you. It is a much more scalable option than the others. Web farm architecture can very easily access the session variables because they are stores in an independent database.

Reliability: Because the data is physically persisted in a database, it is is more reliable than the other options. It has the ability to survive server restarts.

Security: SQL Server is more secure than the in-memory or state server option. You can protect your data more easily by configuring SQL Server security.

The session state mode can be configured via a <sessionState> tag of the web.config file.

In Web farm scenarios, the application path of the Web site in the IIS metabase should be identical in all the Web servers in the Web farm.

Session_End event never fires for any of the out-of-process modes.

Configuring SQL Server to Store a Session State

Before you can actually store a session state in SQL server, you need to configure it. This configuration is done via a command line tool called ASPNET_REGSQL.EXE. You can store the session state in three possible locations within the SQL Server:

Temporary storage: In this case, the session state is stored in the "tempdb" database of SQL Server. The tool creates a database called ASPState and adds certain stored procedures for managing session to it. The tool also creates required tables in the "tempdb" database. If you restart the SQL server, the session data is not persisted.

Persistent storage: The tool creates a database called ASPState and adds stored procedures for managing a session to it. The session state is stored in the ASPState database. The advantage of this method is that the data is persisted even if you restart the SQL server.

 

Custom storage: Both the session state data and the stored procedures are stored in a custom database. The database name must be specified in the configuration file.

The following table lists various command line switches of the tool with respect to session store configuration:

As a developer, you possibly will never interact with the session state database directly. However, it would be nice to have a general understanding of the tables and stored procedures created when you run the ASPNET_REGSQL.EXE tool. Figure 1 shows the tables created by this tool; Figure 2 shows the list of stored procedures.

Figure 1: Tables created in the SQL Server database

Figure 2: Stored procedures created in the SQL Server database

 

Once you configure your SQL server to support session state storage, the next step is to configure your Web site. The <sessionState> tag of web.config allows you to specify information about the session store. The following table lists some of the important attributes of this tag:

To illustrate what you've learned up to now, you can develop a simple Web site and configure it to store a session state in a SQL Server database.

Configuring SQL Server

Open the Visual Studio.NET 2005 command prompt and issue the following command:

aspnet_regsql -ssadd -S .\sqlexpress -E -sstype p

Here, you are using SQL Express as the database with integrated security turned on. Figure 3 shows a sample run of this tool with the session state type set to "persistent".

Figure 3: Sample run of ASPNET_REGSQL.EXE tool

Now, create a new web site and modify the web.config file to have the following markup:

<sessionState

mode="SQLServer"

sqlConnectionString="data source=.\sqlexpress;

integrated security=true">

</sessionState>

Here, you added the <sessionState> tag and set its mode attribute to SQLServer. You also specified the sqlConnectionString attribute and pointed it to your SQL Server.

Creating test web forms

Add two Web forms in the Web site called Page1.aspx and Page2.aspx. The first Web form will display a list of products to choose from. The selected products are stored in a DataSet. The DataSet is then persisted across the requests by storing it in a session variable. The second Web form simply displays the selected products from the DataSet.

To create the first Web form, follow these steps:

Drag and drop a GridView control and SQL Data Source control onto the Web form.

Configure the SQL Data Source control to select the ProductID, ProductName, and UnitPrice columns from the Products table of the Northwind database. (A detailed explanation of using data source controls is out of scope of this article.)

Bind the GridView to the SQL Data Source by setting its DataSourceID property to the ID property of the SQL Data Source control

From the smart tags of GridView, check the "Allow Selection" checkbox.

Add a HyperLink control and set its NavigateUrl property to Page2.aspx.

The following markup is generated as a result of above operations (unwanted markup removed):

...

<asp:GridView ID="GridView1" runat="server"

</asp:GridView>

...

<asp:HyperLink ID="HyperLink1" runat="server"

...

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

Now, add the following code to the SelectedIndexChanged event of the GridView:

protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)

{

   DataSet ds=null;

   if (Session["myds"] == null)

      ds = new DataSet();

      DataTable dt = new DataTable();

      dt.Columns.Add(new DataColumn("ProductName"));

      dt.Columns.Add(new DataColumn("Qty", typeof(System.Int32)));

}

Here, you create a DataSet and a DataTable. The DataTable contains two DataColumns: ProducyName and Qty. You then store the DataSet in a session variable called "myds". When the user selects a product, you create a new DataRow, assign the product details to it, and then add this row to the DataTable. Thus, the user selections are persisted across multiple requests.

To create the second Web form, follow these steps:

Drag and drop a GridView onto the Web form.

Add two bound fields.

Set the HeaderText property of the first bound field to "Product Name". Also, set its DataField property to "ProductName".

Set the HeaderText property of the second bound field to "Quantity". Also, set its DataField property to "Qty".

The following markup is created as a result of above operations (unwanted markup removed):

...

<asp:GridView ID="GridView1" runat="server"

</asp:GridView>

...

Add the following code in the Page_Load event handler of the second Web form.

protected void Page_Load(object sender, EventArgs e)

{

}

Here, you set the DataSource property of the GridView control to your DataSet stored in session. You then bind the GridView by calling its DataBind() method.

Running the sample Web forms

To test your web forms, set Page1.aspx as the start page and run the Web site. You should see something as shown in Figure 4.

Figure 4: Sample run of Page1.aspx

Select a few products by clicking the "Add to cart" button. This will add some rows in the DataTable. Recollect that you are storing your DataSet in a session variable. Then, navigate to Page2.aspx by clicking the "Show my cart" hyperlink. Figure 5 shows Page2.aspx with the previously selected products.

Figure 5: Sample run of Page2.aspx

As you can see, Page2.aspx correctly displays the items you selected on the previous page. This indicates that your session variable was indeed stored in the SQL server database and retrieved on the second page. Also, note that you used the same syntax of storing and retrieving values in the session irrespective of the storage mode.

Disadvantages of Storing the Session State in SQL Server

Though storing the session state in SQL server makes your Web site more scalable and reliable, it has some disadvantages of its own:

Performance: In terms of performance, a SQL Server-based session store is possibly the slowest option. Because your session variables are stored in a physical database, it takes more time to get them in and out of the database. This affects the performance of your Web site.

Cost: Because you are storing your data in a SQL Server database, you need to have a SQL Server license. This can add to overall cost of your Web site.

Serializable data: This method requires that all the data stored in session variables must be serializable. This may force you to mark your own classes as [Serializable] if you want to store them in a session.

ASP.NET 2.0 allows you to store the session state to a SQL Server database. The ASPNET_REGSQL.EXE tool configures the SQL Server to support this feature. Further, the <sessionState> tag configures your Web site to support this mode. Storing a session state in SQL server is a more scalable, secure, and reliable option. However, its performance will be slower as compared to the other storage options.

 

 

 

 

HTTP是个状态很不确定的协议，为了允许用户通过请求保存状态信息，ASP.NET提供了Session存储机制。这些Session变量按每个用户被存储起来。在传统的ASP里，你只能在Web服务器的内存里暂时存储Session变量，但是这个方法已经被证明了在扩展性和可依赖性上的不足。在ASP.NET里，你可以为你的每个请求定制Session状态存储。本文将探讨存储Session变量可伸缩性和可靠性都很好的方式之一的SQL Server 。

在传统的ASP里 ，默认的Session状态保存在服务器的内存中。但是，这种做法带来两方面的问题:

（1）它让服务器超负荷，影响了网站服务器的伸缩性能。

（2）它不能有效地应用于Web服务器群。

让我在一些细节上讨论这些问题，使你能为你选择了Session存储感到庆幸。

Session变量依据每个用户为基础生成。 默认情况下，它们都保留在Web服务器的内存中。想象一个有着成千上万用户的网站。 由于巨大的用户数量， Web服务器存储的活跃Session的数目也将非常的多。 这意味着你存放着非常多的session数据在Web服务器的内存中。 如果不断的对服务器增加负载， 它可能达到饱和，以至造成对应用程序整体扩展性能上的不良影响。

为了解决这个影响到扩展性能的问题，实现Web集群。所谓的 Web集群是一组网络服务器并行运行， 服务器集群里的每个Web服务器都有您的网站的一个镜像。您的网站的流通负载平均分配给每个可用的服务器，从而达到负载平衡。在Web服务器的内存里存储Session变量会阻碍Web集群的建立，下面将举例来说明：

假定有三个Web服务器：S1 ， S2 ，和S3 。并行地连接在一起接受用户请求。 假定这个时候有一个请求R1来到服务器集群并且负载平衡逻辑判定 S2，S3都因为某些其他的任务而没有空闲，但是S1可以处理这个请求。很显然，这个请求会被送到S1进行处理。现在，想象在这个处理过程当中你在S1的内存中存储了某个Session变量。到目前为止，一切还很好。过了一些时间，同样的用户有了另一个请求R2，这个请求需要上一个请求所存储的Session变量。但是这个时候S1已经被一些任务使用着，而S2，S3却处于空闲的状态。你可以猜到根据每一条的负载平衡规则，R2将会被送到S2进行处理。但是，如果那发生了，S2怎么能够得到Session变量？毕竟，它们存储在和S2完全没有任何关联的Web服务器S1的内存中。这意味着R2仍然得等待S1的空闲。这种事情很显然是Web服务器群资源的浪费。

ASP.NET 2.0允许你存储session变量在三个不同地方:

（1）在网站服务器的内存中存储(进程中的)。

（2）在一台专门用来存放Session变量的服务器中存储。( Session状态信息存储服务器 )

（3）在一个SQL Server的数据库里面存储。

默认情况是第一种存储模式。

模式二和模式三通常被称为“进程外”的模式，因为Session变量的存储和Web站点没有关系。 在这篇文章，你将被限制在只探索第三种模式。在SQL Server数据库里存储Session变量具有以下优点:

（1）可扩展性: 如果您正在找一个高度可扩展性的方法来储存Session变量 ，SQL Server正是如此，它比其它的可扩展性更好。 Web服务器群可以很容易地获取Session变量，因为它们都储存在一个独立的数据库。

（2）可靠性:因为数据是很真实的存放在数据库里面，它比其他方法都要好。不用去担心服务器重启的问题。

（3）安全: SQL Server比在内存或Session状态信息存储服务器中更安全。 你可以更简单的通过配置SQL Server的安全选项来保护你的数据。

在web.config 文件里，可以通过定义一个<session State>来配置Session状态模式 。

备注:

如果是在Web服务器群里， Web服务器群的每一台服务器的IIS METABASE的应用程序路径应该是完全相同的。Session结束事件不会因为任何进程之外的模式发生。

 

在你真正的在SQL Server存储Session状态之前，你需要先配置它。 配置工作都是通过一个名为aspnet_regsql.exe的命令行工具来完成的。

在SQL Server里面，你可以在三个可能的地点存储Session状态，

 (1)临时存储: 在这种情况下，Session状态保存在SQL Server的tempdb 数据库里面。 该工具建立一个数据库名为aspstate并增加特定的存储过程来管理要保存的Session信息。 这个工具还创建tempdb 数据库所需的表。 如果您重新启动SQL Server服务，Session信息不会丢失。

 (2)持久存储: 该工具建立一个数据库名为aspstate并增加特定的存储过程来管理要保存的Session信息。 Session状态信息储存在aspstate数据库里。 这种方法的优点是，即使您重新启动的SQL Server 。 Session信息也不会丢失。

 (2)定制存储:Session状态信息和数据存储过程都有用户定制存储。 同时数据库的名称必须在配置文件中声明。

作为一个开发人员, 你可能从来无法直接影响session状态数据库。 然而, 当你运行ASPNET_REGSQL.EXE工具, 你将会对表单和数据库的创建会有一个很好的理解。

配置 1:在SQL数据库服务器中创建表单

配置 2:在数据库服务器中创建存储过程

 

一旦你配置你的SQL服务器来支持session状态存储，下一步就是配置你的网站了。web.config 的标记<sessionState>允许你指定session存储的信息。下列的表中列出了一些这个标记得重要属性：

你可以开发一个简单的网站，设置它来存储一个在SQL服务器数据库中的sesseion状态，来示例你到目前为止所学的东西。

设置SQL服务器

打开Visual Studio.NET 2005命令提示器，使用一下命令

aspnet_regsql -ssadd -S .sqlexpress -E -sstype p

aspnet_regsql -ssadd -S .sqlexpress -E -sstype p

现在，你正在使用带着完整安全属性的SQL Express数据库。

配置 3 展示了一个把带着session 状态类型的工具设置为"persistent"的例子。

现在，创建一个新的网站，修改web.config文件成下列的代码

<sessionState mode="SQLServer" sqlConnectionString="datasource=.sqlexpress;

integrated security=true"></sessionState>

这里，你添加了这个<sessionState>标记，设置它的模式属性为SQLServer。你还要指定sqlConnectionString属性，把它指向你的SQL SERVER。

创建测试网站的WEB窗体

在WEB站点中添加两个WEB窗体。这两个窗体分别叫page1.aspx和page2.aspx。第一个WEB窗体将显示一个产品列表来选择。被选择的产品被保存在一个DataSet中。然后通过保存这个DataSet

在一个session变量中来把它在请求中传递。第二个Web窗体简单的显示了来自DataSet中的被选择的产品。

通过一下的步骤来完成第一个Web 窗体：

 拖拉一个GRIDVIEW控件和一个SQL Data Source控件在WEB FORM上.

 配置SQL Data Source, 从Northwind 数据库的Products 表中选择ProductID, ProductName, 与

 UnitPrice columns

 通过设置他的DataSourceID属性到SQL Data Source控件的ID属性上来绑定GridView到SQL Data Source上.

 在GridView的samrt标记上, 点击"Allow Selection" checkbox.

 添加一个HyperLink 控件,设置NavigateUrl属性到Page2.aspx.

创建一个 DataSet 和 DataTable. FataTable 包含两个DataColumns: ProducyName 和 Qty. 然后你存储一个session变量"myds".当用户选择一个产品的时候, 你可以创建一个新的DataRow, 分配产品的资料给它, 添加这个ROW去DataTable. 用户的选择可以在跨多个请求中持续.

按一下的步骤创建第二张的Web form

 拖拉一个GridView到Web form上.

 添加两个绑定区域

 设置第一个绑定区域的HeaderText属性成"Product Name". 同样的,设置DataField成"Product Name".

 设置第二个绑定区域的HeaderText属性成"Quantity". 同样的,设置DataField成" Qty ".

添加下列的代码到第二张Web Form 的 Page_Load event handler上

protected void Page_Load(object sender, EventArgs e)

{

}

这里,你设置GridView控件的DataSource属性到你的储存在session上的DATASET.然后你通过调用DataBind() 方法来绑定GridView.

为了测试你的WEB FORMS, 设置Page.aspx 作为你的起始页, 然后运行你的WEB SITE. 你应该看到如图4所展示的:

通过”Add to cart”按钮选择一些产品. 这将会添加一些行在DataTable上. 记得你正在存储你的DataSet在一个session变量之中. 然后,通过点击”Show mycart” 超链接导航到 Page.aspx . 图5显示了带着前面所选的产Page.aspx.

正如你所见的, Page2.aspx 正确的显示了你在前页所选的那些条目. 这显示你的session 变量被真正的存储在了SQL服务器的数据库中,并被在第二页中找回. 还有, 注意你在无关储存模式的session中用了相同的存储调用值得标记.

储存Session状态在SQL服务器中的劣势虽然存储session状态在SQL 服务器中可以使你的网站更容易拓展和更稳定. 但是这个技术有一下的几个劣势执行上: 在执行的几个项目中,一个基于SQL SERVER的session 存储可能是一个最慢地选择. 因为你的session变量是存储在物理的数据库中.它用了更多的时候去存储和读取数据库. 这会影响了你的网站的运行速度. 花费: 因为你存储你的数据在数据库中, 你需要一个SQL数据库的执照, 这将给你的网站带来额外的开销. 连续的数据: 这个方法要求你所有保存在session变量中的数据必须是连续的如果你想存储数据在一个session中,这也许强制你去标记你自己的类作为[Serializable].

ASP.NET 2.0 可以允许你存储session状态到一个SQL服务器的数据库中。 ASPNET_REGSQL.EXE工具配置了数据库来支持这个特性。

更进一步， <sessionState>标记配置你的网站来支持这个模式。存储一个session状态在一个SQL服务器中是一种更具拓展，安全性和稳定性的选择。虽然，这种选择的运行相对于其他选择而言将会更慢。