ASP关于数据库的操作

使用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;
}