C#到sql server参数传递 [英] C# to sql server parameter passing
问题描述
如何将数组列表从c#传递到sql server中的存储过程,然后插入需要根据数组列表计数执行。
试试这个..i希望你能找到解决方案。
TSQL:将数组/列表/集传递给存储过程(MS SQL Server) [ ^ ]
< br $> b $ b
http://outcoldman.com/en/archive/2010/08/28/tsql-passing-array-list-set-to-stored-procedure-ms-sql -server- / [ ^ ]
http://stackoverflow.com/questions/1940417/sql-stored-procedure-that-selects-from-array-list-xml-input-parameter [ ^ ]
首先你应该在数据库中创建一个数据类型
// help
创建 TYPE TYPE_NAME AS 表
(
PARAM1 CHAR ( 10 ),
PARAM1 CHAR ( 10 ),
PARAM1 CHAR ( 10 ),
PARAM1 CHAR ( 10 ),
PARAM1 CHAR ( 10 )
)
//在C#你可以使用这个TYPE赞
< pre lang =c#> 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)
{
使用( var conn = new SqlConnection( myconnectionstring))
{
使用(SqlCommand cmd = new SqlCommand( PROCEDURE_Name,conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue( @ Data ,dt);
scon.Open();
cmd.ExecuteNonQuery();
}
}
}
在程序中使用此类型
/ / 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 < span class =code-keyword> CHAR ( 10 );
- 使用光标你可以使用这个表格或者arry
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
请参考这个我想你得到一些提示
TSQL:将数组/列表/集传递给存储过程(MS SQL Server) [ ^ ]
How to pass a array list from c# to store procedure in sql server and then insert need to be perform based on the array list count.
try this..i hope you'll find the solution.
TSQL: Passing array/list/set to stored procedure (MS SQL Server)[^]
http://outcoldman.com/en/archive/2010/08/28/tsql-passing-array-list-set-to-stored-procedure-ms-sql-server-/[^]
http://stackoverflow.com/questions/1940417/sql-stored-procedure-that-selects-from-array-list-xml-input-parameter[^]
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); --useing Cursor you can use this table or arry 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
pls refer this i Think u get some hint
TSQL: Passing array/list/set to stored procedure (MS SQL Server)[^]
这篇关于C#到sql server参数传递的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!