Modifying Data and Updating Databases with C#
We have seen how to access data in both connected and disconnected models so in this tutorial we will see how to update, insert and delete records in both models. We will also see how you can retrieve a separate dataset containing only the changes.
- What are Active Data Objects?
- Connecting to a SQL Server Database with C#
- Consuming Data in Windows Forms C# Applications
- Using ADO.Net Controls in ASP.Net Pages
- Modifying Data and Updating Databases with C#
- Connecting to MySql Databases with C# / ASP.Net
- Reading Excel Spreadsheets with C# ADO.Net
- Creating SQL Server Stored Procedures in C#
- Calling SQL Server Stored Procedures from C#
- Importing and Exporting XML from a DataSet
Adverts Blocked Please disable AdBlocking software and allow me to set cookies so that I can continue providing free content and services.
Updates in the connected model are really quite straight forward. All you need to do is issue a SqlCommand with a valid SQL expression. This is easier to code but requires more effort to work out the logic if doing more complicated updates.
Updates in Connected Mode
SqlConnection con = new SqlConnection(connectionString); con.Open(); SqlCommand cmd = new SqlCommand("UPDATE Products SET ProductName = 'egerg' WHERE ProductID = 3", con); cmd.ExecuteReader(); con.Close();
If you need to perform a lot of updates you can either reproduce the code or create a method to query the database If you use my method, don't forget to close the connection each time it is called otherwise you will drain server resources.
dataReader da; da = queryDatabase("UPDATE Customers SET ContactName = 'Jane Doe' WHERE CompanyName = 'Acme Inc'", connectionString); da.Close(); da = queryDatabase("UPDATE Products SET ProductName = 'Widgets' WHERE ProductID = 3", connectionString); da.Close();
This method will return the SELECTed records or the number of records affected by the updates. If all you were doing is updating the records you could modify the method to close the connection each time. Alternatively, you could pass in a connection object instead of a connection string, that way if you have many instructions to execute you are not constantly opening and closing connections.
Updates in Disconnected Mode
Start off with a blank project, either Windows Forms or ASP.Net, and setup a database connection if you do not have one already. Drag a DataGridView object from the toolbox to the form and configure its data source using "Add Project Data Source" menu option within Grid View Tasks. Add a new data source and select the Northwind database connection. Configure the data source to show the Customers table and finish the wizard. This will create a DataSource, TableAdapter and BindingSource and link everything to the table.
If you run the application it will display the grid view with all the records from the Customers table. You can double-click on a cell to edit it, so go ahead and make a few changes. Close the application and restart it, notice how the changes you made are no longer visible. This is because we are working in disconnected mode - the changes are not sent to the server until the update method of the data adapter is called.
The updates are handled by the data adapter and it will store all the updates made to data-bound controls for all records, so only one update command needs to be issued, of course, you may wish to update more often if there are large numbers of changes to prevent data loss. This code will send all the updates to the server:
You can see all the updated records (or the records that have not been updated) by using a DataView and a DataViewRowState object. You can filter the current data grid view or you can create a new data grid view with only the updated records.
DataView dv = new DataView(nORTHWNDDataSet.Tables["Products"]); dv.RowFilter = DataViewRowState.ModifiedCurrent; dataGridView1.DataSource = dv;
Last updated on: Friday 23rd June 2017
There are no comments for this post. Be the first!