Updating a data source using a dataset

Question

I need an advice. I have an asp.net web service and a winforms client application. The client calls this web method and receives the data set.

   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.  }  

Upon completion, it calls this web update method. It can add, delete and edit rows in a table in a 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();
        }
    }

Client Side Code

   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 ends with this error:

System.Web.Services.Protocols.SoapException: The server could not process the request. ---> System.InvalidOperationException: updating requires a valid UpdateCommand when passing the DataRow collection with changed rows. in WebService.Service.SecureUpdateDataSet (DataSet ds) in D: \ Diploma.Work \ WebService \ Service1.asmx.cs: line 489

SQL Commad, , , SQL SQL. , ?

Answer 1

:

[WebMethod]
public bool SecureUpdateDataSet(DataSet delta)
{

     string connStr = WebConfigurationManager.ConnectionStrings["Employees"].ConnectionString;

     using(var conn = new SqlConnection(connStr))
     {
        conn.Open();

        string sql = "select * from tab1 where 1 = 0";

        using(var da = new SqlDataAdapter(sql, conn))
        {

            var builder = new SqlCommandBuilder(ad);

            da.InsertCommand = builder.GetInsertCommand();
            da.UpdateCommand = builder.GetUpdateCommand();
            da.DeleteCommand = builder.GetDeleteCommand();

            da.Update(delta);

            return true;
        }
    }
    return false;
}

Answer 2

, SqlCommandBuilder - , .