SQL存储过程显然是非常强大的,.NET也支持对存储过程的调用,自己尝试着写了个小Sample,因为在SQL的学习中,只是笔记一篇。
环境:WindowXP SP2, VS2005, SQL2005
假定在SQL server上已经存在了NWind数据库,同时有如下的存储过程:(存储过程的功能是根据输入的起始日期和结束日期,返回这个时间段内的销售记录和总销售值,并非本文重点)
1
set ANSI_NULLS ON
2
set QUOTED_IDENTIFIER ON
3
go
4![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
-- =============================================
7
-- Author: <Vitoria Tang>
8
-- Create date: <2006.0804>
9
-- Description: <It is a sample procedure for get sales record as specific datetime>
10
-- =============================================
11
ALTER PROCEDURE [dbo].[User_SalesByYear]
12
-- Input parameters: Begin date, End date
13
@BeginDate datetime , @EndDate datetime, @Price int = 0 OUTPUT
14
AS
15
BEGIN
16
--declare @Price money
17
CREATE TABLE #TempTable
18
(ID int not null, ProductName nvarchar(40) not null, Price money not null)
19![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
INSERT INTO #TempTable(ID, ProductName, Price)
21
SELECT Orders.OrderID, Products.ProductName, [Order Details].UnitPrice * [Order Details].Discount
22
FROM Orders, Products, [Order Details]
23
WHERE (
24
((Orders.ShippedDate) Is Not Null And
25
(Orders.ShippedDate) Between @BeginDate And @EndDate)
26
AND
27
(Orders.OrderID = [Order Details].OrderID AND [Order Details].ProductID = Products.ProductID)
28
);
29![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
Select * from #TempTable
31
32
Select @Price = sum(Price) from [#TempTable]
33
Print @Price
34
return (@Price)
35
END
36![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Sample界面:
首先建立与数据库的连接:
1
private System.Data.SqlClient.SqlConnection _connection = null;
2
private void InitializeSource()
3
{
4
//’Server name’需要根据自己的实际SqlServer的机器名填上哦
5
string connetctStr = "Data Source=’Server name’;Initial Catalog=NWind;Integrated Security=True";
6
if( _connection == null)
7
_connection = new System.Data.SqlClient.SqlConnection(connetctStr);
8
}
9![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
其次创建执行存储过 程的SqlCommand,当然前后需要打开和关闭数据库连接,调用存储过程需要给SqlCommand的CommandType属性赋值为 CommandType.StoredProcedure,我们的存储过程是有输入和输出参数的,那么在添加到SqlCommand.Prameters 集合中就可以了。如果是输出参数,设置SqlParameter实例的Direction值就可以了,该属性默认值为Input,所以不设置的话,在执行 完后,得不到输出值哦。具体参见下面的代码。
1
private SqlCommand GetCommand(object sender, EventArgs e)
2
{
3
// User_SalesByYear是存储过程的名字
4
string cmdText = "User_SalesByYear";
5
SqlCommand executeCmd = new SqlCommand();
6
executeCmd.Connection = _connection;
7
// 指出executeCmd是执行存储过程
8
executeCmd.CommandType = CommandType.StoredProcedure;
9
executeCmd.CommandText = cmdText;
10
// 看上面的存储过程,因为是有三个输入参数,我们需要给填到SqlCommand.Parameters集合中
11
// 参数一:BeginDate
12
SqlParameter parameter = new SqlParameter( "@BeginDate", SqlDbType.DateTime);
13
parameter.Value = new DateTime( 1995, 1,1);
14
executeCmd.Parameters.Add(parameter);
15
// 参数二:EndDate
16
parameter = new SqlParameter("@EndDate", SqlDbType.DateTime);
17
parameter.Value = new DateTime(1995, 12, 30);
18
executeCmd.Parameters.Add(parameter);
19
// 参数三:Price,这是个输出参数,需要指明参数方向,否则存储过程的输出值不会写到参数中来
20
parameter = new SqlParameter("@Price", 0);
21
parameter.Direction = ParameterDirection.InputOutput;
22
executeCmd.Parameters.Add(parameter);
23
return executeCmd;
24
}
25![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
本文的示例是用SqlDataAdapter来填充了DataSet并把它显示在DataGridView control上,所以接下来,打开数据库连接创建SqlDataAdapter,并填充DataSet吧.
1
private void button1_Click(object sender, EventArgs e)
2
{
3
_connection.Open();
4
SqlDataAdapter adapter = new SqlDataAdapter();
5
adapter.SelectCommand = executeCmd;
6![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7
DataSet source = new DataSet();
8
adapter.Fill(source);
9
// 输出存储过程的输出参数到Output window看看吧
10
Console.WriteLine(executeCmd.Parameters[2].Value);
11
this.dataGridView1.DataSource = source.Tables[0];
12
_connection.Close();
13
}
14![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)