none
inserting when I want it to update RRS feed

  • Question

  • I have a typed dataset that I created by dragging a table in vs.net 2010 from server explorer to solution explorer.  This created the xsd and all the designer files for dataadapter and such.  All of this works great for everything except updating.

    			CFRQDataset ds = new CFRQDataset();
    			CFRQDataset.RawInboundDataTable dt = new CFRQDataset.RawInboundDataTable();
    			CFRQDatasetTableAdapters.RawInboundTableAdapter adapter = new CFRQDatasetTableAdapters.RawInboundTableAdapter();
    			adapter.Fill(dt);
    
    			foreach (CRQLogEntry entry in crqentries)
    			{
    				CFRQDataset.RawInboundRow dr = dt.NewRawInboundRow();
    				dr.VisitorId = entry.VisitorId;		// this is the PK
    				dr.ExternalVisitorId = entry.ExternalVisitorId;
    
    				dr.Hash = entry.ComputeHash();
    
    				dt.AddRawInboundRow(dr);
    			}
    
    			adapter.Update(dt);	// instead of updating, this always tries to insert. :(
    

     The error I get is that the PK already exists.  Thats the thing though, I KNOW it already exists, I just want it to update if the row is there and insert if it isnt.  What am I doing wrong?


    Allen Anderson - Architect http://www.richercomponents.com - (WinForm And Asp.net Controls)
    Sunday, February 13, 2011 10:48 PM

Answers

  • Arrrgh!

    Your're both on the wrong track. Strongly typed table adapters are designed for low-volume query resultsets--never for bulk operations.

    First, the data needs to stay in the database--you're right on this point. Next, you should (never) use ADO or EF to do bulk operations on the data. Both techniques bring the rows to the client, change them and send the updates back. This is terribly inefficient.

    So, the answer is to submit an UPDATE statement that makes the changes in-place. Consider that Transact SQL is a language that can perform extremely complex UPDATE operations. When using this approach, the data remains on the server where it  belongs. ADO can execute any type of TSQL query includng UPDATE or any DML operation using the ExecuteNonQuery method of the Connection object.

    Need examples, see the book.

     


    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by Allen Anderson Monday, February 21, 2011 6:56 AM
    Monday, February 21, 2011 6:18 AM
    Moderator
  • Hi William,

    You are right, Thanks for your help.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 21, 2011 7:20 AM
    Moderator

All replies

  •  

    Hi Allen,

    Thanks for your post.

    >>>CFRQDataset.RawInboundRow dr = dt.NewRawInboundRow();

    dr is new row, it will be added when you call update method.

    The update is performed on a by-row basis. For every inserted, modified, and deleted row, the Update method determines the type of change that has been performed on it (Insert, Update or Delete). Depending on the type of change, the Insert, Update, or Delete command template executes to propagate the modified row to the data source.

    If you want to update the record according to the PK, you should find the row at first, then change other   columns.

    Here is a good link about update records: http://support.microsoft.com/kb/308507

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 14, 2011 6:58 AM
    Moderator
  • hi Alan, thanks for your reply.

    The problem is that we are talking EXTREMELY large data volumes.  Pulling the record before I update it is simply not possible.  My question is how do I get this to update without having to pull the record?  I have the PK of the record and I know the new data that needs to go in it.  What are my options?


    Allen Anderson - Architect http://www.richercomponents.com - (WinForm And Asp.net Controls)
    Monday, February 14, 2011 6:18 PM
  • Hi Allen,

    I got your point, You can find the row by Pk:

     foreach (CRQLogEntry entry in crqentries)
          {
            CFRQDataset.RawInboundRow dr = dt.FindByVisitorId(entry.VisitorId);//find the row
            // dr.VisitorId = entry.VisitorId;		// this is the PK
            dr.ExternalVisitorId = entry.ExternalVisitorId;
            dr.Hash = entry.ComputeHash();
            //dt.AddRawInboundRow(dr);
          }
          adapter.Update(dt);	// instead of updating, this always tries to insert. :(
    

    (http://msdn.microsoft.com/en-us/library/y06xa2h1(v=vs.80).aspx)

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, February 15, 2011 3:16 AM
    Moderator
  • Hi Allen,

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 
     
    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, February 18, 2011 2:07 AM
    Moderator
  • Hi Alan, thanks for the reply.

    Like I said before, this doesn't help as the thing I am trying to do here is NOT pull the row before I update it.   I'm trying to find out how to update a row without first having to pull the row.


    Allen Anderson - Architect http://www.richercomponents.com - (WinForm And Asp.net Controls)
    Saturday, February 19, 2011 1:58 AM
  • Hi Allen,

    Thanks for your feedback.

    I think there may no way to update records without pull it, you can add judgement in your code and let update records in your insert method:

    foreach (CRQLogEntry entry in crqentries)
       {
        CFRQDataset.RawInboundRow dr = dt.FindByVisitorId(entry.VisitorId);//find the row
    if(dr==null)
    {
    dr=dt.NewRawInboundRow();
    dr.VisitorId = entry.VisitorId;// this is the PK
         dt.AddRawInboundRow(dr);
    }
    else
    {
    dr.ExternalVisitorId = entry.ExternalVisitorId;
        dr.Hash = entry.ComputeHash();
    }
           }
       adapter.Update(dt);	// instead of updating, this always tries to insert. :(
    
    
    
    Have a nice day.

     


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 21, 2011 3:40 AM
    Moderator
  • Arrrgh!

    Your're both on the wrong track. Strongly typed table adapters are designed for low-volume query resultsets--never for bulk operations.

    First, the data needs to stay in the database--you're right on this point. Next, you should (never) use ADO or EF to do bulk operations on the data. Both techniques bring the rows to the client, change them and send the updates back. This is terribly inefficient.

    So, the answer is to submit an UPDATE statement that makes the changes in-place. Consider that Transact SQL is a language that can perform extremely complex UPDATE operations. When using this approach, the data remains on the server where it  belongs. ADO can execute any type of TSQL query includng UPDATE or any DML operation using the ExecuteNonQuery method of the Connection object.

    Need examples, see the book.

     


    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by Allen Anderson Monday, February 21, 2011 6:56 AM
    Monday, February 21, 2011 6:18 AM
    Moderator
  • Hi William, thanks for the reply.  It would seem that you are both right in that the strongly typed adapter can't be used to update in place without pulling the row.

    I am able to get the performance I need by using TSQL, I just didn't want to have to spend the extra time to code it out.

    thanks


    Allen Anderson - Architect http://www.richercomponents.com - (WinForm And Asp.net Controls)
    Monday, February 21, 2011 6:56 AM
  • Hi William,

    You are right, Thanks for your help.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 21, 2011 7:20 AM
    Moderator