[Instrumentation.SqlClient] Not working with Microsoft.Data.SqlClient 3.1.2 on .NET Fx 4.6.2

[Kielek]

Bug Report

List of all OpenTelemetry NuGet
packages and version that you are
using (e.g. OpenTelemetry 1.0.2):

• OpenTelemetry 1.4.0
• OpenTelemetry.Instrumentation.SqlClient 1.0.0-rc9.14, probably any older have the same problem.
• Microsoft.Data.SqlClient 3.1.2 (or other from this version)

Runtime version (e.g. net462, net48, netcoreapp3.1, net6.0 etc. You can
find this information from the *.csproj file):

• net462 (.NET (Core) works fine).

Symptom

What is the expected behavior?

Spans/Activities are emitted also for Microsoft.Data.SqlClient 3.1.2

What is the actual behavior?

What did you see instead?

Spans/Activaties are not created, following events are emitted

[2023-02-28T11:12:20.6189013Z] [Warning] EventSource=OpenTelemetry-Instrumentation-SqlClient, Message=Payload is invalid in event 'OnBeginExecute' from handler 'SqlEventSourceListener', span will not be recorded. 
[2023-02-28T11:12:20.6610449Z] [Warning] EventSource=OpenTelemetry-Instrumentation-SqlClient, Message=Payload is invalid in event 'OnEndExecute' from handler 'SqlEventSourceListener', span will not be recorded. 

Reproduce

(It takes some time)

1. Checkout https://github.com/open-telemetry/opentelemetry-dotnet-instrumentation/tree/6f347379ed018dc7545ade7596dacaf775f9578f on Windows with Docker for Linux.
2. execute nuke Workflow

It can be also reproduce more granularly

1. Checkout https://github.com/open-telemetry/opentelemetry-dotnet-instrumentation/tree/6f347379ed018dc7545ade7596dacaf775f9578f on Windows with Docker for Linux.
2. execute nuke BuildTracer
3. Change Override version to 3.1.2 in https://github.com/open-telemetry/opentelemetry-dotnet-instrumentation/blob/6f347379ed018dc7545ade7596dacaf775f9578f/test/test-applications/integrations/TestApplication.SqlClient/TestApplication.SqlClient.csproj#L4 and build
4. Execute test from namespace IntegrationTests.SqlClientMicrosoftTests from VisualStrudio.

Additional Context

Detected by extended set for Automatic Instrumentation with different versions of libraries.

Issue due to differences between supported/non-supported payload
Supported: https://github.com/dotnet/SqlClient/blob/f4568ce68da21db3fe88c0e72e1287368aaa1dc8/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlCommand.cs#L6641
Non-supported: https://github.com/dotnet/SqlClient/blob/3.1-servicing/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlCommand.cs#L6548

Problematic code:

opentelemetry-dotnet/src/OpenTelemetry.Instrumentation.SqlClient/Implementation/SqlEventSourceListener.netfx.cs

Lines 106 to 126 in 9dd54d7

	/*
	Expected payload:
	[0] -> ObjectId
	[1] -> DataSource
	[2] -> Database
	[3] -> CommandText
	Note:
	- For "Microsoft-AdoNet-SystemData" v1.0: [3] CommandText = CommandType == CommandType.StoredProcedure ? CommandText : string.Empty; (so it is set for only StoredProcedure command types)
	(https://github.com/dotnet/SqlClient/blob/v1.0.19239.1/src/Microsoft.Data.SqlClient/netfx/src/Microsoft/Data/SqlClient/SqlCommand.cs#L6369)
	- For "Microsoft-AdoNet-SystemData" v1.1: [3] CommandText = sqlCommand.CommandText (so it is set for all command types)
	(https://github.com/dotnet/SqlClient/blob/v1.1.0/src/Microsoft.Data.SqlClient/netfx/src/Microsoft/Data/SqlClient/SqlCommand.cs#L7459)
	- For "Microsoft.Data.SqlClient.EventSource" v2.0+: [3] CommandText = sqlCommand.CommandText (so it is set for all command types).
	(https://github.com/dotnet/SqlClient/blob/f4568ce68da21db3fe88c0e72e1287368aaa1dc8/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlCommand.cs#L6641)
	*/
	if ((eventData?.Payload?.Count ?? 0) < 4)
	{
	SqlClientInstrumentationEventSource.Log.InvalidPayload(nameof(SqlEventSourceListener), nameof(this.OnBeginExecute));
	return;
	}

Similar issue probably for the EndEvent.

[cijothomas]

dotnet/SqlClient#1258
microsoft/ApplicationInsights-dotnet#2346

3.* is broken. I do not think it was ever fixed in 3.*. 4 and higher should work.