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(); }
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); } }
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 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; }
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; 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 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); }
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); } } }
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; }
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 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 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; }
/// <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; }
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); }
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); }
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; }
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); }
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; }
public void Load(string record) { table = new DataTable(); var fields = record.Split(';'); //if > 0 row if (fields.Count() > 0) { //Build structure for (int i = 0; i < fields.Length; i++) Columns.Add(string.Format("Column{0}", i), typeof(string)); //load each row one by one table.BeginLoadData(); //Transform (null) [string] into null for (int i = 0; i < fields.Count(); i++) { if (fields[i] != null && fields[i].ToString().ToLower() == "(null)".ToLower()) fields[i] = null; } table.LoadDataRow(fields, LoadOption.OverwriteChanges); table.EndLoadData(); } }
/// <summary> /// Returns detailed column information for a specified view /// </summary> /// <param name="strCatalog">The catalog to retrieve columns for (can be null)</param> /// <param name="strView">The view to restrict column information by (can be null)</param> /// <param name="strColumn">The source column to restrict column information by (can be null)</param> /// <returns>A DataTable containing the results</returns> private DataTable Schema_ViewColumns(string strCatalog, string strView, string strColumn) { DataTable tbl = new DataTable("ViewColumns"); DataRow row; string strSql; int n; DataRow schemaRow; DataRow viewRow; tbl.Locale = CultureInfo.InvariantCulture; tbl.Columns.Add("VIEW_CATALOG", typeof(string)); tbl.Columns.Add("VIEW_SCHEMA", typeof(string)); tbl.Columns.Add("VIEW_NAME", typeof(string)); tbl.Columns.Add("VIEW_COLUMN_NAME", typeof(String)); tbl.Columns.Add("TABLE_CATALOG", typeof(string)); tbl.Columns.Add("TABLE_SCHEMA", typeof(string)); tbl.Columns.Add("TABLE_NAME", typeof(string)); tbl.Columns.Add("COLUMN_NAME", typeof(string)); tbl.Columns.Add("ORDINAL_POSITION", typeof(int)); tbl.Columns.Add("COLUMN_HASDEFAULT", typeof(bool)); tbl.Columns.Add("COLUMN_DEFAULT", typeof(string)); tbl.Columns.Add("COLUMN_FLAGS", typeof(long)); tbl.Columns.Add("IS_NULLABLE", typeof(bool)); tbl.Columns.Add("DATA_TYPE", typeof(string)); tbl.Columns.Add("CHARACTER_MAXIMUM_LENGTH", typeof(int)); tbl.Columns.Add("NUMERIC_PRECISION", typeof(int)); tbl.Columns.Add("NUMERIC_SCALE", typeof(int)); tbl.Columns.Add("DATETIME_PRECISION", typeof(long)); tbl.Columns.Add("CHARACTER_SET_CATALOG", typeof(string)); tbl.Columns.Add("CHARACTER_SET_SCHEMA", typeof(string)); tbl.Columns.Add("CHARACTER_SET_NAME", typeof(string)); tbl.Columns.Add("COLLATION_CATALOG", typeof(string)); tbl.Columns.Add("COLLATION_SCHEMA", typeof(string)); tbl.Columns.Add("COLLATION_NAME", typeof(string)); tbl.Columns.Add("PRIMARY_KEY", typeof(bool)); tbl.Columns.Add("EDM_TYPE", typeof(string)); tbl.Columns.Add("AUTOINCREMENT", typeof(bool)); tbl.Columns.Add("UNIQUE", typeof(bool)); if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main"; string master = (String.Compare(strCatalog, "temp", true, CultureInfo.InvariantCulture) == 0) ? _tempmasterdb : _masterdb; tbl.BeginLoadData(); using (SqliteCommand cmdViews = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}] WHERE [type] LIKE 'view'", strCatalog, master), this)) using (SqliteDataReader rdViews = cmdViews.ExecuteReader()) { while (rdViews.Read()) { if (String.IsNullOrEmpty(strView) || String.Compare(strView, rdViews.GetString(2), true, CultureInfo.InvariantCulture) == 0) { using (SqliteCommand cmdViewSelect = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}]", strCatalog, rdViews.GetString(2)), this)) { strSql = rdViews.GetString(4).Replace('\r', ' ').Replace('\n', ' ').Replace('\t', ' '); n = CultureInfo.InvariantCulture.CompareInfo.IndexOf(strSql, " AS ", CompareOptions.IgnoreCase); if (n < 0) continue; strSql = strSql.Substring(n + 4); using (SqliteCommand cmd = new SqliteCommand(strSql, this)) using (SqliteDataReader rdViewSelect = cmdViewSelect.ExecuteReader(CommandBehavior.SchemaOnly)) using (SqliteDataReader rd = (SqliteDataReader)cmd.ExecuteReader(CommandBehavior.SchemaOnly)) using (DataTable tblSchemaView = rdViewSelect.GetSchemaTable(false, false)) using (DataTable tblSchema = rd.GetSchemaTable(false, false)) { for (n = 0; n < tblSchema.Rows.Count; n++) { viewRow = tblSchemaView.Rows[n]; schemaRow = tblSchema.Rows[n]; if (String.Compare(viewRow[SchemaTableColumn.ColumnName].ToString(), strColumn, true, CultureInfo.InvariantCulture) == 0 || strColumn == null) { row = tbl.NewRow(); row["VIEW_CATALOG"] = strCatalog; row["VIEW_NAME"] = rdViews.GetString(2); row["TABLE_CATALOG"] = strCatalog; row["TABLE_SCHEMA"] = schemaRow[SchemaTableColumn.BaseSchemaName]; row["TABLE_NAME"] = schemaRow[SchemaTableColumn.BaseTableName]; row["COLUMN_NAME"] = schemaRow[SchemaTableColumn.BaseColumnName]; row["VIEW_COLUMN_NAME"] = viewRow[SchemaTableColumn.ColumnName]; row["COLUMN_HASDEFAULT"] = (viewRow[SchemaTableOptionalColumn.DefaultValue] != DBNull.Value); row["COLUMN_DEFAULT"] = viewRow[SchemaTableOptionalColumn.DefaultValue]; row["ORDINAL_POSITION"] = viewRow[SchemaTableColumn.ColumnOrdinal]; row["IS_NULLABLE"] = viewRow[SchemaTableColumn.AllowDBNull]; row["DATA_TYPE"] = viewRow["DataTypeName"]; // SqliteConvert.DbTypeToType((DbType)viewRow[SchemaTableColumn.ProviderType]).ToString(); row["EDM_TYPE"] = SqliteConvert.DbTypeToTypeName((DbType)viewRow[SchemaTableColumn.ProviderType]).ToString().ToLower(CultureInfo.InvariantCulture); row["CHARACTER_MAXIMUM_LENGTH"] = viewRow[SchemaTableColumn.ColumnSize]; row["TABLE_SCHEMA"] = viewRow[SchemaTableColumn.BaseSchemaName]; row["PRIMARY_KEY"] = viewRow[SchemaTableColumn.IsKey]; row["AUTOINCREMENT"] = viewRow[SchemaTableOptionalColumn.IsAutoIncrement]; row["COLLATION_NAME"] = viewRow["CollationType"]; row["UNIQUE"] = viewRow[SchemaTableColumn.IsUnique]; tbl.Rows.Add(row); } } } } } } } tbl.EndLoadData(); tbl.AcceptChanges(); return tbl; }
/// <summary> /// Retrieves foreign key information from the specified set of filters /// </summary> /// <param name="strCatalog">An optional catalog to restrict results on</param> /// <param name="strTable">An optional table to restrict results on</param> /// <param name="strKeyName">An optional foreign key name to restrict results on</param> /// <returns>A DataTable with the results of the query</returns> private DataTable Schema_ForeignKeys(string strCatalog, string strTable, string strKeyName) { DataTable tbl = new DataTable("ForeignKeys"); DataRow row; tbl.Locale = CultureInfo.InvariantCulture; tbl.Columns.Add("CONSTRAINT_CATALOG", typeof(string)); tbl.Columns.Add("CONSTRAINT_SCHEMA", typeof(string)); tbl.Columns.Add("CONSTRAINT_NAME", typeof(string)); tbl.Columns.Add("TABLE_CATALOG", typeof(string)); tbl.Columns.Add("TABLE_SCHEMA", typeof(string)); tbl.Columns.Add("TABLE_NAME", typeof(string)); tbl.Columns.Add("CONSTRAINT_TYPE", typeof(string)); tbl.Columns.Add("IS_DEFERRABLE", typeof(bool)); tbl.Columns.Add("INITIALLY_DEFERRED", typeof(bool)); tbl.Columns.Add("FKEY_FROM_COLUMN", typeof(string)); tbl.Columns.Add("FKEY_FROM_ORDINAL_POSITION", typeof(int)); tbl.Columns.Add("FKEY_TO_CATALOG", typeof(string)); tbl.Columns.Add("FKEY_TO_SCHEMA", typeof(string)); tbl.Columns.Add("FKEY_TO_TABLE", typeof(string)); tbl.Columns.Add("FKEY_TO_COLUMN", typeof(string)); if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main"; string master = (String.Compare(strCatalog, "temp", true, CultureInfo.InvariantCulture) == 0) ? _tempmasterdb : _masterdb; tbl.BeginLoadData(); using (SqliteCommand cmdTables = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}] WHERE [type] LIKE 'table'", strCatalog, master), this)) using (SqliteDataReader rdTables = cmdTables.ExecuteReader()) { while (rdTables.Read()) { if (String.IsNullOrEmpty(strTable) || String.Compare(strTable, rdTables.GetString(2), true, CultureInfo.InvariantCulture) == 0) { try { using (SqliteCommandBuilder builder = new SqliteCommandBuilder()) //using (SqliteCommand cmdTable = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}]", strCatalog, rdTables.GetString(2)), this)) //using (SqliteDataReader rdTable = cmdTable.ExecuteReader(CommandBehavior.SchemaOnly)) using (SqliteCommand cmdKey = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].foreign_key_list([{1}])", strCatalog, rdTables.GetString(2)), this)) using (SqliteDataReader rdKey = cmdKey.ExecuteReader()) { while (rdKey.Read()) { row = tbl.NewRow(); row["CONSTRAINT_CATALOG"] = strCatalog; row["CONSTRAINT_NAME"] = String.Format(CultureInfo.InvariantCulture, "FK_{0}_{1}", rdTables[2], rdKey.GetInt32(0)); row["TABLE_CATALOG"] = strCatalog; row["TABLE_NAME"] = builder.UnquoteIdentifier(rdTables.GetString(2)); row["CONSTRAINT_TYPE"] = "FOREIGN KEY"; row["IS_DEFERRABLE"] = false; row["INITIALLY_DEFERRED"] = false; row["FKEY_FROM_COLUMN"] = builder.UnquoteIdentifier(rdKey[3].ToString()); row["FKEY_TO_CATALOG"] = strCatalog; row["FKEY_TO_TABLE"] = builder.UnquoteIdentifier(rdKey[2].ToString()); row["FKEY_TO_COLUMN"] = builder.UnquoteIdentifier(rdKey[4].ToString()); row["FKEY_FROM_ORDINAL_POSITION"] = rdKey[1]; if (String.IsNullOrEmpty(strKeyName) || String.Compare(strKeyName, row["CONSTRAINT_NAME"].ToString(), true, CultureInfo.InvariantCulture) == 0) tbl.Rows.Add(row); } } } catch (SqliteException) { } } } } tbl.EndLoadData(); tbl.AcceptChanges(); return tbl; }
private DataTable Schema_DataTypes() { DataTable tbl = new DataTable("DataTypes"); tbl.Locale = CultureInfo.InvariantCulture; tbl.Columns.Add("TypeName", typeof(String)); tbl.Columns.Add("ProviderDbType", typeof(int)); tbl.Columns.Add("ColumnSize", typeof(long)); tbl.Columns.Add("CreateFormat", typeof(String)); tbl.Columns.Add("CreateParameters", typeof(String)); tbl.Columns.Add("DataType", typeof(String)); tbl.Columns.Add("IsAutoIncrementable", typeof(bool)); tbl.Columns.Add("IsBestMatch", typeof(bool)); tbl.Columns.Add("IsCaseSensitive", typeof(bool)); tbl.Columns.Add("IsFixedLength", typeof(bool)); tbl.Columns.Add("IsFixedPrecisionScale", typeof(bool)); tbl.Columns.Add("IsLong", typeof(bool)); tbl.Columns.Add("IsNullable", typeof(bool)); tbl.Columns.Add("IsSearchable", typeof(bool)); tbl.Columns.Add("IsSearchableWithLike", typeof(bool)); tbl.Columns.Add("IsLiteralSupported", typeof(bool)); tbl.Columns.Add("LiteralPrefix", typeof(String)); tbl.Columns.Add("LiteralSuffix", typeof(String)); tbl.Columns.Add("IsUnsigned", typeof(bool)); tbl.Columns.Add("MaximumScale", typeof(short)); tbl.Columns.Add("MinimumScale", typeof(short)); tbl.Columns.Add("IsConcurrencyType", typeof(bool)); tbl.BeginLoadData(); StringReader reader = new StringReader(SR.DataTypes); tbl.ReadXml(reader); reader.Close(); tbl.AcceptChanges(); tbl.EndLoadData(); return tbl; }
/// <summary> /// Returns the base column information for indexes in a database /// </summary> /// <param name="strCatalog">The catalog to retrieve indexes for (can be null)</param> /// <param name="strTable">The table to restrict index information by (can be null)</param> /// <param name="strIndex">The index to restrict index information by (can be null)</param> /// <param name="strColumn">The source column to restrict index information by (can be null)</param> /// <returns>A DataTable containing the results</returns> private DataTable Schema_IndexColumns(string strCatalog, string strTable, string strIndex, string strColumn) { DataTable tbl = new DataTable("IndexColumns"); DataRow row; List<KeyValuePair<int, string>> primaryKeys = new List<KeyValuePair<int, string>>(); bool maybeRowId; tbl.Locale = CultureInfo.InvariantCulture; tbl.Columns.Add("CONSTRAINT_CATALOG", typeof(string)); tbl.Columns.Add("CONSTRAINT_SCHEMA", typeof(string)); tbl.Columns.Add("CONSTRAINT_NAME", typeof(string)); tbl.Columns.Add("TABLE_CATALOG", typeof(string)); tbl.Columns.Add("TABLE_SCHEMA", typeof(string)); tbl.Columns.Add("TABLE_NAME", typeof(string)); tbl.Columns.Add("COLUMN_NAME", typeof(string)); tbl.Columns.Add("ORDINAL_POSITION", typeof(int)); tbl.Columns.Add("INDEX_NAME", typeof(string)); tbl.Columns.Add("COLLATION_NAME", typeof(string)); tbl.Columns.Add("SORT_MODE", typeof(string)); tbl.Columns.Add("CONFLICT_OPTION", typeof(int)); if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main"; string master = (String.Compare(strCatalog, "temp", true, CultureInfo.InvariantCulture) == 0) ? _tempmasterdb : _masterdb; tbl.BeginLoadData(); using (SqliteCommand cmdTables = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}] WHERE [type] LIKE 'table'", strCatalog, master), this)) using (SqliteDataReader rdTables = cmdTables.ExecuteReader()) { while (rdTables.Read()) { maybeRowId = false; primaryKeys.Clear(); if (String.IsNullOrEmpty(strTable) || String.Compare(rdTables.GetString(2), strTable, true, CultureInfo.InvariantCulture) == 0) { try { using (SqliteCommand cmdTable = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].table_info([{1}])", strCatalog, rdTables.GetString(2)), this)) using (SqliteDataReader rdTable = cmdTable.ExecuteReader()) { while (rdTable.Read()) { if (rdTable.GetInt32(5) == 1) // is a primary key { primaryKeys.Add(new KeyValuePair<int, string>(rdTable.GetInt32(0), rdTable.GetString(1))); // Is an integer -- could be a rowid if no other primary keys exist in the table if (String.Compare(rdTable.GetString(2), "INTEGER", true, CultureInfo.InvariantCulture) == 0) maybeRowId = true; } } } } catch (SqliteException) { } // This is a rowid row if (primaryKeys.Count == 1 && maybeRowId == true) { row = tbl.NewRow(); row["CONSTRAINT_CATALOG"] = strCatalog; row["CONSTRAINT_NAME"] = String.Format(CultureInfo.InvariantCulture, "{1}_PK_{0}", rdTables.GetString(2), master); row["TABLE_CATALOG"] = strCatalog; row["TABLE_NAME"] = rdTables.GetString(2); row["COLUMN_NAME"] = primaryKeys[0].Value; row["INDEX_NAME"] = row["CONSTRAINT_NAME"]; row["ORDINAL_POSITION"] = 0; // primaryKeys[0].Key; row["COLLATION_NAME"] = "BINARY"; row["SORT_MODE"] = "ASC"; row["CONFLICT_OPTION"] = 2; if (String.IsNullOrEmpty(strIndex) || String.Compare(strIndex, (string)row["INDEX_NAME"], true, CultureInfo.InvariantCulture) == 0) tbl.Rows.Add(row); } using (SqliteCommand cmdIndexes = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{2}] WHERE [type] LIKE 'index' AND [tbl_name] LIKE '{1}'", strCatalog, rdTables.GetString(2).Replace("'", "''"), master), this)) using (SqliteDataReader rdIndexes = cmdIndexes.ExecuteReader()) { while (rdIndexes.Read()) { int ordinal = 0; if (String.IsNullOrEmpty(strIndex) || String.Compare(strIndex, rdIndexes.GetString(1), true, CultureInfo.InvariantCulture) == 0) { try { using (SqliteCommand cmdIndex = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].index_info([{1}])", strCatalog, rdIndexes.GetString(1)), this)) using (SqliteDataReader rdIndex = cmdIndex.ExecuteReader()) { while (rdIndex.Read()) { row = tbl.NewRow(); row["CONSTRAINT_CATALOG"] = strCatalog; row["CONSTRAINT_NAME"] = rdIndexes.GetString(1); row["TABLE_CATALOG"] = strCatalog; row["TABLE_NAME"] = rdIndexes.GetString(2); row["COLUMN_NAME"] = rdIndex.GetString(2); row["INDEX_NAME"] = rdIndexes.GetString(1); row["ORDINAL_POSITION"] = ordinal; // rdIndex.GetInt32(1); string collationSequence; int sortMode; int onError; _sql.GetIndexColumnExtendedInfo(strCatalog, rdIndexes.GetString(1), rdIndex.GetString(2), out sortMode, out onError, out collationSequence); if (String.IsNullOrEmpty(collationSequence) == false) row["COLLATION_NAME"] = collationSequence; row["SORT_MODE"] = (sortMode == 0) ? "ASC" : "DESC"; row["CONFLICT_OPTION"] = onError; ordinal++; if (String.IsNullOrEmpty(strColumn) || String.Compare(strColumn, row["COLUMN_NAME"].ToString(), true, CultureInfo.InvariantCulture) == 0) tbl.Rows.Add(row); } } } catch (SqliteException) { } } } } } } } tbl.EndLoadData(); tbl.AcceptChanges(); return tbl; }
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); }
/// <summary> /// Retrieves catalog (attached databases) schema information for the database /// </summary> /// <param name="strCatalog">The catalog to retrieve, can be null</param> /// <returns>DataTable</returns> private DataTable Schema_Catalogs(string strCatalog) { DataTable tbl = new DataTable("Catalogs"); DataRow row; tbl.Locale = CultureInfo.InvariantCulture; tbl.Columns.Add("CATALOG_NAME", typeof(string)); tbl.Columns.Add("DESCRIPTION", typeof(string)); tbl.Columns.Add("ID", typeof(long)); tbl.BeginLoadData(); using (SqliteCommand cmd = new SqliteCommand("PRAGMA database_list", this)) using (SqliteDataReader rd = (SqliteDataReader)cmd.ExecuteReader()) { while (rd.Read()) { if (String.Compare(rd.GetString(1), strCatalog, true, CultureInfo.InvariantCulture) == 0 || strCatalog == null) { row = tbl.NewRow(); row["CATALOG_NAME"] = rd.GetString(1); row["DESCRIPTION"] = rd.GetString(2); row["ID"] = rd.GetInt64(0); tbl.Rows.Add(row); } } } tbl.AcceptChanges(); tbl.EndLoadData(); return tbl; }
/// <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); }
/// <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); }
/// <include file='Doc/en_EN/FbDataReader.xml' path='doc/class[@name="FbDataReader"]/method[@name="GetSchemaTable"]/*'/> public DataTable GetSchemaTable() { this.CheckState(); if (this.schemaTable != null) { return this.schemaTable; } DataRow schemaRow; this.schemaTable = this.GetSchemaTableStructure(); /* Prepare statement for schema fields information */ FbCommand schemaCmd = new FbCommand( this.GetSchemaCommandText(), this.command.Connection, this.command.ActiveTransaction); schemaCmd.Parameters.Add("@TABLE_NAME", FbDbType.Char, 31); schemaCmd.Parameters.Add("@COLUMN_NAME", FbDbType.Char, 31); schemaCmd.Prepare(); schemaTable.BeginLoadData(); for (int i = 0; i < this.fields.Count; i++) { bool isKeyColumn = false; bool isUnique = false; bool isReadOnly = false; int precision = 0; if (!this.fields[i].IsExpression()) { /* Get Schema data for the field */ schemaCmd.Parameters[0].Value = this.fields[i].Relation; schemaCmd.Parameters[1].Value = this.fields[i].Name; FbDataReader r = schemaCmd.ExecuteReader(); if (r.Read()) { isReadOnly = (this.IsReadOnly(r) || this.fields[i].IsExpression()) ? true : false; isKeyColumn = (r.GetInt32(2) == 1) ? true : false; isUnique = (r.GetInt32(3) == 1) ? true : false; precision = r.IsDBNull(4) ? -1 : r.GetInt32(4); } /* Close the Reader */ r.Close(); } /* Create new row for the Schema Table */ schemaRow = schemaTable.NewRow(); schemaRow["ColumnName"] = this.GetName(i); schemaRow["ColumnOrdinal"] = i; schemaRow["ColumnSize"] = this.fields[i].GetSize(); if (fields[i].IsDecimal()) { schemaRow["NumericPrecision"] = schemaRow["ColumnSize"]; if (precision > 0) { schemaRow["NumericPrecision"] = precision; } schemaRow["NumericScale"] = this.fields[i].NumericScale * (-1); } schemaRow["DataType"] = this.GetFieldType(i); schemaRow["ProviderType"] = this.GetProviderType(i); schemaRow["IsLong"] = this.fields[i].IsLong(); schemaRow["AllowDBNull"] = this.fields[i].AllowDBNull(); schemaRow["IsRowVersion"] = false; schemaRow["IsAutoIncrement"] = false; schemaRow["IsReadOnly"] = isReadOnly; schemaRow["IsKey"] = isKeyColumn; schemaRow["IsUnique"] = isUnique; schemaRow["IsAliased"] = this.fields[i].IsAliased(); schemaRow["IsExpression"] = this.fields[i].IsExpression(); schemaRow["BaseSchemaName"] = DBNull.Value; schemaRow["BaseCatalogName"] = DBNull.Value; schemaRow["BaseTableName"] = this.fields[i].Relation; schemaRow["BaseColumnName"] = this.fields[i].Name; schemaTable.Rows.Add(schemaRow); /* Close statement */ schemaCmd.Close(); } schemaTable.EndLoadData(); /* Dispose command */ schemaCmd.Dispose(); return schemaTable; }
/// <summary> /// Executes the SQL text and loads the results into the specified data table. /// </summary> public void ExecuteSelect(DataTable data) { if (data == null) throw new ArgumentNullException("data"); ExecuteSelect(); data.BeginLoadData(); data.Load(sqlReader); data.EndLoadData(); }
internal DataTable GetSchemaTable(bool wantUniqueInfo, bool wantDefaultValue) { CheckClosed(); DataTable tbl = new DataTable("SchemaTable"); DataTable tblIndexes = null; DataTable tblIndexColumns; DataRow row; string temp; string strCatalog = ""; string strTable = ""; string strColumn = ""; tbl.Locale = CultureInfo.InvariantCulture; tbl.Columns.Add(SchemaTableColumn.ColumnName, typeof(String)); tbl.Columns.Add(SchemaTableColumn.ColumnOrdinal, typeof(int)); tbl.Columns.Add(SchemaTableColumn.ColumnSize, typeof(int)); tbl.Columns.Add(SchemaTableColumn.NumericPrecision, typeof(short)); tbl.Columns.Add(SchemaTableColumn.NumericScale, typeof(short)); tbl.Columns.Add(SchemaTableColumn.IsUnique, typeof(Boolean)); tbl.Columns.Add(SchemaTableColumn.IsKey, typeof(Boolean)); tbl.Columns.Add(SchemaTableOptionalColumn.BaseServerName, typeof(string)); tbl.Columns.Add(SchemaTableOptionalColumn.BaseCatalogName, typeof(String)); tbl.Columns.Add(SchemaTableColumn.BaseColumnName, typeof(String)); tbl.Columns.Add(SchemaTableColumn.BaseSchemaName, typeof(String)); tbl.Columns.Add(SchemaTableColumn.BaseTableName, typeof(String)); tbl.Columns.Add(SchemaTableColumn.DataType, typeof(Type)); tbl.Columns.Add(SchemaTableColumn.AllowDBNull, typeof(Boolean)); tbl.Columns.Add(SchemaTableColumn.ProviderType, typeof(int)); tbl.Columns.Add(SchemaTableColumn.IsAliased, typeof(Boolean)); tbl.Columns.Add(SchemaTableColumn.IsExpression, typeof(Boolean)); tbl.Columns.Add(SchemaTableOptionalColumn.IsAutoIncrement, typeof(Boolean)); tbl.Columns.Add(SchemaTableOptionalColumn.IsRowVersion, typeof(Boolean)); tbl.Columns.Add(SchemaTableOptionalColumn.IsHidden, typeof(Boolean)); tbl.Columns.Add(SchemaTableColumn.IsLong, typeof(Boolean)); tbl.Columns.Add(SchemaTableOptionalColumn.IsReadOnly, typeof(Boolean)); tbl.Columns.Add(SchemaTableOptionalColumn.ProviderSpecificDataType, typeof(Type)); tbl.Columns.Add(SchemaTableOptionalColumn.DefaultValue, typeof(object)); tbl.Columns.Add("DataTypeName", typeof(string)); tbl.Columns.Add("CollationType", typeof(string)); tbl.BeginLoadData(); for (int n = 0; n < _fieldCount; n++) { row = tbl.NewRow(); DbType typ = GetSQLiteType(n).Type; // Default settings for the column row[SchemaTableColumn.ColumnName] = GetName(n); row[SchemaTableColumn.ColumnOrdinal] = n; row[SchemaTableColumn.ColumnSize] = SqliteConvert.DbTypeToColumnSize(typ); row[SchemaTableColumn.NumericPrecision] = SqliteConvert.DbTypeToNumericPrecision(typ); row[SchemaTableColumn.NumericScale] = SqliteConvert.DbTypeToNumericScale(typ); row[SchemaTableColumn.ProviderType] = GetSQLiteType(n).Type; row[SchemaTableColumn.IsLong] = false; row[SchemaTableColumn.AllowDBNull] = true; row[SchemaTableOptionalColumn.IsReadOnly] = false; row[SchemaTableOptionalColumn.IsRowVersion] = false; row[SchemaTableColumn.IsUnique] = false; row[SchemaTableColumn.IsKey] = false; row[SchemaTableOptionalColumn.IsAutoIncrement] = false; row[SchemaTableColumn.DataType] = GetFieldType(n); row[SchemaTableOptionalColumn.IsHidden] = false; #if !MONOTOUCH strColumn = _command.Connection._sql.ColumnOriginalName(_activeStatement, n); if (String.IsNullOrEmpty(strColumn) == false) row[SchemaTableColumn.BaseColumnName] = strColumn; row[SchemaTableColumn.IsExpression] = String.IsNullOrEmpty(strColumn); row[SchemaTableColumn.IsAliased] = (String.Compare(GetName(n), strColumn, true, CultureInfo.InvariantCulture) != 0); temp = _command.Connection._sql.ColumnTableName(_activeStatement, n); if (String.IsNullOrEmpty(temp) == false) row[SchemaTableColumn.BaseTableName] = temp; temp = _command.Connection._sql.ColumnDatabaseName(_activeStatement, n); if (String.IsNullOrEmpty(temp) == false) row[SchemaTableOptionalColumn.BaseCatalogName] = temp; #endif string dataType = null; // If we have a table-bound column, extract the extra information from it if (String.IsNullOrEmpty(strColumn) == false) { string collSeq; bool bNotNull; bool bPrimaryKey; bool bAutoIncrement; string[] arSize; // Get the column meta data _command.Connection._sql.ColumnMetaData( (string)row[SchemaTableOptionalColumn.BaseCatalogName], (string)row[SchemaTableColumn.BaseTableName], strColumn, out dataType, out collSeq, out bNotNull, out bPrimaryKey, out bAutoIncrement); if (bNotNull || bPrimaryKey) row[SchemaTableColumn.AllowDBNull] = false; row[SchemaTableColumn.IsKey] = bPrimaryKey; row[SchemaTableOptionalColumn.IsAutoIncrement] = bAutoIncrement; row["CollationType"] = collSeq; // For types like varchar(50) and such, extract the size arSize = dataType.Split('('); if (arSize.Length > 1) { dataType = arSize[0]; arSize = arSize[1].Split(')'); if (arSize.Length > 1) { arSize = arSize[0].Split(',', '.'); if (GetSQLiteType(n).Type == DbType.String || GetSQLiteType(n).Type == DbType.Binary) { row[SchemaTableColumn.ColumnSize] = Convert.ToInt32(arSize[0], CultureInfo.InvariantCulture); } else { row[SchemaTableColumn.NumericPrecision] = Convert.ToInt32(arSize[0], CultureInfo.InvariantCulture); if (arSize.Length > 1) row[SchemaTableColumn.NumericScale] = Convert.ToInt32(arSize[1], CultureInfo.InvariantCulture); } } } if (wantDefaultValue) { // Determine the default value for the column, which sucks because we have to query the schema for each column using (SqliteCommand cmdTable = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].TABLE_INFO([{1}])", row[SchemaTableOptionalColumn.BaseCatalogName], row[SchemaTableColumn.BaseTableName] ), _command.Connection)) using (DbDataReader rdTable = cmdTable.ExecuteReader()) { // Find the matching column while (rdTable.Read()) { if (String.Compare((string)row[SchemaTableColumn.BaseColumnName], rdTable.GetString(1), true, CultureInfo.InvariantCulture) == 0) { if (rdTable.IsDBNull(4) == false) row[SchemaTableOptionalColumn.DefaultValue] = rdTable[4]; break; } } } } // Determine IsUnique properly, which is a pain in the butt! if (wantUniqueInfo) { if ((string)row[SchemaTableOptionalColumn.BaseCatalogName] != strCatalog || (string)row[SchemaTableColumn.BaseTableName] != strTable) { strCatalog = (string)row[SchemaTableOptionalColumn.BaseCatalogName]; strTable = (string)row[SchemaTableColumn.BaseTableName]; tblIndexes = _command.Connection.GetSchema("Indexes", new string[] { (string)row[SchemaTableOptionalColumn.BaseCatalogName], null, (string)row[SchemaTableColumn.BaseTableName], null }); } foreach (DataRow rowIndexes in tblIndexes.Rows) { tblIndexColumns = _command.Connection.GetSchema("IndexColumns", new string[] { (string)row[SchemaTableOptionalColumn.BaseCatalogName], null, (string)row[SchemaTableColumn.BaseTableName], (string)rowIndexes["INDEX_NAME"], null }); foreach (DataRow rowColumnIndex in tblIndexColumns.Rows) { if (String.Compare((string)rowColumnIndex["COLUMN_NAME"], strColumn, true, CultureInfo.InvariantCulture) == 0) { if (tblIndexColumns.Rows.Count == 1 && (bool)row[SchemaTableColumn.AllowDBNull] == false) row[SchemaTableColumn.IsUnique] = rowIndexes["UNIQUE"]; // If its an integer primary key and the only primary key in the table, then its a rowid alias and is autoincrement // NOTE: Currently commented out because this is not always the desired behavior. For example, a 1:1 relationship with // another table, where the other table is autoincrement, but this one is not, and uses the rowid from the other. // It is safer to only set Autoincrement on tables where we're SURE the user specified AUTOINCREMENT, even if its a rowid column. if (tblIndexColumns.Rows.Count == 1 && (bool)rowIndexes["PRIMARY_KEY"] == true && String.IsNullOrEmpty(dataType) == false && String.Compare(dataType, "integer", true, CultureInfo.InvariantCulture) == 0) { // row[SchemaTableOptionalColumn.IsAutoIncrement] = true; } break; } } } } if (String.IsNullOrEmpty(dataType)) { TypeAffinity affin; dataType = _activeStatement._sql.ColumnType(_activeStatement, n, out affin); } if (String.IsNullOrEmpty(dataType) == false) row["DataTypeName"] = dataType; } tbl.Rows.Add(row); } if (_keyInfo != null) _keyInfo.AppendSchemaTable(tbl); tbl.AcceptChanges(); tbl.EndLoadData(); return tbl; }
public void TestBulkLoad_DataTableWithStateNoMapping() { CreateTargetForBulkLoad(); NuoDbBulkLoader loader = new NuoDbBulkLoader(connectionString); loader.BatchSize = 2; loader.DestinationTableName = "TEMP"; DataTable metadata = new DataTable("dummy"); metadata.Columns.Add("xyz", typeof(string)); const int ROW_TO_ADD = 10; metadata.BeginLoadData(); for (int i = 0; i < ROW_TO_ADD; i++) { DataRow row = metadata.NewRow(); row[0] = Convert.ToString(i); metadata.Rows.Add(row); } metadata.EndLoadData(); metadata.AcceptChanges(); metadata.Rows[ROW_TO_ADD / 2].BeginEdit(); metadata.Rows[ROW_TO_ADD / 2][0] = "999"; metadata.Rows[ROW_TO_ADD / 2].EndEdit(); loader.WriteToServer(metadata, DataRowState.Modified); VerifyBulkLoad(1, "999"); }
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; } }
private DataTable readWholeWorkSheet(XlsWorksheet sheet) { XlsBiffIndex idx; if (!readWorkSheetGlobals(sheet, out idx)) return null; DataTable table = new DataTable(sheet.Name); bool triggerCreateColumns = true; m_dbCellAddrs = idx.DbCellAddresses; for (int index = 0; index < m_dbCellAddrs.Length; index++) { if (m_depht == m_maxRow) break; // init reading data m_cellOffset = findFirstDataCellOffset((int)m_dbCellAddrs[index]); //DataTable columns if (triggerCreateColumns) { if (_isFirstRowAsColumnNames && readWorkSheetRow() || (_isFirstRowAsColumnNames && m_maxRow == 1)) { for (int i = 0; i < m_maxCol; i++) { if (m_cellsValues[i] != null && m_cellsValues[i].ToString().Length > 0) table.Columns.Add(m_cellsValues[i].ToString()); else table.Columns.Add(string.Concat(COLUMN, i)); } } else { for (int i = 0; i < m_maxCol; i++) { table.Columns.Add(); } } triggerCreateColumns = false; table.BeginLoadData(); } while (readWorkSheetRow()) { table.Rows.Add(m_cellsValues); } if (m_depht > 0 && !(_isFirstRowAsColumnNames && m_maxRow == 1)) table.Rows.Add(m_cellsValues); } 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; }
protected override DataTable ProcessResult(DataTable schema) { schema.BeginLoadData(); schema.Columns.Add("IS_PRIMARY", typeof(bool)); foreach (DataRow row in schema.Rows) { row["IS_UNIQUE"] = !(row["IS_UNIQUE"] == DBNull.Value || Convert.ToInt32(row["IS_UNIQUE"], CultureInfo.InvariantCulture) == 0); row["IS_PRIMARY"] = !(row["PRIMARY_KEY"] == DBNull.Value || Convert.ToInt32(row["PRIMARY_KEY"], CultureInfo.InvariantCulture) == 0); row["IS_INACTIVE"] = !(row["IS_INACTIVE"] == DBNull.Value || Convert.ToInt32(row["IS_INACTIVE"], CultureInfo.InvariantCulture) == 0); row["IS_SYSTEM_INDEX"] = !(row["IS_SYSTEM_INDEX"] == DBNull.Value || Convert.ToInt32(row["IS_SYSTEM_INDEX"], CultureInfo.InvariantCulture) == 0); } schema.EndLoadData(); schema.AcceptChanges(); schema.Columns.Remove("PRIMARY_KEY"); return schema; }
/// <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); }