First, let's talk about the purpose of our example - to let the database output XML and call it under the .net system.
The data layer of this system uses a SQL server database, and the middle layer can be implemented using the tool "Configuring SQL XML Support in IIS" that comes with SQL. Okay, now we are going to do it. First, configure SQL to output XML:
This thing may sound a bit mysterious, but in fact, it is just adding: FOR XML AUTO after our ordinary query statement.
Give an example:
SELECT TOP 100 topic,name,time
FROM bbs where [order]=1 ORDER BY [Time] DESC
FOR XML AUTO
statement is executed in the SQL query analyzer, you will find that the output is no longer the table we traditionally concept, but several rows of very long strings, and the content is the XML code we need. .
In this way, the first step is to let SQL output XML.
Then we start our second step, so that the XML file can be called using the web.
After debugging successfully, you can create a file: for example, aaa.xml
content is as follows:
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
SELECT TOP 100 topic,name,time
FROM bbs where [order]=1 ORDER BY [Time] DESC
FOR XML AUTO
</sql:query>
</ROOT>
Create a bbb directory in c: and put it in it.
The middle part is our traditional SQL statement, and the front and back encapsulation is our common format.
Put this file in a random directory, then open SQL's "Configure SQL XML support in IIS" and create a virtual directory on the site that requires XML support. Let's call it ccc. The local path is naturally our c: bbb.
Then click Security - enter your SQL username and password, then Data Source, which is your database location and the default database.
Then click Settings, select Allow Template Query, then click Virtual Name, click New Type and call it template name.
ddd, the corresponding file is our aaa.xml.
Then open it in IE: http://your machine name/ccc/ddd
What did you see? Yes, it is the content output by your xml file during SQL query.
What is not output? Open your "Internet Information Services (IIS) Manager" and select "Web Extension Services",
Then select "All unknown ISAP extensions" to allow. Is it OK now?
Okay, now comes our most critical step, step 3 - how to call this dynamically generated XML file in VS.net.
First we need to create an empty unstructured dataset1, and then in the source code this.dataSet1.Locale = new System.Globalization.CultureInfo("zh-CN");
Add this.dataSet1.ReadXml("http://your machine name/ccc/ddd");
Congratulations, you can now use this dynamically generated XML file as a read-only dataset.