topcat 姍舞之間的極度凝聚

利用Array傳遞參數、變數、條件到COM方法

以前建立COM中的Function，往往只要改變參數個數，就會造成機碼不相容的狀況，偶發現一個可以避免的方法

舊 Function A(byval p1 as string, byval p2 as string ,byval p3 as string) as Adodb.Recordet
新 Function A(Parameter as Varinet,Rs as Varent) as Varient
其中，Parameter是一個矩陣，可用來作參數傳遞、多筆維護、SQL查詢條件等。
傳遞【參數、變數】：

比如說有一個Function共有3個參數，分別是RTN,DEPT,AREA
新的寫法如下：
Asp中
 

RTN = Session(“userrtn”)
DEPT = Session(“userdept”)
AREA = Session(“userarea”)
Dim P(3)
P(1) = RTN
P(2) = DEPT
P(3) = AREA
Set Obj = Server.CreateObject(“Pxxx0000.Cxxx0000”)
RC = Obj.A(P,Rs)

Com 中：
 

Public Function A() Function A(Parameter as Varient,Rs as Varient) as Varient
Dim RTN as String
Dim DEPT as String
Dim AREA as String

If Not(IsArray(Parameter) Then
        Goto Err_NotArray
End If


RTN = Parameter(1)
DEPT = Parameter(2)
AREA = Parameter(3)

Set Rs = CreateObject(“ADODB.Recordset”)
With Rs
        .CursorLocation = adUseClientBatch
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .ActiveConnection = conn  
End With

SQLTXT = “SELECT * FROM TXXX1234 WHERE (RTN = ‘” & RTN & ”’) AND (DEPT = ‘” & DEPT & ”’) AND (AREA=’” & AREA & ”’)”
Rs.Open SQLTXT

A = “Success”

……………….

 

==============================================================================
例1：傳遞【條件】

ASP:

Dim P(3,7)
P(1,1) = "WHERE"
P(2,1) = "(SOURCE = "
P(3,1) = "N'2'"
P(1,2) = "OR"
P(2,2) = "SOURCE = "
P(3,2) = "N'5'"
P(1,3) = "OR"
P(2,3) = "SOURCE = "
P(3,3) = "N'6')"
P(1,4) = "AND"
P(2,4) = "(DIVPROD = "
P(3,4) = "N'B'"
P(1,5) = "OR"
P(2,5) = "DIVPROD = "
P(3,5) = "N'I'"
P(1,6) = "OR"
P(2,6) = "DIVPROD = "
P(3,6) = "N'9')"
P(1,7) = "ORDER BY"
P(2,7) = "PROD"
P(3,7) = ""
Set Obj = Server.CreateObject("PXXXXXX.CXXXXXXX")
RC = Obj.ProdMutiLst(P,rs)

===============================================================================

COM:
 

Set Rs = CreateObject("ADODB.Recordset")

With Rs
        .CursorLocation = adUseClientBatch
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .ActiveConnection = conn
End With
‘基本的SELECT
SQLTXT1 = "SELECT * FROM VSANPRD"

If Not IsArray(Parameter) Then
        SQLTXT1 = SQLTXT1 & ""
Else
        ArrayCount = UBound(Parameter, 2)
        For i = 1 To ArrayCount
                SQLTXT1 = SQLTXT1 & " " & Parameter(1, i) & " " & Parameter(2, i) & " " & Parameter(3, i)
        Next
End If
Rs.Open SQLTXT1

ProdMutiLst = "Success"

例2：傳遞【變數、參數、多筆維護】
ASP:

‘***************************************************************
‘** 此部份傳遞變數(參數)
‘***************************************************************

Dim Parameter(13)  
Parameter(1) = OldStudyID  
Parameter(2) = StudyID  
Parameter(3) = YYYY  
Parameter(4) = SName  
Parameter(5) = Birthday  
Parameter(6) = Father  
Parameter(7) = Mother  
Parameter(8) = ClassID  
Parameter(9) = PostNo  
Parameter(10) = City  
Parameter(11) = Area  
Parameter(12) = Road  
Parameter(13) = Memo  

'***************************************************************
'** 此部份【多筆維護】
'***************************************************************
Dim Tel()  
TelCnt = Request.Form("TelNo").Count  
ReDim Tel(2,TelCnt)  
For y=1 to TelCnt  
        Tel(1,y) = Request.Form("TelName").Item(y)  
        Tel(2,y) = Request.Form("TelNo").Item(y)  
Next  
Set Obj = Server.CreateObject("PCSLStudent.CCSLStudent0")  
RC = Obj.StudentModify(RType,Parameter,Tel,rs)

COM:
 

'***************************************************************
'** 此部份傳遞變數(參數)
'***************************************************************
OldStudyID = Parameter(1)
StudyID = Parameter(2)
YYYY = Parameter(3)
SName = Parameter(4)
Birthday = Parameter(5)
Father = Parameter(6)
Mother = Parameter(7)
ClassID = Parameter(8)
PostNo = Parameter(9)
City = Parameter(10)
Area = Parameter(11)
Road = Parameter(12)
Memo = Parameter(13)

TelCnt = UBound(Tel, 2)

SQLTXT2 = "INSERT INTO StudentMain (StudyID, YYYY, Name, Birthday, Father, Mother, ClassID, PostNo, City, Area, Road, [Memo]) VALUES ('" & StudyID & "', '" & YYYY & "', '" & SName & "', '" & Birthday & "', '" & Father & "', '" & Mother & "', " & ClassID & ", '" & PostNo & "', '" & City & "', '" & Area & "', '" & Road & "', '" & Memo & "')"
Conn.Execute SQLTXT2

  

'***************************************************************
'‘** 此部份【多筆維護】
'***************************************************************
For y = 1 To TelCnt
        TelName = Tel(1, y)
        TelNo = Tel(2, y)
        SQLTXT3 = "INSERT INTO StudentTel (StudyID, TelName, TelNo) VALUES ('" & StudyID & "', '" & TelName & "', '" & TelNo & "')"
        conn.Execute SQLTXT3
Next y

以下是簽名：

• 歡迎轉貼本站的文章，不過請在貼文主旨上加上【轉貼】，並在文章中附上本篇的超連結與站名【topcat姍舞之間的極度凝聚】，感恩大家的配合。
• 小喵大部分的文章會以小喵熟悉的語言VB.NET撰寫，如果您需要C#的Code，也許您可以試著用線上的工具進行轉換，這裡提供幾個參考
  • http://converter.telerik.com/
  • http://www.carlosag.net/tools/codetranslator/
  • http://www.developerfusion.com/tools/convert/vb-to-csharp/