Microsoft SQL Server 2005 Developer’s Guide- P12

Microsoft SQL Server 2005 Developer’s Guide- P12:This book is the successor to the SQL Server 2000 Developer’s Guide, whichwas extremely successful thanks to all of the supportive SQL Server developerswho bought that edition of the book. Our first thanks go to all of the peoplewho encouraged us to write another book about Microsoft’s incredible new relationaldatabase server: SQL Server 2005.
Nội dung trích xuất từ tài liệu:
Microsoft SQL Server 2005 Developer’s Guide- P12 Chapter 6: Developing Database Applications with ADO.NET 219 Here you can see again how the connection object has been passed in at the topof the routine. DataAdapter, DataSet, and CommandBuilder objects are then created.The DataSet is then filled inside the Try-Catch loop. The next statement showsretrieving the last row in the SpecialOffer table into a DataRow object. TheDescription field of the DataRow is then set with a new value, which changes theTable.Rows.RowState property for this row to reflect Modified. The next statementcalls the DataAdapter’s Update method. The Update method determines theappropriate command to execute from the value of the RowState property; in thiscase, it will call the UpdateCommand of the DataAdapter to resolve the changed rowback to the data source.Delete Using the CommandBuilderThe next example shows deleting a record from the database.Private Sub DataSetDeleteSql(cn As SqlConnection) Create the dataadapter, and commandbuilder Dim sqlDA As SqlDataAdapter = New SqlDataAdapter( _ SELECT * FROM Sales.SpecialOffer, cn) Dim ds = New DataSet() Dim sqlCB = New SqlCommandBuilder(sqlDA) Try Populate the dataset sqlDA.Fill(ds, SpecialOffer) Mark the record in the datatable for deletion Dim sqlDR = ds.Tables(SpecialOffer).Rows( _ ds.Tables(SpecialOffer).Rows.Count - 1) sqlDR.Delete() Delete the record from the database table sqlDA.Update(ds, SpecialOffer) Catch e As Exception MsgBox(e.Message) End TryEnd Sub Again you can see the connection object passed into the routine, and theDataAdapter, DataSet, and CommandBuilder objects being created. Then theDataSet is filled in the Try-Catch loop. The next statement retrieves the last rowfrom the SpecialOffer DataTable into a DataRow object. Then the DataRow’sDelete method is called to delete the row from the DataTable SpecialOffer. Inreality, this does not physically delete the row from the DataTable but insteadsets the Table.Rows.RowState property to Deleted. Next, when the DataAdapter’s220 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e Update method is called, the DeleteCommand of the DataAdapter will execute and delete the record from the database. In contrast, if you call the DataTable’s Remove or RemoveAt method, the row will be physically removed from the DataTable in the DataSet. If you use the Remove or RemoveAt method and then call the Update method, the row in the data source will not be deleted, because the DataAdapter’s Update method determines what action to take from the Table. Rows.RowState property and all of the remaining rows in the DataTable have a RowState of Unmodified; therefore, no action will take place at the data source. Summary In this chapter, you got a view of how to develop SQL Server database applications using Visual Basic and ADO.NET. You were introduced to the different ADO.NET namespaces and given an overall understanding of the functions of the different classes that compose the ADO.NET architecture. CHAPTER Developing with XML 7 IN THIS CHAPTER The XML Data Type XQuery Support XML Data Type Methods XML Indexes Using the For XML Clause OPENXML XML Bulk Load Native HTTP SOAP Access 221Copyright © 2006 by The McGraw-Hill Companies. Click here for terms of use.222 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e X ML (Extensible Markup Language) is the lingua franca of computer languages. XML’s flexible text-based structure enables it to be used for an incredibly wide array of network tasks, including for data/document transfer, for Web page rendering, and even as a transport for Web services via SOAP (Simple Object Access Protocol). Microsoft first added basic support for XML to SQL Server 2000, by adding the FOR XML clause as part of the SELECT statement and the OpenXML function. The FOR XML clause allowed a SELECT statement to return an XML document containing the results, while the OpenXML function created a rowset over XML contained in one or more columns. To this basic level, Microsoft’s SQL XML Web release for SQL Server 2000 added support for UpdateGrams, Templates, and BulkLoad to XML Views, as well as stored procedure access via Web services and SOAP. However, SQL Server 2000’s support for XML had some limitations. The XML data needed to be stored in a SQL Server database using either the Text or Image data type. Once it was stored, there was little that SQL Server could do with it. SQL Server 2000 was unable t ...