7

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.

5
  • noloack? wondering if that is a typo in the question, or a broken SPROC Commented Sep 16, 2010 at 5:19
  • To confirm - what is table1.isRead before this? It only applies actual changes, so if it was 1 before, no change. Have you tried capturing the log (context.Log = Console.Out;) to see what it is submitting? (similar alternative - use a SQL profiler) Commented Sep 16, 2010 at 5:21
  • @Marc isRead is smallInt in the database, and it is "short" in the code when i see from linq. Default it is 0, and once it is read, i try to update it to be 1 ... and i did (context.Log = Console.Out) this, but i can only see select statement. real thx for your comment, it is helpful. Commented Sep 16, 2010 at 5:54
  • The fact that you don't see an update in the console means that the context doesn't think the table1 has been changed in any way. Have you stepped through the code and verified the value of table1.isRead is 0 before you set it to 1? Commented Sep 16, 2010 at 6:15
  • @Bennor, yes, i step by step debug, and i can see the default value is 0. the same in the database. Commented Sep 16, 2010 at 6:21

2 Answers 2

8

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).

Sign up to request clarification or add additional context in comments.

2 Comments

.. you are genies... i forget to create primary key in my script. let me try what happened after i put a primary on
Just an additional note: The key to getting update (and delete) statements is if the model thinks there is a PK. The best is of course if there is a PK in the DB, but for situations where the DB table doesn't have a PK it is sufficient to mark one or several columns (that uniquely identify records) as PK members and L2S will be happy.
2

Make sure you have the primary key set for that table. If you can't do that in the db, do it in the linq2sql designer.

Answered this here, here and here. Also mentioned it linq to sql pitfalls.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.