本地生成RDL报表文件的创建工具

昨天尝试了一下，不使用Web Service，本地使用同样的方法

        string strCon;

        DataSet ds = new DataSet();
        XmlDataDocument xmlDataDoc;

        strCon = "Server=10.100.3.248;initial catalog=IGS-SMP-WorkTest;User Id=WorkDeveloper;Password=developer;";

        string selectText = "SELECT * FROM SMP_WorkItem";

        SqlDataAdapter dataAdapter = new SqlDataAdapter(selectText, strCon);


        try
        {
            dataAdapter.Fill(ds);

            xmlDataDoc = new XmlDataDocument(ds);

            using(XmlTextWriter writer = new XmlTextWriter("Your Report Location",System.Text.Encoding.UTF8))
            {

         
            xmlDataDoc.WriteTo(writer);
       
            writer.Flush();
            }

        }
        catch
        {
            xmlDataDoc = null;
        }
        finally
        {
            strCon = null;
            ds.Dispose();
        }
    }

然后在Shared Data Source里指定生成的文件路径，Query String根据微软的说明，为空就可以了。
但是不能正确识别XML文件格式，参照向导生成的格式，缺少了很多元素。

通过查找资料的时候发现一个解决方案，我们可以自己制作一个报表生成器，步骤如下：

1. 新建一个Console Application,建立报表生成器：

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Xml;

namespace RDLGenerator
{
    class RdlGenerator
    {
        SqlConnection m_connection;
        string m_connectString;
        string m_commandText;
        ArrayList m_fields;

        public static void Main()
        {
            RdlGenerator myRdlGenerator = new RdlGenerator();
            myRdlGenerator.Run();
        }

        public void Run()
        {
            try
            {
                // Call methods to create the RDL
                this.OpenConnection();
                this.GenerateFieldsList();
                this.GenerateRdl();

                Console.WriteLine("RDL file generated successfully.");
            }

            catch (Exception exception)
            {
                Console.WriteLine("An error occurred: " + exception.Message);
            }

            finally
            {
                // Close the connection string
                m_connection.Close();
            }
        }

        public void OpenConnection()
        {
            TODO:OpenConnection
        }


        public void GenerateFieldsList()
        {
            TODO:GenerateFieldsList
        }


        public void GenerateRdl()
        {
           TODO: GenerateRdl
        }


    }
}

2.创建你自己的数据库连接：

        public void OpenConnection()
        {
            // Create a connection object
            m_connection = new SqlConnection();

            // Create the connection string
            m_connectString = "Your Connnection String
            m_connection.ConnectionString = m_connectString;

            // Open the connection
            m_connection.Open();
        }

3.获取你需要的数据：

        public void GenerateFieldsList()
        {
            SqlCommand command;
            SqlDataReader reader;

            // Executing a query to retrieve a fields list for the report
            command = m_connection.CreateCommand();
            m_commandText =
               "Your SELECT STRING";
            command.CommandText = m_commandText;

            // Execute and create a reader for the current command
            reader = command.ExecuteReader(CommandBehavior.SchemaOnly);

            // For each field in the resultset, add the name to an array list
            m_fields = new ArrayList();
            for (int i = 0; i <= reader.FieldCount - 1; i++)
            {
                m_fields.Add(reader.GetName(i));
            }
        }

4.创建报表XML：

        public void GenerateRdl()
        {
            // Open a new RDL file stream for writing
            FileStream stream;
            stream = File.OpenWrite("Report1.rdl");
            XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);

            // Causes child elements to be indented
            writer.Formatting = Formatting.Indented;

            // Report element
            writer.WriteProcessingInstruction("xml", "version=\"1.0\" encoding=\"utf-8\"");
            writer.WriteStartElement("Report");
            writer.WriteAttributeString("xmlns", null, "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition");
            writer.WriteAttributeString("xmlns:rd", null, "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner");
            writer.WriteElementString("Width", "6in");

            // DataSource element
            writer.WriteStartElement("DataSources");
            writer.WriteStartElement("DataSource");
            writer.WriteAttributeString("Name", null, "DataSource1");
            writer.WriteStartElement("ConnectionProperties");
            writer.WriteElementString("DataProvider", "SQL");
            writer.WriteElementString("ConnectString", m_connectString);
            writer.WriteElementString("IntegratedSecurity", "false");
            writer.WriteEndElement(); // ConnectionProperties
            writer.WriteEndElement(); // DataSource
            writer.WriteEndElement(); // DataSources

            // DataSet element
            writer.WriteStartElement("DataSets");
            writer.WriteStartElement("DataSet");
            writer.WriteAttributeString("Name", null, "DataSet1");

            // Query element
            writer.WriteStartElement("Query");
            writer.WriteElementString("DataSourceName", "DataSource1");
            writer.WriteElementString("CommandType", "Text");
            writer.WriteElementString("CommandText", m_commandText);
            writer.WriteElementString("Timeout", "30");
            writer.WriteEndElement(); // Query

            // Fields elements
            writer.WriteStartElement("Fields");
            foreach (string fieldName in m_fields)
            {
                writer.WriteStartElement("Field");
                writer.WriteAttributeString("Name", null, fieldName);
                writer.WriteElementString("DataField", null, fieldName);
                writer.WriteEndElement(); // Field
            }

            // End previous elements
            writer.WriteEndElement(); // Fields
            writer.WriteEndElement(); // DataSet
            writer.WriteEndElement(); // DataSets

            // Body element
            writer.WriteStartElement("Body");
            writer.WriteElementString("Height", "5in");

            // ReportItems element
            writer.WriteStartElement("ReportItems");

            // Table element
            writer.WriteStartElement("Table");
            writer.WriteAttributeString("Name", null, "Table1");
            writer.WriteElementString("DataSetName", "DataSet1");
            writer.WriteElementString("Top", ".5in");
            writer.WriteElementString("Left", ".5in");
            writer.WriteElementString("Height", ".5in");
            writer.WriteElementString("Width", (m_fields.Count * 1.5) + "in");

            // Table Columns
            writer.WriteStartElement("TableColumns");
            for (int i = 0; i < m_fields.Count; i++)
            {
                writer.WriteStartElement("TableColumn");
                writer.WriteElementString("Width", "1.5in");
                writer.WriteEndElement(); // TableColumn
            }
            writer.WriteEndElement(); // TableColumns

            // Header Row
            writer.WriteStartElement("Header");
            writer.WriteStartElement("TableRows");
            writer.WriteStartElement("TableRow");
            writer.WriteElementString("Height", ".25in");
            writer.WriteStartElement("TableCells");

            foreach (string fieldName in m_fields)
            {
                writer.WriteStartElement("TableCell");
                writer.WriteStartElement("ReportItems");

                // Textbox
                writer.WriteStartElement("Textbox");
                writer.WriteAttributeString("Name", null, "Header" + fieldName);

                writer.WriteStartElement("Style");
                writer.WriteElementString("TextDecoration", "Underline");
                writer.WriteEndElement(); // Style

                writer.WriteElementString("Top", "0in");
                writer.WriteElementString("Left", "0in");
                writer.WriteElementString("Height", ".5in");
                writer.WriteElementString("Width", "1.5in");
                writer.WriteElementString("Value", fieldName);
                writer.WriteEndElement(); // Textbox

                writer.WriteEndElement(); // ReportItems
                writer.WriteEndElement(); // TableCell
            }

            writer.WriteEndElement(); // TableCells
            writer.WriteEndElement(); // TableRow
            writer.WriteEndElement(); // TableRows
            writer.WriteEndElement(); // Header

            // Details Row
            writer.WriteStartElement("Details");
            writer.WriteStartElement("TableRows");
            writer.WriteStartElement("TableRow");
            writer.WriteElementString("Height", ".25in");
            writer.WriteStartElement("TableCells");

            foreach (string fieldName in m_fields)
            {
                writer.WriteStartElement("TableCell");
                writer.WriteStartElement("ReportItems");

                // Textbox
                writer.WriteStartElement("Textbox");
                writer.WriteAttributeString("Name", null, fieldName);

                writer.WriteStartElement("Style");
                writer.WriteEndElement(); // Style

                writer.WriteElementString("Top", "0in");
                writer.WriteElementString("Left", "0in");
                writer.WriteElementString("Height", ".5in");
                writer.WriteElementString("Width", "1.5in");
                writer.WriteElementString("Value", "=Fields!" + fieldName + ".Value");
                writer.WriteElementString("HideDuplicates", "DataSet1");
                writer.WriteEndElement(); // Textbox

                writer.WriteEndElement(); // ReportItems
                writer.WriteEndElement(); // TableCell
            }

            // End Details element and children   
            writer.WriteEndElement(); // TableCells
            writer.WriteEndElement(); // TableRow
            writer.WriteEndElement(); // TableRows
            writer.WriteEndElement(); // Details

            // End table element and end report definition file
            writer.WriteEndElement(); // Table
            writer.WriteEndElement(); // ReportItems
            writer.WriteEndElement(); // Body
            writer.WriteEndElement(); // Report

            // Flush the writer and close the stream
            writer.Flush();
            stream.Close();
        }

5.编译运行你的程序，选择Start Without Debugging.

然后一个rdl格式的报表文件就生成在你制定的目录了，你只要在Report Services Project中添加这个报表进一步设计就可以了。

不过我们可以主要到GenerateRdl方法中的XML基本都是手动方式添加元素，如果把前面的SqlDataReader换成DataSet，那么GenerateRdl方法中的语句也要重写，所以不是特别方便。

这里只是提供一种选择，是好事坏，是否选择，那就是使用者自己的事情了。