This is just the first of some English posts that I'll publish by translating most popular posts in this blog. Original version (in Spanish) is here.
First of all, I must asume that creating reports is one of that things I like worst in programming. But it's quite obvious that few serious applications don't need them, and the one I'm developing now isn't an exception to this rule. So I've been creating some reports recently and I've discovered a new way to do it. And that's what I explain in this post.
As I've said sometimes before in previous posts, I develop with VisualBasic.NET and MySQL database. And I use Crystal Reports to create reports, as this tool is integrated in VisualStudio.NET. Since now, I used to use an ODBC connection configured in each PC to connect to MySQL server. But I didn't like this system much, because actually I'm not working just with one database but with some with different names. They have the same structure, tables and data, but just one is the good one, as the rest are just for developing purposes. It's really easy to use one or other connection string to make the application connect with one or other database, but with reports it isn't so easy as they take data using that ODBC connection (and it just can connect with one database).
But now I've discovered how to create reports with just a DataTable and an XML schema, needing nothing else. Actually it's possible to use a DataSet instead of a DataTable as well. So I'm going to explain it with an easy example and some images.
I'll work with two tables in my MySQL database where I'll keep information about bills. First table is the one with information about headers and has this data:
+---------+------------+-------------------------+
| blh_num | blh_dat | blh_cus |
+---------+------------+-------------------------+
| 1 | 2008-07-30 | CERAMICAS PEPE, S.A. |
| 2 | 2008-07-30 | TALLERES GOMEZ, S.L. |
| 3 | 2008-07-31 | DEPORTES DAMIAN, S.L. |
| 4 | 2008-07-31 | SOFTWARE ALBERTMATA.NET |
+---------+------------+-------------------------+
Second table is the one with information about positions and has this rows:
+---------+---------+------------------------+---------+---------+
| blp_num | blp_pos | blp_art | blp_pri | blp_qty |
+---------+---------+------------------------+---------+---------+
| 1 | 1 | RATON LOGITECH | 15.95 | 1 |
| 2 | 1 | MONITOR LG 19 PULGADAS | 210.5 | 1 |
| 3 | 1 | ROUTER DLINK | 56 | 1 |
| 4 | 1 | RATON LOGITECH | 15.95 | 2 |
| 4 | 2 | TECLADO LOGITECH | 12.95 | 1 |
| 4 | 3 | RECEPTOR GPS ZAPPA | 59.95 | 1 |
| 4 | 4 | PAQUETE 500 FOLIOS | 3.7 | 4 |
+---------+---------+------------------------+---------+---------+
It's something really simple and not normalized, but will be enough for this example, as we're going to create a report that will be the inovice for purchase number 4 (the one with customer SOFTWARE ALBERTMATA.NET). Obviously, we'll need information about both tables but I just want to work with one DataTable, so first of all I'm going to create a MySQL view with this sentence:
CREATE VIEW zbl_bill2print AS
(
SELECT
blh_num AS BILL_NUMBER,
blh_dat AS BILL_DATE,
blh_cus AS BILL_CUSTOMER,
blp_pos AS LINE_NUMBER,
blp_art AS LINE_ARTICLE,
blp_pri AS LINE_UNITPRICE,
blp_qty AS LINE_UNITS,
blp_pri * blp_qty AS LINE_TOTALPRICE
FROM
blh_billheader LEFT JOIN blp_billposits ON blh_num = blp_num
WHERE
blh_num = 4
);
So, from now on the report will be created using this zbl_bill2print view. Let's go with the .NET part.
Step 1. Creating XML file containing table/view structure.
Along this post we'll work with these three things:
1) a Windows form (frmMain) where we'll have the report viewer object.
2) a class (clsReportCreator) we're going to create right now.
3) a report (rptBill) that will be the invoice we want to print.
So let's start creating clsReportCreator class. It'll have only one attribute (the name of the table or view), one constructor method, one method to load DataTable object and one last method to generate the XML file. Here is the full code for this class:
'--------------------------------------------------------------------
' Author: Albert Mata (www.albertmata.net)
' Date: 20080731
' Needs: MySQL.Data reference.
' Description: Class to create a report using just an XML file.
'--------------------------------------------------------------------
Imports MySql.Data.MySqlClient
Public Class clsReportCreator
'----------------------------------------------------------------
' Attributes.
'----------------------------------------------------------------
Private TableOrView As String
'----------------------------------------------------------------
' Constructor method.
'----------------------------------------------------------------
Public Sub New(ByVal TableOrView As String)
Me.TableOrView = TableOrView
End Sub
'----------------------------------------------------------------
' Returns DataTable corresponding to TableOrView.
'----------------------------------------------------------------
Public Function GetDataTable() As DataTable
Dim DA As MySqlDataAdapter
Dim DS As New DataSet
Dim DT As DataTable
Dim ConnectionString As String
Dim SQL As String
'Setting connection string to connect to MySQL database.
ConnectionString = "Database = blog; " _
& "Data Source = localhost; " _
& "User ID = root; " _
& "Password = mypassword"
'Setting SQL string.
SQL = "SELECT * FROM " & Me.TableOrView
'Getting data and filling DataSet and DataTable.
DA = New MySqlDataAdapter(SQL, ConnectionString)
DA.Fill(DS, Me.TableOrView)
DT = DS.Tables(Me.TableOrView)
'Returning DataTable.
Return DT
End Function
'----------------------------------------------------------------
' Creates XML file in desired path.
'----------------------------------------------------------------
Public Sub CreateXMLFile(ByVal FilePath As String)
Dim DT As DataTable
'Creating DataTable.
DT = Me.GetDataTable()
'Writting XML file in desired path.
DT.WriteXmlSchema(FilePath & Me.TableOrView & ".xml")
End Sub
End Class
And we also create frmMain form, which only code by the moment will be this:
'--------------------------------------------------------------------
' Author: Albert Mata (www.albertmata.net)
' Date: 20080731
' Description: Form to show how to create a report using just an XML
' file.
'--------------------------------------------------------------------
Public Class frmMain
'----------------------------------------------------------------
' As a first step, creates XML file.
'----------------------------------------------------------------
Private Sub frmMain_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
'Creating XML file.
Dim RC As New clsReportCreator("zbl_bill2print")
RC.CreateXMLFile("C:\")
End Sub
End Class
Right now we have a first application. If we execute it we'll get C:\zbl_bill2print.xml file with the structure of zbl_bill2print view. So we run it and get that file.
Step 2. Creating report and loading data source.
First, we add a report to our project and give it a name like rptBill.rpt. We create it choosing empty report option, so desestimating any templates.
Now we go to Fields explorer menu and right-click the first option (Database fields). In new contextual menu we click on Database assistant option.
After this we get the Available data source menu, where we choose Create new connection and after that ADO.NET option.
Making this, we'll see a new form where we'll be asked about File's path. In this point we have to find XML file we've created before (in my example C:\zbl_bill2print.xml) and then press Finish. We have NewDataSet option including our just added zbl_bill2print in Available data source menu now.
So we select it and press button to move it to Selected tables menu. Done this, it's time to click on Accept.
With all this stuff we've gotten that zbl_bill2print structure available in Fields explorer menu with all its fields, as shown in image below:
Step 3. Designing report.
Nothing special to say here. Just adding fields from Fields explorer menu, inserting text objects where needed, sums, text formats, images and so on...
I've just created a very simple design like this:
Step 4. Last actions to get the invoice.
Finally we're going to create the bill. To do that, we add a CrystalReportViewer object in frmMain form. I call it crvBill. After that it's necessary to modify frmMain source code to make it look like this:
'--------------------------------------------------------------------
' Author: Albert Mata (www.albertmata.net)
' Date: 20080731
' Description: Form to show how to create a report using just an XML
' file.
'--------------------------------------------------------------------
Imports CrystalDecisions.CrystalReports.Engine
Public Class frmMain
'----------------------------------------------------------------
' Creates XML file (just once) and creates and loads a report.
'----------------------------------------------------------------
Private Sub frmMain_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
'Creating XML file.
Dim RC As New clsReportCreator("zbl_bill2print")
'RC.CreateXMLFile("C:\")
'Creating report.
Dim RD As ReportDocument = New rptBill()
'Setting data source for report.
Dim DT As DataTable = RC.GetDataTable()
RD.SetDataSource(DT)
'Setting data source for possible subreports.
For Each SR As ReportDocument In RD.Subreports
If SR.Database.Tables.Count > 0 Then
SR.SetDataSource(DT)
End If
Next
'Setting recently created report must be shown in viewer.
Me.crvBill.ReportSource = RD
End Sub
End Class
It's important to note that the line where the XML file is created is commented now, as we just need to create this file once to use it to create the source data, but from now on we don't need to generate it every time.
What we're mainly doing in this code is:
1) creating a report object same kind we've designed in step 3,
2) getting a DataTable with data we want to show (in this example and according to the way we've defined MySQL view, we want to show invoice number 4),
3) setting this DataTable as the report's source data,
4) asking CrystalReportViewer to show this report.
We execute the application again and get desired invoice:
Of course there should be quite more information, images and legal texts in a real invoice, but this is just an easy example of how to do the report itself.
So we've seen how to create a report in VisualBasic.NET just using an XML file. Of course there are plenty of things to improve, as optimizing how database connection is done, or avoiding WHERE condition directly in MySQL view and so on... but what I was looking for with this example was just a very minimum guide to show the process.
PS. Some menu and option names can be different as I develope in VisualStudio Spanish version and I've just translated them as I've thought they could appear in English version. Sorry about that!
Update.
There is a second part for this post explaining how to pass parameters from form to report, but it's still only in Spanish.
Como quiera que estos últimos días ando trabajando algunos aspectos que hacen relación a los distintos tipos de datos, sobretodo en lo que a la clase Nullable se refiere, me he topado con una referencia a la velocidad de cálculo de operaciones en función del tipo de dato. Y como me ha parecido curiosa, he buscado la manera de poder comparar de manera fácil y rápida la velocidad entre distintos tipos de datos para ver qué valores me daba.
VisualBasic.NET (supongo que todo el VisualStudio.NET) tiene un comportamiento que me desquicia. Bueno, tiene unos cuantos, pero hoy hablo de uno en particular. Imaginemos un caso muy habitual en el que tengo un control de usuario y un formulario. Primero diseño el control de usuario y luego en el formulario lo insertaré.
Minipost recordatorio para mí mismo... Hace un rato he descubierto con gran sorpresa y desencanto que si en un objeto de tipo DataGridView tengo puestas algunas columnas como tipo ComboBox a través del objeto propio del .NET Framework DataGridViewComboBoxColumn (toma nombre cortito), y el campo en cuestión es de algún tipo que admite nulos -por ejemplo un Nullable(Of UInt16) o un simple String-... hasta que en el ComboBox no seleccionamos ningún valor, en la tabla relacionada de la base de datos se almacenará un valor NULL sin problemas, pero en el momento en que en el ComboBox seleccionamos un valor, no tenemos manera fácil de quitar esa selección. Dicho en plan fácil, un ComboBox dentro de un DataGridView nos permite seleccionar fácilmente un valor pero difícilmente quitar esa selección (aunque parezca mentira).
Cuando desarrollamos en .NET es habitual utilizar controles personalizados que mejoran los preexistentes. Habitualmente podremos hacer uso de librerías públicas que podemos obtener en internet (liberadas o de pago). Otras veces podremos crearnos nosotros mismos nuestras propias librerías de controles. Yo suelo hacer esto último cuando no se trata de algo demasiado complejo, ya que termino antes que buscando componentes de terceros y peleándome después con sus correspondientes licencias. Entre otras cosas porque cualquier tipo de licencia "share alike" no me sirve.
Hace un rato se me ha planteado un problema que quien más quien menos habrá tenido en alguna ocasión. Necesitaba poder comparar dos cadenas y obtener algún indicador de hasta qué punto podría tratarse de la misma cadena escrita de maneras ligeramente diferentes. He estado dándole vueltas sobre cómo podría jugar con los distintos métodos de la clase String para conseguir algún valor. Me he planteado utilizar análisis de frecuencias, generar números ponderados en función de carácter y posición dentro de la cadena... Pero nada me convencía, así que he insistido un poco más en buscar si ya alguien había desarrollado alguna función parecida y para mi mayúscula sorpresa me he dado de bruces con 
Estos últimos días he estado liado programando una clase que tira intensamente de reflexión (me refiero a System.Reflection, no a que haya estado reflexionando intensamente, que también) para relacionar un objeto de una clase determinada (la que sea) con una tabla en la base de datos. Así, utilizando esa clase auxiliar podemos decirle que cargue un objeto a partir de un registro de una tabla o que lo guarde en ella. Y sirve para cualquier clase que tenga una tabla relacionada en la base de datos. Quizá otro día cuelgo esa clase... Pero el caso es que mientras lo desarrollaba he tenido algunos problemillas por no encajarme exactamente los tipos de datos que me devolvía MySQL y los que esperaba .NET, así que tras haber estado buscando cuáles son las equivalencias exactas, paso a exponer la tabla de correspondencias entre tipos de datos tanto en MySQL como en VisualBasic.NET como en el .NET Framework.