为什么不能使用LINQ to SQL将数据更新到数据库中? [英] Why can't I update data into database using LINQ to SQL?

问题描述

我正在尝试从数据库中读取数据时更新数据，请参见下文. 但是在整个过程完成之后，数据并没有得到更新.

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();
                }

这是我的程序:

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

如果我将所有代码都放在TransactionScope块中

And if I put all my code inside TransactionScope block

using (TransactionScope scope = new TransactionScope())
{
    // all the C# code above
    scope.Complete();
}

我将收到一个异常服务器'localhost-sqlserver2005'上的MSDTC不可用."

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();
}

谢谢.

推荐答案

检查是否有问题的实体类中的至少一个成员在L2S设计器中被标记为主键成员.如果一个实体没有任何PK成员，则L2S将在提交更新时默默地忽略它(无提示地，不抛出异常，也没有更新SQL语句生成并发送到db).

Check so at least one member in the entity class in question is marked as as primary key member in the L2S designer. If an entity doesn't have any PK members, L2S will silently ignore it when submitting updates (silently as in no exception thrown, and no update SQL statement generated and sent to db).

这篇关于为什么不能使用LINQ to SQL将数据更新到数据库中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！