Support forums » Products » Ajax Uploader » Uploading file directly into Database

Uploading file directly into Database

Last post 08-20-2009, 1:29 AM by cutechat. 5 replies.
Sort Posts: Previous Next

  •  07-08-2009, 10:58 AM 53833

    Uploading file directly into Database

    Reply Quote
    Hi Terry,
    my users are uploading files upto 400 mb, the ajaxuploader is loading the files correctly to the tempfolder( I am storing all the files in DB) in webserver but  when inserting the file from tempfolder to DB I am getting outof memory exception when trying to load the entire file into byte array in memory. Is there any other way? Can I directly upload the file into DB like streaming or in chunks? 
     
    Please help !
    Best Regards,

  •  07-08-2009, 9:17 PM 53843 in reply to 53833

    Re: Uploading file directly into Database

    Reply Quote
    Hi,
     
    You can split the file into multi part , and for each part, use the UPDATETEXT command :
     
    Regards,
    Terry
     

  •  07-08-2009, 9:19 PM 53844 in reply to 53833

    Re: Uploading file directly into Database

    Reply Quote
    Here is a sample code to use UPDATETEXT :
     
    1. using System;   
    2. using System.Data;   
    3. using System.Data.SqlClient;   
    4. using CuteWebUI;   
    5.   
    6. namespace UploaderDatabaseProvider   
    7. {   
    8.     /// <summary>   
    9.     /// UploaderSqlServerProvider   
    10.     /// </summary>   
    11.     public class UploaderSqlServerProvider : CuteWebUI.UploaderProvider   
    12.     {   
    13.         const int BUFFERSIZE = 204800;   
    14.   
    15.         SqlConnection _conn;   
    16.   
    17.         public override void Init(IAjaxUploader uploader, System.Web.HttpContext context)   
    18.         {   
    19.             string connectionstring = System.Configuration.ConfigurationSettings.AppSettings["UploaderDatabase"];   
    20.             if (connectionstring == null) throw (new Exception("appSettings:UploaderDatabase not found."));   
    21.             _conn = new SqlConnection(connectionstring);   
    22.             _conn.Open();   
    23.         }   
    24.         public override void Dispose()   
    25.         {   
    26.             if (_conn != null)   
    27.                 _conn.Close();   
    28.             base.Dispose();   
    29.         }   
    30.   
    31.         private SqlCommand CreateCommand(string commandtext)   
    32.         {   
    33.             if (_conn == null) throw (new Exception("Not init yet."));   
    34.             SqlCommand cmd = new SqlCommand();   
    35.             cmd.Connection = _conn;   
    36.             cmd.CommandText = commandtext;   
    37.             return cmd;   
    38.         }   
    39.   
    40.   
    41.         public override bool SupportFS   
    42.         {   
    43.             get  
    44.             {   
    45.                 return false;   
    46.             }   
    47.         }   
    48.         public override string GetFSPath(Guid guid)   
    49.         {   
    50.             throw (new NotSupportedException());   
    51.         }   
    52.   
    53.   
    54.         public override void Maintain()   
    55.         {   
    56.             SqlCommand cmd = CreateCommand("DELETE [AjaxUploaderTempFiles] WHERE FileTime<@Time");   
    57.             cmd.Parameters.Add("@Time", SqlDbType.DateTime).Value = DateTime.Now.AddHours(-1);   
    58.             cmd.ExecuteNonQuery();   
    59.         }   
    60.   
    61.         public override bool GetInfo(Guid guid, out string filename, out int filesize, out bool persist)   
    62.         {   
    63.             SqlCommand cmd = CreateCommand("SELECT [FileName],[FileSize],[IsPersist] FROM [AjaxUploaderTempFiles] WHERE FileGuid=@Guid");   
    64.             cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = guid;   
    65.             using (SqlDataReader reader = cmd.ExecuteReader())   
    66.             {   
    67.                 if (reader.Read())   
    68.                 {   
    69.                     filename = reader.GetString(0);   
    70.                     filesize = reader.GetInt32(1);   
    71.                     persist = reader.GetBoolean(2);   
    72.                     return true;   
    73.                 }   
    74.                 else  
    75.                 {   
    76.                     filename = null;   
    77.                     filesize = 0;   
    78.                     persist = false;   
    79.                     return false;   
    80.                 }   
    81.             }   
    82.   
    83.         }   
    84.   
    85.         public override void Delete(Guid guid)   
    86.         {   
    87.             SqlCommand cmd = CreateCommand("DELETE [AjaxUploaderTempFiles] WHERE FileGuid=@Guid");   
    88.             cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = guid;   
    89.             cmd.ExecuteNonQuery();   
    90.         }   
    91.   
    92.         public override void Persist(Guid guid)   
    93.         {   
    94.             SqlCommand cmd = CreateCommand("UPDATE [AjaxUploaderTempFiles] SET IsPersist=1 WHERE FileGuid=@Guid");   
    95.             cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = guid;   
    96.             cmd.ExecuteNonQuery();   
    97.         }   
    98.         public override void UnPersist(Guid guid)   
    99.         {   
    100.             SqlCommand cmd = CreateCommand("UPDATE [AjaxUploaderTempFiles] SET IsPersist=0 WHERE FileGuid=@Guid");   
    101.             cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = guid;   
    102.             cmd.ExecuteNonQuery();   
    103.         }   
    104.   
    105.         public override void Save(Guid guid, string filename, System.IO.Stream stream)   
    106.         {   
    107.             int stepsize = BUFFERSIZE;   
    108.             long filesize = stream.Length;   
    109.             byte[] data = new byte[Math.Min(stepsize, filesize)];   
    110.             stream.Read(data, 0, data.Length);   
    111.             SqlCommand cmd = CreateCommand("INSERT INTO [AjaxUploaderTempFiles] ([FileGuid],[FileTime],[FileName],[FileSize],[FileData],[IsPersist]) VALUES (@Guid,@Time,@Name,@Size,@Data,0)");   
    112.             cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = guid;   
    113.             cmd.Parameters.Add("@Time", SqlDbType.DateTime).Value = DateTime.Now;//for Maintain   
    114.             cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 255).Value = filename;   
    115.             cmd.Parameters.Add("@Size", SqlDbType.Int).Value = filesize;   
    116.             cmd.Parameters.Add("@Data", SqlDbType.Image).Value = data;   
    117.             cmd.ExecuteNonQuery();   
    118.   
    119.             if (filesize <= stepsize)   
    120.                 return;   
    121.   
    122.             int sentsize = stepsize;   
    123.   
    124.             try  
    125.             {   
    126.                 while (true)   
    127.                 {   
    128.                     int readsize = stream.Read(data, 0, data.Length);   
    129.                     if (readsize <= 0)   
    130.                         return;   
    131.   
    132.                     cmd = CreateCommand("DECLARE @ptrval binary(16) ; SELECT @ptrval = TEXTPTR([FileData]) FROM [AjaxUploaderTempFiles] WHERE [FileGuid]=@Guid ; UPDATETEXT [AjaxUploaderTempFiles].[FileData] @ptrval "  
    133.                         + sentsize + " 0 @Data");   
    134.                     cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = guid;   
    135.                     if (readsize != data.Length)   
    136.                     {   
    137.                         byte[] newdata = new byte[readsize];   
    138.                         Buffer.BlockCopy(data, 0, newdata, 0, readsize);   
    139.                         data = newdata;   
    140.                     }   
    141.                     cmd.Parameters.Add("@Data", SqlDbType.Image).Value = data;   
    142.                     cmd.ExecuteNonQuery();   
    143.                     sentsize += readsize;   
    144.                 }   
    145.             }   
    146.             catch (Exception)   
    147.             {   
    148.                 Delete(guid);   
    149.                 throw;   
    150.             }   
    151.         }   
    152.   
    153.   
    154.         public override void AppendData(Guid guid, string filename, System.IO.Stream stream)   
    155.         {   
    156.             int stepsize = BUFFERSIZE;   
    157.             long filesize = stream.Length;   
    158.             byte[] data = new byte[stepsize];   
    159.   
    160.             SqlCommand cmd = CreateCommand("SELECT [FileSize] FROM [AjaxUploaderTempFiles] WHERE [FileGuid]=@Guid ; UPDATE [AjaxUploaderTempFiles] SET [FileSize]=[FileSize]+@Size WHERE [FileGuid]=@Guid");   
    161.             cmd.Parameters.Add("@Size", SqlDbType.Int).Value = filesize;   
    162.             cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = guid;   
    163.             int sentsize = Convert.ToInt32(cmd.ExecuteScalar());   
    164.   
    165.             try  
    166.             {   
    167.                 while (true)   
    168.                 {   
    169.                     int readsize = stream.Read(data, 0, data.Length);   
    170.                     if (readsize <= 0)   
    171.                         break;   
    172.   
    173.                     cmd = CreateCommand("DECLARE @ptrval binary(16) ; SELECT @ptrval = TEXTPTR([FileData]) FROM [AjaxUploaderTempFiles] WHERE [FileGuid]=@Guid ; UPDATETEXT [AjaxUploaderTempFiles].[FileData] @ptrval "  
    174.                         + sentsize + " 0 @Data");   
    175.                     cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = guid;   
    176.                     if (readsize != data.Length)   
    177.                     {   
    178.                         byte[] newdata = new byte[readsize];   
    179.                         Buffer.BlockCopy(data, 0, newdata, 0, readsize);   
    180.                         data = newdata;   
    181.                     }   
    182.                     cmd.Parameters.Add("@Data", SqlDbType.Image).Value = data;   
    183.                     cmd.ExecuteNonQuery();   
    184.                     sentsize += readsize;   
    185.                 }   
    186.             }   
    187.             catch (Exception)   
    188.             {   
    189.                 Delete(guid);   
    190.                 throw;   
    191.             }   
    192.         }   
    193.   
    194.   
    195.         public override System.IO.Stream OpenStream(Guid guid)   
    196.         {   
    197.             SqlCommand cmd = CreateCommand("SELECT DATALENGTH([FileData]) FROM [AjaxUploaderTempFiles] WHERE FileGuid=@Guid");   
    198.             cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = guid;   
    199.             object val = cmd.ExecuteScalar();   
    200.             if (val == null || Convert.IsDBNull(val))   
    201.                 throw (new Exception("File not found."));   
    202.   
    203.             int filesize = Convert.ToInt32(val);   
    204.             int stepsize = BUFFERSIZE;   
    205.   
    206.             if (filesize <= stepsize)   
    207.             {   
    208.                 cmd = CreateCommand("SELECT [FileData] FROM [AjaxUploaderTempFiles] WHERE FileGuid=@Guid");   
    209.                 cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = guid;   
    210.                 using (SqlDataReader reader = cmd.ExecuteReader())   
    211.                 {   
    212.                     if (reader.Read())   
    213.                     {   
    214.                         byte[] data = (byte[])reader.GetValue(0);   
    215.                         return new System.IO.MemoryStream(data);   
    216.                     }   
    217.                 }   
    218.                 throw (new Exception("File not found."));   
    219.             }   
    220.   
    221.             ReadStream readstream = new ReadStream();   
    222.             readstream.provider = this;   
    223.             readstream.guid = guid;   
    224.             readstream.filesize = filesize;   
    225.             return readstream;   
    226.         }   
    227.   
    228.         class ReadStream : System.IO.Stream   
    229.         {   
    230.             public UploaderSqlServerProvider provider;   
    231.             public Guid guid;   
    232.             public int filesize;   
    233.             long pos = 0;   
    234.             byte[] _tempbuff = null;   
    235.             long _tempstart = -1;   
    236.   
    237.             public override long Length   
    238.             {   
    239.                 get  
    240.                 {   
    241.                     return filesize;   
    242.                 }   
    243.             }   
    244.             public override long Position   
    245.             {   
    246.                 get  
    247.                 {   
    248.                     return pos;   
    249.                 }   
    250.                 set  
    251.                 {   
    252.                     if (value < 0) throw (new ArgumentOutOfRangeException("Position"));   
    253.                     if (value >= filesize) throw (new ArgumentOutOfRangeException("Position"));   
    254.                     pos = value;   
    255.                     _tempbuff = null;   
    256.                     _tempstart = -1;   
    257.                 }   
    258.             }   
    259.             public override int Read(byte[] buffer, int offset, int count)   
    260.             {   
    261.                 int readsize = 0;   
    262.   
    263.                 while (true)   
    264.                 {   
    265.                     if (_tempstart != -1 && _tempbuff != null)   
    266.                     {   
    267.                         int start = (int)(pos - _tempstart);   
    268.                         if (start >= 0 && start < _tempbuff.Length)   
    269.                         {   
    270.                             int copysize = Math.Min(count, _tempbuff.Length - start);   
    271.                             Buffer.BlockCopy(_tempbuff, start, buffer, offset, copysize);   
    272.                             pos += copysize;   
    273.                             readsize += copysize;   
    274.                             offset += copysize;   
    275.                             count -= copysize;   
    276.                             if (count <= 0)   
    277.                                 return readsize;   
    278.                         }   
    279.                     }   
    280.   
    281.                     if (pos >= filesize)   
    282.                         return readsize;   
    283.   
    284.                     using (SqlCommand cmd = provider.CreateCommand("DECLARE @ptrval binary(16) ; SELECT @ptrval = TEXTPTR([FileData]) FROM [AjaxUploaderTempFiles] WHERE [FileGuid]=@Guid ; READTEXT [AjaxUploaderTempFiles].[FileData] @ptrval "  
    285.                                + pos + " " + Math.Min(BUFFERSIZE, filesize - pos)))   
    286.                     {   
    287.                         cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = guid;   
    288.                         _tempbuff = (byte[])cmd.ExecuteScalar();   
    289.                         _tempstart = pos;   
    290.                     }   
    291.                 }   
    292.   
    293.                 //return readsize;   
    294.             }   
    295.   
    296.             public override bool CanRead   
    297.             {   
    298.                 get  
    299.                 {   
    300.                     return true;   
    301.                 }   
    302.             }   
    303.             public override bool CanWrite   
    304.             {   
    305.                 get  
    306.                 {   
    307.                     return false;   
    308.                 }   
    309.             }   
    310.             public override bool CanSeek   
    311.             {   
    312.                 get  
    313.                 {   
    314.                     return true;   
    315.                 }   
    316.             }   
    317.             public override void Close()   
    318.             {   
    319.             }   
    320.             public override void Flush()   
    321.             {   
    322.   
    323.             }   
    324.             public override long Seek(long offset, System.IO.SeekOrigin origin)   
    325.             {   
    326.                 long oldpos = pos;   
    327.                 if (origin == System.IO.SeekOrigin.Begin)   
    328.                 {   
    329.                     Position = offset;   
    330.                 }   
    331.                 if (origin == System.IO.SeekOrigin.Current)   
    332.                 {   
    333.                     Position += offset;   
    334.                 }   
    335.                 if (origin == System.IO.SeekOrigin.End)   
    336.                 {   
    337.                     Position = filesize + offset;   
    338.                 }   
    339.                 return oldpos;   
    340.             }   
    341.   
    342.   
    343.             public override void SetLength(long value)   
    344.             {   
    345.                 throw (new NotSupportedException());   
    346.             }   
    347.             public override void Write(byte[] buffer, int offset, int count)   
    348.             {   
    349.                 throw (new NotSupportedException());   
    350.             }   
    351.   
    352.   
    353.   
    354.         }   
    355.   
    356.     }   
    357. }  

     
    Regards,
    Terry
     

  •  07-20-2009, 1:30 PM 54125 in reply to 53844

    Re: Uploading file directly into Database

    Reply Quote
    hi terry,
    thanks for the input.
     
    appreciate it !!

  •  08-15-2009, 5:15 AM 54715 in reply to 54125

    Re: Uploading file directly into Database

    Reply Quote
    Hello,
     
    How do you implement this, I can't get it to work?
     
    Is it posible to post a working sample?
     
    Edit: Have it working partial now... but trying to convert it to MySql and that seems to cause some troubles! 
     
    Thanks. 

  •  08-20-2009, 1:29 AM 54838 in reply to 54715

    Re: Uploading file directly into Database

    Reply Quote
     
     
    You need understand the code, and find a way to store the byte[] to MySql
     
    Regards,
    Terry
     
View as RSS news feed in XML