在C#中创建SQLServer的存储过程

来源：互联网发布：c语言数据结构与算法编辑：程序博客网时间：2024/05/16 06:49

1创建：使用VS2005的“存储过程”模板创建；
2.部署：通过VS2005部署自动在SQLServer中创建存储过程；
3.使用：在C#中使用命令对象调用存储过程。

创建存储过程：
GetProduct.cs:

view source

print?

01.using System;

02.using System.Data;

03.using System.Data.SqlClient;

04.using System.Data.SqlTypes;

05.using Microsoft.SqlServer.Server;

06.

07.

08.public partial class StoredProcedures

09.{

10. [Microsoft.SqlServer.Server.SqlProcedure]

11. public static void GetProduct(int id)

12. {

13. //使用调用该存储过程的客户端打开的连接www.k2tiyu.com

14. SqlConnection conn = new SqlConnection("Context Connection=true");

15. conn.Open();

16. SqlCommand cmd = new SqlCommand();

17. cmd.Connection = conn;

18. cmd.CommandText = "Select ProductID, ProductName, CategoryID, Quantity FROM Products Where ProductID = @ID";

19. cmd.Parameters.Add("@ID", SqlDbType.Int, 0);

20. cmd.Parameters["@ID"].Value = id;

21.

22. SqlDataReader reader = cmd.ExecuteReader();

23. SqlPipe pipe = SqlContext.Pipe;

24. //将读取器返回给客户端www.h258w.com

25. pipe.Send(reader);

26. }

27.};

测试存储过程：www.kanzhibotv.com

01.using System;

02.using System.Collections.Generic;

03.using System.Text;

04.using System.Data.SqlClient;

05.using System.Data;

06.

07.namespace Magci.Test.SQLServer.TestProc

08.{

09. class Program

10. {

11. static void Main(string[] args)

12. {

13. string source = @"server=.\sqlexpress; database=MGC; trusted_connection=true";

14. using (SqlConnection conn = new SqlConnection(source))

15. {

16. conn.Open();

17. SqlCommand cmd = conn.CreateCommand();

18. cmd.CommandText = "GetProduct";

19. cmd.CommandType = CommandType.StoredProcedure;

20. SqlParameter param = new SqlParameter("@id", 1);

21. cmd.Parameters.Add(param);

22. using (SqlDataReader reader = cmd.ExecuteReader())

23. {

24. while (reader.Read())

25. {

26. Console.WriteLine("Name: {0}, CategoryID: {1}, Quantity: {2}", reader["ProductName"], reader["CategoryID"], reader["Quantity"]);

27. }

28.

29. reader.Close();

30. }

31.

32.

33. conn.Close();

34. }

35. Console.ReadLine();

36. }

37. }

38.}