如何让存储过程返回“数据集"?使用我传递的参数?

问题描述：

我对存储过程很陌生.

假设我有一个 IDcategory (int)，我将它传递给一个存储过程.在正常谈话中，它会去:

Say I have a IDCategory (int) and I pass that to a stored procedure. In normal-talk, it'd go:

找到我所有带有IDcategory 等于我的 IDcategory告诉你去找.

Find me all the listings with an IDCategory equal to the IDCategory I'm telling you to find.

所以它会找到 3 个列表，并创建一个包含列的表:

So it would find say 3 listing, and create a table with columns:

IDListing、IDcategory、价格、卖家、图片.

IDListing, IDCategory, Price, Seller, Image.

我怎样才能做到这一点?

How could I achieve this?

答

要从存储过程填充数据集，您需要使用如下代码:

To fill a dataset from a stored procedure you would have code like below:

SqlConnection mySqlConnection =new SqlConnection("server=(local);database=MyDatabase;Integrated Security=SSPI;");

    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    mySqlCommand.CommandText = "IDCategory";
    mySqlCommand.CommandType = CommandType.StoredProcedure;
    mySqlCommand.Parameters.Add("@IDCategory", SqlDbType.Int).Value = 5;

    SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
    mySqlDataAdapter.SelectCommand = mySqlCommand;
    DataSet myDataSet = new DataSet();
    mySqlConnection.Open();
    mySqlDataAdapter.Fill(myDataSet);

您的连接字符串会有所不同，有几种不同的方法可以做到这一点，但这应该能让您继续前进……一旦您掌握了其中的一些，请查看 Using 语句.它有助于清理资源并需要更少的代码行.这假定存储过程名称 IDcategory 具有一个相同的参数.您的设置可能略有不同.

Your connection string will be different and there are a few different ways to do this but this should get you going.... Once you get a few of these under your belt take a look at the Using Statement. It helps clean up the resources and requires a few less lines of code. This assumes a Stored Procedure name IDCategory with one Parameter called the same. It may be a little different in your setup.

在这种情况下，您的存储过程将类似于:

Your stored procedure in this case will look something like:

CREATE PROC [dbo].[IDCategory] 
    @IDCategory int
AS 
    SELECT IDListing, IDCategory, Price, Seller, Image
         FROM whateveryourtableisnamed
         WHERE IDCategory = @IDCategory

这是有关存储过程基础知识的链接:http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx

Here's a link on Stored Procedure basics: http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx

这是有关 ADO.Net 的数据集和其他项目的链接:http://authors.aspalliance.com/quickstart/howto/doc/adoplus/adoplusoverview.aspx

Here's a link on DataSets and other items with ADO.Net: http://authors.aspalliance.com/quickstart/howto/doc/adoplus/adoplusoverview.aspx

如何让存储过程返回“数据集"?使用我传递的参数?

相关推荐