查询问题。没有清除此查询 [英] Query problem. Not cleared this query

问题描述

 string qry = string.Format（@   WITH PayPeriod AS（
 SELECT CAST（DATEADD（day，1，DATEADD（{3}， -  {2}，'{1}'））AS DATE）[StartDate]，CAST（'{1}'AS DATE）PPE 
 UNION ALL 
 SELECT CAST（DATEADD（{3}， -  {2}，P。[StartDate]）AS DATE）[StartDate]，DATEADD（day，-1，CAST（P. [StartDate] AS DATE） ））[PPE]来自PayPeriod P 
 
 WHERE P. [StartDate]>'{0}'
 
）SELECT PPE from PayPeriod，arr [  0 ]。TrimEnd（' '） ，arr [ 1 ]。TrimEnd（' '），payfrequency，diff）; 

注意：我不明白这个查询可以有人解释，任何帮助都将受到高度赞赏。

谢谢

解决方案

我建​​议不要将字符串值直接插入SQL语句。根据值的来源，这会使您对SQL注入开放，但是您也会遇到隐式转换问题，例如字符串到目前为止等等。



为了使用字符串，请使用参数。有关详细信息，请参阅 SqlParameter [ ^ ]和正确执行数据库操作 [ ^ ]

只有几句解释：



1.首先： String.Format（）

  string  qry =  string  .Format （ @  ... {3} ... {2} ... {1} ... {0 } ...，arr [ 0 ]。TrimEnd（' '），arr [ 1 ]。TrimEnd（' '），payfrequency，diff）; 

String.Format方法 [ ^ ]用相应对象值的文本替换 {} 之间的每个项目。这意味着

  //   {0}变为添加[0] .TrimEnd（''） 
  //   {1}变为添加[1] .TrimEnd（''） 
  //   {2}变为payfrequency  
  //   {3}变为差异 

2.其次，一步一步查询：

   -   创建公用表表达式查询 -  CTE  
  WITH  PayPeriod  AS （
   -   初始部分：选择日期：StartDate和PPE  
  SELECT  CAST（DATEADD（日， 1  ，DATEADD（{ 3 }， -  { 2 }，' < span class =code-string> {1}'）） AS   DATE ） [StartDate]，CAST（'  {1}'  AS   DATE ）PPE 
   -    requery part：选择StartDate和PPE，直到StartDate大于传递值为arg [0]  
  UNION  所有 
  SELECT  CAST（DATEADD（{ 3 }， - { 2 }，P。[StartDate]） AS   DATE ）[StartDate]，DATEADD（日，-1，CAST（P. [StartDate]  AS   DATE < /跨度>）） [PPE] 
  FROM  PayPeriod P 
  WHERE  P. [StartDate]> '  {0}' 
）
  SELECT  PPE  FROM  PayPeriod 

更多信息，请参阅：

使用common_table_expression（Transact-SQL） [ ^ ]

使用公用表格式 [ ^ ]

DATEADD [ ^ ]

CAST AND CONVERT [ ^ ]

string qry = string.Format(@"WITH PayPeriod AS(
                SELECT CAST(DATEADD(day,1,DATEADD({3},-{2},'{1}')) AS DATE) [StartDate] ,CAST('{1}' AS DATE) PPE
                UNION ALL
                SELECT  CAST(DATEADD({3},-{2},P.[StartDate]) AS DATE) [StartDate] ,DATEADD(day,-1,CAST(P.[StartDate] AS DATE))[PPE] FROM PayPeriod P

                WHERE P.[StartDate] >'{0}'

                ) SELECT PPE FROM PayPeriod", arr[0].TrimEnd(' '), arr[1].TrimEnd(' '), payfrequency, diff);

Note: I don,t understand this query can someone explain, any help will highly be appreciated.
Thank you

解决方案

I would advice not inserting the string values directly to the SQL statement. Depending on where the values come from, this leaves you open to SQL injection but also you'll have problems with implicit conversions, for example string to date and so on.

In order of using the strings, use parameters. For more information, see SqlParameter[^] and Properly executing database operations[^]

Only few words of explanation:

1. First of all: String.Format()

string qry = string.Format(@"...{3}...{2}...{1}...{0}...", arr[0].TrimEnd(' '), arr[1].TrimEnd(' '), payfrequency, diff);

String.Format method[^] replaces each item in between {} with the text equivalent of a corresponding object's value. This means that

//{0} becomes to add[0].TrimEnd(' ')
//{1} becomes to add[1].TrimEnd(' ')
//{2} becomes to payfrequency
//{3} becomes to diff

2. Second of all, a query step by step:

--create Common Table Expression query - CTE
WITH PayPeriod AS(
    -- initial part: select dates: StartDate and PPE
    SELECT CAST(DATEADD(day,1,DATEADD({3},-{2},'{1}')) AS DATE) [StartDate] ,CAST('{1}' AS DATE) PPE
    -- requery part: select StartDate and PPE till StartDate is greater then value passed as arg[0]
    UNION ALL
    SELECT  CAST(DATEADD({3},-{2},P.[StartDate]) AS DATE) [StartDate] ,DATEADD(day,-1,CAST(P.[StartDate] AS DATE))[PPE]
    FROM PayPeriod P
    WHERE P.[StartDate] >'{0}'
)
SELECT PPE FROM PayPeriod

For further information, please see:
WITH common_table_expression (Transact-SQL)[^]
Using Common Table Expressions[^]
DATEADD[^]
CAST AND CONVERT[^]

这篇关于查询问题。没有清除此查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！