以前还真没有见过数据表中存储XML格式的数据,刚开始听说的时候,还以为是数据表中有XML的字段类型,
再了解,其实也就是字符串类型的,只不过字符串的格式是XML格式的。确实孤陋寡闻!汗...
(可添加多个时间)
(xml的格式可以自己定义的)
一、如何存储XML格式的数据到表中:
1、定义相关实体类
/// <summary> /// 营业时间实体 /// </summary> [XmlRoot("r")] public class OpenHoursEntity { /// <summary> /// 营业时间集合 /// </summary> [XmlElement("ts")] public List<OpenHours> OpenHoursList { get; set; } } /// <summary> /// 营业时间 /// </summary> public class OpenHours { /// <summary> /// 营业开始时间 /// </summary> [XmlElement("b")] public string BeginTime { get; set; } /// <summary> /// 营业结束时间 /// </summary> [XmlElement("e")] public string EndTime { get; set; } }
2、保存
/// <summary> /// 生成XML,不包含声明和命名空间 /// </summary> /// <typeparam name="T">对象类型</typeparam> /// <param name="obj">对象</param> /// <returns>xml字符串</returns> public static string SerializeOnlyXml<T>(T obj) { var serializer = new XmlSerializer(typeof(T)); XmlWriterSettings settings = new XmlWriterSettings(); settings.Encoding = new UTF8Encoding(false); settings.OmitXmlDeclaration = true; // 强制指定命名空间,覆盖默认的命名空间。 XmlSerializerNamespaces namespaces = new XmlSerializerNamespaces(); namespaces.Add(string.Empty, string.Empty); var stream = new MemoryStream(); using (XmlWriter writer = XmlWriter.Create(stream, settings)) { serializer.Serialize(writer, obj, namespaces); } return Encoding.UTF8.GetString(stream.ToArray()); }
Table1 model =new Table1();//数据表对应的实体 OpenHoursEntity entity = new OpenHoursEntity();//营业时间实体 entity.OpenHoursList = new List<OpenHours>(); if (!string.IsNullOrEmpty(lblOpeningHours.Text.Trim())) { if (lblOpeningHours.Text.Trim().Contains(",")) { foreach (string item in lblOpeningHours.Text.Trim().Trim(',').Split(',')) { OpenHours openhours = new OpenHours(); openhours.BeginTime = item.Split('~')[0]; openhours.EndTime = item.Split('~')[1]; entity.OpenHoursList.Add(openhours); } } else { OpenHours openhours = new OpenHours(); openhours.BeginTime = lblOpeningHours.Text.Trim().Split('~')[0]; openhours.EndTime = lblOpeningHours.Text.Trim().Split('~')[1]; entity.OpenHoursList.Add(openhours); } }
model.STPESortName="营业时间";
model.STPEExtendName=SerializeOnlyXml<OpenHoursEntity>(entity);
Add(model);//保存到数据库
二、读取数据表中XML格式的数据并显示
/// <summary> /// 将对象序列化为xml字符串 /// </summary> /// <typeparam name="T">对象类型</typeparam> /// <param name="obj">对象</param> /// <returns>xml字符串</returns> public static string Serialize<T>(T obj) { //序列化实体 var serializer = new XmlSerializer(typeof(T)); //注意如果不设置encoding默认将输出utf-16 //注意这儿不能直接用Encoding.UTF8如果用Encoding.UTF8将在输出文本的最前面添加4个字节的非xml内容 var stream = new MemoryStream(); using (XmlWriter writer = XmlWriter.Create(stream, new XmlWriterSettings { Encoding = new UTF8Encoding(false) })) { serializer.Serialize(writer, obj); } return Encoding.UTF8.GetString(stream.ToArray()); }
显示已有的时间:
var obj = TCSmartFramework.Utility.XMLHelper.GetObjFromXML<OpenHoursEntity>(ConvertHelper.ToString(dt.Rows[0]["STPEExtendName"]), Encoding.UTF8); StringBuilder sbTime = new StringBuilder(); for (int i = 0; i < obj.OpenHoursList.Count(); i++) { sbTime.Append(obj.OpenHoursList[i].BeginTime + "~" + obj.OpenHoursList[i].EndTime).Append(','); } lblOpeningHours.Text = sbTime.ToString();