public static DataSet DataReaderToDataSet(IDataReader reader) { var ds = new DataSet(); DataTable table; do { int fieldCount = reader.FieldCount; table = new DataTable(); for (int i = 0; i < fieldCount; i++) { table.Columns.Add(reader.GetName(i), reader.GetFieldType(i)); } table.BeginLoadData(); var values = new Object[fieldCount]; while (reader.Read()) { reader.GetValues(values); table.LoadDataRow(values, true); } table.EndLoadData(); ds.Tables.Add(table); } while (reader.NextResult()); reader.Close(); return ds; }
protected override DataTable ProcessResult(DataTable schema) { schema.BeginLoadData(); foreach (DataRow row in schema.Rows) { // ToBoolean foreach (DataColumn col in schema.Columns) if (col.Caption.StartsWith("IS_")) row[col.Caption] = row[col.Caption] != DBNull.Value && Convert.ToInt32(row[col.Caption], CultureInfo.InvariantCulture) != 0; if (Convert.ToInt32(row["HELPER_CID"], CultureInfo.InvariantCulture) != 0) row["SOURCE"] = HelperGetObjectDefinition(Convert.ToInt32(row["HELPER_CID"], CultureInfo.InvariantCulture)); } schema.EndLoadData(); schema.AcceptChanges(); schema.Columns.Remove("HELPER_CID"); return schema; }
public static DataSet GetDataSet(String sqlString) { using (SqlCommand Cmd = new SqlCommand(sqlString)) try { Cmd.CommandText = sqlString; Cmd.Connection = DbManager.Con; SqlDataAdapter dbDataAdapter = new SqlDataAdapter(Cmd); DataSet dataSet = new DataSet(); DataTable dataTable = new DataTable(); dataTable.BeginLoadData(); dbDataAdapter.Fill(dataTable); dataTable.EndLoadData(); dataSet.EnforceConstraints = false; dataSet.Tables.Add(dataTable); return dataSet; } catch (Exception ex) { throw; } }
public static DataTable ConvertDataReaderToDataTable(DbDataReader reader) { try { DataTable table = new DataTable(); int fieldCount = reader.FieldCount; for (int fieldIndex = 0; fieldIndex < fieldCount; ++fieldIndex) { table.Columns.Add(reader.GetName(fieldIndex), reader.GetFieldType(fieldIndex)); } table.BeginLoadData(); object[] rowValues = new object[fieldCount]; while (reader.Read()) { reader.GetValues(rowValues); table.LoadDataRow(rowValues, true); } reader.Close(); table.EndLoadData(); return table; } catch (Exception ex) { throw new Exception("DataReader转换为DataTable时出错!", ex); } }
public static DataTable ToDataTable(this TableData data, bool fillData = true, string tableNamePostfix = "") { var table = new DataTable(data.Name + tableNamePostfix); var keys = new List<DataColumn>(); foreach (var field in data.Schema.Fields) { var type = field.ValueType; var innerType = Nullable.GetUnderlyingType(type); var col = table.Columns.Add(field.Name, innerType ?? type); col.ExtendedProperties.Add("XFieldPosition", field); if (field.FieldType == FieldType.Key) { keys.Add(col); } } table.PrimaryKey = keys.ToArray(); if (fillData) { table.BeginLoadData(); foreach (var row in data.Rows) { var dataRow = table.NewRow(); for(var i = 0; i < data.Schema.Fields.Length; i++) { dataRow[i] = row[i]; } table.Rows.Add(dataRow); } table.EndLoadData(); } return table; }
public static System.Data.DataTable ConverDataReaderToDataTable(System.Data.IDataReader reader) { System.Data.DataTable result; if (null == reader) { result = null; } else { System.Data.DataTable dataTable = new System.Data.DataTable(); dataTable.Locale = CultureInfo.InvariantCulture; int fieldCount = reader.FieldCount; for (int i = 0; i < fieldCount; i++) { dataTable.Columns.Add(reader.GetName(i), reader.GetFieldType(i)); } dataTable.BeginLoadData(); object[] values = new object[fieldCount]; while (reader.Read()) { reader.GetValues(values); dataTable.LoadDataRow(values, true); } dataTable.EndLoadData(); result = dataTable; } return(result); }
public static DataTable ConvertDataReaderToDataTable(SqlDataReader reader) { try { DataTable objDataTable = new DataTable(); int intFieldCount = reader.FieldCount; for (int intCounter = 0; intCounter < intFieldCount; ++intCounter) { objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter)); } objDataTable.BeginLoadData(); object[] objValues = new object[intFieldCount]; while (reader.Read()) { reader.GetValues(objValues); objDataTable.LoadDataRow(objValues, true); } reader.Close(); objDataTable.EndLoadData(); return(objDataTable); } catch (Exception ex) { throw new Exception("转换出错!", ex); } }
protected override DataTable ProcessResult(DataTable schema) { schema.BeginLoadData(); foreach (DataRow row in schema.Rows) { if (row["INPUTS"] == DBNull.Value) { row["INPUTS"] = 0; } if (row["OUTPUTS"] == DBNull.Value) { row["OUTPUTS"] = 0; } if (row["IS_SYSTEM_PROCEDURE"] == DBNull.Value || Convert.ToInt32(row["IS_SYSTEM_PROCEDURE"], CultureInfo.InvariantCulture) == 0) { row["IS_SYSTEM_PROCEDURE"] = false; } else { row["IS_SYSTEM_PROCEDURE"] = true; } } schema.EndLoadData(); schema.AcceptChanges(); return schema; }
public static DataTable ConverDataReaderToDataTable(IDataReader reader) { if (reader == null) { return null; } DataTable table = new DataTable { Locale = CultureInfo.InvariantCulture }; int fieldCount = reader.FieldCount; for (int i = 0; i < fieldCount; i++) { table.Columns.Add(reader.GetName(i), reader.GetFieldType(i)); } table.BeginLoadData(); object[] values = new object[fieldCount]; while (reader.Read()) { reader.GetValues(values); table.LoadDataRow(values, true); } table.EndLoadData(); return table; }
protected override DataTable ProcessResult(DataTable schema) { schema.BeginLoadData(); foreach (DataRow row in schema.Rows) { // ToBoolean foreach (DataColumn col in schema.Columns) if (col.Caption.StartsWith("IS_")) row[col.Caption] = row[col.Caption] != DBNull.Value && Convert.ToInt32(row[col.Caption], CultureInfo.InvariantCulture) != 0; row["MS_DBTOOLS_SUPPORT"] = (Convert.ToInt32( HelperGetExtendedProperty( "microsoft_database_tools_support", "schema", "dbo", "table", row["TABLE_NAME"].ToString(), null, null), CultureInfo.InvariantCulture) == 1); } schema.EndLoadData(); schema.AcceptChanges(); return schema; }
protected void CopyData(DataTable source, DataTable destination) { var visbleCount = selectedField.Count(x => x.visible); destination.BeginLoadData(); foreach (DataRow dr in source.Rows) { var newrow = destination.NewRow(); foreach (var rf in selectedField) { if (rf.visible) { newrow[rf.outputField] = dr[rf.outputField]; } } //Выставляем явно значения определенного типа для полей: "Сумма", "Доля рынка в %" и т.д. //(visbleCount * 2) - потому, что участвует код (первичный ключ) и строковое значение, //пример: PriceCode и PriceName. for (int i = (visbleCount * 2); i < source.Columns.Count; i++) { if (!(dr[source.Columns[i].ColumnName] is DBNull) && destination.Columns.Contains(source.Columns[i].ColumnName)) { newrow[source.Columns[i].ColumnName] = Convert.ChangeType(dr[source.Columns[i].ColumnName], destination.Columns[source.Columns[i].ColumnName].DataType); } } destination.Rows.Add(newrow); } destination.EndLoadData(); }
private System.Data.DataTable GetDataTable(ITable pip_Table) { System.Data.DataTable lc_TableData = new System.Data.DataTable("统计结果"); if (pip_Table == null) { return(null); } ICursor lip_Cursor = null; try { // 无数据返回空表 if (pip_Table.RowCount(null) == 0) { return(null); } // 给列赋值 for (int index = 0; index < pip_Table.Fields.FieldCount; index++) { DataColumn lc_DataColum = new DataColumn(); lc_DataColum.ColumnName = pip_Table.Fields.get_Field(index).AliasName; lc_DataColum.Caption = pip_Table.Fields.get_Field(index).AliasName; lc_DataColum.DataType = System.Type.GetType("System.String"); lc_TableData.Columns.Add(lc_DataColum); } // 循环拷贝数据 lip_Cursor = pip_Table.Search(null, false); if (lip_Cursor == null) { return(null); } IRow lip_Row = lip_Cursor.NextRow(); lc_TableData.BeginLoadData(); while (lip_Row != null) { DataRow lc_Row = lc_TableData.NewRow(); for (int i = 0; i < pip_Table.Fields.FieldCount; i++) { string values = lip_Row.get_Value(i).ToString(); lc_Row[i] = values; } lc_TableData.Rows.Add(lc_Row); lip_Row = lip_Cursor.NextRow(); } lc_TableData.EndLoadData(); return(lc_TableData); } catch (Exception ex) { return(lc_TableData); } finally { if (lip_Cursor != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(lip_Cursor); } } }
/// <summary> /// Reads the CSV raw data. /// </summary> /// <returns>A temp DataTable object.</returns> private DataTable ReadCsvRawData() { // Create a temp DataTable to hold data from CSV. var tmpTable = new DataTable(); tmpTable.Columns.Add(LineNumberColumnName); tmpTable.BeginLoadData(); CsvRow row; bool firstRow = true; while (this.reader.ReadRow(out row)) { // Ignore empty lines if (row.TotalColumns == 1 && row[0].IsNullOrWhitespace()) { continue; } // In case the first line if (firstRow) { foreach (string colName in row.Columns) { tmpTable.Columns.Add(colName); } firstRow = false; } else { int i = 1; DataRow dr = tmpTable.NewRow(); dr[LineNumberColumnName] = row.LineNumber; var columns = row.Columns; foreach (string col in columns) { if (!col.IsNullOrWhitespace()) { dr[i] = col; } i++; } tmpTable.Rows.Add(dr); } } tmpTable.EndLoadData(); return tmpTable; }
public static DataTable getDifferentRecords(DataTable FirstDataTable, DataTable SecondDataTable) { FirstDataTable = FirstDataTable.Copy(); FirstDataTable.TableName += " First"; SecondDataTable = SecondDataTable.Copy(); SecondDataTable.TableName += " Second"; //Create Empty Table DataTable ResultDataTable = new DataTable("ResultDataTable"); //use a Dataset to make use of a DataRelation object using (DataSet ds = new DataSet()) { //Add tables ds.Tables.AddRange(new DataTable[] { FirstDataTable, SecondDataTable }); //Get Columns for DataRelation DataColumn[] firstColumns = FirstDataTable.Columns.Cast<DataColumn>().ToArray(); DataColumn[] secondColumns = SecondDataTable.Columns.Cast<DataColumn>().ToArray(); //Create DataRelation DataRelation r1 = new DataRelation(string.Empty, firstColumns, secondColumns, false); ds.Relations.Add(r1); //DataRelation r2 = new DataRelation(string.Empty, secondColumns, firstColumns, false); //ds.Relations.Add(r2); //Create columns for return table List<DataColumn> PK = new List<DataColumn>(); for (int i = 0; i < FirstDataTable.Columns.Count; i++) { DataColumn newdc = ResultDataTable.Columns.Add(FirstDataTable.Columns[i].ColumnName, FirstDataTable.Columns[i].DataType); if (FirstDataTable.PrimaryKey.Contains(FirstDataTable.Columns[i])) PK.Add(newdc); } ResultDataTable.PrimaryKey = PK.ToArray(); //If FirstDataTable Row not in SecondDataTable, Add to ResultDataTable. ResultDataTable.BeginLoadData(); foreach (DataRow parentrow in FirstDataTable.Rows) { DataRow[] childrows = parentrow.GetChildRows(r1); if (childrows == null || childrows.Length == 0) ResultDataTable.LoadDataRow(parentrow.ItemArray, true); } ////If SecondDataTable Row not in FirstDataTable, Add to ResultDataTable. //foreach (DataRow parentrow in SecondDataTable.Rows) //{ // DataRow[] childrows = parentrow.GetChildRows(r2); // if (childrows == null || childrows.Length == 0) // ResultDataTable.LoadDataRow(parentrow.ItemArray, true); //} ResultDataTable.EndLoadData(); } return ResultDataTable; }
/// <summary> /// Create a command and call ExecuteReader. Load the data into a DataTable /// </summary> /// <param name="commandText">The command text</param> /// <param name="parameters">Parameters referring to @p1, @p2, ...</param> /// <returns></returns> public DataTable ExecuteReader(string commandText, params object[] parameters) { using (var command = this.CreateCommand(commandText, parameters)) using (var reader = command.ExecuteReader()) { var result = new DataTable(); result.BeginLoadData(); result.Load(reader); result.EndLoadData(); reader.Close(); return result; } }
protected override DataTable ProcessResult(DataTable schema) { schema.BeginLoadData(); foreach (DataRow row in schema.Rows) foreach (DataColumn col in schema.Columns) if (col.Caption.StartsWith("IS_")) row[col.Caption] = row[col.Caption] != DBNull.Value && Convert.ToInt32(row[col.Caption], CultureInfo.InvariantCulture) != 0; schema.EndLoadData(); schema.AcceptChanges(); return schema; }
public DataParser(string filename) { StreamReader reader = File.OpenText(filename); string line; string[] a; while (null != (line = reader.ReadLine())) { a = line.Split(_Delimiter) .Select<string, string>(s => s.Trim(_Quotes)) .ToArray(); // First line of text file contains // schedule name if (null == _Name) { _Name = a[0]; continue; } // Second line of text file contains // schedule column names if (null == _Table) { _Table = new DataTable(); foreach (string column_name in a) { DataColumn column = new DataColumn(); column.DataType = typeof(string); column.ColumnName = column_name; _Table.Columns.Add(column); } _Table.BeginLoadData(); continue; } // Remaining lines define schedula data DataRow dr = _Table.LoadDataRow(a, true); } _Table.EndLoadData(); }
private void UpdateMe_Load(object sender, EventArgs e) { //WN8ExpectedGrid dd = new WN8ExpectedGrid(); //dd.Show(); WOTStatistics.Core.WOTHelper.AddToLog("########## WOT Statistics initializing"); _updateServers.Add(1, "http://www.vbaddict.net:82/"); oTableUpdate = new DataTable(); oTableUpdate.Columns.Add("Short", System.Type.GetType("System.String")); oTableUpdate.Columns.Add("Name", System.Type.GetType("System.String")); oTableUpdate.Columns.Add("VersionLocal", System.Type.GetType("System.String")); oTableUpdate.Columns.Add("VersionServer", System.Type.GetType("System.String")); oTableUpdate.Columns.Add("Status", System.Type.GetType("System.String")); oTableUpdate.BeginLoadData(); oTableUpdate.LoadDataRow(new object[] { "CheckVersion", "Server Connection" }, true); oTableUpdate.LoadDataRow(new object[] { "AppVersion", "Version", WOTStatistics.Core.UserSettings.AppVersion }, true); oTableUpdate.LoadDataRow(new object[] { "ReleaseNotes", "Release Notes", WOTStatistics.Core.UserSettings.LastReleaseNotes }, true); oTableUpdate.LoadDataRow(new object[] { "SettingsFileVersion", "Settings", WOTStatistics.Core.UserSettings.SettingsFileVersion }, true); oTableUpdate.LoadDataRow(new object[] { "Images", "Image Verification" }, true); oTableUpdate.LoadDataRow(new object[] { "TranslationFileVersion", "Translations", WOTStatistics.Core.UserSettings.TranslationFileVersion }, true); oTableUpdate.LoadDataRow(new object[] { "ScriptFileVersion", "Rating Formula", WOTStatistics.Core.UserSettings.ScriptFileVersion }, true); oTableUpdate.LoadDataRow(new object[] { "DossierVersion", "Dossier Decrypter", WOTStatistics.Core.UserSettings.DossierVersion }, true); oTableUpdate.LoadDataRow(new object[] { "WN8ExpectedVersion", "WN8 Expected Tank Values", WOTStatistics.Core.UserSettings.WN8ExpectedVersion }, true); //oTableUpdate.LoadDataRow(new object[] { "ActiveDossierUploaderVersion", "Active Dossier Uploader", WOTStatistics.Core.UserSettings.ActiveDossierUploaderVersion }, true); oTableUpdate.LoadDataRow(new object[] { "DBMain", "Database State" }, true); oTableUpdate.EndLoadData(); //oStatus.SmallImages = imgStatus; //oStatus.Items.Clear(); //oStatus.Items.Add(new DevExpress.XtraEditors.Controls.ImageComboBoxItem("Fail", 0, 0)); //oStatus.Items.Add(new DevExpress.XtraEditors.Controls.ImageComboBoxItem("Success", 1, 1)); _workerThread = new BackgroundWorker() { WorkerSupportsCancellation = true }; _workerThread.DoWork += new DoWorkEventHandler(bgw_DoWork); _workerThread.RunWorkerCompleted += new RunWorkerCompletedEventHandler(bgw_RunWorkerCompleted); _workerThread.RunWorkerAsync(); }
public static DataTable Distinct(DataTable Table, DataColumn[] Columns) { //Empty table DataTable table = new DataTable("Distinct"); //Sort variable string sort = string.Empty; //Add Columns & Build Sort expression for(int i = 0; i < Columns.Length; i++) { table.Columns.Add(Columns[i].ColumnName,Columns[i].DataType); sort += Columns[i].ColumnName + ","; } //Select all rows and sort DataRow[] sortedrows = Table.Select(string.Empty,sort.Substring(0,sort.Length-1)); object[] currentrow = null; object[] previousrow = null; table.BeginLoadData(); foreach(DataRow row in sortedrows) { //Current row currentrow = new object[Columns.Length]; for(int i = 0; i < Columns.Length; i++) { currentrow[i] = row[Columns[i].ColumnName]; } //Match Current row to previous row if(!RowEqual(previousrow, currentrow)) table.LoadDataRow(currentrow,true); //Previous row previousrow = new object[Columns.Length]; for(int i = 0; i < Columns.Length; i++) { previousrow[i] = row[Columns[i].ColumnName]; } } table.EndLoadData(); return table; }
//Intersection //Union //Difference private DataTable _Intersect( DataTable parentTable, string pColName, DataTable childTable, string cColName) { DataTable parent = parentTable.Copy(); DataTable child = childTable.Copy(); //Creating Empty Table DataTable table = new DataTable("Intersect"); //Creating Dataset to use DataRelation using (DataSet ds = new DataSet("IntersectedData")) { //Adding Tables to Dataset ds.Tables.AddRange(new DataTable[] { parent, child}); //Creating columns for DataRelation DataColumn colParent = parent.Columns[pColName]; DataColumn colChild = child.Columns[cColName]; //Creating DataRelation DataRelation relIntersect = new DataRelation("RelIntersect", colParent, colChild); //Adding DataRelation to DataSet. ds.Relations.Add(relIntersect); //TODO: solve problem here //Cloning the Structure of Parent table to Return table. table = parent.Clone(); //if Parent row is in Child table, Add it to Return table. table.BeginLoadData(); foreach (DataRow parentRow in parent.Rows) { DataRow[] childRows = parentRow.GetChildRows(relIntersect); if (childRows.Length > 0) { table.LoadDataRow(parentRow.ItemArray, true); } }//foreach parent row table.EndLoadData(); }//using Dataset return table; }//_Intersect
public static DataTable ListToDataTable(object list, Dictionary<string, string> titles, bool IsExportAllCol, string tableName) { if (string.IsNullOrEmpty(tableName)) { var type = ZGeneric.GetGenericType(list); tableName = ZGeneric.IsDynamicType(type) ? string.Empty : type.Name; } DataTable table = new DataTable(tableName); table.BeginLoadData(); EachHelper.EachListHeader(list, (rowIndex, name, cellType) => { if (IsExportAllCol || titles.ContainsKey(name)) { string typeName = cellType.ToString(); if (cellType.IsGenericType) typeName = cellType.GetGenericArguments()[0].ToString(); Type newType = Type.GetType(typeName, false); if (newType != null) { //table.Columns.Add((titles[name] ?? name).ToString(), newType); table.Columns.Add(name, newType); } } }); EachHelper.EachListRow(list, (index, rowData) => { DataRow row = table.NewRow(); EachHelper.EachObjectProperty(rowData, (cellIndex, name, value) => { if (IsExportAllCol || titles.ContainsKey(name)) { if (value != null && (Type.GetType(value.GetType().ToString(), false) != null)) //row[(titles[name] ?? name).ToString()] = value; row[name] = value; } }); table.Rows.Add(row); }); table.EndLoadData(); table.AcceptChanges(); return table; }
public static void AddView(ref System.Data.DataView rDtv_Source, ref System.Data.DataTable rDtt_Destination) { System.Data.DataRowView lDrv_Sorce = null; // inicia insercao rDtt_Destination.BeginLoadData(); // percorre e importa foreach (DataRowView lDrv_Sorce_loopVariable in rDtv_Source) { lDrv_Sorce = lDrv_Sorce_loopVariable; // copia a linha atual AddRowView(lDrv_Sorce, ref rDtt_Destination); } // fim rDtt_Destination.EndLoadData(); }
private DataTable BuildSampleTable() { var table = new DataTable(); table.BeginInit(); table.Columns.Add("COL-1", typeof(int)); table.Columns.Add("COL-2"); table.EndInit(); table.BeginLoadData(); for (var i = 0; i < 5; i++) { table.LoadDataRow(new object[] {i, (i + 1).ToString()}, true); } table.EndLoadData(); return table; }
public void Init(object data) { var type = ZGeneric.GetGenericType(data); var tableName = ZGeneric.IsDynamicType(type) ? string.Empty : type.Name; table = new DataTable(tableName); EachHelper.EachListHeader(data, (rowIndex, name, cellType) => { string typeName = cellType.ToString(); if (cellType.IsGenericType) typeName = cellType.GetGenericArguments()[0].ToString(); Type newType = Type.GetType(typeName, false); if (newType != null) table.Columns.Add(name, newType); }); table.BeginLoadData(); title = new List<string>(); }
public DataTable Fetch() { DataTable table = new DataTable(); using (_connection = new SqlConnection(_connectionString)) { _command = new SqlCommand(_sql, _connection); _connection.Open(); table.BeginLoadData(); SqlDataReader reader = _command.ExecuteReader(); table.Load(reader); table.EndLoadData(); reader.Close(); } return table; }
/// <summary> /// Converts the data reader to data set. /// </summary> /// <param name="reader">The reader.</param> /// <returns>DataSet.</returns> public static DataSet ConvertDataReaderToDataSet(IDataReader reader) { DataSet ds = new DataSet(); DataTable dataTable = new DataTable(); DataTable schemaTable = reader.GetSchemaTable(); DataRow row; string columnName; DataColumn column; int count = schemaTable.Rows.Count; for (int i = 0; i < count; i++) { row = schemaTable.Rows[i]; columnName = (string)row["ColumnName"]; column = new DataColumn(columnName, (Type)row["DataType"]); dataTable.Columns.Add(column); } ds.Tables.Add(dataTable); object[] values = new object[count]; try { dataTable.BeginLoadData(); while (reader.Read()) { reader.GetValues(values); dataTable.LoadDataRow(values, true); } } finally { dataTable.EndLoadData(); reader.Close(); } return ds; }
public int Fill(DataTable table) { var reader = this.SelectCommand.ExecuteReader(); if (table.Columns.Count < 1) { for (var i = 0; i < reader.FieldCount; i++) { table.Columns.Add(reader.GetName(i), reader.GetFieldType(i)); } } int count = 0; DataRow row; object[] values = new object[reader.FieldCount]; table.BeginLoadData(); try { while (reader.Read()) { row = table.NewRow(); try { row.BeginEdit(); reader.GetValues(values); row.ItemArray = values; table.Rows.Add(row); } finally { row.EndEdit(); row.AcceptChanges(); } } } finally { table.EndLoadData(); } return count; }
public void save_to_excel_file(string file_name, float[] channel_2, int count_channel_2, float[] timeCoordinate, ProgramSettings program_settings) { DataSet ds = new DataSet("DataSet"); DataTable dt = new DataTable("Cигнал"); string[] str_arr_2 = new string[2] { "Сила, [Н]", "Время, [c]" }; for (int c = 0; c < 2; c++) dt.Columns.Add(str_arr_2[c], typeof(double)); double[,] array = new double[count_channel_2, 2]; for (int i = 0; i < count_channel_2; i++) array[i, 0] = channel_2[i]; for (int i = 0; i < count_channel_2; i++) array[i, 1] = timeCoordinate[i]; dt.BeginLoadData(); DataTable dt_ = new DataTable("Параметры шаблона"); string[] str_arr = new string[3] { "Сила, [Н]", "Длительность прикладываемого усилия, [c]", "Количество попыток" }; for (int c = 0; c < 3; c++) dt_.Columns.Add(str_arr[c], typeof(double)); object[] newRow_ = new object[3]; newRow_[0] = program_settings.f_max; newRow_[1] = program_settings.time_incr; newRow_[2] = program_settings.num_of_attempts; dt_.LoadDataRow(newRow_, true); for (int r = 0; r < count_channel_2; r++) { object[] newRow = new object[2]; newRow[0] = array[r, 0]; newRow[1] = array[r, 1]; dt.LoadDataRow(newRow, true); } ds.Tables.Add(dt); ds.Tables.Add(dt_); ExcelLibrary.DataSetHelper.CreateWorkbook(file_name + ".xls", ds); MessageBox.Show("Результаты измерений сохранены"); }
private void button1_Click(object sender, EventArgs e) { var dt = new System.Data.DataTable(); dt.TableName = "Untitled"; dt.Columns.Add("Name", typeof(string)); dt.Columns.Add("Public", typeof(bool)); dt.BeginLoadData(); foreach (var type in typeof(string).Assembly.GetExportedTypes().Take(100)) { dt.Rows.Add(type.Name, type.IsPublic); } dt.EndLoadData(); var model = new IR.Modeling.Report(); model.DataTable = dt; //NormalizeDataTableColumnNames(); var repdef = CreateSimpleReportStyle1(); repdef.ReportTitle = dt.TableName; repdef.DataTable = dt; repdef.DatasetName = dt.TableName; repdef.DefaultFontFamily = "Segoe UI"; //repdef.GroupOnColumns.AddRange( new int [] { 0 }); var viewingoptions = new Viziblr.Reporting.ViewingOptions(); //if (this.OutputRDLFilename != null) //{ // viewingoptions.OutputRDLFilename = this.OutputRDLFilename; // viewingoptions.SaveRDLFile = true; //} Viziblr.Reporting.Util.ShowReport(repdef, viewingoptions, null); }
public DataTable getAllUgels() { Dt = new DataTable(); Cmd = new SqlCommand(); Cmd.Connection = Conn; try { Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "PA_GET_LIST_UGEL"; Dtr = Cmd.ExecuteReader(); Dt.Constraints.Clear(); Dt.BeginLoadData(); Dt.Load(Dtr); Dtr.Close(); } catch (Exception e) { Console.WriteLine("{0} Exception caught.", e); } return Dt; }
public void RowEnd() { if (_table == null) { _table = new DataTable(); for (var i = 0; i < _headers.Count; i++) { _table.Columns.Add(_headers[i], _values[i] == null ? typeof(string) : _values[i].GetType()); } _table.BeginLoadData(); _loadingData = true; _table.LoadDataRow(_values.ToArray(), true); _headers = null; _values = null; } else { _row.EndEdit(); _table.Rows.Add(_row); _row = null; } }
public DataTable countRowsSchoolTeachersJuego() { Dt = new DataTable(); Cmd = new SqlCommand(); Cmd.Connection = Conn; try { Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "[otaku_bcp].PA_COUNT_SCHOOL_TEACHER_JUEGO"; Dtr = Cmd.ExecuteReader(); Dt.Constraints.Clear(); Dt.BeginLoadData(); Dt.Load(Dtr); Dtr.Close(); } catch (Exception e) { Console.WriteLine("{0} Exception caught.", e); } return Dt; }
/// <summary> /// Loads a DataTable from a sequence of objects. /// </summary> /// <param name="source">The sequence of objects to load into the DataTable.</param> /// <param name="table">The input table. The schema of the table must match that /// the type T. If the table is null, a new table is created with a schema /// created from the public properties and fields of the type T.</param> /// <param name="options">Specifies how values from the source sequence will be applied to /// existing rows in the table.</param> /// <returns>A DataTable created from the source sequence.</returns> public DataTable Create(IQueryable source) { var entityType = source.ElementType; var entityTypeProperties = entityType.GetProperties(); var table = new DataTable(entityType.Namespace + "." + entityType.Name); var cols = entityTypeProperties.Select(p => new DataColumn(p.Name, Nullable.GetUnderlyingType(p.PropertyType) ?? p.PropertyType)); table.Columns.AddRange(cols.ToArray()); table.BeginLoadData(); var entityEnumerator = source.GetEnumerator(); while (entityEnumerator.MoveNext()) { var values = entityTypeProperties.Select(p => p.GetValue(entityEnumerator.Current, null) ?? DBNull.Value); table.LoadDataRow(values.ToArray(), true); } table.EndLoadData(); return table; }
protected override DataTable ProcessResult(DataTable schema) { schema.BeginLoadData(); foreach (DataRow row in schema.Rows) { // ToBoolean foreach (DataColumn col in schema.Columns) if (col.Caption.StartsWith("IS_")) row[col.Caption] = row[col.Caption] != DBNull.Value && Convert.ToInt32(row[col.Caption], CultureInfo.InvariantCulture) != 0; if (row["NUMERIC_PRECISION"] == DBNull.Value) row["NUMERIC_PRECISION"] = 0; if (row["NUMERIC_SCALE"] == DBNull.Value) row["NUMERIC_SCALE"] = 0; var helperDid = Convert.ToInt32(row["HELPER_DID"], CultureInfo.InvariantCulture); if (helperDid != 0) row["COLUMN_DEFAULT"] = HelperGetObjectDefinition(helperDid); if (Convert.ToBoolean(row["IS_COMPUTED"])) row["COMPUTED_SOURCE"] = HelperGetComputed( Convert.ToInt32(row["HELPER_OID"], CultureInfo.InvariantCulture), Convert.ToInt32(row["HELPER_COLID"], CultureInfo.InvariantCulture)); } schema.EndLoadData(); schema.AcceptChanges(); schema.Columns.Remove("HELPER_DID"); schema.Columns.Remove("HELPER_OID"); //schema.Columns.Remove("HELPER_COLID"); schema.Columns["HELPER_COLID"].Caption = "ORDINAL_POSITION"; return schema; }
private void button1_Click(object sender, EventArgs e) { var dt = new System.Data.DataTable(); dt.TableName = "Untitled"; dt.Columns.Add("Name", typeof (string)); dt.Columns.Add("Public", typeof (bool)); dt.BeginLoadData(); foreach (var type in typeof(string).Assembly.GetExportedTypes().Take(100)) { dt.Rows.Add(type.Name, type.IsPublic); } dt.EndLoadData(); var model = new IR.Modeling.Report(); model.DataTable = dt; //NormalizeDataTableColumnNames(); var repdef = CreateSimpleReportStyle1(); repdef.ReportTitle = dt.TableName; repdef.DataTable = dt; repdef.DatasetName = dt.TableName; repdef.DefaultFontFamily = "Segoe UI"; //repdef.GroupOnColumns.AddRange( new int [] { 0 }); var viewingoptions = new Viziblr.Reporting.ViewingOptions(); //if (this.OutputRDLFilename != null) //{ // viewingoptions.OutputRDLFilename = this.OutputRDLFilename; // viewingoptions.SaveRDLFile = true; //} Viziblr.Reporting.Util.ShowReport(repdef,viewingoptions,null); }
public DataTable getAllTeacherGradeForIdUser(int iduser) { Dt = new DataTable(); Cmd = new SqlCommand(); Cmd.Connection = Conn; try { Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "PA_GET_TEACHER_GRADE_FOR_USER_ID"; Cmd.Parameters.Add("@iduser", SqlDbType.Int).Value = iduser; Dtr = Cmd.ExecuteReader(); Dt.Constraints.Clear(); Dt.BeginLoadData(); Dt.Load(Dtr); Dtr.Close(); } catch (Exception e) { Console.WriteLine("{0} Exception caught.", e); } return Dt; }
public DataTable getAllDistritos(int idProvincia) { Dt = new DataTable(); Cmd = new SqlCommand(); Cmd.Connection = Conn; try { Cmd.CommandType = CommandType.StoredProcedure; Cmd.CommandText = "[otaku_bcp].PA_GET_DISTRITOS"; Cmd.Parameters.Add("@idprovincia", SqlDbType.Int).Value = idProvincia; Dtr = Cmd.ExecuteReader(); Dt.Constraints.Clear(); Dt.BeginLoadData(); Dt.Load(Dtr); Dtr.Close(); } catch (Exception e) { Console.WriteLine("{0} Exception caught.", e); } return Dt; }
public static System.Data.DataTable consulta(System.Data.DataTable profesor, System.Data.DataTable alumno) { //Result table System.Data.DataTable table = new System.Data.DataTable("Union"); //Build new columns DataColumn[] newcolumns = new DataColumn[profesor.Columns.Count]; for (int i = 0; i < profesor.Columns.Count; i++) { newcolumns[i] = new DataColumn(profesor.Columns[i].ColumnName, profesor.Columns[i].DataType); } //add new columns to result table table.Columns.AddRange(newcolumns); table.BeginLoadData(); //Load data from first table foreach (DataRow row in profesor.Rows) { table.LoadDataRow(row.ItemArray, true); } //Load data from second table foreach (DataRow row in alumno.Rows) { table.LoadDataRow(row.ItemArray, true); } table.EndLoadData(); return(table); }
/// <summary> /// Selects all rows from both tables as long as there is a match between the columns in both tables. /// </summary> /// <param name="source">The source.</param> /// <param name="child">The child.</param> /// <param name="parentDataColumns">The parent data columns.</param> /// <param name="childDataColumns">The child data columns.</param> /// <returns> /// Returns a <see cref="DataTable" /> representing the joined table. /// </returns> /// <remarks> /// This JOIN method is equivalent to the TSQL INNER JOIN expression using equality. /// </remarks> public static DataTable Join(this DataTable source, DataTable child, DataColumn[] parentDataColumns, DataColumn[] childDataColumns) { DataTable table = new DataTable(source.TableName + "_" + child.TableName); using (DataSet ds = new DataSet()) { ds.Tables.AddRange(new[] { source.Copy(), child.Copy() }); DataColumn[] parentColumns = new DataColumn[parentDataColumns.Length]; for (int i = 0; i < parentColumns.Length; i++) { parentColumns[i] = ds.Tables[0].Columns[parentDataColumns[i].ColumnName]; } DataColumn[] childColumns = new DataColumn[childDataColumns.Length]; for (int i = 0; i < childColumns.Length; i++) { childColumns[i] = ds.Tables[1].Columns[childDataColumns[i].ColumnName]; } DataRelation r = new DataRelation(string.Empty, parentColumns, childColumns, false); ds.Relations.Add(r); for (int i = 0; i < source.Columns.Count; i++) { table.Columns.Add(source.Columns[i].ColumnName, source.Columns[i].DataType); } var dataColumns = new List <DataColumn>(); for (int i = 0; i < child.Columns.Count; i++) { if (!table.Columns.Contains(child.Columns[i].ColumnName)) { table.Columns.Add(child.Columns[i].ColumnName, child.Columns[i].DataType); } else { dataColumns.Add(child.Columns[i]); } } table.BeginLoadData(); foreach (DataRow parentRow in ds.Tables[0].Rows) { var parent = parentRow.ItemArray; var childRows = parentRow.GetChildRows(r); if (childRows.Any()) { foreach (DataRow childRow in childRows) { var children = childRow.ItemArray.Where((value, i) => !dataColumns.Select(o => o.Ordinal).Contains(i)).ToArray(); var join = new object[parent.Length + children.Length]; Array.Copy(parent, 0, join, 0, parent.Length); Array.Copy(children, 0, join, parent.Length, children.Length); table.LoadDataRow(join, true); } } } table.EndLoadData(); } return(table); }
/// <param name="TotalFields">FieldName, 合并字段(相同累计,例如Account,Warehouse);排序字段(按顺序累计,例如period) </param> public static DataTable ConvertToDataTable(this IQueryable source, Dictionary <string, string> TotalFields) { System.Data.DataTable dt = source.ConvertToDataTable(); //无合计字段,无公式字段 if (TotalFields == null || !TotalFields.Any()) { return(dt); } var qryOrderbys = TotalFields.Where(q => q.Value.ToStringTrim().Contains(";") && q.Key.IsAggregate()).GroupBy(q => q.Value.Trim().Replace(" ", "")).Select(q => q.Key); //含有合并和排序字段 //数据集只有一条记录,只有公式字段 if (dt.Rows.Count == 1 || !qryOrderbys.Any()) { return(dt.SetColumnExpression(TotalFields.Where(q => !q.Value.IsAggregate()).ToDictionary(q => q.Key, q => q.Value))); } #region 合计字段 foreach (string key in TotalFields.Where(q => q.Key.IsAggregate()).Select(q => q.Key)) { string field = key.SplitByAs("Field"); if (dt.Columns.Contains(field)) { continue; } dt.Columns.Add(field.ToLower(), typeof(double)); } #endregion //复制有期末没有发生额 DataTable dtSum = dt.Clone(); IQueryable qry = dt.AsEnumerable().AsQueryable(); dt.BeginLoadData(); dtSum.BeginLoadData(); foreach (string sOrderby in qryOrderbys) { string groupbyfields = ""; LambdaExpression[] exprgroupby = dt.Columns.ToExpressions(sOrderby.SplitBy(";", 0).Split(',').ToList(), out groupbyfields); if (groupbyfields == "") { groupbyfields = "1"; } else { groupbyfields = string.Format("new ({0})", groupbyfields); } if (!qry.GroupBy(groupbyfields, "it", exprgroupby).Where("it.Count()>1").Any()) { continue; //每一组只有一条记录 } string tmporderby = sOrderby; List <string> diffFields = tmporderby.Split(new char[] { ';' })[1].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList(); string[] sameFields = tmporderby.Split(new char[] { ';' })[0].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); string[] sumFields = TotalFields.Where(q => q.Value == sOrderby).Select(q => q.Key.SplitByAs("Field")).ToArray(); string sdiff = ""; string sdiffwhere = ""; int x = 0; foreach (string s in diffFields) { sdiff += string.Format(",{0}", s); sdiffwhere += string.Format(" and trim(isnull({0},''))='{1}'", s, "{" + (x++) + "}"); } if (sdiff.StartsWith(",")) { sdiff = sdiff.Substring(1); } if (sdiffwhere.StartsWith(" and ")) { sdiffwhere = sdiffwhere.Substring(5); } DataTable dtDiff = source.GroupBy(string.Format("new ({0})", sdiff), "it").Select(string.Format("new (Key.{0})", sdiff.Replace(",", "Key."))).OrderBy(sdiff).ConvertToDataTable(); if (tmporderby.StartsWith(";")) { tmporderby = sOrderby.Substring(1); } DataRow[] drs = dt.Select("", tmporderby.Replace(';', ',')); for (int i = 1; i < drs.Length; i++) { bool sameLast = !sameFields.Where(q => drs[i][q].ToStringTrim() != drs[i - 1][q].ToStringTrim()).Any(); //与上一条记录相同 List <object> listVals0 = new List <object>(); List <object> listVals1 = new List <object>(); foreach (string s in diffFields) { listVals0.Add(drs[i - 1][s]); listVals1.Add(drs[i][s]); } int diffrow0 = dtDiff.Rows.IndexOf(dtDiff.Select(string.Format(sdiffwhere, listVals0.ToArray()))[0]); int diffrow1 = dtDiff.Rows.Count; if (sameLast) { diffrow1 = dtDiff.Rows.IndexOf(dtDiff.Select(string.Format(sdiffwhere, listVals1.ToArray()))[0]); } while (++diffrow0 < diffrow1) { DataRow dr = dtSum.NewRow(); // bool allzero = true; foreach (string f in sumFields) { dr[f] = drs[i - 1][f]; // if (allzero && dr[f].ToInt() != 0) allzero = false; } // if (allzero) continue; foreach (string f in sameFields) { dr[f] = drs[i - 1][f]; } foreach (string f in diffFields) { dr[f] = dtDiff.Rows[diffrow0][f]; } dtSum.Rows.Add(dr); } if (!sameLast) { continue; } foreach (string f in sumFields) { drs[i][f] = drs[i - 1][f].ToDouble() + drs[i][f].ToDouble(); } } } dtSum.EndLoadData(); if (dtSum.AsEnumerable().Any()) { dt.Merge(dtSum); } dt.EndLoadData(); dt = dt.SetColumnExpression(TotalFields.Where(q => !q.Value.IsAggregate()).ToDictionary(q => q.Key, q => q.Value)); return(dt); }
public DataTable getDifferentRecords(DataTable FirstDataTable, DataTable SecondDataTable) { //Create Empty Table DataTable ResultDataTable = new DataTable("ResultDataTable"); //use a Dataset to make use of a DataRelation object using (DataSet ds = new DataSet()) { //Add tables ds.Tables.AddRange(new DataTable[] { FirstDataTable.Copy(), SecondDataTable.Copy() }); //Get Columns for DataRelation DataColumn[] firstColumns = new DataColumn[ds.Tables[0].Columns.Count]; for (int i = 0; i < firstColumns.Length; i++) { firstColumns[i] = ds.Tables[0].Columns[i]; } DataColumn[] secondColumns = new DataColumn[ds.Tables[1].Columns.Count]; for (int i = 0; i < secondColumns.Length; i++) { secondColumns[i] = ds.Tables[1].Columns[i]; } //Create DataRelation DataRelation r1 = new DataRelation(string.Empty, firstColumns, secondColumns, false); ds.Relations.Add(r1); DataRelation r2 = new DataRelation(string.Empty, secondColumns, firstColumns, false); ds.Relations.Add(r2); //Create columns for return table for (int i = 0; i < FirstDataTable.Columns.Count; i++) { ResultDataTable.Columns.Add(FirstDataTable.Columns[i].ColumnName, FirstDataTable.Columns[i].DataType); } //If FirstDataTable Row not in SecondDataTable, Add to ResultDataTable. ResultDataTable.BeginLoadData(); foreach (DataRow parentrow in ds.Tables[0].Rows) { DataRow[] childrows = parentrow.GetChildRows(r1); if (childrows == null || childrows.Length == 0) { ResultDataTable.LoadDataRow(parentrow.ItemArray, true); } } //If SecondDataTable Row not in FirstDataTable, Add to ResultDataTable. foreach (DataRow parentrow in ds.Tables[1].Rows) { DataRow[] childrows = parentrow.GetChildRows(r2); if (childrows == null || childrows.Length == 0) { ResultDataTable.LoadDataRow(parentrow.ItemArray, true); } } ResultDataTable.EndLoadData(); } return(ResultDataTable); }
public void ImportToDataSet(string filePath, DataSet dataSet, Action <System.Data.DataTable> actionTable, Func <DataRow, Dictionary <string, object>, bool> actionRow, bool dbField = false) { try { string connStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{0}'; Extended Properties='Excel 8.0;HDR=Yes;IMEX=2'", filePath); List <string> sqlList = new List <string>(); foreach (System.Data.DataTable table in dataSet.Tables) { string tableName = string.Empty; if (dbField) { tableName = table.TableName; } else { if (table.ExtendedProperties.ContainsKey(TableProperty.DisplayText)) { tableName = LibSysUtils.ToString(table.ExtendedProperties[TableProperty.DisplayText]); } else { tableName = table.TableName; } } sqlList.Add(string.Format("select * from [{0}$]", tableName)); } dataSet.EnforceConstraints = false; using (OleDbConnection conn = new OleDbConnection(connStr)) { conn.Open(); try { int i = 0; foreach (string sql in sqlList) { System.Data.DataTable curTable = dataSet.Tables[i]; Dictionary <string, DataColumn> colMap = null; if (!dbField) { colMap = new Dictionary <string, DataColumn>(); foreach (DataColumn col in curTable.Columns) { try { colMap.Add(col.Caption, col); } catch (Exception ex) { throw ex; } } } curTable.BeginLoadData(); try { using (OleDbCommand command = new OleDbCommand(sql, conn)) { using (IDataReader reader = command.ExecuteReader()) { while (reader.Read()) { bool canAdd = true; Dictionary <string, object> otherValueList = null; DataRow newRow = curTable.NewRow(); newRow.BeginEdit(); try { bool allNull = true; for (int l = 0; l < reader.FieldCount; l++) { string colName = reader.GetName(l).Trim(); if (!Convert.IsDBNull(reader[l])) { if (allNull) { allNull = false; } string realName = string.Empty; if (dbField) { if (curTable.Columns.Contains(colName)) { realName = colName; } } else { if (colMap.ContainsKey(colName)) { realName = colMap[colName].ColumnName; } } if (!string.IsNullOrEmpty(realName)) { LibControlType controlType = (LibControlType)colMap[colName].ExtendedProperties[FieldProperty.ControlType]; if (controlType == LibControlType.DateTime || controlType == LibControlType.Date) { DateTime curTime; DateTime.TryParse(LibSysUtils.ToString(reader[l]), out curTime); if (controlType == LibControlType.Date) { newRow[realName] = LibDateUtils.DateTimeToLibDate(curTime); } else if (controlType == LibControlType.DateTime) { newRow[realName] = LibDateUtils.DateTimeToLibDateTime(curTime); } } else if (controlType == LibControlType.HourMinute) { string hourtime = LibSysUtils.ToString(reader[l]); hourtime = hourtime.Replace(":", ""); newRow[realName] = LibSysUtils.ToInt32(hourtime); } else if (controlType == LibControlType.Text || controlType == LibControlType.NText) { newRow[realName] = LibSysUtils.ToString(reader[l]).Trim(); } else { newRow[realName] = reader[l]; } } else { if (otherValueList == null) { otherValueList = new Dictionary <string, object>(); } if (!otherValueList.ContainsKey(colName)) { otherValueList.Add(colName, reader[l]); } } } } canAdd = !allNull; //全为null的行是空行不需要导入 if (canAdd) { canAdd = actionRow(newRow, otherValueList); } } finally { newRow.EndEdit(); } if (canAdd) { curTable.Rows.Add(newRow); } } } actionTable(curTable); } } finally { curTable.EndLoadData(); } i++; } } catch (Exception ex) { Exception ex1 = ex; } finally { conn.Close(); dataSet.EnforceConstraints = true; } } } catch (Exception ex) { if (dataSet.HasErrors) { string errorData = string.Empty; foreach (DataTable dt in dataSet.Tables) { foreach (DataRow dr in dt.GetErrors()) { errorData += string.Format(" {0}", dr.RowError); } } Exception dsEx = new Exception(errorData); LibLog.WriteLog(dsEx); } LibLog.WriteLog(ex); throw; } }
/// <summary> /// Removes the equal rows /// </summary> /// <param name="Table"></param> /// <param name="Columns"></param> /// <returns></returns> public static DataTable Distinct(DataTable Table, DataColumn[] Columns) { //Empty table DataTable table = new DataTable("Distinct"); //Sort variable string sort = string.Empty; //Add Columns & Build Sort expression for (int i = 0; i < Columns.Length; i++) { table.Columns.Add(Columns[i].ColumnName, Columns[i].DataType); sort += Columns[i].ColumnName + ","; } //Select all rows and sort DataRow[] sortedrows = Table.Select(string.Empty, sort.Substring(0, sort.Length - 1)); object[] currentrow = null; object[] previousrow = null; table.BeginLoadData(); foreach (DataRow row in sortedrows) { //Current row currentrow = new object[Columns.Length]; for (int i = 0; i < Columns.Length; i++) { currentrow[i] = row[Columns[i].ColumnName]; } //Match Current row to previous row if (!SQLOps.RowEqual(previousrow, currentrow)) { table.LoadDataRow(currentrow, true); } //Previous row previousrow = new object[Columns.Length]; for (int i = 0; i < Columns.Length; i++) { previousrow[i] = row[Columns[i].ColumnName]; } } table.EndLoadData(); return(table); }
/// <summary> /// The Product Method is the equivalent of the CROSS JOIN expression in TSQL. /// </summary> /// <param name="First"></param> /// <param name="Second"></param> /// <returns></returns> /// <remarks>Create new empty table<br> /// Add columns from First table to empty table.<br> /// Add columns from Secondtable to empty table. Rename if necessary<br> /// Loop through First table and for each row loop through Second table and add rows via array manipulation.<br> /// Return Table.</remarks> public static DataTable Product(DataTable First, DataTable Second) { DataTable table = new DataTable("Product"); //Add Columns from First for (int i = 0; i < First.Columns.Count; i++) { table.Columns.Add(new DataColumn(First.Columns[i].ColumnName, First.Columns[i].DataType)); } //Add Columns from Second for (int i = 0; i < Second.Columns.Count; i++) { //Beware Duplicates if (!table.Columns.Contains(Second.Columns[i].ColumnName)) { table.Columns.Add(new DataColumn(Second.Columns[i].ColumnName, Second.Columns[i].DataType)); } else { table.Columns.Add(new DataColumn(Second.Columns[i].ColumnName + "_Second", Second.Columns[i].DataType)); } } table.BeginLoadData(); foreach (DataRow parentrow in First.Rows) { object[] firstarray = parentrow.ItemArray; foreach (DataRow childrow in Second.Rows) { object[] secondarray = childrow.ItemArray; object[] productarray = new object[firstarray.Length + secondarray.Length]; Array.Copy(firstarray, 0, productarray, 0, firstarray.Length); Array.Copy(secondarray, 0, productarray, firstarray.Length, secondarray.Length); table.LoadDataRow(productarray, true); } } table.EndLoadData(); return(table); }
/// <summary> /// It is also refered to as MINUS and is simply all the rows that are in the First table but not the Second. /// </summary> /// <param name="First"></param> /// <param name="Second"></param> /// <returns></returns> /// <remarks> /// Create new empty table<br> /// Create a DataSet and add tables.<br> /// Get a reference to all columns in both tables<br> /// Create a DataRelation<br> /// Using the DataRelation add rows with no child rows.<br> /// Return table<br> /// </remarks> public static DataTable Difference(DataTable First, DataTable Second) { //Create Empty Table DataTable table = new DataTable("Difference"); //Must use a Dataset to make use of a DataRelation object using (DataSet ds = new DataSet()) { //Add tables ds.Tables.AddRange(new DataTable[] { First.Copy(), Second.Copy() }); //Get Columns for DataRelation DataColumn[] firstcolumns = new DataColumn[ds.Tables[0].Columns.Count]; for (int i = 0; i < firstcolumns.Length; i++) { firstcolumns[i] = ds.Tables[0].Columns[i]; } DataColumn[] secondcolumns = new DataColumn[ds.Tables[1].Columns.Count]; for (int i = 0; i < secondcolumns.Length; i++) { secondcolumns[i] = ds.Tables[1].Columns[i]; } //Create DataRelation DataRelation r = new DataRelation(string.Empty, firstcolumns, secondcolumns, false); ds.Relations.Add(r); //Create columns for return table for (int i = 0; i < First.Columns.Count; i++) { table.Columns.Add(First.Columns[i].ColumnName, First.Columns[i].DataType); } //If First Row not in Second, Add to return table. table.BeginLoadData(); foreach (DataRow parentrow in ds.Tables[0].Rows) { DataRow[] childrows = parentrow.GetChildRows(r); if (childrows == null || childrows.Length == 0) { table.LoadDataRow(parentrow.ItemArray, true); } } table.EndLoadData(); } return(table); }
internal static DataTable ReadFile(string path, int numberColumn) { char[] tabs = { '\t' }; // tab ngang char[] quotes = { '\"' }; // dấu nháy kép " DataTable table = new DataTable("dataFromFile"); for (int i = 0; i < numberColumn; i++) { table.Columns.Add(new DataColumn("col" + i, typeof(string))); } using (StreamReader sr = new StreamReader(path)) { table.BeginLoadData(); string line; //int rowsCount = 0; string firstLine = sr.ReadLine(); // string otherLine = sr.ReadToEnd(); // string[] firstLineData = firstLine.Split(new[] { "\"", "\t" }, StringSplitOptions.RemoveEmptyEntries); string[] firstLineData = ( from s in firstLine.Split(tabs) select s.Trim(quotes)).ToArray(); if (firstLineData.Length == numberColumn) { table.LoadDataRow(firstLineData, true); //rowsCount++; } else { foreach (string item in firstLineData) { if (item != String.Empty) { table.Rows.Add(); table.Rows[0][0] = item; // rowsCount++; break; } } } while (true) { line = sr.ReadLine(); if (line == null) break; string[] array = ( from s in line.Split(tabs) select s.Trim(quotes)).ToArray(); if (array.Length == numberColumn) { table.LoadDataRow(array, true); } } } table.EndLoadData(); return table; }
/// <summary> /// Returns a <see cref="T:System.Data.DataTable"/> that describes the column metadata of the <see cref="T:System.Data.IDataReader"/>. /// </summary> /// <returns> /// A <see cref="T:System.Data.DataTable"/> that describes the column metadata. /// </returns> /// <exception cref="T:System.InvalidOperationException">The <see cref="T:System.Data.IDataReader"/> is closed. </exception> public virtual System.Data.DataTable GetSchemaTable() { System.Data.DataTable schemaTable = new System.Data.DataTable(); schemaTable.Columns.Add("ColumnName", typeof(String)); schemaTable.Columns.Add("ColumnOrdinal", typeof(Int32)); schemaTable.Columns.Add("ColumnSize", typeof(Int32)); schemaTable.Columns.Add("NumericPrecision", typeof(Int32)); schemaTable.Columns.Add("NumericScale", typeof(Int32)); schemaTable.Columns.Add("IsUnique", typeof(Boolean)); schemaTable.Columns.Add("IsKey", typeof(Boolean)); schemaTable.Columns.Add("BaseCatalogName", typeof(String)); schemaTable.Columns.Add("BaseColumnName", typeof(String)); schemaTable.Columns.Add("BaseSchemaName", typeof(String)); schemaTable.Columns.Add("BaseTableName", typeof(String)); schemaTable.Columns.Add("DataType", typeof(Type)); schemaTable.Columns.Add("AllowDBNull", typeof(Boolean)); schemaTable.Columns.Add("ProviderType", typeof(Int32)); schemaTable.Columns.Add("IsAliased", typeof(Boolean)); schemaTable.Columns.Add("IsExpression", typeof(Boolean)); schemaTable.Columns.Add("IsIdentity", typeof(Boolean)); schemaTable.Columns.Add("IsAutoIncrement", typeof(Boolean)); schemaTable.Columns.Add("IsRowVersion", typeof(Boolean)); schemaTable.Columns.Add("IsHidden", typeof(Boolean)); schemaTable.Columns.Add("IsLong", typeof(Boolean)); schemaTable.Columns.Add("IsReadOnly", typeof(Boolean)); schemaTable.BeginLoadData(); for (int i = 0; i < this.FieldCount; i++) { System.Data.DataRow schemaRow = schemaTable.NewRow(); schemaRow["ColumnName"] = GetName(i); schemaRow["ColumnOrdinal"] = i; schemaRow["ColumnSize"] = -1; schemaRow["NumericPrecision"] = 0; schemaRow["NumericScale"] = 0; schemaRow["IsUnique"] = false; schemaRow["IsKey"] = false; schemaRow["BaseCatalogName"] = ""; schemaRow["BaseColumnName"] = GetName(i); schemaRow["BaseSchemaName"] = ""; schemaRow["BaseTableName"] = ""; schemaRow["DataType"] = GetFieldType(i); schemaRow["AllowDBNull"] = true; schemaRow["ProviderType"] = 0; schemaRow["IsAliased"] = false; schemaRow["IsExpression"] = false; schemaRow["IsIdentity"] = false; schemaRow["IsAutoIncrement"] = false; schemaRow["IsRowVersion"] = false; schemaRow["IsHidden"] = false; schemaRow["IsLong"] = false; schemaRow["IsReadOnly"] = false; schemaTable.Rows.Add(schemaRow); schemaRow.AcceptChanges(); } schemaTable.EndLoadData(); return(schemaTable); }
//FJC = First Join Column //SJC = Second Join Column /// <summary> /// INNER JOIN /// </summary> /// <param name="First"></param> /// <param name="Second"></param> /// <param name="FJC"></param> /// <param name="SJC"></param> /// <returns></returns> /// <remarks> /// This JOIN method is equivalent to the TSQL INNER JOIN expression using equality.<br> /// This method returns all columns from both tables.<br> /// Once again, column name collision is avoided by appending "_Second" to the columns affected.<br> /// There are a total of 3 signatures for this method.<br> /// In summary the code works as follows:<br> /// Create new empty table<br> /// Create a DataSet and add tables.<br> /// Get a reference to Join columns<br> /// Create a DataRelation<br> /// Construct JOIN table columns<br> /// Using the DataRelation add rows with matching related rows using array manipulation<br> /// Return table<br> /// </remarks> public static DataTable Join(DataTable First, DataTable Second, DataColumn[] FJC, DataColumn[] SJC) { //Create Empty Table DataTable table = new DataTable("Join"); // Use a DataSet to leverage DataRelation using (DataSet ds = new DataSet()) { //Add Copy of Tables ds.Tables.AddRange(new DataTable[] { First.Copy(), Second.Copy() }); //Identify Joining Columns from First DataColumn[] parentcolumns = new DataColumn[FJC.Length]; for (int i = 0; i < parentcolumns.Length; i++) { parentcolumns[i] = ds.Tables[0].Columns[FJC[i].ColumnName]; } //Identify Joining Columns from Second DataColumn[] childcolumns = new DataColumn[SJC.Length]; for (int i = 0; i < childcolumns.Length; i++) { childcolumns[i] = ds.Tables[1].Columns[SJC[i].ColumnName]; } //Create DataRelation DataRelation r = new DataRelation(string.Empty, parentcolumns, childcolumns, false); ds.Relations.Add(r); //Create Columns for JOIN table for (int i = 0; i < First.Columns.Count; i++) { table.Columns.Add(First.Columns[i].ColumnName, First.Columns[i].DataType); } for (int i = 0; i < Second.Columns.Count; i++) { //Beware Duplicates if (!table.Columns.Contains(Second.Columns[i].ColumnName)) { table.Columns.Add(Second.Columns[i].ColumnName, Second.Columns[i].DataType); } else { table.Columns.Add(Second.Columns[i].ColumnName + "_Second", Second.Columns[i].DataType); } } //Loop through First table table.BeginLoadData(); foreach (DataRow firstrow in ds.Tables[0].Rows) { //Get "joined" rows DataRow[] childrows = firstrow.GetChildRows(r); if (childrows != null && childrows.Length > 0) { object[] parentarray = firstrow.ItemArray; foreach (DataRow secondrow in childrows) { object[] secondarray = secondrow.ItemArray; object[] joinarray = new object[parentarray.Length + secondarray.Length]; Array.Copy(parentarray, 0, joinarray, 0, parentarray.Length); Array.Copy(secondarray, 0, joinarray, parentarray.Length, secondarray.Length); table.LoadDataRow(joinarray, true); } } } table.EndLoadData(); } return(table); }