DataTable和Xml互相转化
需求
Sql表查询得到的DataTable要转化成XML，就顺便写个测试的例子，
实现的功能
利用反射实现了DataTable，实体对象，XML的互转。
达到的效果
Git代码：https://git.oschina.net/dingxiaowei/XMLConvertor.git
在线查看代码:https://git.oschina.net/dingxiaowei/XMLConvertor#git-readme
Code：
- sql
if exists (select * from sysobjects where id = OBJECT_ID('[T_Students]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [T_Students]
CREATE TABLE [T_Students] (
[Id] [bigint] IDENTITY (1, 1) NOT NULL,
[Name] [nvarchar] (10) NOT NULL,
[Age] [int] NOT NULL,
[Gender] [bit] NOT NULL DEFAULT (0))
ALTER TABLE [T_Students] WITH NOCHECK ADD CONSTRAINT [PK_T_Students] PRIMARY KEY NONCLUSTERED ( [Id] )
SET IDENTITY_INSERT [T_Students] ON
INSERT [T_Students] ([Id],[Name],[Age],[Gender]) VALUES ( 1,N'张三',13,1)
INSERT [T_Students] ([Id],[Name],[Age],[Gender]) VALUES ( 2,N'李四',23,1)
INSERT [T_Students] ([Id],[Name],[Age],[Gender]) VALUES ( 3,N'王五',22,0)
SET IDENTITY_INSERT [T_Students] OFF- Student实体类
#region Student数据模型
public class Student
{
private Int64 id;
/// <summary>
/// id
/// </summary>
public Int64 Id
{
get { return id; }
set { id = value; }
}
private string name;
/// <summary>
/// 姓名
/// </summary>
public string Name
{
get { return name; }
set { name = value; }
}
private int age;
/// <summary>
/// 年龄
/// </summary>
public int Age
{
get { return age; }
set { age = value; }
}
private bool gender;
/// <summary>
/// 性别
/// </summary>
public bool Gender
{
get { return gender; }
set { gender = value; }
}
}
#endregionusing System;
using System.IO;
using System.Xml.Linq;
using System.Xml.Serialization;
namespace GX
{
public static class Extensions
{
public static string AttrbuteValue(this XElement e, XName name)
{
if (e != null)
{
var a = e.Attribute(name);
if (a != null)
return a.Value;
}
return null;
}
}
/// <summary>
/// Xml序列化和反序列化
/// </summary>
public class XmlUtil
{
#region 反序列化
/// <summary>
/// 反序列化
/// </summary>
/// <param name="type">类型</param>
/// <param name="xml">XML字符串</param>
/// <returns></returns>
public static object Deserialize(Type type, string xml)
{
try
{
using (StringReader sr = new StringReader(xml))
{
XmlSerializer xmldes = new XmlSerializer(type);
return xmldes.Deserialize(sr);
}
}
catch (Exception e)
{
Console.WriteLine(e);
return null;
}
}
/// <summary>
/// 反序列化
/// </summary>
/// <param name="type"></param>
/// <param name="xml"></param>
/// <returns></returns>
public static object Deserialize(Type type, Stream stream)
{
XmlSerializer xmldes = new XmlSerializer(type);
return xmldes.Deserialize(stream);
}
#endregion
#region 序列化
/// <summary>
/// 序列化
/// </summary>
/// <param name="type">类型</param>
/// <param name="obj">对象</param>
/// <returns></returns>
public static string Serializer(Type type, object obj)
{
MemoryStream Stream = new MemoryStream();
XmlSerializer xml = new XmlSerializer(type);
try
{
//序列化对象
xml.Serialize(Stream, obj);
}
catch (InvalidOperationException)
{
throw;
}
Stream.Position = 0;
StreamReader sr = new StreamReader(Stream);
string str = sr.ReadToEnd();
sr.Dispose();
Stream.Dispose();
return str;
}
#endregion
}
}
- 主程序
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Xml.Linq;
namespace XMLTest
{
class Program
{
/// <summary>
/// 将DataTable转化成实体类模型
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dt"></param>
/// <returns></returns>
public static List<T> ConvertToList<T>(DataTable dt) where T : new()
{
// 定义集合
List<T> ts = new List<T>();
// 获得此模型的类型
Type type = typeof(T);
//定义一个临时变量
string tempName = string.Empty;
//遍历DataTable中所有的数据行
foreach (DataRow dr in dt.Rows)
{
T t = new T();
// 获得此模型的公共属性
PropertyInfo[] propertys = t.GetType().GetProperties();
//遍历该对象的所有属性
foreach (PropertyInfo pi in propertys)
{
tempName = pi.Name;//将属性名称赋值给临时变量
//检查DataTable是否包含此列（列名==对象的属性名）
if (dt.Columns.Contains(tempName))
{
// 判断此属性是否有Setter
if (!pi.CanWrite) continue;//该属性不可写，直接跳出
//取值
object value = dr[tempName];
//如果非空，则赋给对象的属性
if (value != DBNull.Value)
pi.SetValue(t, value, null);
}
}
//对象添加到泛型集合中
ts.Add(t);
}
return ts;
}
static IEnumerable<XAttribute> GetAttrbute(DataRow tableRow)
{
string[] nameArray = new[] { "ID", "Name", "Age", "Gender" };
for (int i = 0; i < 4; i++)
{
yield return new XAttribute(nameArray[i], tableRow[i]);
}
}
static string ConvertToXMLFromTable(List<Student> stuList)
{
return GX.XmlUtil.Serializer(typeof(List<Student>), stuList);
}
static void Main(string[] args)
{
string connstr = "server=127.0.0.1;database=Student;uid=sa;pwd=123456";
string sql = " select * from [Student].[dbo].[T_Students]";
using (SqlConnection conn = new SqlConnection(connstr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
DataTable dt = ds.Tables[0];
Console.WriteLine("*****************************************************************************");
Console.WriteLine("数据库到DataTable到Xml");
var stuList = ConvertToList<Student>(dt);
foreach (var stu in stuList)
{
Console.WriteLine("id:" + stu.Id + ",name:" + stu.Name + ",age:" + stu.Age + ",genger:" + stu.Gender);
};
//将Student的数据集合写入到xml
var stuListStr = ConvertToXMLFromTable(stuList);
Console.WriteLine(stuListStr);
//实体类对象转化到xml
Student stu1 = new Student() { Id = 101, Name = "丁小未", Age = 24, Gender = true };
string xml = GX.XmlUtil.Serializer(typeof(Student), stu1);
Console.WriteLine("*****************************************************************************");
Console.WriteLine("xml转化成实体对象");
Console.WriteLine(xml);
//xml转化成实体对象
Student stu2 = GX.XmlUtil.Deserialize(typeof(Student), xml) as Student;
Console.WriteLine("*****************************************************************************");
Console.WriteLine("xml转化成实体对象");
Console.WriteLine("id:" + stu2.Id + ",name:" + stu2.Name + ",age:" + stu2.Age + ",genger:" + stu2.Gender);
//DataTable转化成Xml
DataTable dt1 = new DataTable("StudentTable");
//添加列属性
dt1.Columns.Add("Id", typeof(Int64));
dt1.Columns.Add("Name", typeof(string));
dt1.Columns.Add("Age", typeof(int));
dt1.Columns.Add("Gender", typeof(bool));
//添加行
dt1.Rows.Add(1, "丁小未", 25, true);
dt1.Rows.Add(2, "倪莹莹", 25, false);
Console.WriteLine("*****************************************************************************");
Console.WriteLine("DataTable对象转化成xml");
xml = GX.XmlUtil.Serializer(typeof(DataTable), dt1);
//xml转化到DataTable
DataTable dt2 = GX.XmlUtil.Deserialize(typeof(DataTable), xml) as DataTable;
Console.WriteLine("*****************************************************************************");
Console.WriteLine("xml转化成DataTable");
foreach (DataRow dr in dt2.Rows)
{
foreach (DataColumn col in dt2.Columns)
{
Console.Write(dr[col].ToString() + " ");
}
Console.Write("\r\n");
}
//List转化到xml
List<Student> list1 = new List<Student>();
list1.Add(new Student() { Id = 101, Name = "丁小未", Age = 24, Gender = true });
list1.Add(new Student() { Id = 101, Name = "倪莹莹", Age = 24, Gender = false });
Console.WriteLine("*****************************************************************************");
Console.WriteLine("List转化成DataTable");
xml = GX.XmlUtil.Serializer(typeof(List<Student>), list1);
Console.WriteLine(xml);
//Xml转化到List
List<Student> list2 = GX.XmlUtil.Deserialize(typeof(List<Student>), xml) as List<Student>;
Console.WriteLine("*****************************************************************************");
Console.WriteLine("Xml转化到List");
foreach (Student stu in list2)
{
Console.WriteLine(stu.Name + "," + stu.Age.ToString());
}
}
Console.Read();
}
}
}