C#中使用存儲過程

首先創建存儲過程，sql語句如下所示：

1. USE [YCYFFJKXT]
2. GO
3. /****** 對象: StoredProcedure [dbo].[Login_UserCount] 腳本日期: 07/17/2012 14:53:18 ******/
4. SET ANSI_NULLS ON
5. GO
6. SET QUOTED_IDENTIFIER ON
7. GO
8. CREATE PROC [dbo].[Login_UserCount]
9. @ZH VARCHAR(20),
10. @MM varchar(50),
11. @Rowcount INT OUTPUT
12. AS
13. BEGIN
14. SELECT * FROM YHXX WHERE ZH=@ZH and MM=@MM
15. SET @Rowcount=@@ROWCOUNT
16. END

C#中調用

1. SqlConnection conn_Local = new SqlConnection();
2. conn_Local.ConnectionString = "server=.;database=YCYFFJKXT;user id=sa;password=sa";
3. try
4. {
5. conn_Local.Open();
6. SqlCommand cmd_Count = new SqlCommand("Login_UserCount",conn_Local);
7. cmd_Count.CommandType = CommandType.StoredProcedure;
9. //添加輸入查詢參數、賦予值
10. cmd_Count.Parameters.Add("@ZH", SqlDbType.VarChar);
11. cmd_Count.Parameters.Add("@MM",SqlDbType.VarChar);
12. cmd_Count.Parameters["@ZH"].Value = "A";
13. cmd_Count.Parameters["@MM"].Value = "B";
15. //添加輸出參數
16. cmd_Count.Parameters.Add("@Rowcount", SqlDbType.Int);
17. cmd_Count.Parameters["@Rowcount"].Direction = ParameterDirection.Output;
19. cmd_Count.ExecuteNonQuery();
21. Console.WriteLine("受影響的行數是"+cmd_Count.Parameters["@Rowcount"].Value.ToString());
24. }
25. catch (Exception ex)
26. {
28. throw ex;
29. }
30. finally
31. {
32. conn_Local.Close();
33. }
34. Console.ReadLine();