DataTable和Xml互相转化

需求
sql表查询得到的DataTable要转化成XML，就顺便写个测试的例子，
实现的功能
利用反射实现了DataTable，实体对象，XML的互转。
达到的效果

Git代码：https://git.oschina.net/dingxiaowei/XMLConvertor.git
在线查看代码:https://git.oschina.net/dingxiaowei/XMLConvertor#git-readme
Code：

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],[Gender]) VALUES ( 2,N'李四',23,[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; }
        }
    }
    #endregion

using 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,"倪莹莹",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,Gender = true });
                list1.Add(new Student() { Id = 101,Name = "倪莹莹",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();
        }
    }
}

DataTable和Xml互相转化

猜你在找的XML相关文章