Updating a Data Source with a Dataset

Question

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

Answer 1

I think SqlCommandBuilder is the better and convenient way for adding, updating and deleting data in the database through dataset.

Answer 2

try this:

[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;
}