Delete from database in c#

I'm writing an app in C# WPF with VS10 express. I have to say I'm a very beginner in C# and VS but I'v searched a lot of examples on Google, I really tried to solve this problem on my own..

I have a local database (mydatabase.sdf) and at the load of my window I fill a table of that database with some data. One of the fields of that table needs a unique value, so I want to put in every load the same data, but I get an error than off course.

I want to delete all the data from the database before I refill, this seems to be so easy but I don't get it working...

I tried

dataset.Tables["mytable"].Clear()

that doesn't work, it seems to be deleting only data from the datagrid (dataTable) but not really from the datastore.

also I tried:

for (int i = 0; i < dataset.Tables["mytable"].Rows.Count; i++)
{
  dataset.Tables["mytable"].Rows[i].Delete();
}
  this.TableAdap开发者_C百科ter.Update(this.dataset);

But at startup the dataset.Tables["mytable"].Rows.Count statement returns zero at startup, but if I put in my data I get the "unique-value error".

The only way to get it deleted is to delete it manually from the datagrid and then push an Update button, that really deletes it from the datastore.

It is no option to make that field in the database not-unique because of development reasons.

How can I delete really data from the datastore/database (mydatabase.sdf) in the load of my program??

EDIT

Here is the code how I fill the database with data:

public void FillInternet()
    {
        klantenTableAdapter1.ClearBeforeFill = false;


        string MyConString =    "SERVER=myserver;" +
                                "DATABASE=mydb;" +
                                "UID=myuid;" +
                                "PASSWORD=mypass;";

        MySqlConnection connection = new MySqlConnection(MyConString);
        MySqlCommand command = connection.CreateCommand();
        MySqlDataReader Reader;
        command.CommandText = "SELECT klantnr, voorletters, roepnaam, achternaam, tussenvoegsel, meisjesnaam, straat, huisnr, subhuisnr, postcode, plaats, telthuis, telmobiel, telwerk, fax, email, geboortedatum FROM klanten ORDER BY klantnr";
        connection.Open();
        Reader = command.ExecuteReader();


        try
        {
            while (Reader.Read())
            {
                DataRow newLogRow = dataset1.Tables["klanten"].NewRow();
                var thisrow = "";

                for (int i = 0; i < Reader.FieldCount; i++)
                {
                    thisrow = Reader.GetValue(i).ToString();
                    newLogRow[Reader.GetName(i)] = thisrow;
                }

                dataset1.Tables["klanten"].Rows.Add(newLogRow);
                this.klantenTableAdapter1.Update(this.dataset1);
            }
            connection.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error: " + ex.Message,"Fout",MessageBoxButton.OK,MessageBoxImage.Error);
        }

        dataset1.AcceptChanges();

        //Fill from internet
        //da.Fill(dataset1.klanten);

        //Fill from local database
        klantenTableAdapter1.Fill(dataset1.klanten);

        this.klantenTableAdapter1.Update(this.dataset1);

        this.DataContext = dataset1.klanten.DefaultView;
}

ADO.NET uses a "disconnected" recordset model. It keeps a copy of the data in client-side structures (DataSet and DataTable). Updates/inserts/deletions made to the client-side structures need to be pushed back out to the database. You need to read up on ADO.NET to get a basic understanding of this process and to get a sense of the ADO.NET event-model, which will be necessary if you want to do anything that involves typical real-world complications. There are many books written on ADO.NET because it is a feature-rich middle-tier data layer with significant complexities.

For your purposes, you could read up on the ADO.NET Command object and the SQL "delete" command. You will also need to explore how ADO.NET handles autoincrementing primary keys, which is one of the trickiest aspects of the disconnected model.

If the database itself defines an autoincrementing key, you cannot supply that value when inserting new rows unless you turn the auto-increment off temporarily in the back-end. That is not an ADO.NET issue, BTW. That is 100% back-end.

From your other posts, I'm going on the assumption that your database on this too is MySQL. You mention a unique column which typically means an auto-increment column. If you "delete" the entries after you've built them (say starting 1-10), and then try to re-add your next cycle the same 1-10 items, it can choke on you giving you this message. If you add numbers to your table starting with the last one used... see if that helps.