VS中的SQL行计数 [英] SQL Row Count in VS

问题描述

如何按顺序1,2,3,4获取行计数查询计数1111我尝试了几种不同的方法

 using（var cmd = new SqlCommand（" SELECT ItemCode，ItemName FROM Table1 where ItemCode = @ egg" con））
 
 using（var cmd = new SqlCommand（" SELECT ROW_NUMBER（）Over （按ItemCode排序）为Number，ItemCode FROM Table1，其中ItemCode = @ egg"，con））
使用（var cmd = new SqlCommand（" SELECT Count（*）Over（PARTITION by ItemCode）as Number，ItemCode FROM Table1，其中ItemCode = @ egg"，con））
使用（var cmd = new SqlCommand（" SELECT Top 8 ROW_NUMBER（）Over（Order by ItemCode）作为Number，ItemCode FROM Table1，其中ItemCode = @ egg" ，con））
 
数字
 1 EG216455 
 1 nEG216456 
 1 nEG216457 
 1 nEG216458 
 
数字
 1 EG216455 
 2 nEG216456 
 3 nEG216457 
 4 nEG216458 

以下是代码，您可以看到查询

 private void button1_Click（object sender，EventArgs e）
 {
 string egg = textBox1.Text; // egg将显示"EG216455 \\ nnEG216456 \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ 
 int k = Convert.ToInt32（textBox2.Text）; 
 string [] stringSeparators = new string []; 
 string [] s = egg.Split（stringSeparators，StringSplitOptions.None）; 
 
 
 DataTable dtSerial = new DataTable（）; 
 DataTable table = new DataTable（）; 
 
 for（int i = 0; i< k; i ++）
 {
 //ConfigurationManager.ConnectionStrings["pie"].ConnectionString; 
 string connString = @" Data Source =（LocalDB）\ MSSQLLocalDB; AttachDbFilename = Database.mdf; Integrated Security = True" ;; 
 
使用（SqlConnection con = new SqlConnection（connString））
 {
 using（var cmd = new SqlCommand（" SELECT ItemCode，ItemName FROM Table1 where ItemCode = @ egg" ，con））
 cmd.Parameters.AddWithValue（" @ egg"，s [i]）; 
 con.Open（）; 
 SqlDataReader reader = cmd.ExecuteReader（）; 
 dtSerial.Load（读者）; 
 table = dtSerial.Clone（）; 
 foreach（dtSerial.Rows中的DataRow dr）
 {
 
 table.Rows.Add（dr.ItemArray）; 
} 
} 
 con.Close（）; 
} 
} 
 
 
 dataGridView1.DataSource = dtSerial; 
} 

解决方案

这些代码与您放在第一个代码块中的内容有什么关系？您没有在发布的查询中使用ROW_NUMBER（），因此SQL不会返回它。当然，你按照它们从查询中返回的顺序将值添加到表中，所以
你真的不需要行号吗？ 

我注意到您正在使用ExecuteReader读取数据，然后您尝试将其转换为数据表。这实际上没有必要。如果要使用数据表，请使用FillDataSet并忘记ExecuteReader。这是更少的代码，已经
完全符合您的要求。另请注意，您似乎出于某种原因创建了多个数据表。你不需要做任何这些。

 con.Open（）; 
 
使用（var cmd = new SqlCommand（" ..."，con））
 {
 var param = cmd.Parameters.AddWithVith（" @ egg" ，""; 
 
 var da = new SqlDataAdapter（cmd）; 
 
 for（var i = 0; i< k; i ++）
 {
 param.Value = s [i]; 
 
 var ds = new DataSet（）; 
 da.FillDataSet（ds）; 
 
 dataGridView1.DataSource = ds; 
}; 
}; 

最后请注意，您使用for循环执行此操作k次。但是每次通过你都会踩踏前面的迭代值，因为你将网格上的DataSource分配给当前的迭代结果。

How do I get the query to row count in order 1,2,3,4 it is counting 1111 i have try a few different ways

using (var cmd = new SqlCommand("SELECT ItemCode,ItemName FROM Table1  where ItemCode = @egg", con))

 using (var cmd = new SqlCommand("SELECT ROW_NUMBER() Over (order by ItemCode)as Number, ItemCode FROM Table1  where ItemCode = @egg", con))
 using (var cmd = new SqlCommand("SELECT Count(*) Over(PARTITION by ItemCode)as Number, ItemCode FROM Table1  where ItemCode = @egg", con))
 using (var cmd = new SqlCommand("SELECT Top 8 ROW_NUMBER() Over (Order by ItemCode) as Number, ItemCode FROM Table1  where ItemCode = @egg", con))

Number
1     EG216455
1     nEG216456
1     nEG216457
1     nEG216458

Number
1     EG216455
2     nEG216456
3     nEG216457
4     nEG216458

Here is the code and you can see the query

private void button1_Click(object sender, EventArgs e)
        {
            string egg = textBox1.Text; //egg will show "EG216455\r\nEG216456\r\nEG216457\r\nEG216458\r\nEG216459\r\nEG216460"	
            int k = Convert.ToInt32(textBox2.Text);  
            string[] stringSeparators = new string[];
            string[] s = egg.Split(stringSeparators, StringSplitOptions.None);


            DataTable dtSerial = new DataTable();
            DataTable table = new DataTable();

            for (int i = 0; i < k; i++)
            {
                                   //ConfigurationManager.ConnectionStrings["pie"].ConnectionString;
                string connString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=Database.mdf;Integrated Security=True";

                using (SqlConnection con = new SqlConnection(connString))
                {
                    using (var cmd = new SqlCommand("SELECT ItemCode,ItemName FROM Table1  where ItemCode = @egg", con))
                        cmd.Parameters.AddWithValue("@egg", s[i]); 
                        con.Open();
                        SqlDataReader reader = cmd.ExecuteReader();
                        dtSerial.Load(reader);
                        table = dtSerial.Clone();
                        foreach (DataRow dr in dtSerial.Rows)
                        {

                            table.Rows.Add(dr.ItemArray);
                        }
                    }
                    con.Close();
                }
            }


            dataGridView1.DataSource = dtSerial;
        }

解决方案

How does that code relate to the stuff you put in your first code block? You aren't using ROW_NUMBER() in the query you posted so SQL won't return it. Of course you're adding the values into the table in the order they come back from the query so you don't really need a row number do you? 

I notice that you're using ExecuteReader to read the data and then you're trying to convert it to a datatable. This is really not necessary. If you want to use a datatable then use FillDataSet and forget ExecuteReader. It is less code and already does exactly what you want. Also note that you seem to be creating multiple data tables for some reason. You don't need to do any of this.

con.Open();

using (var cmd = new SqlCommand("...", con))
{
   var param = cmd.Parameters.AddWithVith("@egg", "");

   var da = new SqlDataAdapter(cmd);

   for (var i = 0; i < k; i++)
   {
      param.Value = s[i];

      var ds = new DataSet();
      da.FillDataSet(ds);

      dataGridView1.DataSource = ds;      
   };   
};

Finally note that you're using a for loop to do this k times. But each time through you are stomping over the previous iterations values because you're assigning DataSource on your grid to the current iterations results.

这篇关于VS中的SQL行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！