如何将数组传递给执行存储命令？ [英] how can I pass an array to a execute store command?

问题描述

如何传递一个以逗号分隔的整数数组到实体
作为参数
的 ExecuteStoreCommand 我无法执行此操作：

  this.ObjectContext.ExecuteStoreCommand（INSERT INTO SurveyPatientListMrns 
（UserData，MrnId）SELECT DISTINCT'{0} '，MrnId 
 FROM PatientVisits WHERE（FacilityId = {1}）
 AND（UnitId IN（{2}）），userData，facilityId，（string.Join（，，unitIds））） ; 
  

这里（string.Join（，，unitIds））是一个字符串，因为逗号，我无法将其转换为整数。我如何传递参数？

FYI，unitIds是整数数组

解决方案

虽然它看起来像一个 string.Format 操作， ExecuteStoreCommand 在内部构建一个参数化查询提高性能并帮助保护您免受SQL注入攻击。 （MSDN）

当您将 string.Join 作为 ExecuteStoreCommand 的参数，它将该结果视为 IN 子句的值列表，而是刚刚看起来像一个字符串的字符串。基本上它将生成一个 IN 子句，如下所示：

  UnitId IN（'1,2,3'））
  

这显然不是你想要的。 / p>

您将不得不使用 string.Join 列出的$ $ c来构建SQL命令$ c> uinitIds 在传递之前 ExecuteStoreCommand ：

  string query = @INSERT INTO SurveyPatientListMrns（UserData，MrnId）
 SELECT DISTINCT'{0}'，MrnId 
 FROM PatientVisits WHERE（FacilityId = {1}）AND 
（UnitId IN（+ string.Join（，，unitIds）+））; 
 this.ObjectContext.ExecuteStoreCommand（query，userData，facilityId）; 
  

通常，应避免动态构建SQL查询，因为SQL注入攻击的可能性，但在此case，你知道 unitIds 是整数列表，因此你应该可以。

How to pass an array of integer separated by comma to an ExecuteStoreCommandin the entities as a parameter I am not able to execute this :

this.ObjectContext.ExecuteStoreCommand("INSERT INTO SurveyPatientListMrns 
  (UserData, MrnId) SELECT DISTINCT '{0}' , MrnId 
FROM PatientVisits WHERE (FacilityId = {1})
AND (UnitId IN ({2}))", userData, facilityId, (string.Join(",", unitIds)));

Here (string.Join(",", unitIds)) is a string and i can not cast it as integer because of the commas. How can i pass the parameter then?

FYI, unitIds is a array of integers

解决方案

Though it looks like a string.Format operation, ExecuteStoreCommand is internally building a parameterized query to increase performance and help protect you from SQL injection attacks. (MSDN)

When you do your string.Join as a parameter to ExecuteStoreCommand, it treats that result not as a list of values for the IN clause, but a string that just happens to look like one. Basically it will generate an IN clause that looks like this:

(UnitId IN ('1,2,3'))

Which is obviously not what you want.

You're going to have to build the SQL command with the string.Join-ed list of uinitIds BEFORE passing it ExecuteStoreCommand:

string query = @"INSERT INTO SurveyPatientListMrns  (UserData, MrnId) 
    SELECT DISTINCT '{0}' , MrnId 
    FROM PatientVisits WHERE (FacilityId = {1}) AND 
    (UnitId IN (" + string.Join(",", unitIds) + "))";
this.ObjectContext.ExecuteStoreCommand(query, userData, facilityId);

Normally one should avoid dynamically building SQL queries because of the possibility of a SQL injection attack, but in this case, you know that unitIds is a list of integers, and therefore you should be OK.

这篇关于如何将数组传递给执行存储命令？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！