Stored Procedure in C#

Refer:
Passing an Array of Values to a Stored Procedure in SQL Server 2005[^]
Passing a Comma Delimited Parameter to a Stored Procedure[^]

hiii,


read this article

Array Parameter Handling in a Stored Procedure[^]

u can then pass multiple values comma separated, for e.g. inspector,police,Talati and then u can split it in your SP..

Hi,

Convert your list into CommaSeprated value and pass it to your parameter. On StoredProcedure side you can split the value.

But Why you need all information in StoredProcedure ? Instead you can create one master table and you can use that in your StoredProcedure.

Thanks
-Amit Gajjar

Hi if you have lists of data(parameters) into dataset then you have to convert it into XMLstring using dataset.WriteXMl method and then pass that xmlstring into procedure as created below,

CREATE PROC [dbo].[USP_READXMLFILE] (@PXMLdata Text)
AS

  BEGIN
        SET NOCOUNT ON
        DECLARE @HANDLE INT
        DECLARE @SQUERY VARCHAR(1000)


        EXEC SP_XML_PREPAREDOCUMENT  @HANDLE OUTPUT,@PXMLdata
        SELECT * FROM OPENXML(@HANDLE, '/XMLData/tblparamter', 2)
        WITH (name VARCHAR(50),designation VARCHAR(50))

  END

If you are using SQL2008 or later, you could think about using 'Table Value Parameters'

C# and Table Value Parameters[^]

So, you create a Table type to hold your keys in SQL Server, e.g.

CREATE TYPE [dbo].[ItemKeyList] AS TABLE(
    ItemId [varchar](50) NULL
)

In SQL, you could write a stored procedure that uses this data type - eg, inserts some data into some table

CREATE PROCEDURE [dbo].[something_AddKeysToTable]
	(
		@MainId				BIGINT,
		@KeyList			AS KeyList READONLY
	)
AS
BEGIN
	SET NOCOUNT ON;
	
		INSERT INTO [SomeLookupTable]
			   ([MainId]
			   ,[KeyId])
		SELECT
			@MainId, ItemId 
		FROM
		    @KeyList
		WHERE
			ItemId 
		NOT IN 
		(
			SELECT [KeyId] FROM  [SomeLookupTable] WHERE  MainId = @MainId
		)
			            
END

In c#, you would create a List type that represents your table data

    public class ItemList : List<string>, IEnumerable<sqldatarecord>
    {
        IEnumerator<sqldatarecord> IEnumerable<sqldatarecord>.GetEnumerator()
        {
            var sdr = new SqlDataRecord(new SqlMetaData("ItemId", SqlDbType.VarChar, 50));
            foreach (string item in this)
            {
                sdr.SetString(0, item);
                yield return sdr;
            }
        }
    }
</sqldatarecord></sqldatarecord></sqldatarecord></string>

In c#, when you want to send this data, you just use as per normal, we just need to inform that the SQL Parameter is a Structured datatype. This example converts a c# array into the SQL List type, then sends to the server

public void AssignItemsToMain(long mainId, string[] itemIds)
{
    var list = new ItemList();
    list.AddRange(itemIds);

    using (var cmd = connection.CreateCommand() as SqlCommand)
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "something_AddKeysToTable";
        cmd.Parameters.AddWithValue("@MainId", mainId);

        var p = cmd.Parameters.Add("@ItemList", SqlDbType.Structured);
        p.Direction = ParameterDirection.Input;
        p.TypeName = "ItemKeyList";
        p.Value = list;

        cmd.ExecuteNonQuery();
    }
}

It's a bit of code to write up front, but obviously you can make this quite generic and reuse for all your list data :)

Look at this for more info :

http://www.4guysfromrolla.com/articles/012010-1.aspx[^]

http://www.4guysfromrolla.com/webtech/031004-1.shtml[^]

It depends on stored procedure.
If it accepts string parameters and is able to split string to particular values then you don't have much of a problem, you simply add concatenated values as your parameter value. For example:

command.Parameters.AddWithValue("parameterName", concatenatedValues);

On the other hand you must repeat execution of your command for each value.

//repeat for every value
foreach(string val in Values)
{
   //prepare command if necessary 

   //add value
   command.Parameters.AddWithValue("parameterName", val);

   //then execute command and something with data (if any) it returns
}

If you have means to alter stored procedure's code I advise you to alter it to accept strings with multiple strings (concatenated string).

I hope this helps.