VS2005＋SQL2005 Reporting动态增加报表(*.rdlc)数据源

前言：

在阅读本篇文章前，我建议您先阅读我之前写的另一篇关于VS2005中如何将datast动态绑定到reportViewer的文章：VS2005＋SQL2005 Reporting Service动态绑定报表（Web），因为该篇可以说是对上一篇的补充。

关于本例子的例程下载：https://files.cnblogs.com/carlwave/exdynamicreport.rar

目的：

通过代码生成ado.net DataSet，然后绑定到reportViewer，基本上实现完全代码化，动态化，使对报表操作更方便。

在上篇中我对如何使用ado.net DataSet动态绑定到reportViewer作了详细介绍，只是上篇中自己留下个疑问，就是对report这个xml文件本身的设计变得相当复杂，通过和网上朋友的交流以及查询资料，写了一个对报表xml文件操作的类，基本实现完全动态化报表操作。

对报表（*.rdlc）文件进行操作：

其实，要生成一个rdlc文件并绑定上数据是有很多方法的。

1、静态：使用微软自带的向导生成dataset，绑定上报表。

2、完全动态：由于rdlc文件原本就是一个XML文件，所以你可以完全自行在代码中生成一个xml文件，但是这个方案也不让人满意，原因是：所有的报表对象都得自己生成，没有可视化工具的设计既繁琐又复杂。特别是如果设计几个line，然后再来上几个分组的话，工作量巨大。

3、部分动态：首先加载rdlc文件到一个XmlDocument对象；然后修改xml内容；把xml序列化成字节流，交给ReportViewer显示。该方案缺点在于每次运行都需要重新加载rdlc，修改，增加了代码复杂度和运算量。

4、 动态修改rdlc文件－>静态调用：这个方案就是我这里要介绍的方案，该方案通过对已经生成的rdlc文件进行读写操作，动态加入了ado.net dataset生成的字段和数据源。
优点在于只需要在第一次和dataset数据源变化的时候对报表的数据源部分进行重新读写就可。

PS：关于完全动态和部分动态可以参考：http://www.weste.net/2006/5-29/15422156045.html

详细操作步骤：

1、新建一个解决方案，增加一个Web项目。

2、增加一个报表文件。（图）

3、增加报表xml操作的RdlcDataSet类，这个类是我纯粹为了报表文件关于数据连接写的，由于使用性比较单一，实现了大部分功能，但是总的来说封装的不是最好，如果你要对报表文件有更多的需求，可以重新封装这个类，具体怎么调用看代码。

'------------------------------------------------------------------------------
2

'--ClassName: RdlcDataSet
3

'--Description: for reset the report file(*.rdlc),add the DataSets files and datasources
4

'-- If you want to use this class, you should do:
5

'-- First:Use Vs2005 to creat a report file(*.rdlc)
6

'-- Second:create a ado.net dataset and pass it to this class
7

'-- Third:run in your codes EX:Dim objRdlcDataSet As New RdlcDataSet
8

'-- objRdlcDataSet.RdlcDataSetNew("Reports\temp.rdlc", "Northwind", "Northwind", dataset)
9

'-- Your just need to run it only once.
10

'--Input: ado.net dataset, DataSourceName,dataSetName
11

'--Output :
12

'--08/31/06 - Created (Fei)
13

'-- just run once
14

'-- ex:Dim objRdlcDataSet As New RdlcDataSet
15

'-- objRdlcDataSet.RdlcDataSetNew("Reports\temp.rdlc", "Northwind", "Northwind", dataset)
16

'--
17

'-- if you just want to add a dataset in exist datasets
18

'-- ex:Dim objRdlcDataSet As New RdlcDataSet
19

'-- objRdlcDataSet.RdlcFileCheck("Reports\temp.rdlc")
20

'-- objRdlcDataSet.AddDataSet(DataSetName, DataSourceName, DataSetAdd)
21

Imports System.Data
23

Imports System.Data.SqlClient
24

Imports System.Xml
25

Imports System.IO
26

Public Class RdlcDataSet
28

Inherits System.Web.UI.Page
29

Private mErrorMessage As String
31

Private mXmlReport As XmlDocument
32

Private mRdlcPath As String
33

Private mNodeReport As XmlNode
34

Public Property ErrorMessage() As String
36

Get
37

Return mErrorMessage
38

End Get
39

Set(ByVal Value As String)
40

mErrorMessage = Value
41

End Set
42

End Property
43

'the path of the *.rdlc
45

'ex:"Report\temp.rdlc"
46

Public Property RdlcPath() As String
47

Get
48

Return mRdlcPath
49

End Get
50

Set(ByVal Value As String)
51

mRdlcPath = Value
52

End Set
53

End Property
54

Public Property XmlReport() As XmlDocument
56

Get
57

Return mXmlReport
58

End Get
59

Set(ByVal Value As XmlDocument)
60

mXmlReport = Value
61

End Set
62

End Property
63

Public Property NodeReport() As XmlElement
65

Get
66

Return mNodeReport
67

End Get
68

Set(ByVal Value As XmlElement)
69

mNodeReport = Value
70

End Set
71

End Property
72

'for reset the rdlc file and add datasources and datasets
74

'ex:objRdlcDataSet.RdlcDataSet("Reports\temp.rdlc", "Northwind", "Northwind", dataset)
75

Public Function RdlcDataSetNew(ByVal RdlcPath As String, ByVal DataSourceName As String, ByVal DataSetName As String, ByVal DataSetAdd As DataSet) As Boolean
76

Dim blnResult As Boolean
77

blnResult = RdlcFileCheck(RdlcPath)
78

If blnResult = False Then
79

Return False
80

Exit Function
81

End If
82

Call RdlcDataSetInit()
83

Call AddDataSource(DataSourceName)
84

Call AddDataSet(DataSetName, DataSourceName, DataSetAdd)
85

End Function
86

'for check the rdlc file
88

'in this class it can only for the *.rdlc which is grnerate by VS2005
89

Public Function RdlcFileCheck(ByVal RdlcPath As String) As Boolean
90

Dim strChkRdlc As String
91

strChkRdlc = RdlcPath.Substring(RdlcPath.Length - 4, 4)
92

strChkRdlc = strChkRdlc.ToLower
93

'if not report file return false
94

If strChkRdlc.CompareTo("rdlc") <> 0 Then
95

Me.ErrorMessage = "File is not a report file.(With *.rdlc)"
96

Return False
97

Exit Function
98

End If
99

RdlcPath = Server.MapPath(RdlcPath)
100

Dim xmlReport As New XmlDocument
101

xmlReport.Load(RdlcPath)
102

Dim nodeReport As XmlNode
103

nodeReport = xmlReport.ChildNodes(1)
104

If nodeReport.Name.CompareTo("Report") <> 0 Then
105

Me.ErrorMessage = "File is not a report file grnerate by VS2005."
106

Return False
107

Exit Function
108

End If
109

Me.NodeReport = nodeReport
110

Me.RdlcPath = RdlcPath
111

Me.XmlReport = xmlReport
112

Return True
113

End Function
114

115

'for Rdlc init
116

'delete all datasets and datasources
117

'and add new empty datasets and datasources
118

Public Sub RdlcDataSetInit()
119

Call DelReportNodes("DataSources")
120

Call DelReportNodes("DataSets")
121

Call AddReportNodes("DataSources")
122

Call AddReportNodes("DataSets")
123

Me.XmlReport.Save(Me.RdlcPath)
124

End Sub
125

126

Private Sub DelReportNodes(ByVal strDelName As String)
127

Dim nodelistDel As XmlNodeList
128

nodelistDel = Me.NodeReport.ChildNodes
129

Dim elmDel As XmlElement
130

Dim i As Int32
131

For i = 0 To nodelistDel.Count - 1
132

elmDel = nodelistDel.Item(i)
133

If elmDel.Name.CompareTo(strDelName) = 0 Then
134

Me.NodeReport.RemoveChild(elmDel)
135

i = i - 1
136

End If
137

If i = nodelistDel.Count - 1 Then
138

Exit For
139

End If
140

Next
141

End Sub
142

143

Private Sub AddReportNodes(ByVal strAddName As String)
144

Dim elmAdd As XmlElement
145

elmAdd = Me.XmlReport.CreateElement("", strAddName, "")
146

elmAdd.SetAttribute("xmlns", Me.NodeReport.NamespaceURI)
147

Me.NodeReport.AppendChild(elmAdd)
148

End Sub
149

150

'add datasource
151

'xml:<DataSources>
152

'<DataSource Name="DataSourceName">
153

' <ConnectionProperties>
154

' <ConnectString />
155

' <DataProvider>SQL</DataProvider>
156

' </ConnectionProperties>
157

'</DataSource>
158

'</DataSources>
159

Public Sub AddDataSource(ByVal DataSourceName As String)
160

Dim i As Int32
161

Dim blnResult As Boolean = False
162

Dim nodeDataSources As XmlNode = Me.NodeReport
163

Dim nodelistAddDataSource As XmlNodeList
164

nodelistAddDataSource = Me.NodeReport.ChildNodes
165

Dim elmDataSources As XmlElement
166

For i = 0 To nodelistAddDataSource.Count - 1
167

elmDataSources = nodelistAddDataSource.Item(i)
168

If elmDataSources.Name.CompareTo("DataSources") = 0 Then
169

nodeDataSources = elmDataSources
170

blnResult = True
171

Exit For
172

End If
173

Next
174

If blnResult = False Then
175

Me.ErrorMessage = "node DataSources hasn't found"
176

Exit Sub
177

End If
178

Dim elmDataSource As XmlElement
179

elmDataSource = Me.XmlReport.CreateElement("DataSource")
180

elmDataSource.SetAttribute("Name", DataSourceName)
181

Dim elmConnectionProperties As XmlElement
182

elmConnectionProperties = Me.XmlReport.CreateElement("ConnectionProperties")
183

Dim elmConnectString As XmlElement
184

elmConnectString = Me.XmlReport.CreateElement("ConnectString")
185

Dim elmDataProvider As XmlElement
186

elmDataProvider = Me.XmlReport.CreateElement("DataProvider")
187

elmDataProvider.InnerText = "SQL"
188

elmConnectionProperties.AppendChild(elmconnectstring)
189

elmConnectionProperties.AppendChild(elmDataProvider)
190

elmDataSource.AppendChild(elmConnectionProperties)
191

nodeDataSources.AppendChild(elmDataSource)
192

Me.XmlReport.Save(Me.RdlcPath)
193

End Sub
194

195

'--------------------------------------------------------------------------
196

'--add datasets
197

'--xml:
198

'--<DataSets>
199

'-- <DataSet Name="DataSetName">
200

'-- <Fields>
201

'-- <Field Name="DataFieldName">
202

'-- <DataField>DataFieldName</DataField>
203

'-- </Field>
204

'-- </Fields>
205

'-- <Query>
206

'-- <DataSourceName>DataSourceName</DataSourceName>
207

'-- <CommandText>
208

'-- </CommandText>
209

'-- <Timeout>30</Timeout>
210

'-- </Query>
211

'-- </DataSet>
212

'--</DataSets>
213

'----------------------------------------------------------------------
214

Private Sub AddDataSet(ByVal DataSetName As String, ByVal DataSourceName As String, ByVal dsAdd As DataSet)
215

Dim i As Int32
216

Dim blnResult As Boolean = False
217

Dim nodeDataSets As XmlNode = Me.NodeReport
218

Dim nodelistAddDataSource As XmlNodeList
219

nodelistAddDataSource = Me.NodeReport.ChildNodes
220

Dim elmDataSources As XmlElement
221

For i = 0 To nodelistAddDataSource.Count - 1
222

elmDataSources = nodelistAddDataSource.Item(i)
223

If elmDataSources.Name.CompareTo("DataSets") = 0 Then
224

nodeDataSets = elmDataSources
225

blnResult = True
226

Exit For
227

Else
228

229

End If
230

Next
231

If blnResult = False Then
232

Me.ErrorMessage = "node DataSets hasn't found"
233

Exit Sub
234

End If
235

Dim elmDataSet As XmlElement
236

elmDataSet = Me.XmlReport.CreateElement("DataSet")
237

elmDataSet.SetAttribute("Name", DataSetName)
238

Dim elmFields As XmlElement
239

elmFields = Me.XmlReport.CreateElement("Fields")
240

Dim elmField As XmlElement
241

For i = 0 To dsAdd.Tables(0).Columns.Count - 1
242

elmField = Me.XmlReport.CreateElement("Field")
243

elmField.SetAttribute("Name", dsAdd.Tables(0).Columns(i).ToString)
244

Dim elmDataField As XmlElement
245

elmDataField = Me.XmlReport.CreateElement("DataField")
246

elmDataField.InnerText = dsAdd.Tables(0).Columns(i).ToString
247

'Dim elmTypeName As XmlElement
248

'elmTypeName = Me.XmlReport.CreateElement("rd:TypeName")
249

'elmTypeName.InnerText = dsAdd.Tables(0).Columns(i).DataType.ToString
250

elmField.AppendChild(elmDataField)
251

'elmField.AppendChild(elmTypeName)
252

elmFields.AppendChild(elmField)
253

Next
254

Dim elmQuery As XmlElement
255

elmQuery = Me.XmlReport.CreateElement("Query")
256

Dim elmDataSourceName As XmlElement
257

elmDataSourceName = Me.XmlReport.CreateElement("DataSourceName")
258

elmDataSourceName.InnerText = DataSourceName
259

Dim elmCommandText As XmlElement
260

elmCommandText = Me.XmlReport.CreateElement("CommandText")
261

Dim elmTimeout As XmlElement
262

elmTimeout = Me.XmlReport.CreateElement("Timeout")
263

elmTimeout.InnerText = "30"
264

elmQuery.AppendChild(elmDataSourceName)
265

elmQuery.AppendChild(elmCommandText)
266

elmQuery.AppendChild(elmTimeout)
267

elmDataSet.AppendChild(elmFields)
268

elmDataSet.AppendChild(elmQuery)
269

nodeDataSets.AppendChild(elmDataSet)
270

271

Me.XmlReport.Save(RdlcPath)
272

End Sub
273

274

End Class

4、在代码中生成ado.net dataset，动态调用报表，运行一次（运行一次即可，以后除非dataset的数据源变了，否则可以注释掉）

Partial Class exLoadReport
2

Inherits System.Web.UI.Page
3

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
5

If Not Page.IsPostBack Then
6

LoadCustomersReport("Reports\Customers.rdlc", "Northwind", "dsCustomers")
7

End If
8

End Sub
9

Private Function GetCustomers() As DataSet
11

Dim dsGetCustomers As New DataSet
12

Dim sqlGetCustomers As String = "SELECT * from Customers where city in (select city from Customers group by city having count(city)>=2)"
13

Using connection As New SqlConnection("Data Source=(local);Initial Catalog=Northwind;User ID=sa;Password=;")
14

Dim command As New SqlCommand(sqlGetCustomers, connection)
15

Dim adpGetCustomers As New SqlDataAdapter(command)
16

adpGetCustomers.Fill(dsGetCustomers, "dsGetCustomers")
17

End Using
18

Return dsGetCustomers
19

End Function
20

Private Sub LoadCustomersReport(ByVal RdlcPath As String, ByVal DataSourceName As String, ByVal DataSetName As String)
22

'get the dataset
23

Dim tmpDs As DataSet
24

tmpDs = GetCustomers()
25

'------------------------------------------------------------------
26

'you must run these codes blew before the first time you load the report.
27

'After that, if you haven't changer the ado.net dataset, you don't need
28

'to run it again.
29

'------------------------------------------------------------------
30

'reset the report file(*.rdlc) add 'DataSets' and 'DataSource'
31

'Dim objRdlcDataSet As New RdlcDataSet
33

'objRdlcDataSet.RdlcDataSetNew(RdlcPath, DataSourceName, DataSetName, tmpDs)
34

'------------------------------------------------------------------
35

'set the reportViewer
37

rpvEx.ProcessingMode = ProcessingMode.Local
38

Dim localReport As LocalReport
39

localReport = rpvEx.LocalReport
40

localReport.ReportPath = RdlcPath
41

'Create a report data source for the sales order data
42

Dim dsCustomers As New ReportDataSource()
43

dsCustomers.Name = DataSetName
44

dsCustomers.Value = tmpDs.Tables(0)
45

localReport.DataSources.Add(dsCustomers)
46

localReport.Refresh()
47

End Sub
48

End Class

5、设计报表，增加一个table，在table的属性中输入dataset的名字，注意：这个名字必须和你写报表文件所使用的dataset名字相同，否则会报错，找不到dataset。（图）

6、再次运行即可。

PS：例程中的报表文件加入了一些分组以及函数的调用，关于报表的使用我就不描述了，这方面的资料还是比较多的。写的比较仓促，欢迎大家给出意见。

posted on 2006-09-05 13:14 Carlwave-陆飞(Fei.Lu) 阅读(14038) 评论(38) 编辑收藏举报