SQLCommand Error on Visual Studio Designer (WinForms)

[stavrinc]

On a C# project (.NET Framework 4.8) when I try to create a control SqlCommand using the designer I get the following error

Failed to create component 'SqlCommand'. The error message follows: 'System.invalidCastException: Unable to cast object of type 'Microsoft.Data.SqlClient.SqlCommand' to type 'System.Data.SqlClient.SqlCommand'.

The same problem appears with Microsoft.Data.SqlClient V3 and V2.1.3 and older.

All other controls like SQLDataAdapter are ok.

Thanks in advance for any suggestion

[johnnypham]

Hi @stavrinc, can you provide a repro app or more details? Are you trying to add a custom user control? How is SqlCommand being used in the control?

[Wraith2]

I think the problem is that the designer component in visual studio is expecting to get a System.Data.SqlClient instance to design over but the instance is a Microsoft.Data.SqlClient ovject so the cast fails. You can see in the stack trace that it's trying to do design time stuff and then hits Microsoft.VSDesigner.Data.VS.SqlCommandDesigner and falls over, so i think visual studio would need to be updates to recognise this library instead of only the system version.

[johnnypham]

Thanks @Wraith2, it's out of our control then. @stavrinc if you can provide more details, maybe we can come up with a workaround.

[stavrinc]

Thanks @Wraith2, it's out of our control then. @stavrinc if you can provide more details, maybe we can come up with a workaround.

What details exactly. It is very simple bug. When I create the SQLCommand button on a form I get the above error.

[Wraith2]

The easiest thing to work with is a really really simple repro. Can you provide a sample project that shows the bug, so simple that you can just give someone the zip and tell them to open the designer, click a button and have it crash.

[johnnypham]

What details exactly. It is very simple bug. When I create the SQLCommand button on a form I get the above error.

I don't see a SqlCommand button so I'm assuming it's a custom user control. I'm not seeing the exception when I use the SqlClient library in a custom user control. Like Wraith2 said, please provide a basic application that others can run, otherwise it's difficult to figure out what's wrong.

[Wraith2]

I don't see a SqlCommand button so I'm assuming it's a custom user control.

I'm pretty sure It's in the ancient visual studio SqlDataAdapter user interface that you can use to visually design table adapters and their commands. You can get at a command list and then edit the command properties directly. Getting a simple repro project setup in the breaking state would make it a lot easier to work out how to get it into the broken state though.

[stavrinc]

Steps to reproduce the problem

1. Install Visual Studio 2019 with the latest updates

2. Create a winform project on VB.NET or C# (.NET Framework 4.8)

3. Add reference for Microsoft.Data.SqlClient (V.3) from NuGet
   https://www.nuget.org/packages/Microsoft.Data.SqlClient/

4. Save and Rebuid project

5. Now the SQLCommand Control appears first in Toolbox. Try to Design SQLCommand Control on a winform. Then the error message appears.

I also provide link to a zip file with all files of a very simple project to reproduce the problem.

https://mega.nz/file/78VCUBRB#K71u9vQc7aViMazRy5vYNAhn1i1N9MnMPtSlYSWFVLA

[ErikEJ]

The VS designers are tied to System.Data.SqlClient, avoid using them if you want to use M.D.S.

[johnnypham]

Thanks, I'm able to repro it now.

The VS designers are tied to System.Data.SqlClient, avoid using them if you want to use M.D.S.

Yes, I think this is right.

[johnnypham]

Hi @stavrinc, with PR #1132 merged, data provider components will now use System.Data.SqlClient and not Microsoft.DataSqlClient. Future versions of VS may support Microsoft.Data.SqlClient but there are currently no plans for that.

Here is the nuget if you would like to try it now: build artifacts

Feel free to reopen if you have any more concerns.