如何使用SQL从C#中的两个表中检索多列

本文关键字：两个 检索 SQL 何使用 | 更新日期: 2023-09-27 18:25:39

我有两个表，需要根据公共键ProductID从这两个表中检索一些列。我尝试了以下操作，但出现了一个错误"；关键字"JOIN"附近的语法不正确；

string selectProductStatement = 
"SELECT Products.Code, Products.Description,"+
"Products.Category, Products.Price, BookProducts.Author"+
"FROM Products"+
"INNER JOIN BookProducts ON (Products.@ProductID = BookProducts.ProductID)";
SqlCommand comm = new SqlCommand(selectProductStatement, connection);
comm.Parameters.AddWithValue("@productID", productID);

表格是：

产品

  ProductID
  Code
  Description
  Category
  Price

BookProducts

  BookID PK
  ProductID FK
  Author

如何使用SQL从C#中的两个表中检索多列

您的SQL:中缺少空格

string selectProductStatement = 
    "SELECT Products.Code, Products.Description,"+
    "Products.Category, Products.Price, BookProducts.Author"+
    " FROM Products"+
    " INNER JOIN BookProducts ON (Products.@ProductID = BookProducts.ProductID)";

就我个人而言，我喜欢为SQL命令使用一个文本字符串来消除所有的连接。节省时间和头痛！

在定义字符串之前，请注意@符号。另外，用"using"大括号包装命令意味着你不能忘记处理它

 string selectProductStatement = @"           SELECT 
                                              Products.Code, 
                                              Products.Description,
                                              Products.Category, 
                                              Products.Price, 
                                              BookProducts.Author
                                              FROM Products
                                              INNER JOIN BookProducts 
                                              ON (Products.@ProductID = BookProducts.ProductID) ";
            string connectionString = string.Empty;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                using (SqlCommand sqlCommand = new SqlCommand(selectProductStatement, sqlConnection))
                {
                    sqlCommand.Parameters.AddWithValue("@productID", productID);
                    //Etc
                }
            }

Products和INNER之间没有空格。在查询的第三行FROM Products前后添加一个空格，您就可以了：-）

估计您只需要在"PRODUCTS"answers"INNER"之间有一个空格，即

"FROM Products "+
"INNER JOIN BookProducts ON (Products.@ProductID = BookProducts.ProductID)";

将字符串定义为字符串文字可以帮助解决错误的间距：

string selectProductStatement = 
 @"SELECT Products.Code, Products.Description, 
Products.Category, Products.Price,
 BookProducts.Author FROM Products 
INNER JOIN BookProducts ON (Products.@ProductID =
BookProducts.ProductID)";

它省略了多个字符串片段

的需要和相关问题/可读性

尝试以下

string selectProductStatement = 
"SELECT Products.Code, Products.Description,"+
"Products.Category, Products.Price, BookProducts.Author"+
" FROM Products "+
"INNER JOIN BookProducts ON (Products.ProductID = BookProducts.ProductID) WHERE Products.ProductId = @productID";

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

以上是内部联接查询的一般语法。只需相应地遵循即可。：）

相关文章：