Hiii,
i have used following links for datagridview paging and binding.
I have used DataGridViewButtonColumn to add Edit and Delete buttons in the grid. I have set the display index of columns. On load of form it diplays in set order but after adding or editing or deleting record it changes its order.
please help ....
Implement Paging DataGridView in Windows Forms (WinForms) Application using C# and VB.Net
http://www.aspforums.net/Threads/106667/DataGrid-Paging-with-two-tables-join-winform-C/
public partial class Model : Form
{
public Model()
{
InitializeComponent();
}
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da;
//Set the Page Size.
int PageSize = 5;
private void Model_Load(object sender, EventArgs e)
{
BindGrid(1);
BindCompanyName();
gridData.ColumnHeadersDefaultCellStyle.BackColor = Color.GreenYellow;
this.gridData.EnableHeadersVisualStyles = false;
this.gridData.ColumnHeadersHeight = 30;
gridData.ClearSelection();
gridData.BackgroundColor = System.Drawing.SystemColors.Control;
DataGridViewButtonColumn column1 = new DataGridViewButtonColumn();
column1.Name = "Delete";
column1.Text = "Delete";
column1.UseColumnTextForButtonValue = true;
column1.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
column1.Width = 80;
gridData.Columns.Add(column1);
gridData.AutoSize = true;
gridData.AllowUserToAddRows = false;
gridData.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
this.gridData.Columns["ID"].Visible = false;
this.gridData.Columns["RowNumber"].Visible = false;
this.gridData.GridColor = Color.Black;
this.gridData.BorderStyle = BorderStyle.FixedSingle;
this.gridData.RowsDefaultCellStyle.BackColor = Color.White;
this.gridData.AlternatingRowsDefaultCellStyle.BackColor = Color.WhiteSmoke;
this.gridData.Columns[1].HeaderText = "COMPANY NAME";
this.gridData.Columns[1].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
this.gridData.Columns["Name"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
this.gridData.Columns[2].HeaderText = "MODEL NAME";
this.gridData.Columns[2].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
this.gridData.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
//gridData.CellClick += new DataGridViewCellEventHandler(gridData_CellClick);
DataGridViewButtonColumn column2 = new DataGridViewButtonColumn();
column2.Name = "Edit";
column2.Text = "Edit";
column2.UseColumnTextForButtonValue = true;
column2.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
column2.Width = 80;
gridData.Columns.Add(column2);
gridData.AutoSize = true;
gridData.AllowUserToAddRows = false;
gridData.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
//gridData.CellClick += new DataGridViewCellEventHandler(gridData_CellClick_1);
gridData.Columns["Delete"].DisplayIndex = 0;
gridData.Columns["Name"].DisplayIndex = 1;
gridData.Columns[2].DisplayIndex = 2;
gridData.Columns["Edit"].DisplayIndex = 5;
gridData.SelectionMode = DataGridViewSelectionMode.CellSelect;
}
private void BindGrid(int pageIndex)
{
// string constring = "Data Source=.;Initial Catalog=MobileBilling;Integrated Security=True";
//using (SqlConnection con = new SqlConnection(con))
{
string query = @"SELECT ROW_NUMBER() OVER (
ORDER BY Company.[ID] desc
) AS RowNumber
,Company.[Name] as CompanyName
,Model.[ID]
,Model.[Name]
INTO #Results
FROM [MstMblCompany] Company
INNER JOIN MstMblModel Model
ON Company.ID = Model.MblCompany
SELECT @RecordCount = COUNT(*)
FROM #Results
SELECT *
FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results";
using (SqlCommand cmd = new SqlCommand(query, con))
{
cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
cmd.Parameters.AddWithValue("@PageSize", PageSize);
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
DataTable dt = new DataTable();
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(dt);
gridData.DataSource = dt;
}
int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
this.PopulatePager(recordCount, pageIndex);
}
}
}
private void PopulatePager(int recordCount, int currentPage)
{
List<Page> pages = new List<Page>();
int startIndex, endIndex;
int pagerSpan = 5;
//Calculate the Start and End Index of pages to be displayed.
double dblPageCount = (double)((decimal)recordCount / Convert.ToDecimal(PageSize));
int pageCount = (int)Math.Ceiling(dblPageCount);
startIndex = currentPage > 1 && currentPage + pagerSpan - 1 < pagerSpan ? currentPage : 1;
endIndex = pageCount > pagerSpan ? pagerSpan : pageCount;
if (currentPage > pagerSpan % 2)
{
if (currentPage == 2)
{
endIndex = 5;
}
else
{
endIndex = currentPage + 2;
}
}
else
{
endIndex = (pagerSpan - currentPage) + 1;
}
if (endIndex - (pagerSpan - 1) > startIndex)
{
startIndex = endIndex - (pagerSpan - 1);
}
if (endIndex > pageCount)
{
endIndex = pageCount;
startIndex = ((endIndex - pagerSpan) + 1) > 0 ? (endIndex - pagerSpan) + 1 : 1;
}
//Add the First Page Button.
if (currentPage > 1)
{
pages.Add(new Page { Text = "First", Value = "1" });
}
//Add the Previous Button.
if (currentPage > 1)
{
pages.Add(new Page { Text = "<<", Value = (currentPage - 1).ToString() });
}
for (int i = startIndex; i <= endIndex; i++)
{
pages.Add(new Page { Text = i.ToString(), Value = i.ToString(), Selected = i == currentPage });
}
//Add the Next Button.
if (currentPage < pageCount)
{
pages.Add(new Page { Text = ">>", Value = (currentPage + 1).ToString() });
}
//Add the Last Button.
if (currentPage != pageCount)
{
pages.Add(new Page { Text = "Last", Value = pageCount.ToString() });
}
//Clear existing Pager Buttons.
pnlPager.Controls.Clear();
//Loop and add Buttons for Pager.
int count = 0;
foreach (Page page in pages)
{
Button btnPage = new Button();
btnPage.Location = new System.Drawing.Point(38 * count, 5);
btnPage.Size = new System.Drawing.Size(35, 20);
btnPage.Name = page.Value;
btnPage.Text = page.Text;
btnPage.Enabled = !page.Selected;
btnPage.Click += new System.EventHandler(this.Page_Click);
pnlPager.Controls.Add(btnPage);
count++;
}
}
private void Page_Click(object sender, EventArgs e)
{
Button btnPager = (sender as Button);
this.BindGrid(int.Parse(btnPager.Name));
}
public class Page
{
public string Text { get; set; }
public string Value { get; set; }
public bool Selected { get; set; }
}
private void gridData_MouseClick_1(object sender, MouseEventArgs e)
{
try
{
int ID = Convert.ToInt32(gridData.SelectedRows[0].Cells[0].Value.ToString());
String str1 = "Select Id,Name ,MblCompany from MstMblModel where ID ='" + ID + "'";
//String str1 = "Select m.ID as ModelID,m.Name, m.MblCompany,mf.Name as Company from MstMblModel m inner join MstMblCompany mf on m.MblCompany = mf.Id where m.Id ='" + ID + "'";
SqlCommand cmd22 = new SqlCommand(str1, con);
con.Open();
SqlDataReader dr = cmd22.ExecuteReader();
if (dr.Read())
{
txtName.Text = dr[1].ToString();
cmbName.SelectedValue = dr[2].ToString();
}
con.Close();
//int ID = Convert.ToInt32(gridData.SelectedRows[0].Cells[0].Value.ToString());
//txtName.Text = Convert.ToString(gridData.SelectedRows[0].Cells[2].Value.ToString());
//cmbName.SelectedValue = Convert.ToString(gridData.SelectedRows[0].Cells[1].Value.ToString());
btnAdd.Enabled = false;
//btnUpdate.Enabled = true;
//btnUpdate.Visible = true;
//btnDelete.Visible = true;
//btnDelete.Enabled = true;
}
catch (Exception)
{
throw;
}
}
#endregion
private void BindCompanyName()
{
try
{
SqlCommand cmd = new SqlCommand("Select * from MstMblCompany ", con);
con.Open();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataTable dt = new DataTable();
da.Fill(dt);
DataRow row = dt.NewRow();
row["Name"] = "Please select Name";
dt.Rows.InsertAt(row, 0);
cmbName.DataSource = dt;
cmbName.ValueMember = "ID";
cmbName.DisplayMember = "Name";
cmbName.SelectedIndex = 0;
con.Close();
}
catch (Exception)
{
throw;
}
}
protected void ClearAll()
{
txtName.Text = "";
gridData.ClearSelection();
cmbName.SelectedIndex = -1;
btnAdd.Enabled = true;
}
private void btnAdd_Click_1(object sender, EventArgs e)
{
if (txtName.Text == "")
{
MessageBox.Show("Name Field cannnot be blank");
//txtName.BackColor = Color.Orange;
txtName.Focus();
}
else
try
{
String str1 = "Select * from MstMblModel where Name='" + txtName.Text + "'";
SqlCommand cmd1 = new SqlCommand(str1, con);
con.Open();
SqlDataReader dr = cmd1.ExecuteReader();
if (dr.Read())
{
String a = dr[1].ToString();
if (a == txtName.Text)
{
MessageBox.Show("Record with the same value is already Exists!!!");
}
ClearAll();
con.Close();
}
else
{
SqlCommand cmd = new SqlCommand();
con.Close();
con.Open();
string query = "Insert into MstMblModel values('" + txtName.Text + "','" + cmbName.SelectedValue + "')";
cmd = new SqlCommand(query, con);
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
BindGrid(1);
this.Refresh();
ClearAll();
MessageBox.Show("Record Added Successfully!!!");
}
}
catch (Exception)
{
throw;
}
}
private void btnUpdate_Click_1(object sender, EventArgs e)
{
try
{
con.Open();
int ID = Convert.ToInt32(gridData.SelectedRows[0].Cells[0].Value.ToString());
string query = "Update MstMblModel set Name='" + txtName.Text + "',MblCompany='" + cmbName.SelectedValue + "' where ID='" + ID + "'";
SqlCommand cmd = new SqlCommand(query, con);
cmd.ExecuteNonQuery();
cmd.Dispose();
ClearAll();
con.Close();
MessageBox.Show("Record Updated Succesfully!!!");
BindGrid(1);
this.Refresh();
}
catch (Exception)
{
throw;
}
}
private void btnReset_Click_1(object sender, EventArgs e)
{
ClearAll();
}
private void gridData_CellClick_1(object sender, DataGridViewCellEventArgs e)
{
if (gridData.Columns[e.ColumnIndex].Name == "Delete")
{
CustomSMS cmsg = new CustomSMS();
DialogResult dialogresult = cmsg.ShowDialog();
if (dialogresult == DialogResult.Yes)
{
try
{
using (SqlCommand cmd = new SqlCommand("DELETE FROM MstMblModel where ID = @Id;Select m. ID,mc.Name,m.Name as ModelName from MstMblModel m inner join MstMblCompany mc on m.MblCompany = mc.ID", con))
{
//int ID = Convert.ToInt16(gridData.Rows[e.RowIndex].Cells[0].Value);
int ID = Convert.ToInt32(gridData.Rows[e.RowIndex].Cells["ID"].Value.ToString());
cmd.Parameters.AddWithValue("@Id", ID);
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
sda.Fill(dt);
gridData.DataSource = dt;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
else
{
int ID = Convert.ToInt32(gridData.Rows[e.RowIndex].Cells["ID"].Value.ToString());
string queryRead = "Select Name,MblCompany from MstMblModel where ID ='" + ID + "'";
SqlCommand cmdRead = new SqlCommand(queryRead, con);
con.Open();
SqlDataReader dr = cmdRead.ExecuteReader();
string Model = ""; int MCompany = 0;
if (dr.Read())
{
Model = dr["Name"].ToString();
MCompany = int.Parse(dr["MblCompany"].ToString());
}
con.Close();
//Name = gridData.SelectedRows[0].Cells["Name"].Value.ToString();
Edit_Model popup = new Edit_Model(MCompany, Model);
DialogResult dialogresult = popup.ShowDialog();
if (dialogresult == DialogResult.OK)
{
try
{
int Comp_Name = popup.Edit_Company;
string ModelName = popup.Edit_ModelName;
ID = Convert.ToInt32(gridData.Rows[e.RowIndex].Cells["ID"].Value.ToString());
//int ID = Convert.ToInt16(gridData.Rows[e.RowIndex].Cells[0].Value);
string query = "Update MstMblModel set MblCompany='" + Comp_Name + "', Name = '" + ModelName + "' where ID='" + ID + "'";
SqlCommand cmd = new SqlCommand(query, con);
con.Open();
cmd.ExecuteNonQuery();
cmd.Dispose();
ClearAll();
con.Close();
MessageBox.Show("Record Updated Succesfully!!!");
BindGrid(1);
this.Refresh();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
else if (dialogresult == DialogResult.Cancel)
{
//MessageBox.Show("You clicked on Cancel");
}
popup.Dispose();
}
}
}
}
