CODEWITHSUNDEEP
c#sql
user3067340
user3067340

Reputation: 25

SqlCeCommand insert C# do not really insert into database

when I try to insert, delete or update a record into .sdf database, the record is in the database temporary. When I run the application again the changes are not visible. I think that the commit function do not updates the database.

Here is my code for the insert function:

conn.Open();    
SqlCeTransaction transaction;
transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);

SqlCeCommand cmd = conn.CreateCommand();

cmd.Transaction = transaction;

cmd.CommandText = 
"INSERT INTO tblCustomers " + " (LastName, FirstName, MiddleName, Street, City, Phone, TaxCountry, Dealer," + 
" Email, FEIN, DateOfBirth, SSAN, DriverLicense, State, Exp, Code, Notes)" + 
"VALUES (@last_name, @first_name, @middle_name, @street, @city, @phone_number, @tax_country, @dealer, @email," +
"@fein, @dateofbirth, @ssan, @driver_license, @stat, @exp, @code, @_note)";

cmd.Parameters.AddWithValue("@last_name", textBox2.Text);
cmd.Parameters.AddWithValue("@first_name", textBox3.Text);
cmd.Parameters.AddWithValue("@middle_name", textBox10.Text);
cmd.Parameters.AddWithValue("@street", textBox4.Text);
cmd.Parameters.AddWithValue("@city", textBox5.Text);
cmd.Parameters.AddWithValue("@phone_number", textBox6.Text);
cmd.Parameters.AddWithValue("@tax_country", textBox7.Text);
cmd.Parameters.AddWithValue("@dealer", textBox17.Text);
cmd.Parameters.AddWithValue("@email", textBox8.Text);
cmd.Parameters.AddWithValue("@fein", textBox18.Text);
cmd.Parameters.AddWithValue("@dateofbirth", Convert.ToDateTime(textBox12.Text));
cmd.Parameters.AddWithValue("@ssan", textBox11.Text);
cmd.Parameters.AddWithValue("@driver_license", textBox13.Text);
cmd.Parameters.AddWithValue("@stat", textBox14.Text);
cmd.Parameters.AddWithValue("@exp",Convert.ToDateTime(textBox15.Text));
cmd.Parameters.AddWithValue("@code", textBox16.Text);
cmd.Parameters.AddWithValue("@_note", textBox9.Text);

//cmd.Connection = conn;

cmd.ExecuteNonQuery();

transaction.Commit(CommitMode.Deferred);

clearData();

conn.Close();

Do you have any idea to solve this issue? Thank you!

Upvotes: 0

Views: 680

Answers (2)

Sachin
Sachin

Reputation: 40970

According to MSDN

Use Deferred mode for optimal performance; the changes will be flashed to disk within the timespan specified in the ConnectionString FLUSH INTERVAL property;

So it will take time. Otherwise you can use the CommitMode.Immediate to see effect immediately.

Upvotes: 1

meklarian
meklarian

Reputation: 6625

This line should be modified:

transaction.Commit(CommitMode.Deferred);

You're using CommitMode.Deferred when you actually want CommitMode.Immediate.

See: CommitMode Enumeration @ MSDN

If a deferred commit is what you really want, then you should check your connection string for the flush interval, and modify it to a shorter timespan. The default is 10 seconds.

See: SqlCeConnectionString @ MSDN

Upvotes: 1

Related Questions