Port of MS SQL namespace to MySql?

[Ujn Ujn]

Hello,

Wanted to know if anybody can help me port this namespace to be MySql compatible?

I have tried but with no luck...

It's for use with the Ajax Uploader from ajaxuploader.com

Here is the SQL schema (not MYSQL) - also if anybody could give me a correct converted schema for mysql I would be happy.

----------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AjaxUploaderTempFiles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[AjaxUploaderTempFiles]
GO

CREATE TABLE [dbo].[AjaxUploaderTempFiles] (
[FileGuid] [uniqueidentifier] NOT NULL ,
[FileTime] [datetime] NOT NULL ,
[FileName] [nvarchar] (255)
[FileSize] [int] NOT NULL ,
[FileData] [image] NOT NULL ,
[IsPersist] [bit] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[AjaxUploaderTempFiles] WITH NOCHECK ADD
CONSTRAINT [PK_AjaxUploaderTempFiles] PRIMARY KEY CLUSTERED
(
[FileGuid]
) ON [PRIMARY]
GO
----------------------------------------------------

Original:

################################################################
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());
}



}

}
}
################################################################

my port to MySql (not working)

################################################################
using System;
using System.Data;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using CuteWebUI;

namespace UploaderDatabaseProvider
{
/// <summary>
/// UploaderSqlServerProvider
/// </summary>
public class UploaderSqlServerProvider : CuteWebUI.UploaderProvider
{
const int BUFFERSIZE = 204800;

MySqlConnection _conn;

public override void Init(IAjaxUploader uploader, System.Web.HttpContext context)
{
//string connectionstring = System.Configuration.ConfigurationSettings.AppSettings["LocalMySqlServer"];
string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["LocalMySqlServer"].ConnectionString;
if (connectionstring == null) throw (new Exception("appSettings:LocalMySqlServer not found."));
_conn = new MySqlConnection(connectionstring);
_conn.Open();
}

public override void Dispose()
{
if (_conn != null)
_conn.Close();
base.Dispose();
}

private MySqlCommand CreateCommand(string commandtext)
{
if (_conn == null) throw (new Exception("Not init yet."));
MySqlCommand cmd = new MySqlCommand();
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()
{
MySqlCommand cmd = CreateCommand("DELETE AjaxUploaderTempFiles WHERE FileTime<@Time");
cmd.Parameters.Add("@Time", MySqlDbType.DateTime).Value = DateTime.Now.AddHours(-1);
cmd.ExecuteNonQuery();
}

public override bool GetInfo(Guid guid, out string filename, out int filesize, out bool persist)
{
MySqlCommand cmd = CreateCommand("SELECT FileName,FileSize,IsPersist FROM AjaxUploaderTempFiles WHERE FileGuid=@Guid");
cmd.Parameters.Add("@Guid", MySqlDbType.Guid).Value = guid;
using (MySqlDataReader 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)
{
MySqlCommand cmd = CreateCommand("DELETE AjaxUploaderTempFiles WHERE FileGuid=@Guid");
cmd.Parameters.Add("@Guid", MySqlDbType.Guid).Value = guid;
cmd.ExecuteNonQuery();
}

public override void Persist(Guid guid)
{
MySqlCommand cmd = CreateCommand("UPDATE AjaxUploaderTempFiles SET IsPersist=1 WHERE FileGuid=@Guid");
cmd.Parameters.Add("@Guid", MySqlDbType.Guid).Value = guid;
cmd.ExecuteNonQuery();
}
public override void UnPersist(Guid guid)
{
MySqlCommand cmd = CreateCommand("UPDATE AjaxUploaderTempFiles SET IsPersist=0 WHERE FileGuid=@Guid");
cmd.Parameters.Add("@Guid", MySqlDbType.Guid).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);
MySqlCommand cmd = CreateCommand("INSERT INTO AjaxUploaderTempFiles (FileGuid,FileTime,FileName,FileSize,FileData,IsPersist) VALUES (@Guid,@Time,@Name,@Size,@Data,0)");
cmd.Parameters.Add("@Guid", MySqlDbType.Guid).Value = guid;
cmd.Parameters.Add("@Time", MySqlDbType.DateTime).Value = DateTime.Now;//for Maintain
cmd.Parameters.Add("@Name", MySqlDbType.VarChar, 255).Value = filename;
cmd.Parameters.Add("@Size", MySqlDbType.Int32).Value = filesize;
cmd.Parameters.Add("@Data", MySqlDbType.LongBlob).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", MySqlDbType.Guid).Value = guid;
if (readsize != data.Length)
{
byte[] newdata = new byte[readsize];
Buffer.BlockCopy(data, 0, newdata, 0, readsize);
data = newdata;
}
cmd.Parameters.Add("@Data", MySqlDbType.LongBlob).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];

MySqlCommand cmd = CreateCommand("SELECT FileSize FROM AjaxUploaderTempFiles WHERE FileGuid=@Guid ; UPDATE AjaxUploaderTempFiles SET FileSize=FileSize+@Size WHERE FileGuid=@Guid");
cmd.Parameters.Add("@Size", MySqlDbType.Int32).Value = filesize;
cmd.Parameters.Add("@Guid", MySqlDbType.Guid).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", MySqlDbType.Guid).Value = guid;
if (readsize != data.Length)
{
byte[] newdata = new byte[readsize];
Buffer.BlockCopy(data, 0, newdata, 0, readsize);
data = newdata;
}
cmd.Parameters.Add("@Data", MySqlDbType.LongBlob).Value = data;
cmd.ExecuteNonQuery();
sentsize += readsize;
}
}
catch (Exception)
{
Delete(guid);
throw;
}
}


public override System.IO.Stream OpenStream(Guid guid)
{
MySqlCommand cmd = CreateCommand("SELECT DATALENGTH(FileData) FROM AjaxUploaderTempFiles WHERE FileGuid=@Guid");
cmd.Parameters.Add("@Guid", MySqlDbType.Guid).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", MySqlDbType.Guid).Value = guid;
using (MySqlDataReader 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 (MySqlCommand 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", MySqlDbType.Guid).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());
}



}

}
}

################################################################

So is it because there are some incompatible fields or what is wrong?

Any help would be great,

Thanks.



Edited 2 time(s). Last edit at 08/15/2009 08:02AM by Ujn Ujn.