Excel使用ADO調用SQL Server預存程序，ado預存程序

Excel使用ADO調用SQL Server預存程序，ado預存程序

測試環境：SQL Server 2014，Excel 2013

Excel樣本檔案下載：http://download.csdn.net/detail/zhanghongju/8533555

前期在做一個現場投票的小軟體，準備使用Excel實現，活頁簿中包含有兩個工作表，各包含一個命令按鈕，分別用於提交和查看投票結果。

問題出在查看投票按鈕上，起初只是使用SELECT語句查詢，一切正常；後期對於投票結果需要進行一些權重的計算，過程比較複雜，便想到把計算過程放在SQL Server端，使用預存程序實現。但是，在調用預存程序的過程中卻遇到了問題，一直無法返回記錄集。最後發現問題出現在記錄集的CursorLocation屬性設定上，下面便把此問題的過程和解決方案提供如下，以免發生類似問題。

一、使用CursorLocation屬性設定遊標的區別

CursorLocation屬性包含兩個屬性值：adUseClient（常量值為3）和adUseServer（常量值為2）。adUseClient表示使用由本地遊標庫提供的用戶端資料指標，adUseServer是預設值，表示使用資料提供者的或驅動程式提供的遊標。

adUseClient遊標服務的功能通常更加強大一些，許多功能可能是驅動程式提供的遊標無法提供的。但是，adUseServer遊標具有高度的敏感性，可以看到其他使用者對資料來源所作的更改，對於一些多使用者操作，該特性是具有優勢的。也正是因為此特性，會導致記錄集行數的具有不確定性，因此某些情況下是無法使用RecordCount屬性擷取記錄集的行數，也無法使用EOF和BOF判斷是否到達記錄集的尾部和頭部。

二、在SQL Server中建立樣本資料

下面的語句用於在SQL Server中建立兩個表。dbo.MyScore用於儲存評選人員ID、項目ID和評選分數，dbo.MyPerson用於儲存評選人員ID和評選人員是否為專家的標誌，標誌為1，表示此評選人員為專家。

IF OBJECT_ID('dbo.MyScore') IS NOT NULL

   DROP TABLE dbo.MyScore;

IF OBJECT_ID('dbo.MyPerson') IS NOT NULL

   DROP TABLE dbo.MyPerson;

GO

 

CREATE TABLE dbo.MyScore(PersonID int, ProjectID int, ProjectScore int);

CREATE TABLE dbo.MyPerson(PersonID int, IsExpert int);

INSERT INTO dbo.MyScore VALUES

   (1, 1001, 90),

   (1, 1002, 80),

   (1, 1003, 95),

   (2, 1001, 85),

   (2, 1002, 85),

   (2, 1003, 90),

   (3, 1001, 100),

   (3, 1002, 90),

   (3, 1003, 95);

INSERT INTO dbo.MyPerson VALUES

   (1, 0),

   (2, 0),

   (3, 1);  --3是專家評測人員

 

三、Excel中的查詢方法

1．執行SELECT查詢

開啟Excel，在Sheet1中插入一個命令按鈕，並為其指定Click方法，代碼如下：

Sub 按鈕1_Click()

    Dim cn As New ADODB.Connection, strCn AsString, strSQL As String, rs As New ADODB.Recordset

   

    strCn ="Provider=sqloledb;Server=192.0.168.1;Database=MyTMP;Uid=sa;Pwd=111111"

    cn.Open strCn

    If cn.State <> adStateOpen Then

        On Error Resume Next

        cn.Close

        Err.Clear

        o = MsgBox("資料連線失敗", vbOKOnly,"提示")

        Exit Sub

    End If

  

   

    '清除Excel工作表中的資料並設定列名稱

   ActiveWorkbook.Worksheets("Sheet1").Cells.Select

    Selection.ClearContents

    Range("A1").Value = "參評項目ID"

    Range("B1").Value = "平均分值"

   

    '直接查詢所有評選人員的平均分值

    strSQL = "SELECTProjectID,AVG(ProjectScore) AS ProjectScore FROM dbo.MyScore GROUP BYProjectID"

    rs.Open strSQL, cn, adOpenKeyset,adLockReadOnly

   

    o = MsgBox("記錄數：" &rs.RecordCount & "，遊標位置：" &rs.CursorLocation, vbOKOnly) '提示擷取到的行數和遊標位置

   

     

    With rs

        If Not (.BOF And .EOF) Then '有資料記錄，則遍曆記錄集顯示在Excel工作表中

            For i = 2 To .RecordCount + 1

                Cells(i, 1) = .Fields(0)

                Cells(i, 2) = .Fields(1)

                .MoveNext

            Next

        End If

    End With

End Sub

上面的代碼中使用MsgBox函數來提示擷取到的記錄集行數，有資料記錄的話，則遍曆記錄集，將資料寫入到Excel工作表中。上面的代碼沒有為記錄集指定CursorLocation屬性，則使用預設值adUseServer，執行時可看到彈出對話方塊中顯示的記錄數和遊標位置，如所示。

當然，為了確保ADO對象的正常引用，應當在項目中添加對Microsoft ActiveXData Objects支援庫的引用，如所示。

2．執行預存程序

首先在SQL Server中建立一個名為dbo.usp_GetScore的預存程序，該過程用於判斷評選時是否有專家參與，如果有專家參與，則按照普通人員70%、專家人員30%的權重計算項目的分值；如果沒有，則直接計算項目的平均分值。

CREATE PROCEDURE  dbo.usp_GetScore

AS

BEGIN

   DECLARE@IsExpert AS int;

 

   SELECT TOP 1 @IsExpert = B.IsExpert

   FROM dbo.MyScore A

      INNER JOIN dbo.MyPerson B

      ON A.PersonID = B.PersonID

   WHERE B.IsExpert = 1;

 

   /*有專家投票的情況下，專家比重為30%，其他人員為70%；

   否則，則直接取所有人的平均值*/  

   IF @IsExpertIS NOT NULL 

      WITH CTE AS

         (SELECT A.ProjectID,A.ProjectScore,B.IsExpert

          FROMdbo.MyScore A

             INNERJOIN dbo.MyPersonB

                ONA.PersonID = B.PersonID

          ),

         CTE1 AS

          (SELECT ProjectID, AVG(ProjectScore) * 0.7 AS ProjectScore

          FROM CTE

          WHERE IsExpert <>1

          GROUP BYProjectID

          UNION ALL

          SELECT ProjectID,AVG(ProjectScore) * 0.3 AS ProjectScore

          FROM CTE

          WHERE IsExpert =1

          GROUP BYProjectID

          ) SELECTProjectID, SUM(ProjectScore) AS ProjectScore

            FROM CTE1

             GROUP BY ProjectID;

    ELSE

       SELECT ProjectID, AVG(ProjectScore) AS ProjectScore

       FROM dbo.MyScore

       GROUP BY ProjectID;

END;

將前面介紹的Excel中按鈕strSQL的代碼替換為預存程序，如下所示：

Sub 按鈕1_Click()

    Dim cn As New ADODB.Connection, strCn AsString, strSQL As String, rs As New ADODB.Recordset

   

    strCn ="Provider=sqloledb;Server=46.0.187.151;Database=MyTMP;Uid=sa;Pwd=zhj11111"

    cn.Open strCn

    If cn.State <> adStateOpen Then

        On Error Resume Next

        cn.Close

        Err.Clear

        o = MsgBox("資料連線失敗", vbOKOnly,"提示")

        Exit Sub

    End If

  

   

    '清除Excel工作表中的資料並設定列名稱

   ActiveWorkbook.Worksheets("Sheet1").Cells.Select

    Selection.ClearContents

    Range("A1").Value = "參評項目ID"

    Range("B1").Value = "平均分值"

   

    '直接查詢所有評選人員的平均分值

    strSQL ="EXECUTE dbo.usp_GetScore"

    rs.Open strSQL, cn, adOpenKeyset,adLockReadOnly

   

    o = MsgBox("記錄數：" &rs.RecordCount & "，遊標位置：" &rs.CursorLocation, vbOKOnly) '提示擷取到的行數

   

    With rs

        If Not (.BOF And .EOF) Then '有資料記錄，則遍曆記錄集顯示在Excel工作表中

            For i = 2 To .RecordCount + 1

                Cells(i, 1) = .Fields(0)

                Cells(i, 2) = .Fields(1)

                .MoveNext

            Next

        End If

    End With

End Sub

執行代碼，這時會發現對話方塊返回記錄集的行數為-1，如所示。由於無法確定記錄行數，所以也就無法寫入到Excel中。

解決的方法是把記錄集的CursorLocation屬性指定為adUseClient，這樣就可以正常運行了。參考下面的代碼：

strSQL ="EXECUTE dbo.usp_GetScore"

rs.CursorLocation = adUseClient

rs.Open strSQL,cn, adOpenKeyset, adLockReadOnly

即使未指定adUseClient，雖然記錄集的RecordCount返回-1，但是，記錄集實際上是有資料的，我們可以不使用遍曆記錄集的方法，而是使用Excel的CopyFromRecordset方法把記錄集直接粘貼到工作表中，參考下面的代碼：

Sub 按鈕1_Click()

    Dim cn As New ADODB.Connection, strCn AsString, strSQL As String, rs As New ADODB.Recordset

   

    strCn ="Provider=sqloledb;Server=46.0.187.151;Database=MyTMP;Uid=sa;Pwd=zhj11111"

    cn.Open strCn

    If cn.State <> adStateOpen Then

        On Error Resume Next

        cn.Close

        Err.Clear

        o = MsgBox("資料連線失敗", vbOKOnly,"提示")

        Exit Sub

    End If

  

   

    '清除Excel工作表中的資料並設定列名稱

   ActiveWorkbook.Worksheets("Sheet1").Cells.Select

    Selection.ClearContents

    Range("A1").Value = "參評項目ID"

    Range("B1").Value = "平均分值"

   

    '直接查詢所有評選人員的平均分值

    strSQL = "EXECUTEdbo.usp_GetScore"

    rs.CursorLocation = adUseClient

    rs.Open strSQL, cn, adOpenKeyset,adLockReadOnly

   

   Range("A2").CopyFromRecordset rs  ‘直接粘貼到Excel中

End Sub