无法使用C#将数据从csv插入SQL Server

41zrol4v  于 2023-06-19  发布在  C#
关注(0)|答案(2)|浏览(124)

我目前在尝试使用C#将CSV文件中的数据插入SQL Server数据库时遇到了一个问题。我在ImportController.cs文件中实现了一个方法，它读取CSV文件，解析数据，并尝试使用SQL INSERT语句将其插入数据库。
但是，当我单击import按钮并触发ImportData操作时，数据没有保存到数据库中。相反，我在控制台中得到一个输出，显示更新后的表数据为数组的数组，但实际上没有数据插入数据库。
下面是我的ImportController.cs文件中的相关代码片段：

using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using System;
using System.Data.SqlClient;
using System.IO;
using System.Text;

public class ImportController : Controller
{
    private readonly IConfiguration Configuration;

    public ImportController(IConfiguration configuration)
    {
        Configuration = configuration;
    }

    public IActionResult Index()
    {
        return View();
    }

    [HttpPost]
    public IActionResult Upload(ImportModel model)
    {
        if (model.File != null && model.File.Length > 0)
        {
            model.FileName = Path.GetFileName(model.File.FileName);

            using (var reader = new StreamReader(model.File.OpenReadStream(), Encoding.UTF8))
            {
                model.FileContent = reader.ReadToEnd();
            }
        }

        return View("Upload", model);
    }

    [HttpPost]
    public IActionResult ImportData(ImportModel model)
    {
        if (!string.IsNullOrEmpty(model.FileContent))
        {
            var connectionString = Configuration.GetConnectionString("DefaultConnection");
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        var rows = model.FileContent.Split(new[] { "\r\n", "\n" }, StringSplitOptions.RemoveEmptyEntries);

                        foreach (var row in rows)
                        {
                            var cells = row.Split(",");
                            // Save cells to the database using SQL INSERT statement or your preferred ORM
                            var insertCommand = "INSERT INTO dbo.DEV (EmployeeID, FullName, JobTitle, Department, BusinessUnit, Gender, Ethnicity, Age, HireDate, Country, City) VALUES (@EmployeeID, @FullName, @JobTitle, @Department, @BusinessUnit, @Gender, @Ethnicity, @Age, @HireDate, @Country, @City)";

                            using (var command = new SqlCommand(insertCommand, connection, transaction))
                            {
                                command.Parameters.AddWithValue("@EmployeeID", cells[0]);
                                command.Parameters.AddWithValue("@FullName", cells[1]);
                                command.Parameters.AddWithValue("@JobTitle", cells[2]);
                                command.Parameters.AddWithValue("@Department", cells[3]);
                                command.Parameters.AddWithValue("@BusinessUnit", cells[4]);
                                command.Parameters.AddWithValue("@Gender", cells[5]);
                                command.Parameters.AddWithValue("@Ethnicity", cells[6]);
                                command.Parameters.AddWithValue("@Age", cells[7]);
                                command.Parameters.AddWithValue("@HireDate", cells[8]);
                                command.Parameters.AddWithValue("@Country", cells[9]);
                                command.Parameters.AddWithValue("@City", cells[10]);
                                command.ExecuteNonQuery();
                                Console.WriteLine("Executing query: " + insertCommand);
                            }
                        }

                        transaction.Commit();
                        // Optionally, you can redirect to a success page or return a success message
                        // return RedirectToAction("Index", "Home");
                        Console.WriteLine("Data saved successfully");
                    }
                    catch (Exception ex)
                    {
                        // Handle any errors and rollback the transaction
                        transaction.Rollback();
                        ModelState.AddModelError("", "An error occurred while importing data. Please try again.");
                        // Optionally, you can log the error for further investigation
                    }
                }
            }
        }

        // If no file content is present or import fails, return to the upload page
        return View("Upload", model);
    }
}

这是我的Upload.cshtml文件

@model ImportModel

@{
    ViewData["Title"] = "Import Page";
}

<center>
    <h1>Import Data</h1>
</center>

<form asp-controller="Import" asp-action="Upload" method="post" enctype="multipart/form-data" class="my-4">
    <div class="form-group">
        <label for="fileInput">Select File:</label>
        <div class="d-flex">
            <input id="fileInput" type="file" name="file" class="form-control-file" accept=".csv, application/vnd.ms-excel" required>
            <button type="submit" class="btn btn-primary ml-2">Upload</button>
        </div>
    </div>
</form>

@if (Model != null && !string.IsNullOrEmpty(Model.FileName))
{
    <h4>Uploaded File: <span id="fileName">@Model.FileName</span></h4>
}

@if (!string.IsNullOrEmpty(Model.FileContent))
{
    <h4>File Contents:</h4>
    <div id="fileContents" class="table-responsive">
        <table id="editableTable" class="table table-bordered">
            @foreach (var row in Model.FileContent.Split(new[] { "\r\n", "\n" }, StringSplitOptions.RemoveEmptyEntries))
            {
                <tr>
                    @foreach (var cell in row.Split(","))
                    {
                        <td contenteditable="true">@cell</td>
                    }
                </tr>
            }
        </table>
    </div>
    <br />
    <button id="importBtn" type="button" class="btn btn-primary">Import</button>
    <button id="clearBtn" type="button" class="btn btn-primary">Clear</button>
    <a asp-controller="Home" asp-action="Index" class="btn btn-primary">Home</a>
}

@section Scripts {
    <script>
        var fileInput = document.getElementById('fileInput');
        var importBtn = document.getElementById('importBtn');
        var clearBtn = document.getElementById('clearBtn');
        var fileNameElement = document.getElementById('fileName');
        var editableTable = document.getElementById('editableTable');

        fileInput.addEventListener('change', function () {
            var file = fileInput.files[0];
            if (file) {
                var reader = new FileReader();
                reader.onload = function (e) {
                    document.getElementById('fileContents').innerHTML = e.target.result;
                    importBtn.disabled = false;
                    clearBtn.disabled = false;
                    fileNameElement.textContent = file.name;
                };
                reader.readAsText(file);
            } else {
                fileNameElement.textContent = '';
                document.getElementById('fileContents').innerHTML = '';
                importBtn.disabled = true;
                clearBtn.disabled = true;
            }
        });

        clearBtn.addEventListener('click', function () {
            fileInput.value = '';
            fileNameElement.textContent = '';
            document.getElementById('fileContents').innerHTML = '';
            importBtn.disabled = true;
            clearBtn.disabled = true;
        });

        editableTable.addEventListener('input', function () {
            importBtn.disabled = false;
        });

        importBtn.addEventListener('click', function () {
            var updatedTableData = [];
            var rows = editableTable.getElementsByTagName('tr');
            for (var i = 0; i < rows.length; i++) {
                var cells = rows[i].getElementsByTagName('td');
                var rowData = [];
                for (var j = 0; j < cells.length; j++) {
                    rowData.push(cells[j].innerText);
                    cells[j].setAttribute('data-original-value', cells[j].innerText);
                }
                updatedTableData.push(rowData);
            }
            // Send updatedTableData to the server for database import
            console.log('Updated table data:', updatedTableData);
        });
    </script>
}

最后，这是我的appsettings.json文件的连接字符串：

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "Server=(local)\\SQLEXPRESS;Database=Test;Integrated Security=True;"
  }
}

这是我在控制台中得到的输出：
更新表格数据：（30）[阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）、阵列（11）] 0：
展开后，我得到了数组形式的所有数据

这是调试的输出

Microsoft.Hosting.Lifetime: Information: Application started. Press Ctrl+C to shut down.
Microsoft.Hosting.Lifetime: Information: Hosting environment: Development
Microsoft.Hosting.Lifetime: Information: Content root path: C:\Users\ash\source\repos\Demo\
'Demo.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\6.0.16\System.Threading.Tasks.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'Demo.exe' (CoreCLR: clrhost): Loaded 'Anonymously Hosted DynamicMethods Assembly'. 
'Demo.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App\6.0.16\Microsoft.AspNetCore.WebUtilities.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'Demo.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\6.0.16\System.Net.Http.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'Demo.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\6.0.16\System.Net.NameResolution.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'Demo.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\6.0.16\System.Net.WebSockets.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
The thread 0x30a8 has exited with code 0 (0x0).
The thread 0x3f88 has exited with code 0 (0x0).
The thread 0xbf4 has exited with code 0 (0x0).
The program '[6788] Demo.exe' has exited with code 4294967295 (0xffffffff).
csv

2条答案

按热度按时间
mzmfm0qo

mzmfm0qo1#

要对此进行调试，请执行以下操作：

catch (Exception ex)
{
  /* OTHER LOGIC */
  Debugger.Break(); // Add this line.
  // And mouse over ex to see the actual exception.
}
赞(0）分享  回复(0）举报  2023-06-19
gxwragnw

gxwragnw2#

我能解决我的问题。我修改了代码，以便在插入之前使用bulkCopy和Map进行检查
已更新“ImportController.cs”

using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

public class ImportController : Controller
{
    private readonly IConfiguration _configuration;

    public ImportController(IConfiguration configuration)
    {
        _configuration = configuration;
    }

    public IActionResult Index()
    {
        return View();
    }

    [HttpPost]
    public async Task<IActionResult> Upload(ImportModel model)
    {
        if (model.File != null && model.File.Length > 0)
        {
            model.FileName = Path.GetFileName(model.File.FileName);

            using (var memoryStream = new MemoryStream())
            {
                await model.File.CopyToAsync(memoryStream);
                var fileBytes = memoryStream.ToArray();
                model.FileContent = Encoding.UTF8.GetString(fileBytes);

                var columnMapping = new Dictionary<string, string>
                {
                    { "Employee ID", "EmployeeID" },
                    { "Full Name", "FullName" },
                    { "Job Title", "JobTitle" },
                    { "Department", "Department" },
                    { "Business Unit", "BusinessUnit" },
                    { "Gender", "Gender" },
                    { "Ethnicity", "Ethnicity" },
                    { "Age", "Age" },
                    { "Hire Date", "HireDate" },
                    { "Country", "Country" },
                    { "City", "City" }
                };

                var rows = model.FileContent.Split(Environment.NewLine, StringSplitOptions.RemoveEmptyEntries);
                var dataRows = rows.Skip(1); // Skip the header row
                var dataTable = CreateDataTable(columnMapping.Values);

                foreach (var row in dataRows)
                {
                    var cells = row.Split(",");
                    if (cells.Length == columnMapping.Count)
                    {
                        var dataRow = dataTable.NewRow();

                        for (var i = 0; i < cells.Length; i++)
                        {
                            var columnName = columnMapping.ElementAt(i).Value;
                            dataRow[columnName] = cells[i];
                        }

                        dataTable.Rows.Add(dataRow);
                    }
                }

                await SaveDataToDatabase(dataTable);
            }
        }

        return View("Upload", model);
    }

    private DataTable CreateDataTable(IEnumerable<string> columnNames)
    {
        var dataTable = new DataTable();
        foreach (var columnName in columnNames)
        {
            dataTable.Columns.Add(columnName);
        }
        return dataTable;
    }

    private async Task SaveDataToDatabase(DataTable dataTable)
    {
        var connectionString = _configuration.GetConnectionString("DefaultConnection");
        using (var connection = new SqlConnection(connectionString))
        {
            await connection.OpenAsync();

            using (var bulkCopy = new SqlBulkCopy(connection))
            {
                bulkCopy.DestinationTableName = "YourTableName";
                foreach (DataColumn column in dataTable.Columns)
                {
                    bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
                }
                await bulkCopy.WriteToServerAsync(dataTable);
            }
        }
    }
}

更新'Upload.cshtml'

@model ImportModel

@{
    ViewData["Title"] = "Import Page";
}

<center>
    <h1>Import Data</h1>
</center>

<form asp-controller="Import" asp-action="Upload" method="post" enctype="multipart/form-data" class="my-4">
    <div class="form-group">
        <label for="fileInput">Select File:</label>
        <div class="d-flex">
            <input id="fileInput" type="file" name="file" class="form-control-file" accept=".csv, application/vnd.ms-excel" required>
            <button type="submit" class="btn btn-primary ml-2">Upload</button>
        </div>
    </div>
</form>

@if (Model != null && !string.IsNullOrEmpty(Model.FileName))
{
    <h4>Uploaded File: <span id="fileName">@Model.FileName</span></h4>
}

@if (!string.IsNullOrEmpty(Model.FileContent))
{
    <h4>File Contents:</h4>
    <div id="fileContents" class="table-responsive">
        <table id="editableTable" class="table table-bordered">
            @foreach (var row in Model.FileContent.Split(Environment.NewLine, StringSplitOptions.RemoveEmptyEntries))
            {
                <tr>
                    @foreach (var cell in row.Split(","))
                    {
                        <td contenteditable="true">@cell</td>
                    }
                </tr>
            }
        </table>
    </div>
    <br />
    <button id="importBtn" type="button" class="btn btn-primary">Import</button>
    <button id="clearBtn" type="button" class="btn btn-primary">Clear</button>
    <a asp-controller="Home" asp-action="Index" class="btn btn-primary">Home</a>
}

@section Scripts {
    <script>
        var fileInput = document.getElementById('fileInput');
        var importBtn = document.getElementById('importBtn');
        var clearBtn = document.getElementById('clearBtn');
        var fileNameElement = document.getElementById('fileName');
        var editableTable = document.getElementById('editableTable');

        fileInput.addEventListener('change', function () {
            var file = fileInput.files[0];
            if (file) {
                var reader = new FileReader();
                reader.onload = function (e) {
                    document.getElementById('fileContents').innerHTML = e.target.result;
                    importBtn.disabled = false;
                    clearBtn.disabled = false;
                    fileNameElement.textContent = file.name;
                };
                reader.readAsText(file);
            } else {
                fileNameElement.textContent = '';
                document.getElementById('fileContents').innerHTML = '';
                importBtn.disabled = true;
                clearBtn.disabled = true;
            }
        });

        clearBtn.addEventListener('click', function () {
            fileInput.value = '';
            fileNameElement.textContent = '';
            document.getElementById('fileContents').innerHTML = '';
            importBtn.disabled = true;
            clearBtn.disabled = true;
        });

        editableTable.addEventListener('input', function () {
            importBtn.disabled = false;
        });

        importBtn.addEventListener('click', function () {
            var updatedTableData = [];
            var rows = editableTable.getElementsByTagName('tr');
            for (var i = 0; i < rows.length; i++) {
                var cells = rows[i].getElementsByTagName('td');
                var rowData = [];
                for (var j = 0; j < cells.length; j++) {
                    rowData.push(cells[j].innerText);
                    cells[j].setAttribute('data-original-value', cells[j].innerText);
                }
                updatedTableData.push(rowData);
            }
            // Send updatedTableData to the server for database import
            console.log('Updated table data:', updatedTableData);
        });
    </script>
}
赞(0）分享  回复(0）举报  2023-06-19
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com