/*

The following two tables need to be present

create table tab1(
    [Id] int identity(1,1) not null,
	[Val] varchar(10) not null
) on [primary]

create table tab2(
	[Id] int identity(1,1) not null,
	[Val1] int not null,
	[Val2] int not null
) on [primary]

insert into tab1 (Val) values ('12345')
insert into tab1 (Val) values ('42-43') 


Note: The following works as I would expect in SSMS.

--set xact_abort off/on -- Makes no difference
begin transaction
insert into tab2 (Val1, Val2) values (42, 43)
select Id from tab1 where Val = 12345
insert into tab2 (Val1, Val2) values (100, 200)
commit transaction

At the end of transaction, nothing has been inserted.

Running the program below will report an error but still insert a row.

*/

using Microsoft.Data.SqlClient;
using System.Diagnostics.Tracing;

const string CONNECTION_STRING = "Data Source=...;Initial Catalog=...;User ID=...;Password=...;Encrypt=False";


try
{
    using var listener = new SqlClientListener();

    using SqlConnection connection = new(CONNECTION_STRING);

    connection.Open();

    using SqlTransaction transaction = connection.BeginTransaction();

    SqlCommand cmd1 = new("insert into tab2 (Val1, Val2) values (42, 43)", connection, transaction);
    cmd1.ExecuteNonQuery();

    // This query will have a server response with the message;
    //
    //   'Conversion failed when converting the varchar value '42-43' to data type int.'
    //
    SqlCommand cmd2 = new("select Id from tab1 where Val = 12345", connection, transaction);
    var id = cmd2.ExecuteScalar();
    Console.WriteLine($"id={id}");  // 'id' is fine...

    // This works
    SqlCommand cmd3 = new("insert into tab2 (Val1, Val2) values (100, 200)", connection, transaction);
    cmd3.ExecuteNonQuery();

    // This fails with an exception
    transaction.Commit();
}
catch (Exception ex)
{
    Console.WriteLine(ex.ToString());
}
// At completion;
// The first "insert into..." *will not* be present in table "tab2"
// The second "insert into..." *will be* present in table "tab2"


public class SqlClientListener : EventListener
{
    protected override void OnEventSourceCreated(EventSource eventSource)
    {
        if (eventSource.Name.Equals("Microsoft.Data.SqlClient.EventSource"))
        {
            EnableEvents(eventSource, EventLevel.Informational, EventKeywords.All);
        }
    }

    protected override void OnEventWritten(EventWrittenEventArgs eventData)
    {
        Console.WriteLine(eventData.Payload?[0] ?? "<null>");
    }
}

----- project-file
<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net8.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.Data.SqlClient" Version="6.0.1" />
  </ItemGroup>

</Project>