Wiele firm i osób prywatnych wykorzystuje MsSqla w swoich projektach. Jest to fajna baza danych, ma tez kilka ciekawych feature’ów. Dziś omówię jeden z nich.
Mało osób wie że MsSql jest nie tylko relacyjną bazą danych, ale i obiektową, co oznacza że dane mogą być obiektami.
Pokażę jak stworzyć własny typ danych w MsSqlu – UDT (User Defined Types).
Jest to bardzo przydatna umiejętność, gdyż może zaoszczędzić sporo problemów przy projektowaniu baz danych, a jest słabo opisana w internecie.
Ok, zacznijmy od procesu przez jaki będziemy musieli przejść:
Ok, punkt 1 pominę, gdyż instalacje jego nie jest skomplikowana, dodam tylko że przykład piszę na Visual C# Express 2010 oraz SQL’u 2008 – te wersję mam gdyż korzystam nadal z Win XP ;)
Dobra, spróbujmy rozwiązać taki problem:
Pewna firma która produkuje koła zębate i chce mieć w bazie danych, w przejrzysty sposób pokazane jakiego typu, o jakich zębach oraz o jakiej średnicy koła są aktualnie w magazynie.
TYPY:
TYP1 – w skrócie T1
TYP2 – w skrócie T2
TYP3 – w skrócie T3
ŚREDNICA – liczba podwójnej precyzji
ZĘBY:
PROSTE – w skrócie P
ŚRUBOWE – w skrócie S
DASZKOWE – w skrócie D
ŁUKOWE – w skrócie L
Zęby dodatkowo mają swoją ilość, która jest liczbą całkowitą, a więc np P10, albo L20 jest poprawnym oznaczeniem. Zakładamy że wysokość zębów jest dla każdego koła taka sama (raczej nie spotykane w rzeczywistości ;) ).
Ok, opracujmy teraz jak będzie wyglądało oznaczenie koła:
Typ/Średnica/Ząb
a więc poprawnym oznaczeniem będzie np. T2/2.38/P20
Zebraliśmy wymagania, a więc przystępujemy do pisania.
Tworzymy nowy projekt w Visualu i dodajemy nową klasę (plik *.cs) i dodajemy referencję do Microsoft.SqlServer.Types.dll.
Następnie przystępujemy do właściwego pisania struktury ;)
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined,MaxByteSize = 8000,
IsByteOrdered = true, ValidationMethodName = "Validate")]
Te linijki to swoista konfiguracja. Najważniejsze rzeczy z niej to rzecz jasna ValidationMethodName – czyli nazwa metody która sprawdza czy typ tworzymy z odpowiedniego Stringa i drugi ważny parametr to Format – mamy dwa sensowne typy Formatu – Native i UserDefined. Oba maja swoje plusy i minusy.
Native – w nim możemy używać w naszej strukturze tylko typów prostych, a dokładniej: bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double, SqlByte, SqlInt16, SqlInt32, SqlInt64, SqlDateTime, SqlSingle, SqlDouble, SqlMoney no i inne typy użytkownika które zawierają te typy jako pola. I nic więcej nie musimy robić.
UserDefined – możemy każdy typ użyć, ale musimy je ręcznie serializować.
My mamy w naszym oznaczeniu znaki, są to pojedyncze litery i można by się pokusić o to żeby interpretować je po stronie programu jako liczby (np. wg kodu ASCII), ale ja wybrałem drugą drogę – daje większe możliwości.
No ale wróćmy do kodu
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [Serializable] [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, MaxByteSize = 8000, IsByteOrdered = true, ValidationMethodName = "Validate")] public struct Gear: INullable { private bool is_Null; private string cog; private double diameter; private string type; public bool IsNull { get { return (is_Null); } } } |
Zmienne:
is_Null – określa czy obiekt jest nullem.
cog – określa informację o zębach.
diameter – średnica.
type – typ.
Dodajmy pierwszą wymaganą metodę – Parse(SqlString):
1 2 3 4 5 6 7 8 9 10 | [SqlMethod(OnNullCall = false)] public static Gear Parse(SqlString s) { if (s.IsNull) return Null; Gear fn = new Gear(); string[] values = s.Value.Split(@ "/".ToCharArray()); fn.type = values[0]; fn.diameter = Double.Parse(values[1]); fn.cog = values[2]; if (!fn.Validate()) throw new ArgumentException("Invalid values!"); return fn; } |
Zostajemy przy formacie ze znakiem „/”, a więc jako parametr dostajemy string sql’owy i rozbijamy go względem znaku „/” a odpowiednie części zapisujemy do zmiennych.
Pojawiła się tutaj funkcja Validate() – sprawdza ona czy string który przekazaliśmy nadaje się na zamianę na obiekt. Napiszmy tę funkcję:
1 2 3 4 5 | private bool Validate() { if ((this.cog.Contains("P") || this.cog.Contains("S") || this.cog.Contains("D") || this.cog.Contains("L")) && this.type.Contains("T")) { return true; } else return false; } |
Używamy w niej też pola Null, którego nigdzie nie ma, a więc napiszmy go, wraz z właściwością GET:
1 2 3 4 5 6 7 | public static Gear Null { get { Gear pt = new Gear(); pt.is_Null = true; return pt; } } |
No i fajnie, a teraz czas na kolejną wymaganą metode – ToString() – dzięki niej możemy w SQLu wyświetlić obiekt
1 2 3 4 5 6 7 8 9 10 | public override string ToString() { if (this.IsNull) return "NULL"; else { StringBuilder builder = new StringBuilder(); builder.Append(type + "/"); builder.Append(diameter + "/"); builder.Append(cog); return builder.ToString(); } } |
No tutaj widać co robimy :)
I teraz chyba najtrudniejsza część – musimy zaimplementować interfejs IBinarySerialize, po czym jego metody:
public void Read(System.IO.BinaryReader r);
public void Write(System.IO.BinaryWriter w);
Żeby przeczytać coś, pierw musimy wiedzieć jak jest zapisane, więc zaczniemy od metody Write(). Ogólnie chodzi o to zeby zamienić pola naszej struktury na ciąg binarny.
A więc zaplanujmy to. Niech string będzie miał 20 bajtów. I w sumie to jedyne założenie :P no może kolejność, u nas wychodzi (taka sama kolejność jak przy oznaczeniu – Typ – Średnica – Zęby) String – double – String. A więc do dzieła ;)
1 2 3 4 5 6 7 8 9 10 11 12 13 | public void Write(System.IO.BinaryWriter w) { int maxStringSize = 20; string paddedString; paddedString = type.PadRight(maxStringSize, '\0'); //konczymy string binarnym zerem for (int i = 0; i < paddedString.Length; i++) { w.Write(paddedString[i]); //zapisujemy po literce } w.Write(diameter); //zapisujemy srednice paddedString = cog.PadRight(maxStringSize, '\0'); // to samo z zebami for (int i = 0; i < paddedString.Length; i++) { w.Write(paddedString[i]); } } |
I metoda odwrotna – korzystamy z informacji w jakiej kolejności zapisaliśmy dane.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | public void Read(System.IO.BinaryReader r) { int maxStringSize = 20; char[] chars; int stringEnd; string stringValue; chars = r.ReadChars(maxStringSize); stringEnd = Array.IndexOf(chars, '\0'); if (stringEnd == 0) { stringValue = null; return; } stringValue = new String(chars, 0, stringEnd); this.type = stringValue; this.diameter = r.ReadDouble(); chars = r.ReadChars(maxStringSize); stringEnd = Array.IndexOf(chars, '\0'); if (stringEnd == 0) { stringValue = null; return; } stringValue = new String(chars, 0, stringEnd); this.cog = stringValue; } |
I całość kodu:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 | using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using Microsoft.SqlServer.Types; using System.Data.SqlClient; [Serializable][Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, MaxByteSize = 8000, IsByteOrdered = true, ValidationMethodName = "Validate")] public struct Gear: INullable, IBinarySerialize { private bool is_Null; private string cog; private double diameter; private string type; public bool IsNull { get { return (is_Null); } } [SqlMethod(OnNullCall = false)] public static Gear Parse(SqlString s) { if (s.IsNull) return Null; Gear fn = new Gear(); string[] values = s.Value.Split(@ "/".ToCharArray()); fn.type = values[0]; fn.diameter = Double.Parse(values[1]); fn.cog = values[2]; if (!fn.Validate()) throw new ArgumentException("Invalid values!"); return fn; } private bool Validate() { if ((this.cog.Contains("P") || this.cog.Contains("S") || this.cog.Contains("D") || this.cog.Contains("L")) && this.type.Contains("T")) { return true; } else return false; } public static Gear Null { get { Gear pt = new Gear(); pt.is_Null = true; return pt; } } public override string ToString() { if (this.IsNull) return "NULL"; else { StringBuilder builder = new StringBuilder(); builder.Append(type + "/"); builder.Append(diameter + "/"); builder.Append(cog); return builder.ToString(); } } public void Write(System.IO.BinaryWriter w) { int maxStringSize = 20; string paddedString; paddedString = type.PadRight(maxStringSize, '\0'); for (int i = 0; i < paddedString.Length; i++) { w.Write(paddedString[i]); } w.Write(diameter); paddedString = cog.PadRight(maxStringSize, '\0'); for (int i = 0; i < paddedString.Length; i++) { w.Write(paddedString[i]); } } public void Read(System.IO.BinaryReader r) { int maxStringSize = 20; char[] chars; int stringEnd; string stringValue; chars = r.ReadChars(maxStringSize); stringEnd = Array.IndexOf(chars, '\0'); if (stringEnd == 0) { stringValue = null; return; } stringValue = new String(chars, 0, stringEnd); this.type = stringValue; this.diameter = r.ReadDouble(); chars = r.ReadChars(maxStringSize); stringEnd = Array.IndexOf(chars, '\0'); if (stringEnd == 0) { stringValue = null; return; } stringValue = new String(chars, 0, stringEnd); this.cog = stringValue; } } |
Kompilujemy to do postaci pliku *.dll i otwieramy Management Studiu SQL’a… Pierwsze co musimy zrobić to stworzyć ASSEMBLY – to jest trochę taki pomost między SQLem a innymi językami:
1 | CREATE ASSEMBLY Gear FROM 'E:\Documents and Settings\Matt\Moje dokumenty\Visual Studio 2010\Projects\sql\SQL\SQL\bin\Release\SQL.dll' WITH PERMISSION_SET = SAFE; |
Z naszego Assembly tworzymy teraz typ:
1 | CREATE TYPE dbo.Gear EXTERNAL NAME Gear.[Gear]; |
Żeby udowodnić że to na prawdę działa, stwórzmy tabelę:
1 | CREATE TABLE dbo.Gears (ID INT IDENTITY(1,1) PRIMARY KEY, gear Gear); |
prosta tabela, dwie kolumny, z czego jedna z nich jest naszego typu. No i dodajmy do niej coś:
1 2 | INSERT INTO dbo.Gears VALUES( CONVERT(Gear, 'T1/24,3/P20')); INSERT INTO dbo.Gears VALUES( CONVERT(Gear, 'T3/120,5/L56')); |
Funkcji CONVERT używać musimy. Teraz czas na ostateczne sprawdzenie, napisanie SELECTa:
1 | SELECT ID, gear FROM dbo.Gears; |
Ale po wykonaniu dostaliśmy, hmm.. Coś dziwnego!
ID gear
1 0x5431000000000000000000000000000000000000CDCCCCCCCC4C384050323000000000000000000000000000000000006E861BF0F9210940
2 0x54330000000000000000000000000000000000000000000000205E404C353600000000000000000000000000000000006E861BF0F9210940
Dostaliśmy reprezentację bajtową naszego obiektu. Poprawmy to:
1 | SELECT ID, gear.ToString() FROM dbo.Gears; |
I teraz jest poprawnie:
ID (No column name)
1 T1/24,3/P202
2 T3/120,5/L56
Uff i to by było na tyle. Zobacz sobie że nie można dodać nieprawidlowego wpisu, np:
1 | INSERT INTO dbo.Gears VALUES( CONVERT(Gear, 'P3/120,5/L56')); |
Typy użytkownika są dużo bardziej przydatne niż tutaj opisałem. Przede wszystkim mogą mieć metody i komunikować się z innymi obiektami – co daje nam ogromne możliwości bazodanowe.
Ostatnio tygodnie były tak bardzo wypełnione, że nie udało mi się napisać nawet krótkiego podsumowanie. Więc dziś zbiorczo podsumuję luty… Read More
Zapraszam na krótkie podsumowanie miesiąca. Książki W styczniu przeczytałem "Homo Deus: Historia jutra". Książka łudząco podoba do wcześniejszej książki tego… Read More
Cześć! Zapraszam na podsumowanie roku 2023. Książki Zacznijmy od książek. W tym roku cel 35 książek nie został osiągnięty. Niemniej… Read More
Zapraszam na krótkie podsumowanie miesiąca. Książki W grudniu skończyłem czytać Mein Kampf. Nudna książka. Ciekawsze fragmenty można by było streścić… Read More
Cześć, ostatnio w Internecie pojawiło się dużo artykułów, które nie były przychylne pracy zdalnej. Z drugiej strony większość komentarzy pod… Read More
Zapraszam na krótkie podsumowanie miesiąca. Książki W listopadzie dokończyłem cykl "Z mgły zrodzony" Sandersona. Tylko "Stop prawa" mi nie do… Read More
Pokaż komentarze
Wpis wysokich lotów, pozdrawiam serdecznie