Changes not saving to database using SqlDataAdapter

Question

I'm stuck with my changes not getting saved to the SQL Server database.

I can read the database properly, and when calling RefreshGrid() that refreshes from the database, the gridview updates correctly. But not when restarting the program or checking the actual database, then the changes are gone.

I would normally use Entity Framework for this, but sometimes one needs to learn other ways.

private string conString = ConfigurationManager.ConnectionStrings["RewardsConnectionString"].ConnectionString;
private RewardsDS rewardsDset = new RewardsDS();

private SqlDataAdapter CreateDataAdapter(SqlConnection conn)
{
    // Build the selection query.
    SqlDataAdapter rewardsAdapter = new SqlDataAdapter();
    SqlCommand selectCommand = new SqlCommand("SELECT * FROM Purchases", conn);
    rewardsAdapter.SelectCommand = selectCommand;

    // Build the insertion query
    SqlCommand insertCommand = new SqlCommand(
        @"INSERT INTO Purchases (PurchaseDate, Amount, CustomerId)
        VALUES (@PurchaseDate, @Amount, @CustomerId)", conn);

    insertCommand.Parameters.Add("@PurchaseDate", SqlDbType.DateTime, 0, "PurchaseDate"); // Not sure about this zero ...
    insertCommand.Parameters.Add("@Amount", SqlDbType.Decimal, 0, "Amount");
    insertCommand.Parameters.Add("@CustomerId", SqlDbType.Int, 0, "CustomerId");

    rewardsAdapter.InsertCommand = insertCommand;

    // Build the update query
    SqlCommand updateCommand = new SqlCommand(
        @"UPDATE Purchases SET Amount = @Amount, PurchaseDate = @PurchaseDate WHERE PurchaseId = @PurchaseId", conn);
    updateCommand.Parameters.Add("@Amount", SqlDbType.Decimal, 0, "Amount");
    updateCommand.Parameters.Add("@PurchaseDate", SqlDbType.DateTime, 0, "PurchaseDate");

    SqlParameter param = updateCommand.Parameters.Add("@PurchaseId", SqlDbType.Int, 0, "PurchaseId");
    param.SourceVersion = DataRowVersion.Original;
    rewardsAdapter.UpdateCommand = updateCommand;

    // Build the delete query
    SqlCommand deleteCommand = new SqlCommand(
        "DELETE FROM Purchases WHERE PurchaseId = @PurchaseId", conn);
    param = deleteCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "PurchaseId");
    param.SourceVersion = DataRowVersion.Original;
    rewardsAdapter.DeleteCommand = deleteCommand;

    return rewardsAdapter;
}

// Gets changes and saves them to the database.
private void SaveChanges()
{
    using (SqlConnection conn = new SqlConnection(conString))
    {
        var fadapter = CreateDataAdapter(conn);
        conn.Open();
        DataSet changes = rewardsDset.GetChanges();

        try
        {
            fadapter.Update(changes, "Purchases");
        }
        catch (Exception ex)
        {
            MessageBox.Show("There are no updates to be saved");
        }

        conn.Close();
        rewardsDset.AcceptChanges();
    }
}

// Refresh the dataGridView from the database
private void RefreshGrid()
{
    rewardsDset.Clear();

    using (SqlConnection conn = new SqlConnection(conString))
    {
        conn.Open();
        SqlDataAdapter myAdapter = new SqlDataAdapter("SELECT * FROM Purchases", conn);
        myAdapter.Fill(rewardsDset, "Purchases");
        conn.Close();

        dataGridView1.DataSource = null;
        this.dataGridView1.DataSource = rewardsDset.Tables["Purchases"];
    }
}

Answer 1

I will start by apologizing for wasting anyone's time. Finally found what was causing this error and it was far more basic than I thought and not at all where I've been trying to look for solutions. I've made an embarrassing mistake regarding the connection string, but if someone repeats my mistake this was it:

I've been using the connection string to the DataSet, and not to the actual database ...

Adding the proper connection string certainly solved my problem, and was of course impossible for anyone to see with only the code I posted above.

Answer 2

You simply have to execute the queries:

 rewardsAdapter.InsertCommand.ExecuteNonQuery();