C#使用SqlDataAdapter的Update方法更新数据

sql语法：

Update one Column in a Row
更新记录行中的一个栏目

We want to add a first name to the person with a last name of "Rasmussen":
我们将给person表中LastName为"Rasmussen"的数据添加一个first name值

UPDATE Person SET FirstName = 'Nina'
WHERE LastName = 'Rasmussen'

Update several Columns in a Row
给一记录行更新多个栏目

We want to change the address and add the name of the city:
我们将个人信息中Lastname为"Rasmussen"的所在城市名和地址做一次改变：

UPDATE Person
SET Address = 'Stien 12', City = 'Stavanger'
WHERE LastName = 'Rasmussen'

Update 表名 set (*) value(把表中的所有字段的值对应填写);

例1：

using System.Data.SqlClient;

namespace UpdatingData
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
btnUpdate.Click += new EventHandler(btnUpdate_Click);
UpdateData();
}


string sConnection = "Data Source=scott;Initial Catalog=northwind;Persist Security Info=True;User ID=sa;Password=sa123";
DataSet dsSet = new DataSet();
SqlDataAdapter sdaAdapter = null;
SqlCommandBuilder scbBuilder = null;
private void UpdateData()
{
//建立Connection
SqlConnection scConnection = new SqlConnection(sConnection);
//建立Command
SqlCommand scCommand = scConnection.CreateCommand();
scCommand.CommandText = "select customerID,contactName from customers";
//建立Adapter
sdaAdapter = new SqlDataAdapter(scCommand);

//该对象负责生成用于更新数据库的SQL语句，不必自己创建这些语句
scbBuilder = new SqlCommandBuilder(sdaAdapter);

//得到数据
sdaAdapter.Fill(dsSet, "customers");
dgvView.DataSource = dsSet.Tables["customers"];

}

void btnUpdate_Click(object sender, EventArgs e)
{
//设置值
dsSet.Tables["customers"].Rows[3]["contactName"] = "Thomas Hardy";
//更新数据(UpDate方法自动遍历DataTable中的行，以找出需要对数据库作出变动)
//Rows集合中每个DataRow对象都具有属性RowState，可以跟踪此行是否已删除、添加、修改，还是未作变动。所作的任何变化都会反映到数据库中。
sdaAdapter.Update(dsSet, "customers");
dgvView.DataSource = dsSet.Tables["customers"];

}
}

2

其实非常简单，就是用SqlDataAdapter的update方法就行了。主要代码例如下所示：

private void update()

{

string link = ConfigurationSettings.AppSettings["link_local"].ToString();

SqlConnection conn = new SqlConnection(link);

SqlDataAdapter da = new SqlDataAdapter("SELECT order_id, contract FROM linhai", conn);

DataSet ds = new DataSet();

da.Fill(ds, "linhai");

da.UpdateCommand = new SqlCommand("UPDATE linhai SET contract = @contract " + "WHERE order_id = @order_id" , conn);

da.UpdateCommand.Parameters.Add("@contract", SqlDbType.NVarChar, 15, "contract");

da.UpdateCommand.Parameters.Add("@order_id", SqlDbType.NVarChar, 15, "order_id");

ds.Tables["linhai"].Rows[0]["contract"] = "PPP";

da.Update(ds.Tables[0]);

}