从C＃中的SQL语句读取器获取列名和值 [英] Get column name and value from SQL statement reader in C#

问题描述

伙计们，



我有一个从多表中计数的选择子查询。我将每个总数定义为名称。如何获取每个列名称及其值？我是否必须手动指定计数中的每个名称？

私函数ShowTotalCount（）
 Dim rdr As SqlDataReader 
 Dim cmd As SqlCommand = Nothing 
 Dim totcount As Integer =  0  
 Dim CommandText As  String  =   SELECT 
（SELECT COUNT（*）FROM [dbo]。[MyDownTime] ）作为count_mydowntime，
（SELECT COUNT（*）FROM [dbo]。[MyDownTime]）作为count_mydowntime，
（SELECT COUNT（*）FROM [dbo]。[MyOutput]）as count_myoutput，
（SELECT COUNT（*）FROM [dbo]。[MySpeed]）as count_myspeed，
（SELECT COUNT（*）FROM [dbo]。[MyTemperature]）as count_mytemperature，
（SELECT COUNT（ *）FROM [dbo]。[MyUtility]）as count_myutility 
 objlocalconn.Open（）
 cmd = New SqlCommand（CommandText，objlocalconn）
 cmd.CommandType = CommandType。文字
 rdr = cmd.ExecuteReader（）
如果rdr.HasRows那么
 Do while rdr.Read（）
 Console.WriteLine（  {0}  -  {1}，rdr [  count_mydowntime]，Convert.ToInt32（rdr.GetValue（ 0 ）））
循环
结束如果
结束功能

我发现大多只是从返回的单个数字传递值。但是，如果查询具有AS，如

（选择count（*）AS viewthis1），请选择count（*）AS viewthis2）

我希望它在哪里阅读在viewthis1的SQLDataReader和viewthis2

解决方案

在While循环中尝试此操作，迭代每行的每一列，无论列名如何。如果您只需要访问某些列，请提供列名而不是其索引：

 对于 i =  0    rdr.FieldCount  -   1  
 Console.WriteLine（rdr.GetName（i），dr（i）））
 下一页 

Guys,

I have a select subqueries which getting count from multi table. I define every total as something name. How do I get every column name and its value? Do I have to manual specify every name from the count?

Private Function ShowTotalCount()
    Dim rdr As SqlDataReader
    Dim cmd As SqlCommand = Nothing
    Dim totcount As Integer = 0
    Dim CommandText As String = "SELECT
    (SELECT COUNT(*) FROM [dbo].[MyDownTime]) as count_mydowntime,
    (SELECT COUNT(*) FROM [dbo].[MyDownTime]) as count_mydowntime,
    (SELECT COUNT(*) FROM [dbo].[MyOutput]) as count_myoutput,
    (SELECT COUNT(*) FROM [dbo].[MySpeed]) as count_myspeed,
    (SELECT COUNT(*) FROM [dbo].[MyTemperature]) as count_mytemperature,
    (SELECT COUNT(*) FROM [dbo].[MyUtility]) as count_myutility"
        objlocalconn.Open()
        cmd = New SqlCommand(CommandText, objlocalconn)
        cmd.CommandType = CommandType.Text
        rdr = cmd.ExecuteReader()
        If rdr.HasRows Then
            Do While rdr.Read()
                Console.WriteLine("{0} - {1}", rdr["count_mydowntime"], Convert.ToInt32(rdr.GetValue(0)))
    Loop
        End If
End Function

I found mostly just pass the value as from single number returned. But how about if the queries have "AS" like

(select count(*) AS viewthis1),select count(*) AS viewthis2)

where I want to it read in SQLDataReader of viewthis1 and viewthis2

解决方案

Try this inside your While loop to iterate over each column of each row regardless of the column name. If you need to access only certain columns, provide the column name instead of its index:

For i = 0 To rdr.FieldCount - 1
    Console.WriteLine(rdr.GetName(i), dr(i)))
Next

这篇关于从C＃中的SQL语句读取器获取列名和值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！