How to update an Excel worksheet using DataSet and the OleDbDataAdapter

  • Article

One very convenient thing in ADO.Net is the DataAdapters.

These give you an easy way to fill DataTables and DataSets, and also to update the backing data source with very little effort.

However, in order to be able to do INSERT/UPDATE/DELETE you have to set the appropriate commands for the Adapter. Ie:

OleDbDataAdapter adapter = new OleDbDataAdapter();

adapter.UpdateCommand;

adapter.InsertCommand;

adapter.DeleteCommand;

Either you can create those commands yourself, or you can use a CommandBuilder and pass the Adapter to the CommandBuilder constructor, like so:

OleDbDataAdapter adapter = new OleDbDataAdapter();

OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);

this will automatically create these commands for the DataAdapter (not that this will cause an extra roundtrip to the server to collect the metadata).

However, at least one primary key or unique column must be present in the SELECT, if not, an InvalidOperation exception is generated, and the commands are not generated.

See more here; Generating Commands with CommandBuilders (ADO.NET)

So to sum up so far, if you want to update using the Adapter, then you must have the corresponding INSERT/UPDATE/DELETE commands set, otherwise you will get

System.InvalidOperationException : Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

and if the SELECT doesn’t return any columns that are primary keys you will get this:

System.InvalidOperationException : Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

So that seems easy enough, just create and associate a CommandBuilder and/or make sure that the primary key is included in the SELECT statement.

But what if the data source doesn’t contain any primary keys? For example if the backing data source is an Excel file, which doesn’t contain keys.

Then you will have to write the logic for the INSERT and UPDATE commands yourself, and this is how it is done.

As usual, let’s do this by example:

.1 Create an .xls file called AdapterTest.xls and put it in C:\Temp.

.2 Create the content of Sheet1$ so it looks as follows and save.

FirstName LastName Age

John Smith 30

Bo Diddley 45

Carl Key 20

.3 Create a new Console Application that looks as follows (it will insert one row and update one row):

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.OleDb;

using System.Data;

namespace ExcelDataAdapter

{

    class Program

    {

        static void Main(string[] args)

        {

            string xlsFile = @"C:\\Temp\\AdapterTest.xls";

            string xlsSheet = @"Sheet1$";

            // HDR=Yes means that the first row in the range is the header row (or field names) by default.

            // If the first range does not contain headers, you can specify HDR=No in the extended properties in your connection string.

          string connectionstring = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;'", xlsFile);

            // Create connection

            OleDbConnection oleDBConnection = new OleDbConnection(connectionstring);

            // Create the dataadapter with the select to get all rows in in the xls

            OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT FirstName, LastName, Age FROM [" + xlsSheet + "]", oleDBConnection);

            // Since there is no pk in Excel, using a command builder will not help here.

            //OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);

            // Create the dataset and fill it by using the adapter.

            DataSet dataset = new DataSet();

            adapter.Fill(dataset);//, "PersonTable");

            // Time to create the INSERT/UPDATE commands for the Adapter,

            // the way to do this is to use parameterized commands.

            // *** INSERT COMMAND ***

       adapter.InsertCommand = new OleDbCommand("INSERT INTO [" + xlsSheet + "] (FirstName, LastName, Age) VALUES (?, ?, ?)", oleDBConnection);

            adapter.InsertCommand.Parameters.Add("@FirstName", OleDbType.VarChar, 255).SourceColumn = "FirstName";

            adapter.InsertCommand.Parameters.Add("@LastName", OleDbType.Char, 255).SourceColumn = "LastName";

            adapter.InsertCommand.Parameters.Add("@Age", OleDbType.Char, 255).SourceColumn = "Age";

            // *** UPDATE COMMAND ***

      adapter.UpdateCommand = new OleDbCommand("UPDATE [" + xlsSheet + "] SET FirstName = ?, LastName = ?, Age = ?" +

                                                        " WHERE FirstName = ? AND LastName = ? AND Age = ?", oleDBConnection);

            adapter.UpdateCommand.Parameters.Add("@FirstName", OleDbType.Char, 255).SourceColumn = "FirstName";

            adapter.UpdateCommand.Parameters.Add("@LastName", OleDbType.Char, 255).SourceColumn = "LastName";

            adapter.UpdateCommand.Parameters.Add("@Age", OleDbType.Char, 255).SourceColumn = "Age";

            // For Updates, we need to provide the old values so that we only update the corresponding row.

            adapter.UpdateCommand.Parameters.Add("@OldFirstName", OleDbType.Char, 255, "FirstName").SourceVersion = DataRowVersion.Original;

            adapter.UpdateCommand.Parameters.Add("@OldLastName", OleDbType.Char, 255, "LastName").SourceVersion = DataRowVersion.Original;

            adapter.UpdateCommand.Parameters.Add("@OldAge", OleDbType.Char, 255, "Age").SourceVersion = DataRowVersion.Original;

            // Insert a new row

            DataRow newPersonRow = dataset.Tables[0].NewRow();

            newPersonRow["FirstName"] = "New";

            newPersonRow["LastName"] = "Person";

            newPersonRow["Age"] = "100";

            dataset.Tables[0].Rows.Add(newPersonRow);

            // Updates the first row

            dataset.Tables[0].Rows[0]["FirstName"] = "Updated";

            dataset.Tables[0].Rows[0]["LastName"] = "Person";

            dataset.Tables[0].Rows[0]["Age"] = "55";

            // Call update on the adapter to save all the changes to the dataset

            adapter.Update(dataset);

        }

    }

}

The above code reads in the data in the dataset and then inserts one row and updates another.

Where is the DeleteCommand I hear you say, well the Jet OLE DB provider does not support delete operations for Excel workbooks.

Therefore, you cannot delete records in a workbook by using ADO or ADO.NET. See for example this KB.

If you are writing your own DeleteCommand against, for example, SQL Server, just make sure there is a primary key in the select,

then a normal DELETE FROM … WHERE … will do the trick.

This is no way a comprehensive explanation on how to do this, see this as an introduction and an example on how to do this with Excel.