从c#对象动态构建SQL WHERE子句 [英] Dynamically build a SQL WHERE clause from c# Objects

问题描述

我正在使用HTTP POST请求并使用此 jQuery querybuilder 创建过滤器.

I am creating filters in my HTML table binded with asp.net web API using HTTP POST request and using this jquery querybuilder to create filters.

{
 "condition": "AND",
 "rules": [
   {
     "id": "price",
     "field": "price",
     "type": "double",
     "input": "text",
     "operator": "less",
     "value": "10.25"
   },
   {
     "condition": "OR",
     "rules": [
       {
         "id": "category",
         "field": "category",
         "type": "integer",
         "input": "select",
         "operator": "equal",
         "value": "2"
       },
       {
         "id": "category",
         "field": "category",
         "type": "integer",
         "input": "select",
         "operator": "equal",
         "value": "1"
       }]
   }]
}

我可以使用C#或任何其他可以接收HTTP post对象并在SQL中过滤数据的nuget包在任何条件下转换JSON对象吗?

Is there any way I can convert this JSON object in where conditions using C# or any other nuget package which can receive the HTTP post objects and filter the data in SQL?

C#

public Products GetProductById(object filterObj)  
 {  
     SqlDataReader reader = null;  
     SqlConnection myConnection = new SqlConnection();  
     myConnection.ConnectionString = @"Server=.\SQLSERVER2008R2;Database=DBCompany;User ID=sa;Password=xyz@1234;";  

     SqlCommand sqlCmd = new SqlCommand();  
     sqlCmd.CommandType = CommandType.Text;  
     sqlCmd.CommandText = "SELECT * FROM MYTABLE WHERE PRICE < 10.25 AND (CATEGORY = 2 OR CATEGORY = 1);  
     sqlCmd.Connection = myConnection;  
     myConnection.Open();  
     reader = sqlCmd.ExecuteReader();  
     Products prod = null;  
     while (reader.Read())  
     {  
         prod = new Products ();  
         prod.Id = Convert.ToInt32(reader.GetValue(0));  
         prod.Name = reader.GetValue(1).ToString();  
         prod.CategoryId = Convert.ToInt32(reader.GetValue(2));  
     }  
     return prod ;  

 }  

推荐答案

您可以使用动态一些示例:我们正在从jquery querybuilder传递规则，而不是Build查询并从DB检索相关数据

Some example: We are passing rules from jquery querybuilder, than Build query and retrieve relevant data from DB

    [HttpPost]
    public ActionResult Applay(FilterRule obj)
    {
        var messages = context.messages.BuildQuery(obj).ToList();
        return JsonContent(messages);
    }

更新:

obj已经使用过滤规则反序列化了json(来自jquery querybuilder) context.messages-是Entity Framework上下文的一部分

obj is already deserialized json with filter rules (from jquery querybuilder) context.messages - is part of Entity Framework context

(DbSet<Message> Messages { get; set; })

在这种方法中，您将通过linq构建sql查询，而EF会将其转换为SQL.如果您使用的是ADO.NET，则可以尝试在git hib项目页面上找到相关的示例.

In this approach you are building sql query by means of linq and EF translates it to SQL. If you are using ADO.NET than you can try to find relevant example on git hib project page.

这篇关于从c#对象动态构建SQL WHERE子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！