ado.net实现分页和用dataadapter实现增加修改删除

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;

namespace _20150408
{
public partial class Form1 : Form
{
int pageIndex;
public Form1()
{
InitializeComponent();
}

private void fillToolStripMenuItem_Click(object sender, EventArgs e)
{

GetData();

}

//使用dataadapter进行更新
private void updateToolStripMenuItem_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt = dataGridView1.DataSource as DataTable;
//获取数据库连接字符串
string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

using (SqlConnection conn = new SqlConnection(connStr))
{

SqlDataAdapter adapter = new SqlDataAdapter();
//构造InsertCommand
string sqlStrInsert ="insert into Student values(@name,@address)";
SqlCommand insertCommand = new SqlCommand(sqlStrInsert ,conn);
insertCommand.Parameters.Add("@name", SqlDbType.NVarChar, 50, "sName");//sName为table的列名
insertCommand.Parameters.Add("@address", SqlDbType.NVarChar, 50, "sAddress");
adapter.InsertCommand = insertCommand;
//构造UpadateCommand
string sqlStrUpadate = "update Student set sName=@name,sAddress=@address where sId=@id";
SqlCommand upadateCommand = new SqlCommand(sqlStrUpadate,conn);
upadateCommand.Parameters.Add("@name",SqlDbType.NVarChar,50,"sName");
upadateCommand.Parameters.Add("@address", SqlDbType.NVarChar, 50, "sAddress");
upadateCommand.Parameters.Add("@id", SqlDbType.Int, 50, "sId");
adapter.UpdateCommand = upadateCommand;
//构造DeleteCommand
string sqlDelete = "delete from Student where sId=@id";
SqlCommand deleteCommand=new SqlCommand (sqlDelete,conn);
deleteCommand.Parameters.Add("@id",SqlDbType.Int ,4,"sId");
adapter.DeleteCommand = deleteCommand;

conn.Open();
adapter.Update(dt);
}
GetData();
//SqlConnection conStr = new SqlConnection();
}

//进行分页查询
private void Pages_Click(object sender,EventArgs e)
{
ToolStripMenuItem menu = sender as ToolStripMenuItem;
int pageSize1 = Convert.ToInt32(pageSize.Text );
//计算最后一页的信息
int lastIndex=(int)Math.Ceiling(GetRowcount()*1.0/pageSize1);

switch (menu.Text)
{
case "First":
pageIndex = 1;

break;
case "Last":
pageIndex = lastIndex;

break;
case "Prev":
pageIndex--;
if (pageIndex<=0)
{
pageIndex = 1;
}

break;
case "Next":
pageIndex++;
if (pageIndex>=lastIndex)
{
pageIndex = lastIndex;
}

break;


}
GetData();
}
//获取总行数
private int GetRowcount()
{
int rowCount = 0;
string connStr=System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
using (SqlConnection conn=new SqlConnection (connStr ))
{
string sql = "select COUNT(*) from Student ";
SqlCommand cmd = new SqlCommand(sql ,conn);
conn.Open();
rowCount = Convert.ToInt32(cmd.ExecuteScalar());
}
return rowCount;
}
//private void DataBind(int pageSize2,int pageIndex)
//{
// int sIndex = pageSize2*(pageIndex -1)+1;
// int eIndex = pageSize2 * pageIndex;
// DataTable dt = new DataTable();
// string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
// using (SqlConnection conn = new SqlConnection(connStr))
// {
// string sqlStr = "select rowIndex ,sId ,sName,sAddress from vStudent where rowIndex between @sIndex and @eIndex ";
// SqlCommand cmd = new SqlCommand(sqlStr,conn );
// cmd.Parameters.AddWithValue("@sIndex", sIndex);
// cmd.Parameters.AddWithValue("@eIndex",eIndex );
// SqlDataAdapter adapter = new SqlDataAdapter(cmd);
// DataSet ds = new DataSet();
// conn.Open();
// adapter.Fill(ds);
// dt = ds.Tables[0];
// }

// //return dt;
// dataGridView1.DataSource = dt;

//}

private void Form1_Load(object sender, EventArgs e)
{
pageSize.SelectedIndex = 0;
pageIndex = 1;
}

private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{

}
//获取数据并绑定数据
private void GetData()
{
int pageSize2 = Convert.ToInt32(pageSize.Text);

int sIndex = pageSize2 * (pageIndex - 1) + 1;
int eIndex = pageSize2 * pageIndex;
DataTable dt = new DataTable();
string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
string sqlStr = "select rowIndex ,sId ,sName,sAddress from vStudent where rowIndex between @sIndex and @eIndex ";
SqlCommand cmd = new SqlCommand(sqlStr, conn);
cmd.Parameters.AddWithValue("@sIndex", sIndex);
cmd.Parameters.AddWithValue("@eIndex", eIndex);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
conn.Open();
adapter.Fill(ds);
dt = ds.Tables[0];
}

dataGridView1.DataSource = dt;
}

private void pageSize_SelectedIndexChanged(object sender, EventArgs e)
{
GetData();
}




}
}