First you should create a data type in database
//help
CREATE TYPE TYPE_NAME AS TABLE
(
PARAM1 CHAR(10),
PARAM1 CHAR(10),
PARAM1 CHAR(10),
PARAM1 CHAR(10),
PARAM1 CHAR(10)
)
//in C# you can use this TYPE Like
DataTable dt = new DataTable();
dt.Columns.Add("PARAM1");
dt.Columns.Add("PARAM2");
dt.Columns.Add("PARAM3");
dt.Columns.Add("PARAM4");
dt.Columns.Add("PARAM5");
DataRow dr;
foreach (String lp in anyListORArray)
{
dr = dt.NewRow();
dr["PARAM1"] = lp.PARAM1;
dr["PARAM2"] = lp.PARAM2;
dr["PARAM3"] = lp.PARAM3;
dr["PARAM4"] = lp.PARAM4;
dr["PARAM5"] = lp.PARAM5;
dt.Rows.Add(dr);
}
if (anyListORArray.Count == dt.Rows.Count)
{
using (var conn = new SqlConnection("myconnectionstring"))
{
using (SqlCommand cmd = new SqlCommand("PROCEDURE_Name",conn ))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Data", dt);
scon.Open();
cmd.ExecuteNonQuery();
}
}
}
use this type in Procedure
//help
ALTER PROCEDURE PROCEDURE_Name
(
@Data [dbo].[TYPE_NAME] ReadOnly
)
AS
DECLARE
@PARAM1 CHAR(10),
@PARAM2 CHAR(10),
@PARAM3 CHAR(10),
@PARAM4 CHAR(10),
@PARAM5 CHAR(10);
DECLARE @Cur CURSOR
SET @Cur = CURSOR FOR
SELECT *
FROM @Data
OPEN @Cur
FETCH NEXT FROM @Cur INTO @PARAM1,@PARAM2,@PARAM3,@PARAM4,@PARAM5
INSERT INTO TABLE_NAME
VALUES
(@PARAM1,@PARAM2,@PARAM3,@PARAM4,@PARAM5)
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @Cur INTO @PARAM1,@PARAM2,@PARAM3,@PARAM4,@PARAM5
END
CLOSE @Cur
DEALLOCATE @Cur
END
GO