Guideline to reuse SqlDataRecord

[xaviergxf]

I'm using a lot of SqlDataRecord for mass insert/update/delete on multiple tables and when reading the documentation about SqlDataRecord i don't see any guideline on how to reuse these SqlDataRecord objects. The only thing mentioned on the docs is:

you should re-use existing SqlDataRecord objects instead of creating new ones every time

I have looked into ObjectPool class to reuse these objects, but in my use case it won't work because ObjectPool when used in DI is singleton and i would need an ObjectPool per "table" (like the HttpClientFactory named client pattern)

[JRahnama]

@xaviergxf we will look into this soon.

[lcheunglci]

You can interpret the documentation as you should reuse a SqlDataRecord for each row. The assumption is you insert multiple rows, so you don't need to recreate the SqlDataRecord for each one. However you implement the object reuse is up to you, and ObjectPool is just one .NET feature.

[lcheunglci]

Closing due to inactivity.

[0x53A]

Hi, can you please confirm or deny if the following kind of reuse is safe?

Please see https://stackoverflow.com/questions/37007018/is-it-safe-to-reuse-a-sqldatarecord

        private static SqlMetaData[] sqlMetaDataInt = new[] { new SqlMetaData("ID", SqlDbType.Int) };
        private static IEnumerable<SqlDataRecord> CreateSqlDataRecords(IEnumerable<int> ids)
        {
            SqlDataRecord record = new SqlDataRecord(sqlMetaDataInt);
            foreach (int id in ids)
            {
                record.SetInt32(0, id);
                yield return record;
            }
        }

This is then passed to SqlClient as a Parameter via Dapper:

connection.Query("...", { ids = CreateSqlDataRecords(new[]{ 1, 2, 3 }).AsTableValuedParameter("TVP_Name") })

(Dapper is just sugar for creating an SqlCommand and filling the SqlParameter)

[Dean-NC]

I'd also like to know the answer to what @0x53A asked, because I use this pattern frequently. The documentation assumes we're using a SqlPipe and returning data from a CLR stored procedure, but the pattern we're asking about is sending data as a TVP.

[munik]

@lcheunglci Please reopen. I also need to know the answer to what @0x53A asked. While the approach works in practice, it's sketchy (unless officially documented) because it relies on implementation details of how the SqlClient library uses the IEnumerable. There should be clear documentation on whether it is okay to use the pattern that @0x53A posted

[mdaigle]

Investigating #3957 brought me back to this conversation, so I'm reopening it. Looks like we need some general improvements around SqlDataRecord documentation.