利用XML实现数据的批量查询

 

 using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Xml;

public partial class WebPage_XML_Test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        using (SqlConnection conn = new SqlConnection())
        {
            //将dataSet的数据写入XML文档
            string strConn = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks;Data Source=";
            conn.ConnectionString = strConn;
            SqlCommand com = new SqlCommand();
            com.Connection = conn;
            string strSQL = "select [name] from HumanResources.Department";
            com.CommandText = strSQL;
            SqlDataAdapter da = new SqlDataAdapter(com);
            DataSet ds = new DataSet();
            da.Fill(ds);

            string fileName = @"d:\test.xml";
            ds.WriteXml(fileName);

            //从XML文件抓取数据(也可以直接利用已经存在的DataSet),用GetXml()方法将整个xml数据传入SQL Server 2005
            DataSet dsGetDataFromXML = new DataSet();
            dsGetDataFromXML.ReadXml(fileName);
            com.Parameters.Clear();
            com.CommandText = "SELECT * FROM udf_XML2Table (@myXML)";
            //com.CommandText = "select * from HumanResources.Department where exists(SELECT propAuthor FROM udf_XML2Table (@myXML) where propAuthor=[name])";
            com.Parameters.Add("@myXML", SqlDbType.Xml);
            com.Parameters["@myXML"].Value = dsGetDataFromXML.GetXml().ToString();//用GetXml()方法将整个xml数据传入SQL Server 2005
           
            //验证是否真正成功
            DataSet ds2 = new DataSet();
            SqlDataAdapter da2 = new SqlDataAdapter(com);
            da2.Fill(ds2);
            this.GridView1.DataSource = ds2;
            this.GridView1.DataBind();
        }

 //下面的代码在SQL Server 2005中创建      
//--创建输出行集的用户自定义函数
//create function udf_XML2Table (@xCol xml)
//returns @ret_Table table (propPK int, propAuthor varchar(max))
//with schemabinding
//as
//begin
//      insert into @ret_Table
//      select ROW_NUMBER() OVER(ORDER BY nref.value('.', 'varchar(max)')), nref.value('.', 'varchar(max)')
//      from   @xCol.nodes('////NewDataSet//Table') AS R(nref)
//      return
//end
//go
    }
}