根据传递的变量运行查询 [英] Run a query based on variables passed

问题描述

我正在阅读两个不同的文本文件，一个文件读取事务ID并运行下面的查询以匹配文本文件记录。另一个文本文件检查客户ID并运行相同的查询。我需要做的是构建一个stringbuilder？因此，当它运行事务文本文件时，它会从where子句中删除CustomerID IS NOT NULL。



我尝试过： <

私有DataRowCollection ExtractData（DateTime DailyFileDate）
 {
 Log.Info（查询Microbilt数据......） ; 
 
 string sql = string.Empty; 
 
 {
 sql = @
 SELECT 
 TransID，
 CustomerID，
 RID，
 FileDate 
 FROM 
（
 SELECT TransID AS TransID，
 CustomerID，
 RID，
 FileDate，
 ROW_NUMBER（）OVER（由TransID DESC划分的TransID ORDER）AS RN 
来自MicroBilt.dbo.TransIDIgnoreAudit 
 WHERE FileDate BETWEEN @FileDate AND DATEADD（D，1，@ FileDate AND CustomerID IS NOT NULL 
）A 
 WHERE RN = 1; 
 

解决方案

作为 NotPoliticallyCorrect [ ^ ]提及，你必须使用存储过程 [ ^ ]。

请参阅：演练：仅使用存储过程（C＃） [ ^ ]



顺便说一下...

你可以上传数据直接从文本文件转换为数据表 [< a href =https://msdn.microsoft.com/en-us/library/system.data.datatable(v=vs.110).aspx\"target =_ blankti tle =新窗口> ^ ]对象使用 ADO。 NET [ ^ 。

请参阅：

关于文本文件的大量ADO [ ^ ]

Schema.ini文件（文本文件）司机）| Microsoft Docs [ ^ ]



检查我的过去答案：读取文本文件特定列 [ ^ ]

I am reading two different text files, one file reads the transaction ID and runs the query below to match the text file record. The other text file checks for Customer ID and runs this same query as well. What I need to do is build maybe a stringbuilder? So when it runs the Transaction textfile it elimates the "CustomerID IS NOT NULL" from the where clause.

What I have tried:

private DataRowCollection ExtractData(DateTime DailyFileDate)
       {
           Log.Info("Querying Microbilt data...");

           string sql = string.Empty;

           {
           sql = @"
                           SELECT
                               TransID,
                               CustomerID,
                               RID,
                               FileDate
                           FROM
                           (
                               SELECT TransID AS TransID,
                               CustomerID,
                               RID,
                               FileDate,
                               ROW_NUMBER() OVER(PARTITION BY TransID ORDER BY TransID DESC) AS RN
                               FROM MicroBilt.dbo.TransIDIgnoreAudit
                               WHERE FileDate BETWEEN @FileDate AND DATEADD(D,1, @FileDate AND CustomerID IS NOT NULL
                           ) A
                           WHERE RN = 1";

解决方案

As NotPoliticallyCorrect[^] mentioned, you have to use stored procedure(s)[^].
See: Walkthrough: Using Only Stored Procedures (C#)[^]

By the way...
You can upload data directly from text file into datatable[^] object using ADO.NET[^].
See:
Much ADO About Text Files[^]
Schema.ini File (Text File Driver) | Microsoft Docs[^]

Check my past answer: Read Text File Specific Columns[^]

这篇关于根据传递的变量运行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！