将用户定义的表类型从 VBA 传递到 SQL [英] Pass user-defined table type from VBA to SQL

问题描述

我的任务是创建一个 Excel 电子表格作为 SQL 数据库的前端和一些对数据执行复杂计算的 C#.我的老板想要前端作为电子表格，而对于 VBA 来说，计算似乎太复杂了.

I have been tasked with creating an Excel spreadsheet as the front-end to a SQL database and some C# which performs a complicated calculation on the data. My boss wants the front end as a spreadsheet, and the calculation is seemingly too complex for VBA.

目前，检索数据集的存储过程工作正常.然后，用户将在 Excel 中编辑数据并将其发送回数据库.数据需要作为一个新集合插入，保留以前集合的记录.因此，我编写了以下 SQL 存储过程来完成此任务:

Presently, the stored procedure to retrieve the dataset works fine. The user will then edit the data in Excel and send it back to the database. The data needs to be inserted as a new set, preserving the records of the previous sets. I have therefore written the following SQL stored procedure to complete this task:

DECLARE @Now DATETIME = GETDATE()
DECLARE @DataSetID SMALLINT = (SELECT ISNULL(MAX([DataSetID]), 0) FROM [dbo].[tbl_DataSet]) + 1

--Add DataSet entry
INSERT INTO [dbo].[tbl_DataSet] ([DataSetID], [InputDate])
SELECT @DataSetID, @Now

--Add latest data
INSERT INTO [dbo].[tbl_Data] ([DataSetID], [DataID], [Amount])
SELECT @DataSetID, [DataID], [Amount]
FROM @DataTable
ORDER BY [DataID]

这意味着 VBA 代码需要能够调用存储过程，传入一个用户定义的表类型参数，@DataTable AS [dbo].[typ_DataTable] READONLY.

This means that the VBA code needs to be able to call the stored procedure, passing in a user-defined table type parameter, @DataTable AS [dbo].[typ_DataTable] READONLY.

我目前调用 SQL 用户存储过程的方法，传入标准数据类型参数:

My present method for calling a SQL user stored procedure, passing in standard datatype parameters:

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.ConnectionString = getConnectionString()
cnn.CursorLocation = adUseClient
cnn.Open

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.Parameters.Append cmd.CreateParameter("in", adInteger, adParamInput, , 1)
cmd.Parameters.Append cmd.CreateParameter("bReturn", adBoolean, adParamOutput)
cmd.ActiveConnection = cnn
cmd.CommandText = ProcedureName
cmd.CommandType = adCmdStoredProc

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Set rst = cmd.Execute()

是否有一种简单的方法可以传入用户定义的表类型?

Is there a simple way to pass in user-defined table types?

或者，有没有更好的系统可以用来做这件事?

Alternatively, is there an entirely better system that I can use to do this?

目前我能想到的唯一解决方法是将数据传递给 C#，然后使用以下方法发送到数据库:

The only work-arounds I can think of at the moment is to pass the data to C#, then send to the database using:

command.Parameters.Add(new SqlParameter {
    ParameterName = "@DataTable",
    Value = dataTable,
    TypeName = "[dbo].[typ_DataTable]",
    SqlDbType = SqlDbType.Structured }); 

然而，这似乎有点迂回.

However, this seems a little circuitous.

谢谢.

更新:

由于已经有从 C# 到 Excel 的管道，似乎最好利用这一段令人讨厌的代码，而不是用 Excel <-> SQL 引入第二段代码.C# 还可以轻松用于转换数据类型并验证任何参数或返回变量.使用 VBA 到 C# 的伪异步方法也将改进 Excel 界面，防止挂在 UI 线程上.

As there is already a pipeline from C# to Excel, it seems best to utilise this one nasty bit of code as opposed to introducing a second with Excel <-> SQL. C# can also be easily used to convert the data types and validate any parameters or return variables. Using pseudo-asynchronous methods with VBA to C# will also improve the Excel interface, preventing hanging on the UI thread.

推荐答案

ADO 有一个非常好的表格类型.

ADO has a very nice table type.

目前，检索数据集的存储过程工作正常."

"Presently, the stored procedure to retrieve the dataset works fine. "

分离数据集.

然后用户将在 Excel 中编辑数据...

The user will then edit the data in Excel...

将新数据附加到分离的数据集.从分离的数据集中删除旧数据

Append the new data to the detached dataset. Delete the old data from the detached dataset

...并将其发送回数据库.

... and send it back to the database.

重新附加数据集.更新数据:

Re-Attach the dataset. Update the data:

myRecordSet.ActiveConnection=oConnection
myRecordSet.UpdateBatch

这篇关于将用户定义的表类型从 VBA 传递到 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！