c# - 通过 WinForms 执行 OpenRowset 查询时必须声明表变量“@P1select”

Question

我创建了一个 WindowsForms 工具，当我单击“执行”按钮时，它可以执行放置在路径中的任何选择脚本。现在我用 OpenRowSet 概念创建了一个大查询，它还创建了许多临时表。

但是当我直接在 SSMS 中执行时，我会得到所需的行。当我尝试通过该工具时，它给出了错误：

由于编译错误无法分析批次，必须声明表变量@P1select"

try
{
    DirectoryInfo d = new DirectoryInfo(path);
    FileInfo[] sqlfiles = d.GetFiles("*.sql");
    SqlCommand cm;
    using (SqlConnection cnn = new SqlConnection(ConnectionString))
        if (!(sqlfiles.Length == 0))
        {
            try
            {
                cnn.Open();
                foreach (FileInfo file in sqlfiles)
                {
                  
                    string script = file.OpenText().ReadToEnd();
                  
                    using (cm = cnn.CreateCommand())
                        cm.CommandText = script;
                  
                    cm.CommandTimeout = 3600;
                  
                    SqlDataReader reader = cm.ExecuteReader();
                  
                    var dt = new DataTable();
                    dt.Load(reader);
                    DataGV.AutoGenerateColumns = true;
                    DataGV.DataSource = dt;
                    DataGV.Refresh();
                    DataGV.ReadOnly = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error:" + ex.Message, "Warning");
            }

我想在代码中更新什么来处理这个问题吗？

开放行集查询：

    ''SELECT C.ClientName,C.createdon as ClientCreatedOn,C.Clientid  ,E.EnName,E.EngIDnum
         , E.CreatedOn as EngagementCreatedOn, 
              case when [AuditProjectDefinitionID] is null then case when E.[EngIDnum] is null then ''''Client'''' else ''''Engagement'''' end
                 else ''''Project'''' end ClientEngProject,  
         CN.CountryName
      ,[ProjectNumber]
      ,AudPrj.[AuditProjectName],AudPrj.AuditProjectDefinitionID,
      AudPrj.WorkflowEngIDnum
      
 ,case when mngreq =''''d0840bb3-2a51-4986-ba72-9cbf87bad77f'''' and  audprjactivity =''''A48DCB00-8092-4072-B271-A6B09D517A3A''''   then ''''Audit Project Linked to Enterprise or Other Risk''''
            when  mngreq =''''d0840bb3-2a51-4986-ba72-9cbf87bad77f'''' and audprjactivity =''''F7647E40-B4A2-45D5-9367-21CE413CBB97''''   then ''''Audit Project not Linked to  Enterprise or Other Risk''''
              when mngreq =''''d0840bb3-2a51-4986-ba72-9cbf87bad77f'''' and audprjactivity =''''E4041928-51F2-4765-8318-3498685860A1'''' then ''''Other IA Activity''''
               when mngreq =''''d52b9266-2080-4ac4-a5a6-8b77f59feec6'''' then ''''mngreqProjects''''
                       else  ''''Projects Not added'''' end as audprjactivity 
                                                                                                                                  
   ,case when ProjectType =''''1CC8E2AF-0A5F-4436-928A-4DE0176E2550''''   then ''''Compliance''''
            when ProjectType =''''0F645210-4E03-40FB-A177-93733798F75E''''   then ''''Risk Based''''
                      when ProjectType is null then ''''''''
            else ''''Follow up'''' end as ProjectType 

                                        
                                FROM ' + @DatabaseName1 + '.'+@EngSchemaName1+'.[Client] C
    left join  ' + @DatabaseName1 + '.'+@EngSchemaName1+'.Engagement E on C.ClientID = E.ClientID
    
  left join  ' + @DatabaseName1 + '.'+@EngSchemaName1+'.[AuditProjectDefinition] AudPrj on AudPrj.ClientId=E.ClientID and 
   AudPrj.EngIDnum=E.EngIDnum 
  and 
   ISNULL(IsDeleted,0)<>1
  left join ' + @DatabaseName1 + '.'+@EngSchemaName1+'.Country CN on C.CountryID=CN.CountryID  

    ORDER BY C.ClientID, E.EngIDnum, AudPrj.ProjectNumber 

    '')'

    INSERT INTO #ReportDet exec(@sql1) 


    if OBJECT_ID('tempdb.dbo.#EngagementSDP','U') IS NOT NULL DROP TABLE #EngagementSDP
CREATE TABLE #EngagementSDP(EngIDnum Nvarchar(max), WorkflowEngIDnum Nvarchar(max),subpracticeid int)
 
SET @sql4 = 'SELECT * FROM OPENROWSET(''SQLNCLI'', ''Server=' + @SQLServerName1 + ';Trusted_Connection=yes;'', 
    ''SELECT EngIDnum,WorkflowEngIDnum,subpracticeid
    FROM '+ @DatabaseName1 + '.'+@EngSchemaName1+'.EngagementSDP'')'```

标签： c#.netsql-serverwinforms