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'
给一记录行更新多个栏目
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'
例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]);
}