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

Harald Coppoolse :

Separate the data from how it is displayed\nIn 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).\nThis 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.\nSimilarly 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.\nUsually 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.\nAfter separation of the model and the view, you will have methods to:\n\nFetch 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.\nWrite data to the repository\nDisplay data\nFetch edited data\n\nAnd maybe you need a method to find out which edited data is changed, and thus needs to be updated in your repository.\nYou 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.\nAfter that, I'll explain how the fetched data is displayed.\nAccessing the database\nDo 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.\nAlas you forgot to write what you display in your DataGridView, and what is in the Database. Hence I have to give you an example.\nSuppose you want to display Products: several constant product properties: Name, Description, Product Code, but also the Price and the number of items in Stock.\nclass Product\n{\n public int Id {get; set;}\n public string ProductCode {get; set;}\n public string Name {get; set;}\n public string Description {get; set;}\n\n public decimal Price {get; set;}\n public int StockCount {get; set;}\n}\n\n\ninterface IRepository\n{\n IEnumerable<Product> FetchProductsToDisplay(...);\n void UpdateProducts(IEnumerable<Product> product);\n}\n\nclass Repository : IRepository\n{\n // TODO: implement\n}\n\nIf you use plain old SQL, then fetching Products will be something like this:\nconst string sqlTextFetchProducts = @"SELECT TOP ..."\n + @" Id, ProductCode, Name, ..."\n + @" FROM Products;";\n\nThe exact SQL text differs per database management system that you use. For example SQLight uses Limit 30 instead of TOP 30.\nLuckily 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.\nYou might also need a Left Outer Join, GroupBy, Where, Order, etc. The exact SQL is a bit out of scope of the question.\nWhat 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\n\nAlways make your SQL a const string. Use variables to insert operator input.\n\nFor example, if you only want to display products at a certain WareHouse Location:\nconst string sqlTextFetchProducts = @"SELECT ... FROM Products;";\n + @" WHERE WareHouseLocationId = @WareHouseLocationId"\n\nOk, so let's implement FetchProductsToDisplay:\nprivate string DbConnectionString => ...; // gets the database connection string\n\nIEnumerable<Product> FetchProductsToDisplay(int wareHouseLocationId);\n{\n const string sqlTextFetchProducts = @"...;";\n\n using (var dbConnection = new SQLiteConnection(this.DbConnectionString))\n {\n using (var dbCommand = dbConnection.CreateCommand())\n {\n dbCommand.CommandText = sqlTextFetchProducts ;\n dbCommand.Parameters.AddWithValue("@WareHouseLocationId", wareHouseLocationId);\n dbConnection.Open();\n\n // Execute the query and returns products one by one\n using (SQLiteDataReader dbReader = dbCommand.ExecuteReader())\n {\n while (dbReader.Read())\n {\n Product fetchedProduct = new Product\n {\n Id = dbReader.GetInt64(0),\n ProductCode = dbReader.GetString(1),\n ...\n Price = dbReader.GetDecimal(4),\n StockCount = dbReader.GetInt32(5),\n };\n yield return fetchedProduct;\n }\n }\n }\n }\n}\n\nSeveral interesting things here.\nReturn IEnumerable\nI return an IEnumerable: if my caller only uses the first few items, it is no use to convert all fetched data into Products.\nProduct firstProduct = this.FetchProducts(...).Take(25).ToList();\n\nIt 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.\nUse parameters\nThe 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.\nValues 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:\nIEnumerable<Product> FetchProductsToDisplay(\n int wareHouseLocationId,\n int minimumStockCount,\n decimal maximumPrice)\n{\n using(...)\n ...\n\n dbCommand.Parameters.AddWithValue("@WareHouseLocationId", wareHouseLocationId);\ndbCommand.Parameters.AddWithValue("@StockCount", minimumStockCount);\ndbCommand.Parameters.AddWithValue("@Price", maximumPrice);\n...\n\nConvert fetched data into Products\nAfter executing the query, you use a DbReader to put the fetched data into Products, one by one.\nwhile (dbReader.Read())\n\nReturns true as long as there is unread fetched data.\nId = dbReader.GetInt64(0),\nProductCode = dbReader.GetString(1),\n...\n\nThe 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.\nThe 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.\nOf course you'll also need a method to update a product. This if fairly similar, but instead of ExecuteReader you'll use `\npublic void UpdateProductPrice(int productId, decimal price)\n{\n const string sqlText = "UPDATE " + tableNameProducts\n + " SET Price = @Price"\n + " WHERE Id = @Id;";\n\n using (SQLiteCommand dbCommand = this.DbConnection.CreateCommand())\n {\n dbCommand.CommandText = sqlText;\n dbCommand.Parameters.AddWithValue("@Id", productId);\n dbCommand.Parameters.AddWithValue("@Price", productPrice);\n dbCommand.ExecuteNonQuery();\n }\n}\n\nUp to you to implement void UpdateProduct(Product product).\nOn to the ViewModel!\nDisplaying the Products\nNow 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:\nUsing 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:\npublic MyForm()\n{\n InitializeComponent();\n\n this.columnProductId.DataPropertyName = nameof(Product.Id);\n this.columnProductName.DataPropertyName = nameof(Product.Name);\n ...\n this.columnProductPrice.DataPropertyName = nameof(Product.Price);\n}\n\nThis 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.\nNow to display the products is a one liner:\nprivate BindingList<Product> DisplayedProducts\n{\n get => (BindingList<Product>) this.dataGridViewProducts.DataSource,\n set => this.dataGridViewProducts.DataSource = value;\n}\n\nThis 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.\nprivate IRepository Repository {get;} = new Repository();\n\nprivate IEnumerable<Product> FetchProductsToDisplay()\n{\n return this.Repository.FetchProductsToDisplay(...);\n}\n\npublic void InitProductDisplay()\n{\n this.DisplayedProducts = new BindingList<Product>(\n this.FetchProductsToDisplay().ToList());\n}\n\nAnd 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.\nFor example: if the operator indicates that he has finished changing the Products, he can press an OK, or Apply Now button:\nprivate void OnButtonApplyNow_Clicked(object sender, ...)\n{\n Collection<Product> editedProducts = this.Displayedproducts();\n\n // find out which Products are changed and save them in the repository\n this.ProcessEditedProducts(editedProducts);\n}\n\nNow 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.\nI 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.\nConclusion\nBy 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.\nYou 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.\nIf 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.",

2021-06-07T09:38:33