Sql2005中对XML类型字段的运用

2022-07-15 80酷酷网 80kuku.com

　　sql2005|xml

在SQL2005中增加了xml类型数据。这样，我们可以将我们应用程序中的实体对象直接保存到数据库中。下次要取的时候就可以直接将XML反序列化成实体对象。对于数据量不是很大的情况下，可以考虑使用。

select * from Employee
where [content].exist('//Age[text()>9000]')=1

此SQL语句中带有xpath的查询，可以找出employee表中content为XML类型列中子节点>9000的所有记录

看一下，运用.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Xml.Serialization;
using System.IO;
namespace DevTest
{
    public partial class Form3 : Form
    {
        SqlConnection cn = new SqlConnection("server=192.168.1.100\\SqlExpress;uid=hpasc;pwd=9637004;database=filemanager");
        public Form3()
        {
            InitializeComponent();
            cn.Open();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            DataTable dt = cn.GetSchema();
            this.dataGridView1.DataSource = dt;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            DataTable dt = cn.GetSchema("Tables",null);
            this.dataGridView1.DataSource = dt;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            //this.dataGridView1.DataSource = cn.GetSchema("IndexColumns", new string[] { "FileManager", "dbo", "Creers" });
            SqlCommand cmd = new SqlCommand();
            List<Employee> Emps = new List<Employee>();
            cmd.CommandText = "insert into Employee values(OID,Name,Age,City,Address,Birthday,Content)";
            XmlSerializer ser = new XmlSerializer(typeof(Employee));

            cmd.Connection = cn;
            cmd.Prepare();
            this.textBox1.AppendText("生成对象时间" + System.DateTime.Now.ToString());
            for(int i=0;i<10000;i++)
            {
                System.IO.MemoryStream ms = new System.IO.MemoryStream();
                Employee emp=new Employee();
                emp.Address="emp.Address"+i.ToString();;
                emp.Age=i;
                emp.Name="Name"+i.ToString();
                emp.OID=Guid.NewGuid();
                emp.Birthday=DateTime.Now.AddHours(-i);
                emp.City="City"+i.ToString();
                ser.Serialize(ms,emp);
                ms.Position = 0;

                StreamReader sr = new StreamReader(ms);
                emp.Content = sr.ReadToEnd();
                sr.Close();
                Emps.Add(emp);
            }
            this.textBox1.AppendText("\r\n结束生成对象时间" + System.DateTime.Now.ToString());
            this.textBox1.AppendText("\r\n开始数据插入" + System.DateTime.Now.ToString());
            foreach(Employee emp in Emps)
            {
                cmd.Parameters.Clear();
                cmd.Parameters.Add("OID", SqlDbType.UniqueIdentifier).Value = emp.OID;
                cmd.Parameters.Add("Name", SqlDbType.NVarChar, 50).Value = emp.Name;
                cmd.Parameters.Add("City", SqlDbType.NVarChar, 50).Value = emp.City;
                cmd.Parameters.Add("Address", SqlDbType.NVarChar, 50).Value = emp.Address;
                cmd.Parameters.Add("Age", SqlDbType.Int).Value = emp.Age;
                cmd.Parameters.Add("Birthday", SqlDbType.DateTime).Value = emp.Birthday;
                cmd.Parameters.Add("Content", SqlDbType.Xml).Value = emp.Content;
                cmd.ExecuteNonQuery();
            }
            this.textBox1.AppendText("\r\n结否数据插入" + System.DateTime.Now.ToString());

        }

        private void button4_Click(object sender, EventArgs e)
        {
             List<Employee> Emps = new List<Employee>();

            XmlSerializer ser = new XmlSerializer(typeof(Employee));
            this.textBox1.AppendText("\r\n开始取出数据对象时间" + System.DateTime.Now.ToString());
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandText = "select Content from Employee";
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds);

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                MemoryStream ms = new MemoryStream();
                StreamWriter sr = new StreamWriter(ms);
                sr.WriteLine(dr[0].ToString());
                sr.Flush();
                ms.Position = 0;
                Employee emp=(Employee)ser.Deserialize(ms);
                Emps.Add(emp);
                sr.Close();
                //Employee emp=(Emp)
            }
            this.textBox1.AppendText("\r\n结束反序列生成对象时间" + System.DateTime.Now.ToString());
            this.dataGridView1.DataSource = Emps;
        }
    }

    [Serializable]
    public class Employee
    {
        Guid _OID;

        public Guid OID
        {
            get { return _OID; }
            set { _OID = value; }
        }

string _Name;

        public string Name
        {
            get { return _Name; }
            set { _Name = value; }
        }
        int _Age;

        public int Age
        {
            get { return _Age; }
            set { _Age = value; }
        }

        string _Address;

        public string Address
        {
            get { return _Address; }
            set { _Address = value; }
        }
        DateTime _Birthday;

        public DateTime Birthday
        {
            get { return _Birthday; }
            set { _Birthday = value; }
        }
        string _City;

        public string City
        {
            get { return _City; }
            set { _City = value; }
        }
        string _Content;

        public string Content
        {
            get { return _Content; }
            set { _Content = value; }
        }

    }
}