如何将参数传递给sql程序 [英] how to pass parameter to sql procedure

问题描述

你好



i需要一些帮助，将List值（或者我相信它的List :)传递给我在sql server中的sql过程的参数值



试图解释：



我有一个名为sampleID的datagridview，我有10行数据datagridview和sampleID我有单个数字值

hello

i need some help with passing List values (or so I believe it's List :) ) to parameter value of sql procedure I have in sql server

trying to explain:

I have datagridview with one column called sampleID, I have 10 rows of data in datagridview and in sampleID i have single values of numbers

1
3
3
3
5
5
7
7
9

我有textBox1用户登录windows form保存允许他在表单中看到的数字，让我们说我只能看到包含3和7

的行所以在textBox1中我把它分成这样



，3，，7，



现在我的sql程序很简单

I have textBox1 where user which is logged on windows form holds numbers which he is allowed to see in the form, and let's say that I can only see rows that holds 3 and 7
so in textBox1 I divided it like this

,3,,7,

now my sql procedure is simple

USE [FreeDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[sample_proc]

@PageSize int output, 
@PageNum int output,
@sampleID varchar(MAX) output

as

Declare @RowStart int 
Declare @RowEnd int 

--SELECT	@PageSize = 50,
	--@PageNumber = 1
	
if @PageNum > 0 
Begin 

SET @PageNum = @PageNum -1 

SET @RowStart = @PageSize * @PageNum + 1; 
SET @RowEnd = @RowStart + @PageSize - 1 ; 

With Cust AS 
     ( SELECT *, 
       ROW_NUMBER() OVER (order by ID DESC) as RowNumber 
       FROM Sample WHERE sampleID = @sampleID )

select * 
from Cust 
Where RowNumber >= @RowStart and RowNumber <= @RowEnd end

和在我的windows窗体中，我有datagridview

and in my windows form where datagridview is I have

page.Text = "1";
                size.Text = "25";

                SqlCommand command = new SqlCommand("sample_proc", cs);
                command.CommandType = CommandType.StoredProcedure;

                SqlParameter param = new SqlParameter("@PageNum", SqlDbType.Int);
                SqlParameter param1 = new SqlParameter("@PageSize", SqlDbType.Int);
                SqlParameter param2 = new SqlParameter("@sampleID", SqlDbType.VarChar, -1);

                param.Direction = ParameterDirection.InputOutput;
                param1.Direction = ParameterDirection.InputOutput;
                param2.Direction = ParameterDirection.InputOutput;

                param.Value = page.Text;
                param1.Value = size.Text;

                string[] sm1 = textBox1.Text.Split(',');

                foreach (string value in sm1)
                {                    
                    param2.Value = value;
                }
                
                command.Parameters.Add(param);
                command.Parameters.Add(param1);
                command.Parameters.Add(param2);

                daSample.SelectCommand = command;

                dsSample.Clear();
                daSample.Fill(dsSample, "Sample");

                SampleBS.DataSource = dsSample.Tables["Sample"];

                datagridview.DataSource = SampleBS;

但是当我运行它时我什么都没得，空了



任何人:)

but when I run it I get nothing, empty

anyone :)

推荐答案

在该行之后：

After that line:

command.Parameters.Add(param2);

add：

add:

SqlDataReader reader = command.ExecuteReader();
DataTable dt = new DataTable("Sample");
dt.Load(reader);
datagridview.DataSource = dt

当然，你可以使用 SelectCommand [ ^ ]和 SelectParameters [ ^ ]。 ;）

Of course, you can use SelectCommand[^] and SelectParameters[^] too. ;)

你好，



查看这个帖子，它可以帮到你：

http://stackoverflow.com/questions/1069311/passing-an-参数数组到存储过程 [ ^ ]



JAFC

Hello,

Check this thread, it may help you:
http://stackoverflow.com/questions/1069311/passing-an-array-of-parameters-to-a-stored-procedure[^]

JAFC

你好，



下面我将如何处理ID列表作为SQL中的参数。首先，我有一个函数将Id的分隔符分隔字符串转换为表格。

Hello,

Heres how I handle a list of Ids as a parameter in SQL. First I have a function that converts a delimiter seperated string of Id's into a table.

CREATE FUNCTION fnGetNumericTableFromCommaSeparatedList
(
	@CommaSeparatedList VARCHAR(4000),
	@Delimiter CHAR
)
RETURNS @ListValues TABLE 
(
	Id BIGINT NULL
)
AS
BEGIN

DECLARE @CommaIndex INT
DECLARE @TextValue VARCHAR(255)

SET @CommaIndex = CHARINDEX(@Delimiter, @CommaSeparatedList)

WHILE @CommaIndex > 0
BEGIN					 
	SET @TextValue = LTRIM(RTRIM(SUBSTRING(@CommaSeparatedList, 1, @CommaIndex - 1)))
	
	IF(@TextValue != '' AND (ISNUMERIC(@TextValue) = 1))
	BEGIN			 
		INSERT @ListValues (Id) VALUES (CONVERT(BIGINT, @TextValue))
	END
	 
	IF(@TextValue = 'NULL')
	BEGIN
		INSERT @ListValues (Id) VALUES (NULL)
	END 
	
	SET @CommaSeparatedList = SUBSTRING(@CommaSeparatedList, @CommaIndex + 1, LEN(@CommaSeparatedList)) 
	 
	SET @CommaIndex = CHARINDEX(@Delimiter, @CommaSeparatedList)
END
	
IF (@CommaSeparatedList != '' AND (ISNUMERIC(@CommaSeparatedList) = 1))
BEGIN
	INSERT INTO @ListValues (Id) VALUES (CONVERT(BIGINT, @CommaSeparatedList))
END	
	
IF(@CommaSeparatedList = 'NULL')
BEGIN
	INSERT @ListValues (Id) VALUES (NULL)
END	

RETURN

END

然后我加入到函数

Then I join to the function

DECLARE @ListOfIds VARCHAR(255)
SET @ListOfIds = '1, 3, 5, 6, 6'

SELECT		f.*
FROM		Foo f
INNER JOIN	dbo.fnGetNumericTableFromCommaSeparatedList(@ListOfIds, ',') l
   ON	        f.Id = l.Id

希望有所帮助，

马特

Hope that helps,
Matt

这篇关于如何将参数传递给sql程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！