【7.0.100】服务端命令【执行SQL命令】设置SQL参数引用及SQL执行时有问题
本帖最后由 sz_xd 于 2021-9-6 18:25 编辑【7.0.100】服务端命令【执行SQL命令】设置SQL语句的参数引用有问题
非常感谢活字格团队加入服务端命令【执行SQL命令】， 使用这功能 正常是 可以实现动态的SQL 执行 ，这将极大方便大家使用，
已解决方法 ：
9月06日通过对页面表生成Json字符串，再通过【Json序列化命命】后 发现 采用 nvarchar 参数还是可以，数据量达到3万条考勤数据的由越南工厂上传至香港服务器，但发现采用动态SQL完程保存数据是会影响速度，如果保存1000条数据是没问题，不足1秒完成。
如果一次保存多达3万记录时，还是通过固定的存储过程上传 Json数据，经测试：3万条数据只需要3秒就完成上传了，因这种场境不多！否则采用动态存储过程也可以的，只是效果差些，但都比动态SQL好。
以下为我上传香港服务器的测试数据：
原发现问题：
9月05日发现使用 SQL命令时 发现存在以下问题 ：
1. 发现引用参数的类型，发现少了一个nvarchar(MAX) ，这个SQL对超长字节的类型； 我使用这类型的目的就是 引用 已转换为Json 字符串，因我发现你默认对nvarchar类型为nvarchar(4000),这只有4000个字符串，这对于 Json 字符串这一般是不较用的，故需要你要加用nvarchar(MAX)类型，我后附C#对这类型代码参考！
2.我今天操作使用对Sql命令引用参数时，也提示出错，而我使用了sp_executesql直接执行SQL语句 ，这其中有引用对传入参数，如：Json 字符串
我使用 sp_executesql直接执行SQL语句 的主要目的是减少 活字格平台过度依赖使用SQL存储过程，因项目在实施以后对SQL存储过程的维护或修改是非常艰苦的，这当然是我个人认为，仅作参考；
后附出错见附图：
nvarchar(MAX)类型建议参考：
C#操作Nvarchar(max)
和操作一般nvarchar一样，要注意的一点就是SqlDbType.Nvarchar的长度设置为-1，
new SqlParameter("@text",SqlDbType.NVarChar,-1)
public bool AddMax()
{ StringBuilder strSql = new StringBuilder();
strSql.Append("insert into tb_MaxTest(");
strSql.Append("id,text) values(@id,@text);");
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@id",SqlDbType.VarChar,50),
new SqlParameter("@text",SqlDbType.NVarChar,-1)
};
parameters.Value = "111";
parameters.Value = "写stored procedure时候，如果有参数的type是nvarchar(max)在.Net端设定size时候要设为-1 举例：如果有个参数@Message数据型态是nvarchar(max)，在程序端撰写时需写成：new SqlParameter(\"@Message\",SqlDbType.NVarChar,-1)";
int row = DbHelperSQL.ExecuteSql(@"server=CNPVG-SQLVM\TESTER;InitialCatalog=DMS_Quotation;Pwd=Welcome88;Uid=sqlAdmin;",strSql.ToString(),parameters);
return row > 0;
}
出错图1：
出错图2：
后附参考内容，以帮助大家了解这动态SQL的效果及用法：
动态SQL的执行，注：exec sp_executesql 其实可以实现参数查询和输出参数的
当需要根据外部输入的参数来决定要执行的SQL语句时，常常需要动态来构造SQL查询语句，个人觉得用得比较多的地方就是分页存储过程和执行搜索查询的SQL语句。一个比较通用的分页存储过程，可能需要传入表名，字段，过滤条件，排序等参数，而对于搜索的话，可能要根据搜索条件判断来动态执行SQL语句。
在SQL Server中有两种方式来执行动态SQL语句，分别是exec和sp_executesql。sp_executesql相对而言具有更多的优点，它提供了输入输出接口，可以将输入输出变量直接传递到SQL语句中，而exec只能通过拼接的方式来实现。还有一个优点就是sp_executesql，能够重用执行计划，这就大大提高了执行的性能。所以一般情况下建议选择sp_executesql来执行动态SQL语句。
使用sp_executesql需要注意的一点就是，它后面执行的SQL语句必须是Unicode编码的字符串，所以在声明存储动态SQL语句的变量时必须声明为nvarchar类型，否则在执行的时候会报“过程需要类型为 'ntext/nchar/nvarchar' 的参数 '@statement'”的错误，如果是使用sp_executesql直接执行SQL语句，则必须在前面加上大写字母N，以表明后面的字符串是使用Unicode类型编码的。
下面来看看几种动态执行SQL语句的情况 1.普通SQL语句 (1)exec('select * from Student') (2)exec sp_executesql N'select * from Student'--此处一定要加上N，否则会报错 2.带参数的SQL语句 (1)declare @sql nvarchar(1000)
declare @userId varchar(100)
set @userId='0001'
set @sql='select * from Student where UserID='''+@userId+'''' exec(@sql) (2)declare @sql nvarchar(1000)
declare @userId varchar(100)
set @userId='0001'
set @sql=N'select * from Student where UserID=@userId'
exec sp_executesql @sql,N'@userId varchar(100)',@userId 从这个例子中可以看出使用sp_executesql可以直接将参数写在sql语句中，而exec需要使用拼接的方式，这在一定程度上可以防止SQL注入，因此sp_executesql拥有更高的安全性。另外需要注意的是，存储sql语句的变量必须声明为nvarchar类型的。 (3)带输出参数的SQL语句 create procedure sp_GetNameByUserId ( @userId varchar(100), @userName varchar(100) output ) as declare @sql nvarchar(1000) set @sql=N'select @userName=UserName from Student where UserId=@userId' exec sp_executesql N'@userId varchar(100),@userName varchar(100) output',@userId,@userName output select @userName
链接：http://www.cnblogs.com/hnsdwhl/archive/2011/07/23/2114730.html
能具体说说 嘛 牛逼~ 非常感谢活字格团队加入服务端命令【执行SQL命令】， 使用这功能 正常是 可以实现动态的SQL 执行 ，这将极大方便大家使用，
已解决方法 ：
9月06日通过对页面表生成Json字符串，再通过【Json序列化命命】后 发现 采用 nvarchar 参数还是可以，数据量达到3万条考勤数据的由越南工厂上传至香港服务器，但发现采用动态SQL完程保存数据是会影响速度，如果保存1000条数据是没问题，不足1秒完成。
如果一次保存多达3万记录时，还是通过固定的存储过程上传 Json数据，经测试：3万条数据只需要3秒就完成上传了，因这种场境不多！否则采用动态存储过程也可以的，只是效果差些，但都比动态SQL好。
以下为我上传香港服务器的测试数据：
本帖最后由 Lay.Li 于 2021-9-27 12:17 编辑
sz_xd 发表于 2021-9-6 18:27
非常感谢活字格团队加入服务端命令【执行SQL命令】， 使用这功能 正常是 可以实现动态的SQL 执行 ，这将极 ...
首先也感谢各位大佬对活字格的支持，问题解决了就好，后边有什么问题，不管是产品需求呀，还是产品bug呀，您都可以再论坛发帖提问，我们都会及时参与交流，在大家共同的努力下让活字格变得越来越好，为广大格友提供更好的服务。:hjyzw: Lay.Li 发表于 2021-9-7 10:22
首先也感谢各位大佬对活字格的支持，问题解决了就好，后边有什么问题，不管是产品需求呀，还是产品bug呀 ...
好的，非常感谢！
:mj72: sz_xd 发表于 2021-9-7 14:43
好的，非常感谢！
:i0tw2: 可以上传demo码？ jlyhzxm 发表于 2021-11-22 00:10
可以上传demo码？
您好，这个是楼上大佬的经验分享，由于可能涉及外联库，所以不太方便上传demo，
您有疑问的地方，可以找大佬私聊求助哦 jlyhzxm 发表于 2021-11-22 00:10
可以上传demo码？
其实你留意胡老板的在活字格最新功能介绍中也有提出了，你可查一下，不用看我 demo，这是活字格最新版基本配置了，如不明的，你也可在求助贴上提出，
页:
[1]
2