// todo system.data.dll引用失败 ,2020.3.18自己写了个MyDataTable结构
public MyDataTable ReadFile(string filepath)
{
MyDataTable dt = new MyDataTable();
//首行是字段名 之后是字段值
string[] lines = File.ReadAllLines(filepath);
bool firstline = true;
foreach (string line in lines)
{
string[] words = line.Split('\t');
if (words == null || words.Length == 0)
{
continue;
}
if (firstline)
{
firstline = false;
foreach (string word in words)
{
dt.Columns.Add(word);
}
continue;
}
MyDataRow row = dt.GenOneRow();
int i = 0;
foreach (string word in words)
{
row[i++] = word;
}
dt.Rows.Add(row);
}
return(dt);
}
public IActionResult GetReportExcelByTrainingCourseAll([FromBody] TrainingFilterViewModel trainingFilter)
{
try
{
var worker = new Worker()
{
TemplateFolder = this.appEnvironment.WebRootPath + "\\reports\\",
};
var hasData = this.repositoryReport
.GetReportByTrainingCourse(trainingFilter);
var creDataTable = new MyDataTable();
var dataTable = creDataTable.CreateMyDataTable <TrainingMasterReportViewModel>(hasData.Item1);
Dictionary <string, string> dic = new Dictionary <string, string>()
{
{ "TrainingCourse", hasData.Item2.TrainingCousreCode },
{ "TrainingCourseName", hasData.Item2.TrainingCousreName }
};
var stream = worker.Export(dataTable, dic, "TrainingMasterReport");
stream.Seek(0, SeekOrigin.Begin);
return(File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Reports.xlsx"));
}
catch (Exception ex)
{
return(NotFound(new { Error = "Training Master report not found " + ex.ToString() }));
}
}
/// <summary>
/// Export data table as file. Only CSV for now
/// </summary>
/// <param name="dataTable"></param>
/// <param name="exportType"></param>
/// <param name="entityName"></param>
/// <param name="headerItems"></param>
/// <returns></returns>
public static async Task <FileContentResult> ExportFile(this MyDataTable dataTable, string exportType, string entityName, IDictionary <string, string> headerItems = null)
{
byte[] theBytes = null;
string fileName = DateTime.Now.GetLocalTime().ToString("yyyy-MM-dd hh:mm:ss") + "_" + entityName;
string mimeType = "";
switch (exportType)
{
case "xls":
case "xlsx":
theBytes = await dataTable.ToExcel(headerItems);
mimeType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; // "application/ms-excel"; //
fileName += ".xlsx";
break;
//case "pdf":
// mimeType = "application/pdf";
// fileName += ".pdf";
// theBytes = new byte[0];
// break;
case "csv":
default:
theBytes = await dataTable.ToCSV(headerItems);
mimeType = "text/csv";
fileName += ".csv";
break;
}
return(new FileContentResult(theBytes, mimeType)
{
FileDownloadName = fileName
});
}
public override bool Load()
{
TableReader tr = new TableReader();
DataReader dr = new DataReader();
MyDataTable dt = tr.ReadFile(new MyConfig().WorkDir + "npcProperty.txt");
foreach (MyDataRow row in dt.Rows)
{
var item = new npcPropertyItem();
item.id = dr.GetInt(row["id"].ToString());
item.maxHp = dr.GetInt(row["maxHp"].ToString());
item.maxMp = dr.GetInt(row["maxMp"].ToString());
item.physic = dr.GetInt(row["physic"].ToString());
item.magic = dr.GetInt(row["magic"].ToString());
item.attackRange = dr.GetInt(row["attackRange"].ToString());
item.viewRange = dr.GetInt(row["viewRange"].ToString());
item.moveSpeed = dr.GetInt(row["moveSpeed"].ToString());
item.turnSpeed = dr.GetInt(row["turnSpeed"].ToString());
item.startPoint = dr.GetFloatList(row["startPoint"].ToString());
item.maxRadius = dr.GetFloat(row["maxRadius"].ToString());
item.modelPath = (row["modelPath"].ToString());
item.skillList = dr.GetIntList(row["skillList"].ToString());
m_Items[item.id] = item;
}
return(true);
}
public IActionResult GetReportByTrainingProgramWithPosition(int id)
{
try
{
var worker = new Worker()
{
TemplateFolder = this.appEnvironment.WebRootPath + "\\reports\\",
};
var hasData = this.repositoryReport.GetReportByTrainingProgram(id);
var creDataTable = new MyDataTable();
var dataTable = creDataTable.CreateMyDataTable <TrainingProgram2ReportViewModel>(hasData.Item1);
Dictionary <string, string> dic = new Dictionary <string, string>()
{
{ "Detail", hasData.Item2 },
};
var stream = worker.Export(dataTable, dic, "TrainingProgramReport");
stream.Seek(0, SeekOrigin.Begin);
return(File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Reports.xlsx"));
}
catch (Exception ex)
{
return(NotFound(new { Error = "Training Program report not found " + ex.ToString() }));
}
}
/// <summary>
/// Creates a CSV file in the path given by <paramref name="fullFileName"/>
/// </summary>
/// <param name="dataTable"></param>
/// <param name="fullFileName"></param>
/// <param name="replaceNullsWithDefaultValue">Replace Null Values With default values in the generated files</param>
/// <param name="delimiter">The delimiter. It's comma by default</param>
public static async Task CreateCSVfile(MyDataTable dataTable, string fullFileName, bool replaceNullsWithDefaultValue = false, string delimiter = ",")
{
await Task.Run(() =>
{
using (StreamWriter sw = new StreamWriter(fullFileName, false))
{
WriteFile(sw, dataTable, null, replaceNullsWithDefaultValue, delimiter);
}
});
}
static void Main(string[] args)
{
MyDataTable t1 = new MyDataTable();
t1.Columns.Add(new DataColumn("Name", typeof(string)));
t1.Columns.Add(new DataColumn("DateOfBirth", typeof(DateTime)));
MyDataRow r1 = t1.NewRow() as MyDataRow;
r1["Name"] = "Bob";
r1["DateOfBirth"] = new DateTime(1970, 5, 12);
t1.Rows.Add(r1);
}
public virtual async Task <byte[]> ExportToXlsx(MyDataTable dataTable, IDictionary <string, string> headerItems = null,
bool replaceNullsWithDefaultValue = false)
{
return(await Task.Run(() =>
{
using (var stream = new MemoryStream())
{
// ok, we can run the real code of the sample now
using (var xlPackage = new ExcelPackage(stream))
{
// uncomment this line if you want the XML written out to the outputDir
//xlPackage.DebugMode = true;
// get handles to the worksheets
var worksheet = xlPackage.Workbook.Worksheets.Add("Exported data");
var row = 1;
// General Titles
if (headerItems != null && headerItems.Count > 0)
{
foreach (var item in headerItems)
{
worksheet.Cells[row, 1].Value = item.Key;
worksheet.Cells[row, 2].Value = item.Value;
row++;
}
row++;
}
var poz = 1;
_properties = dataTable.Columns;
foreach (var propertyByName in _properties)
{
propertyByName.Value.ColumnIndex = poz;
poz++;
}
//create Headers and format them
WriteCaption(worksheet, SetCaptionStyle, row);
row++;
foreach (var item in dataTable.Rows)
{
WriteToXlsx(worksheet, item, row++, UseDropdownlistsForAssociatedEntities);
}
xlPackage.Save();
}
return stream.ToArray();
}
}));
}
/// <summary>
/// Creates a CSV file and return it as byte array
/// </summary>
/// <param name="dataTable"></param>
/// <param name="headerItems"></param>
/// <param name="replaceNullsWithDefaultValue"></param>
/// <param name="delimiter"></param>
/// <returns></returns>
public static async Task <byte[]> CreateCSVfile(MyDataTable dataTable, IDictionary <string, string> headerItems = null, bool replaceNullsWithDefaultValue = false, string delimiter = ",")
{
return(await Task.Run(() =>
{
byte[] theByte = null;
using (var memory = new MemoryStream())
{
using (StreamWriter sw = new StreamWriter(memory))
{
WriteFile(sw, dataTable, headerItems, replaceNullsWithDefaultValue, delimiter, true);
}
theByte = memory.ToArray();
}
return theByte;
}));
}
/// <summary>
/// This is currently designed to only work for primitive types as I am only using it for
/// generic export. It can be made better.
/// </summary>
/// <typeparam name="T">This may be a type generated at runtime</typeparam>
/// <param name="items"></param>
/// <param name="validColumns">NB: No need to put S/No column. It's added automatically</param>
/// <param name="generatedType">If null, it defaults to typeof(T). If <paramref name="T"/> is a generated type, supply the type here to aid in the resolution, because it will read as Object.</param>
/// <returns></returns>
public static MyDataTable ToDataTable <T>(this IList <T> items, IList <MyDataColumn> validColumns, Type generatedType = null)
{
if (generatedType == null)
{
generatedType = typeof(T);
}
var dt = new MyDataTable(generatedType.Name);
dt.Columns.Add("No", new MyDataColumn("No", typeof(int)));
foreach (var item in validColumns)
{
dt.Columns.Add(item.ColumnName, item);
}
string propName;
for (int i = 0; i < items.Count; i++)
{
var row = dt.NewRow();
row["No"] = i + 1;
foreach (var col in dt.Columns)
{
if (col.Key == "No")
{
continue;
}
propName = col.Value.PropertyName;
object theVal = null;
try
{
theVal = generatedType.GetProperty(propName).GetValue(items[i], null);
}
catch (TargetException)
{
theVal = null;
}
catch (RuntimeBinderException)
{
theVal = null;
}
row[col.Key] = theVal;
}
dt.Rows.Add(row);
}
return(dt);
}
private static void WriteFile(StreamWriter sw, MyDataTable dataTable, IDictionary <string, string> headerItems = null,
bool replaceNullsWithDefaultValue = false, string delimiter = ",", bool doColumnNames = false)
{
// General Titles
if (headerItems != null && headerItems.Count > 0)
{
foreach (var item in headerItems)
{
sw.WriteLine(item.Key + delimiter + item.Value);
sw.Flush();
}
}
if (doColumnNames)
{
// The Column Titles
string[] columnNames = dataTable.Columns.Keys.ToArray();
sw.WriteLine(string.Join(delimiter, columnNames));
sw.Flush();
}
// The rows
int icolcount = dataTable.Columns.Count;
int i;
foreach (MyDataRow drow in dataTable.Rows)
{
i = 0;
foreach (var cell in drow)
{
var exportStr = IOManager.GetExportString(cell, dataTable.Columns[cell.Key], replaceNullsWithDefaultValue);
if (exportStr.Contains(delimiter))
{
exportStr = "\"" + exportStr + "\"";
}
sw.Write(exportStr);
if (i < icolcount - 1) // which will be at the last item in this inner loop
{
sw.Write(delimiter);
}
i++;
}
sw.Write(sw.NewLine);
}
}
public override bool Load()
{
TableReader tr = new TableReader();
DataReader dr = new DataReader();
MyDataTable dt = tr.ReadFile(new MyConfig().WorkDir + "skill.txt");
foreach (MyDataRow row in dt.Rows)
{
var item = new skillItem();
item.id = dr.GetInt(row["id"].ToString());
item.config = (row["config"].ToString());
item.priority = dr.GetInt(row["priority"].ToString());
item.icon = (row["icon"].ToString());
m_Items[item.id] = item;
}
return(true);
}
public IActionResult GetTrainingCostFromHistoryExcel([FromBody] TrainingFilterViewModel TrainingFilter)
{
string ErrorMessage = "";
try
{
if (TrainingFilter != null)
{
if (TrainingFilter.AfterDate.HasValue && TrainingFilter.EndDate.HasValue)
{
var HasData = this.repositoryReport.GetTrainingCostFromHistory(TrainingFilter);
if (HasData != null)
{
var worker = new Worker()
{
TemplateFolder = this.appEnvironment.WebRootPath + "\\reports\\",
};
var creDataTable = new MyDataTable();
var dataTable = creDataTable.CreateMyDataTable <TrainingCostViewModel>(HasData);
var StartDate = HelperClass.ConverterDate(TrainingFilter.AfterDate.Value.AddHours(7));
var EndDate = HelperClass.ConverterDate(TrainingFilter.EndDate.Value.AddHours(7));
Dictionary <string, string> dic = new Dictionary <string, string>()
{
{ "StartDate", "Çѹ·Õè " + StartDate + " " },
{ "EndDate", " ¶Ö§Çѹ·Õè " + EndDate },
};
var stream = worker.Export(dataTable, dic, "TrainingCostReport");
stream.Seek(0, SeekOrigin.Begin);
return(File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Reports.xlsx"));
}
}
}
}
catch (Exception ex)
{
ErrorMessage = ex.ToString();
}
return(NotFound(new { Error = "Employee history not found" + ErrorMessage }));
}
protected void Page_Load(object sender, EventArgs e)
{
DataTable dt = new MyDataTable();
//first add your series
foreach (DataRow row in dt.DefaultView.ToTable(true, new string[] { "Type" }).Rows)
{
Series series = new Series();
series.Name = (string)row["Type"];
series.ChartType = SeriesChartType.StackedColumn;
Chart1.Series.Add(series);
}
// then add your points;
foreach (DataRow row in dt.Rows)
{
Chart1.Series[(string)row["Type"]].Points.AddXY(row["Location"], new object[] { row["Total"] });
}
}
public void MyDataTableTest()
{
MyDataTable dataTable = new MyDataTable();
dataTable.AddColumn("Id", typeof(int));
dataTable.AddColumn("Name", typeof(string));
for (int i = 20; i >= 0; i--)
{
dataTable.AddRows(i, i.ToString());
}
dataTable.DumpToOutput();
DataTable dt = dataTable.SortByColumn("Id", true);
dataTable.DumpToOutput(dt);
}
public IActionResult GetReportByEmployeeCodeExcel(string EmployeeCode)
{
string ErrorMessage = "";
try
{
if (!string.IsNullOrEmpty(EmployeeCode))
{
var HasData = this.repositoryReport.GetEmployeeHistoryTraining(EmployeeCode);
if (HasData != null)
{
var worker = new Worker()
{
TemplateFolder = this.appEnvironment.WebRootPath + "\\reports\\",
};
var creDataTable = new MyDataTable();
var dataTable = creDataTable.CreateMyDataTable <CourseHistory>(HasData.HistoryCourses);
Dictionary <string, string> dic = new Dictionary <string, string>()
{
{ "EmpCode", HasData.EmpCode },
{ "EmpName", HasData.EmpName },
{ "Position", HasData.Position },
{ "Section", HasData.Section },
{ "StartDate", HasData.StartDate },
};
var stream = worker.Export(dataTable, dic, "TrainingEmployeeReport");
stream.Seek(0, SeekOrigin.Begin);
return(File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Reports.xlsx"));
}
}
}
catch (Exception ex)
{
ErrorMessage = ex.ToString();
}
return(NotFound(new { Error = "Employee history not found" + ErrorMessage }));
}
/// <summary>
/// NB: This may not work well when you have some DB column names different from the property names
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="idT"></typeparam>
/// <param name="items"></param>
/// <param name="connection"></param>
/// <param name="columnsInTheTable"></param>
/// <param name="tableName"></param>
/// <param name="entityName"></param>
/// <param name="isDataMigration">For SQL Server: if true, then we need to Keep Identity</param>
/// <param name="schema">For SQL Server: To be appended to the table name</param>
public static async Task SqlBulkInsert <T, idT>(IList <T> items, IDbConnection connection, string tableName, string entityName = null, bool isDataMigration = false, string schema = "dbo") where T : class, IBaseEntity <idT> where idT : IEquatable <idT>
{
if (items == null)
{
return;
}
if (items.Count == 0)
{
//Logger.Log("SQL Bulk Insert - Nothing to insert for " + entityName);
return;
}
string typeName = typeof(T).Name;
if (string.IsNullOrWhiteSpace(tableName))
{
tableName = typeof(T).GetTableName();
}
if (string.IsNullOrWhiteSpace(entityName))
{
entityName = "null";
}
tableName = tableName.Replace("[", "").Replace("]", "");
if (string.IsNullOrWhiteSpace(schema))
{
schema = "dbo";
}
PropertyInfo[] props;
MyDataTable dt = FillInDataColumns(typeof(T), typeof(idT), connection, tableName, schema, out props);
//Fill in the rows
var saverID = getSaverID();
foreach (var item in items)
{
BuildNewRow(dt, props, item, saverID);
}
//Finally...
await DoBulkInsert(dt, connection, tableName, isDataMigration, schema);
}
/// <summary>
/// NB: This may not work well when you have some DB column names different from the property names
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="idT"></typeparam>
/// <param name="items"></param>
/// <param name="connection"></param>
/// <param name="columnsInTheTable"></param>
/// <param name="tableName"></param>
/// <param name="entityName"></param>
/// <param name="isDataMigration">For SQL Server: if true, then we need to Keep Identity</param>
/// <param name="schema">For SQL Server: To be appended to the table name</param>
public static void SqlBulkInsert(Type TType, IList items, IDbConnection connection, string tableName, string entityName = null, bool isDataMigration = false, string schema = "dbo")
{
if (items == null)
{
return;
}
if (items.Count == 0)
{
//Logger.Log("SQL Bulk Insert - Nothing to insert for " + entityName);
return;
}
string typeName = TType.Name;
if (string.IsNullOrWhiteSpace(tableName))
{
tableName = TType.GetTableName();
}
if (string.IsNullOrWhiteSpace(entityName))
{
entityName = "null";
}
tableName = tableName.Replace("[", "").Replace("]", "");
if (string.IsNullOrWhiteSpace(schema))
{
schema = "dbo";
}
PropertyInfo[] props;
MyDataTable dt = FillInDataColumns(TType, typeof(long), connection, tableName, schema, out props);
//Fill in the rows
var savedBy = getSaverID();
foreach (var item in items)
{
BuildNewRow(dt, props, item, savedBy);
}
//Finally...
DoBulkInsert(dt, connection, tableName, isDataMigration, schema);
}
private static void ExportarDataTable()
{
using (FutbolDataContext ctx = new FutbolDataContext())
{
ctx.Log = Console.Out;
MyDataTable dt = new MyDataTable();
dt.TableName = "Portugueses";
var x = from f in ctx.Futbolista
where f.CodigoPaisNacimiento == "PT"
select dt.MakeDataRow(f.Nombre, f.FechaNacimiento.Value);
x.CopyToDataTable(dt, LoadOption.OverwriteChanges);
// comprobación
foreach (DataRow dr in dt.Rows)
Console.WriteLine(dr[0]);
}
}
private static void BuildNewRow(MyDataTable dt, PropertyInfo[] props, object item, long savedBy)
{
var row = dt.NewRow();
for (int j = 0; j < props.Length; j++)
{
var prop = props[j];
if (!dt.Columns.ContainsKey(prop.GetPropertyName()))
{
if (prop.PropertyType.IsClass && prop.PropertyType != typeof(string))
{
var innerProps = prop.PropertyType.GetProperties();
if (innerProps.Any(x => x.Name == "Id")) // Handle properties mapped as Reference
{
string columnName = string.Format("{0}Id", prop.Name.StartsWith("The") ? prop.Name.Remove(0, 3) : prop.Name);
if (!dt.Columns.ContainsKey(columnName))
{
continue;
}
object theRefValue;
try
{
dynamic propValue = prop.GetValue(item, null);
theRefValue = propValue.Id;
}
catch (TargetException)
{
theRefValue = null;
}
catch (RuntimeBinderException)
{
theRefValue = null;
}
catch (Exception) { throw; }
if (theRefValue == null)
{
row[columnName] = System.DBNull.Value;
continue;
}
row[columnName] = theRefValue;
}
else //Handling Compositely mapped Class properties
{
var modifyFieldNameAttr = prop.GetCustomAttribute <CompositeMappingModifyFieldNamesAttribute>();
for (int k = 0; k < innerProps.Length; k++)
{
var innerProp = innerProps[k];
string innerPropName = null;
if (modifyFieldNameAttr.UseAllPPropertiesWithTHeirDefaultNames)
{
if (!dt.Columns.ContainsKey(innerProp.GetPropertyName()))
{
continue;
}
innerPropName = innerProp.GetPropertyName();
}
else
{
if (!modifyFieldNameAttr.FieldAndPropNames.ContainsKey(innerProp.GetPropertyName()))
{
continue;
}
innerPropName = modifyFieldNameAttr.FieldAndPropNames[innerProp.GetPropertyName()];
if (!dt.Columns.ContainsKey(innerPropName))
{
continue;
}
}
object theInnerValue;
try
{
theInnerValue = innerProp.GetValue(prop.GetValue(item, null), null);
if (theInnerValue == null && innerProp.IsTypeNullable())
{
theInnerValue = Nullable.GetUnderlyingType(innerProp.PropertyType).GetDefaultValue(); //innerProp.PropertyType.GenericTypeArguments[0].GetDefaultValue();
}
}
catch (TargetException)
{
theInnerValue = null;
}
catch (Exception) { throw; }
if (string.IsNullOrWhiteSpace(innerPropName))
{
innerPropName = modifyFieldNameAttr.FieldAndPropNames[innerProp.GetPropertyName()];
}
if (theInnerValue == null)
{
row[innerPropName] = DBNull.Value;
continue;
}
if (innerProp.PropertyType == typeof(DateTime) && theInnerValue != null && Convert.ToDateTime(theInnerValue) < new DateTime(1900, 1, 1))
{
theInnerValue = new DateTime(1900, 1, 1);
}
if (innerProp.PropertyType.IsEnum && theInnerValue != null)
{
//DO NOT use Enum.Parse(innerProp.PropertyType, theInnerValue.ToString()); since we store enums as integers conventionally
theInnerValue = (int)theInnerValue;
}
row[innerPropName] = theInnerValue;
}
}
continue;
}
continue;
}
object theValue;
try
{
if (prop.Name == "DateMigrated")
{
theValue = DateTime.Now.GetLocalTime();
}
else if (prop.Name == "SavedBy")
{
theValue = savedBy;
}
else
{
theValue = prop.GetValue(item, null);
}
if (theValue == null)
{
if (prop.Name == "DateCreated")
{
theValue = new DateTime(1900, 1, 1);
}
else if (prop.Name == "IsDisabled" || prop.Name == "IsDeleted")
{
theValue = false;
}
else if (prop.IsTypeNullable())
{
var mainType = Nullable.GetUnderlyingType(prop.PropertyType);
if (mainType == typeof(DateTime))
{
theValue = new DateTime(1900, 1, 1);
}
else
{
theValue = mainType.GetDefaultValue();
}
}
}
}
catch (TargetException)
{
theValue = null;
}
catch (Exception) { throw; }
if (theValue == null)
{
row[prop.GetPropertyName()] = DBNull.Value;
continue;
}
if (prop.PropertyType == typeof(DateTime) && theValue != null && Convert.ToDateTime(theValue) < new DateTime(1900, 1, 1))
{
theValue = new DateTime(1900, 1, 1);
}
if (prop.PropertyType.IsEnum && theValue != null)
{
//DO NOT use Enum.Parse(prop.PropertyType, theValue.ToString()); since we store enums as integers conventionally
theValue = (int)theValue;
}
row[prop.GetPropertyName()] = theValue;
}
dt.Rows.Add(row);
}
private static MyDataTable FillInDataColumns(Type TType, Type idType, IDbConnection connection, string tableName, string schema, out PropertyInfo[] props)
{
string prepend = "";
if (connection is SqlConnection)
{
prepend = $"use {connection.Database}; ";
}
string sqlForColumnNames = string.Format("{0}select column_name from information_schema.columns where table_name = '{1}' and table_schema = '{2}'"
, prepend, tableName, string.IsNullOrWhiteSpace(prepend) ? connection.Database : schema);
var columnsInDestinationTable = ExecuteSelectQuery(sqlForColumnNames, connection);
props = TType.GetProperties().Where(x => x.CanWrite && x.GetCustomAttribute <NotMappedAttribute>() == null).ToArray();
MyDataTable dt = new MyDataTable(tableName);
var propsToReturn = new List <PropertyInfo>();
//Gather datatable columns
var classes = props.Where(x => x.PropertyType.IsClass && x.PropertyType != typeof(string));
var compositeClasses = classes.Where(x => x.GetCustomAttribute <CompositeMappingModifyFieldNamesAttribute>() != null);
var nonCompositeClasses = classes.Where(x => x.GetCustomAttribute <CompositeMappingModifyFieldNamesAttribute>() == null);
var propsDict = props.ToDictionary(x => x.GetPropertyName());
foreach (var col in columnsInDestinationTable)
{
if (dt.Columns.ContainsKey(col))
{
continue;
}
Type type = null;
if (propsDict.ContainsKey(col))
{
var prop = propsDict[col];
type = prop.IsTypeNullable() ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType;
}
else
{
if (col.EndsWith("Id")) //=> we may be dealing with a reference mapping
{
#region we may be dealing with a reference mapping
foreach (var comp in nonCompositeClasses)
{
if (type != null)
{
break;
}
var innerProps = comp.PropertyType.GetProperties().Where(x => x.CanWrite).ToArray();
if (innerProps.Any(x => x.Name == "Id")) // Handle properties mapped as Reference
{
string columnName = string.Format("{0}Id", comp.Name.StartsWith("The") ? comp.Name.Remove(0, 3) : comp.Name);
if (col == columnName)
{
type = idType;
break;
}
}
}
if (type == null)
{
foreach (var comp in compositeClasses)
{
if (type != null)
{
break;
}
var innerProps = comp.PropertyType.GetProperties().Where(x => x.CanWrite && x.GetCustomAttribute <NotMappedAttribute>() == null).ToArray();
var modifyFieldNameAttr = comp.GetCustomAttribute <CompositeMappingModifyFieldNamesAttribute>();
for (int j = 0; j < innerProps.Length; j++)
{
var innerProp = innerProps[j];
if (modifyFieldNameAttr.UseAllPPropertiesWithTHeirDefaultNames)
{
if (col != innerProp.GetPropertyName())
{
continue;
}
}
else
{
if (!modifyFieldNameAttr.FieldAndPropNames.ContainsKey(innerProp.GetPropertyName()))
{
continue;
}
if (col != modifyFieldNameAttr.FieldAndPropNames[innerProp.GetPropertyName()])
{
continue;
}
}
type = innerProp.IsTypeNullable() ? Nullable.GetUnderlyingType(innerProp.PropertyType) : innerProp.PropertyType;
break;
}
}
}
if (type == null)
{
// let it be Int64.
type = typeof(long);
}
#endregion
}
else //=> we may be dealing with a composite mapping
{
#region MyRegion
foreach (var comp in compositeClasses)
{
if (type != null)
{
break;
}
var innerProps = comp.PropertyType.GetProperties().Where(x => x.CanWrite && x.GetCustomAttribute <NotMappedAttribute>() == null).ToArray();
var modifyFieldNameAttr = comp.GetCustomAttribute <CompositeMappingModifyFieldNamesAttribute>();
for (int j = 0; j < innerProps.Length; j++)
{
var innerProp = innerProps[j];
if (modifyFieldNameAttr.UseAllPPropertiesWithTHeirDefaultNames)
{
if (col != innerProp.GetPropertyName())
{
continue;
}
}
else
{
if (!modifyFieldNameAttr.FieldAndPropNames.ContainsKey(innerProp.GetPropertyName()))
{
continue;
}
if (col != modifyFieldNameAttr.FieldAndPropNames[innerProp.GetPropertyName()])
{
continue;
}
}
type = innerProp.IsTypeNullable() ? Nullable.GetUnderlyingType(innerProp.PropertyType) : innerProp.PropertyType;
break;
}
}
#endregion
}
}
try
{
if (type.IsEnum)
{
type = idType;
}
}
catch (Exception ex)
{
throw new ApplicationException("Failed to determine type for " + col + " when trying to fill DataTable Column", ex);
}
dt.Columns.Add(col, new MyDataColumn(col, type));
}
return(dt);
}
private static async Task DoBulkInsert(MyDataTable dt, IDbConnection connection, string tableName, bool isDataMigration = false, string schema = "dbo")
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
connection.Open();
var con = connection as MySqlConnection;
if (con != null)
{
string strFile = string.Format("{0}_{1}.csv", tableName, DateTime.UtcNow.ToString("yyyyMMMddhhmmss"));
//Create directory if not exist... Make sure directory has required rights..
var baseDir = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "MySQLTemp");
if (!Directory.Exists(baseDir))
{
Directory.CreateDirectory(baseDir);
}
var theFile = Path.Combine(baseDir, strFile);
//If file does not exist then create it and right data into it..
if (!File.Exists(theFile))
{
using (File.Create(theFile))
{
}
}
string fieldTerminator = "\t";
string lineTerminator = "\r\n";
//Generate csv file from where data read
await IO.CsvWriter.CreateCSVfile(dt, theFile, true, fieldTerminator);
try
{
RunMySqlCommand(con, "SET FOREIGN_KEY_CHECKS=0");
MySqlBulkLoader bl = new MySqlBulkLoader(con)
{
TableName = tableName,
FieldTerminator = fieldTerminator,
LineTerminator = lineTerminator,
FileName = theFile,
NumberOfLinesToSkip = 0
};
bl.Columns.Clear();
foreach (var col in dt.Columns)
{
bl.Columns.Add(col.Key);
}
int count = bl.Load();
RunMySqlCommand(con, "SET FOREIGN_KEY_CHECKS=1");
}
finally
{
try
{
File.Delete(theFile);
}
catch { }
}
}
else // SQL Server
{
#region Deal with later - or never
//dt.TableName = string.Format("[{0}].[{1}]", schema, tableName);
//var sqlConn = connection as SqlConnection;
//SqlBulkCopyOptions sqlBulkCopyOptions;
//if (isDataMigration)
//{
// //NB: The Bitwise OR (|) is NOT the same as OR. What this particular ORing does, in English, is actually AND; i.e., we want to
// //Keep Identity and Keep nulls also.
// sqlBulkCopyOptions = SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls;
//}
//else
//{
// sqlBulkCopyOptions = SqlBulkCopyOptions.KeepNulls;
//}
//using (var copy = new SqlBulkCopy(sqlConn, sqlBulkCopyOptions, null)
//{
// BulkCopyTimeout = 10000,
// DestinationTableName = dt.TableName,
// NotifyAfter = 5000,
//})
//{
// foreach (DataColumn column in dt.Columns)
// {
// copy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
// }
// copy.WriteToServer(dt);
//}
#endregion
}
}
public static async Task <byte[]> ToExcel(this MyDataTable dtable, IDictionary <string, string> headerItems = null, bool replaceNullsWithDefaultValue = false, string delimiter = ",")
{
var writer = new ExcelWriter();
return(await writer.ExportToXlsx(dtable, headerItems, replaceNullsWithDefaultValue));
}
private void btnLJSC_Click(object sender, EventArgs e)
{
#region [��֤��Ϣ]
bool IsBlank = false;
DataGridViewRowCollection dgvrc = dgvAreaInfo.Rows;
foreach (DataGridViewRow dgvr in dgvrc)
{
foreach (DataGridViewCell dgvc in dgvr.Cells)
{
if (dgvc.Value == null)
{
IsBlank = true;
break;
}
}
}
for (int i = 0; i < this.dgvAreaInfo.Rows.Count; i++)
{
for (int j = 0; j < 4; j++)
{
if (this.dgvAreaInfo.Rows[i].Cells[j].Value.ToString() == "")
{
MessageBox.Show(this.dgvAreaInfo.Rows[i].Cells[0].Value + " ����Ϣ������������д���������������ļ���", " ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
if (this.dgvAreaInfo.Rows[i].Cells["������"].Value.ToString().Length !=13)
{
MessageBox.Show(this.dgvAreaInfo.Rows[i].Cells[0].Value + " �ķ�վ���벻��ȷ������д���������������ļ���", " ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
}
}
if (IsBlank)
{
MessageBox.Show("������Ϣ�б����пհı������Կհı�������б༭��", " ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
#endregion
#region [����Ϣ]
DataTable dt = DS.Tables[0].GetChanges();//strTerName.GetHashCode()
int iTempCount = DS.Tables[0].Rows.Count;
if (dt != null || IAreacount != iTempCount)
{
DataTable dt1 = DS.Tables[0];
DO.ReturnInt_ExcuteSQL("delete from parameter_Area");
DO.ReturnInt_ExcuteSQL("delete from Territorial_Info ");
DO.ReturnInt_ExcuteSQL("delete from Territorial_Set ");
DO.ReturnInt_ExcuteSQL("delete from Territorial_Config ");
foreach (DataRow dr1 in dt1.Rows)
{
if (DO.ReturnInt_ExcuteSQL("insert into Parameter_Area(MineNO,AreaNO,AreaName,AreaType,AreaCheckPersonCount) values('" + dr1["������"].ToString().Substring(0, 11) + "','" + dr1["������"].ToString().Substring(11) + "','" + dr1["��������"] + "','" + dr1["��������"] + "'," + dr1["����˶�����"] + ")") <= 0)
{
MessageBox.Show("�Ѿ�������ͬ����������!", " ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
}
foreach (DataRow dr1 in dt1.Rows)
{
int areaTypeID = SetAreaTypeID(dr1["��������"].ToString().Trim());
DO.ReturnInt_ExcuteSQL("insert into Territorial_Info (TerritorialID,TerritorialName,TerritorialTypeID, IsEnable, Instruction,Remark,TerritorialNO) "
+ " values(" + dr1["��������"].ToString().GetHashCode() + ",'" + dr1["��������"].ToString() + "'," + areaTypeID + ",1,'','','" + dr1["������"].ToString().Substring(11) + "')");
//ͬ����������
DO.ReturnInt_ExcuteSQL("insert into Territorial_Config (TerConfigID ,TerritorialID,TerWorkTime,TerEmpCount) values(" + dr1["��������"].ToString().GetHashCode()
+ "," + dr1["��������"].ToString().GetHashCode() + ",-1," + dr1["����˶�����"] + ")");
}
foreach (DataRow dr1 in DT_Before.Rows)
{
if (dr1["���������"].ToString().Trim() != "")
{
DO.ReturnInt_ExcuteSQL(" update Parameter_Area_Station set AreaNO= '" + dr1["�������"].ToString() + "',AreaName='" + dr1["���������"].ToString() + "' where AreaName ='" + dr1["��������"].ToString() + "'");
}
}
//ɾ�������վ���ñ����Ѿ�ɾ��������
DO.ReturnDataSet_ExcuteSQL(" delete from Parameter_Area_Station where AreaName not in ( select AreaName from Parameter_Area)");
}
#endregion
#region [������Ϣ]
DataSet ds = DO.ReturnDataSet_ExcuteSQL("select MineNO,AreaType,(MineNO+AreaNO) AreaNO,AreaCheckPersonCount,AreaName from parameter_Area");
if (ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show("��������ϢҪ���ɣ�", " ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
MyDataTable MDT = new MyDataTable(ds.Tables[0], DateTime.Now.ToString("yyyyMMddHHmmss"), "RYQY", "D:\\Location");
try
{
//ɾ����ʷ�켣��Ϣ����������һ��
// DO.ReturnDataSet_ExcuteSQL(" delete from Parameter_TrackPoints");
}
catch
{ }
MessageBox.Show("������Ϣ���ɳɹ�", " ", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
isUseClose = true;
#endregion
}
public static async Task <byte[]> ToCSV(this MyDataTable dtable, IDictionary <string, object> headerItems = null, bool replaceNullsWithDefaultValue = false, string delimiter = ",")
{
return(await IO.CsvEngine.CreateCSVfile(dtable, headerItems, replaceNullsWithDefaultValue, delimiter));
}
static void Main(string[] args)
{
MyDataTable t = new MyDataTable();
t.Rows.Add(t.NewRow()); // <-- Exception here, wrong type (base doesn't count).
}
