迴圈讀取TOP2000條資料

private void xunhuan(string keyword, string filename) { try { while (true) { richTextBox1.Clear(); string sql = "select top 2000 id,productname,merchantId,PinPai,chandi,color,BrowseNodeKeyword from [gwkproduct] where ident like '" + keyword + "_%' and (shortintro='' or shortintro is null)"; DataSet ds = DbHelperSQL.Query(sql); int count = 0; //uint RowsCount = 0; //DataSet ds = ReadAllData(ref RowsCount, textBox1.Text.Trim()); DataTable dt = ds.Tables[0]; int row_count = dt.Rows.Count; for (int j = 0; j < dt.Rows.Count; j++) { int id = (int)dt.Rows[j]["id"]; string productname = dt.Rows[j]["productname"].ToString().Replace('\'', ' '); int merchantid = (int)dt.Rows[j]["merchantId"]; string pinpai = dt.Rows[j]["PinPai"].ToString().Replace('\'', ' '); string chandi = dt.Rows[j]["chandi"].ToString().Replace('\'', ' '); string color = dt.Rows[j]["color"].ToString().Replace('\'', ' '); string fenlei = ""; try { string browsenodekeyword = dt.Rows[j]["BrowseNodeKeyword"].ToString(); fenlei = DbHelperSQL.GetSingle("select catname from category where curpath='" + browsenodekeyword + "'").ToString(); } catch { fenlei = ""; } string merchtname = DbHelperSQL.GetSingle("select merchName from merchant where merchId='" + merchantid + "'").ToString(); string shortintro = productname + " " + merchtname; if (pinpai != "" && pinpai != null) { shortintro = shortintro + " 品牌:" + pinpai; } if (color != "" && color != null) { shortintro = shortintro + " 顏色:" + color; } if (chandi != "" && chandi != null) { shortintro = shortintro + " 產地:" + chandi; } if (fenlei != "") { shortintro = shortintro + " " + fenlei; } string strSql = "update gwkproduct set shortintro='" + shortintro + "' where id='" + id + "'"; count += 1; DbHelperSQL.ExecuteSql(strSql); richTextBox1.AppendText(count.ToString() + "." + strSql + "\r\n"); richTextBox1.ScrollToCaret(); writeErr(filename, count.ToString() + "." + strSql); } if (row_count < 2000) { return; } } } catch { xunhuan(keyword, filename); } } public static void writeErr(string filename, string errstring) { //string errfile = Application.StartupPath + @"\back.txt"; StreamWriter sw = new StreamWriter(filename, true); sw.WriteLine(errstring); sw.Close(); } //迴圈讀取所有資料 public DataSet ReadAllData(ref uint RowsCount,string ident) { int GetRows = 1000; //每次取記錄數 int rowCount = 0; //每次取出的資料 uint startid = 0; //起始讀取ID DataSet ds = new DataSet(); //真到讀取所有資料 for (int i = 0; ; i++) { DataTable dt = new DataTable(); dt = ReadDataByFill(GetRows, startid, ident); rowCount = dt.Rows.Count;//取當前返回表資料中資料量 RowsCount += uint.Parse(rowCount.ToString()); if (RowsCount > 0) { startid = uint.Parse(dt.Rows[rowCount - 1]["id"].ToString()); //擷取最大編號以參考取資料 ds.Tables.Add(dt.Copy()); ds.Tables[i].TableName = "tb" + i.ToString(); } if (rowCount < GetRows) { break; } } return ds; } //為全文新索引頁預留空間產生資料(每次讀取TOPN條記錄，以防一次讀取太慢出現應用程式假死) public DataTable ReadDataByFill(int Top, uint Startid,string ident) { string sql = "Select "; if (Top > 0) { sql += " top " + Top.ToString(); } sql += " id,productname,merchantId,PinPai,chandi,color,BrowseNodeKeyword from [gwkproduct]" + " where ident like '" + ident + "_%' and (shortintro='' or shortintro is null) and id>" + Startid.ToString() + " order by id asc"; DataTable dt = GetDataTable(sql); return dt; } //返回DataTable private DataTable GetDataTable(string sql) { SqlConnection conn = new SqlConnection(@"server=221.122.127.101;database=gouwuke_chs;uid=admin;pwd=7418;"); SqlDataAdapter sda = new SqlDataAdapter(sql, conn); DataSet ds = new DataSet(); sda.Fill(ds); conn.Close(); return ds.Tables[0]; }