Accessing SQL Server Using HTTP

Truy cập SQL Server Sử dụng HTTP Bạn có thể truy cập SQL Server bằng cách sử dụng HTTP (Hypertext Transfer Protocol). Điều này cho phép bạn chạy các câu lệnh SQL từ một trình duyệt. Ví dụ, bạn có thể chạy một câu lệnh SELECT trả về XML, và SQL Server sẽ hiển thị các kết quả trong trình duyệt của bạn. Bạn có thể sử dụng báo cáo XPath để xác định vị trí dữ liệu trong XML trả về và sử dụng XSL stylesheets để định dạng XML trả lại. Tôi sẽ chỉ cho bạn cách làm...
Nội dung trích xuất từ tài liệu:
Accessing SQL Server Using HTTP Accessing SQL Server Using HTTPYou can access SQL Server using HTTP (Hypertext Transfer Protocol). This allows youto run SQL statements from a browser. For example, you can run a SELECT statementthat returns XML, and SQL Server will display the results in your browser. You can useXPath statements to locate data in the returned XML, and use XSL stylesheets to formatthe returned XML. Ill show you how to do all of these things in this section.Warning You can even run INSERT, UPDATE, and DELETE statements-but youll need to be careful about restricting the ability to run these types of statements because an errant user could easily wreck your database.Before you can access SQL Server using HTTP, youll need to configure SQL XMLsupport for IIS (Internet Information Server).Configuring SQL XML Support for IISTo configure SQL XML support for IIS, select Start ➣ Programs ➣ Microsoft SQLServer ➣ Configure SQL XML Support in IIS. This starts the IIS Virtual DirectoryManagement for SQL Server console, as shown in Figure 16.6. You use this console todefine a virtual directory through which you access SQL Server via HTTP.Figure 16.6: The IIS Virtual Directory Management for SQL Server consoleTo define a virtual directory, expand the node for your computer using the + icon (Iveexpanded the node for my computer-which is named JMPRICE-DT1-in Figure 16.6).Next, right-click on Default Web Site and select New ➣ Virtual Directory from the pop-up menu. Youll need to set the properties for your virtual directory using the NewVirtual Directory Properties window. This window contains six tabs, the first of which isnamed General, which you use to set your Virtual Directory Name (the name throughwhich you access SQL Server) and Local Path (the actual directory in your computersfile system where you store files, such as XML and XSLT files). Ive set my VirtualDirectory Name to Northwind and my Local Path to F:\Northwind, as shown in Figure16.7.Figure 16.7: Setting the Virtual Directory Name and Local PathWarning The directory you specify for your Local Path must already exist in your computers file system. Create it using Windows Explorer, and then browse to that directory using the Browse button.Next, you use the Security tab to set the details of how to authenticate the user whenaccessing SQL Server. Ive used the sa SQL Server account, as shown in Figure 16.8.Figure 16.8: Setting the authentication detailsWarning In a production system, youll want to use an account that has limited permissions in the database. For example, youll probably want to grant read access only to tables.Next, you use the Data Source tab to set which SQL Server you want to use, along withthe database you want to access. Ive picked the local SQL Server and the Northwinddatabase, as shown in Figure 16.9.Figure 16.9: Setting the data sourceNext, you use the Settings tab to specify the type of access to SQL Server you want toprovide. Check the following boxes: Allow URL Queries (allows direct execution ofSQL statements), Allow Template Queries (allows the use of XML and XSLT files toretrieve and format results from the database), and Allow XPath Queries (allowsexecution of queries with XPath expressions), as shown in Figure 16.10.Figure 16.10: Setting the type of accessWarning In a production system, youll want to restrict access to Allow Template Queries only. That way, users can execute only queries defined in an XML template file.Next, you use the Virtual Names tab to map a database schema, a template directorycontaining XML and XSLT files, or a database object (dbobject) to a path relative to yourvirtual directory. Click the New button and set your Virtual Name to Templates, the Typeto template, and your Path to a subdirectory named Templates in your Northwinddirectory , as shown in Figure 16.11. Youll need to create the Templates folder first.Figure 16.11: Setting the virtual name configurationWarning The Templates subdirectory you specify in your Path must already exist in your computers file system. Create it using Windows Explorer, and then browse to that directory using the ellipsis (…) button to the right of the Path field.Click Save to continue. You wont be changing anything in the Advanced tab, but feelfree to examine it if you want to. Click OK to save your settings across all the tabs. Yournew virtual directory is then created and will appear in the IIS Virtual DirectoryManagement for SQL Server console.Running Direct SQL Statements Using a BrowserIn this section, youll learn how to run direct SQL statements using a browser. Ill beusing Internet Explorer in the examples, but you can use whatever browser you wish.Running SELECT StatementsIn this section, youll see how to run a SELECT statement. For example, point yourbrowser to the following URL, which contains an embedded SELECT statement:http://localhost/Northwind?sql=SELECT+*+FROM+Customers+WHERE+CustomerID+IN+(ALFKI,ANATR)+FOR+XML+AUTO&root=ROOTAs you can see, the SELECT statement in this URL retrieves two rows from theCustomers table. The first part of the URL ishttp://localhost/NorthwindThis contains the name of the server (localhost) and the virtual directory (Northwind).The second part of the URL is?sql=SELECT+*+FROM+Customers+WHERE+CustomerID+IN+(ALFKI,ANATR)+FOR+XML+AUTO&root=ROOTThis contains the embedded SELECT statement. Because URLs dont allow spaces, youuse plus (+) characters instead. The root parameter at the end of the URL supplies a namefor the root element in the XML returned by the SELECT statement; Ive supplied a rootname of ROOT in the previous example, but you can use whatever name you want.Figure 16.12 shows the result of running the SELECT statement in Internet Explorer.Fig ...