请选择 进入手机版 | 继续访问电脑版
 找回密码
 立即注册

QQ登录

只需一步,快速开始

sz_xd
金牌服务用户   /  发表于:2021-9-5 23:04  /   查看:4293  /  回复:11
本帖最后由 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_Quotationwd=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











本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

评分

参与人数 1金币 +200 收起 理由
Lay.Li + 200 赞一个!

查看全部评分

11 个回复

倒序浏览
Aa金砂
高级会员   /  发表于:2021-9-6 07:01:36
沙发
能具体说说 嘛
回复 使用道具 举报
谢厅讲师达人认证 悬赏达人认证 活字格认证
论坛元老   /  发表于:2021-9-6 08:54:24
板凳
牛逼~
回复 使用道具 举报
sz_xd
金牌服务用户   /  发表于:2021-9-6 18:27:56
地板
非常感谢活字格团队加入服务端命令【执行SQL命令】, 使用这功能 正常是 可以实现动态的SQL 执行 ,这将极大方便大家使用,
已解决方法 :
9月06日通过对页面表生成Json字符串,再通过【Json序列化命命】后 发现 采用 nvarchar 参数还是可以,数据量达到3万条考勤数据的由越南工厂上传至香港服务器,但发现采用动态SQL完程保存数据是会影响速度,如果保存1000条数据是没问题,不足1秒完成。
如果一次保存多达3万记录时,还是通过固定的存储过程上传 Json数据,经测试:3万条数据只需要3秒就完成上传了,因这种场境不多!否则采用动态存储过程也可以的,只是效果差些,但都比动态SQL好。


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

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 使用道具 举报
Lay.Li悬赏达人认证 活字格认证
超级版主   /  发表于:2021-9-7 10:22:32
5#
本帖最后由 Lay.Li 于 2021-9-27 12:17 编辑
sz_xd 发表于 2021-9-6 18:27
非常感谢活字格团队加入服务端命令【执行SQL命令】, 使用这功能 正常是 可以实现动态的SQL 执行 ,这将极 ...

首先也感谢各位大佬对活字格的支持,问题解决了就好,后边有什么问题,不管是产品需求呀,还是产品bug呀,您都可以再论坛发帖提问,我们都会及时参与交流,在大家共同的努力下让活字格变得越来越好,为广大格友提供更好的服务。
回复 使用道具 举报
sz_xd
金牌服务用户   /  发表于:2021-9-7 14:43:18
6#
Lay.Li 发表于 2021-9-7 10:22
首先也感谢各位大佬对活字格的支持,问题解决了就好,后边有什么问题,不管是产品需求呀,还是产品bug呀 ...

好的,非常感谢!

回复 使用道具 举报
Lay.Li悬赏达人认证 活字格认证
超级版主   /  发表于:2021-9-7 14:48:07
7#
sz_xd 发表于 2021-9-7 14:43
好的,非常感谢!

回复 使用道具 举报
jlyhzxm
中级会员   /  发表于:2021-11-22 00:10:13
8#
可以上传demo码?
回复 使用道具 举报
Joe.xu讲师达人认证 悬赏达人认证 活字格认证
超级版主   /  发表于:2021-11-22 10:28:47
9#
jlyhzxm 发表于 2021-11-22 00:10
可以上传demo码?

您好,这个是楼上大佬的经验分享,由于可能涉及外联库,所以不太方便上传demo,
您有疑问的地方,可以找大佬私聊求助哦
回复 使用道具 举报
sz_xd
金牌服务用户   /  发表于:2021-11-22 10:40:45
10#
jlyhzxm 发表于 2021-11-22 00:10
可以上传demo码?

其实你留意胡老板的在活字格最新功能介绍中也有提出了,你可查一下,不用看我 demo  ,这是活字格最新版基本配置了,如不明的,你也可在求助贴上提出,
回复 使用道具 举报
12下一页
您需要登录后才可以回帖 登录 | 立即注册
返回顶部