This is a bit tricky if you do not have any primary keys returned in the select. For example if the backend is an .xls file.
I have written about it here:
http://blogs.msdn.com/spike/archive/2008/10/29/how-to-update-an-excel-worksheet-using-dataset-and-the-oledbdataadapter.aspxIn your case, perhaps this works:
Create a table, insert some rows and create a stored procedure to update:
create table MyTable (col1 int, col2 int, col3 int, col4 int)
insert into MyTable values (1, 2, 3, 4)
insert into MyTable values (1, 2, 3, 4)
create procedure UpdateMyTable(@val1 int, @val2 int,@val3 int,@val4 int)
as
begin
set nocount on
update MyTable set col1 = @val1, col2 = @val2 where col3 = @val3 and col3 = @val3
end
Then in the .Net application:
try
{
string cString = @"Data Source=<your server>;initial catalog=<your database>;Integrated Security=SSPI";
using (SqlConnection con = new SqlConnection(cString))
{
con.Open();
SqlCommand getDataCmd = new SqlCommand("SELECT col1, col2, col3, col4 FROM MyTable", con);
SqlDataAdapter da = new SqlDataAdapter(getDataCmd);
DataSet ds = new DataSet();
da.Fill(ds);
ds.Tables[0].Rows[0][2] = 0;
ds.Tables[0].Rows[0][3] = 0;
da.UpdateCommand = new SqlCommand("UpdateMyTable", con);
da.UpdateCommand.CommandType = CommandType.StoredProcedure;
da.UpdateCommand.Parameters.AddWithValue("@val1", 20);
da.UpdateCommand.Parameters.AddWithValue("@val2", 5);
da.UpdateCommand.Parameters.Add("@val3", SqlDbType.Int, 0, "col3").SourceVersion = DataRowVersion.Original;
da.UpdateCommand.Parameters.Add("@val4", SqlDbType.Int, 0, "col4").SourceVersion = DataRowVersion.Original;
da.Update(ds);
con.Close();
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
HTH
//Michael
This posting is provided "AS IS" with no warranties, and confers no rights.
