Automatically Update Values in Database from Datagridview

Updating Datagridview values into database

Check the links below.

http://www.codeproject.com/Articles/12846/Auto-Saving-DataGridView-Rows-to-a-SQL-Server-Data

C# Issue: How do I save changes made in a DataGridView back to the DataTable used?

WinForms DataGridView - update database

C# WinForms - how to send updates from DataGridView to DataBase

Save changes in dataGridView

How can I update database from datagridview without button

This is not my own work, i found this online.

Here: https://social.msdn.microsoft.com/Forums/en-US/231be175-12d3-44ef-9222-875643a9e7fb/saving-the-data-entered-in-the-datagridview-to-database-table?forum=winformsdatacontrols

1. Add DataGridView Control in the form
2. Select that control and and open DataGridView Tasks clicking on ">" simbol
3. Open Choose DataSource Combo and click on "Add Project DataSource..."
4. Select Database > Next > Select/Create Connection > Next > Select Table you want to fill in the control > Finish
5. This will create a DataSet object will be shown in your form along with auto generated Adapter object too.
6. Select and right click on DataSet object control in designer and Select Edit in DataSet Designer... > this will open related XSD file
7. Right click on the opened UI for the dataset and Select Configure...
8. Select Advance Options button... This window will have all the insert, update, delete and other query's checkboxes selected, keep it as it is and press OK
9. Then press Next and select "Create methods to send updates directly to the database option" [default, it is selected] > Next and Finish it. This will generate required code for doing operations automatically
10. Add the following code in your Form class,

This will fill up the datagridview

    private void DataGridViewDirectDBUpdate_Load(object sender, EventArgs e)
    {
        // TODO: This line of code loads data into the 'northwindDataSet.Users' table. You can move, or remove it, as needed.
        this.yourtableadaptor.Fill(this.yourdataset.yourtable);

    }

This will save the data back to the database

    private void dataGridView1_CellEndEdit(object sender, EventArgs e)
    {
        this.yourtableadaptor.Update(yourdataset);
    }

C# and SQL Server Express and DataGridView update after table insert

Separate the data from how it is displayed

In modern programming, there is a tendency to separate your data (the model) from the way that your data is displayed to the operator (the view).

This gives you the freedom to change the display without having to change the model. For instance, if you want to show less columns, or want to show negative numbers in a different color, or maybe you want to show it in an Excel sheet.

Similarly you can change your Model without having to change the view: if you want to fetch your data from a CSV-file, or Json, instead of from a database, or maybe even fetch the data from the internet: you don't want to change the views you made based on this data.

Usually you need an adapter to make the model suitable for the display. This adapter is quite often called the ViewModel. Together these three items are abbreviated as MVVM. Consider to read some background information about this.

After separation of the model and the view, you will have methods to:

• Fetch data from the repository. The repository is your database, but it can be anything: CSV? Json, XML, internet, or just a dictionary for unit tests.
• Write data to the repository
• Display data
• Fetch edited data

And maybe you need a method to find out which edited data is changed, and thus needs to be updated in your repository.

You wrote that you are new to SQL Server databases. If I look at the rest of your question, it seems that reading and writing to the database is not your problem. Hence I won't go to deep into this. I'll first write how you would access the database using plain old SQL and DbReaders. If you already know how to do this, you can skip this chapter.

After that, I'll explain how the fetched data is displayed.

Accessing the database

Do you fetch the data using plain old SQL, or do you use entity framework? Because you hid this inside your repository, this doesn't matter for the outside world. It won't change if you change the way you fetch and update data.

Alas you forgot to write what you display in your DataGridView, and what is in the Database. Hence I have to give you an example.

Suppose you want to display Products: several constant product properties: Name, Description, Product Code, but also the Price and the number of items in Stock.

class Product
{
     public int Id {get; set;}
     public string ProductCode {get; set;}
     public string Name {get; set;}
     public string Description {get; set;}

     public decimal Price {get; set;}
     public int StockCount {get; set;}
}


interface IRepository
{
    IEnumerable<Product> FetchProductsToDisplay(...);
    void UpdateProducts(IEnumerable<Product> product);
}

class Repository : IRepository
{
   // TODO: implement
}

If you use plain old SQL, then fetching Products will be something like this:

const string sqlTextFetchProducts = @"SELECT TOP ..."
    + @" Id, ProductCode, Name, ..."
    + @" FROM Products;";

The exact SQL text differs per database management system that you use. For example SQLight uses Limit 30 instead of TOP 30.

Luckily you separated your Model from your View and hid these details inside your repository class, so nothing outside the repository changes if you decide to use a different method to access the database.

You might also need a Left Outer Join, GroupBy, Where, Order, etc. The exact SQL is a bit out of scope of the question.

What is important to remember, is that it is very dangerous to change the SQL string with values from some input that an operator or some other external source might provide. If you haven't ever heard of this, read Dangers of SQL injection

Always make your SQL a const string. Use variables to insert operator input.

For example, if you only want to display products at a certain WareHouse Location:

const string sqlTextFetchProducts = @"SELECT ... FROM Products;";
    + @" WHERE WareHouseLocationId = @WareHouseLocationId"

Ok, so let's implement FetchProductsToDisplay:

private string DbConnectionString => ...; // gets the database connection string

IEnumerable<Product> FetchProductsToDisplay(int wareHouseLocationId);
{
    const string sqlTextFetchProducts = @"...;";

    using (var dbConnection = new SQLiteConnection(this.DbConnectionString))
    {
        using (var dbCommand = dbConnection.CreateCommand())
        {
            dbCommand.CommandText = sqlTextFetchProducts ;
            dbCommand.Parameters.AddWithValue("@WareHouseLocationId", wareHouseLocationId);
            dbConnection.Open();

            // Execute the query and returns products one by one
            using (SQLiteDataReader dbReader = dbCommand.ExecuteReader())
            {
                while (dbReader.Read())
                {
                    Product fetchedProduct = new Product
                    {
                        Id = dbReader.GetInt64(0),
                        ProductCode = dbReader.GetString(1),
                        ...
                        Price = dbReader.GetDecimal(4),
                        StockCount = dbReader.GetInt32(5),
                    };
                    yield return fetchedProduct;
                }
            }
        }
    }
}

Several interesting things here.

Return IEnumerable

I return an IEnumerable: if my caller only uses the first few items, it is no use to convert all fetched data into Products.

Product firstProduct = this.FetchProducts(...).Take(25).ToList();

It is probably more efficient to create a special SQL for this, but for this example you can see, that you won't need to convert all fetched data into products.

Use parameters

The SQL text is constant. Parameters have a prefix @, to distinguish them from literal text. This is merely conventions, you might change this, but this makes it easy to spot the parameters.

Values for parameters are added one by one, for instance, if you only want the Product at WareHouseLocation 10, that have a StockCount of at least 2, and a maximum price of €25, you change your SQL such that it contains @WareHouseLocation, @StockCount, @Price and you add:

IEnumerable<Product> FetchProductsToDisplay(
    int wareHouseLocationId,
    int minimumStockCount,
    decimal maximumPrice)
{
    using(...)
    ...

   dbCommand.Parameters.AddWithValue("@WareHouseLocationId", wareHouseLocationId);
dbCommand.Parameters.AddWithValue("@StockCount", minimumStockCount);
dbCommand.Parameters.AddWithValue("@Price", maximumPrice);
...

Convert fetched data into Products
After executing the query, you use a DbReader to put the fetched data into Products, one by one.

while (dbReader.Read())

Returns true as long as there is unread fetched data.

Id = dbReader.GetInt64(0),
ProductCode = dbReader.GetString(1),
...

The fetched items in your SQL text Select Id, ProductCode, ... From ... have an index, Id has index 0, ProductCode has index 1, etc. Use the proper dbReader.Get... to convert the fetched item into the proper type.

The exact methods to convert the fetched data in the dbReader into your class might differ per database management system, but I guess you'll get the gist.

Of course you'll also need a method to update a product. This if fairly similar, but instead of ExecuteReader you'll use `

public void UpdateProductPrice(int productId, decimal price)
{
    const string sqlText = "UPDATE " + tableNameProducts
        + " SET Price = @Price"
        + " WHERE Id = @Id;";

    using (SQLiteCommand dbCommand = this.DbConnection.CreateCommand())
    {
        dbCommand.CommandText = sqlText;
        dbCommand.Parameters.AddWithValue("@Id", productId);
        dbCommand.Parameters.AddWithValue("@Price", productPrice);
        dbCommand.ExecuteNonQuery();
    }
}

Up to you to implement void UpdateProduct(Product product).

On to the ViewModel!

Displaying the Products

Now that we have a method to fetch the Products that must be displayed, we can try to display the fetched products. Althoug you can use this by editing the DataGridViewCells directly, it is way more easier to use DataGridView.DataSource:

Using visual studio designer, you have added a DataGridView and its columns. Use property DataGridView.DataPropertyName to define which column should display which Product property. This can also be done using the visual studio designer, but you can also do this in the constructor:

public MyForm()
{
    InitializeComponent();

    this.columnProductId.DataPropertyName = nameof(Product.Id);
    this.columnProductName.DataPropertyName = nameof(Product.Name);
    ...
    this.columnProductPrice.DataPropertyName = nameof(Product.Price);
}

This method has the advantage that if in future you decide to change the identifiers of the Product properties, they are checked by the compiler if you forgot to change them here. And of course: visual studio will automatically change the identifiers here. This is not done if you use the designer.

Now to display the products is a one liner:

private BindingList<Product> DisplayedProducts
{
    get => (BindingList<Product>) this.dataGridViewProducts.DataSource,
    set => this.dataGridViewProducts.DataSource = value;
}

This will display the Products, according to the View specification you used in the designer: if you want a special format for the Price, or maybe a red background for low stock, nothing will change in the model, nor in the viewmodel.

private IRepository Repository {get;} = new Repository();

private IEnumerable<Product> FetchProductsToDisplay()
{
    return this.Repository.FetchProductsToDisplay(...);
}

public void InitProductDisplay()
{
    this.DisplayedProducts = new BindingList<Product>(
        this.FetchProductsToDisplay().ToList());
}

And bingo! All Products are displayed in the format that you defined in your View. All changes that the operator makes: add / remove / change displayed Products are automatically updated in the BindingList.

For example: if the operator indicates that he has finished changing the Products, he can press an OK, or Apply Now button:

private void OnButtonApplyNow_Clicked(object sender, ...)
{
    Collection<Product> editedProducts = this.Displayedproducts();

    // find out which Products are changed and save them in the repository
    this.ProcessEditedProducts(editedProducts);
}

Now the only challenge left is: how to find out which Displayed Products are edited. As the operator won't press the OK button several times per second, I'd just fetch the original data from the database, and compare them with the edited data to decide whether an update is needed or not.

I wouldn't just update everything, because others might have changed data in such a way that you might decide not to update it. For instance, if your Product has a property IsObsolete, then it might not be wise to change the price.

Conclusion

By separating the Model from the View, the View has become a bunch of one liner methods. Most of the work is done in the model. This model can be unit tested without the use of WinForms.

You can easily change how the data is displayed, without having to change the model. If a low StockCount needs a different background color, the Model doesn't change.
If you want to use WPF instead of Winforms, or if you decide to make your data accessible via internet and a windows service, the model doesn't have to change.

---

Related Topics