如何使用asp.net在SQL Server外键中获取数据？ [英] How to get data in a SQL Server foreign key using asp.net?

问题描述

这个问题与我以前的问题有些相似，与之前的问题相关：如何使用asp.net实体框架获取sql外键中的数据？，所以我将使用相同的SQL Server表。

这一次，而不是通过Entity Framework访问外键值，我想知道如何在这样的常规SQL脚本中执行此操作（请注意，脚本我在说的是在 string.Format 中）：

  public static void getRowNumber（string brand）
 {
 string query = string.Format（SELECT COUNT（*）FROM stringInstrumentItem WHERE brandId LIKE @brand）; 
 
 conn1.Open（）; 
 
 command1.CommandText = query; 
 command1.Parameters.Add（new SqlParameter（brand，brand））; 
 
 Int32 count =（Int32）command1.ExecuteScalar（）; 
 
 conn1.Close（）; 
 
 command1.Parameters.Clear（）; 
 
 AddASPXAndCSFile.X = count; 
} 
  

只是总结一下，我有两个SQL Server表称为 stringInstrumentItem 和品牌。我在表品牌的主键表格 stringInstrumentItem 中创建了外键。所以我试图通过 stringInstrumentItem 品牌中的名称 C $ C>。

如您所见，我试图通过品牌中的值> brandId ，这不行。

希望你们能够帮助我。干杯!

解决方案

如果我正确理解您的问题，似乎是一个简单的查询 -

  SELECT COUNT（*）FROM stringInstrumentItem 
 WHERE brandId IN（SELECT brandId FROM brand WHERE name LIKE @brand）
  / pre> 
 
 
请使用你有 @ 和％ （根据您的要求） 
  command1.Parameters.Add（new SqlParameter品牌，％+品牌+％））; 
  
 
This question is somewhat similar and related to my previous question How to get data in an sql foreign key using asp.net entity framework? so I will be using the same SQL Server tables. 

This time, instead of accessing the foreign key value through Entity Framework, I want to know how to do it in a regular SQL script like this (take note, the script that I'm talking about is inside the string.Format):
public static void getRowNumber(string brand)
{
    string query = string.Format("SELECT COUNT(*) FROM stringInstrumentItem WHERE brandId LIKE @brand");

    conn1.Open();

    command1.CommandText = query;
    command1.Parameters.Add(new SqlParameter("brand", brand));

    Int32 count = (Int32)command1.ExecuteScalar();

    conn1.Close();

    command1.Parameters.Clear();

    AddASPXAndCSFile.X = count;
}
Just to recap, I have two SQL Server tables called stringInstrumentItem and brand. I created the foreign key in the table stringInstrumentItem referring to the primary key of the table brand. So I'm attempting to access the column name in table brand through stringInstrumentItem. 

As you can see, I'm attempting to access the value in table brand through brandId, which does not work. 

Hope you guys can help me on this one. Cheers!  
解决方案
If I understand your question correctly, it seems like a simple query -
SELECT COUNT(*) FROM stringInstrumentItem 
WHERE brandId IN (SELECT brandId FROM brand WHERE name LIKE @brand)
Please make use you have @ and % (based on your requirement).
command1.Parameters.Add(new SqlParameter("@brand", "%" + brand + "%"));


                        
这篇关于如何使用asp.net在SQL Server外键中获取数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！