I am trying to update data while I am reading them from database, see below. But after the whole thing finish, the data didn't get updated.
Is there any transaction syntax i need to specify? (When I debug, I can see I have the right record retrieved.)
using (conn = new SqlConnection(MyConnectionString))
using (SqlCommand cmd = new SqlCommand("dbo.MyProcedure", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Count", count);
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (reader.Read())
{
// wrapper object, not related to database
SampleModel c = new SampleModel();
c.ID= (string)reader["ID"];
c.Name = (string)reader["Name"];
c.Type = (int)reader["Type"];
// modeList will return to outside, not related to database
modelList.Add(c);
sampleTable1 table1 = context.sampleTable1s.SingleOrDefault(t=> t.id = c.ID);
// try to update the "isRead", but it doesn`t work....!!!
// about the datatype, in the data base, it is "smallInt"
// in linq to sql, it is "short?"
// PS Default value all should be 0
table1.isRead = 1;
context.SubmitChanges(); <--- here, it doesn`t do the job // context is new from Linq to SQL
}
}
conn.Close();
}
Here is my procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE MyProcedure
@Count int = 100
AS
BEGIN
SELECT TOP (@Count )
t1.id AS ID,
t1.name AS Name,
t2.type AS TYPE
FROM sampleTable1 as t1 with (nolock),
sampleTable2 as t2 with (nolock)
WHERE (t1.t2Id = t2.Id)
ORDER BY t1.name asc
END
GO
And if I put all my code inside TransactionScope block
using (TransactionScope scope = new TransactionScope())
{
// all the C# code above
scope.Complete();
}
I will get an exception "MSDTC on server 'localhost-sqlserver2005' is unavailable."
And if I only put some of the code, there is no exception, but the data did`t get updated
using (TransactionScope scope = new TransactionScope())
{
sampleTable1 table1 = context.sampleTable1s.SingleOrDefault(t=> t.id = c.ID);
table1.isRead = 1;
context.SubmitChanges();
scope.Complete();
}
Thanks.
