Wednesday, July 6, 2011

Creating a SQL Table using FileStream via SMO

To create a SQL Server 2008 Table with a varbinary(max) column using a FileStream the article FILESTREAM Storage in SQL Server 2008 gives the following example:

USE Production;
GO
CREATE TABLE DocumentStore (
       DocumentID INT IDENTITY PRIMARY KEY,
       Document VARBINARY (MAX) FILESTREAM NULL,
       DocGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL
              UNIQUE DEFAULT NEWID ())
FILESTREAM_ON FileStreamGroup1;
GO

Replicating this from C# using SMO is reasonably straight forward except for the UNIQUE constraint which doesn't appear to be available as an SMO column property. To get round this a index can be created to achieve the same result.

Table documentStoreTable = new Table(db, "DocumentStore");

documentStoreTable.FileStreamFileGroup = "FileStreamGroup1";
// A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.
// DocGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEWID ())
Column uniqueRowGuidColumn = new Column(documentStoreTable, "DocGUID", DataType.UniqueIdentifier);
uniqueRowGuidColumn.Nullable = false;
uniqueRowGuidColumn.RowGuidCol = true;

DefaultConstraint dc = uniqueRowGuidColumn.AddDefaultConstraint("DF_DocumentStore_" + uniqueRowGuidColumn.Name);
dc.Text = "NEWID()";

documentStoreTable.Columns.Add(uniqueRowGuidColumn);

Index id = new Index(documentStoreTable, "unique" + uniqueRowGuidColumn.Name);
id.IndexKeyType = IndexKeyType.DriUniqueKey;
id.IndexedColumns.Add(new IndexedColumn(id, uniqueRowGuidColumn.Name));
documentStoreTable.Indexes.Add(id);