asp.net分页，Effective paging with List View control in ASP.NET

绝地灬酷狼 2022-08-03 03:13 105阅读 0赞

原文：[http://www.codeproject.com/Articles/142326/Effective-paging-with-List-View-control-in-ASP-NET][http_www.codeproject.com_Articles_142326_Effective-paging-with-List-View-control-in-ASP-NET]

demo:

![SouthEast][]

主要代码：

/*
                CREATE PROCEDURE [dbo].[Profile_Total]
                AS
                BEGIN
    
                    SET NOCOUNT ON
                    SELECT COUNT(*) FROM Profile
                END
                
             ==================================================
              
                CREATE PROCEDURE [dbo].[Profile_GET]
                    @PageSize int = null,
                    @CurrentPage int = null,
                    @SortExpression    nvarchar(max) = null
                AS
    
                BEGIN
                    SET NOCOUNT ON
    
                    DECLARE @SqlString nvarchar(max)
                    Declare @UpperBand int
                    Declare @LowerBand int        
        
                    SET @LowerBand  = (@CurrentPage - 1) * @PageSize
                    SET @UpperBand  = (@CurrentPage * @PageSize) + 1
    
                    BEGIN
    
                    WITH tempProfile AS(                    
                        SELECT 
                            [ProfileId],
                            [Name],
                            [Address],
                            [Email],
                            [Mobile], 
                            [IsActive] = CASE [IsActive] WHEN 1 THEN 'Active' WHEN 0 THEN 'DeActive' END,
                            ROW_NUMBER() OVER (ORDER BY 
                                CASE @SortExpression WHEN 'ProfileId'   THEN [ProfileId] END,
                                CASE @SortExpression WHEN 'Name'        THEN [Name] END,
                                CASE @SortExpression WHEN 'Address'     THEN [Address] END,
                                CASE @SortExpression WHEN 'Email'       THEN [Email] END,
                                CASE @SortExpression WHEN 'Mobile'      THEN [Mobile] END,
                                CASE @SortExpression WHEN 'Status'      THEN [IsActive] END
                             ) AS RowNumber
                            FROM [dbo].[Profile]        
                    )     
        
                    SELECT 
                        [ProfileId],
                        [Name],
                        [Address],
                        [Email],
                        [Mobile], 
                        [IsActive]
                        FROM 
                            tempProfile 
                        WHERE 
                            RowNumber > @LowerBand AND RowNumber < @UpperBand
                            ORDER BY  
                                CASE WHEN @SortExpression ='ProfileId'      THEN [ProfileId] END,
                                CASE WHEN @SortExpression ='ProfileId DESC' THEN [ProfileId] END DESC,
                                CASE WHEN @SortExpression ='Name'           THEN [Name] END,
                                CASE WHEN @SortExpression ='Name DESC'      THEN [Name] END DESC,
                                CASE WHEN @SortExpression ='Address'        THEN [Address] END,
                                CASE WHEN @SortExpression ='Address DESC'   THEN [Address] END DESC,
                                CASE WHEN @SortExpression ='Email'          THEN [Email] END,
                                CASE WHEN @SortExpression ='Email DESC'     THEN [Email] END DESC,
                                CASE WHEN @SortExpression ='Mobile'         THEN [Mobile] END,
                                CASE WHEN @SortExpression ='Mobile DESC'    THEN [Mobile] END DESC,
                                CASE WHEN @SortExpression ='Status'         THEN [IsActive] END,
                                CASE WHEN @SortExpression ='Status DESC'    THEN [IsActive] END DESC
                    END
                END
              
             */

[System.ComponentModel.DataObject(true)]
            public class CCDataSource
            {
                public CCDataSource()
                {
                }
    
                [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select, false)]
                public Int32 TotalRowCount(Int32 startRowIndex, Int32 pageSize, String sortExpression)
                {
                    Int32 intTotalProfile = 0;
    
                    using (SqlConnection conn = new SqlConnection("SQLConnetionString......"))
                    {
                        SqlCommand cmdSelect = new SqlCommand();
    
                        conn.Open();
                        cmdSelect.CommandText = "Profile_Total";
                        cmdSelect.CommandType = CommandType.StoredProcedure;
                        cmdSelect.Connection = conn;
    
                        SqlDataReader dataReader = cmdSelect.ExecuteReader();
    
                        dataReader.Read();
                        intTotalProfile = Convert.ToInt32(dataReader[0]);
                    }
                    return intTotalProfile;
                }
    
                [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select, true)]
                public static DataTable GetProfileData(Int32 startRowIndex, Int32 pageSize, String sortExpression)
                {
                    DataTable profileDataTable = new DataTable();
    
                    using (SqlConnection conn = new SqlConnection("SQLConnetionString......"))
                    {
                        SqlCommand cmdSelect = new SqlCommand();
    
                        conn.Open();
                        cmdSelect.CommandText = "Profile_GET";
                        cmdSelect.CommandType = CommandType.StoredProcedure;
                        cmdSelect.Connection = conn;
    
                        startRowIndex = Convert.ToInt32(startRowIndex / pageSize) + 1;
    
                        if (String.IsNullOrEmpty(sortExpression))
                            sortExpression = "ProfileId";
    
                        cmdSelect.Parameters.AddWithValue("@CurrentPage", startRowIndex);
                        cmdSelect.Parameters.AddWithValue("@PageSize", pageSize);
                        cmdSelect.Parameters.AddWithValue("@SortExpression", sortExpression);
    
                        SqlDataAdapter dataAdapter = new SqlDataAdapter();
                        dataAdapter.SelectCommand = cmdSelect;
    
                        dataAdapter.Fill(profileDataTable);
                    }
                    //startRowIndex = Convert.ToInt32(startRowIndex / pageSize) + 1;
                    return profileDataTable;
                }
            }

另外，导入导入excel： [http://www.gemboxsoftware.com/spreadsheet/overview][http_www.gemboxsoftware.com_spreadsheet_overview]

If you need to import Excel file to a database and already have ADO.NET code entities in place (DataSets, DataTables, DataAdapters) you can do this task trivially with this Excel C\# / VB.NET library.  
Here is a sample Excel C\# code how to import Excel to DataTable (after which you can easily transfer DataTable to database with DataAdapter):

ExcelFile ef = new ExcelFile();
     
    // Depending on the format of the input file, you need to change this:
    DataTable dataTable = new DataTable();
    dataTable.Columns.Add("FirstName", typeof(string));
    dataTable.Columns.Add("LastName", typeof(string));
     
    // Load Excel file.
    ef.LoadXls("FileName.xls");
     
    // Select the first worksheet from the file.
    ExcelWorksheet ws = ef.Worksheets[0];
     
    // Extract the data from the worksheet to the DataTable.
    // Data is extracted starting at first row and first column for 10 rows or until the first empty row appears.
    ws.ExtractToDataTable(dataTable, 10, ExtractDataOptions.StopAtFirstEmptyRow, ws.Rows[0], ws.Columns[0]);

[http_www.codeproject.com_Articles_142326_Effective-paging-with-List-View-control-in-ASP-NET]: http://www.codeproject.com/Articles/142326/Effective-paging-with-List-View-control-in-ASP-NET
[SouthEast]: /images/20220731/8144cf83111e478ca97a22269eb37f93.png
[http_www.gemboxsoftware.com_spreadsheet_overview]: http://www.gemboxsoftware.com/spreadsheet/overview