Vizuální studio profilování SQL dotazů. Dynamická konstrukce dotazů. Implementace mapování databáze

V okně Server Explorer, kde jsou databázové tabulky na Microsoft SQL Server prezentovány ve formě stromu (viz obr. 4.1.), otevřete okno pro každou tabulku kliknutím na tlačítko Show Table Data. V tomto okně můžete editovat data tabulky. Toto editační okno vypadá přibližně jako na obr. 4.2. Přibližně proto, že do obrázku nebyl zahrnut celý objem kvůli velkému počtu sloupců.

1. Implementace mapování databáze

Mapování databáze (komponenta DataSet) je nezbytné pro eliminaci zbytečných volání do databáze a na server. To je velmi důležitá vlastnost, protože pokud s databází pracuje mnoho uživatelů, může být zatížení serveru poměrně velké - počet požadavků je mnohonásobně větší, což zpomaluje vyřizování požadavků.

Klientský počítač má tedy kvůli DataSetu zobrazení databáze, se kterou pracuje. Po dokončení práce jsou databáze klienta a serveru synchronizovány (nebo spíše změny provedené v databázi klienta jsou provedeny v databázi serveru)

Mapování naší databáze na MS Visual Studio 2005 vypadá takto:

Všechna číselná data jsou typu Int32, řetězcová data jsou String. Velikosti dat řetězce odpovídají velikostem uvedeným ve fyzickém databázovém modelu.

Snímek obrazovky ukazuje, že každá tabulka má také TableAdapters. Jedná se o jakési mosty mezi DataSet a zdrojem dat. Existují metody jako Fill a GetData()

Tato metoda je navržena tak, aby naplnila tabulku potřebnými daty ze zdroje (databázového souboru)

Metoda GetData().

Navrženo tak, aby přebíralo data z tabulky zobrazení.

1. Implementace softwaru

Hlavním rysem implementace softwaru je skutečnost, že všechny třídy zodpovědné za úpravu databázových tabulek jsou zděděny z jedné základní třídy, což usnadňuje implementaci. Metody této základní třídy byly popsány výše. Virtuální metody základní třídy musí být přepsány v podřízené třídě. Všechny tyto metody umožňují v dítěti měnit takové parametry jako: název hlavičky formuláře, vyplnění a uložení požadované databázové tabulky (jedna podřízená třída pracuje pouze s jednou tabulkou, ve smyslu editace dat; při zobrazení však data uživateli, je možné použít několik databázových tabulek pro zobrazení dat uživatelsky přívětivým způsobem).

1. Rozhraní programu

Hlavní okno je aplikace MDI, která má následující nabídku:

Každá z tabulek pro editaci databáze je vyvolána příslušnou položkou menu "Tabulky".

Hlášení o vytížení učitelů se také vytvoří kliknutím na příslušnou položku nabídky "Výkazy".

Jako doplňková funkce softwaru je zde položka menu "Konzola", která umožňuje provádět SQL dotazy do databáze zadané uživatelem a zobrazit výsledek v tabulce (komponenta DataTable). K tomu samozřejmě potřebujete znát názvy tabulek a polí a byl vytvořen především za účelem pohodlí při implementaci softwaru, ale mohou jej používat i další „zasvěcení“ uživatelé.

Anotace: Po absolvování této přednášky budete schopni: vytvářet dotazy pomocí SQL Server Management Studio Query Builder získávat databázové informace z tabulek databázového systému dynamicky vytvářet jednoduché dotazy na základě uživatelského vstupního formátu uživatelského vstupu a filtrovat složité dynamické dotazy analyzovat a přeformátovat data pro použití ve filtru chraňte databázi před útoky „SQL-injection“, k předání dotazu použijte proceduru sp_executeSql

V předchozí přednášce jsme mluvili o tom, jak zvýšit výkonžádosti. Nyní víte, jak vytvořit efektivní sadu dotazů, která uživatelům poskytne nejužitečnější informace z vaší aplikace pomocí předem vytvořených dotazů v uložených procedurách nebo pohledech.

Ve všech aplikacích kromě těch nejjednodušších však není možné předem znát všechny možné typy informací, které by uživatelé mohli chtít, a jak je mohou chtít filtrovat a organizovat. Místo toho, abyste se snažili poskytnout všechny takové možnosti, můžete dát uživateli kontrolu nad informacemi, které aplikace hlásí. Tato přednáška vysvětluje, jak dynamicky vytvářet dotazy na základě voleb, které uživatel provede během pracovního postupu.

Uživatelské rozhraní pro vytváření dotazů

SQL Server Management Studio obsahuje sofistikované rozhraní pro vytváření dotazů. Pojďme prozkoumat toto rozhraní, abyste získali představu o tom, jak lze dynamicky vytvářet dotazy. Vaše aplikace nebude potřebovat vše řízení poskytuje SQL Server Management Studio. Ve skutečnosti musíte pečlivě zvážit, jak nejlépe omezit volby uživatelů.

Vytvoření dotazu pomocí SQL Server Management Studio Query Builder

Načítání informací o databázových tabulkách

Aby aplikace mohla poskytnout uživateli seznam možností, může potřebovat extrahovat informace o tabulkách databáze. Existuje několik způsobů, jak tyto informace získat. Nejdůležitější z těchto metod je použití schématu INFORMATION_SCHEMA. Toto schéma je standardní v každé databázi.

Pomocí INFORMATION_SCHEMA

Schéma INFORMATION_SCHEMA je speciální schéma, které se nachází v každé databázi. Obsahuje definice některých databázových objektů.

INFORMAČNÍ SCHÉMA odpovídá standardu ANSI, který je navržen pro získávání informací z libovolného databázového stroje vyhovujícího ANSI. V SQL Server se INFORMATION_SCHEMA skládá ze sady pohledů, které se dotazují na databázové tabulky sys* obsahující informace o struktuře databáze. Tyto tabulky lze přímo dotazovat, stejně jako jakékoli jiné databázové tabulky. Ve většině případů je však pro načtení informací z tabulek *sys lepší použít pohledy schématu INFORMATION_SCHEMA.

Poznámka. Schéma INFORMATION_SCHEMA se někdy dotazuje na tabulky, které nejsou potřeba, což je škodlivé pro výkon. V dalším příkladu této kapitoly to není nijak zvlášť důležité, protože aplikace již čekala na vstup uživatele. To by však mělo být zváženo, pokud je rychlost důležitým hlediskem pro vaši aplikaci.

Zde je základní kód T-SQL, který se používá k získání informací o sloupcích obsažených v tabulce:

Všimněte si, že musíte vybrat pole TABLE_SCHEMA, abyste získali schéma pro tabulku. To může být užitečné pro vytváření podobných dotazů v budoucnu. Chcete-li experimentovat s metodami popsanými v této kapitole, vytvořte nový projekt v sadě Visual Studio.

Vytvořte nový projekt sady Visual Studio

1. Vyberte z nabídky Start příkazy Všechny programy, Microsoft Visual Studio 2005, Microsoft Visual Studio 2005.
2. Z nabídky sady Visual Studio vyberte příkazy Soubor, Nový, Projekt (Soubor, Vytvořit, Projekt).
3. V panelu Typy projektu rozbalte uzel Visual Basic (Řešení jazyka Visual Basic) a vyberte šablonu aplikace v panelu Šablony. Pojmenujte projekt Chapter7 a klepněte na OK.
4. Aplikaci pro tento příklad lze nalézt v ukázkových souborech ve složce \Chapter7\DynQuery. Můžete vyjmout a vložit kód pro následující postupy ze souboru Form1.vb.

Získání seznamu tabulek a zobrazení

Obvykle budete muset uživateli umožnit vybrat nejen sloupce, ale také tabulku pro dynamický dotaz, takže budete muset uživateli zobrazit seznam tabulek. Požadované informace lze získat pomocí následujícího dotazu:

VYBERTE TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE Z INFORMAČNÍHO SCHÉMATU.TABLES

V aplikaci lze tento dotaz použít následovně.

Získání seznamu tabulek

Výše uvedený kód jazyka Visual Basic inicializuje objekt SqlCommand s názvem com s řetězcem SQL, který se má provést, a poté provede objekt SqlCommand. Toto je nejjednodušší způsob, jak provést příkaz T-SQL z aplikace.

Jako cvičení můžeme schémata a tabulky načtené z procedury Load našeho formuláře umístit do uživatelského rozhraní aplikace jako formulář, aby si uživatel mohl vybrat schéma a tabulku, se kterou bude pracovat. V příkladu v této kapitole předpokládáme, že uživatel vybral schéma Prodej a tabulku Zákazník.

Jakmile uživatel vybere tabulku, lze seznam sloupců pro tuto tabulku načíst stejnou metodou s použitím vstupu uživatele jako názvu tabulky v dotazu. Chcete-li to provést, zadejte do řetězce dotazu proxy a potom nahraďte tento proxy voláním String.Format . V níže uvedeném kódu je zástupný symbol v řetězci dotazu (0).

Získání seznamu sloupců

1. Přidejte následující proceduru RetrieveColumns do kódu pod procedurou RetrieveTables:

   Sub RetrieveColumns(ByVal TableName As String) MyConnection As New SqlClient.SqlConnection(_ "Zdroj dat=.\SQLExpress;" & _ "Initial Catalog=AdventureWorks;Trusted_Connection=Ano;") Dim sqlStr As String sqlStr = "SELECT TABLE_NAME , COLUMN_NAME, " + _ "ORDINAL_POSITION, DATA_TYPE " + _ "FROM INFORMATION_SCHEMA.COLUMNS " + _ "WHERE (TABLE_NAME = "(0)")" Dim tableColumns jako nový DataTable Dim da jako nový SqlClient.SqlDataFormat(_ String (sqlStr, TableName), MyConnection) da.Fill(tableColumns) For i As Integer = 0 To tableColumns.Rows.Count - 1 With tableColumns.Rows.Item(i) Console.WriteLine("(0) (1) (2 )", _ .Item(1), .Item(2), .Item(3)) End With Next End Sub

2. V proceduře Form1_Load přidejte následující volání do procedury RetrieveColumns za proceduru RetrieveTables:

Příklad vytvoření lokální databáze Microsoft SQL Server vMS Visual Studio

Toto téma ukazuje řešení problému vytvoření databáze typu SQL Server pomocí MS Visual Studio. Zvažují se následující otázky:

• práce s oknem Server Explorer v MS Visual Studio;
• vytvoření lokální databáze typu SQL Server Database;
• vytváření tabulek v databázi;
• editace struktur tabulek;
• vzájemné propojení databázových tabulek;
• zadávání dat do tabulek pomocí MS Visual Studio .

Úkol

Pomocí nástrojů MS Visual Studio vytvořte databázi typu MS SQL Server s názvem Education. Databáze obsahuje dvě tabulky Student a Session. Tabulky jsou propojeny nějakým polem.

Struktura první tabulky "Student".

Struktura druhé tabulky „Relace“.

Výkon

1. Stáhněte si MS Visual Studio.

2. Aktivujteoknoprůzkumník serveru.

Pro práci s databázemi nabízí Microsoft odlehčený databázový server Microsoft SQL Server. Existují různé verze Microsoft SQL Server, například: Microsoft SQL Server 2005, Microsoft SQL Server 2008, Microsoft SQL Server 2014 a další.

Tyto verze si můžete stáhnout z webu společnosti Microsoft www.msdn.com.

Tento server je skvělý pro práci s databázemi. Je zdarma a má grafické rozhraní pro vytváření a správu databází pomocí SQL Server Management Tool.

Před vytvořením databáze musíte nejprve aktivovat obslužný program Server Explorer. K tomu je třeba v MS Visual Studio zavolat (obr. 1)

Rýže. 1. Zavolejte Server Explorer

Po volání bude mít okno Průzkumníka serveru přibližný vzhled, jak je znázorněno na obrázku 2.

Rýže. 2. Okno Server Explorer

3. Vytvoření databáze "Vzdělávání".

Chcete-li vytvořit novou databázi založenou na poskytovateli dat Microsoft SQL Server, klikněte na uzel Datová připojení a poté vyberte „ Vytvořit novou databázi SQL Server...“ (obr. 3).

Rýže. 3. Volání příkazu pro vytvoření databáze SQL Server

V důsledku toho okno " Vytvořte novou databázi SQL Server» (obr. 4).

Okno (v poli "Název serveru") ukazuje název místního serveru nainstalovaného na vašem počítači. V našem případě je toto jméno „SQLEXPRESS“.

Pole "Název nové databáze:" určuje název databáze, která má být vytvořena. V našem případě je tento název Vzdělávání.

Možnost Použít ověřování systému Windows musí zůstat nezměněna a klepněte na tlačítko OK.

Rýže. 4. Vytvoření nové databáze SQL Server 2008 Express pomocí MS Visual Studio 2010

Po provedených akcích bude mít okno Průzkumníka serveru podobu, jak je znázorněno na obrázku 5. Jak je patrné z obrázku 5, databáze Education s názvem

Rýže. 5. Okno Server Explorer po přidání databáze Education

4. Vzdělávací databázové objekty.

Pokud rozbalíte databázi Education (znak „+“), uvidíte seznam následujících hlavních objektů:

• Databázové diagramy - databázové diagramy. Diagramy zobrazují vztahy mezi tabulkami databáze, vztahy mezi poli v různých tabulkách atd.;
• Tabulky - tabulky, ve kterých jsou umístěna data databáze;
• Pohledy – pohledy. Rozdíl mezi pohledy a tabulkami je v tom, že databázové tabulky obsahují data, zatímco pohledy na data nikoli, a obsah je vybírán z jiných tabulek nebo pohledů;
• Uložené procedury - uložené procedury. Jedná se o skupinu souvisejících příkazů SQL, které poskytují další flexibilitu při práci s databází.

5. Tvorba tabulky Student.

V tuto chvíli je databáze Education zcela prázdná a neobsahuje žádné objekty (tabulky, uložené procedury, pohledy atd.).

Chcete-li vytvořit tabulku, musíte vyvolat kontextovou nabídku (kliknutím pravým tlačítkem myši) a vybrat příkaz „ Přidat novou tabulku“ (obrázek 6).

Rýže. 6. Příkaz pro přidání nové tabulky

Existuje další možnost pro přidání databázové tabulky pomocí příkazů nabídky Data:

Rýže. 7. Alternativní možnost přidání nové tabulky

V důsledku toho se otevře okno pro přidání tabulky, které obsahuje tři sloupce (obrázek 8). V prvním sloupci „Název sloupce“ je třeba zadat název odpovídajícího pole databázové tabulky. Do druhého sloupce "Datový typ" je potřeba zadat datový typ tohoto pole. Třetí sloupec „ Allow Nulls “ označuje možnost možnosti absence dat v poli.

Rýže. 8. Nové okno pro vytvoření tabulky

Pomocí tabulkového editoru musíte vytvořit tabulku Student, jak je znázorněno na obrázku 9. Při zavření tabulky je nutné zadat její název.

V editoru tabulek můžete nastavit vlastnosti pole v okně Vlastnosti sloupce. Chcete-li nastavit délku řetězce (nvchar) ve znacích, má okno Vlastnosti sloupce vlastnost Délka. Výchozí hodnota pro tuto vlastnost je 10.

Rýže. 9. Studentský stůl

Dalším krokem je nastavení pole klíče. To se provede zavoláním příkazu „Set Primary Key“ z kontextové nabídky pole Num_book. Propojení mezi tabulkami bude vytvořeno pomocí klíčového pole. V našem případě je klíčové pole číslo knihy záznamů.

Rýže. 10. Nastavení pole klíče

Po nastavení primárního klíče bude okno tabulky vypadat jako na obrázku 11.

Rýže. 11. Stůl Student po závěrečné formaci

Nyní můžete stůl zavřít. V okně pro uložení tabulky je potřeba zadat její název - Student (obr. 12).

Rýže. 12. Zadání názvu tabulky Student

6. Vytvořte tabulku relací.

Po vzoru vytvoření tabulky Student se vytvoří tabulka Session.

Obrázek 13 ukazuje pohled na tabulku relace po konečné formaci. Primární klíč se nastavuje v poli Num_book. Název tabulky je dán Session.

Rýže. 13. Tabulka relací

Po provedených akcích se v okně Server Explorer zobrazí dvě tabulky Student a Session.

Do databáze tak lze přidat libovolný počet tabulek.

7. Editace struktury tabulek.

Jsou chvíle, kdy potřebujete změnit strukturu databázové tabulky.

Chcete-li provést změny databázových tabulek v MS Visual Studio, musíte nejprve zrušit zaškrtnutí možnosti „Zabránit ukládání změn“. které vyžadují opětovné vytvoření tabulky” jak je znázorněno na obrázku 14. V opačném případě MS Visual Studio zablokuje změny v dříve vytvořené tabulce. Okno Možnosti zobrazené na obrázku 14 se vyvolá z nabídky Nástroje v následujícím pořadí:

Rýže. 14. Možnost “ Zabránit ukládání změn, které vyžadují opětovné vytvoření tabulky”

Po přizpůsobení můžete změnit strukturu tabulky. K tomu slouží příkaz „Otevřít definici tabulky“ (obrázek 15) z místní nabídky, který se vyvolá pro vybranou tabulku (kliknutím pravým tlačítkem myši).

Rýže. 15. Vyvolání příkazu „Otevřít definici tabulky“.

Tento příkaz se také nachází v nabídce Data:

Nejprve je třeba vybrat tabulku.

8. Vytvoření vazeb mezi tabulkami.

V souladu se stavem problému jsou tabulky propojeny polem Num_book.

Chcete-li vytvořit vztah mezi tabulkami, musíte nejprve (obrázek 16):

• vyberte objekt Diagram databáze;
• vyberte příkaz Přidat nový diagram z místní nabídky (nebo z nabídky Data).

Rýže. 16. Volání příkazu pro přidání nového diagramu

V důsledku toho se otevře okno pro přidání nového grafu Přidat tabulku (obrázek 17). V tomto okně musíte vybrat dvě tabulky Session a Student za sebou a kliknout na tlačítko Přidat.

Rýže. 17. Okno pro přidávání tabulek do diagramu

Rýže. 18. Tabulky Student a Session po jejich přidání do diagramu

Chcete-li začít vytvářet vztah mezi tabulkami, musíte kliknout na pole Num_book tabulky Student a poté (bez uvolnění tlačítka myši) jej přetáhnout do pole Num_book tabulky Session.

V důsledku toho se postupně otevřou dvě okna: Tabulky a sloupce (obr. 19) a Vztah cizích klíčů (obr. 20), ve kterých je potřeba nechat vše tak, jak je, a volbu potvrdit tlačítkem OK.

V okně Tabulky a sloupce se nastavuje název vztahu (FK_Session_Student ) a názvy nadřazených (Student) a podřízených tabulek.

Rýže. 19. Okno Tabulky a sloupce

Rýže. 20. Okno vlastností vztahu

Po provedených akcích se nastaví vztah mezi tabulkami (obrázek 21).

Rýže. 21. Vztah mezi tabulkami Student a Session

Uložení grafu se provádí úplně stejným způsobem jako uložení tabulky. Název diagramu musíte zvolit podle svého uvážení (například Diagram1).

Po nastavení názvu diagramu se otevře okno Uložit, ve kterém je potřeba potvrdit vaši volbu (obrázek 22).

Rýže. 22. Potvrzení uložení změn v tabulkách

9. Zadávání dat do tabulek.

Systém Microsoft Visual Studio umožňuje přímo zadávat data do databázových tabulek.

V našem případě byla při navazování spojení (obr. 19) zvolena primární (Primary Key Table) tabulka Student. Nejprve tedy musíte zadat data do buněk této konkrétní tabulky. Pokud se pokusíte nejprve zadat data do tabulky Session, systém takový vstup zablokuje výstupem příslušné zprávy.

Pro vyvolání režimu zadávání dat v tabulce Student je potřeba vyvolat příkaz Zobrazit data tabulky z místní nabídky (pravým tlačítkem myši) nebo z nabídky Data (obr. 23).

Rýže. 23. Příkaz Zobrazit data tabulky

Otevře se okno, do kterého je potřeba zadat vstupní údaje (obr. 24).

Rýže. 24. Zadání údajů do tabulky Student

Po zadání dat do tabulky Student je potřeba zadat data do tabulky Session.

Při zadávání dat do pole Num_book tabulky Session musíte zadat přesně stejné hodnoty, které jsou zadány do pole Num_book tabulky Student (protože tato pole spolu souvisí).

Pokud jsou například hodnoty zadané do pole Num_book tabulky Student “101”, “102”, “103” (viz obr. 24), pak by tyto hodnoty měly být zadány do pole Num_book tabulky Session. Pokud se pokusíte zadat jinou hodnotu, systém zobrazí přibližně následující okno (obr. 25).

Rýže. 25. Tabulky týkající se chybových zpráv při zadávání dat Student a Session

Tabulka Session se zadanými údaji je na obrázku 26.

V tomto článku se dozvíte, co je LINQ a jak pomocí něj dotazovat seznamy.

Co je LINQ? Language Integrated Query (LINQ) je integrovaný dotazovací jazyk, nebo spíše kontroverzní projekt Microsoftu, který přidává syntaxi dotazovacího jazyka podobnou SQL. Specifická definice, tento nástroj umožňuje relativně snadné dotazování tabulek a seznamů dat, ne nutně databáze.

V příkladech Microsoftu chybí jeden malý, ale důležitý detail. Pro práci se sadou dat potřebujeme použít strukturu DataContext, jejíž definici lze získat pomocí SPMetal.exe pro seznam nebo knihovnu přítomnou na serveru Sharepoint. Jinými slovy, nejprve musíme definovat tento datový typ, a poté vytvořit dotaz.Obecně doporučuji pro práci v MVS použít speciální utilitu: Imtech Get SPMetal Definition Extension .

Abychom mohli pracovat, potřebujeme nějaký seznam. Použijme jednoduchý seznam – „vlastní seznam“; název - "Jednoduché odvolání"; výchozí pole: Číslo, Jméno.

A tak si vytvoříme jednoduchý (viz příslušná stránka), s jehož pomocí budeme pozorovat výsledek našich manipulací. Při vytváření projektu zvolíme izolovaný model důvěry, pokud je potřeba pracovat se seznamy nebo daty z jiných uzlů, pak je nutné vytvořit projekt pro farmu.

Dále vytvoříme soubor třídy "SimpleReversal.cs" pomocí SPMetal.exe nebo pluginu pro studio. Přidejme odkaz na knihovnu Microsoft.Sharepoint.Linq. Více informací naleznete na webu MSDN.

Vytvořme funkci Render a my_mess. V poslední funkci vytvoříme data pro zobrazení.

// přetížení funkce
chráněné přepsání void Render (HtmlTextWriter Writer)
{
základ .Render(writer);
my_mess(spisovatel);
}

// Funkce našeho seznamu
public void my_mess (spisovatel HtmlTextWriter)
{
// Vytvořte kontext
DataContext data = new DataContext("http://localhost" );
// Přidružte jej k odpovídajícímu seznamu na webu v kořenovém adresáři
Seznam entit<Элемент>Simple_Message = data.GetList<Элемент>("Jednoduchá manipulace");
// Proveďte dotaz - vyberte všechny řádky ze seznamu "Jednoduchý přístup".
var query = from mess in Simple_Message
vybrat zprávu;
// zobrazí všechna data z výsledku dotazu
foreach (prvek var v dotazu)
{
spisovatel.WriteLine( "Položka seznamu:"+ elem.Name.ToString());
spisovatel.WriteBreak();
}

}

Zkompilujeme projekt a přidáme jej na stránku. V důsledku toho se zobrazí všechny řádky v seznamu.

Pokračování později (článek nedokončen)...