/// <summary> /// 获取日消费清单 /// </summary> /// <param name="start"></param> /// <param name="end"></param> /// <returns></returns> public List<Monthly> GetMonthlys(string start, string end) { List<Monthly> result = null; DataTable dt = new DataTable(); string sql = @"SELECT NEWID() ID, DATENAME(YEAR, DATE) + '-' + DATENAME(MONTH, DATE) MONTH, SUM(COST) COST FROM DAILY WHERE DATENAME(YEAR, DATE) + '-' + DATENAME(MONTH, DATE) BETWEEN @START AND @END GROUP BY DATENAME(YEAR, DATE) + '-' + DATENAME(MONTH, DATE) ORDER BY DATENAME(YEAR, DATE) + '-' + DATENAME(MONTH, DATE) ASC"; using (DbCommand cmd = _database.GetSqlStringCommand(sql)) { _database.AddInParameter(cmd, "@START", DbType.String, start); _database.AddInParameter(cmd, "@END", DbType.String, end); using (IDataReader reader = _database.ExecuteReader(cmd)) { dt.Load(reader); } } if (dt != null && dt.Rows.Count > 0) { result = dt.ToList<Monthly>(); } return result; }
private static void PrintCollection(DataTable table) { Console.WriteLine("Printing a DataTable to an IList"); Console.WriteLine("---------------------------"); var list = table.ToList<Customer>(); foreach (var customer in list) { Console.WriteLine("Customer"); Console.WriteLine("---------------"); Console.WriteLine("Id: {0} - Name: {1}", customer.Id, customer.Name); } Console.WriteLine(); }
/// <summary> /// 获取日消费清单 /// </summary> /// <param name="start"></param> /// <param name="end"></param> /// <returns></returns> public List<Daily> GetDailys(DateTime start, DateTime end) { List<Daily> result = null; DataTable dt = new DataTable(); string sql = @"SELECT D.* FROM DAILY D WHERE D.DATE BETWEEN @START AND @END ORDER BY D.DATE ASC"; using (DbCommand cmd = _database.GetSqlStringCommand(sql)) { _database.AddInParameter(cmd, "@START", DbType.Date, start); _database.AddInParameter(cmd, "@END", DbType.Date, end); using (IDataReader reader = _database.ExecuteReader(cmd)) { dt.Load(reader); } } if (dt != null && dt.Rows.Count > 0) { result = dt.ToList<Daily>(); } return result; }
private List<DailySummary> ReportsFilterImplementation(FiltersModel fm,string dateFrom,string dateTo, string Criteria) { List<DailySummary> ViewDS = new List<DailySummary>(); List<DailySummary> TempDS = new List<DailySummary>(); QueryBuilder qb = new QueryBuilder(); DataTable dt = new DataTable(); switch (Criteria) { case "C": //for company dt = qb.GetValuesfromDB("select * from DailySummary " + " where Criteria = '"+Criteria + "' and (Date >= " + "'" + dateFrom + "'" + " and Date <= " + "'" + dateTo + "'" + " )"); ViewDS = dt.ToList<DailySummary>(); if (fm.CompanyFilter.Count > 0) { foreach (var comp in fm.CompanyFilter) { short _compID = Convert.ToInt16(comp.ID); TempDS.AddRange(ViewDS.Where(aa => aa.CriteriaValue == _compID && aa.Criteria == Criteria).ToList()); } ViewDS = TempDS.ToList(); } else TempDS = ViewDS.ToList(); TempDS.Clear(); break; case "L": dt = qb.GetValuesfromDB("select * from DailySummary " + " where Criteria = '"+Criteria + "' and (Date >= " + "'" + dateFrom + "'" + " and Date <= " + "'" + dateTo + "'" + " )"); ViewDS = dt.ToList<DailySummary>(); if (fm.LocationFilter.Count > 0) { foreach (var loc in fm.LocationFilter) { short _locID = Convert.ToInt16(loc.ID); TempDS.AddRange(ViewDS.Where(aa => aa.CriteriaValue == _locID && aa.Criteria == Criteria).ToList()); } ViewDS = TempDS.ToList(); } else TempDS = ViewDS.ToList(); TempDS.Clear(); break; case "D": dt = qb.GetValuesfromDB("select * from DailySummary " + " where Criteria = '"+Criteria + "' and (Date >= " + "'" + dateFrom + "'" + " and Date <= " + "'" + dateTo + "'" + " )"); ViewDS = dt.ToList<DailySummary>(); if (fm.DepartmentFilter.Count > 0) { foreach (var dept in fm.DepartmentFilter) { short _deptID = Convert.ToInt16(dept.ID); TempDS.AddRange(ViewDS.Where(aa => aa.CriteriaValue == _deptID && aa.Criteria == Criteria).ToList()); } ViewDS = TempDS.ToList(); } else TempDS = ViewDS.ToList(); TempDS.Clear(); break; case "E": dt = qb.GetValuesfromDB("select * from DailySummary " + " where Criteria = '"+Criteria + "' and (Date >= " + "'" + dateFrom + "'" + " and Date <= " + "'" + dateTo + "'" + " )"); ViewDS = dt.ToList<DailySummary>(); if (fm.SectionFilter.Count > 0) { foreach (var sec in fm.SectionFilter) { short _secID = Convert.ToInt16(sec.ID); TempDS.AddRange(ViewDS.Where(aa => aa.CriteriaValue == _secID && aa.Criteria == Criteria).ToList()); } ViewDS = TempDS.ToList(); } else TempDS = ViewDS.ToList(); TempDS.Clear(); break; case "S": dt = qb.GetValuesfromDB("select * from DailySummary " + " where Criteria = '"+Criteria + "' and (Date >= " + "'" + dateFrom + "'" + " and Date <= " + "'" + dateTo + "'" + " )"); ViewDS = dt.ToList<DailySummary>(); if (fm.ShiftFilter.Count > 0) { foreach (var shift in fm.ShiftFilter) { short _shiftID = Convert.ToInt16(shift.ID); TempDS.AddRange(ViewDS.Where(aa => aa.CriteriaValue == _shiftID && aa.Criteria == Criteria).ToList()); } ViewDS = TempDS.ToList(); } else TempDS = ViewDS.ToList(); TempDS.Clear(); break; case "T": dt = qb.GetValuesfromDB("select * from DailySummary " + " where Criteria = '"+Criteria + "' and (Date >= " + "'" + dateFrom + "'" + " and Date <= " + "'" + dateTo + "'" + " )"); ViewDS = dt.ToList<DailySummary>(); if (fm.TypeFilter.Count > 0) { foreach (var type in fm.TypeFilter) { short _typeID = Convert.ToInt16(type.ID); TempDS.AddRange(ViewDS.Where(aa => aa.CriteriaValue == _typeID && aa.Criteria == Criteria).ToList()); } ViewDS = TempDS.ToList(); } else TempDS = ViewDS.ToList(); TempDS.Clear(); break; //case "A": // if (fm.CompanyFilter.Count > 0) // { // foreach (var comp in fm.CompanyFilter) // { // short _compID = Convert.ToInt16(comp.ID); // TempDS.AddRange(ViewDS.Where(aa => aa.CriteriaValue == _compID && aa.Criteria == Criteria).ToList()); // } // ViewDS = TempDS.ToList(); // } // else // TempDS = ViewDS.ToList(); // TempDS.Clear(); // break; } return ViewDS; }
public ActionResult Details(string id) { NuclearButtonEditModel model = new NuclearButtonEditModel(); List<NuclearButtonEditInfoModel> list = new List<NuclearButtonEditInfoModel>(); model.infoModel = new List<NuclearButtonEditInfoModel>(); try { //权限验证 //this.HasActionPower(); if (!string.IsNullOrWhiteSpace(id)) { DataTable out_head = new DataTable(); DataTable out_list = new DataTable(); this.facadeVHk.GetDetailsData(id, out out_head, out out_list); if (out_head != null && out_head.Rows.Count > 0) { model = out_head.ToList<NuclearButtonEditModel>().First(); if (out_list != null && out_list.Rows.Count > 0) { foreach (DataRow row in out_list.Rows) { NuclearButtonEditInfoModel infoModel = new NuclearButtonEditInfoModel(); infoModel.in_bill_id = row["in_bill_id"].ToString(); infoModel.in_g_no =row["in_g_no"]!=DBNull.Value?Convert.ToDecimal(row["in_g_no"]):0; infoModel.cust_in_bill_id = row["cust_in_bill_id"].ToString(); infoModel.code_t = row["code_t"].ToString(); infoModel.mg_name = row["mg_name"].ToString(); infoModel.mg_spec = row["mg_spec"].ToString(); infoModel.g_qty = Convert.ToDecimal(row["g_qty"]); infoModel.g_unit = Universal.GetStatusName(_Dictionary.CreatStoreUnit, row["g_unit"].ToString()); infoModel.curr_code = Universal.GetStatusName(_Dictionary.ParaExchrate, row["curr_code"].ToString()); infoModel.unit_price = Convert.ToDecimal(row["unit_price"]); infoModel.trade_ttl = Convert.ToDecimal(row["trade_ttl"]); infoModel.orign_coun = Universal.GetCcodesDisplayName(_Pccode.SourceDestination, row["orign_coun"].ToString()); infoModel.zm_code = Universal.GetCcodesDisplayName(_Pccode.ExemptWay, row["zm_code"].ToString()); infoModel.baxh = row["baxh"].ToString(); infoModel.code_s = row["code_s"].ToString(); infoModel.rate = Convert.ToDecimal(row["rate"]); infoModel.rate_id = row["rate_id"].ToString(); infoModel.pkgs = Convert.ToDecimal(row["pkgs"]); infoModel.gross = Convert.ToDecimal(row["gross"]); infoModel.net = Convert.ToDecimal(row["net"]); infoModel.qty_1 = Convert.ToDecimal(row["qty_1"]); infoModel.unit_code1 = Universal.GetStatusName(_Dictionary.CreatStoreUnit, row["unit_code1"].ToString()); infoModel.usd_unit_price = Convert.ToDecimal(row["usd_unit_price"]); infoModel.usd_trade_ttl = Convert.ToDecimal(row["usd_trade_ttl"]); infoModel.version_num = row["version_num"].ToString(); infoModel.qty_2 = Convert.ToDecimal(row["qty_2"]); infoModel.unit_code2 = Universal.GetStatusName(_Dictionary.CreatStoreUnit, row["unit_code2"].ToString()); infoModel.loc = row["loc"].ToString(); list.Add(infoModel); } model.infoModel = list; JavaScriptSerializer json = new JavaScriptSerializer(); model.jsonList = json.Serialize(list); } } } } catch (Exception ex) { var msgT = Server.UrlEncode("系统出现错误,请联系客服或管理员处理!"); var msgD = Server.UrlEncode(ex.Message); UrlHelper url = new UrlHelper(this.Request.RequestContext); this.Response.Redirect(url.Action("Index", "Error", new { msgTitle = msgT, msgDetail = msgD })); } return View(model); }
/// <summary> /// 批量保存数据 /// </summary> /// <param name="dt">数据</param> /// <param name="extraInfo">额外参数</param> /// <param name="userInfo">用户信息</param> public override object SaveImportData(DataTable dt, Dictionary<string, object> extraInfo, UserInfo userInfo) { string columnName = string.Empty; object objExtra = null; Dictionary<string, string> dict = null; object objCellValue = null; List<string> listAssetsId = new List<string>(); string strAssetsId = string.Empty; foreach (DataRow dr in dt.Rows) { foreach (DataColumn dc in dt.Columns) { columnName = dc.ColumnName; if (extraInfo.TryGetValue(columnName, out objExtra)) { dict = objExtra as Dictionary<string, string>; if (dict != null) { objCellValue = dr[columnName]; if (!ExcelImportHelper.ObjectIsNullOrEmpty(objCellValue)) { dr[columnName] = dict[objCellValue.ToString()]; } } } } } try { //保存任务数据 List<TaskUtil> list = dt.ToList<TaskUtil>(); foreach (var item in list) { TaskHelper.SaveTask(item); } return dt; } catch (Exception ex) { throw ex; } }
/// <summary> /// 获取表名列表 /// </summary> /// <param name="dbName">数据库名</param> /// <returns></returns> public override List<Table> LoadTables(string dbName) { DBUtility.SqlHelper.ConnectionString = DBUtility.SqlHelper.BuildConnectionString(dbName); string sql = string.Format(getTablesExpression, dbName); DataTable temp = new DataTable(); temp.Load(DBUtility.SqlHelper.ExecuteReader(sql, null)); return temp.ToList<Table>(r => new Table() { Name = r[0].ToString() }); }
/// <summary> /// 根据表名获取数据库某张表的完整信息。 /// </summary> /// <param name="tableName">表名</param> /// <returns></returns> public override List<Column> LoadDataTableInfo(string tableName) { string sql = string.Format(sqlExpression, tableName); DataTable temp = new DataTable(); temp.Load(DBUtility.SqlHelper.ExecuteReader(sql, null)); return temp.ToList<Column>(); }
/// <summary> /// 获取所有数据库名称列表。 /// </summary> /// <returns></returns> public override List<string> LoadDataBases() { DataTable temp = new DataTable(); temp.Load(DBUtility.SqlHelper.ExecuteReader(getDataBaseExpression, null)); return temp.ToList<string>(row => row[0].ToString()); }
// Runtime Analysis: // A = COUNT(*) in actrec // C = COUNT(*) in clnnum // J = COUNT(*) in jobtyp // E = COUNT(*) in employ // N = count of records in initialJobList, or 1 if initialJobList is null private void MultiJobSelector_Load(object sender, EventArgs e) { try { using (var con = Connections.GetOLEDBConnection()) { using (var joblist = con.GetTempDBF()) { if (initialJobList != null) { // Runtime: O(N*Log(N)) // specifying that recnum is unique means it is indexed, which gives us better join times con.ExecuteNonQuery("create table {0} (recnum n(10,0) not null unique)", joblist); foreach (var recnum in initialJobList) { con.ExecuteNonQuery("insert into {0} (recnum) values ({1})", joblist, recnum); } } // Runtime: O(A+A*Log(R)+A*Log(J)+A*Log(E)) var sql = string.Format( "select actrec.recnum as JobNumber, jobnme as JobName, jobtyp.typnme as JobType, actrec.status as JobStatus" + ", clnnum as ClientNumber, reccln.shtnme as ClientName" + ", alltrim(employ.fullst) as Supervisor, actrec.contct as SiteContact" + " from actrec" + " left join reccln on clnnum = reccln.recnum" + " left join jobtyp on actrec.jobtyp = jobtyp.recnum" + " left join employ on actrec.sprvsr = employ.recnum"); // Runtime: O(A*Log(N)) sql += initialJobList != null ? string.Format(" join {0} ijoblst on actrec.recnum = ijoblst.recnum", joblist) : ""; // Runtime: O(N) datalines_dt = con.GetDataTable("Job List", sql); datalines_dt = datalines_dt.ToList<DataLine>().ToDataTable("datalines"); current_dt = datalines_dt; // Total Runtime: O(N*Log(N) + A + A*Log(R) + A*Log(J) + A*Log(E) + A*Log(N) + N) // we know that A > N, so we can drop the N*Log(N) term // O(MAX(A*Log(R)+A*Log(J)+A*Log(E)+A*Log(N))) - simplify to dominate term without constants // O(A*MAX(Log(R),Log(J),Log(E),Log(N))) - simplify to dominate term // O(A*Log(MAX(R,J,E,N)) } // load the project supervisors var supervisors_dt = con.GetDataTable("Supervisors", "select distinct employ.recnum, employ.fullst from actrec join employ on actrec.sprvsr = employ.recnum order by employ.fullst asc"); cmbSupervisors.DataSource = new string [] { "*Any*" }.Concat( (from s in supervisors_dt.Rows.ToIEnumerable() select s["fullst"].ToString().Trim())).ToArray(); cmbSupervisors.SelectedIndex = 0; } } catch (Exception ex) { Env.Log("{0}\r\n{1}", ex.Message, ex.StackTrace); MessageBox.Show("Error Loading Job List Form", "Error", MessageBoxButtons.OK); } #if false // Runtime: O(A+A*Log(R)+A*Log(J)+A*Log(E)) datalines_dt = Connections.Connection.GetDataTable("datalines" , "select actrec.recnum as JobNumber, jobnme as JobName, jobtyp.typnme as JobType, actrec.status as JobStatus" + ", clnnum as ClientNumber, reccln.shtnme as ClientName" + ", alltrim(employ.fstnme) + ' ' + alltrim(employ.lstnme) as Supervisor, actrec.contct as SiteContact" + " from actrec" + " left join reccln on clnnum = reccln.recnum" + " left join jobtyp on actrec.jobtyp = jobtyp.recnum" + " left join employ on actrec.sprvsr = employ.recnum"); var progress = new ProgressDialog(datalines_dt.Rows.Count + 1); progress.Text = "Getting Job List"; if (initial_job_filter != null) { progress.Show(); } // Runtime: O(A) // get the correct types, and make sure all columns exist var datalines = datalines_dt.ToList<DataLine>(); datalines_dt = datalines.ToDataTable("datalines"); var jobs = (from j in smbtable.GetAll<actrec>() where initial_job_filter(j) select j.recnum).ToArray(); progress.Tick(); if (initial_job_filter != null) { progress.Text = "Filtering Job List"; // Runtime: O(AN) datalines_dt = datalines_dt.FilterRows(row => { progress.Tick(); return jobs.Contains(Convert.ToInt64(row["JobNumber"])); // return true; }); } current_dt = datalines_dt; progress.Close(); // Total Runtime: O(A + A*Log(R) + A*Log(J) + A*Log(E) + A + AN) // O(A*MAX(Log(R),Log(J),Log(E),N)) - Simplify to dominate term // O(AN) #endif this.chkStatus1.Checked = Env.GetConfigVar("mjselect_chkStatus1", true, true); this.chkStatus2.Checked = Env.GetConfigVar("mjselect_chkStatus2", false, true); this.chkStatus3.Checked = Env.GetConfigVar("mjselect_chkStatus3", true, true); this.chkStatus4.Checked = Env.GetConfigVar("mjselect_chkStatus4", true, true); this.chkStatus5.Checked = Env.GetConfigVar("mjselect_chkStatus5", true, true); this.chkStatus6.Checked = Env.GetConfigVar("mjselect_chkStatus6", true, true); DoFilter(); this.txtFilter.KeyPress += new KeyPressEventHandler(txtFilter_KeyPress); this.chkStatus1.CheckedChanged += new EventHandler(chkStatus_CheckedChanged); this.chkStatus2.CheckedChanged += chkStatus_CheckedChanged; this.chkStatus3.CheckedChanged += chkStatus_CheckedChanged; this.chkStatus4.CheckedChanged += chkStatus_CheckedChanged; this.chkStatus5.CheckedChanged += chkStatus_CheckedChanged; this.chkStatus6.CheckedChanged += chkStatus_CheckedChanged; }
public void ToListTest() { DataTable dt = new DataTable(); dt.Columns.Add("id", Type.GetType("System.Int32")); dt.Columns.Add("name", Type.GetType("System.String")); DataRow dr = dt.NewRow(); dr[0] = 1; dr[1] = "a"; dt.Rows.Add(dr); dr = dt.NewRow(); dr[0] = 2; dr[1] = "b"; dt.Rows.Add(dr); dr = dt.NewRow(); dr[0] = 3; dr[1] = "c"; dt.Rows.Add(dr); int expected = 3; int actual; actual = dt.ToList<TestEntity>().Count; Assert.AreEqual(expected, actual, "验证ToList方法的正确性。"); }
public static void Main(string[] args) { //DES加密解密 //EncryptionUtils des = new EncryptionUtils(); //Console.WriteLine(des.MD5Encrypt16("张季").ToUpper()); //Console.WriteLine(des.MD5Encrypt32("张季").ToUpper()); ////Redis 非关系型数据库 //var redisClient = new RedisClient(localHostIP, redisServicePort); ////向Redis中添加一个Key/Value对+ //redisClient.Set<string>("username", "edisonchou"); ////从Redis中读取一个Value值 //string userName = redisClient.Get<string>("username"); //Console.WriteLine(userName); //CreateRandomPassWord create = new CreateRandomPassWord(); //Console.WriteLine(create.GenerateStrongPassword(8)); //Console.WriteLine(create.CreateTemporaryPassword(8)); //Random random = new Random(); //int i = random.Next(10, 999); //UploadAndDownloadImage img = new UploadAndDownloadImage(); //img.SaveImageToURL("test", ""); //string s = "1111111"; //string ss = s.Substring(0,s.Length>=100?100: s.Length); //Console.WriteLine(ss); //二维码 //string str = "http://www.baidu.com"; //string filtPath = @"D:\图片\58.png"; //Server.MapPath("~/Images/1.jpg"); //string logoFilePath = @"D:\图片\3.png";// Server.MapPath("~/Images/faceIcon.png"); //if (QrCode.CreateQRcode(str, filtPath, true, logoFilePath)) //{ // Console.WriteLine("OK!"); //} //else //{ // Console.WriteLine("Fail!!!!!"); //} DataTable dt = new DataTable(); var list=dt.ToList<Login>(); try { var s = WechatBase.GetAccess_Token("wx82315287d9fc52ae", "00dbe4ae7304f61ef9f77c7bd8fc0f48"); string url = string.Format("https://api.weixin.qq.com/cgi-bin/user/info?access_token={0}&openid={1}&lang={2}", s, "oHHout3XiYzGrFnyD6YxF2QU_sNQ", "zh_CN"); WebClient wc = new WebClient(); var t=wc.DownloadString(url); Console.WriteLine(t); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } Console.ReadLine(); }