Hi
Trying to get FILESTREAM working in XAF app
I prepared the database for FILESTREAM support and tested with direct quieries.
so i have:
Code public class BaseObject : XPCustomObject
{
public BaseObject(Session session) : base(session) { }
[Persistent("Oid"), Key(true), Browsable(false)]
private Guid oid = Guid.Empty;
[PersistentAlias("oid")]
[Browsable(false)]
[VisibleInLookupListView(false)]
public Guid Oid
{
get { return oid; }
}
[Persistent, Browsable(true), VisibleInLookupListView(false),ReadOnly(true)]
public string EarthCapeID
{
get { return Oid.ToString("D").ToLower(); }
}
public override void AfterConstruction()
{
base.AfterConstruction();
oid = XpoDefault.NewGuid();
}
}
and File data where i thought i'd decorate Content property with [DbType("VARBINARY(MAX) FILESTREAM")]
Codepublic class FileData : BaseObject, IFileData, IEmptyCheckable
{
[Persistent]
private int size;
private XPDelayedProperty delayedContent = new XPDelayedProperty();
private string fileName = "";
public FileData(Session session) : base(session) { }
public void LoadFromStream(string fileName, Stream stream)
{
FileName = fileName;
byte[] bytes = new byte[stream.Length];
stream.Read(bytes, 0, bytes.Length);
Content = bytes;
}
public void SaveToStream(Stream stream)
{
stream.Write(Content, 0, Size);
stream.Flush();
}
public void Clear()
{
Content = null;
FileName = String.Empty;
}
public override string ToString()
{
return FileName;
}
[Size(260)]
public string FileName
{
get { return fileName; }
set
{
SetPropertyValue<string>("FileName", ref fileName, value);
}
}
[Persistent, Delayed("delayedContent"),
ValueConverter(typeof(CompressionConverter)),
MemberDesignTimeVisibility(false)]
[DbType("VARBINARY(MAX) FILESTREAM")]
private byte[] Content
{
get { return (byte[])delayedContent.Value; }
set
{
int oldSize = size;
delayedContent.Value = value;
if (value != null)
{
size = value.Length;
}
else
{
size = 0;
}
OnChanged("Content");
OnChanged("Size", oldSize, size);
}
}
public int Size
{
get { return size; }
}
#region IEmptyCheckable Members
[NonPersistent, MemberDesignTimeVisibility(false)]
public bool IsEmpty
{
get { return string.IsNullOrEmpty(FileName); }
}
#endregion
}
I get "System.Data.SqlClient.SqlException: A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column."
Whats up?
Evgeniy
PS
Maybe there is a need for XAF code sample that will include Spatial and FILESTREAM support
Hi Evgeniy,
Your table doesn't have a ROWGUIDCOL - this is explained at length in Oliver's blogposts. Please re-check it.
Let me know in case of any difficulty.
Thanks,
Dennis
P.S.
>>
Maybe there is a need for XAF code sample that will include Spatial and FILESTREAM support
<<
I do not think so, this is not related to XAF, but rather to XPO. I think the information provided by Oliver is enough out-of-the-box.
Well, as per Oliver's update:
"Update: Another idea from one of our untiring XPO team members - Alex this time - you could use an XPCustomObject and establish a Guid type primary key. This will be marked with ROWGUIDCOL automatically (we introduced this originally to make the databases replication friendly) and of course it will have the required unique index."
and in fact if I introduce StreamId field as in his example it gives an error stating "two ROWGIUDCOLumns"
So thats why i thought i'd ask…
(screenshot attached)
Evgeniy
Hi Evgeniy,
I apologize for the delay.
You can have several columns of the datatype uniqueidentifier. But only one can have the ROWGUIDCOL attribute. Please try to reduce the ROWGUIDCOL property for the Oid column in the properties.
Another variant is to mark the guid Oid property of your base class with the following line: [DbType("uniqueidentifier unique") ] and recreate your table.
Did this help you?
Thanks,
Dennis
Dennis, still couldnt make it work :(
I attached my sample project.
I followed your second option as the first one would be a pain to use in practice when i have to drop the database several times per day.
Evgeniy
PS
remember to:
http://msdn.microsoft.com/en-us/library/bb933995.aspx
and i included Filegroup sql script in the zip
Hi Evgeniy,
Thank you for the update.
I have researched your sample and found that you have applied the [DbType("uniqueidentifier UNIQUE")] attribute not to the private persistent member, but to the alias property. That's wrong.
You need to apply this attribute to the oid member instead.
Does this work for you now?
Thanks,
Dennis
Nope, i still get "Duplicate column specified as ROWGUIDCOL"
Evgeniy
Hello Evgeniy,
Since your data table already has a uniqueidentifier ROWGUIDCOL (Oid), the StreamId column is not needed, and can be removed.
Actually, I don't see any benefit in using FILESTREAM in XPO. It's represented as a byte array in .NET memory. Please read James' comment in Oliver's blog - he is right. If you already have a FILESTREAM field in your database, then you can load it into XPO, and Oliver has demonstrated this. However, if you are not obliged to use FILESTREAM, it's best to define a persistent property of type "byte[]".
Thanks,
Nick
Nick, i see your point
Streaming though is not the main aim (if at all) to use FIlestream in my case. You I am not going into this but other points are described for instance here: http://blogs.msdn.com/manisblog/archive/2007/10/21/filestream-data-type-sql-server-2008.aspx
Anyway, thanks for resolving this! (Duh!)
Evgeniy
If i try to add something like this in Updater
if (Session.FindObject<User>(new BinaryOperator("FirstName", "Sam")) == null) { User user = new User(Session); user.FirstName = "Sam"; user.UserName = "Sam"; user.SetPassword(""); user.Save(); Role r = new Role(Session); r.Name = "role"; r.AddPermission(new ObjectAccessPermission(typeof(BaseObject), ObjectAccess.AllAccess)); r.AddPermission(new ObjectAccessPermission(typeof(FileData), ObjectAccess.AllAccess)); r.AddPermission(new ObjectAccessPermission(typeof(User), ObjectAccess.AllAccess)); r.Save(); user.Roles.Add(r); }I get
Violation of UNIQUE KEY constraint 'UQ__Persiste__DA15413E182C9B23'. Cannot insert duplicate key in object 'dbo.PersistentPermission'.
Evgeniy
PS
Without this the sample attached works fine when I removed streamid column
Hello Evgeniy,
Am I correct in my assumption that this is a new question and it's unrelated to the FILESTREAM problem discussed above? Please feel free to open new tickets for your new questions.
Please turn on XPO logging. It will show you queries sent to your database, and you'll be able to spot the INSERT query that fails. Then please open your database and the problematic table. I suspect there is an attempt to insert a new row with some value which already exists in your table, while a data column is marked as UNIQUE.
Thanks,
Nick
It kind of relates to it as the only thing that apparently causes it is [DbType("uniqueidentifier UNIQUE")] for oid property, which i need to add in case i decorate my FileData.Content with [DbType("VARBINARY(MAX) FILESTREAM")]
I have the sample attached where FileStream is disabled (so no need to set up FILESTREAM support on the server and database).
Evgeniy
Evgeniy,
I couldn't reproduce the problem with your persistent class in a new project with XPO. XAF may be the cause of the problem. We are investigating it.
Thanks,
Nick
Hi Evgeniy,
I've forced this to work with XAF and our BaseObject on my machine!
First of all, I suggest you check the following valuable article: SQL Server 2008 FILESTREAM - Part 1.
One more article: SQL Server Storage Engine, will be helpful as well.
The main idea is to remove the grid StreamId property from the FileData class, since we have already an unique identifier, which is actually the Oid, property that already met our needs to file stream support.
I have attached a sample project, which works well for me.
Thank you for your patience,
Dennis
Dennis, thanks again for taking time to resolve this!
There are couple of issues though.
I already removed StreamId column from FileData and Filestream worked fine in that sample which i stated in my post 27.8.2008 22:19:42
After that I tried to add some code in Updater.cs (28.8.2008 9:34:26) which caused the project to crash and the reason was [DbType("uniqueidentifier UNIQUE")] - Nick said it needs investigation and it works with XPO app but something is wrong in XAF.
So if you could test my code (from 28.8.2008 9:34:26) with the very sample you send and verify that it runs.
Cheers,
Evgeniy