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.