Make SQL Queries over HTTP with XMLwith VS.Net Lance Robinsonlancer@nsoftware.com This users guide will go through some of the basic steps that one might take in order send an HTTP request to a SQL Server. The request will instruct the SQL server to return XML, which we will parse and display neatly in a grid.
Requirements
To do this, we'll need SQLXML installed on top of SQL Server 2000. We'll also make use of the WebForm and XMLp components included in the IP*Works! .Net Edition. Click here to download a fully functional free trial of this very useful toolkit. For the purposes of this document, we will assume that IP*Works! .Net Edition has already been installed on the readers computer. This toolkit includes sample code for both VB.Net and C#, but here we will be coding in VB.Net.
The first thing we need to do is understand how to make the http request to the SQL server. The best resource for this information is of course the help documentation for SQLXML itself. Using this documentation, we learn that we must run a SQLXML configuration application to configure SQLXML with IIS, creating a virtual directory. After that we'll see that the request is sent just like any other HTTP request, on port 80, with a query string containing the SQL query, ie:
"SELECT CustomerId, CompanyName FROM Customer"
http://localhost/sql?sql=select%20CustomerId,%20CompanyName%20from%20Customers%20FOR%20XML%20AUTO
The resulting response is basic unvalidated XML which includes the results of the query. Try typing this in your browser to see the output for yourself (replace http://localhost/sql with your own sql virtual directory).
A Form
Lets go ahead and get some GUI out of the way. We'll provide only a very few basic inputs for this demo. Textboxes for the URL to use to access the server, a query string, a user name, and password.
I have changed the security settings of the IIS virtual directory to use basic authentication rather than windows integrated. NTLM authentication will be supported in the very soon to be released (entering Beta in Spr 2002) IP*Works! .Net SSL Edition.
I am using the user name "sa" (system administrator) and the password "" (blank). The "Query" button will simply submit the HTTP request to the server using the WebForm component, receive the information back and parse it to the listview box on the form.
WebForm
We'll use the WebForm component (rather than HTTP) because it can easily and automatically URL encode the query string information.
In order to perform Basic Authentication, set the User and Password properties of the component, ie:
Webform1.User = txtUser.Text
Webform1.Password = txtPassword.Text
Next, I set the encoding property of the WebForm component. This property allows me to select between query string (get), multipart/form data (post), or url encoded (post). Then we'll add the variables required in the query string using the AddFormVar method (syntax: AddFormVar(varname, varvalue)). As we see from the SQLXML documentation, the name of the variable is "sql" and the value of that variable is the query that we actually want to send. Then we'll add "FOR XML AUTO;" to instruct the server how to format the data (for more info on data formatting see the SQLXML documentation).
Webform1.Encoding = nsoftware.IPWorks.WebformEncodings.encQueryString Webform1.AddFormVar("sql", txtQuery.Text & " FOR XML AUTO;")
Webform1.SubmitTo(txtServer.Text)
Now the request has been received by the server and the server sends back the XML response. In order to catch this response, we can use either the Webform Transfer event, or the TransferredData property. Right now, we will use the TransferredData property. This property is ready and available as soon as the SubmitTo method returns.
Parsing
Ok, so we have the XML data - now we will use whatever method to parse this xml and display it in a grid or listview. I am going to demonstrate using the XMLp component to parse this data and split it into a listview.
We will dynamically create as many listview columns as there are search terms. For example, in my example we are searching for CustomerId and CompanyName - so there will be two columns in the resulting listview - CustomerId and CompanyName.
The XML data that comes back from this request will not be valid, but raw XML with no parent nodes. We'll just add our own parent node quite simply by prepending and appending a root element around the data.
'input the xml data received to the xml parser component
'(add "<root>" element: SQLServer does not provide validated XML)
Xmlp1.InputText = "<root>" & Webform1.TransferredData & "</root>"
lvwOutput.Items.Clear()
lvwOutput.Columns.Clear()
lvwOutput.Columns.Add("Number", 50, HorizontalAlignment.Left)
'go to the first element and see what type of data we are getting,
'set up the appropriate column headers
Dim i As Integer
Dim j As Integer
Xmlp1.XPath = "/root/[1]" '[1] is the first child
For i = 1 To Xmlp1.AttrCount
'For every attribute of this first child, we create a matching listview column
lvwOutput.Columns.Add(Xmlp1.AttrName(i), (lvwOutput.Width - 50) / Xmlp1.AttrCount, HorizontalAlignment.Left)
lvwOutput.Columns(lvwOutput.Columns.Count - 1).Text = Xmlp1.AttrName(i)
Next i
'Now, go back to the root
Xmlp1.XPath = "/root"
'And for every single child of the root, extract the info and add it to the listview
For i = 1 To Xmlp1.XChildren
Xmlp1.XPath = "/root/[" & CStr(i) & "]"
lvwOutput.Items.Add(CStr(i))
For j = 1 To Xmlp1.AttrCount
lvwOutput.Items(lvwOutput.Items.Count - 1).SubItems.Add(Xmlp1.AttrValue(j))
Next j
Next i
There are of course many, many ways to parse the data - and many forms that your data might arrive in depending on the query you use. Use any query you like, parse the data any way you prefer. But this simple demonstration shows one easy way to perform SQL queries over HTTP using XML - after you get past the first steps the possibilities are many.
More Information
For information about the author, please contact lancer@nsoftware.com.
For more information about IP*Works! or the WebForm or XMLp components, please visit /n software.
Copyright © 2002, Lance Robinson - All Rights Reserved. For publishing permissions, contact lancer@nsoftware.com.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More ASP.NET Articles
More By aspfree
 developerWorks - FREE Tools!
|
Visit IBM developerWorks to download a free trial version of Lotus Quickr 8.0, which enables collaboration by transforming the way everyday business content such as documents, rich media, photos, and video can be shared. Lotus Quickr makes it faster and easier to share content of all types (not just documents) within virtual teams. It is designed to make it easier to collaborate across organizational boundaries, while continuing to work within the context of familiar desktop applications.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to download IBM DB2 Express-C 9.5, a no-charge version of DB2 Express 9 database server. DB2 Express-C offers the same core data server base features as other DB2 Express editions and provides a solid base to build and deploy applications developed using C/C++, Java, .NET, PHP, and other programming languages.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to download the latest trial version of IBM Data Studio V1.1 at no cost. IBM Data Studio is a comprehensive data management solution that helps you effectively design, develop, deploy and manage your data, databases, and database applications throughout the data management life cycle utilizing a consistent and integrated user interface. Unlike other client-side data management solutions that focus on only one aspect of the application lifecycle or database administration, Data Studio complements the Rational Software Delivery platform, providing unparalleled flexibility for a heterogeneous data server environment across platforms.
FREE! Go There Now!
|
|
|
|
Download a free trial version of IBM Rational Developer for System i V7.1, which provides a complete development environment for traditional i5/OS application development. IBM Rational Developer for System i is a new eclipse-based workstation offering for i5/OS application development that provides a comprehensive Integrated Development Environment for edit/compile/debug of traditional RPG/COBOL/C/C++ i5/OS applications.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to download a free trial of the Rational Host Access Transformation Services (HATS) Toolkit. The HATS toolkit provides a set of plug-ins for the IBM Rational Software Delivery Platform to help you easily extend your legacy applications. HATS makes your 3270 and 5250 applications available as HTML through the most popular Web browsers, while converting your host screens to a Web look and feel and it also enables you to develop new Web, portal, and rich-client applications.
FREE! Go There Now!
|
|
|
|
This webcast outlines the best practices that must be instituted to gain the maximum benefit from SOA while maintaining high quality of service. Whether you are deploying new applications or managing and monitoring your existing infrastructure, learn how you can ensure high quality of services with SOA based solutions from IBM. All registrants who attend this live Web Seminar will receive complimentary access to a white paper titled “Maintaining QoS in an SOA Environment”.
FREE! Go There Now!
|
|
|
|
Regression testing -- in which code is thoroughly tested to ensure that changes have not produced unexpected results -- is an important part of any development process. But many testing environments neglect the terminal-based applications that still form the backbone of many industries. In this tutorial, you'll learn how the Rational Functional Tester Extension for Terminal-Based Applications works with other Rational Functional Tester to help test terminal-based applications quickly and easily.
FREE! Go There Now!
|
|
|
|
Try the latest version of IBM Rational Manual Tester V7.0.1 by downloading a free trial from IBM developerWorks. This manual test authoring and execution tool promotes test step reuse to reduce the impact of software change on testers and business analysts and addresses the needs of teams performing at least a portion of their testing manually.
FREE! Go There Now!
|
|
|
|
Whether you are creating new applications or modifying existing ones, managing integration of new components with traditional z/OS elements is a critical part of building and deploying modern applications. Listen to this webcast to see how IBM can help you optimize your development process using an IDE like Rational Developer for System z that integrates with management tools, such as ClearCase to manage your application development on mainframes.
FREE! Go There Now!
|
|
|
|
With IBM Rational Systems Development Solution, you can deliver products faster with higher quality. Within this kit, Read the “Model Driven Systems Development” white paper to see how to improve product quality and communication. Then check out the rest of the e-Kit to learn more about important topics that can affect the success of any software project through customer examples, tutorials, informative Webcasts, and best practices for designing, building and managing systems. From start to finish, at every stage in your projects, Rational Systems Development Solution can help your company reach its full potential.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
