我目前在尝试使用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).
2条答案
按热度按时间mzmfm0qo1#
要对此进行调试,请执行以下操作:
gxwragnw2#
我能解决我的问题。我修改了代码,以便在插入之前使用bulkCopy和Map进行检查
已更新“ImportController.cs”
更新'Upload.cshtml'