dataSet in Web Service

Question

Hi!

I am using UltraGrid for inserting data in table. The problem is that i need to do insert,update, delete with web service. I am getting dataSet from web service and display it into grid but when I do some changes on the grid for example insert or update data, I need also to send new dataSet to web service so that web service do update data.

This is the table ID (PK,int,not null) Name(nvarchar 100,null)

This is the code for client side:

Public Sub Refresh()
    Dim p As localhost.Service1 = New localhost.Service1
   'bind datatable to UltraGrid 
    UltraGrid1.DataSource = p.GetData()
    End Sub

This is the code for web service for getting data from table:

<WebMethod()> Public Function GetData() As DataSet
    Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT  ID,Name FROM Btable", nwindConn)
    Dim custDS As DataSet = New DataSet()
    custDA.MissingSchemaAction = MissingSchemaAction.AddWithKey
    custDA.Fill(custDS, "Btable")
    GetData= custDS
End Function

This all working fine but now I want to insert new row to grid and send that to web service and insert it from there. How I can do that? Thanks!

Answer 1

You should handle the event "AfterRowInsert" of the UltraTable and retrieve the values from the new row. You could then call a new web method on your web service that would insert the row into your database.

The web method may look something like this....

<WebMethod()> Public sub AddRecord(id as integer, name as string)

dim sSql as string = "INSERT INTO Btable (ID, Name) VALUES (@ID, @Name)"    
Dim oCmd as SqlCommand = nwindConn.CreateCommand()
oCmd.CommandText = sSql
oCmd.Parameters.Add("@ID", SqlDbType.Int).Value = id
oCmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = name
oCmd.ExecuteNonQuery()

End Function 

If your ID column is an auto number it would be slightly different. In this situation your web method would only accept a name parameter and would return the ID of the new record. In MS SQL Server you can get this by calling SCOPE_IDENTITY() after your INSERT query.