public Resp_Binary Import_Data(string fileName, int userId)
{
var targetFile = new FileInfo(fileName);
if (!targetFile.Exists)
{
return new Resp_Binary {
message = "导入的数据文件未保存成功"
}
}
;
var excelFile = new ExcelQueryFactory(fileName);
excelFile.AddMapping <LayoutExModel>(x => x.CarNumber, "车牌号*");
excelFile.AddMapping <LayoutExModel>(x => x.ValideTime, "有效期");
excelFile.AddMapping <LayoutExModel>(x => x.Channel, "行政通道*");
excelFile.AddMapping <LayoutExModel>(x => x.Degree, "有效次数*");
excelFile.AddMapping <LayoutExModel>(x => x.TriggerType, "进出场控制*");
excelFile.AddMapping <LayoutExModel>(x => x.Description, "布控说明");
DateTime?dtN = null;
var excelContent = excelFile.Worksheet <LayoutExModel>(0);
//var sheetList = excelFile.GetWorksheetNames();
var layouts = new List <Layout>();
foreach (var row in excelContent)
{
if (string.IsNullOrEmpty(row.CarNumber))
{
return(new Resp_Binary {
message = "车牌号不允许为空"
});
}
var layout = new Layout
{
CarNumber = row.CarNumber,
ValideTime = DateTime.TryParse(row.ValideTime, out DateTime dt) ? dt : dtN,
SysUserId = userId,
Channel = row.Channel,
Degree = Enum.TryParse(row.Degree, out Layout_Degree result) ? (int)result : 1,
TriggerType = Enum.TryParse(row.TriggerType, out Layout_TriggerType result2) ? (int)result2 : 0,
CreateTime = DateTime.Now,
IsValid = 1,
Description = row.Description
};
layouts.Add(layout);
}
BulkOperation <Layout> .MySqlBulkInsert(layouts, _repository);
return(new Resp_Binary {
flag = 1, message = "导入成功"
});
}
public Resp_Binary Add_Batch(IEnumerable <LayoutDTO> models)
{
if (models.Count() < 5000)
{
BulkOperation <Layout> .MySqlBulkInsert(models.GetPrototype <LayoutDTO, Layout>(), _repository);
}
else
{
BulkOperation <Layout> .MySqlBulkInsertAsync(models.GetPrototype <LayoutDTO, Layout>(), _repository);
}
return(Resp_Binary.Add_Sucess);
}
public Resp_Binary Add_Batch(IEnumerable <RecordManagerDTO> models)
{
if (models.Count() < 5000)
{
BulkOperation <RecordManager> .MySqlBulkInsert(models.GetPrototype <RecordManagerDTO, RecordManager>(), _repository);
}
else
{
BulkOperation <RecordManager> .MySqlBulkInsertAsync(models.GetPrototype <RecordManagerDTO, RecordManager>(), _repository);
}
_repository.DelRepeatRecord();
return(Resp_Binary.Add_Sucess);
}
public void ReplaceTable(DataTable table)
{
try
{
using (MySqlConnection conn = new MySqlConnection(connString))
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM Giraffe"; //Delete old table
cmd.ExecuteNonQuery();
Console.WriteLine("Table deleted.");
}
using (MySqlConnection conn = new MySqlConnection(connString))
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
List <string> columns = GetColumnNames(conn);
columns.RemoveAt(0); //Remove 'Id' column
using (var bulk = new BulkOperation(conn))
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
bulk.DestinationTableName = "Giraffe";
bulk.BulkInsert(table);
}
Console.WriteLine("New Table inserted.");
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public Resp_Binary Import_Data(string fileName, int userId)
{
var targetFile = new FileInfo(fileName);
if (!targetFile.Exists)
{
return new Resp_Binary {
message = "导入的数据文件未保存成功"
}
}
;
var excelFile = new ExcelQueryFactory(fileName);
excelFile.AddMapping <RecordManager>(x => x.CarNumber, "车牌号*");
excelFile.AddMapping <RecordManager>(x => x.Channel, "行政通道*");
excelFile.AddMapping <RecordManager>(x => x.TLicense, "行驶证号*");
excelFile.AddMapping <RecordManager>(x => x.DLicense, "驾驶证号*");
excelFile.AddMapping <RecordManager>(x => x.RecordMGrade, "备案分级");
excelFile.AddMapping <RecordManager>(x => x.ValideTime, "有效期");
excelFile.AddMapping <RecordManager>(x => x.CarColor, "车身颜色");
excelFile.AddMapping <RecordManager>(x => x.CarType, "车类型");
excelFile.AddMapping <RecordManager>(x => x.Driver, "驾驶人");
excelFile.AddMapping <RecordManager>(x => x.Contact, "联系方式");
excelFile.AddMapping <RecordManager>(x => x.Organization, "组织单位");
var excelContent = excelFile.Worksheet <RecordManager>(0);
var records = new List <RecordManager>();
if (excelContent.IsNotNull() && excelContent.Count() > 0)
{
foreach (var row in excelContent)
{
if (string.IsNullOrEmpty(row.CarNumber))
{
return(new Resp_Binary {
message = "车牌号不允许为空"
});
}
if (string.IsNullOrEmpty(row.TLicense))
{
return(new Resp_Binary {
message = "行驶证号不允许为空"
});
}
if (string.IsNullOrEmpty(row.DLicense))
{
return(new Resp_Binary {
message = "驾驶证号不允许为空"
});
}
var record = new RecordManager
{
IsValid = 1,
CarNumber = row.CarNumber,
Channel = row.Channel,
TLicense = row.TLicense,
DLicense = row.DLicense,
RecordMGrade = row.RecordMGrade,
ValideTime = row.ValideTime,
CarColor = row.CarColor,
CarType = row.CarType,
Driver = row.Driver,
Contact = row.Contact,
Organization = row.Organization,
SysUserId = userId,
CreateTime = DateTime.Now,
};
records.Add(record);
}
}
BulkOperation <RecordManager> .MySqlBulkInsert(records, _repository);
//_repository.DelRepeatRecord();
return(new Resp_Binary {
flag = 1, message = "导入成功"
});
}
