• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

ASP.NET - Web service to allow client to manipulate tables...

I have created a web service for our clients to consume so that they can manipulate the tables however they want...they can upload as many records or return as many records with this code I got from MicroSoft....
http://msdn.microsoft.com/en-us/library/s5xy331f(v=vs.80).aspx#Y139

Will the attached code do the job?

' --------------------------------------
' Also some questions as I am trying to understand it....

1) In the below function....GetCustomers() ...does it return the whole db?
1a. what is MissingSchemaAction.AddWithKey

<WebMethod( Description := "Returns Northwind Customers", EnableSession := False )> _
  Public Function GetCustomers() As DataSet
    Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
      "SELECT CustomerID, CompanyName FROM Customers", connection)

    Dim custDS As DataSet = New DataSet()
    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    adapter.Fill(custDS, "Customers")

    Return custDS
  End Function


2) for the UpdateCustomers function...their are all these adapter.insert, adapter.delete...
how does the function know which action to perform?? all of them?  is there suppose to be only
one adapter.xxx in the function and MS is just supplying them all?
 2a.  for this ..adapter.UpdateCommand.Parameters.Add( _
      "@CustomerID", SqlDbType.NChar, 5, "CustomerID")
do I need to insert this:SqlDbType.NChar, 5, ..my db is already setup ..I dont need to create the column...just make sure that the data is cast to whatever the db column is cast to...but not sure why it is being used...
<% @ WebService Language = "vb" Class = "Sample" %>
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services

<WebService(Namespace:="http://microsoft.com/webservices/")> _
Public Class Sample

Public connection As SqlConnection = New SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial Catalog=Northwind")

  <WebMethod( Description := "Returns Northwind Customers", EnableSession := False )> _
  Public Function GetCustomers() As DataSet
    Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
      "SELECT CustomerID, CompanyName FROM Customers", connection)

    Dim custDS As DataSet = New DataSet()
    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    adapter.Fill(custDS, "Customers")

    Return custDS
  End Function

  <WebMethod( Description := "Updates Northwind Customers", EnableSession := False )> _
  Public Function UpdateCustomers(custDS As DataSet) As DataSet
    Dim adapter As SqlDataAdapter = New SqlDataAdapter()

    adapter.InsertCommand = New SqlCommand( _
      "INSERT INTO Customers (CustomerID, CompanyName) " & _
      "Values(@CustomerID, @CompanyName)", connection)
    adapter.InsertCommand.Parameters.Add( _
      "@CustomerID", SqlDbType.NChar, 5, "CustomerID")
    adapter.InsertCommand.Parameters.Add( _
      "@CompanyName", SqlDbType.NChar, 15, "CompanyName")

    adapter.UpdateCommand = New SqlCommand( _
      "UPDATE Customers Set CustomerID = @CustomerID, " & _
      "CompanyName = @CompanyName WHERE CustomerID = " & _
      @OldCustomerID", connection)
    adapter.UpdateCommand.Parameters.Add( _
      "@CustomerID", SqlDbType.NChar, 5, "CustomerID")
    adapter.UpdateCommand.Parameters.Add( _
      "@CompanyName", SqlDbType.NChar, 15, "CompanyName")

    Dim parameter As SqlParameter = _
      adapter.UpdateCommand.Parameters.Add( _
      "@OldCustomerID", SqlDbType.NChar, 5, "CustomerID")
    parameter.SourceVersion = DataRowVersion.Original

    adapter.DeleteCommand = New SqlCommand( _
      "DELETE FROM Customers WHERE CustomerID = @CustomerID", _
      connection)
    parameter = adapter.DeleteCommand.Parameters.Add( _
      "@CustomerID", SqlDbType.NChar, 5, "CustomerID")
    parameter.SourceVersion = DataRowVersion.Original

    adapter.Update(custDS, "Customers")

    Return custDS
  End Function
End Class

Open in new window

0
GlobaLevel
Asked:
GlobaLevel
  • 2
  • 2
1 Solution
 
GlobaLevelAuthor Commented:
Can I also have an exampl of how my client would consume this...what do they need to add to their web page to connect to my tables vis WS...thanks
0
 
CodeCruiserCommented:
Missing schema action determines what the adapter will do if the schema(columns etc) of the dataset does not match with the result of the query

http://msdn.microsoft.com/en-us/library/system.data.common.dataadapter.missingschemaaction.aspx

>In the below function....GetCustomers() ...does it return the whole db?
Its returning the whole Customers table. You can change the query and add a Where clause.

>for the UpdateCustomers function
The status of each row is maintained. It could be Added, Deleted, Modified, Unchanged. So the adapter uses this to automatically run the correct query.
0
 
GlobaLevelAuthor Commented:
for this function: UpdateCustomers..what happens if I only want to delete..not insert..do I get rid of the rest of the methods(ie, adapter.insert..adapter.update) ?
0
 
CodeCruiserCommented:
Yeah try that.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now