File streams in SQL 2008 provide a nice way of storing large blobs of data in the database without impacting database size. However creating them manually is not something that interests most programmers who want to use SMO.
So how can we create these through code?
The first thing you need to do is enable FILESTREAMS if they’re not already. Do this from SQL Server Configuration Manager:
Select Properties from the action menu
Enable Filestreams & restart the database if needed.
You now need to enable Filestream access (Server Properties->Advanced) on the server from SQL Management studio.
We’re now ready to start creating a database with a filestream and some tables:
Create The Database:
You can look at the article by S.Vinothkumar or use the code based on his work below:
Sub CreateDatabaseWithStream(ByVal dbName As String, ByVal serverName As String, ByVal dbPath As String)
Dim mdfFile As String = IO.Path.Combine(dbPath, dbName & ".mdf")
Dim ldfFile As String = IO.Path.Combine(dbPath, dbName & "_log.ldf")
Dim FilesDir As String = IO.Path.Combine(dbPath, dbName & "_filestream")
Dim server As New Microsoft.SqlServer.Management.Smo.Server(serverName)
Dim database As Database = New Database(server, dbName)
database.FileGroups.Add(New FileGroup(database, "PRIMARY"))
database.FileGroups.Add(New FileGroup(database, "FileStream" & dbName, True))
Dim dtPrimary As DataFile = New DataFile(database.FileGroups("PRIMARY"), "PriValue", mdfFile)
database.FileGroups("PRIMARY").Files.Add(dtPrimary)
database.FileGroups("FileStream" & dbName).Files.Add(New DataFile(database.FileGroups("FileStream" & dbName), "FileStream" & dbName & " Values", FilesDir))
Dim logFile As LogFile = New LogFile(database, "PriValue_log", ldfFile)
database.LogFiles.Add(logFile)
database.Create()
database.Refresh()
End Sub
Add A Table:
we now need to create a table with some columns, a data column and a type column.
The function below does this, the important things to remember are to set the id as a unqiue key.
Sub CreateTable(ByVal db As Database, ByVal tableName As String)
Dim tb As New Table(db, tableName)
Dim col As Column
col = New Column(tb, "rowid", DataType.UniqueIdentifier)
col.Identity = False
col.Nullable = False
col.RowGuidCol = True
Dim Dc As DefaultConstraint = col.AddDefaultConstraint("DF_RowGuid")
Dc.Text = "NEWID()"
tb.Columns.Add(col)
tb.Create()
Dim idx As New Index(tb, "AK_" & Now.Ticks)
Dim icol1 As IndexedColumn
icol1 = New IndexedColumn(idx, "rowid", False)
idx.IndexedColumns.Add(icol1)
idx.IndexKeyType = IndexKeyType.DriUniqueKey
idx.IsClustered = True
idx.Create()
col = New Column(tb, "filedata", DataType.VarBinaryMax, True)
col.Identity = False
col.Nullable = True
tb.Columns.Add(col)
col = New Column(tb, "ext", DataType.VarChar(255))
col.Identity = False
col.Nullable = True
tb.Columns.Add(col)
tb.Alter()
db.Alter()
End Sub
Create The Text Index:
Once we have our database then it’s time to create a our freetext index. You can do this simply by create a new freetext catalog for our new database, this is well documented here but in summary is creates a new catalog against the specified database.
dim ftc as New FullTextCatalog(db, "Test_Catalog")
ftc.IsDefault = True
ftc.Create()
Dim fti As FullTextIndex
fti = New FullTextIndex(tb)
Dim ftic As FullTextIndexColumn
ftic = New FullTextIndexColumn(fti, "filedata")
ftic.TypeColumnName = "ext"
ftic.Language = "English"
fti.IndexedColumns.Add(ftic)
fti.ChangeTracking = ChangeTracking.Automatic
fti.UniqueIndexName = idx.Name
fti.CatalogName = "Test_Catalog"
fti.Create()
So with everything setup the next thing to do is load your data into the table – make sure that you set the extension and the text indexing engine will do the rest. Then just use contains TSQL contains to mine the text catalog.
DECLARE @SearchWord nvarchar(30)
SET @SearchWord = 'test'
SELECT id,name
FROM files
WHERE CONTAINS(filedata, @SearchWord);
