mySQL Update or Insert

[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

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

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

[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!

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

[C#Jaap]

Does it work for you?