0

I have created a WPF application that has a graphical user interface that allows users to upload Excel files. These files are supposed to be stored in a designated SQL Server database. I would like each file to be stored as its own table and for the columns to match up with the columns in the file. However, the files aren't even saving to the database. But, when my application debugs, it shows an error that says the file has already been uploaded into the database. When I run a query to find it, it is in there, but with no data and not stored in tables? Here is a snippet of the code that I believe is relevant:

 // Backup Excel data to the database
string fileName = System.IO.Path.GetFileNameWithoutExtension(files[i]); // Get the file name without extension
string tableName = fileName; // Use the file name as the table name
string connectionString = "Connection string"; // I have my connection string in the actual code. 
BackupExcelDataToDatabase(files[i], tableName, connectionString);
 private void BackupExcelDataToDatabase(string excelFilePath, string tableName, string connectionString)
        {
            // Read the Excel file
            using (ExcelPackage package = new ExcelPackage(new FileInfo(excelFilePath)))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets[1]; // Assuming the data is in the first worksheet

                // Create a DataTable to hold the Excel data
                DataTable dataTable = new DataTable();

                // Define the column names and types based on the specific column definitions
                dataTable.Columns.Add("Column1", typeof(string)); // Varchar(50)
                dataTable.Columns.Add("Column2", typeof(int)); // Integer
                dataTable.Columns.Add("Column3", typeof(string)); // Varchar(50)
                dataTable.Columns.Add("Column4", typeof(int)); // Integer
                dataTable.Columns.Add("Column5", typeof(DateTime)); // Date
                dataTable.Columns.Add("Column6", typeof(decimal)); // Decimal
                dataTable.Columns.Add("Column7", typeof(decimal)); // Decimal
                dataTable.Columns.Add("Column8", typeof(decimal)); // Decimal
                dataTable.Columns.Add("Column9", typeof(decimal)); // Decimal
                dataTable.Columns.Add("Column10", typeof(decimal)); // Decimal
                dataTable.Columns.Add("Column11", typeof(decimal)); // Decimal
                dataTable.Columns.Add("Column12", typeof(decimal)); // Decimal
                dataTable.Columns.Add("Column13", typeof(string)); // Varchar(100)
                dataTable.Columns.Add("Column14", typeof(int)); // Integer
                dataTable.Columns.Add("Column15", typeof(string)); // Varchar(50)
                dataTable.Columns.Add("Column16", typeof(string)); // Varchar(50)
                dataTable.Columns.Add("Column17", typeof(string)); // Varchar(50)
                dataTable.Columns.Add("Column18", typeof(string)); // Varchar(50)
                dataTable.Columns.Add("Column19", typeof(string)); // Varchar(50)
                dataTable.Columns.Add("Column20", typeof(string)); // Varchar(50)

                // Read the Excel data and populate the DataTable
                for (int row = 2; row <= worksheet.Dimension.Rows; row++) // Assuming the first row contains column headers
                {
                    DataRow dataRow = dataTable.NewRow();
                    for (int col = 1; col <= worksheet.Dimension.Columns; col++)
                    {
                        string columnName = $"Column{col}";
                        object cellValue = worksheet.Cells[row, col].Value;

                        if (cellValue != null)
                        {
                            dataRow[columnName] = cellValue.ToString();
                        }
                    }
                    dataTable.Rows.Add(dataRow);
                }

                // Insert the DataTable into the SQL Server database
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    // Create the table with the specified table name
                    using (SqlCommand createCommand = new SqlCommand($"CREATE TABLE [{tableName}] ({GetTableColumnsDefinition(dataTable)})",
                        connection))
                    {
                        createCommand.ExecuteNonQuery();
                    }

                    // Insert data into the table using SqlBulkCopy
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                    {
                        bulkCopy.DestinationTableName = tableName;
                        bulkCopy.WriteToServer(dataTable);
                    }
                }
            }
        }

        private string GetTableColumnsDefinition(DataTable dataTable)
        {
            StringBuilder sb = new StringBuilder();

            foreach (DataColumn column in dataTable.Columns)
            {
                string columnName = column.ColumnName;
                string dataType;

                // Get the data type based on the column's CLR type
                if (column.DataType == typeof(string))
                {
                    dataType = "VARCHAR(50)";
                }
                else if (column.DataType == typeof(int))
                {
                    dataType = "INT";
                }
                else if (column.DataType == typeof(DateTime))
                {
                    dataType = "DATE";
                }
                else if (column.DataType == typeof(decimal))
                {
                    dataType = "DECIMAL(18,2)"; // Adjust precision and scale as needed
                }
                else
                {
                    dataType = "NVARCHAR(MAX)";
                }

                sb.Append($"{columnName} {dataType},");
            }

            // Remove the trailing comma
            if (sb.Length > 0)
            {
                sb.Length--;
            }

            return sb.ToString();
        }

I have tried to create a different class. I have defined each column with their corresponding data types. I have used OfficeOpenXml and System.Data.SqlClient.

  • you create your table, each time but maybe the table was already created? – siggemannen Jul 17 '23 at 14:24
  • There are no tables that shows under the database I specified. However, I did find it in the master database in the server. The file was not uploaded in it though. Regardless, the data from the file is not saving. – appdevlaur Jul 17 '23 at 14:38
  • Okey, can you attach the actual error you're getting and on what line are you getting it on – siggemannen Jul 17 '23 at 14:51
  • I wonder why you save the files in a such complicated way. Wouldn't it be easier to save them as a binary? – montonero Jul 17 '23 at 16:19

0 Answers0