ITEEDU

26.2.4.6.2. 创建数据源

在Crystal Reports中创建报告时，在设计报告时，有两个用于访问MySQL数据的选项。

第1个选项是，设计报告时，使用Connector/ODBC作为ADO数据源。你能够浏览数据库，并使用拖放式操作选择表和字段以创建报告。该方法的缺点是，必须在应用程序中执行额外操作以生成与报告预期的数据集匹配的数据集。

第2个选项是在VB.NET中创建数据集，并将其保存为XML格式。随后，该XML文件可被用于设计报告。在应用程序中显示报告时，它的表现相当良好，但设计时的通用性较差，这是因为在创建数据集时，必须选择所有的相关列。如果忘记选择了某一列，在能够将列添加到报告前，必须重新创建数据集。

使用下述代码，可根据查询操作创建数据集，并将其写入磁盘。

[VB]

Dim myData As New DataSet
Dim conn As New MySqlConnection
Dim cmd As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter

conn.ConnectionString = "server=127.0.0.1;" _
    & "uid=root;" _
    & "pwd=12345;" _
    & "database=world"

Try
    conn.Open()
    cmd.CommandText = "SELECT city.name AS cityName, city.population AS CityPopulation, " _ 
        & "country.name, country.population, country.continent " _
        & "FROM country, city ORDER BY country.continent, country.name"
    cmd.Connection = conn
    
    myAdapter.SelectCommand = cmd
    myAdapter.Fill(myData)
    
    myData.WriteXml("C:\dataset.xml", XmlWriteMode.WriteSchema)
Catch ex As Exception
    MessageBox.Show(ex.Message, "Report could not be created", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
 

[C#]

DataSet myData = new DataSet();
MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
MySql.Data.MySqlClient.MySqlDataAdapter myAdapter;

conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();
myAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter();

conn.ConnectionString = "server=127.0.0.1;uid=root;" +
  "pwd=12345;database=test;";
  
try
{
  cmd.CommandText = "SELECT city.name AS cityName, city.population AS CityPopulation, " +
  "country.name, country.population, country.continent " +
  "FROM country, city ORDER BY country.continent, country.name";
  cmd.Connection = conn;
  
  myAdapter.SelectCommand = cmd;
  myAdapter.Fill(myData);
  
  myData.WriteXml(@"C:\dataset.xml", XmlWriteMode.WriteSchema);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
  MessageBox.Show(ex.Message, "Report could not be created",
  MessageBoxButtons.OK, MessageBoxIcon.Error);
}

如果你选择使用Connector/ODBC来设计报告，可从dev.mysql.com下载它。