向SQL语句添加参数 [英] Adding parameters to a SQL statement

问题描述

您好，我是C#和SQL的新手.我有一个小的例程，该例程从文本文件导入发票，检查字段的大小，然后将其加载到表中.一切都很好，尽管我想象看起来很笨拙.我现在正在尝试向代码中添加参数，但现在我迷路了.有人可以告诉我如何使用现有代码执行此操作，也可以使我完全走下一条更好的道路.这也是我的第一个帮助请求，因此，对于构架问题的建议也将不胜感激.

这是存储过程:

Hello, I am very new to C# and SQL. I have a small routine that imports an invoice from a text file, checks the size of the fields and then loads it to a table. This is all working fine, though I imagine looks very clumsy. I am now trying to add parameters to the code and I am now lost. Can someone please show me how to do this with the existing code or alternately send me down a better path altogether. This is also my first request for help, so advice on framing questions will also be appreciated.

Here is the stored Procedure:

ALTER PROCEDURE spSelectInvoice
@supplierID int

AS
    SELECT * FROM tblSupplierInvoices WHERE fldSupplierID = @SupplierID

这就是我要使其正常工作的部分，但是在各个论坛上追问了多个问题之后，我现在迷路了.在da.fill(ds，"tblSupplierInvoices")处，我如何或在哪里声明@supplierID?出现以下错误

And this the part where I''m trying to get it to work, but after following multiple questions on various forums, I am now lost. How or where do I declare @supplierID as this fall over at da.fill(ds, "tblSupplierInvoices"); with the following error

Procedure or function ''spSelectInvoice'' expects parameter ''@supplierID'', which was not supplied.

// sqlConnection is declared on the form and is correct.

myConnection = sqlConnection;
string connectionString = myConnection.ConnectionString;
string commandString = "EXECUTE spSelectInvoice";

SqlDataAdapter da = new SqlDataAdapter(commandString, connectionString);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds, "tblSupplierInvoices");

DataTable dt = ds.Tables["tblSupplierInvoices"];
da.FillSchema(dt, SchemaType.Source);

推荐答案

尝试以下操作:

myConnection = sqlConnection;
string connectionString = myConnection.ConnectionString;
string commandString = "spSelectInvoice";
SqlDataAdapter da = new SqlDataAdapter(commandString, connectionString);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.AddWithValue("@supplierID", myIdToSelect);
...

这篇关于向SQL语句添加参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！