【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[0].Value = "111";
         parameters[1].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;  Initial  Catalog=DMS_Quotation

wd=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

Aa金砂 · Aa金砂

能具体说说嘛

谢厅 · 谢厅

牛逼~

sz_xd · sz_xd

非常感谢活字格团队加入服务端命令【执行SQL命令】，使用这功能正常是可以实现动态的SQL 执行，这将极大方便大家使用，
已解决方法：
9月06日通过对页面表生成Json字符串，再通过【Json序列化命命】后发现采用 nvarchar 参数还是可以，数据量达到3万条考勤数据的由越南工厂上传至香港服务器，但发现采用动态SQL完程保存数据是会影响速度，如果保存1000条数据是没问题，不足1秒完成。
如果一次保存多达3万记录时，还是通过固定的存储过程上传 Json数据，经测试：3万条数据只需要3秒就完成上传了，因这种场境不多！否则采用动态存储过程也可以的，只是效果差些，但都比动态SQL好。

以下为我上传香港服务器的测试数据：

Lay.Li · Lay.Li

本帖最后由 Lay.Li 于 2021-9-27 12:17 编辑

sz_xd 发表于 2021-9-6 18:27
非常感谢活字格团队加入服务端命令【执行SQL命令】，使用这功能正常是可以实现动态的SQL 执行，这将极 ...

首先也感谢各位大佬对活字格的支持，问题解决了就好，后边有什么问题，不管是产品需求呀，还是产品bug呀，您都可以再论坛发帖提问，我们都会及时参与交流，在大家共同的努力下让活字格变得越来越好，为广大格友提供更好的服务。

sz_xd · sz_xd

Lay.Li 发表于 2021-9-7 10:22
首先也感谢各位大佬对活字格的支持，问题解决了就好，后边有什么问题，不管是产品需求呀，还是产品bug呀 ...

好的，非常感谢！

Lay.Li · Lay.Li

sz_xd 发表于 2021-9-7 14:43
好的，非常感谢！

jlyhzxm · jlyhzxm

可以上传demo码？

Joe.xu · Joe.xu

jlyhzxm 发表于 2021-11-22 00:10
可以上传demo码？

您好，这个是楼上大佬的经验分享，由于可能涉及外联库，所以不太方便上传demo，
您有疑问的地方，可以找大佬私聊求助哦

sz_xd · sz_xd

jlyhzxm 发表于 2021-11-22 00:10
可以上传demo码？

其实你留意胡老板的在活字格最新功能介绍中也有提出了，你可查一下，不用看我 demo ，这是活字格最新版基本配置了，如不明的，你也可在求助贴上提出，

		自动登录	找回密码
密码			立即注册

[已处理] 【7.0.100】服务端命令【执行SQL命令】设置SQL参数引用及SQL执行时有问题

本帖子中包含更多资源

评分

11 个回复

本帖子中包含更多资源

时代开发者征文活动

活字格认证

微信认证勋章

悬赏达人

讲师达人

活字格高级认证

圣诞拼拼乐