Exporting DataTable to Excel with C# and OleDbConnection - Only column names are written

Question

I would like to write a DataSet with several DataTables into an Excel file. Each DataTable gets its own worksheet. It works so far, but only the column headings (= columns of the DataTable) are written per worksheet. Is there a way to debug why the values are not written?

I am also trying to recognise the data types, can it go wrong?

 public static void ExportExcelTable(string filename, DataSet dataset)
        {
            var excelConnectionString = GetExcelConnectionString(filename);
            using (var connection = new OleDbConnection(string.Format(excelConnectionString, filename)))
            {
                connection.Open();

                foreach (DataTable datatable in dataset.Tables)
                {
                    using (var createCmd = connection.CreateCommand())
                    {
                        createCmd.CommandText = CreateTable(datatable);
                        createCmd.ExecuteNonQuery();

                        using (var dataadapter = new OleDbDataAdapter($"SELECT * FROM [{datatable.TableName}]", connection))
                        {
                            using (var builder = new OleDbCommandBuilder(dataadapter))
                            {
                                builder.RefreshSchema();
                                builder.QuotePrefix = "[";
                                builder.QuoteSuffix = "]";
                                dataadapter.InsertCommand = builder.GetInsertCommand();
                                dataadapter.Update(datatable);
                            }
                        }
                    }
                }
            }
        }

        static string GetExcelConnectionString(string file)
        {
            var props = new Dictionary<string, string>();
            var extension = file.Split('.').Last();

            switch (extension)
            {
                case "xls":
                    props["Provider"] = "Microsoft.Jet.OLEDB.4.0";
                    props["Extended Properties"] = "Excel 8.0";
                    break;
                case "xlsx":
                    props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
                    props["Extended Properties"] = "Excel 12.0 XML";
                    break;
                default:
                    throw new Exception($"Fehler: {file}");
            }

            props["Data Source"] = file;

            var sb = new StringBuilder();

            foreach (var prop in props)
            {
                sb.Append(prop.Key);
                sb.Append('=');
                sb.Append(prop.Value);
                sb.Append(';');
            }

            return sb.ToString();
        }

        static string CreateTable(DataTable table)
        {
            string sqlsc = "CREATE TABLE " + table.TableName + " (";
            for (int i = 0; i < table.Columns.Count; i++)
            {
                sqlsc += "\n [" + table.Columns[i].ColumnName + "] ";
                string columnType = table.Columns[i].DataType.ToString();
                switch (columnType)
                {
                    case "System.Int32":
                        sqlsc += " int ";
                        break;
                    case "System.Int64":
                        sqlsc += " bigint ";
                        break;
                    case "System.Int16":
                        sqlsc += " smallint";
                        break;
                    case "System.Byte":
                        sqlsc += " tinyint";
                        break;
                    case "System.Decimal":
                        sqlsc += " decimal ";
                        break;
                    case "System.DateTime":
                        sqlsc += " datetime ";
                        break;
                    default:
                        sqlsc += " longtext";
                        break;
                }
                sqlsc += ",";
            }
            return sqlsc.Substring(0, sqlsc.Length - 1) + "\n)";
        }

Answer 1

A dataadapter uses the DataRow.RowState to determine which query to run. Rows need to be in a rowstate of Added to trigger the use of the InsertCommand. If you load a datatable with rows in such a way that they are not marked as Added (for example ImportRow or via some way that resulted in AcceptChanges being called e.g. you called it even though you did Rows.Add it or you Filled using a dataadapter with AcceptChangesDuringFill = true), no insert runs