dotnet / dotnet/SqlClient

Sql image data type can't be mapped to SqlBinary in System.Data.SqlTypes

Open
#1,573 8 comments 0 reactions 0 assignees View on GitHub
Repro Available :heavy_check_mark:
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.