C# DataTable.ToList方法代碼示例

        /// <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)
//.........這裏部分代碼省略.........

        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";

//.........這裏部分代碼省略.........

        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();
        }