// 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). }