Calling Stored Procedures
public bool CallSP(string queryString, object paramNames, object paramValues)
This calls a stored procedure. It returns true on success, false otherwise. You can pass the name and value pair of each parameter to CallSP as two arrays. This is useful when working with many parameters and XML aggregates. In this case, the query string argument to CallSP must be the name of the parameter:
CallSP("CreateTable",nameArray,valueArray)The following example shows how to pass inputs to a stored procedure call and iterate through any output parameters.
Dim nameArray
nameArray = Array("Name", "Industry")
Dim valueArray
valueArray = Array("Jon Doe", "Floppy Disks")
Dim module As New ExcelComModule
module.SetProviderName ("Elasticsearch")
module.SetConnectionString ("Server=127.0.0.1;Port=9200;")
Query = "INSERT INTO [CData].[Elasticsearch].Employee (Name, Industry) VALUES (@Name, @Industry)"
If module.Insert(Query, nameArray, valueArray) Then
MsgBox "The insertion was successful."
Else
MsgBox "The insertion failed."
End If
module.Close
Dim module As New ExcelComModule
module.SetProviderName ("Elasticsearch")
module.SetConnectionString ("Server=127.0.0.1;Port=9200;")
Dim nameArray
nameArray = Array("TableName")
Dim valueArray
valueArray = Array("Orders")
StoredProc = "CreateTable"
Cursor = Application.Cursor
Application.Cursor = xlWait
If module.CallSP(StoredProc, nameArray, valueArray) Then
MsgBox "Execution was successful."
Dim ColumnCount As Integer
ColumnCount = module.GetColumnCount
For Count = 0 To ColumnCount - 1
Application.ActiveSheet.Cells(1, Count + 1).Value = module.GetColumnName(Count)
Next
Dim RowIndex As Integer
RowIndex = 10
While (Not module.EOF)
For columnIndex = 0 To ColumnCount - 1
If Conversion.CInt(module.GetColumnType(columnIndex)) = Conversion.CInt(vbDate) And Not IsNull(module.GetValue(columnIndex)) Then
Application.ActiveSheet.Cells(RowIndex, columnIndex + 1).Value = Conversion.CDate(module.GetValue(columnIndex))
Else
Application.ActiveSheet.Cells(RowIndex, columnIndex + 1).Value = module.GetValue(columnIndex)
End If
Next
module.MoveNext
RowIndex = RowIndex + 1
Wend
Else
MsgBox "Execution failed."
End If
Application.Cursor = Cursor
module.Close