时间戳

首页 > 解决方案 > SQL 命令字符串 c#

c# - SQL 命令字符串 c#

问题描述

我目前正在编写一个导出程序事件日志并将其写入数据库的小脚本。但是，我的 SQLinsert命令有问题。我究竟做错了什么？这只是一个代码剪切

string command = "DECLARE @x xml;";
command += "SELECT @x = P";
command += "FROM OPENROWSET(BULK 'C:\\Users\\NT-AUTORITÄT\\SYSTEM\\MFilesLog\\Event-Log 23.10.2020 09-05.xml', SINGLE_BLOB) AS Products(P)";
command += "SELECT @x";
command += "DECLARE @hdoc int";
command += "EXEC sp_xml_preparedocument @hdoc, OUTPUT, @x";
command += "INSERT INTO [MFlogs].[dbo].[MLogs]";
command += "SELECT * FROM OPENXML(@hdoc, '/Export/root/event/data/objectversion/objver', 2);";
command += "WITH(";
command += "id int '../../../id',";
command += "type varchar(50) '../../../type',";
command += "category varchar(50) '../../../category',";
command += "timestamp varchar(50) '../../../timestamp',";
command += "causedbyuser varchar(50) '../../../causedbyuser',";
command += "objtype varchar(50),";
command += "objid varchar(50),";
command += "version varchar(50),";
command += "objectguid varchar(50) '../objectguid',";
command += "versionguid varchar(50) '../versionguid',";
command += "title varchar(100) '../title',";
command += "displayid int '../displayid')";

SqlCommand cmd = new SqlCommand(command, connection);
int result = cmd.ExecuteNonQuery();

这是事件日志中的错误：

必须声明 @hdoc 标量变量。

有人可以帮忙吗？

标签： c#sqlsqlcommand

解决方案

只看第一行，你就会遇到问题：

    command += "SELECT @x = P";
    command += "FROM OPENROWSET(BULK 'C:\\Users\\NT-AUTORITÄT\\SYSTEM\\MFilesLog\\Event-Log 23.10.2020 09-05.xml', SINGLE_BLOB) AS Products(P)";

最终会像：

SELECT @x = PFROM OPENROWSET(BULK 'C:\\Users\\NT-AUTORI ....

因为你没有留下任何间距，像这样，但在其他任何地方你也需要它：

command += "SELECT @x = P";
command += " FROM OPENROWSET(BULK 'C:\\Users\\NT-AUTORITÄT\\SYSTEM\\MFilesLog\\Event-Log 23.10.2020 09-05.xml', SINGLE_BLOB) AS Products(P)";

尝试修复所有间距问题，看看会发生什么。

推荐阅读