Capture SqlBulkCopy spans

[stevejgordon]

We don't currently capture any spans when using the SqlBulkCopy API from Microsoft.Data.SqlClient or System.Data.SqlClient. I haven't yet dug into the source code for these libraries to confirm if they emit any diagnostic events we should listen to. It's possible they do not. If the latter is true, we may be able to instrument with the profiler-based approach instead.

I attempted to reproduce this in a console app with both the NuGet-based and profiler-based agent set ups.

Code to use in reproductions:

var connectionString = "<SOMECONNECTIONSTRING>";

DoSqlClientCall(connectionString);
DoBulk(connectionString);

Console.WriteLine("Done!");

static void DoBulk(string connectionString)
{
    using var sourceConnection = new SqlConnection(connectionString);
    sourceConnection.Open();

    using var destinationConnection = new SqlConnection(connectionString);
    destinationConnection.Open();

    var commandSourceData = new SqlCommand("SELECT * FROM Emails;", sourceConnection);
    var reader = commandSourceData.ExecuteReader();

    using var bulkCopy = new SqlBulkCopy(destinationConnection);
    bulkCopy.DestinationTableName = "EmailsCopy";

    try
    {
        var transaction = Agent.Tracer.StartTransaction("bulkcopy", "app");
        bulkCopy.WriteToServer(reader);
        transaction.End(); // results in a transaction with no child spans.
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
    finally
    {
        reader.Close();
    }
}

static void DoSqlClientCall(string connectionString)
{
    using var connection = new SqlConnection(connectionString);
    connection.Open();

    var create = new SqlCommand("CREATE TABLE Emails(Id INTEGER,Email TEXT)", connection);
    create.ExecuteNonQuery();

    var create2 = new SqlCommand("CREATE TABLE EmailsCopy(Id INTEGER,Email TEXT)", connection);
    create2.ExecuteNonQuery();

    InsertEmail("test1@example.example", connection);
    InsertEmail("test2@example.example", connection);

    var command = new SqlCommand("SELECT Email FROM Emails", connection);
    var reader = command.ExecuteReader();
    try
    {
        while (reader.Read())
        {
            var read = reader["Email"];
            Console.WriteLine($"{read}");
        }
    }
    finally
    {
        reader.Close();
    }
}

static void InsertEmail(string email, SqlConnection connection)
{
    using var command = connection.CreateCommand();
    command.CommandText = "INSERT into Emails(id ,email) values(@id,@email)";
    command.Prepare();
    command.Parameters.AddWithValue("@id", 0);
    command.Parameters.AddWithValue("@email", email);
    command.ExecuteNonQuery();
}

NOTE: The Otel SDK also doesn't collect any spans when using SqlBulkCopy.