ASP关于数据库的操作

来源：互联网发布：熊猫直播for mac 编辑：程序博客网时间：2024/05/22 20:55

使用ADO.NET进行数据库的操作

1. 添加SqlClient命名空间

using System.Data.SqlClient;

2. 在Page_Load中获取表，必要时，需要将它绑定给GridView控件

a. 创建一个连接字符串和命令字符串

b. 将字符串传递给SqlDataAdapter的构造函数

c. 创建DataSet的实例

d. 询问DataAdapter来填充DataSet

e. 从DataSet中抽取表格

f. 将表格绑定给GridView

例子：

protected void Page_Load(object sender, EventArgs e){string connectionString="Data Source=服务器地址/ip地址;Initial Catalog=数据库名;User ID=用户名;Password=密码";string commandString="Select * from Customers";SqlDataAdapter dataAdapter=new SqlDataAdapter(commandString, connectionString);DataSet dataSet = new DataSet();dataAdapter.Fill(dataSet, "Customers");DataTable dataTable=dataSet.Tables["Customers"];GridView1.DataSource=dataTable;GridView1.DataBind();}

插入数据：

protected void btnAdd_Click(object sender, EventArgs e){string cmd = @"Insert into Shippers values ('"+this.txtName.Text+"','"+this.txtPhone.Text+"')";UpdateDB(cmd);PopulateGrid();//刷新Grid控件，相当于重新再读取一遍数据库}

编辑数据：

protected void btnEdit_Click(object sender, EventArgs e){int shipperID = GetSelectedRecod();//获取主键的值string cmd = @"Update Shippers set CompanyName = '"+this.txtName.Text+"',Phone='"+this.txtPhone.Text+@"'where ShipperID="+shipperID;UpdateDB(cmd);PopulateGrid();//刷新Grid控件，相当于重新再读取一遍数据库}

删除数据：

protected void btnDelete_Click(object sender, EventArgs e){string cmd = @"delete from Shippers where shipperID = "+GetSelectedRecord();UpdateDB(cmd);PopulateGrid();//刷新Grid控件，相当于重新再读取一遍数据库}

private void UpdateDB(string cmd）{string connectionString="Data Source=服务器地址/ip地址;Initial Catalog=数据库名;User ID=用户名;Password=密码";System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConection(connectionString);try{connection.Open();System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();command.Connection = connection;command.CommandText = cmd;command.ExecuteNonQuery();}finally{connection.Close();}}

获取单击Grid的行：

protected int GetSelectedRecord(){int shipperID = -1;int index = GridView1.SelectedIndex;if (index != -1){DataKey key = GridView1.DataKeys[index];shipperID = (int)key.Value;}return shipperID;}