Artykuł
10/06/2011

FileStream w SQL Server 2008, cz. 2/2

Autor: Wojciech Kalbarczyk

Opublikowano: 2010-11-08

Aplikacje klienckie mogą uzyskać dostęp do BLOB poprzez Win32 API. SQL Server 2008 oferuje uzyskanie dostępu do danych poprzez następujące funkcje:

- PathName – zwraca ścieżkę do obiektu

SELECT obrazek.PathName() AS PathName

FROM dbo.Obraz

WHERE obrazek='tekst'

Obraz 12. PathName

- GET_FILESTREAM_TRANSACTION_CONTEXT – zwraca token do aktualnej transakcji. W wypadku jej braku zwracana jest wartość NULL.

SELECT GET_FILESTREAM_TRANSACTION_CONTEXT () AS GET_FILESTREAM_TRANSACTION_CONTEXT

Obraz 13. GET_FILESTREAM_TRANSACTION_CONTEXT

- funkcje API OpenSqlFilestream:

1. ReadFile – odczytuje dane z pliku

2. WriteFile – zapisuje dane do pliku

3. TransmitFile – umożliwia przesyłanie plików

4. SetFilePointer – modyfikuje pozycję wskaźnika do otwartego pliku

5. SetEndOfFile – ustawia wskaźnik na koniec pliku

6. FlushFileBuffers – zapisuje dane z bufora do pliku

Tworzenie kopii zapasowych oraz przywracanie danych w bazach korzystających z FILESTREAM nie różni się niczym od wykonywania tych czynności w innych bazach.

BACKUP DATABASE [Pictures_FileStream] TO DISK = N'[…]Pictures_FileStream.bak' WITH NOFORMAT, NOINIT, NAME = N'Pictures_FileStream-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

Obraz 14. Tworzenie kopii zapasowej.

Wiemy już, jak działa mechanizm FILESTREAM po stronie bazy danych, więc przyjrzyjmy się teraz, jak możemy wykorzystać go w praktyczny sposób. Stworzymy w tym celu galerię obrazów przechowywanych w bazie danych za pomocą FILESTREAM. Skorzystamy do tego z bazy danych DB_FILESTREAM utworzonej w poprzednich przykładach. Tworzymy nową tabelę, która będzie przechowywała przykładowe obrazki.

CREATE TABLE dbo.Galeria

(

id_Obraz UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,

nr int,

nazwa varchar(50),

typ varchar(50),

obrazek varbinary(max) FILESTREAM

);

Następnie uzupełniamy tabelę przykładowymi danymi:

INSERT INTO dbo.Galeria

VALUES (NEWID(),1,'pierwszy','image/jpeg',(SELECT * FROM

OPENROWSET(BULK N'C:\Foto\pierwszy.jpg', SINGLE_BLOB)AS IMAGE))

Analogicznie dodajemy resztę wierszy.

Nasza tabela wygląda następująco:

SELECT * FROM dbo.Galeria

Obraz 15.Tabela z przykładowymi danymi.

Następnie tworzymy prostą aplikację ASP .NET, która wyświetli obrazy wraz z dodatkowymi informacjami. Aby połączyć się z bazą danych, musimy w pliku Web.config zdefiniować połączenie. W moim przypadku jest to:

</connectionStrings>

Następnie do pliku Default.aspx.vb dodajemy metodę, która będzie odpowiedzialna za wyświetlenie obrazów. W tym celu łączymy się z naszą bazą oraz wykonujemy zapytanie SQL. Definiujemy również typ MIME wyświetlanych plików oraz przesyłamy do strumienia HTTP obrazek w postaci binarnej, czyli takiej, w jakiej jest przechowywany w naszej bazie.

Imports System.Data.SqlClient

Partial Class _Default

Inherits System.Web.UI.Page

Protected Sub Page_DataBinding(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.DataBinding

End Sub

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim nr As Integer = Convert.ToInt32(Request.QueryString("nr"))

Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

Const SQL As String = "SELECT [typ], [obrazek] FROM [Galeria] WHERE [nr] = @nr"

Dim myCommand As New SqlCommand(SQL, myConnection)

myCommand.Parameters.AddWithValue("@nr", nr)

myConnection.Open()

Dim myReader As SqlDataReader = myCommand.ExecuteReader

If myReader.Read Then

Response.ContentType = myReader("typ").ToString()

Response.BinaryWrite(myReader("obrazek"))

End If

myReader.Close()

myConnection.Close()

End Using

End Sub

End Class

W pliku Default.aspx dodajemy kontrolkę GridView, która posłuży do wyświetlania danych pobranych z naszej bazy. Dodajemy kolumny zawierające id_Obrazu, numer, nazwę, typ oraz sam obraz. Warto zwrócić uwagę na sposób, w jaki zdefiniowany jest adres wyświetlanego obrazka. Dzięki wyrażeniu Eval adres kolejnych obrazów zmieniany jest dynamicznie.

<%@ Page Title="Home Page" Language="VB" MasterPageFile="~/Site.Master" AutoEventWireup="false"

CodeFile="Default.aspx.vb" Inherits="_Default" %>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">

</asp:Content>

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">

<h2>

<asp:GridView ID="GridView1" runat="server"

AutoGenerateColumns="False" DataKeyNames="nr"

DataSourceID="SqlDataSource1">

<asp:BoundField DataField="id_Obraz" HeaderText="id_Obraz"

SortExpression="id_Obraz" />

<asp:BoundField DataField="nr" HeaderText="nr" InsertVisible="False"

ReadOnly="True" SortExpression="nr" />

<asp:BoundField DataField="nazwa" HeaderText="nazwa"

SortExpression="nazwa" />

<asp:BoundField DataField="typ" HeaderText="typ"

SortExpression="typ" />

<asp:TemplateField HeaderText="obrazek">

<asp:Image ID="Image1" runat="server" ImageUrl='<%# Eval("nr", "~/Default.aspx?nr={0}") %>' /> </p>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [id_Obraz], [nr], [nazwa], [typ], [obrazek] FROM [Galeria] ORDER BY [nr]"></asp:SqlDataSource>

</h2>

</asp:Content>

W wyniku uruchomienia powyższej aplikacji otrzymujemy zobrazowanie wierszy z naszej bazy danych:

Obraz 16. Galeria obrazów ASP .NET .

Gdy mamy już gotową galerię, możemy dojść do wniosku, że przydałaby się aplikacja, dzięki której moglibyśmy w łatwy sposób dodawać oraz pobierać z bazy pliki. Przypuśćmy, że chcielibyśmy umieścić w bazie danych plik tekstowy znajdujący się na naszym dysku twardym. Należy więc określić ścieżkę do pliku, który chcemy dodać do bazy. Następnie definiujemy połączenie z bazą, do której dołączymy plik, oraz definiujemy polecenie SQL odpowiedzialne za dodanie pliku do bazy. Wraz z tekstem zawartym w pliku dodamy informacje o nazwie oraz formacie pliku. Pozostaje tylko połączyć się z bazą oraz wykonać tak przygotowane zapytanie SQL.

Dim filePath As String = "C:\tekst.doc"

Dim ConnString As String = "Server=KALBAR-8C057618; Initial Catalog = DB_FILESTREAM ; Integrated Security = SSPI"

Dim strQuery As String = "insert into Galeria(id_Obraz,nazwa,typ,obrazek) values (NEWID(),@nazwa,@typ,@obrazek)"

Dim cmd As New SqlCommand(strQuery)

cmd.Parameters.AddWithValue("@nazwa", Path.GetFileNameWithoutExtension(filePath))

cmd.Parameters.AddWithValue("@typ", Path.GetExtension(filePath))

cmd.Parameters.AddWithValue("@obrazek", File.ReadAllBytes(filePath))

Dim SQLConn As New SqlConnection()

SQLConn.ConnectionString = ConnString

SQLConn.Open()

cmd.Connection = SQLConn

cmd.ExecuteNonQuery()

SQLConn.Close()

Obraz 17. Baza przed dodaniem pliku.

Obraz 18. Baza po dodaniu pliku.

Mamy zatem już możliwość dodawania plików do naszej bazy. Z pewnością przydałaby się również możliwość pobierania plików z powrotem na nasz twardy dysk. W tym celu ponownie tworzymy połączenie z bazą, w której znajduje się nasz plik, oraz tworzymy zapytanie SQL zwracające nasz plik. Wynik zapytania zapisujemy do tablicy typu Byte. Następnie zawartość tablicy zapisujemy w obiekcie typu MemoryStream, aby następnie za pomocą strumienia fs zapisać tekst do pliku tekst2.docx .

Dim SQLConn As New SqlConnection

SQLConn.ConnectionString = "Server=KALBAR-8C057618; Initial Catalog = DB_FILESTREAM ; Integrated Security = SSPI"

Dim cmd As New System.Data.SqlClient.SqlCommand("SELECT obrazek FROM Galeria WHERE nazwa='tekst' ")

cmd.Connection = SQLConn

SQLConn.Open()

Dim buffer As Byte() = DirectCast(cmd.ExecuteScalar(), Byte())

Dim ms As New MemoryStream

ms.Write(buffer, 0, buffer.Length)

Dim fs As FileStream = File.Create("C:\tekst2.docx")

ms.WriteTo(fs)

cmd.Connection.Close()

theFile.Close()

ms.Close()

Obraz 19.Plik pobrany z bazy danych.

Wprowadzenie mechanizmu FILESTREAM pozwoliło na przechowywanie dowolnie dużych plików binarnych w systemie NTFS. Zachowany został przy tym bardzo szybki czas odczytu danych. Stosowanie FILESTREAM zaleca się, gdy mamy do czynienia z plikami większymi niż 1MB, bądź gdy zależy nam na wspomnianym czasie odczytu. Dzięki temu zwiększymy wydajność, zachowując możliwość transakcyjnego przetwarzania danych.

FileStream w SQL Server 2008, cz. 2/2

Autor: Wojciech Kalbarczyk

Dodatkowe zasoby