I am trying to pass my DataTable to a stored procedure. The DataTable holds the contents of an Excel sheet. The Excel sheet has two empty columns, five with text, and five with decimal numbers.
The empty columns are OppdragsMVAkode and OppdragsgebyrMVAkode.
If I run my application, I get this exception:
System.ArgumentException: Input string was not in a correct format. Couldn't store <> in OppdragsMVAkode Column. Expected type is Decimal.
If I add a temporary number, I get this exception:
System.Data.SqlClient.SqlException: Error converting data type nvarchar to numeric.
I don't get why it's reading it as a string.
My stored procedure (spGetTrips):
ALTER PROCEDURE [dbo].[spGetTrips]
@trips udtTripsPerMonth readonly
AS
BEGIN
INSERT INTO tblTripsPerMonth
SELECT
[KjøretøyID],
SUBSTRING([År], 7, 4),
SUBSTRING([Måned], 4, 2),
[Betaling (brutto)],
[Betaling (netto)],
[Bestillingsgebyr (netto)],
[Betalingsgebyr (netto)],
[OppdragsMVAkode],
CONCAT(LøyvehaverFakturaID, + 'UF-' + SUBSTRING([År], 9, 2) + SUBSTRING([Måned], 4, 2)),
[Oppdragsgebyr (netto)],
[OppdragsgebyrMVAkode],
CONCAT([RidelRegionFakturaID], + 'UF-' + SUBSTRING([År], 9, 2) + SUBSTRING([Måned], 4, 2))
FROM @trips
UPDATE tblTripsPerMonth
SET [OppdragsMVAkode] = (
SELECT [ID]
FROM [tblMVAkoder]
WHERE [ID] = 'MVAkode2'
);
UPDATE tblTripsPerMonth
SET [OppdragsgebyrMVAkode] = (
SELECT [ID]
FROM [tblMVAkoder]
WHERE [ID] = 'MVAkode5'
);
END
As you can see above, I am setting the value of the two empty columns with UPDATE clauses in the Stored Procedure. Whether they are empty in the Excel sheet, or with some preliminary value, and then to be overridden, I care not - I just want it to work.
Here's my User-Defined Table Type (udtTripsPerMonth):
CREATE TYPE [dbo].[udtTripsPerMonth] AS TABLE(
[KjøretøyID] [nvarchar](50) NULL,
[År] [nvarchar](50) NULL,
[Måned] [nvarchar](50) NULL,
[Betaling (brutto)] [decimal](10, 2) NULL,
[Betaling (netto)] [decimal](10, 2) NULL,
[Bestillingsgebyr (netto)] [decimal](10, 2) NULL,
[Betalingsgebyr (netto)] [decimal](10, 2) NULL,
[OppdragsMVAkode] [decimal](10, 2) NULL,
[LøyvehaverFakturaID] [nvarchar](50) NULL,
[Oppdragsgebyr (netto)] [decimal](10, 2) NULL,
[OppdragsgebyrMVAkode] [decimal](10, 2) NULL,
[RidelRegionFakturaID] [nvarchar](50) NULL
)
GO
And my table (tblTripsPerMonth):
CREATE TABLE [dbo].[tblTripsPerMonth](
[ID] [int] IDENTITY(1,1) NOT NULL,
[KjøretøyID] [nvarchar](50) NULL,
[År] [nvarchar](50) NULL,
[Måned] [nvarchar](50) NULL,
[Betaling (brutto)] [decimal](10, 2) NULL,
[Betaling (netto)] [decimal](10, 2) NULL,
[Bestillingsgebyr (netto)] [decimal](10, 2) NULL,
[Betalingsgebyr (netto)] [decimal](10, 2) NULL,
[OppdragsMVAkode] [decimal](10, 2) NULL,
[LøyvehaverFakturaID] [nvarchar](50) NULL,
[Oppdragsgebyr (netto)] [decimal](10, 2) NULL,
[OppdragsgebyrMVAkode] [decimal](10, 2) NULL,
[RidelRegionFakturaID] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
The other table I reference in the UPDATE clauses (tblMVAkoder):
CREATE TABLE [dbo].[tblMVAkoder](
[ID] [nvarchar](50) NOT NULL,
[Startdato] [date] NULL,
[Sluttdato] [date] NULL,
[MVAsats] [decimal](10, 2) NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
Here's my C# code (Excel to DataTable):
private void btnExport_Click(object sender, RoutedEventArgs e) {
OpenFileDialog of = new();
of.Filter = "Excel Files | *.xlsx;";
of.Title = "Importer Excel fil.";
if (of.ShowDialog() == true) {
dgPaidTrip.ItemsSource = ImportExceltoDataTable(of.FileName).DefaultView;
btnClearForm.IsEnabled = true;
}
using (SqlConnection con = new(ConnectionString.connectionString))
using (var cmd = new SqlCommand("spGetTrips", con) { CommandType = CommandType.StoredProcedure }) {
con.Open();
DataTable dt = ImportExceltoDataTable(of.FileName);
cmd.Parameters.Add(new SqlParameter("@trips", dt));
cmd.ExecuteNonQuery();
}
}
public static DataTable ImportExceltoDataTable(string filePath) {
using (XLWorkbook wb = new(filePath)) {
IXLWorksheet ws = wb.Worksheet(1);
int tl_Row = ws.FirstCellUsed().Address.RowNumber;
int tl_Col = ws.FirstCellUsed().Address.ColumnNumber;
int br_Row = ws.LastCellUsed().Address.RowNumber;
int br_Col = ws.LastCellUsed().Address.ColumnNumber;
DataTable dt = new();
dt.Columns.Add("KjøretøyID", typeof(string));
dt.Columns.Add("År", typeof(string));
dt.Columns.Add("Måned", typeof(string));
dt.Columns.Add("Betaling (brutto)", typeof(decimal));
dt.Columns.Add("Betaling (netto)", typeof(decimal));
dt.Columns.Add("Bestillingsgebyr (netto)", typeof(decimal));
dt.Columns.Add("Betalingsgebyr (netto)", typeof(decimal));
dt.Columns.Add("OppdragsMVAkode", typeof(decimal));
dt.Columns.Add("LøyvehaverFakturaID", typeof(string));
dt.Columns.Add("Oppdragsgebyr (netto)", typeof(decimal));
dt.Columns.Add("OppdragsgebyrMVAkode", typeof(decimal));
dt.Columns.Add("RidelRegionFakturaID", typeof(string));
IXLRow currentRow;
for (int dtRow = 0; dtRow < br_Row - tl_Row; dtRow++) {
currentRow = ws.Row(tl_Row + dtRow + 1);
dt.Rows.Add();
for (int dtCol = 0; dtCol < br_Col - tl_Col + 1; dtCol++) {
dt.Rows[dtRow][dtCol] = currentRow.Cell(tl_Col + dtCol).Value;
}
}
return dt;
}
}
As far as I can see, all my column types match, as well as their order.
I changed all my types to nvarchar, and the code "works".
But someone smarter than me told me it was not a good idea to fake the column types.
What am I missing?
To pass a null value, you need to set the column value as DBNull.Value. You can set DBNull like this
dt.Rows[dtRow][dtCol] = currentRow.Cell(tl_Col + dtCol).Value ?? (object)DBNull.Value;
You must also set SqlDBType TypeName and Direction properties
cmd.Parameters.Add(
new SqlParameter("@trips", SqlDBType.Structured)
{
TypeName = "dbo.udtTripsPerMonth",
Direction = ParameterDirection.Input,
Value = dt
});