Create a SQL Server Stored Procedure in C #

1. Create: use the "Stored Procedure" template of vs2005 to create the template;
2. Deployment: automatically create a stored procedure in sqlserver through vs2005 deployment;
3. Use: Use the command object in C # To Call The stored procedure.

Create a stored procedure:
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. // use the connection www.k2tiyu.com opened by the client that calls the Stored Procedure

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. // return the reader to the client www.h258w.com

25. Pipe. Send (Reader );

26 .}

27 .};

Test the stored procedure: 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 .}