C# sqlcommand與SSMS取得資料不同

c# sql ssms

Juro十六 2023-02-08 13:28:04 ‧ 1102 瀏覽

分享至

想請問為何我得出的資料差這麼多，確認SQL command都是一樣的，可是得到的結果不一樣

//SSMS
SELECT id, username, postdate, views FROM Book_table
//查詢結果
//id(int),username(nvarchat),postdate(datetime),views(int)
//1,Juro,2023-02-06 00:00:00.000,125
//2,Juro,2023-02-06 00:00:00.000,125
//3,Juro,2023-03-06 00:00:00.000,125
//4,Juro,2023-03-06 00:00:00.000,125
SELECT username, SUM(views) as views FROM Book_table WHERE datepart(month, postdate) = 2 AND datepart(year, postdate) = 2023 GROUP BY username order by views desc
//查詢結果
//username(nvarchat),views(int)
//Juro,250

//C#
public List<Book_top> GetBook_Top()
{
    List<Book_top> BItems = new List<Book_top>();
    SqlConnection sqlConnection = new SqlConnection(Resources.SqlConnectionString);
    SqlCommand sqlCommand = new SqlCommand($"SELECT username, SUM(views) as views FROM Book_table WHERE datepart(month, postdate) = {DateTime.Now.Month} AND datepart(year, postdate) = {DateTime.Now.Year} GROUP BY username order by views desc");
    sqlCommand.Connection = sqlConnection;
    sqlConnection.Open();
    SqlDataReader reader = sqlCommand.ExecuteReader();
    if (reader.HasRows)
    {
        while (reader.Read())
        {
            Book_top list = new Book_top
            {
                username = reader.GetString(reader.GetOrdinal("username")),
                views = reader.GetInt32(reader.GetOrdinal("views"))
            };
            BItems.Add(list);
        }
    }
    sqlConnection.Close();
    return BItems;
}
//查詢結果
[0]=Juro125
[1]=Juro125
[2]=Juro125
[3]=Juro125

alien663 iT邦研究生 3 級 ‧ 2023-02-08 16:48:09 檢舉

我直接複製你的程式碼去跑，完全沒有你說的問題，跟SSMS上查詢的的結果一模一樣。

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

實習工程師

iT邦新手 1 級 ‧ 2023-02-08 15:21:49

SSMS

//SSMS
SELECT 
    id, username, postdate, views 
FROM Book_table


SELECT 
	username, SUM(views) as views 
FROM table_name 
WHERE datepart(month, postdate) = 2 AND datepart(year, postdate) = 2023 
GROUP BY username order by views desc

請問是 table_name ? 還是 Book_table ?

回應 5
分享
檢舉

看更多先前的回應...收起先前的回應...

Juro十六 iT邦新手 3 級 ‧ 2023-02-08 16:33:56 檢舉

都是Book_table，但那個不影響問題我修改一下

實習工程師 iT邦新手 1 級 ‧ 2023-02-08 16:58:51 檢舉

看了一下，也沒問題...

實習工程師 iT邦新手 1 級 ‧ 2023-02-08 17:11:08 檢舉

while (reader.Read())
{
Response.Write(reader.GetString(reader.GetOrdinal("username")));
}

用這個語法看看，是真的會印出四次嗎?

實習工程師 iT邦新手 1 級 ‧ 2023-02-08 17:18:34 檢舉

SELECT id, username, postdate, views FROM Book_table

或是將語法換成這個，在while 內 Response.Write(id); 看看。

實習工程師 iT邦新手 1 級 ‧ 2023-02-08 17:19:28 檢舉

如果印出都沒問題的話，就跟 SqlCommand 沒問題了。

登入發表回應

JamesDoge

iT邦高手 1 級 ‧ 2023-02-09 08:31:26

提供一個DEBUG的方法
您可以將C#程式碼中的SqlCommand語句內容印出來
等你印出來後....可以貼到SSMS執行看看結果
您可以對比一下到底你的SQL語句和您執行的SQL語句是否相同，以確定是否存在任何差異。

public List<Book_top> GetBook_Top()
{
    List<Book_top> BItems = new List<Book_top>();
    SqlConnection sqlConnection = new SqlConnection(Resources.SqlConnectionString);
    SqlCommand sqlCommand = new SqlCommand($"SELECT username, SUM(views) as views FROM Book_table WHERE datepart(month, postdate) = {DateTime.Now.Month} AND datepart(year, postdate) = {DateTime.Now.Year} GROUP BY username order by views desc");
    sqlCommand.Connection = sqlConnection;

    //印出來
    Console.WriteLine("SQL Command: " + sqlCommand.CommandText);

    sqlConnection.Open();
    SqlDataReader reader = sqlCommand.ExecuteReader();
    if (reader.HasRows)
    {
        while (reader.Read())
        {
            Book_top list = new Book_top
            {
                username = reader.GetString(reader.GetOrdinal("username")),
                views = reader.GetInt32(reader.GetOrdinal("views"))
            };
            BItems.Add(list);
        }
    }
    sqlConnection.Close();
    return BItems;
}