淺析在Access中模擬SqlServer存儲過程翻頁_Access數據庫教程

      編輯Tag賺U幣
      教程Tag:暫無Tag,歡迎添加,賺取U幣!

      推薦:淺談在ACCESS中LIKE的用法
      Access里like的通配符用法是這樣: “?”表示任何單一字符; “*”表示零個或多個字符; “#”表示任何一個數字 所以應該是: select * from databasename where fieldname like '*XX*' 原來在SQL SERVER 里是用%%的,在ACCESS里是用**號的,怪不得都找不到數

      sql server中翻頁存儲過程:
      Create PROC blog_GetPagedPosts
      (
      @PageIndex int,
      @PageSize int,
      @BlogID int=0,
      @PostType int=-1,
      @CategoryID int=-1,
      @Hiding bit =0,
      @Count int output

      )
      as
      DECLARE @PageLowerBound int
      DECLARE @PageUpperBound int
      SET @PageLowerBound = @PageSize * @PageIndex - @PageSize
      SET @PageUpperBound = @PageLowerBound + @PageSize + 1

      Create Table #IDs
      (
      TempID int IDENTITY (1, 1) NOT NULL,
      EntryID int not null
      )
      Insert into #IDs(EntryID) select DISTINCT [ID] from view_Content where CategoryID=@CategoryID and blogID=@BlogID order by [ID] desc
      SELECT vc.*
      FROM View_Content vc
      INNER JOIN #IDS tmp ON (vc .[ID] = tmp.EntryID)
      WHERE tmp.TempID > @PageLowerBound
      AND tmp.TempID < @PageUpperBound and vc.Hiding=0
      ORDER BY tmp.TempID
      SELECT @Count=COUNT(*) FROM #IDS
      SELECT @Count=COUNT(*) FROM #IDS
      DROP TABLE #IDS
      return @Count
      GO

      在Access中由于不支持存儲過程,不能建立臨時表只能在程序中實現
      Access中實現如下,這也是我在myblog Access版中使用的:
      public List<DayBook> GetPagedPost(PagedPost p, out int TotalRecords)
      {
      List<DayBook> list = new List<DayBook>();

      using (OleDbConnection conn = GetOleDbConnection())
      {
      StringBuilder sql = new StringBuilder();
      sql.AppendFormat("select [ID] from blog_Content as p ");//構造查詢條件
      if (p.CategoryID > 0)
      {
      sql.AppendFormat(",blog_Categories AS c, blog_Links AS l WHERE c.CategoryID=l.CategoryID and (p.ID=l.PostID ) and c.CategoryID={1} and p.BlogID={0} ",p.BlogID, p.CategoryID);
      }
      else
      {
      sql.AppendFormat(" where p.blogID={0} ", p.BlogID);
      }
      if (p.PostType != PostType.Undeclared)
      {
      sql.AppendFormat(" and p.PostType={0} ", (int)p.PostType);
      }
      sql.Append(" order by p.[DateUpdated] desc");
      // NetDiskContext.Current.Context.Response.Write(sql.ToString());
      //NetDiskContext.Current.Context.Response.End();
      OleDbCommand MyComm = new OleDbCommand(sql.ToString(), conn);
      List<int> IDs = new List<int>(); //獲取主題ID列表
      conn.Open();
      using (OleDbDataReader dr = MyComm.ExecuteReader())
      {
      while (dr.Read())
      {
      IDs.Add((int)dr[0]);

      }
      }

      TotalRecords=IDs.Count;//返回記錄總數
      if (TotalRecords < 1)
      return list;
      int pageLowerBound = p.PageSize * p.PageIndex - p.PageSize;//記錄索引
      int pageUpperBound = pageLowerBound + p.PageSize ;
      StringBuilder sb = new StringBuilder();
      if (TotalRecords >= pageLowerBound)
      for (int i = pageLowerBound; i < TotalRecords && i < pageUpperBound; i++)
      {
      sb.AppendFormat("{0},", IDs[i]);//構造ID in() 條件,取其中一頁
      }
      else return list; //如沒有記錄返回空表
      if(sb.Length>1)
      sb.Remove(sb.Length - 1, 1);//刪除最后一個逗號
      MyComm.CommandText = string.Format("SELECT b.* , c.Account as Account FROM blog_Content b, Blog_Config c where b.BlogID=c.BlogID and b.[ID] in ({0}) order by b.dateadded desc", sb.ToString());
      using (OleDbDataReader dr = MyComm.ExecuteReader())
      {
      while (dr.Read())
      {
      list.Add(DataHelp.LoadDayBook(dr));
      }
      }
      return list;
      }
      }

       

      分享:解讀ACCESS中默認保存路徑的修改方法
      1.Microsoft Access文件默認保存路徑修改方法 Access默認保存路徑也是C:\My Documents目錄。默認保存路徑修改方法:打開Access程序,單擊菜單欄“工具”下的“選項”命令,接著在出現的“選項”對話框中選擇“常規”標簽頁面,將“默認數據庫文件夾”改成專

      來源:模板無憂//所屬分類:Access數據庫教程/更新時間:2010-02-23
      相關Access數據庫教程