Check if record exists in MySQL database before inserting

Question

How do I check if a record exists in a MySQL database using C#?

If a record exists, the form should display a message box saying that the record already exists and not save the values into the database. Otherwise, the new record shall be saved into the database.

What is the C# syntax for it? I am using Visual Studio as my IDE.

private void btnSave_Click(object sender, EventArgs e)
{
    string constring = "datasource=localhost;port=3306;username=root;password=root";
    string QueryAdd = "insert into assetmanagement.assets (`AssetId`,`AssetType`,`AssetDescription`,`SerialNumber`,`Barcode`,`Quantity`,`Manufacturer`,`Model`,`Category`,`Condition`,`Location`,`Department`,`DateAcquired`,`InService`,`Supplier`,`Notes`,`AddedBy`,`DateAdded`) values ('" + this.txtAssetId.Text + "','" + this.txtAssetType.Text + "','" + this.txtAssetDesc.Text + "','" + this.txtSerial.Text + "','" + this.txtBarcode.Text + "','" + this.txtQuantity.Text + "','" + this.txtManufacturer.Text + "','" + this.txtModel.Text + "','" + this.txtCategory.Text + "','" + this.txtCondition.Text + "','" + this.txtLocation.Text + "','" + this.txtDepartment.Text + "','" + this.txtDateAcquired.Text + "','" + this.txtInService.Text + "','" + this.txtSupplier.Text + "','" + this.txtNotes.Text + "','" + this.txtAddedBy.Text + "','" + this.txtDateAdded.Text + "') ;";
    MySqlConnection connDataBase = new MySqlConnection(constring);
    MySqlCommand cmdDataBase = new MySqlCommand(QueryAdd, connDataBase);
    MySqlDataReader myReader;
    try
    {
        connDataBase.Open();
        myReader = cmdDataBase.ExecuteReader();
        MessageBox.Show("Saved");
        while (myReader.Read())
        {

        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

The answers in the duplicate are bad also for a Sql Server database where the standard MERGE statement exists, let's forget that this question is about MySql. — Steve, Jan 08 '17 at 10:56
What do you mean by "Visual Basic as my IDE"? Visual Basic Express (like [Visual Basic 2005 Express Edition](https://en.wikipedia.org/wiki/Microsoft_Visual_Studio_Express#Visual_Basic_Express))? — Peter Mortensen, Jan 08 '17 at 13:00
If you solved your problem do not put “solved” in your title; that does not mark your question as answered in the search or UI. Instead, put the answer in the big Answer text field below. After a day or so you can mark your own answer as the solution, that will mark your question as solved in the UI and you'll even get a shiny new participation badge. — Dour High Arch, Jan 08 '17 at 18:59
"I hope you can provide the codes for this" - no, that's not what we do here. People will offer to help, but they won't do it for you. This wording is best avoided, since it makes questions far too broad. — halfer, Jan 09 '17 at 23:14

score 2 · Answer 1 · answered Jan 08 '17 at 10:54

2

You can use INSERT ... ON DUPLICATE KEY UPDATE syntax of MySQL (explained here) if you want to update the record if it already exists.

If you just want to IGNORE if the record already exists then you can use 'INSERT IGNORE statement' (explained here).

answered Jan 08 '17 at 10:54

Darshan Mehta

30,102
11
68
102

score 0 · Answer 2 · answered Jan 08 '17 at 11:10

0

I don't know if MySQL and SQL are the same thing, but with SQL you can use Select Count (*) with ExecuteNonQuery(). If the method returns 1, you have a duplicate. If not, that record doesn't exist.

Sorry if I'm wrong.

answered Jan 08 '17 at 11:10

Cihan Kurt

343
1
5
21

1

[MySQL](http://en.wikipedia.org/wiki/MySQL) and [SQL](http://en.wikipedia.org/wiki/SQL) are not the same thing. MySQL is an [RDBMS](https://en.wikipedia.org/wiki/Relational_database_management_system). – Peter Mortensen Jan 08 '17 at 13:02
So it also uses different queries? @PeterMortensen – Cihan Kurt Jan 08 '17 at 13:05

Check if record exists in MySQL database before inserting

2 Answers2