ASP.NET MVC存储过程导入Excel至SQL Server时日期时间类型异常

Hey there, let's dig into why your Date_d_arrivée and heure_d_arrivée values are ending up as 0001-01-01 and 00:00:00.000000 when importing Excel data. This usually boils down to issues with data type recognition during reading, parameter handling, or table schema setup. Here's how to fix it step by step:

1. Verify Excel Cell Formatting #

First, double-check your Excel file:

• Ensure cells containing dates/times are formatted as Date or Time (not Text or General). Sometimes values that look like dates are actually stored as text, which can cause import failures.
• If you have mixed formats (some cells as text, some as dates), Excel might misinterpret the column type, leading to null values that fall back to the default.

2. Fix Excel Data Reading in ASP.NET MVC #

The way you read Excel data in your MVC app is critical. Let's cover common libraries:

For EPPlus (most popular for modern Excel files): #

Make sure you're explicitly casting to the correct date/time types, handling nulls properly:

// Example: Reading a date cell
DateTime? arrivalDate = worksheet.Cells[row, dateColumnIndex].GetValue<DateTime?>();

// Reading a time cell
TimeSpan? arrivalTime = worksheet.Cells[row, timeColumnIndex].GetValue<TimeSpan?>();

If your Excel dates are stored as text (e.g., "dd/MM/yyyy"), parse them explicitly:

string dateText = worksheet.Cells[row, dateColumnIndex].Text;
DateTime arrivalDate;
if (!DateTime.TryParse(dateText, CultureInfo.InvariantCulture, DateTimeStyles.None, out arrivalDate))
{
    // Handle invalid date (log error, skip row, etc.)
    arrivalDate = default; // or set to null if allowed
}

For OleDb: #

Adjust your connection string to force proper type detection. Add TypeGuessRows=0 to scan all rows instead of just the first 8, and IMEX=1 to handle mixed types:

string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\YourFile.xlsx;
                           Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text""";

When reading, convert the OleDb value to DateTime/TimeSpan:

var dateValue = reader["Date_d_arrivée"];
DateTime? arrivalDate = dateValue != DBNull.Value ? Convert.ToDateTime(dateValue) : null;

var timeValue = reader["heure_d_arrivée"];
TimeSpan? arrivalTime = timeValue != DBNull.Value ? TimeSpan.Parse(timeValue.ToString()) : null;

3. Ensure Correct Parameter Passing to the Stored Procedure #

When calling your usp_InsertNewEmployeeDetails1 stored procedure, make sure you're passing values correctly (not accidentally passing null or mismatched types):

using (SqlCommand cmd = new SqlCommand("usp_InsertNewEmployeeDetails1", connection))
{
    cmd.CommandType = CommandType.StoredProcedure;
    
    // Pass date parameter: use DBNull.Value if the value is null
    cmd.Parameters.Add("@Date_d_arrivée", SqlDbType.Date).Value = arrivalDate ?? DBNull.Value;
    
    // Pass time parameter
    cmd.Parameters.Add("@heure_d_arrivée", SqlDbType.Time).Value = arrivalTime ?? DBNull.Value;
    
    // Add other parameters...
    cmd.ExecuteNonQuery();
}

Avoid using AddWithValue for date/time types, as it can lead to implicit type conversion issues. Always specify SqlDbType explicitly.

4. Check SQL Server Table Schema #

It’s possible your table columns are configured to use default values instead of allowing nulls. Run this query to check:

SELECT 
    COLUMN_NAME, 
    IS_NULLABLE, 
    COLUMN_DEFAULT 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'YourEmployeeTableName' 
AND COLUMN_NAME IN ('Date_d_arrivée', 'heure_d_arrivée');

• If IS_NULLABLE is NO and COLUMN_DEFAULT is set to '0001-01-01' or '00:00:00.000000', that’s why nulls are being replaced with defaults. Either:
  • Alter the table to allow nulls:

    ALTER TABLE YourEmployeeTableName ALTER COLUMN Date_d_arrivée DATE NULL;
    ALTER TABLE YourEmployeeTableName ALTER COLUMN heure_d_arrivée TIME NULL;
    

  • Or ensure you always pass valid date/time values from your app.

5. Validate Stored Procedure Logic #

Double-check your stored procedure to make sure it’s not overriding the input parameters with default values unintentionally. For example, ensure you’re inserting the @Date_d_arrivée parameter directly into the table, not hardcoding a default:

-- Correct: Use the input parameter
INSERT INTO YourTable (Date_d_arrivée, heure_d_arrivée, ...)
VALUES (@Date_d_arrivée, @heure_d_arrivée, ...);

-- Wrong: Accidentally using a default
INSERT INTO YourTable (Date_d_arrivée, heure_d_arrivée, ...)
VALUES (DEFAULT, DEFAULT, ...);

Start with checking the Excel formatting and data reading code—those are the most common culprits. Let me know if you hit any snags with specific parts!

内容的提问来源于stack exchange，提问作者lot