EndLoadData() public method

public EndLoadData ( ) : void
return void
Beispiel #1
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 #2
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);
            }
        }
Beispiel #3
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);
            }
        }
Beispiel #4
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 #5
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 #6
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 #7
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 #8
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 #9
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);
                }
            }
        }
        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 #12
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 #13
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 #14
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 #15
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 #16
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;
        }
Beispiel #18
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 #19
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 #20
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 #21
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 #22
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 #23
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 #24
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;
		}
        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;
        }
Beispiel #27
0
        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();
            }
        }
Beispiel #28
0
    /// <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;
    }
Beispiel #29
0
    /// <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;
    }
Beispiel #30
0
    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;
    }
Beispiel #31
0
    /// <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;
    }
Beispiel #32
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 #33
0
    /// <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;
    }
Beispiel #34
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);
        }
Beispiel #35
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);
        }
		/// <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();
        }
Beispiel #38
0
    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;
    }
Beispiel #39
0
        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");
        }
Beispiel #40
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;
            }
        }
		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);
        }