[研究][ASP.NET]SQL Server 資料庫連線數量測試(一)程式與連線數量
2022-05-03
環境:Visual Studio 2022 + ASP.NET + WebForm + Web Application + C# + SQL Server 2019
********************************************************************************
SQL Server Management Studio 上執行
-- 顯示每個 SQL Server 連線帳號的連線數量 select loginame,count(1) as Nums from sys.sysprocesses group by loginame order by 2 desc; -- 顯示整個 SQL Server 連線數量 SELECT COUNT(*) AS CONNECTIONS FROM master..sysprocesses; |
顯示最大連線數量
select @@max_connections; |
得到 32767
*****
查詢連線明細。
USE master SELECT c.session_id, c.connect_time, s.login_time, c.client_net_address, s.login_name, s.status FROM sys.dm_exec_connections c left join sys.dm_exec_sessions s on c.session_id = s.session_id |
*****
Web.Config 內容Web.Config 內容
<?xml version="1.0" encoding="utf-8"?> <!-- 如需如何設定 ASP.NET 應用程式的詳細資訊,請前往 https://go.microsoft.com/fwlink/?LinkId=169433 --> <configuration> <connectionStrings> <add name="TestDBConnectionString" connectionString="Data Source=.;Initial Catalog=TestDB;Persist Security Info=True;User ID=testuser;Password=P@ssw0rd" providerName="System.Data.SqlClient" /> <add name="TestDBConnectionString2" connectionString="Data Source=.;Initial Catalog=TestDB;Persist Security Info=True;User ID=testuser;Password=P@ssw0rd" providerName="System.Data.SqlClient" /> </connectionStrings> <system.web> <compilation debug="true" targetFramework="4.8" /> <httpRuntime targetFramework="4.8" /> </system.web> <system.codedom> <compilers> <compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:1659;1699;1701" /> <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:41008 /define:_MYTYPE=\"Web\" /optionInfer+" /> </compilers> </system.codedom> </configuration> |
Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication1.Default" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> <title></title> </head> <body> <form id="form1" runat="server"> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TestDBConnectionString %>" SelectCommand="SELECT * FROM [Table1]"></asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="SN" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" /> <asp:BoundField DataField="CName" HeaderText="CName" SortExpression="CName" /> </Columns> </asp:GridView> <br /> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:TestDBConnectionString %>" SelectCommand="SELECT [SN],[CName] FROM [Table1]"></asp:SqlDataSource> <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" DataKeyNames="SN" DataSourceID="SqlDataSource2"> <Columns> <asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" /> <asp:BoundField DataField="CName" HeaderText="CName" SortExpression="CName" /> </Columns> </asp:GridView> <br /> <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:TestDBConnectionString2 %>" SelectCommand="SELECT [SN],[CName] FROM [Table1]"></asp:SqlDataSource> <asp:GridView ID="GridView3" runat="server" AutoGenerateColumns="False" DataKeyNames="SN" DataSourceID="SqlDataSource3"> <Columns> <asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" /> <asp:BoundField DataField="CName" HeaderText="CName" SortExpression="CName" /> </Columns> </asp:GridView> <br /> </form> </body> </html> |
測試畫面
- 如果在Visual Studio 2022中執行,testuser 是1個連線;關閉執行網頁後,立刻歸 0 。
- 如果不在Visual Studio 2022中執行,而是 deploy 後執行,testuser 是1個連線;關閉執行網頁後,不會立刻歸0 ,根據下面這篇,應該會留存約 4~8分鐘。
- [研究][ASP.NET]SQL Server 資料庫連線池(連線集區、Connection Pool、連線共用)
- https://shaurong.blogspot.com/2022/05/aspnetsql-server-connection-pool.html
- Web.Config中TestDBConnectionString和TestDBConnectionString2雖然連線字串名稱不同,但連線字串值 connectionString 內容相同,所以預設會使用同一個連線。
- SqlDataSource1 和 SqlDataSource2 的 SelectCommand 不同,但使用相同 TestDBConnectionString 連線字串,所以使用同一個連線。
- SqlDataSource3 和 SqlDataSource2 的 SelectCommand 相同,雖使用不相同 TestDBConnectionString 和 TestDBConnectionString2 連線字串,但 TestDBConnectionString和TestDBConnectionString2連線字串值 connectionString 內容相同,所以預設會使用同一個連線。
********************************************************************************
Default.aspx.cs
using System; using System.Data.SqlClient; using System.Web.Configuration; namespace WebApplication1 { public partial class Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { string queryString = @"SELECT * FROM [Table1];"; using (SqlConnection connection = new SqlConnection( WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString)) { SqlCommand command = new SqlCommand(queryString, connection); connection.Open(); command.ExecuteNonQuery(); } using (SqlConnection connection = new SqlConnection( WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString)) { SqlCommand command2 = new SqlCommand(queryString, connection); connection.Open(); command2.ExecuteNonQuery(); } } } } |
測試結果,使用1個連線。
********************************************************************************
Default.aspx.cs,失敗,不可以這樣寫。
using System; using System.Data.SqlClient; using System.Web.Configuration; namespace WebApplication1 { public partial class Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { string queryString = @"SELECT * FROM [Table1];"; using (SqlConnection connection = new SqlConnection( WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString)) { SqlCommand command = new SqlCommand(queryString, connection); connection.Open(); command.ExecuteNonQuery(); } //錯誤 CS0136 無法在此範圍宣告名為 'connection' 的區域變數或參數,因為該名稱已用於封入區域變數範圍,以定義區域變數或參數 WebApplication1 C:\CodeTemp6\WebApplication1\WebApplication1\Default.aspx.cs 12 作用中 SqlConnection connection = new SqlConnection( WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString); SqlCommand command2 = new SqlCommand(queryString, connection); connection.Open(); command2.ExecuteNonQuery(); } } } |
無法編譯。
********************************************************************************
Default.aspx.cs
using System;
using System.Data.SqlClient;
using System.Web.Configuration;
namespace WebApplication1
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string queryString = @"SELECT * FROM [Table1];";
using (SqlConnection connection = new SqlConnection(
WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
command.ExecuteNonQuery();
}
SqlConnection connection2 = new SqlConnection(
WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString);
SqlCommand command2 = new SqlCommand(queryString, connection2);
connection2.Open();
command2.ExecuteNonQuery();
}
}
}
|
測試結果,testuser 使用2個連線。
********************************************************************************
Default.aspx.cs
using System; using System.Data.SqlClient; using System.Web.Configuration; namespace WebApplication1 { public partial class Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { string queryString = @"SELECT * FROM [Table1];"; using (SqlConnection connection = new SqlConnection( WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString)) { SqlCommand command = new SqlCommand(queryString, connection); connection.Open(); command.ExecuteNonQuery(); } using (SqlConnection connection = new SqlConnection( WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString)) { SqlCommand command2 = new SqlCommand(queryString, connection); connection.Open(); command2.ExecuteNonQuery(); } } } } |
測試結果,使用1個連線。
********************************************************************************
Default.aspx.cs
using System; using System.Data.SqlClient; using System.Web.Configuration; namespace WebApplication1 { public partial class Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { string queryString = @"SELECT * FROM [Table1];"; using (SqlConnection connection = new SqlConnection( WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString)) { SqlCommand command = new SqlCommand(queryString, connection); connection.Open(); command.ExecuteNonQuery(); } using (SqlConnection connection2 = new SqlConnection( WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString)) { SqlCommand command2 = new SqlCommand(queryString, connection2); connection2.Open(); command2.ExecuteNonQuery(); } } } } |
測試結果,使用1個連線。
********************************************************************************
結論
- 如果都使用 using,就算連線名稱connection 和connection2不同,也會使用相同連線。
- 如果都使用 using,就算連線名稱connection 相同,也會使用相同連線。
- 如果連線名稱connection 相同,一個using,一個沒 using,編譯失敗。
- 如果連線名稱connection 和connection2不同,一個using,一個沒 using,使用2個連線。
- 如果連線名稱connection 和connection2不同,2個都沒 using,使用2個連線。
********************************************************************************
Default.aspx 中 GridView 和 SqlSource 全砍;
Default.aspx.cs
using System; using System.Data.SqlClient; using System.Web.Configuration; namespace WebApplication1 { public partial class Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { string queryString = @"SELECT * FROM [Table1];"; using (SqlConnection connection = new SqlConnection( WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString)) { SqlCommand command = new SqlCommand(queryString, connection); connection.Open(); command.ExecuteNonQuery(); connection.Close(); connection.Dispose(); } } } } |
Deploy 後測試,結果使用1個連線。
也就是 Close 或 Dispose 疑似不會真的立刻釋放連線。
********************************************************************************
(完)
相關
[研究][ASP.NET]SQL Server 連線數量測試(四)Close、Dispose、ClearPool 與 ClearAllPools
https://shaurong.blogspot.com/2022/05/aspnetsql-server-closedisposeclearpool.html
[研究][ASP.NET]SQL Server 連線數量測試(三)連線歸屬測試
https://shaurong.blogspot.com/2022/05/aspnetsql-server_44.html
[研究][ASP.NET]SQL Server 資料庫連線數量測試(二)資料庫中儲存的連線資訊https://shaurong.blogspot.com/2022/05/aspnetsql-server_3.html
[研究][ASP.NET]SQL Server 連線數量測試(一)程式與資料庫連線數量https://shaurong.blogspot.com/2022/05/aspnetsql-server.html
沒有留言:
張貼留言