1.新建sql Server Project项目，并在项目中添加存储过程模版，此时自动引入System.Data.SqlServer命名空间，并创建一个static函数
2.在自动创建好的函数中添加如下代码，以下是返回SqlDataReader的实例。
1
using System;
2using System.Data;
3using System.Data.SqlClient;
4using System.Data.SqlTypes;
5using Microsoft.SqlServer.Server;
6using System.Text;
7
8public partial class StoredProcedures
9
{
10
[Microsoft.SqlServer.Server.SqlProcedure]
11 public static void StoredProcedure1()
12
{
13 StringBuilder sqlSb = new StringBuilder();
14 string showFiled = "ZfId,ZfXm";
15 string fromTable = "Xf_ZfJbXx";
16 string conditionFiled = "ZfXb";
17 string valueString = "男";
18
19 sqlSb.AppendFormat("select {0} from {1} where {2}='{3}'", showFiled, fromTable, conditionFiled, valueString);
20
21 using (SqlConnection cnn = new SqlConnection("Context Connection=true"))
22 {
23 using (SqlCommand sqlCmd = new SqlCommand())
24 {
25 cnn.Open();
26 sqlCmd.Connection = cnn;
27 sqlCmd.CommandText = sqlSb.ToString();
28 SqlDataReader sqlReader = sqlCmd.ExecuteReader();
29
30 SqlPipe sqlP = SqlContext.Pipe;
31 sqlP.Send(sqlReader);
32
}
33 }
34 }
35
};
36
37
using System;2
using System.Data;3
using System.Data.SqlClient;4
using System.Data.SqlTypes;5
using Microsoft.SqlServer.Server;6
using System.Text;7
8
public partial class StoredProcedures9
{
10
[Microsoft.SqlServer.Server.SqlProcedure]11
public static void StoredProcedure1()12
{
13
StringBuilder sqlSb = new StringBuilder();14
string showFiled = "ZfId,ZfXm";15
string fromTable = "Xf_ZfJbXx";16
string conditionFiled = "ZfXb";17
string valueString = "男";18
19
sqlSb.AppendFormat("select {0} from {1} where {2}='{3}'", showFiled, fromTable, conditionFiled, valueString);20
21
using (SqlConnection cnn = new SqlConnection("Context Connection=true"))22
{23
using (SqlCommand sqlCmd = new SqlCommand())24
{25
cnn.Open();26
sqlCmd.Connection = cnn;27
sqlCmd.CommandText = sqlSb.ToString();28
SqlDataReader sqlReader = sqlCmd.ExecuteReader();29
30
SqlPipe sqlP = SqlContext.Pipe;31
sqlP.Send(sqlReader);32
}33
}34
}35
};36
37
3.修改数据库的兼容级别：
打开查看SQL Server Management Studio,按照以下步骤操作:
鼠标右键选择使用的数据库,点击属性；选择-选项-兼容级别:SQL Server 2005(90).
4.如果CLR为关闭
则执行系统存储过程
sp_configure 'clr enabled','1'
reconfigure
5.可以在查询分析器里运行此存储过程了。
