Detailed Implementation of the datagridview bindingnavigator control in the database stored procedure page

After referring to a lot of materials and continuous debugging, I finally figured out the problem. Implemented a simple paging example, although this approach is not very good.

ProgramThere are 3 controls

Bindingnavigator: the one on the datagridview control. In the project, the name is bindngrdemo.

Datagridview: dgvdemo

Bindingsource: Do not use bindsedemo.

 

The sample database pub of SQL Server is used.

Write paging stored procedures in pub Database

 Create   Procedure [DBO]. [pagination] @ Columns Varchar (500 ), -- The columns to be displayed, divide by comma @ Tablename Varchar (100 ), -- The name of the table to be searched @ Ordercolumnname Varchar (100 ), -- The name of the column to be used in order @ Order   Varchar (50 ), -- The order method, ASC or DESC @ Where   Varchar (100 ), -- The where condition, if there is not conditon use 1 = 1 @ Pageindex Int , -- Current page index @ Pagesize Int , -- The size of the page @ Pagecount Int   Output  -- The total page count, define as output parameter  As  Begin  Declare @ Sqlrecordcount nvarchar (100) -- The SQL statement to get the total count of the records  Declare @ Sqlselect nvarchar (1000) -- The SQL select statment  Set @ Sqlrecordcount = N 'Select @ recordcount = count (*) from' + @ Tablename + 'Where' + @ Where  Declare @ Recordcount Int  Exec Sp_executesql @ sqlrecordcount, n '@ Recordcount int output' , @ Recordcount Output   -- Transfer the parameter dynamic  If (@ Recordcount % @ pagesize = 0) Set @ Pagecount = @ recordcount/@ pagesize Else  Set @ Pagecount = @ recordcount/@ pagesize + 1 Set @ Sqlselect = N 'Select' + @ Columns + 'From (select row_number () over (order' + @ Ordercolumnname + '' + @ Order +') As tempid, * from' + @ Tablename + 'Where' + @ Where + ') As temptablename where tempid' + STR (@ pageindex-1) * @ pagesize + 1) + And' + STR (@ pageindex * @ pagesize) Exec (@ Sqlselect) End

 

Below isCodeNow:

Several variables are defined in the project:

 Public Partial ClassFrmdatapagination: FORM {# RegionDefine the page sizeStatic IntPagesize = 20;Static IntPagecount = 0;# Endregion...}
 

The preceding two variables are defined as the size of each page when pages are stored in the database, and the other is used to receive the total number of pages output when pages are stored in the stored procedure.

 

Add a function to generate the SELECT command to pass the SQL command to sqldataadapter.

         Private   Static Sqlcommand generateselectcommand ( String Columns, String Tablename, String Ordercolumnname,String Order, String Wherecondition, Int Pageindex, Int Pagesize, sqlconnection conn) {sqlcommand sqlcmd = New Sqlcommand ( "Pagination" , Conn); sqlcmd. commandtype = commandtype. storedprocedure; sqlcmd. Parameters. addwithvalue ( "@ Columns" , Columns); sqlcmd. Parameters. addwithvalue ( "@ Tablename" , Tablename); sqlcmd. Parameters. addwithvalue ( "@ Ordercolumnname" , Ordercolumnname); sqlcmd. Parameters. addwithvalue ("@ Order" , Order); sqlcmd. Parameters. addwithvalue ( "@ Where" , Wherecondition); sqlcmd. Parameters. addwithvalue ( "@ Pageindex" , Pageindex); sqlcmd. Parameters. addwithvalue ( "@ Pagesize" , Pagesize); sqlparameter pagecount = New Sqlparameter ( "@ Pagecount" , Sqldbtype. INT); pagecount. Direction = parameterdirection. output; sqlcmd. Parameters. Add (pagecount); sqlcmd. updatedrowsource = updaterowsource. None; Return Sqlcmd ;}

 

The following is the loaddata function. In this function, you can specify the Table, column, and other parameters you want to select.

There are two types of data binding

You can directly use SQL command + SQL datareader

You can also use sqldataadapter + dataset or datatable.

         // Load the page data          Private  Static   Void Loaddata ( Int Pageindex, datagridview dgvdemo ){ String Strconn = "Server = (local); database = pubs; Integrated Security = sspi" ; Try { Using (Sqlconnection conn = New Sqlconnection (strconn) {conn. open (); // Use sqlcommand to fetch the data                      /* Sqlcommand cmd = generateselectcommand ("fname", "employee", "fname", "ASC", "1 = 1", pageindex, pagesize, Conn );  Sqldatareader RDR = cmd. executereader ();  Bindingsource bindsedemo = new bindingsource ();  Bindsedemo. datasource = RDR;  Dgvdemo. datasource = bindsedemo;  */                      // Use sqldataadapter to fetch the data Sqldataadapter sqlda = New Sqldataadapter (); sqlda. selectcommand = generateselectcommand ("Fname, lname, hire_date" , "Employee" , "Fname" , "ASC" , "1 = 1" , Pageindex, pagesize, Conn); datatable DS = New Datatable (); sqlda. Fill (DS); pagecount = ( Int ) Sqlda. selectcommand. Parameters [ "@ Pagecount" ]. Value; bindingsource bindsedemo = New Bindingsource (); bindsedemo. datasource = Ds; dgvdemo. datasource = bindsedemo; sqlda. Dispose (); // Use sqldataadapter dataset to fetch the data                      /*  Dataset DS = new dataset ();  Sqldataadapter sqlda = new sqldataadapter ();  Sqlda. selectcommand = generateselectcommand ("fname", "employee", "fname", "ASC", "1 = 1", pageindex, pagesize, Conn );  Sqlda. Fill (DS );  Pagecount = (INT) sqlda. selectcommand. Parameters ["@ pagecount"]. value; Dgvdemo. datasource = Ds. Tables [0];  Sqlda. Dispose ();  */ Conn. Close ();}} Catch (Exception ex) {MessageBox. Show (ex. Message, "Information :" , Messageboxbuttons. OK, messageboxicon. Information );}}

 

Code for form load

 Private   Void Frmdatapagination_load ( Object Sender, eventargs e ){// Even if there is no records, there is no exception Loaddata (1, dgvdemo ); // Set the status of the bindingnavigator Control              If (Pagecount = 0 | pagecount = 1) {bindngrdemo. movefirstitem. Enabled = False ; Bindngrdemo. movelastitem. Enabled = False ; Bindngrdemo. movenextitem. Enabled = False ; Bindngrdemo. movepreviousitem. Enabled = False ;} Else {Bindngrdemo. movefirstitem. Enabled = False ; Bindngrdemo. movelastitem. Enabled = True ; Bindngrdemo. movenextitem. Enabled = True ; Bindngrdemo. movepreviousitem. Enabled = False ; Bindngrdemo. positionitem. Text = "1" ; Bindngrdemo. countitem. Text = "{" + Pagecount. tostring () + "}" ;}}

 

Events of the bindingnavigator Control

Includes 4 keys

Backward, last, forward, beginning

         Private   Void Bindingnavigatormovenextitem_click ( Object Sender, eventargs e ){ Int Currentpage = convert. toint32 (bindngrdemo. positionitem. Text ); If (Currentpage <pagecount ){ Int Page = currentpage + 1; bindngrdemo. positionitem. Text = page. tostring (); loaddata (page, dgvdemo ); If (Page = pagecount) {bindngrdemo. movenextitem. Enabled = False ; Bindngrdemo. movelastitem. Enabled = False ;} If (Page> = 2) {bindngrdemo. movepreviousitem. Enabled = True ; Bindngrdemo. movefirstitem. Enabled = True ;}} Else {MessageBox. Show ( "This is the last page" , "Information" , Messageboxbuttons. OK, messageboxicon. Information );}} Private   Void Bindingnavigatormovepreviousitem_click ( Object Sender, eventargs e ){Int Currentpage = convert. toint32 (bindngrdemo. positionitem. Text ); If (Currentpage> = 2 ){ Int Page = currentpage-1; bindngrdemo. positionitem. Text = page. tostring (); loaddata (page, dgvdemo ); If (Page = 1) {bindngrdemo. movepreviousitem. Enabled = False ; Bindngrdemo. movefirstitem. Enabled = False ;} If (Page <= pagecount) {bindngrdemo. movenextitem. Enabled = True ; Bindngrdemo. movelastitem. Enabled = True ;}} Else {MessageBox. Show ( "This is the first page" , "Information" , Messageboxbuttons. OK, messageboxicon. Information );}} Private   Void Bindingnavigatormovelastitem_click ( Object Sender, eventargs e) {loaddata (pagecount, dgvdemo); bindngrdemo. positionitem. Text = pagecount. tostring (); bindngrdemo. movelastitem. Enabled = False ; Bindngrdemo. movenextitem. Enabled =False ; Bindngrdemo. movepreviousitem. Enabled = True ; Bindngrdemo. movefirstitem. Enabled = True ;} Private   Void Bindingnavigatormovefirstitem_click ( Object Sender, eventargs e) {loaddata (1, dgvdemo); bindngrdemo. positionitem. Text = "1" ; Bindngrdemo. movefirstitem. Enabled = False ; Bindngrdemo. movepreviousitem. Enabled = False ; Bindngrdemo. movenextitem. Enabled = True ; Bindngrdemo. movelastitem. Enabled = True ;}}

 

 

Now it's all done, and the other parts are compiled by yourself, such as the display of the dview control.