Updating a Data Source with a Dataset
- by Paul
Hi, I need advice. I have asp.net web service and winforms client app.
Client call this web method and get dataset.
1. [WebMethod]
2. public DataSet GetSecureDataSet(string id)
3. {
4.
5.
6. SqlConnection conn = null;
7. SqlDataAdapter da = null;
8. DataSet ds;
9. try
10. {
11.
12. string sql = "SELECT * FROM Tab1";
13.
14. string connStr = WebConfigurationManager.ConnectionStrings["Employees"].ConnectionString;
15.
16. conn = new SqlConnection(connStr);
17. conn.Open();
18.
19. da = new SqlDataAdapter(sql, conn);
20.
21. ds = new DataSet();
22. da.Fill(ds, "Tab1");
23.
24. return ds;
25. }
26. catch (Exception ex)
27. {
28. throw ex;
29. }
30. finally
31. {
32. if (conn != null)
33. conn.Close();
34. if (da != null)
35. da.Dispose();
36. }
37. }
After he finish work he call this update web method. He can add, delete and edit rows in table in dataset.
[WebMethod]
public bool SecureUpdateDataSet(DataSet ds)
{
SqlConnection conn = null;
SqlDataAdapter da = null;
SqlCommand cmd = null;
try
{
DataTable delRows = ds.Tables[0].GetChanges(DataRowState.Deleted);
DataTable addRows = ds.Tables[0].GetChanges(DataRowState.Added);
DataTable editRows = ds.Tables[0].GetChanges(DataRowState.Modified);
string sql = "UPDATE * FROM Tab1";
string connStr = WebConfigurationManager.ConnectionStrings["Employees"].ConnectionString;
conn = new SqlConnection(connStr);
conn.Open();
cmd = new SqlCommand(sql, conn);
da = new SqlDataAdapter(sql, conn);
if (addRows != null)
{
da.Update(addRows);
}
if (delRows != null)
{
da.Update(delRows);
}
if (editRows != null)
{
da.Update(editRows);
}
return true;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null)
conn.Close();
if (da != null)
da.Dispose();
}
}
Code on client side
1. //on client side is dataset bind to datagridview
2. Dataset ds = proxy.GetSecureDataSet("");
3. ds.AcceptChanges();
4.
5. //edit dataset
6.
7.
8. //get changes
9. DataSet editDataset = ds.GetChanges();
10.
11. //call update webmethod
12. proxy.SecureUpdateDataSet(editDataSet)
But it finish with this error :
System.Web.Services.Protocols.SoapException: Server was unable to process request. --- System.InvalidOperationException: Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
at WebService.Service.SecureUpdateDataSet(DataSet ds) in D:\Diploma.Work\WebService\Service1.asmx.cs:line 489
Problem is with SQL Commad, client can add, delete and insert row, how can write a corect SQL command.... any advice please? Thank you
