app.config:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add connectionString="Data Source=PC-20120110JFDT;Initial Catalog=test1;Persist Security Info=True;User ID=sa;Password=yhb" name="connstr" />
<add connectionString="Data Source=PC-20120110JFDT;Initial Catalog=Test;Persist Security Info=True;User ID=sa;Password=yhb" name="connstr2" />
<add connectionString="Data Source=PC-20120110JFDT;Initial Catalog=School;Persist Security Info=True;User ID=sa;Password=yhb" name="connstr3" />
</connectionStrings>
</configuration>
ClassModel_student.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace WindowsFormsApplication1
{
class ClassModel_student
{
public string FId
{
get;
set;
}
public string FName
{
get;
set;
}
public string FAge
{
get;
set;
}
public string FGender
{
get;
set;
}
public string FMath
{
get;
set;
}
public string FEnglish
{
get;
set;
}
public string Fbirthday
{
get;
set;
}
}
}
c#
List<ClassModel_student> cmsLi = new List<ClassModel_student>();
private void button2_Click(object sender, EventArgs e)
{
string connstr= ConfigurationManager.ConnectionStrings["connstr2"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
using(SqlCommand cmd=new SqlCommand("usp_print",conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@pagesize", textBox1.Text));
cmd.Parameters.Add(new SqlParameter("@pageIndex", textBox2.Text));
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)//如果找到多行记录
{
while (reader.Read())
{
ClassModel_student cms = new ClassModel_student();
cms.FId = reader.IsDBNull(reader.GetOrdinal("FId")) ? string.Empty : Convert.ToString( reader.GetInt32(reader.GetOrdinal("FId")));
cms.FName = reader.IsDBNull(reader.GetOrdinal("FName")) ? string.Empty : reader.GetString(reader.GetOrdinal("FName"));
cms.FAge = reader.IsDBNull(reader.GetOrdinal("FAge")) ? string.Empty : Convert.ToString( reader.GetInt32(reader.GetOrdinal("FAge")));
cms.FGender = reader.IsDBNull(reader.GetOrdinal("FGender")) ? string.Empty :Convert.ToString( reader.GetBoolean(reader.GetOrdinal("FGender")));
cms.FMath = reader.IsDBNull(reader.GetOrdinal("FMath")) ? string.Empty : Convert.ToString( reader.GetDouble(reader.GetOrdinal("FMath")));
cms.FEnglish = reader.IsDBNull(reader.GetOrdinal("FEnglish")) ? string.Empty : Convert.ToString( reader.GetDouble(reader.GetOrdinal("FEnglish")));
cms.Fbirthday = reader.IsDBNull(reader.GetOrdinal("Fbirthday")) ? string.Empty :Convert.ToString( reader.GetDateTime(reader.GetOrdinal("Fbirthday")));
cmsLi.Add(cms);
}
}
}
}
dataGridView1.DataSource = cmsLi;
}
sql:
create proc usp_print @pagesize int,@pageIndex int
as
begin
select * from
(select *,ROW_NUMBER()over(order by FId asc)as rownumber from MyStudents) as t1 where t1.rownumber between @pagesize*(@pageIndex-1)+1 and @pagesize*@pageIndex
end