static void log(string msg)
{ Console.WriteLine("{0:HH:mm:ss.fff} {1}", DateTime.Now, msg); System.Threading.Thread.Sleep(2000);
}
static void Main(string[] args)
{ MyLabDataContext db = new MyLabDataContext();
log("DataContext created"); var q = from o in db.Players
select o;
log("LINQ defined"); Console.WriteLine(q.Count());
log("Get the row count"); Player p = new Player()
{ UserName = "Darkthread",
RegDate = new DateTime(2009, 1, 1),
HiScore = 65536
};
db.Players.InsertOnSubmit(p);
log("InsertOnSubmit"); db.SubmitChanges();
log("SubmitChanges"); db.Dispose();
log("Disposed"); Console.Read();
}
在以上的程式範例中,每個動作間相隔兩秒鐘,並記錄發生時間,以便跟SQL Profiler記錄的時間對照。依我的推論,連線開啟關閉應該會發生在q.Count()與db.SubmitChanes()。
| EventClass |
StartTime |
EndTime |
TextData |
| Audit Login |
2009-10-19 00:55:54.587 |
NULL |
-- network protocol: TCP/IP |
| Audit Logout |
2009-10-19 00:55:54.587 |
2009-10-19 00:55:54.647 |
NULL |
| RPC:Completed |
2009-10-19 00:55:54.647 |
2009-10-19 00:55:54.647 |
exec sp_reset_connection |
| Audit Login |
2009-10-19 00:55:54.647 |
NULL |
-- network protocol: TCP/IP |
| SQL:BatchCompleted |
2009-10-19 00:55:54.647 |
2009-10-19 00:55:54.647 |
SELECT COUNT(*) AS [value] FROM [dbo].[Player] AS [t0] |
| Audit Logout |
2009-10-19 00:55:54.647 |
2009-10-19 00:55:58.720 |
NULL |
| RPC:Completed |
2009-10-19 00:55:58.720 |
2009-10-19 00:55:58.720 |
exec sp_reset_connection |
| Audit Login |
2009-10-19 00:55:58.720 |
NULL |
-- network protocol: TCP/IP |
| RPC:Completed |
2009-10-19 00:55:58.810 |
2009-10-19 00:55:58.810 |
exec sp_executesql N'INSERT INTO [dbo].[Player]([UserName], [RegDate], [HiScore]) VALUES (@p0, @p1, @p2)',N'@p0 nvarchar(10),@p1 datetime,@p2 int',@p0=N'Darkthread',@p1='2009-01-01 00:00:00',@p2=65536 |
| Audit Logout |
2009-10-19 00:55:58.720 |
2009-10-19 00:59:25.550 |
NULL |
我這麼解讀,00:55:50建立DataContext時並沒有開啟任何連線,q.Count()發生於00:55:54,此時有了第一次Audit Login。基於Connection Pooling的特性,每次SqlConnection.Open()時,會產生一個Audit Logout、exec sp_reset_connection再加一個Audit Login,而在此發生了兩次,第一次在00:55:54,第二次在00:55:58,剛好就是q.Count()及SubmitChanges()發生的時點。
由以上觀察,應可印證我的推論"LINQ to SQL裡DataContext內部對SqlConnection的處理原則為: 必要時才開啟,用後立即關閉"。進一步延伸,加上using應不會產生顯著影響,但對於實作IDispose的物件使用using絕對是良好的習慣。