いよいよ Linq to SQL です。
こんなSQLサーバーデータベースとプログラムを作ってみました。
IDENTITYを使ったキーと文字列列２つとtimestampです。
dbmlファイルにテーブルをドロップダウンしただけです。
オプティミスティック同時実行制御をきちんとやっています。
Table1はタイムスタンプがないので、
WHERE ([ID] = @p0) AND ([TEXT1] = @p1) AND ([TEXT2] = @p2)
Table2はタイムスタンプがあるので、
WHERE ([ID] = @p0) AND ([timestamp] = @p1)
timestampの読み直しもきちんと行われています。
今回はすごい技術だってコメントしておきます。
try
{
Console.WriteLine("【TABLE1】");
using (var context = new LinqTestDataContext())
{
context.Log = Console.Out;
Console.WriteLine("【SELECT】");
var table1query = from a in context.Table_1 select a;
foreach (var r in table1query)
Console.WriteLine(r.ID.ToString() + "," + r.TEXT1 + "," + r.TEXT2);
Console.WriteLine("【DELETE】");
context.Table_1.DeleteAllOnSubmit(table1query);
context.SubmitChanges();
Console.WriteLine("【INSERT】");
Table_1 record1insert = new Table_1();
record1insert.TEXT1 = "TEXT11";
record1insert.TEXT2 = "TEXT12";
context.Table_1.InsertOnSubmit(record1insert);
context.SubmitChanges();
Console.WriteLine("【UPDATE】");
foreach (var record in table1query)
record.TEXT1 = "TEXT11X";
context.SubmitChanges();
}
Console.WriteLine("【TABLE2】");
using (var context = new LinqTestDataContext())
{
context.Log = Console.Out;
Console.WriteLine("【SELECT】");
var table2query = from a in context.Table_2 select a;
foreach (var r in table2query)
Console.WriteLine(r.ID.ToString() + "," + r.TEXT1 + "," + r.TEXT2);
Console.WriteLine("【DELETE】");
context.Table_2.DeleteAllOnSubmit(table2query);
context.SubmitChanges();
Console.WriteLine("【INSERT】");
Table_2 record2insert = new Table_2();
record2insert.TEXT1 = "TEXT21";
record2insert.TEXT2 = "TEXT22";
context.Table_2.InsertOnSubmit(record2insert);
context.SubmitChanges();
Console.WriteLine("【UPDATE】");
foreach (var record in table2query)
record.TEXT1 = "TEXT21X";
context.SubmitChanges();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
【TABLE1】
【SELECT】
SELECT [t0].[ID], [t0].[TEXT1], [t0].[TEXT2]
FROM [dbo].[Table_1] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
9,TEXT11X ,TEXT12
【DELETE】
SELECT [t0].[ID], [t0].[TEXT1], [t0].[TEXT2]
FROM [dbo].[Table_1] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
DELETE FROM [dbo].[Table_1] WHERE ([ID] = @p0) AND ([TEXT1] = @p1) AND ([TEXT2]
= @p2)
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [9]
-- @p1: Input NChar (Size = 10; Prec = 0; Scale = 0) [TEXT11X ]
-- @p2: Input NChar (Size = 10; Prec = 0; Scale = 0) [TEXT12 ]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
【INSERT】
INSERT INTO [dbo].[Table_1]([TEXT1], [TEXT2])
VALUES (@p0, @p1)
SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]
-- @p0: Input NChar (Size = 10; Prec = 0; Scale = 0) [TEXT11]
-- @p1: Input NChar (Size = 10; Prec = 0; Scale = 0) [TEXT12]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
【UPDATE】
SELECT [t0].[ID], [t0].[TEXT1], [t0].[TEXT2]
FROM [dbo].[Table_1] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
UPDATE [dbo].[Table_1]
SET [TEXT1] = @p3
WHERE ([ID] = @p0) AND ([TEXT1] = @p1) AND ([TEXT2] = @p2)
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [10]
-- @p1: Input NChar (Size = 10; Prec = 0; Scale = 0) [TEXT11]
-- @p2: Input NChar (Size = 10; Prec = 0; Scale = 0) [TEXT12]
-- @p3: Input NChar (Size = 10; Prec = 0; Scale = 0) [TEXT11X]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
【TABLE2】
【SELECT】
SELECT [t0].[ID], [t0].[TEXT1], [t0].[TEXT2], [t0].[timestamp]
FROM [dbo].[Table_2] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
9,TEXT21X ,TEXT22
【DELETE】
SELECT [t0].[ID], [t0].[TEXT1], [t0].[TEXT2], [t0].[timestamp]
FROM [dbo].[Table_2] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
DELETE FROM [dbo].[Table_2] WHERE ([ID] = @p0) AND ([timestamp] = @p1)
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [9]
-- @p1: Input Timestamp (Size = 8; Prec = 0; Scale = 0) [SqlBinary(8)]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
【INSERT】
INSERT INTO [dbo].[Table_2]([TEXT1], [TEXT2])
VALUES (@p0, @p1)
SELECT [t0].[ID], [t0].[timestamp]
FROM [dbo].[Table_2] AS [t0]
WHERE [t0].[ID] = (SCOPE_IDENTITY())
-- @p0: Input NChar (Size = 10; Prec = 0; Scale = 0) [TEXT21]
-- @p1: Input NChar (Size = 10; Prec = 0; Scale = 0) [TEXT22]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
【UPDATE】
SELECT [t0].[ID], [t0].[TEXT1], [t0].[TEXT2], [t0].[timestamp]
FROM [dbo].[Table_2] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
UPDATE [dbo].[Table_2]
SET [TEXT1] = @p2
WHERE ([ID] = @p0) AND ([timestamp] = @p1)
SELECT [t1].[timestamp]
FROM [dbo].[Table_2] AS [t1]
WHERE ((@@ROWCOUNT) > 0) AND ([t1].[ID] = @p3)
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [10]
-- @p1: Input Timestamp (Size = 8; Prec = 0; Scale = 0) [SqlBinary(8)]
-- @p2: Input NChar (Size = 10; Prec = 0; Scale = 0) [TEXT21X]
-- @p3: Input Int (Size = 0; Prec = 0; Scale = 0) [10]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8