我有一个C#应用程序，用于从.XLSX 文件，并将数据插入到 SQL 服务器表中。
例如，我有一个带有标识和 3 列的数据表。 当我执行SQL命令时。我有这个错误：
插入到表变量不允许的标识列中。这 表值参数"@product"的数据不符合 参数的表类型。SQL 服务器错误是： 200，状态： 7
存储过程
ALTER PROCEDURE [dbo].[InserimentoDatiExcel](@product [udtInserimentoDatiExcel] readonly)
AS
BEGIN
INSERT INTO Prova
(ID, PIPPO, PLUTO, PAPERINO)
SELECT *
FROM @product
--FROM Prova
--WHERE ID IN (SELECT ID FROM @product)
END
桌子
CREATE TYPE [dbo].[udtInserimentoDatiExcel] AS TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[PIPPO] [varchar](1) NOT NULL,
[PLUTO] [varchar](1) NOT NULL,
[PAPERINO] [varchar](1) NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = ON)
)
GO
数据表：图像
法典
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
namespace BatchReadExcel
{
class Program
{
static void Main()
{
try
{
DataTable dt = new DataTable();
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(@"C:Usersf.pianoDocumentsprova.xlsx", false))
{
WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild<SheetData>();
IEnumerable<Row> rows = sheetData.Descendants<Row>();
//TEST
//dt.Columns.Add("ID", typeof(Int32));
dt.Columns.Add("ID", typeof(Guid));
dt.Columns["ID"].AutoIncrement = true;
dt.Columns["ID"].AutoIncrementSeed = 0;
dt.Columns["ID"].AutoIncrementStep = 1;
//TEST
foreach (Cell cell in rows.ElementAt(0))
{
dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));
}
foreach (Row row in rows) //this will also include your header row...
{
DataRow tempRow = dt.NewRow();
/*Si inserisce columnIndex = 1 quando devi utilizzare
l'autoincrement sulla prima colonna (in questo caso ID)*/
int columnIndex = 1;
foreach (Cell cell in row.Descendants<Cell>())
{
// Gets the column index of the cell with data
int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(cell.CellReference));
//cellColumnIndex--; //zero based index
if (columnIndex < cellColumnIndex)
{
do
{
tempRow[columnIndex] = 1;
columnIndex++;
}
while (columnIndex < cellColumnIndex);
}
tempRow[columnIndex] = GetCellValue(spreadSheetDocument, cell);
columnIndex++;
}
dt.Rows.Add(tempRow);
}
}
dt.Rows.RemoveAt(0); //rimuove la prima riga in cui ci sono le colonne
//leggo le righe di una colonna specifica
//string pippoRows = "";
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// //pippoRows = Convert.ToInt32(dt.Rows[i]["PIPPO"]);
// pippoRows = Convert.ToString(dt.Rows[i]["PIPPO"]);
// //Similarly for QuantityInIssueUnit_uom.
//}
//TODO: Insert data in SQL Server
var stringaConnessione = Properties.Settings.Default.connectionString;
SqlConnection sqlcon = new SqlConnection();
sqlcon.ConnectionString = stringaConnessione;
SqlCommand sqlcmd = new SqlCommand("InserimentoDatiExcel", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
//sqlcmd.Parameters.AddWithValue("@pippo", dt.Columns["PIPPO"].Container);
sqlcmd.Parameters.AddWithValue("@product", dt);
//sqlcmd.Parameters.Add(new SqlParameter("@pippo", dt.Columns["PIPPO"].ColumnMapping));
sqlcon.Open();
sqlcmd.ExecuteNonQuery();
sqlcon.Close();
}
catch (Exception ex)
{
}
}
/// <summary>
/// Given a cell name, parses the specified cell to get the column name.
/// </summary>
/// <param name="cellReference">Address of the cell (ie. B2)</param>
/// <returns>Column Name (ie. B)</returns>
public static string GetColumnName(string cellReference)
{
// Create a regular expression to match the column name portion of the cell name.
Regex regex = new Regex("[A-Za-z]+");
Match match = regex.Match(cellReference);
return match.Value;
}
/// <summary>
/// Given just the column name (no row index), it will return the zero based column index.
/// Note: This method will only handle columns with a length of up to two (ie. A to Z and AA to ZZ).
/// A length of three can be implemented when needed.
/// </summary>
/// <param name="columnName">Column Name (ie. A or AB)</param>
/// <returns>Zero based index if the conversion was successful; otherwise null</returns>
public static int? GetColumnIndexFromName(string columnName)
{
//return columnIndex;
string name = columnName;
int number = 0;
int pow = 1;
for (int i = name.Length - 1; i >= 0; i--)
{
number += (name[i] - 'A' + 1) * pow;
pow *= 26;
}
return number;
}
public static string GetCellValue(SpreadsheetDocument document, Cell cell)
{
SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
if (cell.CellValue == null)
{
return "";
}
string value = cell.CellValue.InnerXml;
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
}
else
{
return value;
}
}
}
}
在您的第一个代码片段中，您将插入 ID 列，该列是标识列(无法插入(。
您需要更新代码以不包含 ID 列。