The application of XML type fields in Sql2005

Sql2005|xml

XML type data has been added to the SQL2005. In this way, we can save the entity objects in our application directly to the database. The next time you want to take it, you can deserialize the XML directly into an entity object. If the data volume is not very large, you can consider using.

SELECT * FROM Employee
where [Content].exist ('//age[text () >9000] ') =1

A query with XPath in this SQL statement that finds all records in the employee table that have content in the XML type column of the >9000 node

Take a look, use it.

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 ("Generate Object Time" + 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 End Generation Object Time" + System.DateTime.Now.ToString ());
This.textBox1.AppendText ("\ r \ n Start data insertion" + 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 junction data insertion" + 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 Start fetching data Object Time" + 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 End reverse Sequence Generation object Time" + 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;}
}

}
}