Member Avatar for Krefie

Hey boys and girls,

I need help, I am working on a project that requires me to build a business tier (.dll) for a c# program, I've only ever used SQL server.

The problem comes in as follows, at university I was tuaght how to import a datasource via the wizzard. I cant do this with mySQL (Xampp, phpmyadmin) as im using the express version of VS 2010. Ok all that aside the problem:

I've written 2 example classes called Client.cs and ClientList.cs, I have succesfully read data via query from mySQL and saved it into a dataset for use in the presentation tier (under the default constructor of ClientList.cs).

I now need to write an update function, to either save or insert any changes made in the presentation tier datagrid...I have a few clues as to how I want to do it, just one function called Update, if there was changes to the row I need to just run query "Update", if a new row was added I want it to run Query "Insert".

Will it be best to return the whole dataset to the business tier and run a foreach loop with rowstate == unchanged, to see if a value was changed or a new row inserted? or is there a better way to do this?

Any help will be greatly appreciated.

Kind Regards
Krefie

  • 3 Contributors
  • 5 Replies
  • 231 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by C#Jaap

Dani AI

Generated

Good thread — correctly pointed out the MySQL "insert-or-update" option and mentioned the alternative REPLACE behavior. ’s loop demonstrates a working idea but a few pitfalls and refinements are worth calling out so the solution is robust, safe, and performant.

A simpler, idiomatic .NET approach is to let ADO.NET track changes and call Update once. For example, fill a DataTable with a MySqlDataAdapter, let the UI edit that table, then use a MySqlCommandBuilder (or supply explicit InsertCommand/UpdateCommand) and call dataAdapter.Update(dataTable). This uses RowState (Added/Modified/Deleted) and avoids manual row-state checks and per-row SQL string building.

using (var conn = new MySqlConnection(connString))
using (var da = new MySqlDataAdapter("SELECT Client_ID,Name,Surname,Username,Password FROM Client", conn))
using (var cb = new MySqlCommandBuilder(da))
{
    da.InsertCommand = cb.GetInsertCommand();
    da.UpdateCommand = cb.GetUpdateCommand();
    da.DeleteCommand = cb.GetDeleteCommand();
    da.Update(myClientDataTable);
}

Practical recommendations and gotchas:

  • Reuse prepared commands rather than building SQL each loop. Preparing once and changing parameter values is much faster.
  • Avoid AddWithValue where possible; declare parameter types and sizes explicitly to prevent type-mismatch surprises.
  • Wrap multi-row work in a transaction so the set of changes is atomic.
  • For large batches consider multi-row INSERT (with on-duplicate-key semantics) or the MySQL bulk loader instead of executing one statement per row.
  • REPLACE deletes then inserts (can break FKs, reset AUTO_INCREMENT); ON DUPLICATE KEY UPDATE modifies in-place — choose based on desired semantics.
  • Use optimistic concurrency where necessary (e.g., include a timestamp/version column in UPDATE WHERE) to avoid overwriting concurrent changes.
  • Never store plaintext passwords; store a salted hash.

These adjustments keep the code clearer, improve performance, and reduce subtle bugs while still supporting the insert-or-update workflow discussed earlier in the thread.

Recommended Answers

All 5 Replies

Member Avatar for pritaeas

Perhaps unknown to you, MySQL has the following construct:

INSERT INTO ... ON DUPLICATE KEY UPDATE ...

Technically it makes it possible to use a single query for both the insert and the update. It tries to insert, if the PK exists it does an update.

Member Avatar for C#Jaap

I think you are looking for this feature: Click Here REPLACE in stead of INSERT and UPDATE. You'll only have to check whether a row in changed or not.

Member Avatar for Krefie

Hey guys,

Thanks for the replies, Ill try Pritaeas solution and let u know. I didnt know about both solutions and would certainly like to try the replace function, although I think in this instance it might not work as most of the database is delete restrict!

Let you know in a bit about the solution!

Member Avatar for Krefie

so this is the solution i came up with,

welcome to critisise

foreach (Client client in this)
           {
               cmd.Parameters.Clear();
               cmd.CommandText = "INSERT INTO Client (Client_ID,Name,Surname,Username,Password)" +
                         "VALUES (@Client_ID,@Name,@Surname,@Username,@Password) ON DUPLICATE KEY                      UPDATE Name = @Name," +
                         "Surname = @Surname,Username = @Username,Password = @Password";

               cmd.Parameters.AddWithValue("@Client_ID", client.Client_ID);
               cmd.Parameters.AddWithValue("@Name", client.Name);
               cmd.Parameters.AddWithValue("@Surname", client.Surname);
               cmd.Parameters.AddWithValue("@Username", client.Username);
               cmd.Parameters.AddWithValue("@Password", client.Password);

               cmd.ExecuteNonQuery(); 
           }
Member Avatar for C#Jaap

Does it work for you?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.