BLOB files in SQL-database as chunk

Question

I tried to modify the example from: link to example but i receive an error;
Unable to cast object of type 'System.DBNull' to type 'System.Byte[]'
I suppose that the returned ID (UniqueIdentifier) isn't correct.

My code:

public static Guid AddRecord(string firstCol, DateTime SecCol, string photoFilePath)
{
    using (SqlConnection connection = new SqlConnection(
        "Data Source=(local);Integrated Security=true;Initial Catalog=Test;"))
    {
        SqlCommand addRec = new SqlCommand(
            "INSERT INTO myTable (firstCol,SecCol,Image) " +
            "VALUES (@firstCol,@SecCol,0x0)" +
            "SELECT @Identity = NEWID();" +
            "SELECT @Pointer = TEXTPTR(Image) FROM myTable WHERE ID = @Identity", connection);

        addRec.Parameters.Add("@firstCol", SqlDbType.VarChar, 25).Value = firstCol;
        addRec.Parameters.Add("@SecCol", SqlDbType.DateTime).Value = SecCol;

        SqlParameter idParm = addRec.Parameters.Add("@Identity", SqlDbType.UniqueIdentifier);
        idParm.Direction = ParameterDirection.Output;

        SqlParameter ptrParm = addRec.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
        ptrParm.Direction = ParameterDirection.Output;

        connection.Open();

        addRec.ExecuteNonQuery();

        Guid newRecID = (Guid)idParm.Value;

        StorePhoto(photoFilePath, (byte[])ptrParm.Value, connection);

        return newRecID;
    }
}

@Mithrandir: Thx. I couldn't get the code properly formatted in my question. — John Doe, Feb 08 '12 at 20:17

Sergey Kalinichenko · Accepted Answer · 2012-02-08T20:25:39.530

2

As noted in the other answer, the example is obsolete; I would not recommend using it.

If you are set on making it work just as an exercise, change your SQL to insert the ID that you created into myTable, as follows:

SqlCommand addRec = new SqlCommand(
            "SELECT @Identity = NEWID();" +
            "INSERT INTO myTable (ID,firstCol,SecCol,Image) " +
            "VALUES (@Identity,@firstCol,@SecCol,0x0)" +
            "SELECT @Pointer = TEXTPTR(Image) FROM myTable WHERE ID = @Identity", connection);

edited Feb 08 '12 at 20:25

answered Feb 08 '12 at 20:19

Sergey Kalinichenko

714,442
84
1,110
1,523

Works exelent. But if it can be done better for my 'Windows Form Application'. Please advice me. – John Doe Feb 08 '12 at 20:35
@Robertico The `TEXTPTR` feature [is on its way out](http://msdn.microsoft.com/en-us/library/ms176068.aspx), so I think a better way to slice your image and load it would be to use [`SqlParameter`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx)'s [`Offset`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.offset.aspx) property. Unfortunately, Microsoft does not offer a good example for it, so you would need to search around for a good walk-through. – Sergey Kalinichenko Feb 08 '12 at 20:56

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

I found a much better way here, this is the SQL Server 2005 + way todo it.

string sql = "UPDATE BinaryData SET Data.Write(@data, LEN(data), @length) WHERE fileName=@fileName";

        SqlParameter dataParam = cmd.Parameters.Add("@data", SqlDbType.VarBinary);
        SqlParameter lengthParam = cmd.Parameters.Add("@length", SqlDbType.Int);
        cmd.CommandText = sql;

        fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
        int readBytes = 0;
        while (cIndex < fileSize)
        {
            if (cIndex + BUFFER_SIZE > fileSize)
                readBytes = fileSize - cIndex;
            else
                readBytes = BUFFER_SIZE;
            fs.Read(buffer, 0, readBytes);

            dataParam.Value = buffer;
            dataParam.Size = readBytes;
            lengthParam.Value = readBytes;

            cmd.ExecuteNonQuery();
            cIndex += BUFFER_SIZE;
        }

BinaryData is the table name.

Data.Write is a system function call where Data is a column name

score 1 · Answer 3 · answered Feb 08 '12 at 20:20

1

That example is obsolete. USe of TEXTPTR is strongly discouraged after SQL Server 2005, along with the deprecated TEXT, NTEXT and IMAGE types. The correct SQL Server 2005 and after method of efficiently manipulating BLOBs is to use UPDATE .WRITE syntax and MAX data types. If you want to see an example look at Download and Upload images from SQL Server via ASP.Net MVC

answered Feb 08 '12 at 20:20

Remus Rusanu

288,378
40
442
569

My app. is a 'Windows Form Application' and the server SQL-server 2008 R2. Database column type is 'Image'. It's not possible to change the type. – John Doe Feb 08 '12 at 20:27

BLOB files in SQL-database as chunk

3 Answers3