Updating SQL Database from DataGridView in C#

Question

There's a few tutorials out there on this, but I'm assuming I must have implemented something wrong, because the code I followed from combined tutorials is not working as it should.

These are the tutorials: https://youtu.be/_i4mYXSaD4w , https://youtu.be/_sB0A6FIhUM

I'm trying to create a DataGridView that displays some basic data, just 4 columns of information. I want the user to be able to add, update and delete rows of information. I've manually created 1 row of information in the SQL database just to avoid in 'catches' when the program is loaded.

I've got the 1 line of my SQL Database information loading just fine, but when I edit the information, and click my update button it doesn't seem to work on the SQL side, even though the program side works. By this I mean, I have a messagebox that confirms it's been updated, but when I close the App and the run it again, it loads the old data, and when I double check the database, it hasn't been updated. If someone could help me adjust this so when I add rows of information to the DataGridView or edit the rows, and have the SQL file actually receive the changes/updates, I'd appreciate it. Here is my code:

public partial class Form1 : Form
{
    SqlConnection con;
    SqlDataAdapter sda;
    DataTable dt;
    SqlCommandBuilder scb;
    private int rowIndex = 0;

    public Form1()
    {
        InitializeComponent();
    }


    private void Form1_Load(object sender, EventArgs e)
    {
        try
        {
            con = new SqlConnection(Properties.Settings.Default.SchoolConnectionString);
            con.Open();
            sda = new SqlDataAdapter("SELECT * FROM School", con);
            dt = new DataTable();
            sda.Fill(dt);
            dataGridView1.DataSource = dt;
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

    private void UpButton_Click(object sender, EventArgs e)
    {

        try
        {
            scb = new SqlCommandBuilder(sda);
            sda.Update(dt);
            MessageBox.Show("Information Updated", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }
    //button to refresh the data without need to close the app
    private void RefButton_Click(object sender, EventArgs e)
    {
        try
        {
            con = new SqlConnection(Properties.Settings.Default.SchoolConnectionString);
            con.Open();
            sda = new SqlDataAdapter("SELECT * FROM School", con);
            dt = new DataTable();
            sda.Fill(dt);
            dataGridView1.DataSource = dt;
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

    private void CloseButton_Click(object sender, EventArgs e)
    {
        Application.Exit();
    }

    //BROKEN - supposed to bring up a menu item to delete the row
    private void dataGridView1_CellMouseUp(object sender, DataGridViewCellMouseEventArgs e)
    {
        if (e.Button == MouseButtons.Right)
        {
            this.dataGridView1.Rows[e.RowIndex].Selected = true;
            this.rowIndex = e.RowIndex;
            this.dataGridView1.CurrentCell = this.dataGridView1.Rows[e.RowIndex].Cells[1];
            this.contextMenuStrip1.Show(this.dataGridView1, e.Location);
            contextMenuStrip1.Show(Cursor.Position);
        }

    }

    private void contextMenuStrip1_Click(object sender, CancelEventArgs e)
    {
        if (!this.dataGridView1.Rows[this.rowIndex].IsNewRow)
        {
            this.dataGridView1.Rows.RemoveAt(this.rowIndex);
        }
    }


}

Now, what I'm not sure about is 1 thing I changed from one of the tutorials, was that they used a dataset rather than a datatable, but as I understand it, other than a dataset being able to hold multiple table structures, there's no differences in pulling the data or updating it. For the fact that I didn't have mounds of data and different tables to use, I felt DataTable was sufficient for my use of this program.

Additionally, in my database, I have a primary key that's an integer, and 4 columns of text. I'd prefer to avoid the stupid column of integers like line numbers and just make my first text column the primary key, but when I try to do this and update the database, it throws errors. If there's a way to do this, I'd appreciate the explanation of how, or if there's a way to hide the first line number column the pulls the integer value for the primary key, and have it automatically increment and/or adjust this value according to editing, changes and new rows being added that'd be great. Just to clear up, if I add rows 2, 3 and 4, I want these values to be autogenerated and just make that column not visible. As it stands, I have to manually type the integer in there.

Thanks for any help and advice.

Update #1:

Okay, so taking some recommendations, I have tried using DataSet in the following format:

private void Form1_Load(object sender, EventArgs e)
    {
        try
        {
            con = new SqlConnection(Properties.Settings.Default.SchoolConnectionString);
            con.Open();
            sda = new SqlDataAdapter("SELECT * FROM School", con);
            ds = new DataSet();
            sda.Fill(ds, "e");
            dataGridView1.DataSource = ds.Tables["e"];
          }
        catch (Exception ex)
        {
            MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

    private void UpButton_Click(object sender, EventArgs e)
    {

        try
        {
            scb = new SqlCommandBuilder(sda);
            sda.Update(ds, "e");
            MessageBox.Show("Information Updated", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

I've tried using DataTable again in the format given in the first answer provided using the following format but in 2 ways: 1 without a new instance of the SqlCommandBuilder, and one with: WITH:

private void Form1_Load(object sender, EventArgs e)
    {
        try
        {
            con = new SqlConnection(Properties.Settings.Default.SchoolConnectionString);
            con.Open();
            sda = new SqlDataAdapter("SELECT * FROM School", con);
            dt = new DataTable();
            sda.Fill(dt);
            dataGridView1.DataSource = dt;
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

    private void UpButton_Click(object sender, EventArgs e)
    {

        try
        {
            scb = new SqlCommandBuilder(sda);
            newDT = dt.GetChanges();
            if (newDT != null)
            {
                sda.Update(newDT);
            }
            MessageBox.Show("Information Updated", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

WITHOUT: This version produces an error that says: "Update requires a valid UpdateCommand when passed DataRow collection with modified rows."

private void Form1_Load(object sender, EventArgs e)
    {
        try
        {
            con = new SqlConnection(Properties.Settings.Default.SchoolConnectionString);
            con.Open();
            sda = new SqlDataAdapter("SELECT * FROM School", con);
            dt = new DataTable();
            sda.Fill(dt);
            dataGridView1.DataSource = dt;
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

    private void UpButton_Click(object sender, EventArgs e)
    {

        try
        {
            newDT = dt.GetChanges();
            if (newDT != null)
            {
                sda.Update(newDT);
            }
            MessageBox.Show("Information Updated", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

Sooooo, I'm stumped. All except the one without SqlCommandBuilder trigger the Updated Message Box, but none of them actually save the changes to the sql database.

Answer 1

Well, thanks to more Googling and combing through other questions here on this site, I found someone answered a similar question that made me realize I was in fact missing something simple. So in the event someone else runs into this issue, I thought I'd post the highly possible similar mistake I was making that was resulting in this issue.

So, what I didn't realize, was that Visual Studio was making a temp version of my database in the \bin\debug folder. (If there's a way to turn that functionality off, I'd love to hear about it, because I think that's rather stupid) For whatever reason, if you open up in the Sever Explorer to find your .mdf file, manually adding or deleting information in that database is reflected in the program when run, because it looks for this file, makes a copy of it, and then works from there while you're running your application for testing purposes. The disappointing fact, is that (again, unless I missed where to keep this from happening) it doesn't transversely copy these changes you make to the temp version, over to the primary .mdf file you created in the first place.

So, this is why I saw no changes, because I was looking at the primary database file where it was in fact not working from. Once I found out how to locate the temp version from within Visual Studio, and I queried that file database, I did in fact see the changes. For clarity, multiple versions of what I tried originally were in fact working. So, all of the solutions I tried above aside from the one where I mentioned it produced an error due to a lack of the SqlCommandBuilder call, in fact do update the temporary database.

As a side note, coming from a Visual Studio, C#, SQL newbie perspective, I'm surprised with the extensive tutorials and information on SQL Databases and DataGridView, that this isn't noted in the process of tutorials. Even Youtube videos I watch didn't make this explicitly clear that when they "proved" the updates were happening, they didn't make it obvious they were checking the temp database, not the primary one.

Answer 2

It looks to me like you're never setting a SQL update statement, only select statements.

You'll need to add something in like:

sda.UpdateCommand = "UPDATE TABLE SET ..."

Or create a new dataAdapter/command to handle your update

Once you have that in place, calling update on sda should work.

Revised:

If it were me, I would use SqlCommand for your update statement.

private void UpButton_Click(object sender, EventArgs e)
{

    try
    {
        using(con = new SqlConnection(Properties.Settings.Default.SchoolConnectionString))
        {
            con.Open();
            string sqlCommand = "Update (Table) set value=@Value where id=@ID";
            SqlCommand cmd = new SqlCommand(sqlCommand, con);
            cmd.Parameters.AddWithValue("@Value", updatedValue);
            cmd.Parameters.AddWithValue("@ID", idOfRowToUpdate);
            int rowsAffected = cmd.ExecuteNonQuery();
            if(rowsAffected == 1)
            {
                MessageBox.Show("Information Updated", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            con.Close();
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

Things to note:

You'll need to get the values you're updating from your table and replace it for UpdatedValue and idOfRowToUpdate.

You'll need to form the UPDATE command for your table.

the "Using" brackets help properly dispose of your connection object.

Technically with the using brackets con.Close() isn't required. I do it just in case.

Revision #2:

Ok, I did some more research into SqlDataAdapter and how it functions in regards to a datatable, it looks like this is much easier than I thought.

You should be able to drop in this new code to the UpButton_Click method and have it work:

try
    {
        DataTable newDT = dt.GetChanges();
        if(newDT != null)
        {
            sda.Update(newDT);
        }
        MessageBox.Show("Information Updated", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }

    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }

According to This StackOverflow Article, this should be all you need to make the update.