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(); }
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); } }
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; } }
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); } }
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()); } } }
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()); }
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; }
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); } }); }
static void PrintAllCarIDs(DataTable data) { EnumerableRowCollection enumData = data.AsEnumerable(); foreach (DataRow r in enumData) { Console.WriteLine("CarID = {0}", r["CarID"]); } }
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; } }
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 } }
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); } }
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); }
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); } }
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(); } } } } }
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; }
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); }
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()); });
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); }
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); } }
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(); }
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); }
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; }
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; }
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); } }
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; }
///----------------------------------------------------------------------------- /// <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); }
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; } }
/// <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()); } }
/// <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 } }
/// <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(); }
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); }
///------------------------------------------------------------------------ /// <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); }
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(); } }