public void CreateDT()
        {
            DataTable table = new DataTable();

            table.Columns.Add(new DataColumn("Id", typeof(int)));
            table.Columns.Add(new DataColumn("Name"));
            table.Columns.Add(new DataColumn("Price", typeof(float)));

            string[] names = {"Dima","Vanyz","Kolyan","Anton","Maks","Oleg","Volodya","Misha","Sasha","Roma"};
            AddNewRow(table,names);

            Console.WriteLine("\n");
               var task1 = table.AsEnumerable().Where(x => (int)x["Id"] > 3).ToList();
               var task2 = table.AsEnumerable().Where(x => (int)x["Id"] > 4).Select(x => x["Price"]).ToArray();
               var  task3 = table.AsEnumerable().OrderBy(x => (float)x["Price"]).ToList();
               var task4 = table.AsEnumerable().Select(x => new { IdField = x["Id"], NameField = x["Name"], PriceField = x["Price"] }).ToList();
               var task5 = table.AsEnumerable().Where(x => (int)x["Id"] > 2 && (int)x["Id"] < 8).OrderByDescending(x => (float)x["Price"]).ToArray();

               foreach (DataRow row in table.Rows)
               {
               foreach (DataColumn column in table.Columns)
                   Console.WriteLine("{0}:{1} ", column.ColumnName, row[column]);

               }

            Console.ReadKey();
        }
Beispiel #2
0
        static void Test()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("ID"));
            dt.Columns.Add(new DataColumn("NAME"));
            dt.Columns.Add(new DataColumn("VALUE"));

            DataRow dr1 = dt.NewRow();
            dr1["ID"] = "01";
            dr1["NAME"] = "张三";
            dr1["VALUE"] = 1024;
            dt.Rows.Add(dr1);

            DataRow dr2 = dt.NewRow();
            dr2["ID"] = "02";
            dr2["NAME"] = "李四";
            dr2["VALUE"] = 1014;
            dt.Rows.Add(dr2);

            DataRow dr3 = dt.NewRow();
            dr3["ID"] = "03";
            dr3["NAME"] = "王五";
            dr3["VALUE"] = 1025;
            dt.Rows.Add(dr3);

            DataRow dr4 = dt.NewRow();
            dr4["ID"] = "04";
            dr4["NAME"] = "赵六";
            dr4["VALUE"] = 1004;
            dt.Rows.Add(dr4);

            // 获取DataTable中某一列的最大最小值
            var maxValue = dt.AsEnumerable().Max(p => { return p["VALUE"]; });
            var minValue = dt.AsEnumerable().Min(p => { return p["VALUE"]; });
        }
        public static DataTable CompareDataTables(DataTable firstDataTable, DataTable secondDataTable, bool isCaseSensitive = false, bool enforceOrder = false)
        {
            firstDataTable.CaseSensitive = isCaseSensitive;
            secondDataTable.CaseSensitive = isCaseSensitive;

            if (enforceOrder)
            {
                firstDataTable.Columns.Add(new DataColumn("CSTestSortOrder", typeof (int)));
                secondDataTable.Columns.Add(new DataColumn("CSTestSortOrder", typeof(int)));

                for (var i = 0; i < firstDataTable.Rows.Count; i++)
                {
                    firstDataTable.Rows[i]["CSTestSortOrder"] = i;
                }
                for (var i = 0; i < secondDataTable.Rows.Count; i++)
                {
                    secondDataTable.Rows[i]["CSTestSortOrder"] = i;
                }
            }

            var differences = firstDataTable.AsEnumerable().Except(secondDataTable.AsEnumerable(), DataRowComparer.Default)
                                .Union(secondDataTable.AsEnumerable().Except(firstDataTable.AsEnumerable(), DataRowComparer.Default));

            var resultDataTable = differences.Any() ? differences.CopyToDataTable() : new DataTable();
            resultDataTable.TableName = "ResultDataTable";

            return resultDataTable;
        }
        private static void PerformBinning(TypeOfNumericProbabilityEnum probabilityEnum, DataTable result, List<int> numericColumns)
        {
            switch (probabilityEnum)
            {
                    case TypeOfNumericProbabilityEnum.Binning:

                    foreach (int numericColumn in numericColumns)
                    {
                        double[] values = result.AsEnumerable().Select(m => double.Parse(m[numericColumn].ToString())).ToArray();
                        double[] bins = IntervalCreation.MakeBins(values, 4);
                        string[] newValues = values.Select(m => IntervalCreation.InBin(m, bins)).ToArray();

                        for (int i = 0; i < result.Rows.Count; i++)
                        {
                            DataRow row = result.Rows[i];
                            row[numericColumn] = newValues[i];
                        }
                    }

                    break;

                    case TypeOfNumericProbabilityEnum.NormalDistribution:

                    string[] targetValues = result.AsEnumerable().Select(m => m[StaticStorage.TargetColum].ToString().Format()).ToArray();
                    string[] targetDistinctValues = targetValues.Distinct().ToArray();

                    List<NormalDistributionValueItem> valueItems = new List<NormalDistributionValueItem>();

                    foreach (int numericColumn in numericColumns)
                    {
                        foreach (string targetDistinctValue in targetDistinctValues)
                        {
                            NormalDistributionValueItem normalDistributionValue = new NormalDistributionValueItem();

                            List<double> values =
                                (from DataRow dataRow in result.Rows
                                    where dataRow[StaticStorage.TargetColum].Equals(targetDistinctValue)
                                    select double.Parse(dataRow[numericColumn].ToString()))
                                    .ToList();

                            normalDistributionValue.ColumnId = numericColumn;
                            normalDistributionValue.TargetValue = targetDistinctValue;
                            normalDistributionValue.Mean = values.Average();
                            normalDistributionValue.Variance =
                                values.Sum(m => Math.Pow(m - normalDistributionValue.Mean, 2))/values.Count - 1;

                            normalDistributionValue.Std = Math.Sqrt(normalDistributionValue.Variance);

                            valueItems.Add(normalDistributionValue);
                        }
                    }

                    StaticStorage.NormalDistributionValueItems = valueItems;
                    break;
                default:
                    throw new ArgumentOutOfRangeException(nameof(probabilityEnum), probabilityEnum, null);
            }
        }
Beispiel #5
0
        static void Main(string[] args)
        {
            DataTable table = new DataTable();
            DataColumn a = new DataColumn("id", typeof(int));
            a.Unique = true;
            table.Columns.Add(a);
            table.Columns.Add("name", typeof(string));
            table.Columns.Add("price", typeof(float));

            Random r = new Random();
            for (int i = 1; i <= 10; i++)
            {
                table.Rows.Add(new object[]{i,i.ToString() + "name", r.Next(10000)/100f});
            }

            IEnumerable<DataRow> q1 = table.AsEnumerable()
                .Where(x => (int)x["id"] > 3);
            Console.WriteLine("q1:");
            foreach (var item in q1)
            {
                Console.WriteLine("id - {0},\t name - {1},\t price - {2}", item[0], item[1], item[2]);
            } Console.ReadKey();

            IEnumerable<float> q2 = table.AsEnumerable()
                .Where(x => (int)x["id"] > 4)
                .Select(x => (float)x["price"]);
            Console.WriteLine("q2:");
            foreach (var item in q2)
            {
                Console.WriteLine(item);
            } Console.ReadKey();

            IEnumerable<float> q3 = table.AsEnumerable()
                .Select(x => (float)x["price"])
                .OrderBy(x => x);
            Console.WriteLine("q3:");
            foreach (var item in q3)
            {
                Console.WriteLine(item);
            } Console.ReadKey();

            var q4 = table.AsEnumerable()
                .Select(x => new { IdField = x["id"], NameField = x["name"], PriceField = x["price"]});
            Console.WriteLine("q4:");
            foreach (var item in q4)
            {
                Console.WriteLine(item);
            } Console.ReadKey();

            IEnumerable<DataRow> q5 = table.AsEnumerable()
                .Where(x => (int)x["id"] > 2 && (int)x["id"] < 8)
                .OrderByDescending(x => (float)x["price"]);
            Console.WriteLine("q5:");
            foreach (var item in q5)
            {
                Console.WriteLine("id - {0},\t name - {1},\t price - {2}", item[0],item[1],item[2]);
            } Console.ReadKey();
        }
        private static DataTable GetPivotTable(DataTable dataTable)
        {
            if (dataTable == null)
            {
                return null;
            }

            if (dataTable.Rows.Count.Equals(0))
            {
                return null;
            }

            const string ITEM_NAME = "ItemName";
            const string item_name = "item_name";
            const string office_code = "office_code";
            const string total_sales = "total_sales";

            using (DataTable table = new DataTable())
            {
                table.Columns.Add(ITEM_NAME);

                var items = dataTable.AsEnumerable().Select(s => s.Field<string>(item_name)).Distinct().ToList();
                var offices = dataTable.AsEnumerable().Select(s => s.Field<string>(office_code)).Distinct().ToList();

                foreach (var office in offices)
                {
                    table.Columns.Add(Conversion.TryCastString(office));
                }

                foreach (var item in items)
                {
                    DataRow row = table.NewRow();
                    string itemName = Conversion.TryCastString(item);
                    row[ITEM_NAME] = itemName;

                    foreach (DataColumn column in table.Columns)
                    {
                        string columnName = column.ColumnName;

                        if (columnName != ITEM_NAME)
                        {
                            decimal results = dataTable.AsEnumerable().
                                Where(r => r.Field<string>(office_code) == columnName
                                    && r.Field<string>(item_name) == itemName)
                           .Select(r => r.Field<decimal>(total_sales))
                           .Sum();

                            row[columnName] = results;
                        }
                    }

                    table.Rows.Add(row);
                }

                return table;
            }
        }
Beispiel #7
0
 private void FindMissingRows(DataTable TableA, DataTable TableB)
 {
     IEnumerable<int> idsNotInB = TableA.AsEnumerable().Select(r => r.Field<int>("id"))
         .Except(TableB.AsEnumerable().Select(r => r.Field<int>("id")));
     DataTable TableC = (from row in TableA.AsEnumerable()
         join id in idsNotInB
             on row.Field<int>("id") equals id
         select row).CopyToDataTable();
 }
        private void btnSearch_Click(object sender, EventArgs e)
        {
            try
            {
                strTestData = "";
                foreach (GridEXRow row in grdStatistics.GetCheckedRows())
                {
                    strTestData += Utility.sDbnull(row.Cells["TestData_Name"].Value) + ",";
                }

                dtResult = SPs.SpGetHivHbsiiHcv(dtpFromDate.Value.Date, dtpToDate.Value.Date.AddDays(1).AddMilliseconds(-1),
                                         Utility.Int16Dbnull(cboSex.SelectedValue),strTestData).GetDataSet().Tables[0];
                if (dtResult.Rows.Count <= 0)
                {
                    Utility.ShowMsg("Không tìm thấy dữ liệu. Chọn lại điều kiện tìm kiếm !");
                    dtResult.Clear();
                    dtResult.AcceptChanges();
                    return;
                }
                grdResult.DataSource = dtResult;

                string sTestName;
                for (int i = 0; i < dtStatistics.Rows.Count; i++)
                {
                    sTestName = Utility.sDbnull(dtStatistics.Rows[i]["TestData_Name"]);
                    dtStatistics.Rows[i]["Negative"] = (from dr in dtResult.AsEnumerable()
                                                        where(dr.Field<object>(sTestName+"_Diagnose") != null) &&
                                                            (dr.Field<string>(sTestName +"_Diagnose") == "Âm tính")
                                                        select dr).Count();
                    dtStatistics.Rows[i]["Positive"] = (from dr in dtResult.AsEnumerable()
                                                        where (dr.Field<object>(sTestName + "_Diagnose") != null) &&
                                                            (dr.Field<string>(sTestName + "_Diagnose") == "Dương tính")
                                                        select dr).Count();
                    dtStatistics.Rows[i]["Total"] = Utility.Int32Dbnull(dtStatistics.Rows[i]["Negative"]) + Utility.Int32Dbnull(dtStatistics.Rows[i]["Positive"]);
                }

                foreach (GridEXRow row in grdStatistics.GetRows())
                {
                    sTestName = Utility.sDbnull(row.Cells["TestData_Name"].Value);
                    grdResult.RootTable.Columns[sTestName].Visible = row.IsChecked;
                    grdResult.RootTable.Columns[sTestName + "_Diagnose"].Visible = row.IsChecked;
                }

                dtStatistics.AcceptChanges();
            }
            catch (Exception ex)
            {
                Utility.ShowMsg(ex.Message);
            }
        }
Beispiel #9
0
        public static List <ReturnOrderModel> GetReturnOrders(string path)
        {
            if (!File.Exists(path))
            {
                throw new FileNotFoundException("Excel file with order not found: " + path);
            }

            using (FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read))
            {
                using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream))
                {
                    DataSet result = excelReader.AsDataSet();

                    System.Data.DataTable dt = result.Tables[0];

                    return((from c in dt.AsEnumerable()
                            group c by new
                    {
                        ItemCode = c.Field <string>(0),
                        ColorCode = c.Field <string>(1)
                    } into colorGroups
                            select new ReturnOrderModel()
                    {
                        ItemNumber = colorGroups.Key.ItemCode,
                        ColorCode = colorGroups.Key.ColorCode,
                        StoreNumber = colorGroups.FirstOrDefault().Field <string>(4)
                    }).ToList());
                }
            }
        }
Beispiel #10
0
 public async Task <System.Data.DataTable> ConvertExcelToDataTableAsync(string FileName)
 {
     return(await Task.Run(new Func <System.Data.DataTable>(() =>
     {
         // Code from http://www.c-sharpcorner.com/code/788/how-to-convert-excel-to-datatable-in-C-Sharp.aspx
         int totalSheet = 0; //No of sheets on excel file
         DataSet ds = new DataSet();
         using (OleDbConnection objConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"))
         {
             objConn.Open();
             OleDbCommand cmd = new OleDbCommand();
             OleDbDataAdapter oleda = new OleDbDataAdapter();
             System.Data.DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
             string sheetName = string.Empty;
             if (dt is null)
             {
                 return null;
             }
             var tempDataTable = (from dataRow in dt.AsEnumerable()
                                  where !dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase")
                                  select dataRow).CopyToDataTable();
             dt = tempDataTable;
             totalSheet = dt.Rows.Count;
             sheetName = dt.Rows[0]["TABLE_NAME"].ToString();
             cmd.Connection = objConn;
             cmd.CommandType = CommandType.Text;
             cmd.CommandText = string.Format("SELECT * FROM [{0}]", sheetName);
             oleda = new OleDbDataAdapter(cmd);
             oleda.Fill(ds, "excelData");
             objConn.Close();
         }
         return ds.Tables["excelData"];
     })));
 }
 public static List <DataRow> ExecuteSelectCommand(MySqlCommand command)
 {
     System.Data.DataTable table = null;
     try
     {
         command.Connection.Open();
         MySqlDataReader reader = command.ExecuteReader();
         table = new System.Data.DataTable();
         table.Load(reader);
         reader.Close();
     }
     catch (Exception ex)
     {
         Console.WriteLine(ex.Message);
     }
     finally
     {
         try
         {
             command.Connection.Close();
         }
         catch { }
     }
     return(table.AsEnumerable().ToList());
 }
Beispiel #12
0
        public List<MysteryGame> GameList(string ConnectionStringValue, string databasename, string filter)
        {
            List<MysteryGame> returnValue = new List<MysteryGame>();
            SqlConnection sqlConn = new SqlConnection(ConnectionStringValue);
            if (sqlConn.State == ConnectionState.Closed)
                sqlConn.Open();
            sqlConn.ChangeDatabase(databasename);
            SqlCommand spHandler = new SqlCommand("spGames", sqlConn);
            spHandler.Parameters.AddWithValue("@step", 3);
            spHandler.Parameters.AddWithValue("@filter", filter);
            spHandler.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter daGameHandler = new SqlDataAdapter(spHandler);
            DataTable dtGameHandler = new DataTable();
            daGameHandler.Fill(dtGameHandler);

            var convertedList = (from rw in dtGameHandler.AsEnumerable()
                                 select new MysteryGame()
                                 {
                                     gameid = Convert.ToString(rw["gameid"]),
                                     download = Convert.ToString(rw["download"]),
                                     drawdate = Convert.ToString(rw["drawdate"]),
                                     submitter = Convert.ToString(rw["submitter"]),
                                     name = Convert.ToString(rw["name"]),
                                     platform = Convert.ToString(rw["platform"]),
                                     goal = Convert.ToString(rw["goal"]),
                                     specialrequirements = Convert.ToString(rw["specialrequirements"]),
                                     tournamentraceresult = Convert.ToString(rw["tournamentraceresult"]),
                                     notes = Convert.ToString(rw["notes"]),
                                     draws = Convert.ToString(rw["draws"]),
                                     pastebin = (Utilities.PasteBinPass(Convert.ToString(rw["pastebin"]))) ? Convert.ToString(rw["pastebin"]) : "pastebin not provided"
                                 }).ToList();

            return convertedList;
        }
Beispiel #13
0
        private void InternalCompare(System.Data.DataTable parentDataTable, DataColumn[] aKeyColumns, DataRelation relation)
        {
            Parallel.ForEach(parentDataTable.AsEnumerable(), (DataRow row) =>
            {
                if (_result.ComparisonErrors.Count > 10000)
                {
                    return;
                }

                var matchedRows = row.GetChildRows(relation); // matches based on the relation
                var keyValues   = GetKeyValuesOnRow(row, aKeyColumns);

                if (matchedRows.Length > 0)
                {
                    // match was found
                    CompareValuesForMatchedRows(row, matchedRows, keyValues);
                    CompareNumberOfMatches(matchedRows, keyValues);
                }
                else
                {
                    // could not find key in b
                    MarkRowAsMissing(keyValues);
                }
            });
        }
Beispiel #14
0
        static void PrintAllCarIDs(DataTable data) {
            EnumerableRowCollection enumData = data.AsEnumerable();

            foreach (DataRow r in enumData) {
                Console.WriteLine("CarID = {0}", r["CarID"]);
            }
        }
Beispiel #15
0
 private IDataModel ConvertDataTabel2DataModel(DataTable dt, bool isGlobal)
 {
     try
     {
         var query = from row in dt.AsEnumerable()
                     where row.Field<bool>(SqlFunc4Data.IsGlobal) == isGlobal
                     select row;
         var table = new DataTable();
         table.Clear();
         DataRow dataRow = table.NewRow();
         if (query.Count() != 0)
         {
             foreach (DataRow dr in dt.Rows)
             {
                 string name = dr.ItemArray[0].ToString();
                 table.Columns.Add(name);
                 dataRow[name] = dr.ItemArray[1];
             }
         }
         return new DataModel(dataRow);
     }
     catch (System.Exception ex)
     {
         Debug.WriteLine(ex.Message);
         return null;
     }
 }
Beispiel #16
0
        public static MembershipUser GetCurrentUser()
        {
            if (HttpContext.Current.Session["User"] != null)
            {
                return(HttpContext.Current.Session["User"] as MembershipUser);
            }
            else
            {
                if (HttpContext.Current.User.Identity.IsAuthenticated)
                {
                    System.Data.DataTable dt = Helper.GetUser(HttpContext.Current.User.Identity.Name);
                    dt.AsEnumerable().Cast <DataRow>().ToList().
                    ForEach(x =>
                    {
                        if (x.Field <string>("RoleName").ToUpper() == "MIA")
                        {
                            HttpContext.Current.Session["User.DeptID"] = x.Field <string>("DEPARTEMENT_ID");
                            HttpContext.Current.Session["User.Dept"]   = x.Field <string>("NM_UNIT_ORG");
                        }
                    });

                    if (dt.Rows.Count > 0)
                    {
                        HttpContext.Current.Session["User.UnitName"] = dt.Rows[0]["NAMA_UNIT"].ToString();
                        HttpContext.Current.Session["User.RoleName"] = dt.Rows[0]["RoleName"].ToString();
                    }

                    return(HttpContext.Current.Session["User"] as MembershipUser);
                }
                return(null);
            }
        }
        //таблица будет пустой в двух случаях
        //когда она на самом деле пустая и если она содержит только пусты строки
        //пустые строки получатся из-за того что некоторые отчеты их формируют для того что бы
        //зарезервировать место под заголовок отчета
        private bool IsEmpty(DataTable reportTable)
        {
            if (!CheckEmptyData)
            {
                return(false);
            }

            //если таблицы с данными нет то значит в отчете происходит
            //что то специальное
            if (reportTable == null)
            {
                return(false);
            }

            if (reportTable.Rows.Count == 0)
            {
                return(true);
            }

            if (reportTable.Rows.Count < 100)
            {
                return(reportTable.AsEnumerable().All(r => reportTable.Columns.Cast <DataColumn>().All(c => r[c] is DBNull)));
            }
            return(false);
        }
        public static System.Data.DataTable ConvertExcelToDataTable(string FileName)
        {
            System.Data.DataTable dtResult = null;
            int totalSheet = 0;

            using (OleDbConnection objConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"))
            {
                objConn.Open();
                OleDbCommand          cmd   = new OleDbCommand();
                OleDbDataAdapter      oleda = new OleDbDataAdapter();
                DataSet               ds    = new DataSet();
                System.Data.DataTable dt    = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string sheetName            = string.Empty;
                if (dt != null)
                {
                    var tempDataTable = (from dataRow in dt.AsEnumerable() where !dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase") select dataRow).CopyToDataTable();
                    dt         = tempDataTable;
                    totalSheet = dt.Rows.Count;
                    sheetName  = dt.Rows[0]["TABLE_NAME"].ToString();
                }
                cmd.Connection  = objConn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
                oleda           = new OleDbDataAdapter(cmd);
                oleda.Fill(ds, "excelData");
                dtResult = ds.Tables["excelData"];
                objConn.Close();
                return(dtResult); //Returning Dattable
            }
        }
Beispiel #19
0
        public static List <T> toList <T>(this System.Data.DataTable dt, DataFieldMappings df)
        {
            try {
                // const BindingFlags flags = BindingFlags.Public | BindingFlags.Instance;
                var columnNames = dt.Columns.Cast <DataColumn>()
                                  .Select(c => c.ColumnName)
                                  .ToList();
                var ints             = Activator.CreateInstance <T>();
                var objectProperties = GetAllProperties(typeof(T));
                var targetList       = dt.AsEnumerable().Select(dataRow =>
                {
                    var instanceOfT = Activator.CreateInstance <T>();
                    // foreach (var properties in objectProperties.Where(properties => columnNames.Contains(properties.Name) && dataRow[properties.Name] != DBNull.Value))
                    foreach (DataFieldMapping d in df.GetMapping())
                    {
                        if (dt.Columns.Contains(d.DataField))
                        {
                            var property = objectProperties.Where(
                                properties => properties.Name == d.MemberField && dataRow[d.DataField] != DBNull.Value
                                );
                            if (property.Count() > 0)
                            {
                                var pt = property.FirstOrDefault();
                                pt.SetValue(instanceOfT, dataRow[d.DataField], null);
                            }
                        }
                    }
                    return(instanceOfT);
                }).ToList();

                return(targetList.ToList <T>());
            } catch (Exception e) {
                throw new Exception(e.Message, e.InnerException);
            }
        }
Beispiel #20
0
        public List <ViewModels.HighRisk> GetPatients(string PageType)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            dt = GetPatients(-1, PageType);

            /*
             * string type = "";
             * type= dt.Columns["patientName"].DataType.ToString();
             * type = dt.Columns["unitnum"].DataType.ToString();
             * type = dt.Columns["MaxLifetimeScore"].DataType.ToString();
             * type = dt.Columns["LastCompApptDate"].DataType.ToString();
             * type = dt.Columns["LastMRI"].DataType.ToString();
             * type = dt.Columns["Diseases"].DataType.ToString();
             * type = dt.Columns["dob"].DataType.ToString();
             */

            List <ViewModels.HighRisk> items = dt.AsEnumerable().Select(row =>
                                                                        new ViewModels.HighRisk
            {
                patientName      = row.Field <string>("patientName"),
                unitnum          = row.Field <string>("unitnum"),
                MaxLifetimeScore = row.Field <double>("MaxLifetimeScore"),
                LastCompApptDate = row.Field <DateTime?>("LastCompApptDate"),
                LastMRI          = row.Field <DateTime?>("LastMRI"),
                Diseases         = row.Field <string>("Diseases"),
                dob          = row.Field <string>("dob"),
                isRCPt       = row.Field <int?>("isRCPt"),
                genTested    = row.Field <int?>("genTested"),
                DoNotContact = row.Field <int?>("DoNotContact"),
                MaxBRCAScore = row.Field <double>("MaxBRCAScore"),
                geneNames    = row.Field <string>("geneNames"),
            }).ToList();

            return(items);
        }
Beispiel #21
0
        public static bool Login(string username, string password)
        {
            if (Membership.ValidateUser(username, password))
            {
                HttpContext.Current.Session["User"] = Membership.GetUser(username);

                System.Data.DataTable dt = Helper.GetUser(username);

                dt.AsEnumerable().Cast <DataRow>().ToList().
                ForEach(x =>
                {
                    if (x.Field <string>("RoleName").ToUpper() == "MIA")
                    {
                        HttpContext.Current.Session["User.DeptID"] = x.Field <string>("DEPARTEMENT_ID");
                        HttpContext.Current.Session["User.Dept"]   = x.Field <string>("NM_UNIT_ORG");
                    }
                });

                if (dt.Rows.Count > 0)
                {
                    HttpContext.Current.Session["User.UnitName"] = dt.Rows[0]["NM_UNIT_ORG"].ToString();
                    HttpContext.Current.Session["User.RoleName"] = dt.Rows[0]["RoleName"].ToString();
                }

                FormsAuthentication.SetAuthCookie(username, true);
                return(true);
            }
            else
            {
                return(false);
            }
        }
Beispiel #22
0
        public static void Upload(this System.Data.DataTable dt, string tableName)
        {
            string query = "SELECT * from " + tableName;


            using (SqlConnection conn = new SqlConnection(SqlConnStr))
            {
                using (SqlDataAdapter oda = new SqlDataAdapter())
                {
                    using (SqlCommandBuilder bu = new SqlCommandBuilder())
                    {
                        using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                        {
                            oda.SelectCommand             = new SqlCommand(query, conn);
                            bulkcopy.DestinationTableName = "dbo." + tableName;
                            DataTable dtsql = new DataTable();
                            oda.Fill(dtsql);
                            List <DataRow> lst_temp = dt.AsEnumerable().ToList();
                            foreach (DataRow row in lst_temp)
                            {
                                dtsql.ImportRow(row);
                            }
                            conn.Open();

                            bulkcopy.WriteToServer(dtsql);
                            conn.Close();
                        }
                    }
                }
            }
        }
Beispiel #23
0
        public static IEnumerable<DataRow> GetRowsFromDataSheets(OleDbConnection connection)
        {
            if (connection == null)
            {
                throw new ArgumentNullException("Must provide a valid OleDbConnection object.", "connection");
            }
            
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            
            DataTable schema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            var allDataRows = new List<DataRow>();

            foreach (DataRow sheet in schema.Rows)
            {
                string sheetName = sheet.Field<string>("TABLE_NAME");

                DataTable sheetData = new DataTable();

                OleDbDataAdapter sheetAdapter = new OleDbDataAdapter(String.Format("select * from [{0}]", sheetName), connection);

                sheetAdapter.Fill(sheetData);

                var sheetDataRows = sheetData.AsEnumerable();

                allDataRows.AddRange(sheetDataRows);
            }

            connection.Close();

            return allDataRows;
        }
Beispiel #24
0
        private string WriteCSV(List <Data> list, string realName)
        {
            var    json     = JsonConvert.SerializeObject(list);
            string fileName = txtOutputFolder.Text + @"\" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + "-" + realName + ".csv";

            DataTable dataTable = (DataTable)JsonConvert.DeserializeObject(json, (typeof(DataTable)));

            var lines = new List <string>();

            string[] columnNames = dataTable.Columns
                                   .Cast <DataColumn>()
                                   .Select(column => column.ColumnName)
                                   .ToArray();

            var header = string.Join(",", columnNames.Select(name => $"\"{name}\""));

            lines.Add(header);

            var valueLines = dataTable.AsEnumerable()
                             .Select(row => string.Join(",", row.ItemArray.Select(val => $"\"{val}\"")));

            lines.AddRange(valueLines);

            File.WriteAllLines(fileName, lines);

            return(fileName);
        }
Beispiel #25
0
        public static System.Data.DataTable LerExcel(string path)
        {
            System.Data.DataTable dt = new System.Data.DataTable();

            //Conexão com Excel
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0 xml;HDR=YES;IMEX=1;'";

            using (OleDbConnection conExcel = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conExcel;

                conExcel.Open();

                System.Data.DataTable table = conExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                bool SheetTestData = table.AsEnumerable().Any(t => t.Field <string>("TABLE_NAME").ToUpper() == "MASSAS$");
                if (!SheetTestData)
                {
                    throw new SheetFailedException("O arquivo do Excel deve conter apenas uma planilha com o nome MASSAS");
                }

                cmd.CommandText = "select * from [MASSAS$]";

                OleDbDataAdapter adp = new OleDbDataAdapter(cmd);

                adp.Fill(dt);
            }

            return(dt);
        }
		[ExpectedException (typeof (InvalidOperationException))] // for no rows
		public void CopyToDataTableNoArgNoRows ()
		{
			DataTable dt = new DataTable ();
			dt.Columns.Add ("CID", typeof (int));
			dt.Columns.Add ("CName", typeof (string));
			dt.AsEnumerable ().CopyToDataTable<DataRow> ();
		}
 public OperationResult <List <DataRow> > SubmitQuery(string sql, CommandType commandType, IEnumerable <KeyValuePair <string, object> > parameters) =>
 PerformDataBaseCall(sql, commandType, parameters, command => {
     System.Data.DataTable result = new System.Data.DataTable();
     using (var reader = command.ExecuteReader())
         result.Load(reader);
     return(result.AsEnumerable().ToList());
 });
Beispiel #28
0
        public async override Task RunCommand(object sender)
        {
            var         engine    = (IAutomationEngineInstance)sender;
            OBDataTable dataTable = (OBDataTable)await v_DataTable.EvaluateCode(engine);

            dynamic valueIndex = await v_DataValueIndex.EvaluateCode(engine);

            string columnName = "";

            if (v_Option == "Column Index")
            {
                int index = (int)valueIndex;
                columnName = dataTable.Columns[index].ColumnName;
            }
            else if (v_Option == "Column Name")
            {
                columnName = (string)valueIndex;
            }

            List <OBDataTable> dataTableList = new List <OBDataTable>();

            dataTableList = (from table in dataTable.AsEnumerable()
                             group table by new { placeCol = table[columnName] } into dataTableGroup
                             select dataTableGroup.ToList().CopyToDataTable()).ToList();

            dataTableList.SetVariableValue(engine, v_OutputUserVariableName);
        }
        public List<string> GetUsersFavouriteFolders(string username)
        {
            string sql = SqlPatternUsersFavouriteFolders();
            DataTable res = new DataTable();
            using (SqlConnection conn = new SqlConnection(GetReportServerConnectionString()))
            {
                SqlCommand comm = new SqlCommand(sql, conn);
                comm.Parameters.Add("@UserName", SqlDbType.NVarChar, 520).Value = username;
                SqlDataAdapter adapt = new SqlDataAdapter(comm);
                adapt.Fill(res);
            }
            List<string> topFolders = res.AsEnumerable().Select(r => r.Field<string>("Folder")).ToList();
            // if less than 5 top up from global most used
            if (topFolders.Count() < 5)
            {
                int shortfall = 5 - topFolders.Count();
                List<string> globalFolders = GetGlobalFavouriteFolders();
                for (int i = 0; i < shortfall; i++)
                {
                    if (i < globalFolders.Count())
                        topFolders.Add(globalFolders[i]);
                }

            }
            return topFolders;
        }
        public async override Task RunCommand(object sender)
        {
            var engine = (IAutomationEngineInstance)sender;

            var vKeyColumn = (string)await v_KeyColumn.EvaluateCode(engine);

            var vValueColumn = (string)await v_ValueColumn.EvaluateCode(engine);

            var excelObject   = ((OBAppInstance)await v_InstanceName.EvaluateCode(engine)).Value;
            var excelInstance = (Application)excelObject;

            Worksheet excelSheet  = excelInstance.ActiveSheet;
            Range     sourceRange = excelInstance.GetRange("A1:", excelSheet);

            int rw = sourceRange.Rows.Count;
            int cl = 2;
            int rCnt;
            int cCnt;

            DataTable DT = new DataTable();

            for (rCnt = 2; rCnt <= rw; rCnt++)
            {
                DataRow newRow = DT.NewRow();
                for (cCnt = 1; cCnt <= cl; cCnt++)
                {
                    if (((sourceRange.Cells[rCnt, cCnt] as Range).Value2) != null)
                    {
                        if (!DT.Columns.Contains(cCnt.ToString()))
                        {
                            DT.Columns.Add(cCnt.ToString());
                        }
                        newRow[cCnt.ToString()] = ((sourceRange.Cells[rCnt, cCnt] as Range).Value2).ToString();
                    }
                }
                DT.Rows.Add(newRow);
            }

            string cKeyName = ((sourceRange.Cells[1, 1] as Range).Value2).ToString();

            DT.Columns[0].ColumnName = cKeyName;
            string cValueName = ((sourceRange.Cells[1, 2] as Range).Value2).ToString();

            DT.Columns[1].ColumnName = cValueName;

            var dictlist = DT.AsEnumerable().Select(x => new
            {
                keys   = (string)x[vKeyColumn],
                values = (string)x[vValueColumn]
            }).ToList();

            Dictionary <string, string> outputDictionary = new Dictionary <string, string>();

            foreach (var dict in dictlist)
            {
                outputDictionary.Add(dict.keys, dict.values);
            }

            outputDictionary.SetVariableValue(engine, v_OutputUserVariableName);
        }
Beispiel #31
0
         public List<Category> SelectAll()
        {
            SqlConnection con = null;
            DataTable dt = null;
            List<Category> list = new List<Category>();
            try
            {
                con = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString());
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "select * from Category";
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                dt = new DataTable();
                da.Fill(dt);
                list = dt.AsEnumerable().Select(r => new Category()
                {
                    Name = (string) r["Name"],
                    Id = (int) r["Id"]
                }
                    ).ToList();

                return list;
            }
            catch (Exception ex)
            {
                return list;
            }
            finally
            {
                con.Close();
            }
        }
        public void Test_DataTable_Equals_with_ExpectedObjects()
        {
            var expected = new DataTable();
            expected.Columns.Add("Id");
            expected.Columns.Add("Name");
            expected.Columns.Add("Age");

            expected.Rows.Add(1, "A", 10);
            expected.Rows.Add(2, "B", 20);
            expected.Rows.Add(3, "C", 30);

            var actual = new DataTable();
            actual.Columns.Add("Id");
            actual.Columns.Add("Name");
            actual.Columns.Add("Age");

            actual.Rows.Add(1, "A", 10);
            actual.Rows.Add(2, "B", 20);
            actual.Rows.Add(3, "C", 30);

            var expectedDictionary =
                expected.AsEnumerable()
                    .Select(dr => expected.Columns.Cast<DataColumn>().ToDictionary(dc => dc.ColumnName, dc => dr[dc]));

            var actualDictionary =
                actual.AsEnumerable()
                    .Select(dr => actual.Columns.Cast<DataColumn>().ToDictionary(dc => dc.ColumnName, dc => dr[dc]));

            expectedDictionary.ToExpectedObject().ShouldEqual(actualDictionary);
        }
        public FormNavigator()
        {
            InitializeComponent();
            treeView.ImageList = IconsLoader.IconsList;

            //correct treeview right click handling
            treeView.MouseDown += treeView_MouseDown;

            DbDataAdapter Adapter = DbProvider.GetDataAdapter("SELECT ClassName, FormName FROM ClassRef", ORM.DB_University.connection);

            Table = new DataTable();
            Adapter.Fill(Table);

            var query = (from DataRow _class in Table.AsEnumerable()
                     select new { ClassName = _class[0].ToString(), FormName = _class[1].ToString() }).ToList();

            foreach (var row in query)
            {
                ToolStripMenuItem it = new ToolStripMenuItem();

                it.Text = row.ClassName.ToString();
                it.Name = row.ClassName.ToString();
                it.Tag = row.FormName.ToString();

                AddToolStripMenuItem.DropDownItems.Add(it);
            }
        }
Beispiel #34
0
        public void Output(TableInfo table, DataTable data)
        {
            var outputFilename = string.Format("{0}.csv", data.TableName);

            _logger.Info("Writing data to {0}...", outputFilename);

            using (var stringWriter = new StringWriter())
            using (var csv = new CsvWriter(stringWriter))
            {
                foreach (DataColumn column in data.Columns)
                {
                    csv.WriteField(column.ColumnName);
                }
                csv.NextRecord();
                foreach (var row in data.AsEnumerable())
                {
                    foreach (var item in row.ItemArray)
                    {
                        csv.WriteField(item.ToString());
                    }
                    csv.NextRecord();
                }

                File.WriteAllText(outputFilename, stringWriter.ToString());
            }

            _logger.Info("Done writing data to {0}.", outputFilename);
        }
        internal void Init(DataTable TableProductCategory)
        {
            List<Category>categories = new List<Category>();
            foreach (var item in TableProductCategory.AsEnumerable())
            {
                categories.Add(new Category
                {
                    pc_id = Convert.ToInt32(item.ItemArray[0]),
                    pc_parent_id = Convert.ToInt32(item.ItemArray[1]),
                    pc_name = Convert.ToString(item.ItemArray[2]),
                    pc_description = Convert.ToString(item.ItemArray[8])
                });
            }

            treeViewCategories.Nodes.Clear();

            var RootCategories = categories.Where(x => x.pc_parent_id == 0).ToList();

            foreach(Category category in RootCategories)
            {
                // Root Nodes
                TreeNode node = null;
                //node = treeViewCategories.Nodes.Add(category.pc_id.ToString(), category.pc_name);
                draw(categories, category, node);

            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static List<string> LAYDSACH_IDKHOTHUOC_CHAN()
        {
            DataTable m_dtKhoThuoc = new DataTable();
              SqlQuery sqlQuery = new Select().From(TDmucKho.Schema);
              if (!globalVariables.IsAdmin)
              {
              sqlQuery.Where(TDmucKho.Columns.IdKho).In(new Select(QheNhanvienKho.Columns.IdKho)
                                                        .From(QheNhanvienKho.Schema).Where(QheNhanvienKho.Columns.IdNhanvien)
                                                        .IsEqualTo(globalVariables.gv_intIDNhanvien));

              }
              if (sqlQuery.HasWhere)
              sqlQuery.And(TDmucKho.Columns.KieuKho).In(lstKhochan);
              else
              {
              sqlQuery.Where(TDmucKho.Columns.KieuKho).In(lstKhochan);
              }
              sqlQuery.And(TDmucKho.Columns.KhoThuocVt).In(lstKhoThuoc);
              sqlQuery.And(TDmucKho.Columns.LaTuthuoc).IsEqualTo(0);
              sqlQuery.And(TDmucKho.Columns.LoaiKho).IsEqualTo(0);
              sqlQuery.OrderAsc(TDmucKho.Columns.SttHthi);
              m_dtKhoThuoc = sqlQuery.ExecuteDataSet().Tables[0];
              if (m_dtKhoThuoc.Rows.Count <= 0) return new List<string>();
              return m_dtKhoThuoc.AsEnumerable().Select(c => c.Field<string>(TDmucKho.Columns.IdKho)).ToList<string>();
        }
        /// <summary>
        /// Get all Products from Product table
        /// </summary>
        /// <returns>A List of Product objects</returns>
        public List<Product> GetProducts()
        {
            DataTable dt = new DataTable();
              SqlDataAdapter da = null;

              da = new SqlDataAdapter("SELECT * FROM Product",
                              AppSettings.Instance.ConnectString);

              da.Fill(dt);

              var query =
            (from dr in dt.AsEnumerable()
              select new Product
              {
            ProductId = Convert.ToInt32(dr["ProductId"]),
            ProductName = dr["ProductName"].ToString(),
            IntroductionDate =
              DataConvert.ConvertTo<DateTime>(dr["IntroductionDate"],
                default(DateTime)),
            Cost = DataConvert.ConvertTo<decimal>(
              dr["Cost"], default(decimal)),
            Price = DataConvert.ConvertTo<decimal>(
              dr["Price"], default(decimal)),
            IsDiscontinued = DataConvert.ConvertTo<bool>(
              dr["IsDiscontinued"], default(bool))
              });

              return query.ToList();
        }
Beispiel #38
0
 public SourceInfo(DataTable tessResults)
 {
     var results = (from rows in tessResults.AsEnumerable()
                    select new
                    {
                        SourceId = rows.Field<int?>("source_no"),
                        PromoCode = rows.Field<string>("promo_code"),
                        ModeOfSaleId = rows.Field<int?>("mos"),
                        OverridesRank = rows.Field<string>("override_rank_ind"),
                        SourceName = rows.Field<string>("source_name"),
                        AppealId = rows.Field<int?>("appeal_no"),
                        AppealDescription = rows.Field<string>("appeal_desc"),
                        Text1 = rows.Field<string>("text1"),
                        Text2 = rows.Field<string>("text2"),
                        Text3 = rows.Field<string>("text3"),
                        Text4 = rows.Field<string>("text4"),
                        Text5 = rows.Field<string>("text5"),
                        Text6 = rows.Field<string>("text6"),
                        Date = rows.Field<string>("promote_dt")
                    }).Single();
     SourceId = results.SourceId;
     PromoCode = results.PromoCode;
     ModeOfSaleId = (short?)results.ModeOfSaleId;
     OverridesRank = ToBool(results.OverridesRank);
     SourceName = results.SourceName;
     AppealId = results.AppealId;
     AppealDescription = results.AppealDescription;
     Text1 = results.Text1;
     Text2 = results.Text2;
     Text3 = results.Text3;
     Text4 = results.Text4;
     Text5 = results.Text5;
     Text6 = results.Text6;
     Date = ToDateTime(results.Date);
 }
Beispiel #39
0
        static void Main(string[] args)
        {
            DataSet ds = GetDataSet();
            DataTable full = new DataTable();
            foreach (DataTable table in ds.Tables) 
            {
                full.Merge(table);
            }

            var result = from tab in full.AsEnumerable()
                         group tab by tab["time"]
                             into groupDt
                             select new
                             {
                                 Group = groupDt.Key,
                                 Sum = groupDt.Sum(r => decimal.Parse(r["count"].ToString()))
                             };
            var max = result.OrderByDescending(x => x.Sum).First();
            DateTime maxTime = (DateTime)max.Group;
            CultureInfo ci = CultureInfo.InvariantCulture;

            Console.WriteLine("Максимальное количество посетителей "
                + max.Sum + " было в " + maxTime.ToString("HH:mm:ss", ci));

            Console.ReadKey();
        }
        /// <summary>
        /// Get a single Product from the Product table
        /// </summary>
        /// <param name="productId">A Product ID to find</param>
        /// <returns>A Product object</returns>
        public Product GetProduct(int productId)
        {
            DataTable dt = new DataTable();
              SqlDataAdapter da = null;
              SqlCommand cmd = null;

              cmd = new SqlCommand("SELECT * FROM Product WHERE ProductId = @ProductId",
            new SqlConnection(AppSettings.Instance.ConnectString));
              cmd.Parameters.Add(new SqlParameter("@ProductId", productId));

              da = new SqlDataAdapter(cmd);
              da.Fill(dt);

              Product entity =
            (from dr in dt.AsEnumerable()
             select new Product
             {
               ProductId = Convert.ToInt32(dr["ProductId"]),
               ProductName = dr["ProductName"].ToString(),
               IntroductionDate =
             DataConvert.ConvertTo<DateTime>(dr["IntroductionDate"],
               default(DateTime)),
               Cost = DataConvert.ConvertTo<decimal>(
             dr["Cost"], default(decimal)),
               Price = DataConvert.ConvertTo<decimal>(
             dr["Price"], default(decimal)),
               IsDiscontinued = DataConvert.ConvertTo<bool>(
             dr["IsDiscontinued"], default(bool))
             }).FirstOrDefault();

              return entity;
        }
Beispiel #41
0
        public string GetCarrier()
        {
            DataManager dataManager = new DataManager();
            DataTable friendTable = new DataTable();

            friendTable = dataManager.GetAllFriends();

            string carrier = null;
            string friendsCarrier = null;
            string friendName = FriendComboBox.SelectedItem.ToString();
            DataRow[] foundRows = friendTable
                                  .AsEnumerable()
                                  .Where(row => row.Field<string>("Name") == friendName)
                                  .ToArray();
            for (int i = 0; i < foundRows.Length; i++)
            {
                carrier = foundRows[i][2].ToString();
            }

            if (carrier == "AT&T")
            {
                friendsCarrier = "@txt.att.net";
            }

            if (carrier == "SprintPCS")
            {
                friendsCarrier = "@messaging.sprintpcs.com";
            }

            return friendsCarrier;
        }
 private static IEnumerable<DataRow> GetRows(DataTable funcTable, int parentId)
 {
     var result = from row in funcTable.AsEnumerable()
                  where row.Field<int>("FN_PARENT_ID") == parentId
                  select row;
     return result;
 }
        public override void RunCommand(object sender)
        {
            var engine = (IAutomationEngineInstance)sender;

            var         dataTableVariable = v_DataTable.ConvertUserVariableToObject(engine, nameof(v_DataTable), this);
            OBDataTable dataTable         = (OBDataTable)dataTableVariable;

            var valueIndex = v_DataValueIndex.ConvertUserVariableToString(engine);

            string columnName = "";

            if (v_Option == "Column Index")
            {
                int index = int.Parse(valueIndex);
                columnName = dataTable.Columns[index].ColumnName;
            }
            else if (v_Option == "Column Name")
            {
                columnName = valueIndex;
            }

            List <OBDataTable> dataTableList = new List <OBDataTable>();

            dataTableList = (from table in dataTable.AsEnumerable()
                             group table by new { placeCol = table[columnName] } into dataTableGroup
                             select dataTableGroup.ToList().CopyToDataTable()).ToList();

            dataTableList.StoreInUserVariable(engine, v_OutputUserVariableName, nameof(v_OutputUserVariableName), this);
        }
 public ConstituentHeader(DataTable tessResults)
 {
     var header = (from row in tessResults.AsEnumerable()
                   select new
                   {
                       customer_no = row.Field<int?>("customer_no"),
                       full_name1 = row.Field<string>("full_name1"),
                       full_name2 = row.Field<string>("full_name2"),
                       all_const = row.Field<string>("all_const"),
                       memb_level = row.Field<string>("memb_level"),
                       current_status = row.Field<string>("current_status"),
                       memb_expirtion = row.Field<DateTime?>("memb_expirtion"),
                       create_dt = row.Field<DateTime?>("create_dt"),
                       donation = row.Field<decimal?>("donation"),
                       userid = row.Field<string>("userid"),
                       batch_no = row.Field<int?>("batch_no"),
                       on_account = row.Field<decimal?>("on_account"),
                       inactive = Invert(ToBool(row.Field<string>("inactive"))),
                       batch_type = row.Field<int?>("batch_type")
                   }).Single();
     ConstituentId = header.customer_no;
     FullName1 = header.full_name1;
     FullName2 = header.full_name2;
     ConstituenciesList = header.all_const;
     MembershipLevel = header.memb_level;
     CurrentStatus = header.current_status;
     MembershipExpirationDate = header.memb_expirtion;
     CreatedDateTime = header.create_dt;
     Donation = header.donation;
     RetrievedByUser = header.userid;
     RetrievedDuringBatchId = header.batch_no;
     OnAccount = header.on_account;
     Active = header.inactive;
     RetrievedDuringBatchTypeId = header.batch_type;
 }
Beispiel #45
0
        private void btnLoad_Click(object sender, EventArgs e)
        {
            //MessageBox.Show(Util.TaskInfo.TaskSetting.InputFilePath);


            ExlAddin.Read2Dt dt = new ExlAddin.Read2Dt();
            dt.FilePrepare();
            dtForm = dt.Exl2Dt();
            dataGridView1.DataSource = dtForm;



            //MessageBox.Show(test.symType);
            //Console.WriteLine(dataGridView1.Left.ToString());


            // filter SymbolType to Listbox to select
            var query = from c in dtForm.AsEnumerable()
                        group c by c.Field<string>("SymbolType");

            foreach (var item in query)
            {
                if (item.Key == null)
                    continue;
                //Console.WriteLine(item.Key);
                listBoxSymbol.Items.Add(item.Key);
            }
        }
Beispiel #46
0
        public static DataTable GetDataTable(QueryModel queryModel)
        {
            var sql = CreateSql(queryModel);
            _dataTable = CreateDataTable(queryModel.SelectedCols, queryModel.TimeFrame);
            var reader = DataExportClientDataManager.GetReader(sql);

            if (reader != null)
            {
                try
                {
                    while (reader.Read())
                    {
                        var row = _dataTable.NewRow();
                        for (int i = 0; i < _dataTable.Columns.Count; i++)
                        {
                            row[i] = reader.GetValue(i);
                        }
                        _dataTable.Rows.Add(row);
                    }
                }
                finally
                {
                    reader.Close();
                }
            }
            if (!queryModel.DateOrDaysBack)
            {
                return (from rows in _dataTable.AsEnumerable()
                       where rows.Field<DateTime>("Time").Hour > queryModel.Start.Hour
                        && rows.Field<DateTime>("Time").Hour < queryModel.End.Hour
                       select rows).CopyToDataTable();
            }

            return _dataTable;
        }
Beispiel #47
0
        ///-----------------------------------------------------------------------------
        /// <summary>
        ///     得意先情報をDataTableからClsCsvData.ClsCsvTokuisakiクラスに取得 :
        ///     2020/04/09</summary>
        /// <param name="tID">
        ///     得意先コード</param>
        /// <returns>
        ///     ClsCsvData.ClsCsvTokuisakiクラス</returns>
        ///-----------------------------------------------------------------------------
        public static ClsCsvData.ClsCsvTokuisaki GetTokuisakiFromDataTable(string tID, System.Data.DataTable data)
        {
            // 返り値クラス初期化
            ClsCsvData.ClsCsvTokuisaki cls = new ClsCsvData.ClsCsvTokuisaki
            {
                TOKUISAKI_CD       = "",
                YUKO_START_YMD     = "",
                YUKO_END_YMD       = "",
                TOKUISAKI_NM       = "",
                TOKUISAKI_KANA_NM  = "",
                TOKUISAKI_YUBIN_NO = "",
                TOKUISAKI_ZYUSYO1  = "",
                TOKUISAKI_ZYUSYO2  = "",
                TOKUISAKI_TEL      = "",
                TOKUISAKI_FAX      = "",
                DELFLG             = global.FLGOFF
            };

            DataRow[] rows = data.AsEnumerable().Where(a => a["TOKUISAKI_CD"].ToString().PadLeft(7, '0') == tID && a["DELFLG"].ToString() == global.FLGOFF).ToArray();

            foreach (var t in rows)
            {
                // 有効開始日、有効終了日を検証する
                int cYuko_Start_Date = Utility.StrtoInt(t["YUKO_START_YMD"].ToString());    // 有効開始日付
                int cYuko_End_Date   = Utility.StrtoInt(t["YUKO_END_YMD"].ToString());      // 有効終了日付
                int toDate           = Utility.StrtoInt(DateTime.Today.Year.ToString() + DateTime.Today.Month.ToString("D2") + DateTime.Today.Day.ToString("D2"));

                if (cYuko_Start_Date > toDate)
                {
                    continue;
                }

                if (cYuko_End_Date != global.flgOff)
                {
                    if (toDate > cYuko_End_Date)
                    {
                        continue;
                    }
                }

                cls.TOKUISAKI_CD       = t["TOKUISAKI_CD"].ToString();          // 得意先コード
                cls.YUKO_START_YMD     = t["YUKO_START_YMD"].ToString();        // 有効開始日付
                cls.YUKO_END_YMD       = t["YUKO_END_YMD"].ToString();          // 有効終了日付
                cls.TOKUISAKI_NM       = t["TOKUISAKI_NM"].ToString();          // 得意先名称
                cls.TOKUISAKI_KANA_NM  = t["TOKUISAKI_KANA_NM"].ToString();     // 得意先カナ名称
                cls.TOKUISAKI_YUBIN_NO = t["TOKUISAKI_YUBIN_NO"].ToString();    // 郵便番号
                cls.TOKUISAKI_ZYUSYO1  = t["TOKUISAKI_ZYUSYO1"].ToString();     // 得意先住所
                cls.TOKUISAKI_ZYUSYO2  = t["TOKUISAKI_ZYUSYO2"].ToString();     // 得意先住所
                cls.TOKUISAKI_TEL      = t["TOKUISAKI_TEL"].ToString();         // 得意先TEL
                cls.TOKUISAKI_FAX      = t["TOKUISAKI_FAX"].ToString();         // 得意先FAX
                cls.DELFLG             = t["DELFLG"].ToString();                // 削除フラグ

                break;
            }

            return(cls);
        }
Beispiel #48
0
        private void btb_start_Click(object sender, EventArgs e)
        {
            if (txtexcelfile.Text == "")
            {
                MessageBox.Show("Please Select Excel File");
            }
            else if (txtfolder.Text == "")
            {
                MessageBox.Show("Please Select Folder Path");
            }
            else if (txtfolderpath.Text == "")
            {
                MessageBox.Show("Please Select Desired Folder Path To Save The Files");
            }
            else
            {
                btb_start.Enabled = false;
                btn_stop.Enabled  = true;

                lblmessage.Refresh();
                lblmessage.Text = "Excel values are copying into application";
                string excelfile = string.Empty;
                excelfile = txtexcelfile.Text;
                System.Data.DataTable dt = XLStoDTusingInterOp(excelfile);
                string rel = "Released";
                dt.DefaultView.RowFilter = "Dateiname like '%.tif'";

                if (radioButton1.Checked == true)
                {
                    excelTable = dt;
                }
                else
                {
                    excelTable = dt.AsEnumerable()
                                 .Where(row => row.Field <String>("LifeCycle State ") == rel)
                                 .CopyToDataTable();
                }

                int excelcount = excelTable.Rows.Count;
                progressBar1.Maximum = excelcount;
                lblmessage.Refresh();
                lblmessage.Text = Convert.ToString(0);
                //progressBar1.Maximum = excelcount;
                ThreadStart theprogress = new ThreadStart(mainlogic);
                // Now the thread which we create using the delegate
                //Thread.CurrentThread.Priority = ThreadPriority.Lowest;


                Thread startprogress = new Thread(theprogress);
                startprogress.Priority = ThreadPriority.Lowest;
                // We can give it a name (optional)
                startprogress.Name = "Update ProgressBar";
                // Start the execution
                startprogress.Start();
                progressBar1.Visible = true;
            }
        }
Beispiel #49
0
        /// <summary>
        /// 將資料依狀態分類成修改,新增,刪除的DataTable
        /// </summary>
        /// <param name="DataList">資料</param>
        /// <returns>由新增,修改,刪除DataTable組成的DataSet</returns>
        public DataSet DataDataClassification(System.Data.DataTable DataList)
        {
            DataSet   ds         = new DataSet();
            DataTable insertList = new DataTable();
            DataTable updateList = new DataTable();
            DataTable delList    = new DataTable();

            try
            {
                ////需要新增的資料
                var filter = (from row in DataList.AsEnumerable()
                              where row.Field <string>("Flag") == "I"
                              select row);
                if (filter.Any())
                {
                    insertList = filter.CopyToDataTable();
                }
                ////需要修改的資料
                filter = (from row in DataList.AsEnumerable()
                          where row.Field <string>("Flag") == "U"
                          select row);
                if (filter.Any())
                {
                    updateList = filter.CopyToDataTable();
                }
                ////需要刪除的資料
                filter = (from row in DataList.AsEnumerable()
                          where row.Field <string>("Flag") == "D"
                          select row);
                if (filter.Any())
                {
                    delList = filter.CopyToDataTable();
                }
                ds.Tables.Add(insertList);
                ds.Tables.Add(updateList);
                ds.Tables.Add(delList);
                return(ds);
            }
            catch (Exception ex)
            {
                LogInfo.WriteErrorInfo(ex);
                return(new DataSet());
            }
        }
Beispiel #50
0
        /// <summary>
        /// Create a new worksheet and add and format data
        /// </summary>
        /// <param name="table">the table of data to be displayed</param>
        /// <param name="sheetName">The name to be given to the sheet (and tables)</param>
        /// <param name="splitTableOn">If you are going to split the table in two parts</param>
        /// <param name="isJsonData">Whether the data is json or SQL, sql data has more information about data types</param>
        /// <param name="firstNumberColumn">If provided everything after this will be formatted as a number</param>
        /// <param name="extraWideColumns">The columns with lots of text in</param>
        public static void CreateWorkSheet(System.Data.DataTable table, string sheetName, string splitTableOn = "", bool isJsonData = false, string firstNumberColumn = "", int[] extraWideColumns = null, bool addTotalRow = true)
        {
            Microsoft.Office.Interop.Excel.Worksheet worksheet = new Microsoft.Office.Interop.Excel.Worksheet();
            List <System.Data.DataTable>             tables    = new List <System.Data.DataTable>();

            if (sheetName == null)
            {
                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
            }
            else
            {
                worksheet      = Globals.ThisAddIn.Application.Worksheets.Add(After: Globals.ThisAddIn.Application.ActiveSheet);
                worksheet.Name = sheetName;
            }
            worksheet.UsedRange.Clear();
            if (!String.IsNullOrEmpty(splitTableOn))
            {
                //split the data set in two to handle the two parts
                var rows = table.AsEnumerable().Where(x => String.IsNullOrEmpty(x.Field <string>(splitTableOn)));
                System.Data.DataTable topPartTable = rows.Any() ? rows.CopyToDataTable() : table.Clone();
                topPartTable.TableName = sheetName + "_1";
                rows = table.AsEnumerable().Where(x => !String.IsNullOrEmpty(x.Field <string>(splitTableOn)));
                System.Data.DataTable bottomPartTable = rows.Any() ? rows.CopyToDataTable() : table.Clone();
                bottomPartTable.TableName = sheetName + "_2";
                tables.Add(topPartTable);
                tables.Add(bottomPartTable);
            }
            else
            {
                table.TableName = sheetName;
                tables.Add(table);
            }
            int lastRow = 1;

            foreach (System.Data.DataTable t in tables)
            {
                RemoveNonDisplayColumns(t);
                Microsoft.Office.Interop.Excel.Range rng = AddDataToSheet(t, worksheet, lastRow);
                AddExcelTable(rng, t, t.TableName, addTotalRow, isJsonData, firstNumberColumn);
                FormatTable(t, rng, isJsonData, firstNumberColumn, extraWideColumns);
                lastRow += t.Rows.Count + 8; //allow for total and a gap
            }
        }
Beispiel #51
0
        /// <summary>
        ////// <summary>
        /// 查找
        /// </summary>
        /// <returns></returns>
        public static void Select(System.Data.DataTable EnvirDataTB,
                                  DateTime Begin_Time, DateTime End_Time, int Env_TempID)

        {
            var MySelRes = from line in EnvirDataTB.AsEnumerable()
                           where (line.Field <int>("DataTypeID")) == Env_TempID &&
                           (line.Field <DateTime>("MeasureTime")) > Begin_Time &&
                           (line.Field <DateTime>("MeasureTime")) < End_Time
                           select line;
            int numMySelRec = MySelRes.Count();
        }
Beispiel #52
0
        public int getNumRoles(int act_id)
        {
            System.Data.DataTable tab = this.actions.Tables["Actions"];
            int roles = 0;
            var query = from t in tab.AsEnumerable()
                        where t.Field <UInt32>("act_id") == act_id
                        select t.Field <int>("act_obj_num");

            roles = query.First();
            return(roles);
        }
        private string GetFieldValue(System.Data.DataTable workSheetCoverLocInfo, string fieldName, string fieldLabel, int relativePos, int offset)
        {
            string returnVal = "";

            var xy = GetTableRowColIndexesForExactMatch(workSheetCoverLocInfo, fieldLabel);

            switch (relativePos)
            {
            case 0:
                returnVal = workSheetCoverLocInfo.AsEnumerable().AsDataView().Table.Rows[xy.rowIndex + 1][xy.colIndex].ToString().Trim();
                break;

            case 1:
                returnVal = workSheetCoverLocInfo.AsEnumerable().AsDataView().Table.Rows[xy.rowIndex][xy.colIndex + 1].ToString().Trim();
                break;

            default:
                break;
            }
            return(returnVal);
        }
        public static string RetornaItemArquivoConfigConexao(string nomeArquivo, string nomeChave)
        {
            string retorno = "";
            string curDir  = System.IO.Path.GetDirectoryName(System.AppDomain.CurrentDomain.BaseDirectory.ToString());

            System.Data.DataTable dtArquivoExistente = ArquivoXML.AbrirArquivoXML(curDir, nomeArquivo);
            if (dtArquivoExistente == null || dtArquivoExistente.Rows.Count == 0)
            {
                //não existe
                retorno = "";
            }
            else
            {
                var existe = dtArquivoExistente.AsEnumerable().Any(M => M["Chave"].ToString() == nomeChave);
                if (existe)
                {
                    retorno = dtArquivoExistente.AsEnumerable().FirstOrDefault(M => M["Chave"].ToString() == nomeChave)["Valor"].ToString();
                }
            }
            return(retorno);
        }
Beispiel #55
0
        ///------------------------------------------------------------------------
        /// <summary>
        ///     商品マスターデータテーブルから情報を取得する : 2020/04/09 </summary>
        /// <param name="syohins">
        ///     ClsCsvData.ClsCsvSyohin_Newクラス配列</param>
        /// <param name="sSyohinCD">
        ///     商品コード  </param>
        /// <returns>
        ///     商品マスタークラス</returns>
        ///------------------------------------------------------------------------
        public static ClsCsvData.ClsCsvSyohin_New GetSyohinsFromDataTable(System.Data.DataTable data, string sSyohinCD)
        {
            ClsCsvData.ClsCsvSyohin_New cls = new ClsCsvData.ClsCsvSyohin_New
            {
                SYOHIN_CD  = "",
                SYOHIN_NM  = "",
                SYOHIN_SNM = "",
                //SYOHIN_KANA = "",
                SIRESAKI_CD      = "",
                SIRESAKI_NM      = "",
                SIRESAKI_KANA_NM = "",
                JAN_CD           = "",
                SYOHIN_KIKAKU    = "",
                CASE_IRISU       = global.flgOff,
                //NOUHIN_KARI_TANKA = global.flgOff,
                //RETAIL_TANKA = global.flgOff,
                //HATYU_LIMIT_DAY_CNT = global.flgOff,
                START_SALE_YMD   = "",
                LAST_SALE_YMD    = "",
                SHUBAI           = false,
                SYOHIN_KIND_L_CD = "",
                SYOHIN_KIND_M_CD = "",
                SYOHIN_KIND_S_CD = "",
                SYOHIN_KIND_CD   = ""
            };

            DataRow[] row = data.AsEnumerable().Where(a => a["SYOHIN_CD"].ToString().PadLeft(8, '0') == sSyohinCD.PadLeft(8, '0')).ToArray();

            foreach (var t in row)
            {
                cls.SYOHIN_CD  = t["SYOHIN_CD"].ToString();
                cls.SYOHIN_NM  = t["SYOHIN_NM"].ToString();
                cls.SYOHIN_SNM = t["SYOHIN_SNM"].ToString();
                //cls.SYOHIN_KANA = syohins[i].SIRESAKI_CD;
                cls.SIRESAKI_CD      = t["SIRESAKI_CD"].ToString();
                cls.SIRESAKI_NM      = t["SIRESAKI_NM"].ToString();
                cls.SIRESAKI_KANA_NM = t["SIRESAKI_KANA_NM"].ToString();
                cls.JAN_CD           = t["JAN_CD"].ToString();
                cls.SYOHIN_KIKAKU    = t["SYOHIN_KIKAKU"].ToString();
                cls.CASE_IRISU       = Utility.StrtoDouble(t["CASE_IRISU"].ToString());
                cls.START_SALE_YMD   = t["START_SALE_YMD"].ToString();
                cls.LAST_SALE_YMD    = t["LAST_SALE_YMD"].ToString();
                cls.SHUBAI           = Convert.ToBoolean(Utility.StrtoInt(t["SHUBAI"].ToString()));
                cls.SYOHIN_KIND_L_CD = t["SYOHIN_KIND_L_CD"].ToString();
                cls.SYOHIN_KIND_M_CD = t["SYOHIN_KIND_M_CD"].ToString();
                cls.SYOHIN_KIND_S_CD = t["SYOHIN_KIND_S_CD"].ToString();
                cls.SYOHIN_KIND_CD   = t["SYOHIN_KIND_CD"].ToString();
                break;
            }

            return(cls);
        }
Beispiel #56
0
        public void DisplayProducts(System.Data.DataTable table)
        {
            System.Data.DataSet b;


            var productNames = from products in table.AsEnumerable() select products.Field <string>("ProductName");

            Console.WriteLine("Product Names: ");
            foreach (string productName in productNames)
            {
                Console.WriteLine(productName);
            }
        }
        public ReactionOutput(System.Data.DataRow reaction, System.Data.DataTable reactants, System.Data.DataTable rxnReactants, System.Data.DataTable catalysts, System.Data.DataTable solvents, System.Data.DataTable byProducts, System.Data.DataTable references)
        {
            Name = reaction["Name"].ToString();
            Int64 id      = Convert.ToInt64(reaction["id"]);
            Int64 groupId = Convert.ToInt64(reaction["Functional_Group_id"]);

            URL     = reaction["URL"].ToString();
            Product = reaction["Product"].ToString();
            //ByProducts = reaction["ByProducts"].ToString();
            //Reactants = reaction["Reactants"].ToString();
            Int64 catalystId = Convert.ToInt64(reaction["Catalyst_id"]);

            Catalyst = catalysts.Rows[Convert.ToInt32(catalystId)]["Name"].ToString();
            Int64 solventId = Convert.ToInt64(reaction["Solvent_id"]);

            Solvent  = solvents.Rows[Convert.ToInt32(solventId)]["Name"].ToString();
            AcidBase = reaction["AcidBase"].ToString();
            List <string> m_Reactants = new List <string>();
            var           results     = from myRow in rxnReactants.AsEnumerable()
                                        where myRow.Field <Int64>("namedreaction_id") == Convert.ToInt64(id)
                                        select myRow;

            foreach (System.Data.DataRow row in results)
            {
                m_Reactants.Add(reactants.Rows[Convert.ToInt32(row["reactant_id"])].ToString());
            }
            Reactants = m_Reactants.ToArray <string>();
            List <string> m_ByProducts = new List <string>();

            results = from myRow in byProducts.AsEnumerable()
                      where myRow.Field <Int64>("namedreaction_id") == Convert.ToInt64(id)
                      select myRow;

            foreach (System.Data.DataRow row in results)
            {
                m_ByProducts.Add(reactants.Rows[Convert.ToInt32(row["reactant_id"])].ToString());
            }
            ByProducts = m_ByProducts.ToArray <string>();
            List <Reference> m_References = new List <Reference>();

            results = from myRow in references.AsEnumerable()
                      where myRow.Field <Int64>("Reaction_id") == id &&
                      myRow.Field <Int64>("Functional_Group_id") == groupId
                      select myRow;

            foreach (System.Data.DataRow row in results)
            {
                m_References.Add(new Reference(groupId, id, row["RISData"].ToString()));
            }
            References = m_References.ToArray <Reference>();
        }
        public static void GeraArquivoConfigConexao(string nomeArquivo, string nomeChave, string valorChave)
        {
            string curDir = System.IO.Path.GetDirectoryName(System.AppDomain.CurrentDomain.BaseDirectory.ToString());

            System.Data.DataTable dtArquivoExistente = ArquivoXML.AbrirArquivoXML(curDir, nomeArquivo);

            if (dtArquivoExistente == null || dtArquivoExistente.Rows.Count == 0)
            {
                System.Data.DataTable dt = new System.Data.DataTable("Config");
                dt.Columns.Add("Chave", typeof(string));
                dt.Columns.Add("Valor", typeof(string));
                System.Data.DataRow dr = dt.NewRow();

                #region Linha
                dr["Chave"] = nomeChave;
                dr["Valor"] = valorChave;
                dt.Rows.Add(dr);
                #endregion

                ArquivoXML.GravaArquivoXML(dt, curDir, nomeArquivo);
            }
            else
            {
                System.Data.DataRow dr = dtArquivoExistente.NewRow();
                var existe             = dtArquivoExistente.AsEnumerable().Any(M => M["Chave"].ToString() == nomeChave);
                if (existe)
                {
                    foreach (DataRow item in dtArquivoExistente.Rows)
                    {
                        if (item["Chave"].ToString() == nomeChave)//existe
                        {
                            #region Linha
                            item["Chave"] = nomeChave;
                            item["Valor"] = valorChave;
                            #endregion
                            break;
                        }
                    }
                }
                else
                {
                    #region Linha
                    dr["Chave"] = nomeChave;
                    dr["Valor"] = valorChave;
                    dtArquivoExistente.Rows.Add(dr);
                    #endregion
                }

                ArquivoXML.GravaArquivoXML(dtArquivoExistente, curDir, nomeArquivo);
            }
        }
        protected void btnImport_Click(object sender, EventArgs e)
        {
            ArrayList alist        = new ArrayList();
            string    connString   = "";
            string    strFileType  = Path.GetExtension(fileuploadExcel.FileName).ToLower();
            string    fileBasePath = Server.MapPath("~/Files/");
            string    fileName     = Path.GetFileName(this.fileuploadExcel.FileName);
            string    fullFilePath = fileBasePath + fileName;

            //Connection String to Excel Workbook
            if (strFileType.Trim() == ".xls")
            {
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullFilePath +
                             ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
            }
            else if (strFileType.Trim() == ".xlsx")
            {
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullFilePath +
                             ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
            }
            if (fileuploadExcel.HasFile)
            {
                string          query = "SELECT [UserName],[Education],[Location] FROM [Sheet1$]";
                OleDbConnection conn  = new OleDbConnection(connString);
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                OleDbCommand     cmd = new OleDbCommand(query, conn);
                OleDbDataAdapter da  = new OleDbDataAdapter(cmd);
                DataSet          ds  = new DataSet();
                da.Fill(ds);
                //Assigning dataset table data to GridView Control
                Session["griddata"] = ds.Tables[0];
                System.Data.DataTable dtt = (System.Data.DataTable)Session["griddata"];      //griddata is the gridview data from another page
                var res = (from f in dtt.AsEnumerable()
                           select f.Field <string>("Location")
                           );
                foreach (string s in res.AsEnumerable())
                {
                    alist.Add(s);
                }
                Session["dropdownvalue"] = alist;
                grvExcelData.DataSource  = Session["griddata"];
                grvExcelData.DataBind();
                da.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }