利用Web Service存取SQL Server資料庫

 
作者：楊先民  

精誠資訊/恆逸教育訓練中心資深講師

※網路引用請註明完整出處

我們都知道，如果你寫應用程式需要連接到 SQL Server，你必須準備SQL Native Client或是OLE DB For SQL Server，而且大多數都會希望你的前端應用程式是使用 .NET 開發，然而本文的主題是「如果你的前端並非 .NET的程式」，那你應該如何連接到SQL Server處理資料呢？

Web Service當成資料處理的中繼層

本文的情境前端是使用 iphone的程式開發Objective-C，並且使用原生的方式來呼叫已經寫好的Web Service程式，也就是我並不打算使用 Jason的方式來存取Web Service，自己控制比較有實作的感覺啊，而且也順便記錄一下這種複雜的存取控制方式，頓時之間你會發現使用 .NET來呼叫Web Service是多麼的簡單（只需設定一個 proxy物件就好，但 iphone開發沒那麼容易）！

情境是這樣的，我在 iphone上撰寫了一支車輛油耗管理的程式，詳細請參考下列網站：

https://itunes.apple.com/tw/app/you-hao-wei-xiupro/id491654074?mt=8

這支程式不但管理使用者的車輛油耗以及保養維修記錄，還可以上傳使用者的油耗資訊和其他同車廠同型號的使用者比較，所以我設計了一個如下的介面：

當使用者先登錄車籍資料，並且按下「上傳油耗資訊」，使用者就可以將自身的油耗資訊上傳，並且在統計頁面上可以知道自己的油耗，以及全部車種的油耗排名，如下圖：

其實，單純就以資料庫的角度來看油耗資訊上傳非常容易，只不過就是寫個 update指令，將油耗資訊寫入資料庫罷了，不過別忘記我們用的是 iphone的程式，並不是 .NET的程式，也許有 3-party的廠商提供函式庫給 iphone呼叫 SQL Server，但最通用的方式還是自己透過 Web Service最實在了，主因是 Web Service是一個跨平台的程式，透過 tcp/ip與 xml做為媒界，任何前端（並非只有 Windows前端）可以呼叫 Web Service，而你只需處理回傳回來的 xml資料即可（xml現今用來廣泛的做為資料交換之用）。

所以我們必須做幾件事情：

1.  在 SQL Server中撰寫更新油耗的 stored procedure，用來做資料處理。

2.  利用 asp .net Web Service，呼叫1所撰寫的 stored procedure。

3.  在 iphone中利用程式呼叫 Web Service，更新油耗資訊。

 

我們就一步步來：

首先，1的部分就是寫一個更新油耗資訊的 stored procedure，如下:

Create proc [dbo].[COil_UpdateOil]

@deviceid int,@totalwalkkm decimal(10,2),@totallitre decimal(10,2)

as

IF @totallitre=0

set @totallitre=1

update Oil set walkkm = @totalwalkkm , Litre = @totallitre where DeviceID = @deviceid

select @@ROWCOUNT

 

其中就是輸入三個參數，一個是裝置id，一個是總行駛公里，一個是總加油公升，如此而已。

再來，利用 asp .net 的  Web Service，呼叫寫好的 stored procedure，程式如下：

    <WebMethod()> _

Public Function updateOil(ByVal deviceid As String, ByVal totalwork As String, ByVal totallitre As String) As Integer

        '

Dim returnValue As Integer

returnValue = 0

 

Dim myConnection As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("uploadoil"))

Dim myCommand As SqlCommand

myCommand = New SqlCommand("COil_UpdateOil", myConnection)

myCommand.CommandType = CommandType.StoredProcedure

 

Dim parameterCustomerid As SqlParameter = New SqlParameter("@deviceid", SqlDbType.Int, 4)

parameterCustomerid.Value = deviceid

Dim parameterdefaultcar As SqlParameter = New SqlParameter("@totalwalkkm", SqlDbType.Decimal, 9)

parameterdefaultcar.Value = Convert.ToDecimal(totalwork)

 

Dim parameterTotallitre As SqlParameter = New SqlParameter("@totallitre", SqlDbType.Decimal, 9)

parameterTotallitre.Value = Convert.ToDecimal(totallitre)

myCommand.Parameters.Add(parameterCustomerid)

myCommand.Parameters.Add(parameterdefaultcar)

myCommand.Parameters.Add(parameterTotallitre)

' Execute the command

Try

           myConnection.Open()

            returnValue = myCommand.ExecuteScalar

            myConnection.Close()

                  Catch ex As Exception

                 myConnection.Close()

            returnValue = 0
End Try

        Return returnValue
End Function

 

寫好的 Web Service，可以在網站測試一下，初始畫面應該是如下：

而我們還要注意下方的 SOAP的呼叫方式，這個將是等會我們要用 iphone 呼叫的程式：

目前我依然使用 SOAP 1.1的呼叫方式，你有興趣可以使用再更下方的 SOAP 1.2。

如果你原先寫的是 .NET程式，你可能根本不會想看這張圖，因為 .NET只要把 Web Service的網址參考進來並且指定一個 proxy物件就可以直接呼叫了，換言之這部分的動作被 .NET給包起來了，如果你在 iphone中是使用 Jason的話，也大概是這麼包（不過絕對沒有 .NET這麼容易就是了）。

不過在 iphone中你要是撰寫的是原生的 Web Service，就要把上圖的那個呼叫與回傳好好研究一下，因為我們必須自己呼叫 SOAP Action。

我們對照瀏覽器上的 SOAP，將我們自已的 SOAP Action組裝起來，下方的第一張圖是瀏覽器上的Envelope（信封，將 SOAP Action用信封包起來丟出去的意思），第二張則是用 objective-c的 stringformat將 SOAP action組合成訊息字串 soapMessage。

是的，在 iphone中，你就是要利用 objective-c將這段 SOAP Action傳送到Web Service端，然後輸入所需要的參數（以本例而言，是 deviceid、totalwork以及 totallitre這三個參數）。

接下來，設定NSMutableURLRequest，將我們的 Web Service位址，也就是 http://xxx.xxx.xxx.xxx/upload.asmx指定，並且包上 HeaderField傳送出去，也就是在瀏覽器中，原本 SOAP Action的上面，還有一段資訊，如下：

是的，就是這段資訊，要一併隨著我們的 SOAP Action的信封傳送到 Web Service中。所以尚需要加上這一段程式碼：

其中有一行程式是 msgLength這個變數，主要是 HeaderField中需要 SOAP Action的 Content-Length，所以必須把剛才前面所定義的 soapMessage的長度輸入進來，然後就可以開始處理Web Service 所回傳的資訊了。

（當然，這裡不要忘記，由於我們設定 connection 的 delegate屬性為 self，表示 xml Web Service這段部分的處理是由我們自行來完成，故不要忘記在h檔定義的地方加上NSXMLParserDelegate，如下圖：

由於要自己處理Web Service的回傳（如果此Web Service沒有回傳的話，只需注意網路有沒有連線成功即可，後續不用處理，但由於本例有回傳結果，所以我們必須知道回傳的格式），所以我們必須知道Web Service會回傳什麼資訊回來，因此我們先在Web Service上測試我們的值以及取得回傳結果的 xml資料，如下兩張圖：

回傳結果如下：

還記得我們的 stored procedure嗎？當資料修改完，回傳修改數量，以上圖為例，回傳值為1表示有資料被修改，那麼就代表成功了。我們在 iphone中要寫的程式主要是取回1這個數值，只要它是大於零，表示資料有成功修改到，本上傳油耗的程式即大功告成！

至此，我們需要設定剛才的 theConnection，要在它接收到資料時，將回傳的結果存入事先定義好的陣列中，所以我們必須在didReceiveResponse中清空陣列以及在didReceiveData中將取出的值填入陣列中，程式碼如下：

在連線結束載入時，利用 NSXMLParser開始剖析回傳回來的 XML資料，如下圖：

而可以注意到程式中的 xmlParser的 setDelegate設定為 self，所以代表你要自行撰寫取出 XML時的剖析內容，因此我們要自行控制didStartElement、foundCharacters以及didEndElement這三個事件。

 

在此稍微說明一下，XML是個階層式的資料，所以資料很有可能長的如下：

<Employee>

        <id>E01</id>

</Employee>

 

所謂的 didStartElement，代表發現 <Employee>這個 Element，但我們如果要的是<id>這個 element中的 E01資料，則就得請 parser繼續往下找。

 

foundCharacters則是代表 E01這個字元被找到，我們這裡必需寫程式把它存起來，由於 XML是 unicode以及是文字資料型別，所以要用文字字串或是陣列儲存起來。

 

最後的 didEndElement則是 </Employee>，也就是 XML中的尾巴這部分，代表整個 XML的結束。

 

至於我們如何知道頭尾的 element是什麼呢？很簡單，在瀏覽器上看的 demo其實就給了我們資訊，如下圖：

是的，也就是我們在didStartElement需要判斷是否值為updateOilResult，如果是的話，就可以開始讀裡面的值，如下圖： 

在此程式中，我們設定 UpdateOil為 YES，主要是如果你在同一個程式中呼叫多支 Web Service，負責處理的 method是同一支，所以你要設定像 UpdateOil這樣的旗標，用來說明我已經找到某個 element了，並且把其他的旗標設定為 NO（iphone中是用 YES NO而不是 true與 false）。

然後在 foundCharacters中撰寫程式判斷回傳的結果是否為1，如下圖：

由於 xml的文字都是 unicode字串，所以即便是它看起來像數字的0與1，還是要以字串的方式處理。

 

最後，在 didEndElement中結束這次 XML資料的剖析，如下圖：

至此，由 iphone呼叫Web Service的程式宣告完成，是不是感覺有點複雜呢？其實以前習慣了.NET開發，很多動作都包起來，但其實Web Service就是一個很簡單的概念，把SOAP Action用一個信封包起來，利用HTTP傳送到網路上的Web Service，而SOAP Action裡面已經把呼叫Web Service的動作定義好了，Web Service處理完之後再將xml回傳結果用一個信封回傳，而應用程式端看自己有沒有需要接收回傳值，再利用xmlparser負責處理即可，有部分程式是可以直接套用就可以使用的，各位可以參考看看，不用依賴Jason，畢竟寫原生的Web Service好處就是只要概念通，其他應用程式也大概是類似的道理。