{"id":359,"date":"2007-12-13T12:32:47","date_gmt":"2007-12-13T11:32:47","guid":{"rendered":"http:\/\/soci.hu\/blog\/index.php\/2007\/12\/13\/sql-server-2008-ujdonsagok-5-streaming-adatok-kezelese-kliens-oldalrol\/"},"modified":"2008-07-21T11:08:09","modified_gmt":"2008-07-21T10:08:09","slug":"sql-server-2008-ujdonsagok-5-streaming-adatok-kezelese-kliens-oldalrol","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2007\/12\/13\/sql-server-2008-ujdonsagok-5-streaming-adatok-kezelese-kliens-oldalrol\/","title":{"rendered":"SQL Server 2008 \u00fajdons\u00e1gok 5. – streaming adatok kezel\u00e9se kliensoldalr\u00f3l"},"content":{"rendered":"

Az el\u0151z\u0151 r\u00e9szben<\/a> elk\u00e9sz\u00fclt a t\u00e1bl\u00e1nk, ami streaming adatokat tud t\u00e1rolni. Itt az ideje p\u00e1r fot\u00f3t belesz\u00f3rni az adatb\u00e1zisba (illetve a f\u00e1jlrendszerbe).<\/p>\n

Az adatokat bek\u00fcldhetj\u00fck SQL parancsk\u00e9nt is, a megszokott TDS csatorn\u00e1t felhaszn\u00e1lva. Azonban pont az\u00e9rt rakt\u00e1k ki filerendszerbe ezeket a nagy adatokat, hogy NE TDS-en kereszt\u00fcl, hanem SMB-vel, a windows file megoszt\u00e1s\u00e1n kereszt\u00fcl \u00e9rj\u00fck el \u0151ket. Ez kicsit szokatlan lesz, hisz lesz ugyan SQL INSERT, de lesz sima f\u00e1jlkezel\u00e9s is a megold\u00e1sban. Mondom, menne sima SQL-lel is, de az nem lenne t\u00fal hat\u00e9kony.<\/p>\n

No, a megold\u00e1s el\u00e9g bizarr lesz. Nincs ugyanis managed fel\u00fclet az adatok kezel\u00e9s\u00e9re! Legal\u00e1bbis most, 2007 v\u00e9g\u00e9n nincs. Nat\u00edv API van, azt lehet rugdosni .NET-b\u0151l, interopon kereszt\u00fcl, ha kell.<\/p>\n

A megold\u00e1s menete v\u00e1zlatosan a k\u00f6vetkez\u0151.
\n1. Hozz\u00e1kapcsol\u00f3dunk a szerverhez SqlConnectionnel, tranzakci\u00f3t ind\u00edtunk, hozz\u00e1rendelj\u00fck a haszn\u00e1land\u00f3 SqlCommandunkhoz. Eddig semmi \u00faj.<\/p>\n

2. Besz\u00farjuk a stream metaadatait, a sima adatokat, egy insert-tel, hagyom\u00e1nyos m\u00f3don, ADO.NET-tel. <\/p>\n

\r\ninsert into Kepek (Id, Name, Photo) values (@Id, @Name, cast ('' as varbinary(max)))\r\n<\/pre>\n
A furcsa \u00fcres stringet kasztol\u00f3 iz\u00e9 az\u00e9rt van a parancsban, hogy meg\u00e1gyazzunk az adatoknak a filerendszerben. E n\u00e9lk\u00fcl, ha null maradna az oszlop \u00e9rt\u00e9ke nem j\u00f6nne l\u00e9tre f\u00e1jl a diszken, \u00edgy a k\u00f6vetkez\u0151 l\u00e9p\u00e9s se menne.<\/p>\n
3. Visszaolvassuk a sorunkhoz tartoz\u00f3 stream mint f\u00e1jl el\u00e9r\u00e9si \u00fatj\u00e1t \u00e9s egy tranzakci\u00f3 azonos\u00edt\u00f3t, ez kell majd a k\u00f6vetkez\u0151 l\u00e9p\u00e9sben. Mindkett\u0151re van egy \u00faj f\u00fcggv\u00e9ny illetve met\u00f3dus (kiemelve).<\/p>\n
\r\nselect Photo.<strong>PathName()<\/strong> PathName, <strong>get_filestream_transaction_context()<\/strong> TranCtx from Kepek where Id = @Id\r\n<\/pre>\n
4. Most j\u00f6n a bizarrabb r\u00e9sz. Kapunk egy nat\u00edv f\u00fcggv\u00e9nyt, azzal lehet megnyitni trazakcion\u00e1lisan a stream\u00fcnket mint f\u00e1jlt: OpenSqlFilestream. Mivel \u0151 nat\u00edv cucc, kell hozz\u00e1 interop deklar\u00e1ci\u00f3, kb. \u00edgy:<\/p>\n
[DllImport(“sqlncli10.dll”, SetLastError = true, CharSet = CharSet.Unicode)]
\npublic static extern SafeFileHandle OpenSqlFilestream(
\n    string FilestreamPath,
\n    UInt32 DesiredAccess,
\n    UInt32 OpenOptions,
\n    byte[] FilestreamTransactionContext,
\n    UInt32 FilestreamTransactionContextLength,
\n    LARGE_INTEGER_SQL AllocationSize
\n    );<\/p>\n
[StructLayout(LayoutKind.Sequential)]
\npublic struct LARGE_INTEGER_SQL
\n{
\n    public Int64 QuadPart;
\n    public LARGE_INTEGER_SQL(Int64 quadPart) { QuadPart = quadPart; }
\n}<\/p>\n
Ronda, de az interop m\u00e1r csak ilyen. \u00d6r\u00fclj\u00fcnk, hogy m\u0171k\u00f6dik. Vagy tess\u00e9k haszn\u00e1lni C++-t.<\/p>\n
A param\u00e9terek \u00e9rt\u00e9k\u00e9t az el\u0151z\u0151 pont select-je szolg\u00e1ltatja, amit egy reader-rel \u00e9rek el:<\/p>\n
byte[] tranCtx = (byte[])reader[“TranCtx”];<\/p>\n
SafeFileHandle h = OpenSqlFilestream(
\n(string)reader[“PathName”],
\nDESIRED_ACCESS_WRITE,
\n0,
\ntranCtx,
\n(uint)tranCtx.Length,
\nnew LARGE_INTEGER_SQL(0))<\/p>\n
Kapunk egy sz\u00e9p Handle-t, amit az interop r\u00e9teg egyb\u0151l be is csomagol egy SafeFileHandle-be, mert azt \u00fagy illik (aki nem hallott a safehandle-\u00f6kr\u0151l, most \u00e1lljon meg, \u00e9s s\u00fcrg\u0151sen olvasson ut\u00e1na<\/a>, nem hossz\u00fa a cikk).<\/p>\n
5. A Win32 handle j\u00f3 dolog, de nem kezd\u00fcnk neki WriteFile API-val baromkodni, hanem kihaszn\u00e1ljuk, hogy a FileStream k\u00f6nnyen \u00f6sszebar\u00e1tkozik valahonnan szerzett File Handle-\u00f6kkel:<\/p>\n
FileStream fsWrite = new FileStream(h, FileAccess.Write)<\/p>\n
6. Most m\u00e1r csak \u00edrni kell b\u0151szen az fsWrite-ba. Figyelj\u00fck meg, hogy ez a l\u00e9nyege pont a streaming el\u00e9r\u00e9snek, hogy nem \u00f6sszerakunk egy 34 GByte-os ojjektumot, mondjuk byte[]-\u00f6t, \u00e9s odav\u00e1gjuk a szervenek (eleve, a kliens belehalna ebbe), hanem apr\u00e1nk\u00e9nt lap\u00e1toljuk be az adatokat. R\u00e1\u00e9r\u0151sen, \u00f6regesen. Valahogy \u00edgy pl.<\/p>\n
int readed;
\nbyte[] buff = new byte[4096];
\ndo
\n{
\n    readed = fsRead.Read(buff, 0, buff.Length);
\n    fsWrite.Write(buff, 0, readed);
\n}
\nwhile (readed > 0);<\/p>\n
Az fsRead a helyi k\u00e9pre van megnyitva, amit betolunk a szervernek. Teccik l\u00e1tni, kicsi kis darabk\u00e1kban megy be a hatalmas k\u00e9p.<\/p>\n
Sz\u00e1mos k\u00e9rd\u00e9s, mint izol\u00e1ci\u00f3s szint, mi l\u00e1tszik ebb\u0151l a f\u00e1jlrendszerben, kihaszn\u00e1lja-e a tranzakcion\u00e1lis NTFS-t, stb. mer\u00fcl m\u00e9g fel, ezekkel a k\u00f6vetkez\u0151 r\u00e9szben foglalkozok.<\/p>\n
Z\u00e1r\u00e1sul berakom a teljes k\u00f3dot, \u00e9lvezz\u00e9tek. :)<\/p>\n
(Tud valaki valami norm\u00e1lis, k\u00e9sz, sz\u00e9p wordpress theme-et, ami \u00fagy van bel\u0151ve, hogy olyan sz\u00e9lesen form\u00e1zza meg a sz\u00f6veget, mint amekkora az ablak m\u00e9rete? Elkezdtem \u00e1tszabni ezt a Kubrick theme-et, de ez hal\u00e1l,  nekem bonyolult meg\u00e9rteni \u00e9s \u00e1t\u00edrni. A k\u00f3dok miatt kellene.)<\/p>\n
using System;
\nusing System.Data.SqlClient;
\nusing System.IO;
\nusing System.Data;
\nusing System.Runtime.InteropServices;
\nusing Microsoft.Win32.SafeHandles;<\/p>\n
namespace FSTest
\n{
\n    class Program
\n    {
\n        static void Main(string[] args)
\n        {
\n            SqlTransaction tran = null;
\n            try
\n            {
\n                using (SqlConnection conn = new SqlConnection(
\n                    @”Data Source=.\\sql2008;Initial Catalog=FSTeszt;Integrated Security=true;”))
\n                {
\n                    using (SqlCommand cmd = new SqlCommand())
\n                    {
\n                        conn.Open();
\n                        tran = conn.BeginTransaction();
\n                        cmd.Connection = conn;
\n                        cmd.Transaction = tran;<\/p>\n
                        foreach (string f in Directory.GetFiles(@”E:\\ment\\kepek\\Hivatni”, “*.jpg”))
\n                        {
\n                            \/\/K\u00e9p alapadatok besz\u00far\u00e1sa
\n                            cmd.CommandText = @”insert into Kepek (Id, Name, Photo)
\n                                values (@Id, @Name, CAST (” as varbinary(max)))”;
\n                            cmd.Parameters.Clear();
\n                            cmd.Parameters.Add(“@Name”, SqlDbType.NVarChar).Value = Path.GetFileName(f);
\n                            Guid id = Guid.NewGuid();
\n                            cmd.Parameters.Add(“@Id”, SqlDbType.UniqueIdentifier).Value = id;
\n                            cmd.ExecuteNonQuery();<\/p>\n
                            \/\/Maga a k\u00e9p ment\u00e9se mint streaming adat
\n                            cmd.CommandText = @”select Photo.PathName() PathName,
\n                                get_filestream_transaction_context() TranCtx from Kepek where Id = @Id”;
\n                            using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
\n                            {
\n                                if (reader.Read())
\n                                {
\n                                    byte[] tranCtx = (byte[])reader[“TranCtx”];<\/p>\n
                                    using (SafeFileHandle h = OpenSqlFilestream(
\n                                        (string)reader[“PathName”],
\n                                        DESIRED_ACCESS_WRITE,
\n                                        0,
\n                                        tranCtx,
\n                                        (uint)tranCtx.Length,
\n                                        new LARGE_INTEGER_SQL(0)))
\n                                    {<\/p>\n
                                        if (!h.IsInvalid)
\n                                        {
\n                                            using (FileStream fsWrite = new FileStream(h, FileAccess.Write))
\n                                            using (FileStream fsRead = File.OpenRead(f))
\n                                            {
\n                                                int readed;
\n                                                byte[] buff = new byte[4096];
\n                                                do
\n                                                {
\n                                                    readed = fsRead.Read(buff, 0, buff.Length);
\n                                                    fsWrite.Write(buff, 0, readed);
\n                                                }
\n                                                while (readed > 0);
\n                                            }
\n                                        }
\n                                        else
\n                                        {
\n                                            int errno = Marshal.GetLastWin32Error();
\n                                            Console.WriteLine(errno);
\n                                            Environment.Exit(-1);
\n                                        }
\n                                    }
\n                                }
\n                            }<\/p>\n
                        }
\n                    }
\n                    tran.Commit();
\n                }
\n            }
\n            catch (Exception e)
\n            {
\n                tran.Rollback();
\n                Console.WriteLine(e);
\n            }
\n        }<\/p>\n
        public const UInt32 DESIRED_ACCESS_READ = 0x00000000;
\n        public const UInt32 DESIRED_ACCESS_WRITE = 0x00000001;
\n        public const UInt32 DESIRED_ACCESS_READWRITE = 0x00000002;<\/p>\n
        [DllImport(“sqlncli10.dll”, SetLastError = true, CharSet = CharSet.Unicode)]
\n        public static extern SafeFileHandle OpenSqlFilestream(
\n            string FilestreamPath,
\n            UInt32 DesiredAccess,
\n            UInt32 OpenOptions,
\n            byte[] FilestreamTransactionContext,
\n            UInt32 FilestreamTransactionContextLength,
\n            LARGE_INTEGER_SQL AllocationSize
\n            );<\/p>\n
        [StructLayout(LayoutKind.Sequential)]
\n        public struct LARGE_INTEGER_SQL
\n        {
\n            public Int64 QuadPart;
\n            public LARGE_INTEGER_SQL(Int64 quadPart) { QuadPart = quadPart; }
\n        }<\/p>\n
        [DllImport(“kernel32.dll”)]
\n        private extern static void CloseHandle(IntPtr handle);
\n    }
\n}<\/p>\n","protected":false},"excerpt":{"rendered":"
Az el\u0151z\u0151 r\u00e9szben elk\u00e9sz\u00fclt a t\u00e1bl\u00e1nk, ami streaming adatokat tud t\u00e1rolni. Itt az ideje p\u00e1r fot\u00f3t belesz\u00f3rni az adatb\u00e1zisba (illetve a f\u00e1jlrendszerbe)….<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,25,6,4,30,58],"tags":[],"class_list":["post-359","post","type-post","status-publish","format-standard","hentry","category-net","category-adonet","category-adatbazisok","category-szakmai-elet","category-sql-server","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/359","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=359"}],"version-history":[{"count":0,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/359\/revisions"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=359"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=359"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=359"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}