C# - SqlDataReader missing columns

Question

我的记录如下表所示。sql查询

My SQL query is

select Category_NM as 'Kategoriler', avg(Payment) as 'Ortalamalar' 
from Islem 
where Category_Type = 'Gider' 
group by Category_NM 

和我的代码:

public SqlDataReader DataReader(string Query_)
{
SqlCommand cmd = new SqlCommand(Query_, con);
SqlDataReader dr = cmd.ExecuteReader();
return dr;
}

和

SqlDataReader dr = sınıf.DataReader("select Category_NM as 'Kategoriler',AVG(Payment) as 'Ortalamalar' from Islem where Category_Type = 'Gider' group by Category_NM");
dr.Read();
chart1.Series[0].IsValueShownAsLabel = true;
while (dr.Read())
{
// MessageBox.Show(Convert.ToString(dr[0]+""+dr[1]));
chart1.Series[0].Points.AddXY(dr[0],dr[1]);
}
dr.Close();

问题ıs阅读器不读取所有记录-像这样:

输入图片描述

Answer 1

因为你第一次调用dr.Read()，然后迭代它们。

更正:

SqlDataReader dr = sınıf.DataReader("select Category_NM as 'Kategoriler',AVG(Payment) as 'Ortalamalar' from Islem where Category_Type = 'Gider' group by Category_NM");
chart1.Series[0].IsValueShownAsLabel = true;
while (dr.Read())
{
// MessageBox.Show(Convert.ToString(dr[0]+""+dr[1]));
chart1.Series[0].Points.AddXY(dr[0], dr[1]);
}
dr.Close();

每个Read()向前移动行指针。所以第一个Read()跳过第一个记录。

如果您需要检查是否有任何记录，然后chart1.Series[0].IsValueShownAsLabel = true;，使用if (dr.HasRows)就足够了，如:

if(dr.HasRows)
{
chart1.Series[0].IsValueShownAsLabel = true;
//Iterate dr like while(dr.Read()){...}
}

但是你必须知道你的while块逻辑总是在位置0读取记录，并在col0和col1获取值，如果你需要读取所有记录，你可以通过foreach或for进行迭代，如:

foreach (DbDataRecord s in dr)
{
string kategoriler= s.GetString(0);
string ortalamalar = s.GetString(1);
}

或while:

while(dr.Read())
{
var kat = dr["Kategoriler"].ToString();
var ort = dr["Ortalamalar"].ToString();
}