BLOB files in SQL database as part

Question

I tried to change the example: link to the example , but I get an error message;
Unable to cast object of type 'System.DBNull' to type 'System.Byte[]'
I believe the return identifier (UniqueIdentifier) ​​is incorrect.

My code is:

 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; } } 

Answer 1

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

If you are configured to make it work as an exercise, modify your SQL to insert the identifier you created into myTable , as shown below:

 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); 

Answer 2

This example is deprecated. USe TEXTPTR strongly discouraged after SQL Server 2005, along with the legacy TEXT, NTEXT, and IMAGE types. The correct SQL Server 2005 and the efficient BLOB management method is to use the UPDATE .WRITE and MAX syntax types. If you want to see an example, see Uploading and uploading images from SQL Server through ASP.Net MVC

Answer 3

I found a much better way here , this is the way SQL Server 2005 + 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 name of the table.

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