none
Simple table update RRS feed

  • Question

  • I think I might be over-complicating this, but I'm making a methoid to both update a record in a table and return a value from the record:
        public static int UpdateTruck(ScOptionalTktFields tktdata) {
          int result = 0;
          object trkserial;
    
          try {
            using (SqlCommand cmd = cn.CreateCommand()) {
              cmd.Transaction = tn;
    
              cmd.CommandText = @"SELECT * FROM Trk WHERE TrkId = @TrkId";
              cmd.Parameters.AddWithValue("TrkId", tktdata.DspTrkId);
    
              using (SqlDataAdapter da = new SqlDataAdapter(cmd)) {
                da.UpdateCommand = new SqlCommand();
                da.UpdateCommand.Connection = cn;
    
                using (DataSet ds = new DataSet()) {
                  ds.Tables.Add("Trk");
                  int rowCount = da.Fill(ds.Tables["Trk"]);
    
                  if (rowCount == 1) {
                    
                    DataRow dr = ds.Tables["Trk"].Rows[0];
                    trkserial = dr["TrkSerial"];
    
                    if (trkserial != null) 
                      result = (int)trkserial;
    
                    if (dr["TrkShipped"] == null) {
                      dr["TrkShipped"] = tktdata.DspMainQtyNet;
                    } else {
                      dr["TrkShipped"] += tktdata.DspMainQtyNet;
                    }
    
                    da.Update(ds, "Trk");
                  }
                }
              }
            }
          } catch (Exception ex) {
            throw new Exception(string.Format("Exception during Trk update for TrkId [{0}]:\n{1}", tktdata.DspTrkId, ex.Message));
          }
    
          return result;
        }
    
    

    So that's how far I am at the moment, but I can see that for this to work, I need to supply the da.UpdateCommand.CommandText with an update command. But since I'm already updating fields directly (the TrkShipped field,) I think I must be missing something.

    Can someone please suggest the best way to accomplish this?

    Thanks,


    Bill McCormick -- MSDN Subscriber
    Monday, August 16, 2010 6:04 PM

Answers

  • I guess I was on the right track.

    The simple answer is to add this:

    SqlCommandBuilder cb = new SqlCommandBuilder(da);
    
    

    Everything else is the pretty much the same.

    Thanks,


    Bill McCormick -- MSDN Subscriber
    • Marked as answer by Bill McCormick Wednesday, August 18, 2010 10:01 PM
    Wednesday, August 18, 2010 10:01 PM

All replies

  • So that's how far I am at the moment, but I can see that for this to work, I need to supply the da.UpdateCommand.CommandText with an update command. But since I'm already updating fields directly (the TrkShipped field,) I think I must be missing something.

    I don't know why that would matter (updating fields directly) ... you're NOT updating the database table when you update the DataRow, if that's what you were implying. You DO still need to supply an UpdateCommand if you want to use the .Update() method. You could also simply run the .ExecuteNonQuery() method. In either case, you still need the appropriate UPDATE SQL command.

    Unless I'm totally missing what your question is ... it's late, I'm tired ... I should just go to bed.  ;0)


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Tuesday, August 17, 2010 4:46 AM
  • Hi Bill,

     

    You need to supply the update logic when you use SqlDataAdapter to update the table. Basically, there are two ways to supply the update logic. One is to set the InsertCommand,UpdateCommand and DeleteCommand manually.  That is also the same with what you have mentioned in your post “I need to supply the da.UpdateCommand.CommandText with an update command”. In this way you can have the best control on your update, but the code is so tedious. Another way is to use SqlCommandBuilder to generate the update logic for you. But this needs the table has a primary key and the primary key is include in your query.

     

    You can find some information about How To Update a SQL Server Database by Using the SqlDataAdapter Object from http://support.microsoft.com/kb/308507/en-us, and you can refer to http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx to get information and some samples about SqlCommandBuilder.

     

     

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, August 17, 2010 8:34 AM
    Moderator
  • And you can also take a look at my 3-part DataAccess series on my blog. I didn't mention it in my first reply, because it sounded like you understood some of the DataAdapter concepts sufficiently, but what the heck ... here it is:

    http://geek-goddess-bonnie.blogspot.com/2009/09/dataaccess-part-i.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-iii.html

    Each post adds extra complexity to the Data Access classes, but more flexiblity. However, even just the information in the first post is enough to get anyone going and is a great starting point for those wishing to have more control over their DataAccess.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Tuesday, August 17, 2010 3:21 PM
  • I guess I was on the right track.

    The simple answer is to add this:

    SqlCommandBuilder cb = new SqlCommandBuilder(da);
    
    

    Everything else is the pretty much the same.

    Thanks,


    Bill McCormick -- MSDN Subscriber
    • Marked as answer by Bill McCormick Wednesday, August 18, 2010 10:01 PM
    Wednesday, August 18, 2010 10:01 PM