Table-Valued Parameters<\/strong><\/h2>\n\n\n\nTVP to jedna z najbardziej u\u017cytecznych funkcji SQL Server. Takie parametry zapewniaj\u0105 \u0142atwy spos\u00f3b organizowania wielu wierszy danych z aplikacji klienckiej do us\u0142ugi SQL Server<\/a>. Oznacza to, \u017ce mo\u017cemy przekaza\u0107 ca\u0142\u0105 tabel\u0119 do procedury sk\u0142adowanej lub funkcji w jednym parametrze. Aby ich u\u017cy\u0107, musimy zacz\u0105\u0107 od utworzenia typu w bazie danych.<\/p>\n\n\n\nCREATE TYPE [dbo].[udtt_Project] AS TABLE\n(\n\t[Id] int NULL,\n\t[Name] nvarchar(4000) NOT NULL,\n\t[ProjectStartDate] DateTimeOffset NULL,\n\t[Active] bit NOT NULL, \n\t[Draft] bit NOT NULL\n)\n<\/pre><\/div>\n\n\nW nast\u0119pnym kroku mo\u017cemy utworzy\u0107 procedur\u0119 sk\u0142adowan\u0105, kt\u00f3ra przyjmie nasz typ tabelaryczny zdefiniowany przez u\u017cytkownika jako parametr.<\/p>\n\n\n
\nCREATE PROCEDURE [dbo].[ProcedureWhichAcceptsProjectsAsTVP]\n\t@projects [dbo].[udtt_Project] readonly\nAS\nBEGIN\n\tSELECT count(*)\n\tfrom @projects;\nEND\n<\/pre><\/div>\n\n\nTo wszystko, co musimy zrobi\u0107 w bazie danych. Jednak zanim b\u0119dziemy mogli u\u017cy\u0107 naszej procedury sk\u0142adowanej, musimy nauczy\u0107 si\u0119, jak przekazywa\u0107 Table-Valued Parameter za pomoc\u0105 Dappera\u2026<\/p>\n\n\n\n
Przekazywanie Table-Valued Parameter z wykorzystaniem Dappera<\/strong><\/h2>\n\n\n\nAby utworzy\u0107 Table-Valued Parameter, musimy wywo\u0142a\u0107 metod\u0119 AsTableValuedParameter<\/em>. Metoda ta konwertuje DataTable<\/em> lub IEnumerable<T> where T : IDataRecord<\/em> na ICustomQueryParameter<\/em>. Skupimy si\u0119 na DataTable<\/em>, poniewa\u017c uwa\u017cam, \u017ce b\u0119dzie \u0142atwiejszy do zrozumienia.<\/p>\n\n\n\nJako programi\u015bci zwykle nie u\u017cywamy tych typ\u00f3w, ale raczej listy okre\u015blonych modeli. Oznacza to, \u017ce aby utworzy\u0107 TVP, musimy przekonwertowa\u0107 nasz\u0105 List\u0119<\/em> na DataTable<\/em>, a nast\u0119pnie wywo\u0142a\u0107 AsTableValuedParameter<\/em>. Zak\u0142adaj\u0105c, \u017ce mamy model C#, kt\u00f3ry dok\u0142adnie reprezentuje nasz typ tabelaryczny (User-Defined Table Type<\/em>), kt\u00f3ry wygl\u0105da nast\u0119puj\u0105co:<\/p>\n\n\n\npublic class Project\n{\n public int? Id { get; set; }\n public string Name { get; set; } = String.Empty;\n public DateTimeOffset? ProjectStartDate { get; set; }\n public bool Active { get; set; }\n public bool Draft { get; set; }\n}\n\n<\/pre><\/div>\n\n\nPierwsza my\u015bl, jaka przychodzi nam do g\u0142owy, to po prostu utworzenie DataTable<\/em>, r\u0119czne dodanie potrzebnych kolumn i wype\u0142nienie ich danymi w nast\u0119puj\u0105cy spos\u00f3b:<\/p>\n\n\n\nusing var conn = new SqlConnection(connectionString);\nconn.Open();\n\n\/\/ some example data\nList<Project> projects = new()\n{\n new Project { Id = 1, Name = "Name1", ProjectStartDate = DateTimeOffset.Parse("2022-11-01"), Active = true, Draft = false },\n new Project { Id = 2, Name = "Name2", ProjectStartDate = DateTimeOffset.Parse("2022-12-01"), Active = false, Draft = true }\n};\n\n\/\/ create DataTable\nDataTable projectsDT = new();\nprojectsDT.Columns.Add(nameof(Project.Id), typeof(int));\nprojectsDT.Columns.Add(nameof(Project.Name), typeof(string));\nprojectsDT.Columns.Add(nameof(Project.ProjectStartDate), typeof(DateTimeOffset));\nprojectsDT.Columns.Add(nameof(Project.Active), typeof(bool));\nprojectsDT.Columns.Add(nameof(Project.Draft), typeof(bool));\n\n\/\/ add rows to DataTable\nforeach (var project in projects)\n{\n var row = projectsDT.NewRow();\n row[nameof(Project.Id)] = project.Id ?? (object)DBNull.Value;\n row[nameof(Project.Name)] = project.Name;\n row[nameof(Project.ProjectStartDate)] = project.ProjectStartDate ?? (object)DBNull.Value;\n row[nameof(Project.Active)] = project.Active;\n row[nameof(Project.Draft)] = project.Draft;\n projectsDT.Rows.Add(row);\n}\n\n\/\/ create parameters\nvar parameters = new\n{\n projects = projectsDT.AsTableValuedParameter("[dbo].[udtt_Project]")\n};\n\n\/\/ execute Stored Procedure\nreturn await conn.ExecuteScalarAsync<int>(\n "[dbo].[ProcedureWhichAcceptsProjectsAsTVP]",\n param: parameters,\n commandType: CommandType.StoredProcedure);\n<\/pre><\/div>\n\n\nTyle wystarczy, aby wszystko zadzia\u0142a\u0142o. Dodatkowo, mo\u017cesz do\u0142\u0105czy\u0107 fragment kodu odpowiedzialny za tworzenie DataTable<\/em> jako metod\u0119 statyczn\u0105 w klasie Project<\/em>, aby ponownie wykorzysta\u0107 go w innych miejscach.<\/p>\n\n\n\nWygenerowany skrypt SQL<\/strong><\/h3>\n\n\n\nSprawd\u017amy teraz, co Dapper wygenerowa\u0142 i przekaza\u0142 do bazy danych. W narz\u0119dziu Profiler widzimy, \u017ce zosta\u0142 wykonany nast\u0119puj\u0105cy skrypt:<\/p>\n\n\n
\ndeclare @p1 dbo.udtt_Project\ninsert into @p1 values(1,N'Name1','2022-11-01 00:00:00 +01:00',1,0)\ninsert into @p1 values(2,N'Name2','2022-12-01 00:00:00 +01:00',0,1)\n\nexec [dbo].[ProcedureWhichAcceptsProjectsAsTVP] @projects=@p1\n<\/pre><\/div>\n\n\nCzy widzisz co\u015b niepokoj\u0105cego? C\u00f3\u017c, wszystkie dane wstawiane do zmiennej @p1<\/em> s\u0105 mapowane porz\u0105dkowo. Instrukcja Insert<\/em> nie okre\u015bla \u017cadnych kolumn, wi\u0119c ca\u0142a operacja opiera si\u0119 na prawid\u0142owej kolejno\u015bci kolumn po stronie DataTable<\/em> w C# oraz User-Defined Table Type<\/em> w SQL.<\/p>\n\n\n\nNale\u017cy zawsze pami\u0119ta\u0107, \u017ce nie ma mapowania wed\u0142ug nazwy kolumny i za ka\u017cdym razem podczas tworzenia DataTable<\/em> powinni\u015bmy zadba\u0107 o:<\/p>\n\n\n\n\n- w\u0142a\u015bciw\u0105 kolejno\u015b\u0107 kolumn,<\/li>\n\n\n\n
- dodawanie kolumn, kt\u00f3re istniej\u0105 w UDTT, ale nie w modelu C#,<\/li>\n\n\n\n
- pomijanie w\u0142a\u015bciwo\u015bci, kt\u00f3re istniej\u0105 w modelu C#, ale nie w UDTT.<\/li>\n<\/ul>\n\n\n\n
Automatyzacja tworzenia TVP<\/strong><\/h2>\n\n\n\nW sumie nauczyli\u015bmy si\u0119 ju\u017c przekazywa\u0107 TVP za pomoc\u0105 Dappera, wi\u0119c co wi\u0119cej mo\u017cemy powiedzie\u0107? C\u00f3\u017c, wyobra\u017a sobie, \u017ce u\u017cywamy w aplikacji wielu r\u00f3\u017cnych UDTT lub cz\u0119sto dochodzi do zmian w ich strukturze podczas developmentu. Jeste\u015bmy wtedy zmuszeni r\u0119cznie zdefiniowa\u0107 struktur\u0119 DataTable<\/em> dla wszystkich UDTT i poprawi\u0107 je, je\u015bli wyst\u0105pi\u0105 jakie\u015b zmiany. Oczywi\u015bcie nie chcemy traci\u0107 czasu na takie rzeczy, dlatego postaramy si\u0119 zautomatyzowa\u0107 proces budowania DataTable<\/em>.<\/p>\n\n\n\nZautomatyzowane tworzenie TVP \u2013 podej\u015bcie numer 1<\/strong><\/h3>\n\n\n\nPierwsza my\u015bl, jaka przychodzi do g\u0142owy, to automatyczne tworzenie DataTable<\/em> na podstawie w\u0142a\u015bciwo\u015bci klasy. Nie b\u0119dziemy musieli tego robi\u0107 za ka\u017cdym razem, gdy pojawi si\u0119 nowy UDTT. Po prostu tworzymy model w j\u0119zyku C# z dok\u0142adnie takimi samymi w\u0142a\u015bciwo\u015bciami i uruchamiamy metod\u0119, kt\u00f3ra generuje DataTable<\/em>. Wygl\u0105da dobrze? Dobra, zaczynamy! <\/p>\n\n\n\nMo\u017cemy zdefiniowa\u0107 metod\u0119 rozszerzaj\u0105c\u0105, kt\u00f3ra konwertuje IEnumerable<T><\/em> na DataTable<\/em> na podstawie w\u0142a\u015bciwo\u015bci zdefiniowanych w T<\/em><\/p>\n\n\n\npublic static DataTable ToDataTable<T>(this IEnumerable<T> enumerable)\n{\n var dataTable = new DataTable();\n var propertyDescriptorCollection = TypeDe-scriptor.GetProperties(typeof(T));\n for (int i = 0; i < propertyDescriptorCollection.Count; i++)\n {\n var propertyDescriptor = propertyDescriptorCollection[i];\n var type = propertyDescriptor.PropertyType;\n\n if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))\n type = Nullable.GetUnderlyingType(type)!;\n\n dataTable.Columns.Add(propertyDescriptor.Name, type);\n }\n var values = new object[propertyDescriptorCollection.Count];\n foreach (T iListItem in enumerable)\n {\n for (int i = 0; i < values.Length; i++)\n {\n values[i] = propertyDescriptorCollec-tion[i].GetValue(iListItem)!;\n }\n dataTable.Rows.Add(values);\n }\n return dataTable;\n}\n<\/pre><\/div>\n\n\ni u\u017cy\u0107 jej do budowania TVP w nast\u0119puj\u0105cy spos\u00f3b:<\/p>\n\n\n
\nusing var conn = new SqlConnection(connectionString);\nconn.Open();\n\n\/\/ some example data\nList<Project> projects = new()\n{\n new Project { Id = 1, Name = "Name1", ProjectStartDate = DateTimeOff-set.Parse("2022-11-01"), Active = true, Draft = false },\n new Project { Id = 2, Name = "Name2", ProjectStartDate = DateTimeOff-set.Parse("2022-12-01"), Active = false, Draft = true }\n};\n\n\/\/ create DataTable\nvar projectsDT = projects.ToDataTable();\n\n\/\/ create parameters\nvar parameters = new\n{\n projects = projectsDT.AsTableValuedParameter("[dbo].[udtt_Project]")\n};\n\n\/\/ execute Stored Procedure\nreturn await conn.ExecuteScalarAsync<int>(\n "[dbo].[ProcedureWhichAcceptsProjectsAsTVP]",\n param: parameters,\n commandType: CommandType.StoredProcedure);\n<\/pre><\/div>\n\n\nTo podej\u015bcie wygl\u0105da obiecuj\u0105co<\/strong>. Nie musimy ju\u017c r\u0119cznie definiowa\u0107 DataTable<\/em>. Wystarczy tylko wywo\u0142a\u0107 metod\u0119 ToDataTable<\/em> na dowolnym IEnumerable<T><\/em>.<\/p>\n\n\n\nNiestety, takie podej\u015bcie ma swoje wady. Pami\u0119tasz, jak Dapper tworzy TVP? Dodaje dane do kolumn w okre\u015blonej kolejno\u015bci, a nie wed\u0142ug nazw kolumn. Je\u015bli si\u0119 nad tym zastanowi\u0107, jest kilka sytuacji, w kt\u00f3rych to podej\u015bcie nie zadzia\u0142a:<\/p>\n\n\n\n
\n- Je\u015bli kolejno\u015b\u0107 w\u0142a\u015bciwo\u015bci modelu C# nie pasuje do kolejno\u015bci kolumn UDTT (mo\u017ce w modelach b\u0119dzie jakie\u015b dziedziczenie, kt\u00f3re zaburzy kolejno\u015b\u0107 itp.?).<\/li>\n\n\n\n
- Je\u015bli w modelu C# jest jaka\u015b dodatkowa w\u0142a\u015bciwo\u015b\u0107, kt\u00f3rej nie ma w UDTT (b\u0119dziemy pr\u00f3bowali przekaza\u0107 za du\u017co kolumn i dostaniemy b\u0142\u0105d).<\/li>\n\n\n\n
- Je\u015bli w UDTT istnieje kolumna, kt\u00f3ra nie istnieje w modelu C# (podanie mniejszej liczby kolumn ni\u017c jest to wymagane zak\u0142\u00f3ca dopasowanie ich w odpowiedniej kolejno\u015bci i mo\u017ce powodowa\u0107 b\u0142\u0105d zgodno\u015bci typu w kolumnach).<\/li>\n<\/ul>\n\n\n\n
Bior\u0105c pod uwag\u0119 powy\u017csze, takie podej\u015bcie b\u0119dzie dzia\u0142a\u0107 tylko wtedy<\/strong>, gdy liczba i kolejno\u015b\u0107 kolumn b\u0119d\u0105 dok\u0142adnie takie same mi\u0119dzy modelem C# oraz UDTT. Oczywist\u0105 zalet\u0105 jest automatyczne tworzenie DataTable<\/em>, jednak uwzgl\u0119dniaj\u0105c wady tego podej\u015bcia, mo\u017cna powiedzie\u0107, \u017ce nie jest ono tak wszechstronne<\/strong>, jak by\u015bmy tego chcieli.<\/p>\n\n\n\nMimo wszystko mo\u017cesz je zastosowa\u0107, je\u015bli w twoim projekcie wszystkie modele C# dok\u0142adnie pasuj\u0105 do UDTT i zadbasz o to, \u017ceby tak pozosta\u0142o. Je\u015bli cho\u0107 troch\u0119 martwi Ci\u0119 b\u0142\u0119dne mapowanie, zapraszam do kolejnej sekcji, w kt\u00f3rej postaramy si\u0119 opracowa\u0107 lepsze rozwi\u0105zanie<\/strong>.<\/p>\n\n\n\nZautomatyzowane tworzenie TVP \u2013 podej\u015bcie numer 2<\/strong><\/h3>\n\n\n\nNasze pierwsze podej\u015bcie mia\u0142o wiele wad, kt\u00f3re musimy wzi\u0105\u0107 pod uwag\u0119 oraz je wyeliminowa\u0107. Jednak, aby zapewni\u0107 prawid\u0142ow\u0105 kolejno\u015b\u0107 kolumn lub uzupe\u0142nia\u0107 brakuj\u0105ce kolumny, musimy zna\u0107 zar\u00f3wno definicj\u0119 modelu w j\u0119zyku C#, jak i UDTT w bazie danych. Spr\u00f3bujemy wi\u0119c odczyta\u0107 definicj\u0119 UDTT z bazy danych i spr\u00f3bowa\u0107 dopasowa\u0107 ka\u017cd\u0105 w\u0142a\u015bciwo\u015b\u0107 do konkretnej kolumny UDTT wed\u0142ug nazwy. Raz na zawsze po\u017cegnamy ba\u0142agan zwi\u0105zany z kolejno\u015bci\u0105 kolumn lub b\u0142\u0119dami wynikaj\u0105cymi ze z\u0142ej liczby kolumn. <\/p>\n\n\n\n
Zobaczmy zatem implementacj\u0119: <\/p>\n\n\n
\nusing Dapper;\nusing System.Data;\n\nnamespace DapperTVPOrderedExample.DapperExtensions\n{\n internal static class DapperExtensions\n {\n \/\/\/ <summary>\n \/\/\/ This extension converts enumerable of objects to a Dapper TVP with columns ordered by T-SQL UDTT columns.\n \/\/\/ Class properties names must correspond to T-SQL UDTT column names.\n \/\/\/ <\/summary>\n \/\/\/ <typeparam name="T">Type to convert from<\/typeparam>\n \/\/\/ <param name="enumerable"><\/param>\n \/\/\/ <param name="typeName">database type name; if schema is not defi-ned then 'dbo' schema is used by default<\/param>\n \/\/\/ <param name="connection">database connection needed to determine column order<\/param>\n \/\/\/ <param name="transaction">optional transaction if conversion is done inside any<\/param>\n \/\/\/ <returns>Table-Valued Parameter<\/returns>\n public static async Task<SqlMapper.ICustomQueryParameter> AsTableValu-edParameter<T>(this IEnumerable<T> enumerable, string typeName, IDbConnection connection, IDbTransaction? transaction = null)\n {\n \/\/ set type schema and name\n var schema = "dbo";\n var isolatedTypeName = "";\n var typeNameParts = typeName.Replace("[", "").Replace("]", "").Split(".");\n if (typeNameParts.Length == 1)\n {\n isolatedTypeName = typeNameParts[0];\n }\n else if (typeNameParts.Length == 2)\n {\n schema = typeNameParts[0];\n isolatedTypeName = typeNameParts[1];\n }\n else\n {\n throw new ArgumentException($"Argument typeName = {typeName} can have only one dot dividing schema from type name.");\n }\n\n \/\/ query for column names in order\n var typeColumnsQuery = $@"SELECT c.name\n FROM sys.columns c\n join sys.table_types t on t.type_table_object_id = c.object_id\n join sys.schemas s on s.schema_id = t.schema_id\n where s.name = '{schema}'\n and t.[name] = '{isolatedTypeName}'\n order by column_id;";\n\n var orderedColumnNames = await connec-tion.QueryAsync<string>(typeColumnsQuery, transaction: transaction);\n if (orderedColumnNames == null || !orderedColumnNames.Any())\n throw new Exception($"Table type {typeName} returned no co-lumns");\n\n \/\/ create DataTable\n using var dt = new DataTable();\n var props = typeof(T).GetProperties();\n\n \/\/ add columns in specific order\n foreach (string colName in orderedColumnNames)\n {\n var prop = props.SingleOrDefault(p => p.Name == colName);\n if (prop == null)\n dt.Columns.Add(colName);\n else\n dt.Columns.Add(prop.Name, Nulla-ble.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);\n }\n\n \/\/ add row to DataTable for every item\n foreach (var item in enumerable)\n {\n var row = dt.NewRow();\n \/\/ find corresponding prop for every column and add its value or null\n foreach (string colName in orderedColumnNames)\n {\n try\n {\n var prop = props.SingleOrDefault(p => p.Name == colNa-me);\n if (prop == null)\n row[colName] = (object)DBNull.Value;\n else\n {\n var value = prop.GetValue(item, null);\n row[prop.Name] = value ?? (object)DBNull.Value;\n }\n }\n catch (Exception ex)\n {\n throw new Exception($"Exception when assigning value to DataTable column when colName = '{colName}'", ex);\n }\n\n }\n dt.Rows.Add(row);\n }\n \/\/ return TVP\n return dt.AsTableValuedParameter(typeName);\n }\n\n \/\/\/ <summary>\n \/\/\/ This extension converts an object to a 1-row Dapper TVP with co-lumns ordered by T-SQL UDTT columns.\n \/\/\/ Class properties names must coorrestopond to T-SQL UDTT column names.\n \/\/\/ <\/summary>\n \/\/\/ <typeparam name="T">Type to convert from<\/typeparam>\n \/\/\/ <param name="item"><\/param>\n \/\/\/ <param name="typeName">database type name; if schema is not defi-ned then 'dbo' schema is used by default<\/param>\n \/\/\/ <param name="connection">database connection needed to determine column order<\/param>\n \/\/\/ <param name="transaction">optional transaction if conversion is done inside any<\/param>\n \/\/\/ <returns>Table-Valued Parameter<\/returns>\n public static async Task<SqlMapper.ICustomQueryParameter> AsTableValu-edParameter<T>(this T item, string typeName, IDbConnection connection, IDb-Transaction? transaction = null) where T : class\n {\n return await (new List<T> { item }).AsEnumerable().AsTableValuedParameter(typeName, connection, transaction);\n }\n\n \/\/\/ <summary>\n \/\/\/ This extension converts list of objects to a Dapper TVP with co-lumns ordered by T-SQL UDTT columns.\n \/\/\/ Class properties names must coorrestopond to T-SQL UDTT column names.\n \/\/\/ <\/summary>\n \/\/\/ <typeparam name="T">Type to convert from<\/typeparam>\n \/\/\/ <param name="list"><\/param>\n \/\/\/ <param name="typeName">database type name; if schema is not defi-ned then 'dbo' schema is used by default<\/param>\n \/\/\/ <param name="connection">database connection needed to determine column order<\/param>\n \/\/\/ <param name="transaction">optional transaction if conversion is done inside any<\/param>\n \/\/\/ <returns>Table-Valued Parameter<\/returns>\n public static async Task<SqlMapper.ICustomQueryParameter> AsTableValu-edParameter<T>(this List<T> list, string typeName, IDbConnection connection, IDbTransaction? transaction = null)\n {\n return await list.AsEnumerable().AsTableValuedParameter(typeName, connection, transaction);\n }\n\n\n }\n}\n<\/pre><\/div>\n\n\nW powy\u017cszym kodzie u\u017cywamy tylko Dapper<\/em> i System.Data<\/em>. Rzeczy takie jak:<\/p>\n\n\n\n\n- namespace,<\/li>\n\n\n\n
- nazwa klasy,<\/li>\n\n\n\n
- nazwy metod<\/li>\n<\/ul>\n\n\n\n
mo\u017cesz \u015bmia\u0142o zmieni\u0107, je\u015bli chcesz. Nazwa\u0142em metody AsTableValuedParameter<\/em> (tak samo jak oryginalna metoda Dappera), aby jasno pokaza\u0107, \u017ce zwracaj\u0105 ten sam typ, co Dapper.<\/p>\n\n\n\nWarto wspomnie\u0107, \u017ce mamy 3 metody statyczne, z kt\u00f3rych 2 tylko przekazuj\u0105 dane zgodnie z odpowiednim typem. Skupimy si\u0119 wi\u0119c na pierwszej metodzie, kt\u00f3ra zawiera ca\u0142\u0105 logik\u0119.<\/p>\n\n\n\n
Pierwszy parametr to Enumerable<\/em> obiekt\u00f3w zawieraj\u0105cy dane do konwersji. Nast\u0119pnie, musimy poda\u0107 nazw\u0119 UDTT, na kt\u00f3ry chcemy dokona\u0107 konwersji. Mo\u017cna j\u0105 przekaza\u0107 z lub bez schematu lub nawias\u00f3w kwadratowych. Kolejno przekazujemy po\u0142\u0105czenie z baz\u0105 danych. Zostanie ono u\u017cyte do uzyskania kolejno\u015bci kolumn UDTT. Jako ostatni mamy opcjonalny parametr transakcji, kt\u00f3ry mo\u017cna przekaza\u0107, je\u015bli konwersja ma miejsce w ramach dowolnej transakcji.<\/p>\n\n\n\nLogika jest do\u015b\u0107 prosta. Pocz\u0105tkowo ustawiamy zmienne typu string ze schematem i nazw\u0105 UDTT. Nast\u0119pnie wysy\u0142amy zapytanie do bazy danych, aby uzyska\u0107 kolejno\u015b\u0107 kolumn UDTT. Na podstawie wyniku dodajemy kolumny do DataTable<\/em> w odpowiedniej kolejno\u015bci z typem zaczerpni\u0119tym ze znalezionej w\u0142a\u015bciwo\u015bci w modelu C# (lub bez typu, je\u015bli w\u0142a\u015bciwo\u015b\u0107 nie zosta\u0142a znaleziona). P\u00f3\u017aniej dodajemy wiersz do DataTable<\/em> dla ka\u017cdego obiektu z wej\u015bciowego Enumerable<\/em> i przypisujemy warto\u015bci do odpowiednich kolumn na podstawie nazwy (lub null, je\u015bli nie znaleziono w\u0142a\u015bciwo\u015bci). Na koniec wywo\u0142ujemy oryginaln\u0105 metod\u0119 AsTableValuedParameter<\/em> Dappera i zwracamy jej wynik.<\/p>\n\n\n\nJak u\u017cy\u0107 naszych nowych metod? Przede wszystkim pami\u0119taj o dodaniu using DapperTVPOrderedExample.DapperExtensions<\/em>. Nast\u0119pnie mo\u017cesz ich u\u017cy\u0107 bezpo\u015brednio na dowolnym obiekcie, IEnumerable lub Li\u015bcie obiekt\u00f3w w nast\u0119puj\u0105cy spos\u00f3b:<\/p>\n\n\n\nusing var conn = new SqlConnection(connectionString);\nconn.Open();\n\n\/\/ some example data\nList<Project> projects = new()\n{\n new Project { Id = 1, Name = "Name1", ProjectStartDate = DateTimeOffset.Parse("2022-11-01"), Active = true, Draft = false },\n new Project { Id = 2, Name = "Name2", ProjectStartDate = DateTimeOffset.Parse("2022-12-01"), Active = false, Draft = true }\n};\n\n\/\/ create parameters\nvar parameters = new\n{\n projects = await projects.AsTableValuedParameter("[dbo].[udtt_Project]", conn)\n};\n\n\/\/ execute Stored Procedure\nreturn await conn.ExecuteScalarAsync<int>(\n "[dbo].[ProcedureWhichAcceptsProjectsAsTVP]",\n param: parameters,\n commandType: CommandType.StoredProcedure);\n<\/pre><\/div>\n\n\nI to wszystko. Nie musimy si\u0119 ju\u017c martwi\u0107 o:<\/p>\n\n\n\n
\n- odpowiedni\u0105 kolejno\u015b\u0107 kolumn,<\/li>\n\n\n\n
- dodawanie kolumn, kt\u00f3re istniej\u0105 w UDTT, ale nie w modelu C#,<\/li>\n\n\n\n
- pomijanie w\u0142a\u015bciwo\u015bci, kt\u00f3re istniej\u0105 w modelu C#, ale nie w UDTT.<\/li>\n<\/ul>\n\n\n\n
Musimy jednak zwr\u00f3ci\u0107 uwag\u0119 na kilka innych rzeczy:<\/p>\n\n\n\n
\n- nazwy w\u0142a\u015bciwo\u015bci musz\u0105 by\u0107 zgodne z nazwami kolumn UDTT,<\/li>\n\n\n\n
- je\u015bli w UDTT jest dodatkowa kolumna, kt\u00f3rej nie ma w modelu C#, kolumna w TVP zostanie dodana z warto\u015bciami null<\/em> bez \u017cadnego ostrze\u017cenia,<\/li>\n\n\n\n
- ka\u017cda konwersja do TVP spowoduje wys\u0142anie dodatkowego zapytania do bazy danych.<\/li>\n<\/ul>\n\n\n\n
Oczywi\u015bcie, mo\u017cemy dalej rozwija\u0107 ten pomys\u0142<\/strong>, aby wyeliminowa\u0107 te problemy. Mo\u017cemy zaimplementowa\u0107 atrybut dla w\u0142a\u015bciwo\u015bci, kt\u00f3ry b\u0119dzie zawiera\u0142 mapowanie nazw kolumn UDTT, je\u015bli chcemy nazwa\u0107 w\u0142a\u015bciwo\u015bci inaczej. Mamy mo\u017cliwo\u015b\u0107 zaimplementowania log\u00f3w, kt\u00f3re b\u0119d\u0105 ostrzega\u0107, je\u015bli jaka\u015b kolumna UDTT nie zostanie dopasowana do \u017cadnej w\u0142a\u015bciwo\u015bci klasy. Mo\u017cemy r\u00f3wnie\u017c pomy\u015ble\u0107 o cache, je\u015bli do\u015b\u0107 cz\u0119sto u\u017cywamy UDTT w naszej aplikacji, aby zmniejszy\u0107 liczb\u0119 zapyta\u0144 do bazy danych w celu odczytania definicji UDTT.<\/p>\n\n\n\nTo tylko og\u00f3lne pomys\u0142y, kt\u00f3re maj\u0105 na celu zainspirowa\u0107 Ci\u0119<\/strong> do dalszego rozwoju.<\/p>\n\n\n\n![\"oferta](\"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2024\/12\/praca-m-10.jpg\")
<\/a><\/figure>\n\n\n\n<\/div>\n\n\n\nWnioski<\/strong><\/h2>\n\n\n\nWsparcie TVP w Twojej aplikacji mo\u017ce przynie\u015b\u0107 ogromne korzy\u015bci w zakresie wydajno\u015bci. Mam nadziej\u0119, \u017ce automatyzacja tworzenia TVP przedstawiona w \u201edrugim podej\u015bciu\u201d u\u0142atwi Ci nieco prac\u0119.<\/p>\n\n\n\n
Pami\u0119taj, \u017ce chocia\u017c ten przyk\u0142ad u\u017cywa SQL Server\/Azure SQL, mo\u017cesz go u\u017cywa\u0107 z dowolnym innym systemem bazy danych. To tylko kwestia zamiany zapytania, kt\u00f3re odpowiada za uzyskanie kolumn UDTT we w\u0142a\u015bciwej kolejno\u015bci.<\/p>\n\n\n\n
***<\/p>\n\n\n\n
Je\u015bli interesuje Ci\u0119 tematyka SQL lub .Net, polecamy inne artyku\u0142y naszych ekspert\u00f3w m.in.: Analizowanie danych JSON w bazie danych MSSQL za pomoc\u0105 SQL<\/a>, Tabele i kolumny, czy mo\u017ce grafy, dokumenty i mapy, czyli jaki mamy wyb\u00f3r planuj\u0105c bazy danych?<\/a> oraz ML.NET \u2013 uczenie maszynowe w wydaniu Microsoftu.<\/a><\/p>\n\n\n\n***<\/p>\n\n\n\n
Piewsza publikacja artyku\u0142u odby\u0142a si\u0119 23.01.2023.<\/p>\n\n\n
\nCREATE TYPE [dbo].[udtt_Project] AS TABLE\n(\n\t[Id] int NULL,\n\t[Name] nvarchar(4000) NOT NULL,\n\t[ProjectStartDate] DateTimeOffset NULL,\n\t[Active] bit NOT NULL, \n\t[Draft] bit NOT NULL\n)\n<\/pre><\/div>\n\n\nW nast\u0119pnym kroku mo\u017cemy utworzy\u0107 procedur\u0119 sk\u0142adowan\u0105, kt\u00f3ra przyjmie nasz typ tabelaryczny zdefiniowany przez u\u017cytkownika jako parametr.<\/p>\n\n\n
\nCREATE PROCEDURE [dbo].[ProcedureWhichAcceptsProjectsAsTVP]\n\t@projects [dbo].[udtt_Project] readonly\nAS\nBEGIN\n\tSELECT count(*)\n\tfrom @projects;\nEND\n<\/pre><\/div>\n\n\nTo wszystko, co musimy zrobi\u0107 w bazie danych. Jednak zanim b\u0119dziemy mogli u\u017cy\u0107 naszej procedury sk\u0142adowanej, musimy nauczy\u0107 si\u0119, jak przekazywa\u0107 Table-Valued Parameter za pomoc\u0105 Dappera\u2026<\/p>\n\n\n\n
Przekazywanie Table-Valued Parameter z wykorzystaniem Dappera<\/strong><\/h2>\n\n\n\nAby utworzy\u0107 Table-Valued Parameter, musimy wywo\u0142a\u0107 metod\u0119 AsTableValuedParameter<\/em>. Metoda ta konwertuje DataTable<\/em> lub IEnumerable<T> where T : IDataRecord<\/em> na ICustomQueryParameter<\/em>. Skupimy si\u0119 na DataTable<\/em>, poniewa\u017c uwa\u017cam, \u017ce b\u0119dzie \u0142atwiejszy do zrozumienia.<\/p>\n\n\n\nJako programi\u015bci zwykle nie u\u017cywamy tych typ\u00f3w, ale raczej listy okre\u015blonych modeli. Oznacza to, \u017ce aby utworzy\u0107 TVP, musimy przekonwertowa\u0107 nasz\u0105 List\u0119<\/em> na DataTable<\/em>, a nast\u0119pnie wywo\u0142a\u0107 AsTableValuedParameter<\/em>. Zak\u0142adaj\u0105c, \u017ce mamy model C#, kt\u00f3ry dok\u0142adnie reprezentuje nasz typ tabelaryczny (User-Defined Table Type<\/em>), kt\u00f3ry wygl\u0105da nast\u0119puj\u0105co:<\/p>\n\n\n\npublic class Project\n{\n public int? Id { get; set; }\n public string Name { get; set; } = String.Empty;\n public DateTimeOffset? ProjectStartDate { get; set; }\n public bool Active { get; set; }\n public bool Draft { get; set; }\n}\n\n<\/pre><\/div>\n\n\nPierwsza my\u015bl, jaka przychodzi nam do g\u0142owy, to po prostu utworzenie DataTable<\/em>, r\u0119czne dodanie potrzebnych kolumn i wype\u0142nienie ich danymi w nast\u0119puj\u0105cy spos\u00f3b:<\/p>\n\n\n\nusing var conn = new SqlConnection(connectionString);\nconn.Open();\n\n\/\/ some example data\nList<Project> projects = new()\n{\n new Project { Id = 1, Name = "Name1", ProjectStartDate = DateTimeOffset.Parse("2022-11-01"), Active = true, Draft = false },\n new Project { Id = 2, Name = "Name2", ProjectStartDate = DateTimeOffset.Parse("2022-12-01"), Active = false, Draft = true }\n};\n\n\/\/ create DataTable\nDataTable projectsDT = new();\nprojectsDT.Columns.Add(nameof(Project.Id), typeof(int));\nprojectsDT.Columns.Add(nameof(Project.Name), typeof(string));\nprojectsDT.Columns.Add(nameof(Project.ProjectStartDate), typeof(DateTimeOffset));\nprojectsDT.Columns.Add(nameof(Project.Active), typeof(bool));\nprojectsDT.Columns.Add(nameof(Project.Draft), typeof(bool));\n\n\/\/ add rows to DataTable\nforeach (var project in projects)\n{\n var row = projectsDT.NewRow();\n row[nameof(Project.Id)] = project.Id ?? (object)DBNull.Value;\n row[nameof(Project.Name)] = project.Name;\n row[nameof(Project.ProjectStartDate)] = project.ProjectStartDate ?? (object)DBNull.Value;\n row[nameof(Project.Active)] = project.Active;\n row[nameof(Project.Draft)] = project.Draft;\n projectsDT.Rows.Add(row);\n}\n\n\/\/ create parameters\nvar parameters = new\n{\n projects = projectsDT.AsTableValuedParameter("[dbo].[udtt_Project]")\n};\n\n\/\/ execute Stored Procedure\nreturn await conn.ExecuteScalarAsync<int>(\n "[dbo].[ProcedureWhichAcceptsProjectsAsTVP]",\n param: parameters,\n commandType: CommandType.StoredProcedure);\n<\/pre><\/div>\n\n\nTyle wystarczy, aby wszystko zadzia\u0142a\u0142o. Dodatkowo, mo\u017cesz do\u0142\u0105czy\u0107 fragment kodu odpowiedzialny za tworzenie DataTable<\/em> jako metod\u0119 statyczn\u0105 w klasie Project<\/em>, aby ponownie wykorzysta\u0107 go w innych miejscach.<\/p>\n\n\n\nWygenerowany skrypt SQL<\/strong><\/h3>\n\n\n\nSprawd\u017amy teraz, co Dapper wygenerowa\u0142 i przekaza\u0142 do bazy danych. W narz\u0119dziu Profiler widzimy, \u017ce zosta\u0142 wykonany nast\u0119puj\u0105cy skrypt:<\/p>\n\n\n
\ndeclare @p1 dbo.udtt_Project\ninsert into @p1 values(1,N'Name1','2022-11-01 00:00:00 +01:00',1,0)\ninsert into @p1 values(2,N'Name2','2022-12-01 00:00:00 +01:00',0,1)\n\nexec [dbo].[ProcedureWhichAcceptsProjectsAsTVP] @projects=@p1\n<\/pre><\/div>\n\n\nCzy widzisz co\u015b niepokoj\u0105cego? C\u00f3\u017c, wszystkie dane wstawiane do zmiennej @p1<\/em> s\u0105 mapowane porz\u0105dkowo. Instrukcja Insert<\/em> nie okre\u015bla \u017cadnych kolumn, wi\u0119c ca\u0142a operacja opiera si\u0119 na prawid\u0142owej kolejno\u015bci kolumn po stronie DataTable<\/em> w C# oraz User-Defined Table Type<\/em> w SQL.<\/p>\n\n\n\nNale\u017cy zawsze pami\u0119ta\u0107, \u017ce nie ma mapowania wed\u0142ug nazwy kolumny i za ka\u017cdym razem podczas tworzenia DataTable<\/em> powinni\u015bmy zadba\u0107 o:<\/p>\n\n\n\n\n- w\u0142a\u015bciw\u0105 kolejno\u015b\u0107 kolumn,<\/li>\n\n\n\n
- dodawanie kolumn, kt\u00f3re istniej\u0105 w UDTT, ale nie w modelu C#,<\/li>\n\n\n\n
- pomijanie w\u0142a\u015bciwo\u015bci, kt\u00f3re istniej\u0105 w modelu C#, ale nie w UDTT.<\/li>\n<\/ul>\n\n\n\n
Automatyzacja tworzenia TVP<\/strong><\/h2>\n\n\n\nW sumie nauczyli\u015bmy si\u0119 ju\u017c przekazywa\u0107 TVP za pomoc\u0105 Dappera, wi\u0119c co wi\u0119cej mo\u017cemy powiedzie\u0107? C\u00f3\u017c, wyobra\u017a sobie, \u017ce u\u017cywamy w aplikacji wielu r\u00f3\u017cnych UDTT lub cz\u0119sto dochodzi do zmian w ich strukturze podczas developmentu. Jeste\u015bmy wtedy zmuszeni r\u0119cznie zdefiniowa\u0107 struktur\u0119 DataTable<\/em> dla wszystkich UDTT i poprawi\u0107 je, je\u015bli wyst\u0105pi\u0105 jakie\u015b zmiany. Oczywi\u015bcie nie chcemy traci\u0107 czasu na takie rzeczy, dlatego postaramy si\u0119 zautomatyzowa\u0107 proces budowania DataTable<\/em>.<\/p>\n\n\n\nZautomatyzowane tworzenie TVP \u2013 podej\u015bcie numer 1<\/strong><\/h3>\n\n\n\nPierwsza my\u015bl, jaka przychodzi do g\u0142owy, to automatyczne tworzenie DataTable<\/em> na podstawie w\u0142a\u015bciwo\u015bci klasy. Nie b\u0119dziemy musieli tego robi\u0107 za ka\u017cdym razem, gdy pojawi si\u0119 nowy UDTT. Po prostu tworzymy model w j\u0119zyku C# z dok\u0142adnie takimi samymi w\u0142a\u015bciwo\u015bciami i uruchamiamy metod\u0119, kt\u00f3ra generuje DataTable<\/em>. Wygl\u0105da dobrze? Dobra, zaczynamy! <\/p>\n\n\n\nMo\u017cemy zdefiniowa\u0107 metod\u0119 rozszerzaj\u0105c\u0105, kt\u00f3ra konwertuje IEnumerable<T><\/em> na DataTable<\/em> na podstawie w\u0142a\u015bciwo\u015bci zdefiniowanych w T<\/em><\/p>\n\n\n\npublic static DataTable ToDataTable<T>(this IEnumerable<T> enumerable)\n{\n var dataTable = new DataTable();\n var propertyDescriptorCollection = TypeDe-scriptor.GetProperties(typeof(T));\n for (int i = 0; i < propertyDescriptorCollection.Count; i++)\n {\n var propertyDescriptor = propertyDescriptorCollection[i];\n var type = propertyDescriptor.PropertyType;\n\n if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))\n type = Nullable.GetUnderlyingType(type)!;\n\n dataTable.Columns.Add(propertyDescriptor.Name, type);\n }\n var values = new object[propertyDescriptorCollection.Count];\n foreach (T iListItem in enumerable)\n {\n for (int i = 0; i < values.Length; i++)\n {\n values[i] = propertyDescriptorCollec-tion[i].GetValue(iListItem)!;\n }\n dataTable.Rows.Add(values);\n }\n return dataTable;\n}\n<\/pre><\/div>\n\n\ni u\u017cy\u0107 jej do budowania TVP w nast\u0119puj\u0105cy spos\u00f3b:<\/p>\n\n\n
\nusing var conn = new SqlConnection(connectionString);\nconn.Open();\n\n\/\/ some example data\nList<Project> projects = new()\n{\n new Project { Id = 1, Name = "Name1", ProjectStartDate = DateTimeOff-set.Parse("2022-11-01"), Active = true, Draft = false },\n new Project { Id = 2, Name = "Name2", ProjectStartDate = DateTimeOff-set.Parse("2022-12-01"), Active = false, Draft = true }\n};\n\n\/\/ create DataTable\nvar projectsDT = projects.ToDataTable();\n\n\/\/ create parameters\nvar parameters = new\n{\n projects = projectsDT.AsTableValuedParameter("[dbo].[udtt_Project]")\n};\n\n\/\/ execute Stored Procedure\nreturn await conn.ExecuteScalarAsync<int>(\n "[dbo].[ProcedureWhichAcceptsProjectsAsTVP]",\n param: parameters,\n commandType: CommandType.StoredProcedure);\n<\/pre><\/div>\n\n\nTo podej\u015bcie wygl\u0105da obiecuj\u0105co<\/strong>. Nie musimy ju\u017c r\u0119cznie definiowa\u0107 DataTable<\/em>. Wystarczy tylko wywo\u0142a\u0107 metod\u0119 ToDataTable<\/em> na dowolnym IEnumerable<T><\/em>.<\/p>\n\n\n\nNiestety, takie podej\u015bcie ma swoje wady. Pami\u0119tasz, jak Dapper tworzy TVP? Dodaje dane do kolumn w okre\u015blonej kolejno\u015bci, a nie wed\u0142ug nazw kolumn. Je\u015bli si\u0119 nad tym zastanowi\u0107, jest kilka sytuacji, w kt\u00f3rych to podej\u015bcie nie zadzia\u0142a:<\/p>\n\n\n\n
\n- Je\u015bli kolejno\u015b\u0107 w\u0142a\u015bciwo\u015bci modelu C# nie pasuje do kolejno\u015bci kolumn UDTT (mo\u017ce w modelach b\u0119dzie jakie\u015b dziedziczenie, kt\u00f3re zaburzy kolejno\u015b\u0107 itp.?).<\/li>\n\n\n\n
- Je\u015bli w modelu C# jest jaka\u015b dodatkowa w\u0142a\u015bciwo\u015b\u0107, kt\u00f3rej nie ma w UDTT (b\u0119dziemy pr\u00f3bowali przekaza\u0107 za du\u017co kolumn i dostaniemy b\u0142\u0105d).<\/li>\n\n\n\n
- Je\u015bli w UDTT istnieje kolumna, kt\u00f3ra nie istnieje w modelu C# (podanie mniejszej liczby kolumn ni\u017c jest to wymagane zak\u0142\u00f3ca dopasowanie ich w odpowiedniej kolejno\u015bci i mo\u017ce powodowa\u0107 b\u0142\u0105d zgodno\u015bci typu w kolumnach).<\/li>\n<\/ul>\n\n\n\n
Bior\u0105c pod uwag\u0119 powy\u017csze, takie podej\u015bcie b\u0119dzie dzia\u0142a\u0107 tylko wtedy<\/strong>, gdy liczba i kolejno\u015b\u0107 kolumn b\u0119d\u0105 dok\u0142adnie takie same mi\u0119dzy modelem C# oraz UDTT. Oczywist\u0105 zalet\u0105 jest automatyczne tworzenie DataTable<\/em>, jednak uwzgl\u0119dniaj\u0105c wady tego podej\u015bcia, mo\u017cna powiedzie\u0107, \u017ce nie jest ono tak wszechstronne<\/strong>, jak by\u015bmy tego chcieli.<\/p>\n\n\n\nMimo wszystko mo\u017cesz je zastosowa\u0107, je\u015bli w twoim projekcie wszystkie modele C# dok\u0142adnie pasuj\u0105 do UDTT i zadbasz o to, \u017ceby tak pozosta\u0142o. Je\u015bli cho\u0107 troch\u0119 martwi Ci\u0119 b\u0142\u0119dne mapowanie, zapraszam do kolejnej sekcji, w kt\u00f3rej postaramy si\u0119 opracowa\u0107 lepsze rozwi\u0105zanie<\/strong>.<\/p>\n\n\n\nZautomatyzowane tworzenie TVP \u2013 podej\u015bcie numer 2<\/strong><\/h3>\n\n\n\nNasze pierwsze podej\u015bcie mia\u0142o wiele wad, kt\u00f3re musimy wzi\u0105\u0107 pod uwag\u0119 oraz je wyeliminowa\u0107. Jednak, aby zapewni\u0107 prawid\u0142ow\u0105 kolejno\u015b\u0107 kolumn lub uzupe\u0142nia\u0107 brakuj\u0105ce kolumny, musimy zna\u0107 zar\u00f3wno definicj\u0119 modelu w j\u0119zyku C#, jak i UDTT w bazie danych. Spr\u00f3bujemy wi\u0119c odczyta\u0107 definicj\u0119 UDTT z bazy danych i spr\u00f3bowa\u0107 dopasowa\u0107 ka\u017cd\u0105 w\u0142a\u015bciwo\u015b\u0107 do konkretnej kolumny UDTT wed\u0142ug nazwy. Raz na zawsze po\u017cegnamy ba\u0142agan zwi\u0105zany z kolejno\u015bci\u0105 kolumn lub b\u0142\u0119dami wynikaj\u0105cymi ze z\u0142ej liczby kolumn. <\/p>\n\n\n\n
Zobaczmy zatem implementacj\u0119: <\/p>\n\n\n
\nusing Dapper;\nusing System.Data;\n\nnamespace DapperTVPOrderedExample.DapperExtensions\n{\n internal static class DapperExtensions\n {\n \/\/\/ <summary>\n \/\/\/ This extension converts enumerable of objects to a Dapper TVP with columns ordered by T-SQL UDTT columns.\n \/\/\/ Class properties names must correspond to T-SQL UDTT column names.\n \/\/\/ <\/summary>\n \/\/\/ <typeparam name="T">Type to convert from<\/typeparam>\n \/\/\/ <param name="enumerable"><\/param>\n \/\/\/ <param name="typeName">database type name; if schema is not defi-ned then 'dbo' schema is used by default<\/param>\n \/\/\/ <param name="connection">database connection needed to determine column order<\/param>\n \/\/\/ <param name="transaction">optional transaction if conversion is done inside any<\/param>\n \/\/\/ <returns>Table-Valued Parameter<\/returns>\n public static async Task<SqlMapper.ICustomQueryParameter> AsTableValu-edParameter<T>(this IEnumerable<T> enumerable, string typeName, IDbConnection connection, IDbTransaction? transaction = null)\n {\n \/\/ set type schema and name\n var schema = "dbo";\n var isolatedTypeName = "";\n var typeNameParts = typeName.Replace("[", "").Replace("]", "").Split(".");\n if (typeNameParts.Length == 1)\n {\n isolatedTypeName = typeNameParts[0];\n }\n else if (typeNameParts.Length == 2)\n {\n schema = typeNameParts[0];\n isolatedTypeName = typeNameParts[1];\n }\n else\n {\n throw new ArgumentException($"Argument typeName = {typeName} can have only one dot dividing schema from type name.");\n }\n\n \/\/ query for column names in order\n var typeColumnsQuery = $@"SELECT c.name\n FROM sys.columns c\n join sys.table_types t on t.type_table_object_id = c.object_id\n join sys.schemas s on s.schema_id = t.schema_id\n where s.name = '{schema}'\n and t.[name] = '{isolatedTypeName}'\n order by column_id;";\n\n var orderedColumnNames = await connec-tion.QueryAsync<string>(typeColumnsQuery, transaction: transaction);\n if (orderedColumnNames == null || !orderedColumnNames.Any())\n throw new Exception($"Table type {typeName} returned no co-lumns");\n\n \/\/ create DataTable\n using var dt = new DataTable();\n var props = typeof(T).GetProperties();\n\n \/\/ add columns in specific order\n foreach (string colName in orderedColumnNames)\n {\n var prop = props.SingleOrDefault(p => p.Name == colName);\n if (prop == null)\n dt.Columns.Add(colName);\n else\n dt.Columns.Add(prop.Name, Nulla-ble.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);\n }\n\n \/\/ add row to DataTable for every item\n foreach (var item in enumerable)\n {\n var row = dt.NewRow();\n \/\/ find corresponding prop for every column and add its value or null\n foreach (string colName in orderedColumnNames)\n {\n try\n {\n var prop = props.SingleOrDefault(p => p.Name == colNa-me);\n if (prop == null)\n row[colName] = (object)DBNull.Value;\n else\n {\n var value = prop.GetValue(item, null);\n row[prop.Name] = value ?? (object)DBNull.Value;\n }\n }\n catch (Exception ex)\n {\n throw new Exception($"Exception when assigning value to DataTable column when colName = '{colName}'", ex);\n }\n\n }\n dt.Rows.Add(row);\n }\n \/\/ return TVP\n return dt.AsTableValuedParameter(typeName);\n }\n\n \/\/\/ <summary>\n \/\/\/ This extension converts an object to a 1-row Dapper TVP with co-lumns ordered by T-SQL UDTT columns.\n \/\/\/ Class properties names must coorrestopond to T-SQL UDTT column names.\n \/\/\/ <\/summary>\n \/\/\/ <typeparam name="T">Type to convert from<\/typeparam>\n \/\/\/ <param name="item"><\/param>\n \/\/\/ <param name="typeName">database type name; if schema is not defi-ned then 'dbo' schema is used by default<\/param>\n \/\/\/ <param name="connection">database connection needed to determine column order<\/param>\n \/\/\/ <param name="transaction">optional transaction if conversion is done inside any<\/param>\n \/\/\/ <returns>Table-Valued Parameter<\/returns>\n public static async Task<SqlMapper.ICustomQueryParameter> AsTableValu-edParameter<T>(this T item, string typeName, IDbConnection connection, IDb-Transaction? transaction = null) where T : class\n {\n return await (new List<T> { item }).AsEnumerable().AsTableValuedParameter(typeName, connection, transaction);\n }\n\n \/\/\/ <summary>\n \/\/\/ This extension converts list of objects to a Dapper TVP with co-lumns ordered by T-SQL UDTT columns.\n \/\/\/ Class properties names must coorrestopond to T-SQL UDTT column names.\n \/\/\/ <\/summary>\n \/\/\/ <typeparam name="T">Type to convert from<\/typeparam>\n \/\/\/ <param name="list"><\/param>\n \/\/\/ <param name="typeName">database type name; if schema is not defi-ned then 'dbo' schema is used by default<\/param>\n \/\/\/ <param name="connection">database connection needed to determine column order<\/param>\n \/\/\/ <param name="transaction">optional transaction if conversion is done inside any<\/param>\n \/\/\/ <returns>Table-Valued Parameter<\/returns>\n public static async Task<SqlMapper.ICustomQueryParameter> AsTableValu-edParameter<T>(this List<T> list, string typeName, IDbConnection connection, IDbTransaction? transaction = null)\n {\n return await list.AsEnumerable().AsTableValuedParameter(typeName, connection, transaction);\n }\n\n\n }\n}\n<\/pre><\/div>\n\n\nW powy\u017cszym kodzie u\u017cywamy tylko Dapper<\/em> i System.Data<\/em>. Rzeczy takie jak:<\/p>\n\n\n\n\n- namespace,<\/li>\n\n\n\n
- nazwa klasy,<\/li>\n\n\n\n
- nazwy metod<\/li>\n<\/ul>\n\n\n\n
mo\u017cesz \u015bmia\u0142o zmieni\u0107, je\u015bli chcesz. Nazwa\u0142em metody AsTableValuedParameter<\/em> (tak samo jak oryginalna metoda Dappera), aby jasno pokaza\u0107, \u017ce zwracaj\u0105 ten sam typ, co Dapper.<\/p>\n\n\n\nWarto wspomnie\u0107, \u017ce mamy 3 metody statyczne, z kt\u00f3rych 2 tylko przekazuj\u0105 dane zgodnie z odpowiednim typem. Skupimy si\u0119 wi\u0119c na pierwszej metodzie, kt\u00f3ra zawiera ca\u0142\u0105 logik\u0119.<\/p>\n\n\n\n
Pierwszy parametr to Enumerable<\/em> obiekt\u00f3w zawieraj\u0105cy dane do konwersji. Nast\u0119pnie, musimy poda\u0107 nazw\u0119 UDTT, na kt\u00f3ry chcemy dokona\u0107 konwersji. Mo\u017cna j\u0105 przekaza\u0107 z lub bez schematu lub nawias\u00f3w kwadratowych. Kolejno przekazujemy po\u0142\u0105czenie z baz\u0105 danych. Zostanie ono u\u017cyte do uzyskania kolejno\u015bci kolumn UDTT. Jako ostatni mamy opcjonalny parametr transakcji, kt\u00f3ry mo\u017cna przekaza\u0107, je\u015bli konwersja ma miejsce w ramach dowolnej transakcji.<\/p>\n\n\n\nLogika jest do\u015b\u0107 prosta. Pocz\u0105tkowo ustawiamy zmienne typu string ze schematem i nazw\u0105 UDTT. Nast\u0119pnie wysy\u0142amy zapytanie do bazy danych, aby uzyska\u0107 kolejno\u015b\u0107 kolumn UDTT. Na podstawie wyniku dodajemy kolumny do DataTable<\/em> w odpowiedniej kolejno\u015bci z typem zaczerpni\u0119tym ze znalezionej w\u0142a\u015bciwo\u015bci w modelu C# (lub bez typu, je\u015bli w\u0142a\u015bciwo\u015b\u0107 nie zosta\u0142a znaleziona). P\u00f3\u017aniej dodajemy wiersz do DataTable<\/em> dla ka\u017cdego obiektu z wej\u015bciowego Enumerable<\/em> i przypisujemy warto\u015bci do odpowiednich kolumn na podstawie nazwy (lub null, je\u015bli nie znaleziono w\u0142a\u015bciwo\u015bci). Na koniec wywo\u0142ujemy oryginaln\u0105 metod\u0119 AsTableValuedParameter<\/em> Dappera i zwracamy jej wynik.<\/p>\n\n\n\nJak u\u017cy\u0107 naszych nowych metod? Przede wszystkim pami\u0119taj o dodaniu using DapperTVPOrderedExample.DapperExtensions<\/em>. Nast\u0119pnie mo\u017cesz ich u\u017cy\u0107 bezpo\u015brednio na dowolnym obiekcie, IEnumerable lub Li\u015bcie obiekt\u00f3w w nast\u0119puj\u0105cy spos\u00f3b:<\/p>\n\n\n\nusing var conn = new SqlConnection(connectionString);\nconn.Open();\n\n\/\/ some example data\nList<Project> projects = new()\n{\n new Project { Id = 1, Name = "Name1", ProjectStartDate = DateTimeOffset.Parse("2022-11-01"), Active = true, Draft = false },\n new Project { Id = 2, Name = "Name2", ProjectStartDate = DateTimeOffset.Parse("2022-12-01"), Active = false, Draft = true }\n};\n\n\/\/ create parameters\nvar parameters = new\n{\n projects = await projects.AsTableValuedParameter("[dbo].[udtt_Project]", conn)\n};\n\n\/\/ execute Stored Procedure\nreturn await conn.ExecuteScalarAsync<int>(\n "[dbo].[ProcedureWhichAcceptsProjectsAsTVP]",\n param: parameters,\n commandType: CommandType.StoredProcedure);\n<\/pre><\/div>\n\n\nI to wszystko. Nie musimy si\u0119 ju\u017c martwi\u0107 o:<\/p>\n\n\n\n
\n- odpowiedni\u0105 kolejno\u015b\u0107 kolumn,<\/li>\n\n\n\n
- dodawanie kolumn, kt\u00f3re istniej\u0105 w UDTT, ale nie w modelu C#,<\/li>\n\n\n\n
- pomijanie w\u0142a\u015bciwo\u015bci, kt\u00f3re istniej\u0105 w modelu C#, ale nie w UDTT.<\/li>\n<\/ul>\n\n\n\n
Musimy jednak zwr\u00f3ci\u0107 uwag\u0119 na kilka innych rzeczy:<\/p>\n\n\n\n
\n- nazwy w\u0142a\u015bciwo\u015bci musz\u0105 by\u0107 zgodne z nazwami kolumn UDTT,<\/li>\n\n\n\n
- je\u015bli w UDTT jest dodatkowa kolumna, kt\u00f3rej nie ma w modelu C#, kolumna w TVP zostanie dodana z warto\u015bciami null<\/em> bez \u017cadnego ostrze\u017cenia,<\/li>\n\n\n\n
- ka\u017cda konwersja do TVP spowoduje wys\u0142anie dodatkowego zapytania do bazy danych.<\/li>\n<\/ul>\n\n\n\n
Oczywi\u015bcie, mo\u017cemy dalej rozwija\u0107 ten pomys\u0142<\/strong>, aby wyeliminowa\u0107 te problemy. Mo\u017cemy zaimplementowa\u0107 atrybut dla w\u0142a\u015bciwo\u015bci, kt\u00f3ry b\u0119dzie zawiera\u0142 mapowanie nazw kolumn UDTT, je\u015bli chcemy nazwa\u0107 w\u0142a\u015bciwo\u015bci inaczej. Mamy mo\u017cliwo\u015b\u0107 zaimplementowania log\u00f3w, kt\u00f3re b\u0119d\u0105 ostrzega\u0107, je\u015bli jaka\u015b kolumna UDTT nie zostanie dopasowana do \u017cadnej w\u0142a\u015bciwo\u015bci klasy. Mo\u017cemy r\u00f3wnie\u017c pomy\u015ble\u0107 o cache, je\u015bli do\u015b\u0107 cz\u0119sto u\u017cywamy UDTT w naszej aplikacji, aby zmniejszy\u0107 liczb\u0119 zapyta\u0144 do bazy danych w celu odczytania definicji UDTT.<\/p>\n\n\n\nTo tylko og\u00f3lne pomys\u0142y, kt\u00f3re maj\u0105 na celu zainspirowa\u0107 Ci\u0119<\/strong> do dalszego rozwoju.<\/p>\n\n\n\n<\/a><\/figure>\n\n\n\n<\/div>\n\n\n\nWnioski<\/strong><\/h2>\n\n\n\nWsparcie TVP w Twojej aplikacji mo\u017ce przynie\u015b\u0107 ogromne korzy\u015bci w zakresie wydajno\u015bci. Mam nadziej\u0119, \u017ce automatyzacja tworzenia TVP przedstawiona w \u201edrugim podej\u015bciu\u201d u\u0142atwi Ci nieco prac\u0119.<\/p>\n\n\n\n
Pami\u0119taj, \u017ce chocia\u017c ten przyk\u0142ad u\u017cywa SQL Server\/Azure SQL, mo\u017cesz go u\u017cywa\u0107 z dowolnym innym systemem bazy danych. To tylko kwestia zamiany zapytania, kt\u00f3re odpowiada za uzyskanie kolumn UDTT we w\u0142a\u015bciwej kolejno\u015bci.<\/p>\n\n\n\n
***<\/p>\n\n\n\n
Je\u015bli interesuje Ci\u0119 tematyka SQL lub .Net, polecamy inne artyku\u0142y naszych ekspert\u00f3w m.in.: Analizowanie danych JSON w bazie danych MSSQL za pomoc\u0105 SQL<\/a>, Tabele i kolumny, czy mo\u017ce grafy, dokumenty i mapy, czyli jaki mamy wyb\u00f3r planuj\u0105c bazy danych?<\/a> oraz ML.NET \u2013 uczenie maszynowe w wydaniu Microsoftu.<\/a><\/p>\n\n\n\n***<\/p>\n\n\n\n
Piewsza publikacja artyku\u0142u odby\u0142a si\u0119 23.01.2023.<\/p>\n\n\n