c# - 如何在 t-sql 中使用 where 子句计算过滤后的行数？

Question

给出下表。我需要使用事务 SQL 计算在 SubCategory 列中具有值的每条记录的行数作为“Children's Books”。在这种情况下，我期望结果为 2。我尝试过类似的方法

SqlCommand cmd = new SqlCommand("Select Count(*) From [DisplayCenterTab] Where [SubCategory ]= '" + subcateg + "' ", con);

但它返回零 (0) 结果而不是 2。我在这里缺少什么？谢谢

我的表:

Id        ProductId       ProductName      Category            SubCategory
34534     34643645        dfhfsjfdjgh      sdfagdsfhfhgfhj     dfgsdhhgfh
45234     456436          fghdfjfgj        dfgsdhfhfgfgh       Children's Books
46536     45646           fgjdgjfgh        dfgshfgfdghj        Children's Books
43645     466456          systyerttry      sdhdfhfggjh         dfhshfdjgfgh
34526     456345          areyruuty        dfshfdfgjghj        dafgshfghgfh

更新

JQuery 代码

$("#sellstuff").click(function () {
    var dataObject = $("#scrolldummy").text();
    //var dataObject = "Children's Books";
    $.getJSON("/OnlineStore/TotalNumberofSubCateg", dataObject, function (data) {
        $("#search").val(data + " Assorted Items for Sale.");
    });
})

C#代码

[HttpGet]
        public JsonResult TotalNumberofSubCateg(string subcateg)
        {
            int rowcount;
            string constr = ConfigurationManager.ConnectionStrings["StockConnString"].ConnectionString;
            SqlConnection con = new SqlConnection(constr);
            con.Open();

            SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM DisplayCenterTab Where SubCategory = '"+ subcateg +"' ", con);

            cmd.CommandType = CommandType.Text;
            rowcount = Convert.ToInt32(cmd.ExecuteScalar());
            con.Close();
            return Json(rowcount, JsonRequestBehavior.AllowGet);
        }

Answer 1

您需要从 subcateg 变量中转义单引号字符。试试这个

    SqlCommand cmd = 
new SqlCommand("Select Count(*) From [DisplayCenterTab] Where [SubCategory ]= '" + subcateg.Replace("'","''") + "' ", con);