.net, C# 
2 Comments C# 导入导出Json数据
需求 : 将现有数据库的数据进行导出成Json, —>修改, —-> 将相关数据表清空后立即将修改后的Json文件导入数据表,,其中Json数据的结构不变
实现：
１： 将数据导出 ：
先说表: PictrueSource , PictureGroup, ConfigSourceData
表的结构:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Skylark.Core;
using Skylark.Core.CommonEnums;
namespace Test.Model
{
// 图
public class PictureSource : EntityBase
{
/// <summary>
/// 序号
/// </summary>
public int SerialNumber { get; set; }
/// <summary>
/// 别名
/// </summary>
public string AliasName { get; set; }
/// <summary>
/// 名称
/// </summary>
public string Name { get; set; }
/// <summary>
/// 值
/// </summary>
public string Value { get; set; }
/// <summary>
/// 值名称
/// </summary>
public string ValueName { get; set; }
/// <summary>
/// 图片
/// </summary>
public Guid PictureId { get; set; }
/// <summary>
/// 图所属分组
/// </summary>
public PictureGroup PictureGroup { get; set; }
/// <summary>
/// 分组的外键
/// </summary>
public Guid? PictureGroupId { get; set; }
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Skylark.Core;
namespace Test.Models
{
public class PictureGroup : EntityBase
{
/// <summary>
/// 分组名称
/// </summary>
public string Name { get; set; }
/// <summary>
/// 分组别名
/// </summary>
public string AliasName { get; set; }
/// <summary>
/// 分组序号
/// </summary>
public int SortNumber { get; set; }
/// <summary>
/// 图列表
/// </summary>
// public HashSet<PictureSource> PictureConfig { get; set; }
public List<PictureSource> PictureSource { get; set; }
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Skylark.Core;
namespace Test.Models
{
/// <summary>
/// 配置资源数据
/// </summary>
/// <seealso cref="Skylark.Core.EntityBase" />
public class ConfigSourceData : EntityBase
{
/// <summary>
/// 图片资源
/// </summary>
public List<PictureSource> PictureSources { get; set; }
/// <summary>
/// 图片组资源
/// </summary>
public List<PictureGroup> PictureGroups { get; set; }
}
}
2: 考虑导出:,查出要导出的数据 —-> 导出的位置,—-> 返回的数据
try
{
var dataVm = new ConfigSourceDataVM();
var pictureSources = PictureSourceRepository.Find().ToList().Select( a => new PictureSource
{
Id = a.Id,
AliasName = a.AliasName,
PictureGroupId = a.PictureGroupId,
Name = a.Name,
IsEnable = a.IsEnable,
Value = a.Value,
ValueName = a.ValueName,
SerialNumber = a.SerialNumber,
PictureId = a.PictureId,
CreateOn = a.CreateOn
});
var pictureGroups = PictureGroupRepository.Find().ToList().Select(a => new PictureGroup
{
Id = a.Id,
AliasName = a.AliasName,
Name = a.Name,
IsEnable = a.IsEnable,
SortNumber = a.SortNumber,
CreateOn = a.CreateOn,
});
// 将数据映射到ViewModel(主要是给前端看的model,内容跟Model的内容是一样的)中
dataVm.PictureSources = Mapper.Map<List<PictureSourceVM>>(pictureSources);
dataVm.PictureGroups = Mapper.Map<List<PictureGroupVM>>(pictureGroups);
// 序列化数据 , 写入文件存储起来:
var result = JsonConvert.SerializeObject(dataVm);
string path = @"C:\MallConfigDataJson\ConfigJson.json";
if (!Directory.Exists("C:\\MallConfigDataJson"))
{
Directory.CreateDirectory("C:\\MallConfigDataJson");
}
if (!File.Exists(path))
{
using (StreamWriter sw = File.CreateText(path)){};
}
// 若文件存在,则清空文件的内容
Stream fs = File.Open(path, FileMode.Open);
fs.SetLength(0);
fs.Close();
File.AppendAllText(path, result); // 写入json数据
return FastResponse.Response(HttpStatusCode.OK, result);
}
catch (Exception e)
{
return FastResponse.Response(HttpStatusCode.BadRequest, "导出失败");
}
3: 导入: 接收文件(用post 方式,File类型传过来json文件),读取内容,清空数据表,, 插入数据表
IFormFile file = Request.Form.Files[0];
string aLastName = file.FileName.Substring(file.FileName.LastIndexOf(".") + 1, (file.FileName.Length – file.FileName.LastIndexOf(".") – 1)); //扩展名
if (aLastName != "json")
{
return FastResponse.Response(HttpStatusCode.BadRequest, false);
}
// 读取接收到的文件
StreamReader sr = new StreamReader(file.OpenReadStream(), Encoding.UTF8);
var data = sr.ReadToEnd();
if (string.IsNullOrEmpty(data))
{
return FastResponse.Response(HttpStatusCode.BadRequest, false);
}
if (string.IsNullOrEmpty(data))
{
return FastResponse.Response(HttpStatusCode.BadRequest, false);
}
return Service.ImportSourceConfigData(data);
在ImportSourceConfigData 的Api 中实现删除与插入数据处理
下面的_dbContext需要提前注入一下才可以使用哦,
public Response<bool> ImportSourceConfigData(string data)
{
try
{
_dbContext.Database.ExecuteSqlCommand("DELETE FROM PictureSource");
_dbContext.Database.ExecuteSqlCommand("DELETE FROM PictureGroup");
_dbContext.Database.ExecuteSqlCommand("DELETE FROM ConfigSourceData");
var datavm = JsonConvert.DeserializeObject<ConfigSourceData>(data);
ConfigSourceDataRepository.Insert(datavm);
return FastResponse.Response(HttpStatusCode.OK, true);
}
catch (Exception ex)
{
return FastResponse.Response(HttpStatusCode.BadRequest, false);
}
/*也可以分表插入.但必须注意先后顺序,外键关系
// 组
foreach (var group in datavm.PictureGroups)
{
var groupMap = Mapper.Map<PictureGroup>(group);
PictureGroupRepository.Insert(groupMap);
}
// 图
foreach (var picture in datavm.PictureSources)
{
var pictureMap = Mapper.Map<PictureSource>(picture);
PictureSourceRepository.Insert(pictureMap);
}*/
}
温馨提示 : 我在清空数据表的时候,最开始想用Truncate Table TableName实现，
_dbContext.Database.ExecuteSqlCommand("TRUNCATE TABLE PictureGroup");
_dbContext.Database.ExecuteSqlCommand("TRUNCATE TABLE PictureSource");
_dbContext.Database.ExecuteSqlCommand("TRUNCATE TABLE ConfigSourceData");
最后发现因为有外键关系总是删除不成功，遂去找解决方案： 网上说１： 先禁用表单外键,,再用Truncate 删除,最后再启用外键: 具体的实现代码:
启用与禁用外键的约束: 查看外键状态
alter table PictureSource nocheck constraint all; // 禁用
_dbContext.Database.ExecuteSqlCommand("DELETE FROM PictureSource"); // 删除表数据
alter table PictureSource check constraint all; // 启用
alter table PictureGroup nocheck constraint all; // 禁用
_dbContext.Database.ExecuteSqlCommand("DELETE FROM PictureGroup "); // 删除表数据
alter table PictureGroup check constraint all; // 启用
alter table ConfigSourceData nocheck constraint all; // 禁用
_dbContext.Database.ExecuteSqlCommand("DELETE FROM ConfigSourceData "); // 删除表数据
alter table ConfigSourceData check constraint all; // 启用
select name,is_disabled from sys.foreign_keys // 另外附上查看外键状态的 sql语句
结果: 实测无效
2 : 先删除外键,, 再进行Truncate Table TableName , 最后再添加外键
_dbContext.Database.ExecuteSqlCommand("ALTER TABLE PictureSource DROP CONSTRAINT FK_PictureSource_ConfigSourceData_ConfigSourceDataId,FK_PictureSource_PictureGroup_PictureGroupId");
_dbContext.Database.ExecuteSqlCommand("TRUNCATE TABLE PictureSource");
_dbContext.Database.ExecuteSqlCommand("ALTER TABLE PictureSource ADD CONSTRAINT FK_PictureSource_ConfigSourceData_ConfigSourceDataId FOREIGN KEY(ID) REFERENCES PictureSource(ID)");
_dbContext.Database.ExecuteSqlCommand("ALTER TABLE PictureSource ADD CONSTRAINT FK_PictureSource_PictureGroup_PictureGroupId FOREIGN KEY(ID) REFERENCES PictureSource(ID)");
_dbContext.Database.ExecuteSqlCommand("ALTER TABLE PictureGroup DROP CONSTRAINT FK_PictureGroup_ConfigSourceData_ConfigSourceDataId");
_dbContext.Database.ExecuteSqlCommand("TRUNCATE TABLE PictureGroup");
_dbContext.Database.ExecuteSqlCommand("ALTER TABLE PictureGroup ADD CONSTRAINT FK_PictureGroup_ConfigSourceData_ConfigSourceDataId FOREIGN KEY(ID) REFERENCES PictureGroup(ID)");
_dbContext.Database.ExecuteSqlCommand("TRUNCATE TABLE ConfigSourceData"); // 清除表(此表无外键)
结果 : 实测无效
最后实在受不了了,就用了Delete From TableName
结果 : 实测有效.哈哈
打完收工!!