public partial class DataSet_Delete : System.Web.UI.Page
{
private string strConn = "data source=localhost;initial catalog=Northwind;user id=sa;password=sa";
SqlConnection Conn;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//调用LoadGridView方法以填充和显示数据
LoadGridView();
}
}
private void ConnectionDB()
{
if (Conn == null)
{
//如果SqlConnection对象不存在新建该对象
Conn = new SqlConnection(strConn);
}
if (Conn.State == ConnectionState.Closed)
{
//如果Connection对象的State状态为关闭Closed就将其打开
Conn.Open();
}
}
//创建CloseConnection方法,实现关闭数据库的操作
private void CloseConnection()
{
Conn.Close();
}
//创建LoadGridView方法,实现填充GridView控件以显示数据
private void LoadGridView()
{
string SelectSql = "SELECT * FROM Categories";
ConnectionDB();
SqlDataAdapter myDataAdapter = new SqlDataAdapter(SelectSql, Conn);
DataSet myDataSet = new DataSet();
myDataAdapter.Fill(myDataSet, "Categories");
CloseConnection();
GVCategory.DataSource = myDataSet.Tables["Categories"];
GVCategory.DataBind();
}
protected void RowDelete(object sender, GridViewDeleteEventArgs e)
{
int CategroyID = Convert.ToInt32(GVCategory.Rows[e.RowIndex].Cells[0].Text);
GVCategory.EditIndex = -1;
ConnectionDB();
string SelectSql = "SELECT * FROM Categories";
SqlDataAdapter myDA = new SqlDataAdapter(SelectSql, Conn);
DataSet DS = new DataSet();
myDA.Fill(DS, "Categories");
CloseConnection();
//创建本地数据表table
DataTable table = DS.Tables["Categories"];
//设置table的PrimaryKey属性,其主键码设置为CategoryID列
table.PrimaryKey = new DataColumn[] { table.Columns["CategoryID"] };
//使用Find方法查找当前进行删除的数据行
DataRow delRow = table.Rows.Find(CategroyID);
//使用Delete方法删除数据行
delRow.Delete();
//创建SqlCommandBuilder对象myCB,它将自动创建DELETE语句
SqlCommandBuilder myCB = new SqlCommandBuilder(myDA);
ConnectionDB();
//使用DataAdapter对象的Update方法将删除数据行后的table表推入到数据库中
myDA.Update(table);
CloseConnection();
//重新载入数据,显示删除数据后的数据表内容
LoadGridView();
}
}