在c＃和sql中寻找需要多个字段的产品 [英] seeking a product where more than field is needed in c# and sql

问题描述

我有一个产品表，我正在根据产品名称，类型和尺寸寻找产品，如何为此编写SQL语句？

i have a table with products i am seeking a product according to product name, type and size, how to write a SQL statement for that?

try
          {

              string str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\WStoreSystem\\WStoreSystem\\WStoreSystem\\bin\\Debug\\StoreSys.mdb";
              OleDbConnection con = new OleDbConnection(str);
              con.Open();
              OleDbCommand comm = new OleDbCommand();
              comm.Connection = con;
              string qu = "SELECT StockID FROM Stock where ProName '" + comboBox1.Text + "'" || ProType like '" + comboBox2.Text + "'" || ProSize like '" + comboBox3.Text + "'";

             comm.CommandText = qu;
              OleDbDataReader reder = comm.ExecuteReader();
              while (reder.Read())
              {
                  txtStockID.Text =(reder["StockID"].ToString());
              }
              con.Close();
          }
          catch (Exception ex)
          {
              MessageBox.Show("Error " + ex);
          }
      }

推荐答案

您的代码容易受到 SQL注入 [ ^ ]。



从不使用字符串连接来构建SQL查询。 始终使用参数化查询。



而不是指定数据库文件的完整路径，请使用 | DataDirectory | 在您的连接字符串中。这将自动指向Windows应用程序的应用程序目录，以及Web应用程序的 App_Data 目录。



使用块在中包裹一次性物品，以确保始终清理其资源。



没有必要当你只读取一个值时，使用 ExecuteReader 。请改用 ExecuteScalar 。



SQL使用运算符 AND 和或，而不是C风格&& 和 || 。





Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Rather than specifying the full path to the database file, use |DataDirectory| in your connection string. This will automatically point to the application directory for a Windows application, and the App_Data directory for a web application.

Wrap disposable objects in a using block to ensure that their resources are always cleaned up.

There's no need to use ExecuteReader when you're only reading a single value. Use ExecuteScalar instead.

And SQL uses the operators AND and OR, not the C-style && and ||.

const string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|StoreSys.mdb";
const string Query = "SELECT TOP 1 StockID FROM Stock WHERE ProName = ? Or ProType = ? Or ProSize = ?";

using (var connection = new OleDbConnection(ConnectionString))
using (var command = new OleDbCommand(Query, connection))
{
    // OleDb doesn't use named parameters, so only the order matters here:
    command.Parameters.AddWithValue("p0", comboBox1.Text);
    command.Parameters.AddWithValue("p1", comboBox2.Text);
    command.Parameters.AddWithValue("p2", comboBox3.Text);

    connection.Open();
    txtStockID.Text = Convert.ToString(command.ExecuteScalar());
}

此外，帮自己一个忙，给你的控件有意义的名字。接受设计师指定的默认名称只会导致以后混淆。

---

编辑：

根据评论，听起来您希望匹配所选参数的所有，而不是匹配所选参数的任何。如果是这样，请将代码更改为：

Also, do yourself a favour and give your controls meaningful names. Accepting the default name assigned by the designer will only cause confusion later.

---

Based on the comments, it sounds like you want to match all of the selected parameters, rather than matching any of the selected parameters. If so, change the code to:

const string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|StoreSys.mdb";
const string Query = "SELECT TOP 1 StockID FROM Stock WHERE (ProName = ? Or ? = '') And (ProType = ? Or ? = '') And (ProSize = ? Or ? = '')";

using (var connection = new OleDbConnection(ConnectionString))
using (var command = new OleDbCommand(Query, connection))
{
    // OleDb doesn't use named parameters, so only the order matters here:
    command.Parameters.AddWithValue("p0", comboBox1.Text);
    command.Parameters.AddWithValue("p1", comboBox1.Text);
    command.Parameters.AddWithValue("p2", comboBox2.Text);
    command.Parameters.AddWithValue("p3", comboBox2.Text);
    command.Parameters.AddWithValue("p4", comboBox3.Text);
    command.Parameters.AddWithValue("p5", comboBox3.Text);

    connection.Open();
    txtStockID.Text = Convert.ToString(command.ExecuteScalar());
}

（你必须添加两个参数，因为OleDb没有使用命名参数。）

这篇关于在c＃和sql中寻找需要多个字段的产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！