3 Data Set Data Set XML serialization Binary

Съдържание (3) • Реализация на несвързан модел • Класът Data. Set, силно-типизирани Data. Set-и • XML serialization • Binary serialization (Data. Set. Remoring. Format Property) • Класовете Data. Table(Load and Save Methods) Data. Relation, Data. View и Data. Adapter

Namespace-и на ADO. NET • Пространства от имена от ADO. NET • System. Data – основни архитектурни класове на ADO. NET • System. Data. Common – общи класове за всички data Provider-и • System. Data. Sql. Client и System. Data. Sql. Types – Data Provider класове за достъп до SQL Server • System. Data. Ole. Db – връзка с Ole. DB • System. Data. Odbc – връзка с ODBC • System. Xml – връзка с XML

Еволюция на ADO към ADO. NET ADO Connection Command ADO. NET Xxx. Connection Xxx. Transaction Xxx. Command Data. Set Recordset Xxx. Data. Reader Xxx. Data. Adapter

Компоненти на ADO. NET Disconnected model Data. Set Data. Adapter SQL Server. NET Data Provider Ole. Db. NET Data Provider Connected model Data. Reader Oracle. NET Data Provider Command ODBC. NET Data Provider

Primary Provider Classes and Interfaces in ADO. NET Base Classes Sql. Client Classes Generic Interface Db. Connection Sql. Connection IDb. Connection Db. Command Sql. Command IDb. Command Db. Data. Reader Sql. Data. Reader IData. Reader / IData. Record Db. Transaction Sql. Transaction IDb. Transaction Db. Parameter Sql. Parameter IDb. Data. Parameter Db. Parameter. Collection Sql. Parameter. Collection IData. Parameter. Collection Db. Data. Adapter Sql. Data. Adapter IDb. Data. Adapter Db. Command. Builder Sql. Command. Builder Db. Connection. String. Builder Sql. Connection. String. Builder DBData. Permission Sql. Permission

State. Change – пример private const string CONNECTION_STRING = "Server=. ; " + " Database=Pubs; Integrated Security=true"; private Sql. Connection m. Conn; private void Init. Connection() { m. Conn = new Sql. Connection(CONNECTION_STRING); m. Conn. State. Change += new State. Change. Event. Handler(Conn. State. Change); m. Conn. Open(); } private void Conn. State. Change(object sender, State. Change. Event. Args e) { Debug. Write. Line("SQL Server connection: " + e. Original. State. To. String() + " --> " + e. Current. State. To. String()); }

Info. Message – пример private const string CONNECTION_STRING = "Server=. ; " + "Database=Pubs; Integrated Security=true"; private Sql. Connection m. Conn; private void Init. Connection() { m. Conn = new Sql. Connection(CONNECTION_STRING); m. Conn. Info. Message += new Sql. Info. Message. Event. Handler(Conn. Info. Message); m. Conn. Open(); } private void Conn. Info. Message(object sender, Sql. Info. Message. Event. Args e) { Debug. Write. Line("SQL Server message: " + e. Message + " Source: " + e. Source); }

Класовете в свързана среда Sql. Data. Reader Xml. Reader Sql. Command Sql. Connection Sql. Parameter

Свързан модел – пример using System; using System. Data. Sql. Client; class Test. Sql. Command { private const string CONNECTION_STRING = "Server=. ; " + " Database=pubs; Integrated Security=true"; private const string COMMAND_SELECT_AUTHORS = "SELECT au_fname, au_lname, phone FROM authors"; static void Main() { Sql. Connection con = new Sql. Connection(CONNECTION_STRING); con. Open(); try { (примерът продължава)

Свързан модел – пример Sql. Command command = new Sql. Command(COMMAND_SELECT_AUTHORS, con); Sql. Data. Reader reader = command. Execute. Reader(); using (reader) { while (reader. Read()) { string first. Name = (String) reader["au_fname"]; string last. Name = (String) reader["au_lname"]; string phone = (String) reader["phone"]; Console. Write. Line("{0} {1} - {2}", first. Name, last. Name, phone); } } } finally { con. Close(); } } }

Създаване на Sql. Command • Можете да създадете Sql. Command обект по няколко начина: • Програмно Sql. Command command = new Sql. Command( "SELECT * FROM Products, connection); • От Server Explorer във VS. NET • От Toolbox във VS. NET

Параметрични заявки – пример private void Insert. Shipper(string a. Name, string a. Phone) { Sql. Command cmd. Insert. Shipper = new Sql. Command( "INSERT INTO Shippers(Company. Name, Phone) " + "VALUES (@Name, @Phone)", db. Connection); Sql. Parameter param. Name = new Sql. Parameter("@Name", Sql. Db. Type. NVar. Char); param. Name. Value = a. Name; cmd. Insert. Shipper. Parameters. Add(param. Name); Sql. Parameter param. Phone = new Sql. Parameter("@Phone", Sql. Db. Type. NVar. Char); param. Phone. Value = a. Phone; cmd. Insert. Shipper. Parameters. Add(param. Phone); cmd. Insert. Shipper. Execute. Non. Query(); }

Използване на транзакции • Работа с транзакции в SQL Server: BEGIN TRANSACTION DECLARE @order. Details. Error int, @product. Error int DELETE FROM "Order Details" WHERE Product. ID=42 SELECT @order. Details. Error = @@ERROR DELETE FROM Products WHERE Product. ID=42 SELECT @product. Error = @@ERROR IF @order. Details. Error = 0 AND @product. Error = 0 COMMIT TRANS ELSE ROLLBACK TRANS

Използване на транзакции • Нивото на изолация се дефинира с енумерацията Isolation. Level • Нива на изолация на Sql. Transaction • Read. Uncommited • Read. Commited • Repeatable. Read • Serializable • Пример: Sql. Transaction trans = db. Connection. Begin. Transaction(Isolation. Level. Serializable);

Връзка с други бази от данни Реализирани са базови класове : • Db. Connection • Db. Command • Db. Data. Reader • Db. Transaction • Db. Parameter. Collection • Db. Data. Adapter • Db. Command. Builder • Db. Connection. String. Builder • DBData. Permission

OLE DB Data Provider • Ole. Db. Connection – осъществява връзка с OLE DB източник на данни Ole. Db. Connection db. Conn = new Ole. Db. Connection( @"Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=C: My. DB. mdb; Persist Security Info=False"); • Ole. Db. Command – изпълнява SQL команди върху OLE DB връзка към база данни • Ole. Db. Parameter – параметър на команда • Ole. Db. Data. Reader – за извличане на данни от команда, изпълнена през OLE DB • Ole. Db. Data. Adapter – обменя данни между Data. Set обекти и OLE DB база данни

Връзка с OLE DB – пример • Имаме база данни C: Library. mdb, създадена с MS Access • В нея има таблица Users: • Използваме "Microsoft Jet 4. 0 Provider" за връзка от ADO. NET през OLE DB • Създаваме Connection String чрез Ole. Db. Connection компонентата от Toolbox-a на VS. NET: Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source= C: Library. mdb; Persist Security Info=False

Връзка с OLE DB – пример Ole. Db. Connection db. Conn = new Ole. Db. Connection( @"Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source" + @"=C: Library. mdb; Persist Security Info=False"); db. Conn. Open(); Ole. Db. Command cmd = new Ole. Db. Command( "INSERT INTO Users ([username], [password]) " + "VALUES (@user, @pass)", db. Conn); cmd. Parameters. Add("@user", Ole. Db. Type. Var. Char). Value = "new user name"; cmd. Parameters. Add("@pass", Ole. Db. Type. Var. Char). Value = "secret password"; int affected = cmd. Execute. Non. Query(); Console. Write. Line("{0} records were inserted", affected);

Работа с дати – пример CREATE TABLE Messages ( Msg. Id int identity not null primary key, Msg. Text nvarchar(1000), Msg. Date datetime –- Don’t use varchar for dates! ) public void Add. Msg(string a. Text, Date. Time a. Date) { Sql. Command cmd. Insert. Msg = new Sql. Command( "INSERT INTO Messages(Msg. Text, Msg. Date) " + "VALUES (@Msg. Text, @Msg. Date)", m. Db. Con); Sql. Parameter param. Msg. Date = new Sql. Parameter( "@Msg. Date", Sql. Db. Type. Date. Time); param. Msg. Date. Value = a. Date; cmd. Insert. Msg. Parameters. Add(param. Msg. Date); } cmd. Insert. Msg. Execute. Non. Query();

Модел на данните Data. Set Data. Table 1 Data. Table … 2 1 … 3 2 4 3 4 ……… 2 ……… 3 …… 4 … 1 <? xml version="1. 0" standalone="yes"? > <xs: schema id="Data. Set. Users" target. Namespace="http: //www. tempuri. org/Data. Set. Users. xsd" xmlns: mstns="http: //www. tempuri. org/Data. Set 1. xsd" xmlns: xs="http: //www. w 3. org/2001/XMLSchema" xmlns: msdata="urn: schemas-microsoft-com: xml-msdata" attribute. Form. Default="qualified" element. Form. Default="qualified"> <xs: element name="Data. Set. Users" msdata: Is. Data. Set="true" msdata: Locale="bg-BG"> <xs: complex. Type> <xs: choice max. Occurs="unbounded"> <xs: element name="Users"> <xs: complex. Type> <xs: sequence> <xs: element name="username" type="xs: string" min. Occurs="0" /> <xs: element name="password" type="xs: string" min. Occurs="0" /> </xs: sequence> </xs: complex. Type> </xs: element> </xs: choice> </xs: complex. Type> </xs: element> </xs: schema> XML Schema

Сериализация – пример Data. Set adventure. Data. Set = new Data. Set(); // Fill adventure. Data. Set from the database Populate. Data. Set(ds. Adventure); Binary. Formatter binary. Formatter = new Binary. Formatter(); File. Stream file. Stream = new File. Stream(". . \xml. txt", File. Mode. Create); // Serialize adventure. Data. Set with the default behavior // of Binary. Formatter binary. Formatter. Serialize(file. Stream, adventure. Data. Set); file. Stream. Close(); (примерът продължава)

Сериализация – пример adventure. Data. Set. Remoting. Format = Serialization. Format. Binary; file. Stream = new File. Stream(". . \bin. txt", File. Mode. Create); //Serialize ds. Adventure in binary format binary. Formatter. Serialize(file. Stream, adventure. Data. Set); file. Stream. Close();

Връзка между Data. Table и Data. Reader – пример // Initialize connection and command // … adventure. Works. Connection. Open(); adventure. Works. Data. Reader = cmd. Select. Execute. Reader(); Data. Table currency. Data. Table = new Data. Table("Currency"); // Load table from Data. Reader currency. Data. Table. Load(adventure. Works. Data. Reader); // Use data from table // …

Запазване и зареждане от Data. Table – пример Data. Table person. Data. Table = Create. Data. Table(); Print. Table. Data(person. Data. Table, "Original. Table"); // A text writer to store the data from the table Text. Writer persons. Text. Writer = new String. Writer(); // Write data from the table to the writer person. Data. Table. Write. Xml(persons. Text. Writer, Xml. Write. Mode. Write. Schema); string persons. String = persons. Text. Writer. To. String(); persons. Text. Writer. Close(); (примерът продължава)

Запазване и зареждане от Data. Table – пример Console. Write. Line("XML: n" + persons. String); // A text reader to pass the xml to Read. Xml(. . . ) Text. Reader persons. Text. Reader = new String. Reader(persons. String); Data. Table new. Persons. Data. Table = new Data. Table(); // Load data table with xml data new. Persons. Data. Table. Read. Xml(persons. Text. Reader); persons. Text. Reader. Close(); Print. Table. Data(new. Persons. Data. Table, "New table");

Работа с Data. Table – пример Data. Table tbl = new Data. Table("Authors"); tbl. Columns. Add("au_id", typeof(int)); tbl. Columns. Add("au_fname", typeof(string)); tbl. Columns. Add("au_lname", typeof(string)); tbl. Columns. Add("au_phone", typeof(string)); // The row is detached (not added to the table) Data. Row row = tbl. New. Row(); row[0] = 1; row[1] = "Branimir"; row[2] = "Giurov"; row["phone"] = "+359 2 XXXX"; tbl. Rows. Add(row);

Използване на ограничения (constraints) • Дефиниране на първичен ключ (Primary key) // Single column PK dt. Customers. Primary. Key = new Data. Column() { dt. Customers. Columns("Customer. ID") } // Multiple columns PK dt. Employees. Primary. Key = new Data. Column() { dt. Employees. Columns("Last. Name"), dt. Employees. Columns("First. Name") }

Използване на ограничения (constraints) • Дефиниране на Unique. Constraint и Foreign. Key. Constraint ds. Tables["Product"]. Constraints. Add( new Unique. Constraint("UC_Product. Name", ds. Tables["Product"]. Columns["Product. Name"])); Foreign. Key. Constraint cust. Order. FK = new Foreign. Key. Constraint("Cust. Order. FK", ds. Tables["Cust. Table"]. Columns["Customer. ID"], ds. Tables["Orders. Table"]. Columns["Customer. ID"]); cust. Order. FK. Delete. Rule = Rule. None; // Cannot delete a customer value // that has associated existing orders. ds. Tables["Orders. Table"]. Constraints. Add(cust. Order. FK);

Data. Relation обекти(2) • Съдържат се в: • Relations колекцията на Data. Set обекта • Child. Relations колекцията на Data. Table • Parent. Relations колекцията на Data. Table • Създаване на нова релация ds. Northwind. Relations. Add("FK_Customers. Orders", dt. Customers. Columns["Customer. ID"], dt. Orders. Columns["Customer. ID"], true);

Data. Relation обекти(3) • Get. Child. Rows() – връща редовете наследници на даден ред по име на релация (метод на Data. Row) ds. Northwind. Relations. Add("FK_Customers. Orders", dt. Customers. Columns["Customer. ID"], dt. Orders. Columns["Customer. ID"], true); // Process all customers and their orders foreach (Data. Row dr. Customer in ds. Northwind. Tables["Customers"]. Rows) { foreach (Data. Row dr. Order in dr. Customer. Get. Child. Rows("FK_Customer. Orders")) { // Do some work with the rows } }

Data. View клас(3) • Пример: • Създаване на нов обект • Филтриране по версия на данните Data. Table users. Table =. . . ; Data. View users. View = new Data. View(users. Table); // Show only modified versions of current rows and new rows users. View. Row. State. Filter = Data. View. Row. State. Modified. Current | Data. View. Row. State. Added;

Read. Xml и Write. Xml – пример Data. Set ds. Students = new Data. Set(); // A string that contains the XML data string xml. Student. Data = "<students><student>" + "<name>Petar Petrov</name><fn>12345</fn>" + "</student><student>" + "<name>Ivanov</name><fn>54321</fn>" + "</student></students>"; // A String. Reader to pass the XML to Read. Xml(. . . ) String. Reader sr. Xml. Students = new String. Reader(xml. Student. Data); ds. Students. Read. Xml(sr. Xml. Students, Xml. Read. Mode. Infer. Schema); sr. Xml. Students. Close(); (примерът продължава)

Read. Xml и Write. Xml – пример // A String. Writer to store the data from the Data. Set String. Writer sw. Students = new String. Writer(); ds. Students. Write. Xml(sw. Students, Xml. Write. Mode. Write. Schema); string str. Students = sw. Students. To. String(); sw. Students. Close(); // Print to the console the XML before reading it // in the Data. Set, and the XML produced by the // Data. Set's Write. Xml(. . . ) method Console. Write. Line("XML before: n" + xml. Student. Data + "nnn. XML after: n " + str. Students);

Зареждане на Data. Set от Data. Reader – пример // Initialize connection and command // … adventure. Works. Connection. Open(); adventure. Works. Data. Reader = cmd. Select. Execute. Reader(); adventure. Data. Set = new Data. Set(); // Load table from Data. Reader adventure. Data. Set. Load(adventure. Works. Data. Reader, Load. Option. Upsert, "Currency"); // Use data from data set // …

Data. Adapter – архитектура Select. Command Insert. Command Sql. Data. Adapter Update. Command Delete. Command Sql. Connection

Data. Adapter – пример static void Main() { string str. Con = "Data Source=(local); " + "Integrated Security=SSPI; Database=Northwind"; Sql. Connection cn. Northwind = new Sql. Connection(str. Con); Sql. Command cmd. Select = Create. Select. Command(cn. Northwind); Sql. Data. Adapter da. Employees = new Sql. Data. Adapter(cmd. Select); Data. Set ds. Northwind = new Data. Set(); da. Employees. Fill(ds. Northwind, "Employees"); // Set the Auto. Increment property of Employee. ID column Data. Table employees. Table = ds. Northwind. Tables["Employees"]; Data. Column column. Employee. Id = employees. Table. Columns["Employee. ID"]; column. Employee. Id. Auto. Increment = true; column. Employee. Id. Auto. Increment. Seed = -1; column. Employee. Id. Auto. Increment. Step = -1; (примерът продължава)

Data. Adapter – пример // Create the commands for the data adapter da. Employees. Insert. Command = Create. Insert. Command(cn. Northwind); da. Employees. Delete. Command = Create. Delete. Command(cn. Northwind); da. Employees. Update. Command = Create. Update. Command(cn. Northwind); // Add new record and update the database Data. Row row = employees. Table. New. Row(); row["Last. Name"] = "Ivanov"; row["First. Name"] = "Ivan"; employees. Table. Rows. Add(row); da. Employees. Update(ds. Northwind, "Employees"); Console. Write. Line("Inserted row id={0}. ", row["Employee. ID"]); // Change the added record and update the database row["Last. Name"] = "Petrov"; da. Employees. Update(ds. Northwind, "Employees"); Console. Write. Line("Updated the row. "); (примерът продължава)

Data. Adapter – пример // Delete the added record and update the database row. Delete(); da. Employees. Update(ds. Northwind, "Employees"); Console. Write. Line("Deleted the row. "); } static Sql. Command Create. Select. Command(Sql. Connection a. Connection) { string str. Select = "SELECT Employee. ID, Last. Name, First. Name" + " FROM Employees"; Sql. Command cmd. Select = new Sql. Command(str. Select, a. Connection); return cmd. Select; } static Sql. Command Create. Insert. Command(Sql. Connection a. Connection) { string str. Insert = "INSERT Employees(Last. Name, First. Name) "+ "VALUES(@Last. Name, @First. Name); " + "SET @Employee. ID=Scope_Identity()"; (примерът продължава)

Data. Adapter – пример Sql. Command cmd. Insert = new Sql. Command(str. Insert, a. Connection); Sql. Parameter. Collection cparams = cmd. Insert. Parameters; Sql. Parameter emp. ID = cparams. Add("@Employee. ID", Sql. Db. Type. Int, 0, "Employee. ID"); emp. ID. Direction = Parameter. Direction. Output; cparams. Add("@Last. Name", Sql. Db. Type. NVar. Char, 20, "Last. Name"); cparams. Add("@First. Name", Sql. Db. Type. NVar. Char, 10, "First. Name"); return cmd. Insert; } static Sql. Command Create. Update. Command(Sql. Connection a. Connection) { string str. Update = "UPDATE Employees SET " + "Last. Name=@Last. Name, First. Name=@First. Name " + "WHERE Employee. ID=@Employee. ID"; Sql. Command cmd. Update = new Sql. Command(str. Update, a. Connection); Sql. Parameter. Collection cparams = cmd. Update. Parameters; (примерът продължава)

Data. Adapter – пример Sql. Parameter emp. ID = cparams. Add("@Employee. ID", Sql. Db. Type. Int, 0, "Employee. ID"); emp. ID. Source. Version = Data. Row. Version. Original; cparams. Add("@Last. Name", Sql. Db. Type. NVar. Char, 20, "Last. Name"); cparams. Add("@First. Name", Sql. Db. Type. NVar. Char, 10, "First. Name"); return cmd. Update; } static Sql. Command Create. Delete. Command(Sql. Connection a. Connection) { string str. Delete = "DELETE FROM Employees " + "WHERE Employee. ID = @Employee. ID"; Sql. Command cmd. Delete = new Sql. Command(str. Delete, a. Connection); Sql. Parameter emp. ID = cmd. Delete. Parameters. Add( "@Employee. ID", Sql. Db. Type. Int, 0, "Employee. ID"); emp. ID. Source. Version = Data. Row. Version. Original; return cmd. Delete; }

Data. Set. Get. Changes() – пример if(! my. Data. Set. Has. Changes(Data. Row. State. Modified)) return; // Create temporary Data. Set variable Data. Set modified. Data. Set; // Get. Changes for modified rows only modified. Data. Set = my. Data. Set. Get. Changes( Data. Row. State. Modified); // Check the Data. Set for errors if(modified. Data. Set. Has. Errors) { // Insert code to resolve errors } // After fixing errors, update the data source // with the Data. Adapter used to create the Data. Set adp. Update(modified. Data. Set);

Несвързан модел – типичен сценарий на работа 4. Прилагаме направените промени и разрешаваме конфликтите user. Data. Adapter. Row. Updated += new Sql. Row. Updated. Event. Handler(On. Row. Updated); private void On. Row. Updated(object sender, Sql. Row. Updated. Event. Args e) { // Handle the conflict … е. Status = Update. Status. Continue; } user. Data. Adapter. Update(ds. Changes); 5. Зареждаме отново Data. Set-а от базата данни, за да работим с актуални данни user. Data. Adapter. Fill(ds. Users);

Класът Sql. Xml - пример // Initialize connection and command // … // Load data from database adventure. Works. Connection. Open(); adventure. Works. Data. Reader = select. Command. Execute. Reader(); // Read the xml value in each row and print it while(adventure. Works. Data. Reader. Read()) { sales. Sql. Xml = adventure. Works. Data. Reader. Get. Sql. Xml(0); sales. Xml. Reader = sales. Sql. Xml. Create. Reader(); // Print the content of the xml // … }

Xml. Data. Document – пример Data. Set my. Data. Set = new Data. Set(); // … Fill the Data. Set … Xml. Data. Document xml. Doc = new Xml. Data. Document(my. Data. Set); // Get all elements with Product. ID = 43 Xml. Node. List node. List = xml. Document. Element. Select. Nodes("descendant: : Customers[*/Order. Details/ Product. ID=43]"); foreach (Xml. Node my. Node in node. List) { Data. Row customer = xml. Doc. Get. Row. From. Element( (Xml. Element)my. Node); Console. Write. Line(customer["Company. Name"]); }

Страниране на данни (4) • Страниране на данни от SQL 6. 5, 7, 2000. - Временна таблица • • CREATE PROCEDURE Get. Products. Paged @start. Row int, @End. Row int AS CREATE TABLE #Temp ( ID int IDENTITY PRIMARY KEY, Product. ID /* here goes PK type */ ) • INSERT INTO #Temp SELECT Product. ID FROM Table ORDER BY [Criteria that leaves Product. ID monotonically increasing] • SELECT p. * FROM Products p JOIN #Temp temp ON p. Product. ID = temp. Product. ID ORDER BY temp. ID • WHERE ID > @Start. Row AND ID < @End. Row • GO

Страниране на данни (5) • Страниране на данни от SQL 6. 5, 7, 2000. - SET ROWCOUNT – оператор • CREATE PROCEDURE Get. Products. Paged • @last. Product. ID int, • @page. Size int • AS • SET ROWCOUNT @page. Size • • • SELECT * FROM Products WHERE [standard search criteria] AND Product. ID > @last. Product. ID ORDER BY [Criteria that leaves Product. ID monotonically increasing] GO

Страниране на данни (6) • Страниране на данни от SQL 6. 5, 7, 2000. - Използване на подзаявка • CREATE PROCEDURE Get. Products. Paged • @start. Row int, • @End. Row int • AS • SELECT * FROM Products WHERE Product. ID IN • (SELECT TOP @Page. Size Product. ID FROM Product WHERE Product. ID NOT IN • (SELECT TOP @Start. Row Product. ID FROM Product ORDER BY [Criteria. . . ]) • ORDER BY [Criteria that leaves Product. ID monotonically increasing] • GO

Страниране на данни (7) • Използване на курсор • • DECLARE @PK /* PK Type */ DECLARE @tbl. PK TABLE ( PK /* PK Type */ NOT NULL PRIMARY KEY ) • • DECLARE Paging. Cursor CURSOR DYNAMIC READ_ONLY FOR SELECT @PK FROM Table ORDER BY Sort. Column • • OPEN Paging. Cursor FETCH RELATIVE @ Start. Row FROM Paging. Cursor INTO @PK • • • WHILE @Page. Size > 0 AND @@FETCH_STATUS = 0 BEGIN INSERT @tbl. PK(PK ) VALUES(@PK) @tbl. PK(PK) FETCH NEXT FROM Paging. Cursor INTO @PK SET @Page. Size = @Page. Size - 1 END • • CLOSE Paging. Cursor DEALLOCATE Paging. Cursor • • SELECT. . . FROM Table JOIN @ tbl. PK temp ON Table. PK = temp. PK ORDER BY Sort. Column

Страниране на данни (8) • Страниране на данни от SQL 2005 CREATE PROCEDURE Get. Products. Paged @start. Row. Number int, @page. Size int AS WITH Numbered. Projects AS ( SELECT ROW_NUMBER() OVER (order by Product. ID) as Row, * FROM Products) SELECT* FROM Numbered. Projects WHERE (Row BETWEEN @start. Row. Number AND @start. Row. Number + @page. Size)) ORDER BY [Criteria that leaves Product. ID monotonically increasing] GO

Lecture Topic Questions?