CODEWITHSUNDEEP
c#sqldatabase
guy
guy

Reputation: 21

How do I save changes back to the database?

I have a database in my c# project, and I have got a little problem with it. I can read the data from the database and show it, but when I am using an SQL query like insert/update/remove, the changes does not save back into the database file and after a few minutes they just gone...

A little code example :

DataSet ds = new DataSet();
string sql_string = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True";
string sql = "INSERT INTO Table1 (name, time, needs, text) VALUES ('string', 'string', 'string', 'string')";
SqlConnection conn = new SqlConnection(sql_string);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds); ;
cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();         
conn.Close();

Now I understand that I need to use the SqlDataAdapter.update() to save the changes, but I don't know how to use it?

Upvotes: 2

Views: 5731

Answers (4)

KMC
KMC

Reputation: 20046

If you're using DataSet, after you populate your DataSet from database, you have to propagate these changes back to update your database. DataSet is a run-time object stored in memory.

If you're simply executing a SQL commend to update your database, then it's straightforward (use using statement and Parameters.Add instead of placing values directly to your SQL statement to avoid injection):

using (SqlCeConnection conn = new SqlCeConnection(connString))
{
    string sqlStr = @"INSERT INTO FooTable (FooName) VALUES (@FooName)";
    using (SqlCeCommand cmd = new SqlCeCommand(sqlStr, conn))
    {
        try
        {
            conn.Open();
            cmd.Parameters.AddWithValue("@FooName", 'someString');

            cmd.ExecuteNonQuery();

            conn.Close();
        }
        catch (SqlCeException se)
        {
            MessageBox.Show(se.ToString());
        }
    }
}

Upvotes: 4

carboncrank
carboncrank

Reputation: 71

I'm digging up this question because the correct answer is not clearly described (please read the whole thread if you disagree): the closest to being correct is actually from jfuhr.

I have experienced the identical problem to the one first described by the OP, i.e.:

  • Can connect to the DB
  • Can retrieve all records (or subset) from the DB
  • Appear to be able to write changes to the DB (and even use a separate datagridview control which retrieves all records from the DB including the ones you've added/edited to confirm this)
  • However the changes are not propagated to the DB somehow - a restart of the whole IDE (or inspection via SQL Server Management Studio) shows the new records have not been saved.

In my case this problem occurred despite using proven code samples which worked perfectly elsewhere.

The solution is to change the path in the connection string from:

AttachDbFilename=|DataDirectory|...Database.mdf

to the absolute path:

AttachDbFilename=C:\\Users\\Me\\Projects\\MyProject\\MyDatabase.mdf

Upvotes: 1

user815362
user815362

Reputation:

KMC's code should update your table.

The only thing I can think of is that in your connection string, you have AttachDbFilename=|DataDirectory|\Database.mdf;

But it looks like you are connecting to a SQLExpress instance. Try removing this and from the conn string. When the ExecuteNonQuery is called, the changes are immediate.

Upvotes: 0

Bibhu
Bibhu

Reputation: 4081

Have look on this article its give a good example on how to use SqlDataAdapter to select and update data.

Upvotes: 0

Related Questions