C# Can't modify a database

Question

I'm currently trying to modify a local database using a C# program. I followed this example : https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.commit.aspx

The problem is that the modification is done when the program is running (for instance, if I add a new row, I will see it in my program), but when I stop the program and I check the database, there is no change anymore - the database is exactly the same as before the execution.

I've been working on this issue for several days and haven't found a solution.

Here is an extract of my work:

SqlConnection connectionLocale = new SqlConnection("Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|LocalDB.mdf;Integrated Security=True");

SqlCommand command2 = connectionLocale.CreateCommand();
SqlTransaction transaction;

connectionLocale.Open();
transaction = connectionLocale.BeginTransaction();

command2.Connection = connectionLocale;
command2.Transaction = transaction;

try
{
    command2.CommandText = "DELETE FROM Chercheur";
    command2.ExecuteNonQuery();
    transaction.Commit();
    Console.WriteLine("commit ok");
}
catch
{
    transaction.Rollback();
}

connectionLocale.Close();

I'm using Visual Vtudio 2012 with a .mdf database.

After some research I saw somewhere that the connection (AttachDbFilename=|DataDirectory|LocalDB.mdf) creates a temporary database and that all the change are done on this temporary database.

How can I affect the original database instead of this temporary database?

Check this answer http://stackoverflow.com/questions/17147249/why-saving-changes-to-a-database-fails/17147460#17147460 — Steve, Aug 05 '15 at 19:58

score 1 · Answer 1 · answered Aug 05 '15 at 19:55

To persist changes you need to put the database on a real SQL Server and change your connection string to something like: Server=someServerName; Database=someDatabaseName; Trusted_Connection=true or Server=someServerName; Database=someDatabaseName; User ID=someSqlUser; Password=somePassword.

score 1 · Answer 2 · answered Aug 05 '15 at 19:55

You need to point your connection string to the correct instance (the "original database"):

SqlConnection connectionLocale = new SqlConnection("Data Source = YourInstanceName; initial catalog = YourDatabaseName; Integrated Security=True");

Substituting the following values:

YourInstanceName - the name of the SQL Server instance where your database lives (can be obtained from select @@servername; while connected to the SQL instance)

YourDatabaseName - the name of the database where you want to modify the data

The connection string tells the provider where to target your queries. If you're not pointing to the correct destination SQL Server instance (and database), then you'll get the undesirable results that you're experiencing.

score 0 · Answer 3 · answered Aug 05 '15 at 19:55

Your connection string is pointing to the local db. Your connection string -

"Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|LocalDB.mdf;Integrated Security=True"

must be changed to point to your actual db.

Take a look here for a process to generate your connection string. You could also refer to connectionstring.com for sample connection strings.

C# Can't modify a database

3 Answers3