Support forums - Re: Uploading file directly into Database

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

Re: Uploading file directly into Database

07-08-2009, 9:19 PM

cutechat
Joined on 07-22-2004
Posts 2,332

Re: Uploading file directly into Database

Reply Quote

Here is a sample code to use UPDATETEXT :

using System;
using System.Data;
using System.Data.SqlClient;
using CuteWebUI;
namespace UploaderDatabaseProvider
{
/// <summary>
/// UploaderSqlServerProvider
/// </summary>
public class UploaderSqlServerProvider : CuteWebUI.UploaderProvider
{
const int BUFFERSIZE = 204800;
SqlConnection _conn;
public override void Init(IAjaxUploader uploader, System.Web.HttpContext context)
{
string connectionstring = System.Configuration.ConfigurationSettings.AppSettings["UploaderDatabase"];
if (connectionstring == null) throw (new Exception("appSettings:UploaderDatabase not found."));
_conn = new SqlConnection(connectionstring);
_conn.Open();
}
public override void Dispose()
{
if (_conn != null)
_conn.Close();
base.Dispose();
}
private SqlCommand CreateCommand(string commandtext)
{
if (_conn == null) throw (new Exception("Not init yet."));
SqlCommand cmd = new SqlCommand();
cmd.Connection = _conn;
cmd.CommandText = commandtext;
return cmd;
}
public override bool SupportFS
{
get
{
return false;
}
}
public override string GetFSPath(Guid guid)
{
throw (new NotSupportedException());
}
public override void Maintain()
{
SqlCommand cmd = CreateCommand("DELETE [AjaxUploaderTempFiles] WHERE FileTime<@Time");
cmd.Parameters.Add("@Time", SqlDbType.DateTime).Value = DateTime.Now.AddHours(-1);
cmd.ExecuteNonQuery();
}
public override bool GetInfo(Guid guid, out string filename, out int filesize, out bool persist)
{
SqlCommand cmd = CreateCommand("SELECT [FileName],[FileSize],[IsPersist] FROM [AjaxUploaderTempFiles] WHERE FileGuid=@Guid");
cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = guid;
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
filename = reader.GetString(0);
filesize = reader.GetInt32(1);
persist = reader.GetBoolean(2);
return true;
}
else
{
filename = null;
filesize = 0;
persist = false;
return false;
}
}
}
public override void Delete(Guid guid)
{
SqlCommand cmd = CreateCommand("DELETE [AjaxUploaderTempFiles] WHERE FileGuid=@Guid");
cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = guid;
cmd.ExecuteNonQuery();
}
public override void Persist(Guid guid)
{
SqlCommand cmd = CreateCommand("UPDATE [AjaxUploaderTempFiles] SET IsPersist=1 WHERE FileGuid=@Guid");
cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = guid;
cmd.ExecuteNonQuery();
}
public override void UnPersist(Guid guid)
{
SqlCommand cmd = CreateCommand("UPDATE [AjaxUploaderTempFiles] SET IsPersist=0 WHERE FileGuid=@Guid");
cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = guid;
cmd.ExecuteNonQuery();
}
public override void Save(Guid guid, string filename, System.IO.Stream stream)
{
int stepsize = BUFFERSIZE;
long filesize = stream.Length;
byte[] data = new byte[Math.Min(stepsize, filesize)];
stream.Read(data, 0, data.Length);
SqlCommand cmd = CreateCommand("INSERT INTO [AjaxUploaderTempFiles] ([FileGuid],[FileTime],[FileName],[FileSize],[FileData],[IsPersist]) VALUES (@Guid,@Time,@Name,@Size,@Data,0)");
cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = guid;
cmd.Parameters.Add("@Time", SqlDbType.DateTime).Value = DateTime.Now;//for Maintain
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 255).Value = filename;
cmd.Parameters.Add("@Size", SqlDbType.Int).Value = filesize;
cmd.Parameters.Add("@Data", SqlDbType.Image).Value = data;
cmd.ExecuteNonQuery();
if (filesize <= stepsize)
return;
int sentsize = stepsize;
try
{
while (true)
{
int readsize = stream.Read(data, 0, data.Length);
if (readsize <= 0)
return;
cmd = CreateCommand("DECLARE @ptrval binary(16) ; SELECT @ptrval = TEXTPTR([FileData]) FROM [AjaxUploaderTempFiles] WHERE [FileGuid]=@Guid ; UPDATETEXT [AjaxUploaderTempFiles].[FileData] @ptrval "
+ sentsize + " 0 @Data");
cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = guid;
if (readsize != data.Length)
{
byte[] newdata = new byte[readsize];
Buffer.BlockCopy(data, 0, newdata, 0, readsize);
data = newdata;
}
cmd.Parameters.Add("@Data", SqlDbType.Image).Value = data;
cmd.ExecuteNonQuery();
sentsize += readsize;
}
}
catch (Exception)
{
Delete(guid);
throw;
}
}
public override void AppendData(Guid guid, string filename, System.IO.Stream stream)
{
int stepsize = BUFFERSIZE;
long filesize = stream.Length;
byte[] data = new byte[stepsize];
SqlCommand cmd = CreateCommand("SELECT [FileSize] FROM [AjaxUploaderTempFiles] WHERE [FileGuid]=@Guid ; UPDATE [AjaxUploaderTempFiles] SET [FileSize]=[FileSize]+@Size WHERE [FileGuid]=@Guid");
cmd.Parameters.Add("@Size", SqlDbType.Int).Value = filesize;
cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = guid;
int sentsize = Convert.ToInt32(cmd.ExecuteScalar());
try
{
while (true)
{
int readsize = stream.Read(data, 0, data.Length);
if (readsize <= 0)
break;
cmd = CreateCommand("DECLARE @ptrval binary(16) ; SELECT @ptrval = TEXTPTR([FileData]) FROM [AjaxUploaderTempFiles] WHERE [FileGuid]=@Guid ; UPDATETEXT [AjaxUploaderTempFiles].[FileData] @ptrval "
+ sentsize + " 0 @Data");
cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = guid;
if (readsize != data.Length)
{
byte[] newdata = new byte[readsize];
Buffer.BlockCopy(data, 0, newdata, 0, readsize);
data = newdata;
}
cmd.Parameters.Add("@Data", SqlDbType.Image).Value = data;
cmd.ExecuteNonQuery();
sentsize += readsize;
}
}
catch (Exception)
{
Delete(guid);
throw;
}
}
public override System.IO.Stream OpenStream(Guid guid)
{
SqlCommand cmd = CreateCommand("SELECT DATALENGTH([FileData]) FROM [AjaxUploaderTempFiles] WHERE FileGuid=@Guid");
cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = guid;
object val = cmd.ExecuteScalar();
if (val == null || Convert.IsDBNull(val))
throw (new Exception("File not found."));
int filesize = Convert.ToInt32(val);
int stepsize = BUFFERSIZE;
if (filesize <= stepsize)
{
cmd = CreateCommand("SELECT [FileData] FROM [AjaxUploaderTempFiles] WHERE FileGuid=@Guid");
cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = guid;
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
byte[] data = (byte[])reader.GetValue(0);
return new System.IO.MemoryStream(data);
}
}
throw (new Exception("File not found."));
}
ReadStream readstream = new ReadStream();
readstream.provider = this;
readstream.guid = guid;
readstream.filesize = filesize;
return readstream;
}
class ReadStream : System.IO.Stream
{
public UploaderSqlServerProvider provider;
public Guid guid;
public int filesize;
long pos = 0;
byte[] _tempbuff = null;
long _tempstart = -1;
public override long Length
{
get
{
return filesize;
}
}
public override long Position
{
get
{
return pos;
}
set
{
if (value < 0) throw (new ArgumentOutOfRangeException("Position"));
if (value >= filesize) throw (new ArgumentOutOfRangeException("Position"));
pos = value;
_tempbuff = null;
_tempstart = -1;
}
}
public override int Read(byte[] buffer, int offset, int count)
{
int readsize = 0;
while (true)
{
if (_tempstart != -1 && _tempbuff != null)
{
int start = (int)(pos - _tempstart);
if (start >= 0 && start < _tempbuff.Length)
{
int copysize = Math.Min(count, _tempbuff.Length - start);
Buffer.BlockCopy(_tempbuff, start, buffer, offset, copysize);
pos += copysize;
readsize += copysize;
offset += copysize;
count -= copysize;
if (count <= 0)
return readsize;
}
}
if (pos >= filesize)
return readsize;
using (SqlCommand cmd = provider.CreateCommand("DECLARE @ptrval binary(16) ; SELECT @ptrval = TEXTPTR([FileData]) FROM [AjaxUploaderTempFiles] WHERE [FileGuid]=@Guid ; READTEXT [AjaxUploaderTempFiles].[FileData] @ptrval "
+ pos + " " + Math.Min(BUFFERSIZE, filesize - pos)))
{
cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = guid;
_tempbuff = (byte[])cmd.ExecuteScalar();
_tempstart = pos;
}
}
//return readsize;
}
public override bool CanRead
{
get
{
return true;
}
}
public override bool CanWrite
{
get
{
return false;
}
}
public override bool CanSeek
{
get
{
return true;
}
}
public override void Close()
{
}
public override void Flush()
{
}
public override long Seek(long offset, System.IO.SeekOrigin origin)
{
long oldpos = pos;
if (origin == System.IO.SeekOrigin.Begin)
{
Position = offset;
}
if (origin == System.IO.SeekOrigin.Current)
{
Position += offset;
}
if (origin == System.IO.SeekOrigin.End)
{
Position = filesize + offset;
}
return oldpos;
}
public override void SetLength(long value)
{
throw (new NotSupportedException());
}
public override void Write(byte[] buffer, int offset, int count)
{
throw (new NotSupportedException());
}
}
}
}

Regards,
Terry

View Complete Thread