Sql2005中对XML类型字段的运用

该文章转载自网络大本营：http://www.xrss.cn/Dev/DotNet/200721010251.Html

在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; }
}

}
}

该文章转载自网络大本营：http://www.xrss.cn/Dev/DotNet/200721010251.Html