如何使用键值对列表查询数据库 [英] How to query the database with a list of key-value pairs

问题描述

假设我有一个包含3列的表格：Id，Category，Name。

我想查询表格：

{Category =Cat1AND Name =ABC}的行或 {Category =Cat2AND Name = ABC} 或 {Category =Cat2AND Name =DEF}

如何？无需使用大量的 WHERE或
列表我正在考虑使用 IN ...

解决方案

div>

您可以创建一个临时表

  create table #temp（category varchar（50），name varchar ））
 insert into #temp values（'Cat1'，'abc'），（'Cat2'，'cde'），（'Cat3'，'eee'）
  

然后加入您的主表格

  select * from table1 
 inner join #temp 
 on table1.Category =＃temp.Category and table1.Name =＃temp.Name 
  

---

如果你想从代码中使用这种方法，你可以使用表参数。

定义表类型：

  CREATE TYPE dbo.ParamTable AS TABLE 
 （50），Name varchar（50））
  

和一个将读取数据的存储过程： / p>

  create procedure GetData（@param dbo.ParamTable READONLY）
 AS 
 select * from table1 
 inner join @param p 
 on table1.Category = p.Category and table1.Name = p.Name 
  

然后你可以使用那些从C＃代码，例如：

  using（var conn = new SqlConnection Data Source = localhost; Initial Catalog = Test2; Integrated Security = True））
 {
 conn.Open（）; 
 
 DataTable param = new DataTable（）; 
 param.Columns.Add（new DataColumn（Category，Type.GetType（System.String）））; 
 param.Columns.Add（new DataColumn（Name，Type.GetType（System.String）））; 
 param.Rows.Add（new object [] {Cat1，abc}）; 
 
 using（var command = conn.CreateCommand（））
 {
 command.CommandText =GetData; 
 command.CommandType = CommandType.StoredProcedure; 
 command.Parameters.AddWithValue（@ param，param）; 
 
 using（var reader = command.ExecuteReader（））
 {
 //阅读
} 
} 
 
 } 
  

Say I have the a table with 3 columns : Id, Category, Name.

I would like to query the table that way : Get me the rows for which { Category = "Cat1" AND Name = "ABC" } OR { Category = "Cat2" AND Name = "ABC" } OR { Category = "Cat2" AND Name = "DEF" }

How? Without having to resort to a huge list of WHERE OR I was thinking of using IN...but is it possible to use that in conjunction with 2 columns?

Thanks!

解决方案

You can create a temp table

create table #temp (Category varchar(50), Name varchar(50))
insert into #temp values ('Cat1', 'abc'), ('Cat2', 'cde'), ('Cat3', 'eee')

And then join your main table

select * from table1
inner join #temp
on table1.Category = #temp.Category and table1.Name = #temp.Name 

---

If you want to use that approach from the code, you can do that using table parameters.

Define a table type:

CREATE TYPE dbo.ParamTable AS TABLE
    ( Category varchar(50), Name varchar(50) )

and a stored proc that will read the data:

create procedure GetData(@param dbo.ParamTable READONLY)
AS
    select * from table1
    inner join @param p
    on table1.Category = p.Category and table1.Name = p.Name

Then you can use those from the C# code, for example:

using (var conn = new SqlConnection("Data Source=localhost;Initial Catalog=Test2;Integrated Security=True"))
{
    conn.Open();

    DataTable param = new DataTable();
    param.Columns.Add(new DataColumn("Category", Type.GetType("System.String")));
    param.Columns.Add(new DataColumn("Name", Type.GetType("System.String")));
    param.Rows.Add(new object[] { "Cat1", "abc" });

    using (var command = conn.CreateCommand())
    {
        command.CommandText = "GetData";
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddWithValue("@param", param);

        using (var reader = command.ExecuteReader())
        {
            // reading here
        }
    }

}

这篇关于如何使用键值对列表查询数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！