BeginLoadData() public method

public BeginLoadData ( ) : void
return void
Beispiel #1
0
        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;
        }
Beispiel #2
0
		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;
		}
Beispiel #3
0
    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;
            }
    }
Beispiel #4
0
        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;
        }
Beispiel #6
0
 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);
 }
Beispiel #7
0
        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;
		}
Beispiel #9
0
 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;
 }
Beispiel #10
0
        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;
        }
Beispiel #11
0
        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();
        }
Beispiel #12
0
        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);
                }
            }
        }
Beispiel #13
0
        /// <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;
     }
 }
Beispiel #16
0
        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;
        }
Beispiel #17
0
        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();
        }
Beispiel #18
0
        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();
        }
Beispiel #19
0
        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;
        }
Beispiel #20
0
        //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
Beispiel #21
0
        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;
        }
Beispiel #22
0
        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>();
        }
Beispiel #25
0
        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;
        }
Beispiel #26
0
        /// <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;
        }
Beispiel #27
0
    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;
    }
Beispiel #28
0
        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("Результаты измерений сохранены");
        }
Beispiel #29
0
        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);
        }
Beispiel #30
0
        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;
        }
Beispiel #33
0
        /// <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;
        }
Beispiel #34
0
		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;
		}
Beispiel #35
0
        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);
        }
Beispiel #39
0
        /// <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);
        }
Beispiel #41
0
        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);
        }
Beispiel #42
0
        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;
            }
        }
Beispiel #43
0
        /// <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);
        }
Beispiel #44
0
        /// <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);
        }
Beispiel #45
0
        /// <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;
        }
Beispiel #47
0
        /// <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);
        }
Beispiel #48
0
        //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);
        }