I am wanting to pass a DataTable as a parameter to a procedure, this DataTable comes from an Excel spreadsheet that is then converted into a DataTable.
In SQL I created a Type Table with the required fields, all columns with the same name as the DataTable it receives. In this Excel spreadsheet some fields can be null, that is, come in blank .. and these fields are properly informed in the Type that I created in SQL.
Below the code of how I am passing the DataTable to the procedure
public void ImportToSql(DataTable dt)
{
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString"].ConnectionString))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"EXEC [dbo].AddPrextAndOperacoes @dataTable";
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = cmd.Parameters.AddWithValue("@dataTable", dt);
param.SqlDbType = SqlDbType.Structured;
var returnParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
conn.Open();
cmd.ExecuteNonQuery();
var result = returnParameter.Value;
}
}
The problem is that when it reports the following error when it encounters the first field with no value
BeforepassingdtasparametertoImportToSql()IcheckifthereisanycolumnwithoutvalueandaddDBNull.Value,eventhentheerrorisdisplayed
foreach(DataColumncolindataTable.Columns){foreach(DataRowrowindataTable.Rows){row[col]=row[col]?.ToString()=="" ? DBNull.Value : row[col];
}
}