查询数据在dataGridView上分页,在Form1_Load里已经执行一遍select * from 工资表
在show里还要执行一遍select * from 工资表,请教有办法解决show里不要在执行一遍查询。如果有更好的分页方法更好啊。 public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
public static int INum=0,AllCount=0;
int Sizes = 4;
private void Form1_Load(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection("server=.;pwd=;uid=sa;database=db_09"))
{
SqlDataAdapter da = new SqlDataAdapter("select * from 工资表",con);
DataTable dt = new DataTable();
da.Fill(dt);
int i=dt.Rows.Count;
AllCount = i;
int m = i % Sizes;
if (m == 0)
{
m = i / Sizes;
}
else
{
m = i / Sizes+1;
}
this.label3.Text = m.ToString();
show(0,4);
this.label4.Text = "1";
} } private void show(int i,int j)
{
SqlConnection con = new SqlConnection("server=.;pwd=;uid=sa;database=db_09");
SqlDataAdapter daone = new SqlDataAdapter("select * from 工资表", con);
DataSet dsone = new DataSet();
daone.Fill(dsone, i, j, "one");
this.dataGridView1.DataSource = dsone.Tables["one"].DefaultView;
dsone = null;
} private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
this.dataGridView1.DataSource = null;
this.label4.Text = "1";
show(0, Convert.ToInt16(this.label4.Text)*4);
} private void linkLabel2_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
this.label4.Text = this.label3.Text;
this.dataGridView1.DataSource = null;
show(Convert.ToInt16(this.label4.Text) * 4-4,AllCount-1);
} private void linkLabel3_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
this.dataGridView1.DataSource = null;
int m=Convert.ToInt16(this.label4.Text) - 1;
if (m < 1)
{
this.label4.Text = "1";
}
else
{
this.label4.Text = m.ToString();
}
int a=Convert.ToInt16(this.label4.Text) * 4-4; show(a,4);
} private void linkLabel4_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
this.dataGridView1.DataSource = null;
int m = Convert.ToInt16(this.label4.Text) + 1;
if (m > Convert.ToInt16(this.label3.Text))
{
this.label4.Text = this.label3.Text;
}
else
{
this.label4.Text = m.ToString();
}
int a = Convert.ToInt16(this.label4.Text) * 4 -4; show(a, 4);
}
}
在show里还要执行一遍select * from 工资表,请教有办法解决show里不要在执行一遍查询。如果有更好的分页方法更好啊。 public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
public static int INum=0,AllCount=0;
int Sizes = 4;
private void Form1_Load(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection("server=.;pwd=;uid=sa;database=db_09"))
{
SqlDataAdapter da = new SqlDataAdapter("select * from 工资表",con);
DataTable dt = new DataTable();
da.Fill(dt);
int i=dt.Rows.Count;
AllCount = i;
int m = i % Sizes;
if (m == 0)
{
m = i / Sizes;
}
else
{
m = i / Sizes+1;
}
this.label3.Text = m.ToString();
show(0,4);
this.label4.Text = "1";
} } private void show(int i,int j)
{
SqlConnection con = new SqlConnection("server=.;pwd=;uid=sa;database=db_09");
SqlDataAdapter daone = new SqlDataAdapter("select * from 工资表", con);
DataSet dsone = new DataSet();
daone.Fill(dsone, i, j, "one");
this.dataGridView1.DataSource = dsone.Tables["one"].DefaultView;
dsone = null;
} private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
this.dataGridView1.DataSource = null;
this.label4.Text = "1";
show(0, Convert.ToInt16(this.label4.Text)*4);
} private void linkLabel2_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
this.label4.Text = this.label3.Text;
this.dataGridView1.DataSource = null;
show(Convert.ToInt16(this.label4.Text) * 4-4,AllCount-1);
} private void linkLabel3_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
this.dataGridView1.DataSource = null;
int m=Convert.ToInt16(this.label4.Text) - 1;
if (m < 1)
{
this.label4.Text = "1";
}
else
{
this.label4.Text = m.ToString();
}
int a=Convert.ToInt16(this.label4.Text) * 4-4; show(a,4);
} private void linkLabel4_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
this.dataGridView1.DataSource = null;
int m = Convert.ToInt16(this.label4.Text) + 1;
if (m > Convert.ToInt16(this.label3.Text))
{
this.label4.Text = this.label3.Text;
}
else
{
this.label4.Text = m.ToString();
}
int a = Convert.ToInt16(this.label4.Text) * 4 -4; show(a, 4);
}
}
首先:SqlDataAdapter da = new SqlDataAdapter("select * from 工资表",con);
DataTable dt = new DataTable();
da.Fill(dt);
int i=dt.Rows.Count;如果数据量大的情况下就会产生问题,曾经有朋友以前就问过为什么8万条数据用你上面用到的方法统计数据总数会有问题。
应该用"select count(*) from 工资表"这种SQL语句来查询总共有多少条记录。
Command对象也称为数据库命令对象,Command对象主要执行包括添加、删除、修改及查询数据的操作的命令。也可以用来执行存储过程。用于执行存储过程时需要将Command对象的CommandType 属性设置为CommandType.StoredProcedure,默认情况下CommandType 属性为CommandType.Text,表示执行的是普通SQL语句。
Command主要有三个方法:
ExecuteNonQuery () :执行一个SQL语句,返回受影响的行数,这个方法主要用于执行对数据库执行增加、更新、删除操作,注意查询的时候不是调用这个方法。
ExecuteReader ():执行一个查询的SQL语句,返回一个DataReader对象。
ExecuteScalar ():从数据库检索单个值。这个方法主要用于统计操作。下面是统计表中有多少记录的实例。//实例化Connection对象
SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AspNetStudy;Persist Security Info=True;User ID=sa;Password=sa");
//实例化Command对象
SqlCommand command = new SqlCommand("select count(1) as 男性人数 from UserInfo where sex=1", connection);
//打开Connection对象
connection.Open();
//执行SQL语句
int count = int.Parse(command.ExecuteScalar().ToString());
//关闭Connection对象
connection.Close();
Response.Write("在UserInfo表里共有" + count + "个男性。");
//实现分页 后台代码,这里gridview命名为:gv ,希望对你有帮助
try
{ LblCurrentIndex.Text = "第 " + (gv.PageIndex + 1).ToString() + " 页";
LblPageCount.Text = "共 " + gv.PageCount.ToString() + " 页";
LblRecordCount.Text = "总共 " + ds.Tables[0].Rows.Count.ToString() + " 条记录";
if (ds.Tables[0].Rows.Count == 0)//如果没有记录显示
{
btnFirst.Enabled = false;
btnPrev.Enabled = false;
btnNext.Enabled = false;
btnLast.Enabled = false; LblCurrentIndex.Enabled = false;
LblPageCount.Enabled = false;
LblRecordCount.Enabled = false;
}
else if (gv.PageCount == 1)//只有一页记录可以显示
{
btnFirst.Enabled = false;
btnPrev.Enabled = false;
btnNext.Enabled = false;
btnLast.Enabled = false;
}
else //有多页记录
{
if (CurrentPageIndex == 0) //当前为第一页
{
btnFirst.Enabled = false;
btnPrev.Enabled = false;
btnNext.Enabled = true;
btnLast.Enabled = true;
}
else if (CurrentPageIndex == gv.PageCount - 1)//当前为最后一页
{
btnFirst.Enabled = true;
btnPrev.Enabled = true;
btnNext.Enabled = false;
btnLast.Enabled = false;
}
else //当前为第一页与最后一页之间
{
btnFirst.Enabled = true;
btnPrev.Enabled = true;
btnNext.Enabled = true;
btnLast.Enabled = true;
}
// 计算生成分页页码,分别为:"首 页" "上一页" "下一页" "尾 页"
btnFirst.CommandName = "1";
btnPrev.CommandName = (gv.PageIndex == 0 ? "1" : gv.PageIndex.ToString()); btnNext.CommandName = (gv.PageCount == 1 ? gv.PageCount.ToString() : (gv.PageIndex + 2).ToString());
btnLast.CommandName = gv.PageCount.ToString();
} }
catch (Exception ex)
{
Response.Write("数据库错误,错误原因:" + ex.Message);
Response.End();
}
public int CurrentPageIndex //获取当前页
{
get { return this.gv.PageIndex; }
set { this.gv.PageIndex = value; }
}
//前台代码:
<tr>
<td >
<asp:GridView ID="gv" runat="server" >
</asp:GridView>
</td>
</tr>
<tr>
<td style="height:25px; background-color:#5D7B9D">
<asp:LinkButton ID="btnFirst" CommandArgument="first" OnClick="PagerButtonClick" runat="server">首 页</asp:LinkButton>
<asp:LinkButton ID="btnPrev" CommandArgument="prev" OnClick="PagerButtonClick" runat="server">上一页</asp:LinkButton>
<asp:LinkButton ID="btnNext" CommandArgument="next" OnClick="PagerButtonClick" runat="server">下一页</asp:LinkButton>
<asp:LinkButton ID="btnLast" CommandArgument="last" OnClick="PagerButtonClick" runat="server">尾 页</asp:LinkButton>
</td>
</tr>