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>
<add name="ConnectionString" connectionString="Data Source=KALBAR-8C057618;Initial Catalog=DB_FILESTREAM;Integrated Security=True" providerName="System.Data.SqlClient"/>
</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">
<Columns>
<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">
<ItemTemplate>
<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.