none
How to insert or update Blob data for SQLServer based on BUFFER_SIZE RRS feed

  • Question

  • Hi Everyone,

     

    I have a question about how to use ADO.Net to insert or update a column in SqlServer 2005 which has data type of varBinary(Max)

     

     Sure I can write T-SQL statements to insert or update data into table. However, sometime, the file size is huge (talking about hundred of megabytes to a gigabyte of data) and I do not want to perform insert or update operation in one shot, I want to insert  or update  a chunk of data (control by buffer size) at a time until there are no more data to write.  I have done something similar to this but for Oracle database using OracleBlob object.  I have spent at least a couple hours to find some object which is similar to OracleLob but I could not. Please help!

     

    Regards,

     

    John Dang

     

     

    Code Snippet

    // this code segment works well for Oracle database

     

    OracleLob lob = dataReader.GetOracleLob(1);

    fileStream = File.OpenRead(path);

    lob.SetLength(0);

    while ( (readBytes = fileStream.Read(buffer,0,BUFFER_SIZE)) > 0 )

    {         

         lob.Write(buffer,0, readBytes );

    }

     

    Tuesday, July 10, 2007 12:46 PM

Answers

  • I think I found a better solution. TextPtr function may work for SqlServer 2000. However, If you use SqlServer 2005 then this is a better way to do.

     

    sql = String.Concat("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 ;

    }

    Friday, July 13, 2007 12:25 PM

All replies

  • Why bother sending it in chunk, let your application handle it. You just need to set a longer query timeout.

     

    Try something like this:

     

    Code Snippet

    Dim cn As SqlConnection = New SqlConnection("Your Connection String Here")

    Dim cmd As SqlCommand = New SqlCommand("SaveBLOB", cn)

    cmd.CommandType = CommandType.StoredProcedure

    cmd.CommandTimeout = 300 'allows 30 seconds execution time before it issue a timeout error

    cmd.Parameters.Add("@BinaryData", SqlDbType.Image).Value = BinaryData

    cn.Open()

    Try

    cmd.ExecuteNonQuery()

    Catch ex As Exception

    Throw ex

    Finally

    cn.Close()

    End Try

     

    Next is you have to make a way to tell your user that data is being upload by showing a progress bar or indicator.

     

    I hope this helps.

    Tuesday, July 10, 2007 2:58 PM
  • Thanks for your email.

    However, I do not argee with you on this one. Since this  codes are run on the server, let say I have 100 users try to upload file at the same time and the size of each file is from 100Meg  to 1 gigabytes. Assume the server has 8 gigabytes of ram. If I do not update a chunk of data at a time, the server will crash right away.

     

    JDang

    Tuesday, July 10, 2007 9:32 PM
  • Here an article from MSDN ".NET Framework Developer Center " on how to save image to your database a BLOB in chunks.

     

    "Conserving Resources When Writing BLOB Values to SQL Server"

    http://msdn2.microsoft.com/en-us/library/3517w44b(VS.71).aspx

     

    still your problem is not solve, you still have to upload your file into the server before you can save it into you server. Here the solution from "CodeRage"

     

    http://forums.asp.net/p/55127/106552.aspx#106552

     

    I hope this solves your problem.

     

    Goodluck.

     

    Wilmar Acosta

     

    Wednesday, July 11, 2007 6:15 AM
  • Thanks for your help.

    However, the sample codes on microsoft does not work and the error is :

    "Argument data type varbinary(max) is invalid for argument 1 of textptr function"

    when it execute these line of codes

     

    cmd.CommandText = "Select @ptr= TextPtr(data) From BinaryData Where fileName=@fileName";

    cmd.Parameters.Add(new SqlParameter("@fileName", fileName));

    SqlParameter dataPtr = cmd.Parameters.Add(new SqlParameter("@ptr", SqlDbType.Binary, 16));

    dataPtr.Direction= ParameterDirection.Output;

    // throw error right here

    cmd.ExecuteNonQuery();

     

    Perhaps, It works with SqlServer 2000, not  SqlServer 2005. Are there any solution for this bug?

     

    Regards,

     

    Jdang

     

    Thursday, July 12, 2007 8:00 PM
  • I think I found a better solution. TextPtr function may work for SqlServer 2000. However, If you use SqlServer 2005 then this is a better way to do.

     

    sql = String.Concat("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 ;

    }

    Friday, July 13, 2007 12:25 PM