如何获得从sql动态数据透视到vb

  • Z时代
  • 2024-01-10
  • 分类：问答

我想根据我找到的元素得到一个动态的结果集。这里是我的查询的一个示例：如何获得从sql动态数据透视到vb

declare @til DateTime = dateadd(MINUTE, -0, getdate()) 
declare @fra datetime = DATEADD(MINUTE, -350, @til) 
declare @title nvarchar(max) = 'test title' 
DECLARE @cols AS NVARCHAR(MAX), 
@query AS NVARCHAR(MAX); 
create table errors (collection_id bigint, nr smallint, position smallint, stamp datetime) 
create table t (collection_id bigint, collection_name nvarchar(max), nr smallint, [status] smallint, stamp datetime) 
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(errors.position) 
         from t t 
         left join errors on errors.collection_id = t.collection_id and errors.nr = t.nr 
         where t.Status = 4 and errors.Stamp > @fra and t.collection_name = ''' + @title + ''' and errors.collection_id is not null 
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') , 1, 1, ''); 
select @cols 
set @query = 'declare @til DateTime = dateadd(MINUTE, -0, getdate()) 
      declare @fra datetime = DATEADD(MINUTE, -350, @til) 
      ;with cte as (select t.collection_name, errors.position, count(errors.Tryksag_Nedtagsfejl_Id) antal 
      from t 
      left join errors on errors.collection_id = s.collection_id and errors.nr = t.nr 
      where t.Status = 4 and errors.Stamp > @fra and and t.collection_name = ''' + @title + ''' 
      group by t.collection_name, errors.position) 
       SELECT collection_name, ' + @cols + ' from 
       cte 
      pivot 
      (
       sum(antal) 
       for position in (' + @cols + ') 
      ) p ' 
execute(@query)

到目前为止，我已经做了查询，我可以在SSMS运行，并给出了输出我的愿望。这将是这样的：

我怎样才能使这个结果集提供给我在vb.net？当我运行它作为查询它不给我的结果（假设它不会看到从执行结果集），我认为这是更好地创建一个存储

添加VB代码

Dim var_til As Short = 0 
      Dim var_fra As Short = -60 
      Dim Linie As String = "Red" 
      Dim tx = "Test title" 
      Dim Stt2 = "declare @til DateTime = dateadd(MINUTE, " & var_til & ", getdate()) " _ 
       & "declare @fra datetime = DATEADD(MINUTE, " & var_fra & " , @til) " _ 
       & "DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) " _ 
       & "declare @linie as nvarchar(max) = '" & Linie & "' " _ 
       & "declare @title as nvarchar(max) = '" & tx & "' " _ 
       & "select @cols = STUFF((SELECT distinct ',' + QUOTENAME(errors.position) " _ 
       & "from t " _ 
       & "left join errors on errors.collection_id = t.collection_id And errors.nr = t.nr " _ 
       & "where t.Status = 4 And errors.Stamp > @fra And t.collection_name = ''' + @title + ''' and errors.collection_id is not null " _ 
       & "FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') , 1, 1, '') " _ 
       & " " _ 
       & "set @query = ';with cte as (select t.collection_name, errors.position, count(errors.Tryksag_Nedtagsfejl_Id) antal " _ 
       & "from t " _ 
       & "left join errors on errors.collection_id = s.collection_id And errors.nr = t.nr " _ 
       & "where t.Status = 4 And errors.Stamp > @fra And And t.collection_name = ''' + @title + ''' " _ 
       & "group by t.collection_name, errors.position) " _ 
       & "SELECT collection_name, ' + @cols + ' from " _ 
       & "cte " _ 
       & "pivot " _ 
       & "(" _ 
       & "sum(antal) " _ 
       & "for position in (' + @cols + ') " _ 
       & ") p ' " _ 
       & "execute(@query) " 
      Dim sqlConnection2 As New SqlConnection("Data Source=CONDOR-TI;Initial Catalog=Condor_db;Integrated Security=True") 
      Dim cmd2 As New SqlCommand 
      Dim reader2 As SqlDataReader 
      cmd2.CommandText = Stt2 
      cmd2.CommandType = CommandType.Text 
      cmd2.Connection = sqlConnection2 
      sqlConnection2.Open() 
      reader2 = cmd2.ExecuteReader() 
      While reader2.Read 
       Console.Write(reader2(0)) 
      End While 
      Console.WriteLine() 
      sqlConnection2.Close() 
      reader2.Close()

回答：

过程来执行该SQL命令，并使用下面的代码从VB.Net执行它：

Dim sqlConnection1 As New SqlConnection("Your Connection String") 
Dim cmd As New SqlCommand 
Dim reader As SqlDataReader 
cmd.CommandText = "StoredProcedureName" 
cmd.CommandType = CommandType.StoredProcedure 
cmd.Connection = sqlConnection1 
sqlConnection1.Open() 
reader = cmd.ExecuteReader() 
' Data is accessible through the DataReader object here. 
sqlConnection1.Close()

参考

  • How to: Execute a Stored Procedure that Returns Rows

回答：

您可以分两步运行。确保你在同一个连接上运行这两个命令。

在第一个命令中，使用ExecuteNonQuery将cte选择到#temp表中。

在第二个命令调用ExecuteReaer方法（）

Dim var_til As Short = 0 
Dim var_fra As Short = -60 
Dim Linie As String = "Red" 
Dim tx = "Test title" 
Dim Stt2 = "declare @til DateTime = dateadd(MINUTE, " & var_til & ", getdate()) " _ 
    & "declare @fra datetime = DATEADD(MINUTE, " & var_fra & " , @til) " _ 
    & "DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) " _ 
    & "declare @linie as nvarchar(max) = '" & Linie & "' " _ 
    & "declare @title as nvarchar(max) = '" & tx & "' " _ 
    & "select @cols = STUFF((SELECT distinct ',' + QUOTENAME(errors.position) " _ 
    & "from t " _ 
    & "left join errors on errors.collection_id = t.collection_id And errors.nr = t.nr " _ 
    & "where t.Status = 4 And errors.Stamp > @fra And t.collection_name = ''' + @title + ''' and errors.collection_id is not null " _ 
    & "FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') , 1, 1, '') " _ 
    & " " _ 
    & "set @query = ';with cte as (select t.collection_name, errors.position, count(errors.Tryksag_Nedtagsfejl_Id) antal " _ 
    & "from t " _ 
    & "left join errors on errors.collection_id = s.collection_id And errors.nr = t.nr " _ 
    & "where t.Status = 4 And errors.Stamp > @fra And And t.collection_name = ''' + @title + ''' " _ 
    & "group by t.collection_name, errors.position) " _ 
    & "SELECT collection_name, ' + @cols + ' INTO #OUT from " _ 
    & "cte " _ 
    & "pivot " _ 
    & "(" _ 
    & "sum(antal) " _ 
    & "for position in (' + @cols + ') " _ 
    & ") p ' " _ 
    & "execute(@query) " 
dim stt3 as string = "select * from #OUT" 
Dim sqlConnection2 As New SqlConnection("Data Source=CONDOR-TI;Initial Catalog=Condor_db;Integrated Security=True") 
Dim cmd2 As New SqlCommand 
cmd2.CommandText = Stt2 
cmd2.CommandType = CommandType.Text 
cmd2.Connection = sqlConnection2 
Dim cmd3 As New SqlCommand 
Dim reader3 As SqlDataReader 
cmd3.CommandText = Stt3 
cmd3.CommandType = CommandType.Text 
cmd3.Connection = sqlConnection2 
sqlConnection2.Open() 
cmd2.ExecuteNonQuery() 
reader3 = cmd3.ExecuteReader() 
While reader3.Read 
    Console.Write(reader3(0)) 
End While 
Console.WriteLine() 
sqlConnection2.Close() 
reader3.Close()

以上是 如何获得从sql动态数据透视到vb 的全部内容， 来源链接： utcz.com/qa/262677.html

回到顶部