C#调用SQL Server参数过程传参

SQL SERVER生成测试环境:

[sql] view plain copy print ?

1. Create database Test;  
2. go 
3. USE [Test] 
4. GO 
5. if OBJECT_ID('Tab2','U') is not null 
6.     drop table Tab2 
7. go 
8. CREATE TABLE [dbo].[Tab2]( 
9.     [ID] [int] IDENTITY(1,1) NOT NULL, 
10.     [TabID] [int] NOT NULL, 
11.     [Name2] [nvarchar](50) NULL 
12. )  
13. GO 
14. SET IDENTITY_INSERT [dbo].[Tab2] ON  
15. GO 
16. INSERT [dbo].[Tab2] ([ID], [TabID], [Name2]) VALUES (1, 245575913, N'ID') 
17. GO 
18. INSERT [dbo].[Tab2] ([ID], [TabID], [Name2]) VALUES (2, 245575913, N'name') 
19. GO 
20. INSERT [dbo].[Tab2] ([ID], [TabID], [Name2]) VALUES (3, 277576027, N'ID') 
21. GO 
22. INSERT [dbo].[Tab2] ([ID], [TabID], [Name2]) VALUES (4, 277576027, N'Name2') 
23. GO 
24. INSERT [dbo].[Tab2] ([ID], [TabID], [Name2]) VALUES (5, 277576027, N'TabID') 
25. GO 
26. SET IDENTITY_INSERT [dbo].[Tab2] OFF 
27. GO 
28. if OBJECT_ID('P2','P') is not null 
29.     drop procedure P2 
30. go 
31. Create procedure P2 
32. ( 
33. @StartID int, 
34. @EndID int, 
35. @Rowcount int output 
36. ) 
37. as 
38. select * from Tab2 where ID between @StartID and @EndID 
39. set @Rowcount=@@ROWCOUNT 
40. go 

Create database Test; 
go
USE [Test]
GO
if OBJECT_ID('Tab2','U') is not null
	drop table Tab2
go
CREATE TABLE [dbo].[Tab2](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[TabID] [int] NOT NULL,
	[Name2] [nvarchar](50) NULL
) 
GO
SET IDENTITY_INSERT [dbo].[Tab2] ON 
GO
INSERT [dbo].[Tab2] ([ID], [TabID], [Name2]) VALUES (1, 245575913, N'ID')
GO
INSERT [dbo].[Tab2] ([ID], [TabID], [Name2]) VALUES (2, 245575913, N'name')
GO
INSERT [dbo].[Tab2] ([ID], [TabID], [Name2]) VALUES (3, 277576027, N'ID')
GO
INSERT [dbo].[Tab2] ([ID], [TabID], [Name2]) VALUES (4, 277576027, N'Name2')
GO
INSERT [dbo].[Tab2] ([ID], [TabID], [Name2]) VALUES (5, 277576027, N'TabID')
GO
SET IDENTITY_INSERT [dbo].[Tab2] OFF
GO
if OBJECT_ID('P2','P') is not null
	drop procedure P2
go
Create procedure P2
(
@StartID int,
@EndID int,
@Rowcount int output
)
as
select * from Tab2 where ID between @StartID and @EndID
set @Rowcount=@@ROWCOUNT
go

--打开Visual Studio—创建项目—选择【控制台应用程序】

[csharp] view plain copy print ?

1. #region Using Directives 
2. using System; 
3. using System.Data; 
4. using System.Data.SqlClient; 
5. using System.Collections.Generic; 
6. using System.Linq; 
7. using System.Text; 
8. using System.Threading.Tasks; 
9. #endregion 
10.  
11. namespace TestExecute 
12. { 
13.     class Program 
14.     { 
15.         static void Main(string[] args) 
16.         { 
17.             SqlConnection thisConnection = new SqlConnection(@"Server=(Local);Database=Test;User ID=sa;Password=1"); 
18.             thisConnection.Open(); 
19.             SqlCommand thisCommand = thisConnection.CreateCommand(); 
20.             thisCommand.CommandType = CommandType.StoredProcedure; 
21.             thisCommand.CommandText = "P2"; 
22.             IDataParameter[] parameters = { 
23.                 new SqlParameter("@StartID",SqlDbType.Int), 
24.                 new SqlParameter("@EndID",SqlDbType.Int), 
25.                 new SqlParameter("@Rowcount",SqlDbType.Int), 
26.                 new SqlParameter("return_value",SqlDbType.Int) 
27.             }; 
28.             parameters[0].Value = "1"; 
29.             parameters[1].Value = "5"; 
30.             parameters[2].Direction = ParameterDirection.Output; 
31.             parameters[3].Direction = ParameterDirection.ReturnValue; 
32.             thisCommand.Parameters.AddRange(parameters); 
33.             thisCommand.ExecuteNonQuery(); 
34.             thisConnection.Close(); 
35.             Console.WriteLine("@Rowcount:{0}\nReturn_value:{1}",parameters[2].Value,parameters[3].Value); 
36.             Console.ReadKey(); 
37.         } 
38.     } 
39. } 

#region Using Directives
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
#endregion

namespace TestExecute
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection thisConnection = new SqlConnection(@"Server=(Local);Database=Test;User ID=sa;Password=1");
            thisConnection.Open();
            SqlCommand thisCommand = thisConnection.CreateCommand();
            thisCommand.CommandType = CommandType.StoredProcedure;
            thisCommand.CommandText = "P2";
            IDataParameter[] parameters = {
                new SqlParameter("@StartID",SqlDbType.Int),
                new SqlParameter("@EndID",SqlDbType.Int),
                new SqlParameter("@Rowcount",SqlDbType.Int),
                new SqlParameter("return_value",SqlDbType.Int)
            };
            parameters[0].Value = "1";
            parameters[1].Value = "5";
            parameters[2].Direction = ParameterDirection.Output;
            parameters[3].Direction = ParameterDirection.ReturnValue;
            thisCommand.Parameters.AddRange(parameters);
            thisCommand.ExecuteNonQuery();
            thisConnection.Close();
            Console.WriteLine("@Rowcount:{0}\nReturn_value:{1}",parameters[2].Value,parameters[3].Value);
            Console.ReadKey();
        }
    }
}

--按F5运行结果：