Sql image data type can't be mapped to SqlBinary in System.Data.SqlTypes
- Dominant language
- C#
- Stars
- 989
- Forks
- 340
- Avg merge
- 4d 18h
- Merged PRs (30d)
- 69
Description
### Description
Having a sql server table with a column image data type is not mapping to SqlBinary and only maps to byte[].
### Reproduction Steps
1. Create a database using [Northwind sql-server-samples](https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/northwind-pubs)
2. Use the following code to retrieve Employee Table data having image data type:
```
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
DataTable dt = new DataTable();
DataColumn column = dt.Columns.Add("EmployeeID", typeof(SqlInt32));
DataColumn column1 = dt.Columns.Add("FirstName", typeof(SqlString));
DataColumn column2 = dt.Columns.Add("BirthDate", typeof(SqlDateTime));
// this will throw exception: System.InvalidOperationException: 'Inconvertible type mismatch between SourceColumn 'Photo' of Byte[] and the DataColumn 'Photo' of SqlBinary.'
DataColumn column3 = dt.Columns.Add("Photo", typeof(SqlBinary));
// this works:
//DataColumn column3 = dt.Columns.Add("Photo", typeof(byte[]));
DataColumn column4 = dt.Columns.Add("Notes", typeof(SqlString));
var sqlConnStringBuilder = new SqlConnectionStringBuilder
{
ApplicationName = "Sql Example",
DataSource = "localhost",
UserID = "sa",
Password = "",
InitialCatalog = "Northwind"
};
var sqlConn = new SqlConnection(sqlConnStringBuilder.ConnectionString);
var sqlCmd = new SqlCommand
{
Connection = sqlConn,
CommandType = CommandType.Text,
CommandText = "Select EmployeeID, FirstName, BirthDate, Photo, Notes from Employees"
};
sqlConn.Open();
SqlDataReader sqlRdr = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
DataTable dataTable = sqlRdr.GetSchemaTable();
dt.Load(sqlRdr);
```
### Expected behavior
No exception, and data should be retrieved.
### Actual behavior
System.InvalidOperationException
HResult=0x80131509
Message=Inconvertible type mismatch between SourceColumn 'Photo' of Byte[] and the DataColumn 'Photo' of SqlBinary.
Source=System.Data.Common
StackTrace:
at System.Data.Common.DataColumnMapping.GetDataColumnBySchemaAction(String sourceColumn, String dataSetColumn, DataTable dataTable, Type dataType, MissingSchemaAction schemaAction)
at System.Data.Common.DataColumnMappingCollection.GetDataColumn(DataColumnMappingCollection columnMappings, String sourceColumn, Type dataType, DataTable dataTable, MissingMappingAction mappingAction, MissingSchemaAction schemaAction)
at System.Data.ProviderBase.SchemaMapping.SetupSchemaWithKeyInfo(MissingMappingAction mappingAction, MissingSchemaAction schemaAction, Boolean gettingData, DataColumn parentChapterColumn, Object chapterValue)
at System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillMappingInternal(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillMapping(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
at System.Data.DataTable.Load(IDataReader reader)
at Program.$(String[] args) in C:\Users\maxim\Source\Repos\ADONETTutorial.4\ADONETTutorial.4\Program.cs:line 42
This exception was originally thrown at this call stack:
[External Code]
Program.$(string[]) in Program.cs
### Regression?
Not sure did not test it in .NET Framework.
### Known Workarounds
If we use C# native type instead of the SlqTypes it works.
```
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
DataTable dt = new DataTable();
DataColumn column = dt.Columns.Add("EmployeeID", typeof(SqlInt32));
DataColumn column1 = dt.Columns.Add("FirstName", typeof(SqlString));
DataColumn column2 = dt.Columns.Add("BirthDate", typeof(SqlDateTime));
DataColumn column3 = dt.Columns.Add("Photo", typeof(byte[]));
DataColumn column4 = dt.Columns.Add("Notes", typeof(SqlString));
var sqlConnStringBuilder = new SqlConnectionStringBuilder
{
ApplicationName = "Sql Example",
DataSource = "localhost",
UserID = "sa",
Password = "",
InitialCatalog = "Northwind"
};
var sqlConn = new SqlConnection(sqlConnStringBuilder.ConnectionString);
var sqlCmd = new SqlCommand
{
Connection = sqlConn,
CommandType = CommandType.Text,
CommandText = "Select EmployeeID, FirstName, BirthDate, Photo, Notes from Employees"
};
sqlConn.Open();
SqlDataReader sqlRdr = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
DataTable dataTable = sqlRdr.GetSchemaTable();
dt.Load(sqlRdr);
```
### Configuration
.NET Core 6.0.3
Windows 10 x64
Console Application
Visual Studio Community 2022
### Other information
The error is happening in the GetDataColumnBySchemaAction method in DataColumn class of System.Data.Common namespace. At the end of the method, there is a check if c# type and underlying type is an array. SqlBianry type is returning false and causes the error.
Contributor guide
Assessment
This issue has not been assessed yet.