SQL Server FileStream (excerpt from this site)

/*

This article mainly describes SQL Server FileStream support, if you are interested in SQL Server FileStream support, you can click on the following article to watch it, If you are interested in it, you can click on the following article to watch it.

The server has a property that specifies how the server supports file streams.

In fact, the file stream appears to solve a contradiction in the storage of large objects.

For large objects, the first method is stored in the database, which is typically done using the image field, or varbinary (max). The advantage is that you can make a unified backup, but in fact the read efficiency is low. Files that are larger than 1MB will be problematic

The second way is that the file is stored in the file system, and a path is stored in the database. In this way the database pressure is reduced, but it is not convenient for unified backup and management.

The file stream of SQL SERVER 2008 is actually the unification of the two. Files are also placed on the file system, but are managed by the database. You can unify backup and restore.

FileStream is not a field type, it is defined after a field and declares the column for file flow. The column is still stored in the binary

It is generally in the database to specifically add a filegroup and one or more files to store FILESTREAM data.

Then, on the disk there is a directory (Test), there will be a FILESTREAM.HDR file

The client is transparent. No modifications are required.

*/

--The function of this code is to enable the server to support file flow

EXEC sp_filestream_configure

@enable_level = 3;

CREATE DATABASE Archive

On

PRIMARY (NAME = Arch1,

FILENAME = ' c:labarchdat1.mdf '),

FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM (NAME = Arch3,

FILENAME = ' c:labfilestream1 ')

LOG on (NAME = Archlog1,

FILENAME = ' c:labarchlog1.ldf ')

GO

CREATE TABLE Archive.dbo.Records

(

[Id] [uniqueidentifier] ROWGUIDCOL not NULL UNIQUE,

[SerialNumber] INTEGER UNIQUE,

[Chart] VARBINARY (MAX) FILESTREAM NULL

-This sentence indicates that the column is to be stored with a file stream

--There are some improvements that can be made here, which is to add a field of file type (FileName)

)

filestream_on FileStreamGroup1

--The row indicates to which file stream file group to store

GO

--The following code cannot be run, and if a table is going to use a file stream, you must have a GUID column, and as a unique key column

CREATE TABLE Archive.dbo.Records2

(

--[id] [uniqueidentifier] ROWGUIDCOL not NULL UNIQUE,

[SerialNumber] INTEGER UNIQUE,

[Chart] VARBINARY (MAX) FILESTREAM NULL

-This sentence indicates that the column is to be stored with a file stream

)

filestream_on FileStreamGroup1--The row indicates to which file stream to store

GO

INSERT into Archive.dbo.Records

VALUES (NEWID (), 1, NULL);

GO

INSERT into Archive.dbo.Records

VALUES (NEWID (), 2,

CAST ("as varbinary (max)");

GO

INSERT into Archive.dbo.Records

VALUES (NEWID (), 3,

CAST (' Seismic Data ' as varbinary (max));

GO

UPDATE Archive.dbo.Records

SET [Chart] = CAST (' Xray 1 ' as varbinary (max))

WHERE [SerialNumber] = 2;

DELETE Archive.dbo.Records

WHERE serialnumber = 1;

GO

SELECT * from Archive.dbo.Records

GO

--For client programming, there is no difference, this is just a storage change of the server

--Save

--Save music files

/*

Try

{

OpenFileDialog dialog = new OpenFileDialog ();

Dialog. Filter = "Music file (*.mp3) |*.mp3";

if (dialog. ShowDialog () = = DialogResult.OK)

{

using (SqlConnection conn = new SqlConnection (CONNECTIONSTRING)) {

Conn. Open ();

using (SqlCommand cmd = conn. CreateCommand ()) {

Cmd.commandtext = "INSERT into binarytable (binarycontents) VALUES (@file)";

FileStream fs = new FileStream (dialog. FileName, FileMode.Open);

byte[] bytes = new Byte[fs. Length];

Fs. Read (bytes, 0, bytes. Length);

SqlParameter param = new SqlParameter ("@file", sqldbtype.varbinary,1000000);

Param. Value = bytes;

Cmd. Parameters.Add (param);

MessageBox.Show (cmd. ExecuteNonQuery () = = 1? "Save file Successfully": "Save file Failed");

Fs. Close ();

}

Conn. Close ();

}

}

Else

MessageBox.Show ("The user canceled the operation");

}

catch (Exception ex)

{

MessageBox.Show (ex. Message);

}

Read

Reading music files

Try

{

using (SqlConnection conn = new SqlConnection (CONNECTIONSTRING)) {

Conn. Open ();

using (SqlCommand cmd = conn. CreateCommand ()) {

Cmd.commandtext = "SELECT top 1 binarycontents from binarytable ORDER by ID Desc";

SqlDataReader reader = cmd. ExecuteReader ();

SaveFileDialog Dialog = new SaveFileDialog ();

Dialog. Filter = "Music file (*.mp3) |*.mp3";

if (dialog. ShowDialog () = = DialogResult.OK)

{

Reader. Read ();

System.Data.SqlTypes.SqlBinary result = reader. GetSqlBinary (0);//Noteworthy is that there is no Getsqlimage method here.

FileStream fs = new FileStream (dialog. FileName, FileMode.Create);

Fs. Write (result. Value, 0, result. Length);

Fs. Close ();

Reader. Close ();

}

Else

MessageBox.Show ("User cancel operation");

}

Conn. Close ();

}

}

catch (Exception ex) {MessageBox.Show (ex. Message); }

*/

This article is from the sky-dreaming blog, so be sure to keep this source http://kinwar.blog.51cto.com/3723399/1426854