SqlDataAdapter更新数据前与SqlCommandBuilder生成命令:DataSet ds = new DataSet();
SqlDataAdapter myda = new SqlDataAdapter("select * from worktype",connection);
SqlDataBuilder scb = new SqlDataBuilder(sda)
sda.Update(ds,"worktype");ds中只有一个表worktype,有字段worktype,与数据库表对应,为什么用这种办法没新增数据的时候,不能将自增长的字段获取的值更新到ds. 如果我用SqlCommand每个生成SqlDataAdapter,然后再更新,则可以将数据库自动增长字段的值反映到ds中!!!!应如何设置,才可以更新自增长值到ds中呢?
SqlDataAdapter myda = new SqlDataAdapter("select * from worktype",connection);
SqlDataBuilder scb = new SqlDataBuilder(sda)
sda.Update(ds,"worktype");ds中只有一个表worktype,有字段worktype,与数据库表对应,为什么用这种办法没新增数据的时候,不能将自增长的字段获取的值更新到ds. 如果我用SqlCommand每个生成SqlDataAdapter,然后再更新,则可以将数据库自动增长字段的值反映到ds中!!!!应如何设置,才可以更新自增长值到ds中呢?
catDA.UpdateCommand = new SqlCommand("UPDATE Categories SET CategoryName = @CategoryName " +"WHERE CategoryID = @CategoryID" , nwindConn);
catDA.UpdateCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15, "CategoryName");
SqlParameter workParm = catDA.UpdateCommand.Parameters.Add("@CategoryID", SqlDbType.Int);
workParm.SourceColumn = "CategoryID";
workParm.SourceVersion = DataRowVersion.Original;DataSet catDS = new DataSet();
catDA.Fill(catDS, "Categories");
DataRow cRow = catDS.Tables["Categories"].Rows[0];
cRow["CategoryName"] = "New Category";
catDA.Update(catDS);
如果是的话可以用@@IDENTITY 来表示你最新加入的记录的标识值
比如我们向数据库中插入一条数据,然后返回这条记录的自增长值。
INSERT INTO Production.Location (Name, CostRate, Availability, ModifiedDate)
VALUES ('Damaged Goods', 5, 2.5, GETDATE());
GO
SELECT @@IDENTITY AS 'Identity';//这里就取到了标识值,你改成自己的就行
/// </summary>
/// <returns></returns>
private SqlCommand LoadInsertCOShipmentCommand()
{
string _sql = "INSERT INTO COShipment(CostOrderID, ShipDate, ShipTo, Quantity, UnitID) VALUES (@" +
"CostOrderID, @ShipDate, @ShipTo, @Quantity, @UnitID); SELECT COShipmentID, CostO" +
"rderID, ShipDate, ShipTo, Quantity, UnitID FROM COShipment WHERE (COShipmentID =" +
" @@IDENTITY)"; SqlCommand Comm = new SqlCommand(_sql); Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CostOrderID", System.Data.SqlDbType.Int, 4, "CostOrderID"));
Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ShipDate", System.Data.SqlDbType.DateTime, 8, "ShipDate"));
Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ShipTo", System.Data.SqlDbType.Int, 4, "ShipTo"));
Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Quantity", System.Data.SqlDbType.Money, 8, "Quantity"));
Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UnitID", System.Data.SqlDbType.Int, 4, "UnitID"));
//
return Comm;
} /// <summary>
/// 更新物料表
/// </summary>
/// <returns></returns>
private SqlCommand LoadUpdateCOShipmentCommand()
{
string _sql = @"UPDATE COShipment SET CostOrderID = @CostOrderID, ShipDate = @ShipDate, ShipTo = @ShipTo, Quantity = @Quantity, UnitID = @UnitID WHERE (COShipmentID = @Original_COShipmentID) AND (CostOrderID = @Original_CostOrderID OR @Original_CostOrderID IS NULL AND CostOrderID IS NULL) AND (Quantity = @Original_Quantity OR @Original_Quantity IS NULL AND Quantity IS NULL) AND (ShipDate = @Original_ShipDate OR @Original_ShipDate IS NULL AND ShipDate IS NULL) AND (ShipTo = @Original_ShipTo OR @Original_ShipTo IS NULL AND ShipTo IS NULL) AND (UnitID = @Original_UnitID OR @Original_UnitID IS NULL AND UnitID IS NULL); SELECT COShipmentID, CostOrderID, ShipDate, ShipTo, Quantity, UnitID FROM COShipment WHERE (COShipmentID = @COShipmentID)";
SqlCommand Comm = new SqlCommand(_sql);
Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CostOrderID", System.Data.SqlDbType.Int, 4, "CostOrderID"));
Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ShipDate", System.Data.SqlDbType.DateTime, 8, "ShipDate"));
Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ShipTo", System.Data.SqlDbType.Int, 4, "ShipTo"));
Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Quantity", System.Data.SqlDbType.Money, 8, "Quantity"));
Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UnitID", System.Data.SqlDbType.Int, 4, "UnitID"));
Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_COShipmentID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "COShipmentID", System.Data.DataRowVersion.Original, null));
Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_CostOrderID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "CostOrderID", System.Data.DataRowVersion.Original, null));
Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Quantity", System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Quantity", System.Data.DataRowVersion.Original, null));
Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_ShipDate", System.Data.SqlDbType.DateTime, 8, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ShipDate", System.Data.DataRowVersion.Original, null));
Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_ShipTo", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ShipTo", System.Data.DataRowVersion.Original, null));
Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_UnitID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "UnitID", System.Data.DataRowVersion.Original, null));
Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@COShipmentID", System.Data.SqlDbType.Int, 4, "COShipmentID"));
return Comm;
}
private SqlCommand LoadDeleteCOShipmentCommand()
{
string _sql = @"DELETE FROM COShipment WHERE (COShipmentID = @Original_COShipmentID) AND (CostOrderID = @Original_CostOrderID OR @Original_CostOrderID IS NULL AND CostOrderID IS NULL) AND (Quantity = @Original_Quantity OR @Original_Quantity IS NULL AND Quantity IS NULL) AND (ShipDate = @Original_ShipDate OR @Original_ShipDate IS NULL AND ShipDate IS NULL) AND (ShipTo = @Original_ShipTo OR @Original_ShipTo IS NULL AND ShipTo IS NULL) AND (UnitID = @Original_UnitID OR @Original_UnitID IS NULL AND UnitID IS NULL)"; SqlCommand Comm = new SqlCommand(_sql);
Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_COShipmentID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "COShipmentID", System.Data.DataRowVersion.Original, null));
Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_CostOrderID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "CostOrderID", System.Data.DataRowVersion.Original, null));
Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Quantity", System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Quantity", System.Data.DataRowVersion.Original, null));
Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_ShipDate", System.Data.SqlDbType.DateTime, 8, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ShipDate", System.Data.DataRowVersion.Original, null));
Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_ShipTo", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ShipTo", System.Data.DataRowVersion.Original, null));
Comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_UnitID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "UnitID", System.Data.DataRowVersion.Original, null));
return Comm;
}
SqlDataAdapter _shipDA = new SqlDataAdapter();
_shipDA.InsertCommand = this.LoadInsertCOShipmentCommand();
_shipDA.InsertCommand.Connection = conn;
_shipDA.InsertCommand.Transaction = tran; _shipDA.UpdateCommand = this.LoadUpdateCOShipmentCommand();
_shipDA.UpdateCommand.Connection = conn;
_shipDA.UpdateCommand.Transaction = tran; _shipDA.DeleteCommand = this.LoadDeleteCOShipmentCommand();
_shipDA.DeleteCommand.Connection = conn;
_shipDA.DeleteCommand.Transaction = tran;
_shipDA.Update(ds,"COShipment"):
DataSet ds = new DataSet();
SqlDataAdapter myda = new SqlDataAdapter("select * from worktype",connection);
SqlDataBuilder scb = new SqlDataBuilder(sda)
sda.Update(ds,"worktype");更新数据没有问题,唯一的问题是没有将自增长的值带出!
DataSet中没有数据库字段的自增长值
"CostOrderID, @ShipDate, @ShipTo, @Quantity, @UnitID); SELECT COShipmentID, CostO" +
"rderID, ShipDate, ShipTo, Quantity, UnitID FROM COShipment WHERE (COShipmentID =" +
" @@IDENTITY)";
你在直接用Command时,在insert语句后还有一句查询语句即SELECT COShipmentID, CostO"...而用SqlCommandBuider不会再生成后面一句查询命令的...
SqlDataAdapter sda = new SqlDataAdapter();
public Form1()
{
InitializeComponent();
} private void button1_Click(object sender, EventArgs e)
{
sda.Update((DataTable)bs.DataSource );
//重新绑定
DataBind();
} private void Form1_Load(object sender, EventArgs e)
{
DataBind();
}
private void DataBind()
{
//以下进行数据绑定
SqlConnection con = new SqlConnection("server=.;database=student;uid=sa;pwd=0421");
sda = new SqlDataAdapter("select * from studentInfor", con);
SqlCommandBuilder buider = new SqlCommandBuilder(sda);
DataSet ds = new DataSet();
sda.Fill(ds, "student");
bs.DataSource = ds.Tables["student"];
this.dataGridView1.DataSource = bs;
}