Question

.net c#读取sql sever数据库问题，如何写SQL语句

加载数据时，先读取数据库中前30条，点击更多后加载31-60行的信息，后面这个SQL语句如何写？谢谢！

Answer 1

如果你会编程,可以这样做:
private void Form1_Load(object sender, EventArgs e)
{
try {
SqlDataAdapter adr = new SqlDataAdapter("select top 30 * from (select top 30 * from table order by id asc) TT order by TT.序号 ASC", con);
DataSet Dat = new DataSet();
adr.Fill(Dat, "top");
dataGridView1.DataSource = Dat.Tables["top"].DefaultView;
//dataGridView1.DataBindings();
}
catch { }
}
int but1=0, but2=0, but01=0, but02=0;
private void button1_Click(object sender, EventArgs e)
{
try
{
if (but01 != 0 && but01 > 30)
{
but01 = but01 - 30;
but02 = but01 - 30;
}
SqlCommand com = new SqlCommand("select count(*) from chunyunjiabandangAn", con);
if (con.State == ConnectionState.Closed) con.Open();
int countint = 0;
try { countint = Convert.ToInt32(com.ExecuteScalar()); com.Dispose(); con.Close(); }
catch { com.Dispose(); con.Close(); }
if (but02<=0)
{
but02 = 0;
string SQL = "SELECT * FROM (SELECT *,(ROW_NUMBER() OVER ( ORDER BY 序号 ASC)) AS ROWNUMBER FROM table) TT WHERE TT.ROWNUMBER <=" + but01 + " AND TT.ROWNUMBER >" + but02;
SqlDataAdapter adr = new SqlDataAdapter(SQL, con);
DataSet Dat = new DataSet();
adr.Fill(Dat, "top");
dataGridView1.DataSource = Dat.Tables["top"].DefaultView;
but2 = 30;
but1 = 0;
this.button1.Enabled = false;
}
else if(but01>0 && but02>=0)
{

string SQL = "SELECT * FROM (SELECT *,(ROW_NUMBER() OVER ( ORDER BY 序号 ASC)) AS ROWNUMBER FROM table) TT WHERE TT.ROWNUMBER <=" + but01 + " AND TT.ROWNUMBER >" + but02;
SqlDataAdapter adr = new SqlDataAdapter(SQL, con);
DataSet Dat = new DataSet();
adr.Fill(Dat, "top");
dataGridView1.DataSource = Dat.Tables["top"].DefaultView;

but1 = but02;
but2 = but1 - 30;
this.button2.Enabled = true;
this.button1.Enabled = true;
}
}
catch { }
}

private void button2_Click(object sender, EventArgs e)
{
try
{
if (but1 == 0 && but2 == 0)
{
but1 = 30;
but2 = but1 + 30;
}
SqlCommand com = new SqlCommand("select count(*) from chunyunjiabandangAn", con);
if (con.State == ConnectionState.Closed) con.Open();
int countint = 0;
try { countint = Convert.ToInt32(com.ExecuteScalar()); com.Dispose(); con.Close(); } catch { com.Dispose(); con.Close(); }

if (but2 > countint)
{
but2 = countint;
but01 = but2;
but02 = but1;
string SQL = "SELECT * FROM (SELECT *,(ROW_NUMBER() OVER ( ORDER BY 序号 ASC)) AS ROWNUMBER FROM chunyunjiabandangAn) TT WHERE TT.ROWNUMBER <=" + but2 + " AND TT.ROWNUMBER >" + but1;
SqlDataAdapter adr = new SqlDataAdapter(SQL, con);
DataSet Dat = new DataSet();
adr.Fill(Dat, "top");
dataGridView1.DataSource = Dat.Tables["top"].DefaultView;
this.button2.Enabled = false;
}
else
{
string SQL = "SELECT * FROM (SELECT *,(ROW_NUMBER() OVER ( ORDER BY 序号 ASC)) AS ROWNUMBER FROM chunyunjiabandangAn) TT WHERE TT.ROWNUMBER <=" + but2 + " AND TT.ROWNUMBER >" + but1;
SqlDataAdapter adr = new SqlDataAdapter(SQL, con);
DataSet Dat = new DataSet();
adr.Fill(Dat, "top");
dataGridView1.DataSource = Dat.Tables["top"].DefaultView;
but1 = but2;
but01 = but1;
but02 = but01-30;
but2 = but1 + 30;

this.button1.Enabled = true;
this.button2.Enabled = true;
}
}
catch { }
}
以上代码,就能完全实现的你的要求.
希望对你有所帮助!

Answer 2

其实这个问题很简单，你肯定是会的、你的这个问题就跟一般去公司面试的题差不多（写出一条Sql语句：取出表A中第31到第40记录（SQLServer,以自动增长的ID作为主键,注意：ID可能不是连续的）。解决办法就是：
1、把你查出来的前30条记录放在一个集合中(listtable)或者是得到第30条记录的编码（或者ID）.
2、写SQL语句：select top 30 * from A where id not in listtable.
3、在“更多”的点击事件下写触发的方法。
希望可以帮助到你。。

Answer 3

设置IDENTITY列，插入数据，而无需考虑

Answer 4

如果你是网页类型的话试试ajax嘛，主流的，微博啊QQ空间啊都在用。而且还不用你点击，你把页面往下拉的时候自动显示。

追问

具体如何实现，兄弟，有详细代码吗，谢谢！Ajax就了解一点儿。 我邮箱：810379191@qq.com

追答

已经发了 你看下 如果不行可以百度一下 类似的功能实现代码都是大同小异的 如果你想看案例书 可以去找那个 ASP.NET项目案例 那本书看看 里面有几个BS项目用到这个技术

Answer 5

Row_Number Over()