 |
| Preview of Paging Control |
 |
| Add Toolstrip and change properties like above |
|
|
|
|
|
Codebehind:
private void FillGrid(string SortColumn)
{
if (con.con.State == ConnectionState.Closed)
{
con.con.Open();
}
string City = "" ;
string PinCode = "" ;
string PostOffice = "" ;
string District ="" ;
string State = "";
string Name = cmbName.Text == "Select" ? "" : cmbName.Text;
string MEMBNO = cmbMemberNo.Text == "Select" ? "" : cmbMemberNo.Text;
string BloodGroup = "" ;
string query = "";
query = "SELECT * FROM( select row_number() OVER (ORDER BY " + SortColumn + ") AS SlNo,SlNo as Id,TITLE," +
" MEMBNO,NAME,ADDR1,ADDR2,ROAD,CITY," +
" PIN,BLOODGRP,PHONE,ThumbPhoto from Members " +
"left join MembershipCard on MEMBNO=CardNO " +
"where CITY like '%" + City + "%' and PIN like '%" + PinCode + "%' and ROAD like '%" + PostOffice + "%' " +
"and NAME like '%" + Name + "%' and MEMBNO like '%" + MEMBNO + "%' and BLOODGRP like '%" + BloodGroup +
"%' and Status=1 ) AS alias WHERE SlNo BETWEEN (@PageIndex - 1) * @PageSize + 1 " +
" AND @PageIndex * @PageSize order by " + SortColumn + " asc ";
query = query.Replace("@PageSize", pageRows.ToString());
query = query.Replace("@PageIndex", CurrentPage.ToString());
SqlDataAdapter da = new SqlDataAdapter(query, con.con);
DataSet ds = new DataSet();
da.Fill(ds);
dt = ds.Tables[0];
dt1 = new DataTable();
dt1.Columns.Add("Select", System.Type.GetType("System.Boolean"));
dt1.Columns.Add("SlNo");
dt1.Columns.Add("Id");
dt1.Columns.Add("TITLE");
dt1.Columns.Add("MEMBNO");
dt1.Columns.Add("NAME");
dt1.Columns.Add("ADDR1");
dt1.Columns.Add("ADDR2");
dt1.Columns.Add("ROAD");
dt1.Columns.Add("CITY");
dt1.Columns.Add("PIN");
dt1.Columns.Add("BLOODGRP");
dt1.Columns.Add("PHONE");
//dt1.Columns.Add("Photo", typeof(Bitmap));
//dt1.Columns["Photo"].AllowDBNull = true;
tempdt = new DataTable();
tempdt.Columns.Add("Select", System.Type.GetType("System.Boolean"));
tempdt.Columns.Add("SlNo");
tempdt.Columns.Add("Id");
tempdt.Columns.Add("TITLE");
tempdt.Columns.Add("MEMBNO");
tempdt.Columns.Add("NAME");
tempdt.Columns.Add("ADDR1");
tempdt.Columns.Add("ADDR2");
tempdt.Columns.Add("ROAD");
tempdt.Columns.Add("CITY");
tempdt.Columns.Add("PIN");
tempdt.Columns.Add("BLOODGRP");
tempdt.Columns.Add("PHONE");
//tempdt.Columns.Add("Photo", typeof(Bitmap));
//tempdt.Columns["Photo"].AllowDBNull = true;
DataRow dr;
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
dr = dt1.NewRow();
dr["Select"] = false;
dr["SlNo"] = dt.Rows[i]["SlNo"].ToString();
dr["Id"] = dt.Rows[i]["Id"].ToString();
dr["TITLE"] = dt.Rows[i]["TITLE"].ToString();
dr["MEMBNO"] = dt.Rows[i]["MEMBNO"].ToString();
dr["NAME"] = dt.Rows[i]["NAME"].ToString();
dr["ADDR1"] = dt.Rows[i]["ADDR1"].ToString();
dr["ADDR2"] = dt.Rows[i]["ADDR2"].ToString();
dr["ROAD"] = dt.Rows[i]["ROAD"].ToString();
dr["CITY"] = dt.Rows[i]["CITY"].ToString();
dr["PIN"] = dt.Rows[i]["PIN"].ToString();
dr["BLOODGRP"] = dt.Rows[i]["BLOODGRP"].ToString();
//dr["PHONE"] = dt.Rows[i]["PHONE"].ToString();
//if (dt.Rows[i]["ThumbPhoto"] == DBNull.Value)
//{
// System.IO.FileInfo imageInfo = new System.IO.FileInfo(Path.GetDirectoryName(
// System.Reflection.Assembly.GetExecutingAssembly().Location) + "\\face_outline.gif");
// Photocontent = new byte[imageInfo.Length];
// imagestream = imageInfo.OpenRead();
// imagestream.Read(Photocontent, 0, Photocontent.Length);
// Photocontent = CommonFunctions.createThumnail(imagestream, 50, 50);
// dr["Photo"] = CommonFunctions.byteArrayToImage(Photocontent);
//}
//else
//{
// dr["Photo"] = CommonFunctions.byteArrayToImage((byte[])dt.Rows[i]["ThumbPhoto"]);
//}
dt1.Rows.Add(dr);
}
dgvUsers.AllowUserToAddRows = false;
dgvUsers.AllowUserToDeleteRows = false;
dgvUsers.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dgvUsers.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
dgvUsers.DataSource = dt1;
dgvUsers.Refresh();
con.con.Close();
}
private void RefreshGrid(string SortColumn)
{
FillGrid(SortColumn);
if (con.con.State == ConnectionState.Closed)
{
con.con.Open();
}
cmd = new SqlCommand("select COUNT(*) from Members", con.con);
int count = int.Parse(cmd.ExecuteScalar().ToString());
lblmsg.Text = "Total Number : " + count;
PagesCount = Convert.ToInt32(Math.Ceiling(count * 1.0 / pageRows));
CurrentPage = 1;
RefreshPagination();
RebindGridForPageChange();
}
private void RebindGridForPageChange()
{
//Rebinding the Datagridview with data
int datasourcestartIndex = (CurrentPage - 1) * pageRows;
tempdt = dt1.Clone();
DataRow dr = null;
int ii = 0;
int jj = 0;
tempdt.Rows.Clear();
for (int i = datasourcestartIndex; i < datasourcestartIndex + pageRows; i++)
{
if (i >= dt1.Rows.Count)
break;
dr = tempdt.NewRow();
for (jj = 0; jj <= dgvUsers.Columns.Count - 1; jj++)
{
if (jj == 13)
{
dr[jj] = (Bitmap)dt1.Rows[i][jj];
}
else
{
dr[jj] = dt1.Rows[i][jj].ToString();
}
}
tempdt.Rows.Add(dr);
}
dgvUsers.DataSource = tempdt;
dgvUsers.Refresh();
}
private void RefreshPagination()
{
ToolStripButton[] items = new ToolStripButton[] { toolStripButton1, toolStripButton2, toolStripButton3, toolStripButton4, toolStripButton5 };
//pageStartIndex contains the first button number of pagination.
int pageStartIndex = 1;
if (PagesCount > 10 && CurrentPage > 2)
pageStartIndex = CurrentPage - 2;
if (PagesCount > 10 && CurrentPage > PagesCount - 2)
pageStartIndex = PagesCount - 4;
for (int i = pageStartIndex; i < pageStartIndex + 5; i++)
{
if (i > PagesCount)
{
items[i - pageStartIndex].Visible = false;
}
else
{
//Changing the page numbers
items[i - pageStartIndex].Text = i.ToString(CultureInfo.InvariantCulture);
//Setting the Appearance of the page number buttons
if (i == CurrentPage)
{
items[i - pageStartIndex].BackColor = Color.Black;
items[i - pageStartIndex].ForeColor = Color.White;
}
else
{
items[i - pageStartIndex].BackColor = Color.White;
items[i - pageStartIndex].ForeColor = Color.Black;
}
}
}
//Enabling or Disalbing pagination first, last, previous , next buttons
if (CurrentPage == 1)
btnBackward.Enabled = btnFirst.Enabled = false;
else
btnBackward.Enabled = btnFirst.Enabled = true;
if (CurrentPage == PagesCount)
btnForward.Enabled = btnLast.Enabled = false;
else
btnForward.Enabled = btnLast.Enabled = true;
}
