none
Table Valued Parameter - VarBinary(Max) column - Inserted content is wrong with multiple row insertions RRS feed

  • Question

  • I tried using custom collection (that subclasses List<T> and implements IEnumerable<SqlDataRecord> interface in combination with table valued parameter in SQL Server 2008 on .NET Framework 4.0 (on windows xp with sp3). The table has a max varbinary column. The issue is that when I tried to insert rows into the table using table valued parameter concept, the varbinary column content is erroneous. I discovered that if the first row to insert has say 80KB data followed by next record with 60KB data for varbinary max column, the 2nd row actually contains 80KB data after insertion. I checked that my code is doing the right thing. For now, I worked around this issue by sorting my collection from least to greatest number of bytes for this column before invoking insert operation. That seemed to work. Following are the steps to reproduce this issue. Please let me know if I am making any mistake here.

     

    1) I created following table in SQL Server 2008.

     

                        CREATE TABLE [dbo].[TestTVP](

                            [Id] [int] NOT NULL,

                            [Name] varchar(30) NOT NULL,

                            [Content] [varbinary](max) NULL,

                            CONSTRAINT [PK_TestTVP] PRIMARY KEY CLUSTERED 

                            (

                                [Id] ASC

                            )

                        )

                        GO

    2) Created following table type that matched above

                CREATE TYPE [dbo].[TestTVPType] AS TABLE 
                (
                    Id int NOT NULL,
                    [Name] varchar(30) NOT NULL,  
                    Content varbinary(max) NULL,
                    PRIMARY KEY (Id)
                )
                GO

    3) Here is the definition of my entity and collection:

            public class MyEntity
            {
                public int Id { get; set; }
                public string Name { get; set; }
                public byte[] Content { get; set; }
            }

            private class MyEntityCollection : List<MyEntity>, IEnumerable<SqlDataRecord>
            {
                public MyEntityCollection(IEnumerable<MyEntity> ent)
                    : base(ent)
                {
                }

                IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
                {
                    var record = new SqlDataRecord(
                        new SqlMetaData("Id", SqlDbType.Int),
                        new SqlMetaData("Name", SqlDbType.VarChar, 30),
                        new SqlMetaData("Content", SqlDbType.VarBinary, SqlMetaData.Max)
                    );


                    foreach (MyEntity doc in this)
                    {
                        int ordinal = 0;

                        record.SetInt32(ordinal++, doc.Id);

                        record.SetString(ordinal++, doc.Name);

                        byte[] contentBytes = doc.Content;
                        record.SetBytes(ordinal++, 0, contentBytes, 0, contentBytes.Length);

                        yield return record;
                    }
                }
            }

    4) Here is code to insert the records into table

            [TestMethod]
            public void InsertRecords()
            {
                List<MyEntity> inserts = new List<MyEntity>();

                inserts.Add(new MyEntity() { Id = 1, Name = "One.jpg", Content = File.ReadAllBytes(@"C:\Temp\Input\One.jpg") });
                inserts.Add(new MyEntity() { Id = 2, Name = "Two.doc", Content = File.ReadAllBytes(@"C:\Temp\Input\Two.doc") });

                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    conn.Open();

                    MyEntityCollection insertCollection = new MyEntityCollection(inserts);

                    string sqlInsertCmd = "INSERT INTO [dbo].[TestTVP] (Id, Name, Content) " +
                        " SELECT t.Id, t.Name, t.Content FROM @TestTVPTableType AS t;";

                    SqlCommand insertCommand = new SqlCommand(sqlInsertCmd, conn);

                    SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@TestTVPTableType", insertCollection);
                    tvpParam.SqlDbType = SqlDbType.Structured;
                    tvpParam.TypeName = "[dbo].[TestTVPType]";

                    int insertedRows = insertCommand.ExecuteNonQuery();

                    if (insertedRows != inserts.Count)
                    {
                        throw new InvalidOperationException(
                            string.Format("No. of actual inserted rows ({0}) did not match no. of the expected rows ({1}).",
                                insertedRows, inserts.Count));
                    }
                }
            }

            private string ConnectionString
            {
                get
                {
                    ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["conn"];

                    if (settings == null)
                    {
                        throw new ConfigurationErrorsException(
                            string.Format("Connection string with name 'conn' is missing in the config file.")
                            );
                    }

                    return settings.ConnectionString;
                }
            }

    5) The One.jpg file size is 70KB. and Two.doc file size is 19 KB. So first record in the collection contains 70KB content and second record contains 19KB.

    6) This creates following records in the table:

    1 One.jpg   0xFFD8FFE000104A46494 ....
    2 Two.doc   0xD0CF11E0A1B11AE100 ....

    7) Here is the code to read the table contents: 

            [TestMethod]
            public void ReadContent()
            {
                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    conn.Open();

                    SqlCommand command = new SqlCommand("SELECT Id, Name, Content FROM TestTVP");
                    command.Connection = conn;

                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        byte[] content = (byte[])reader["Content"];

                        Console.Out.WriteLine("{0}, {1}", reader["Name"], content.Length);

                        File.WriteAllBytes(string.Format(@"C:\Temp\Output\{0}", reader["Name"] as string), content);
                    }
                }
            }

    8) This prints following info to console:

    One.jpg, 71189
    Two.doc, 71189

    9) If you inspect the files in C:\Temp\Output\ directory, you would see that the above file sizes are accurate.

    10) Now switch "inserts.Add(new MyEntity()  ..." statements in the InsertRecords method so that first record contains 19KB content and second record contains 70KB content. Delete the records in table and invoke InsertRecords method again followed by ReadContent method.

    11) You would see following output:
    One.jpg, 71189
    Two.doc, 19456

    12) The size on the disk for these output files confirms above numbers.


     

    • Moved by KJian_ Thursday, May 13, 2010 2:17 AM (From:.NET Framework inside SQL Server)
    Wednesday, May 12, 2010 6:46 PM

Answers

All replies