VB.NET中如何从SQL Server加载数据到Crystal Report并按查询生成报表

Hey there! Let's break down exactly how to implement your two VB.NET + Crystal Reports requirements step by step—loading data from SQL Server and generating a report on button click with a custom query.

First, let's cover getting your SQL Server data into a Crystal Report:

• Step 1: Set up your report and DataSet
  Create a new Crystal Report in your project, design it to match your target data structure, then add a typed DataSet that mirrors the table(s) you'll pull from SQL Server. This helps Crystal Reports map data fields correctly.

• Step 2: Fetch and bind data with code
  Use VB.NET's database classes to pull data from SQL Server, then feed it to your report. Here's a working snippet:

  Imports System.Data.SqlClient
  Imports CrystalDecisions.CrystalReports.Engine
  Imports CrystalDecisions.Shared
  
  ' Inside your form class
  Private Sub LoadBaseReportData()
      ' Replace with your actual database credentials
      Dim connString As String = "Data Source=YOUR_SERVER_NAME;Initial Catalog=YOUR_DB_NAME;Integrated Security=True;"
      Dim baseQuery As String = "SELECT * FROM YourTargetTable"
  
      Using conn As New SqlConnection(connString)
          Dim dataAdapter As New SqlDataAdapter(baseQuery, conn)
          Dim reportDataSet As New YourTypedDataSet() ' Your pre-defined DataSet
  
          ' Fill the DataSet with SQL Server data
          dataAdapter.Fill(reportDataSet, "YourTargetTable")
  
          ' Load and bind the report
          Dim crystalReport As New YourCrystalReportFile()
          crystalReport.SetDataSource(reportDataSet)
  
          ' Assign to your CrystalReportViewer control
          CrystalReportViewer1.ReportSource = crystalReport
      End Using
  End Sub
  

For the button-triggered custom query report, you just need to adjust the query dynamically in the button's click event. Here's how to do it:

Private Sub btnGenerateCustomReport_Click(sender As Object, e As EventArgs) Handles btnGenerateCustomReport.Click
    ' Example: Get custom query from a text box, or hardcode it as needed
    Dim customQuery As String = "SELECT CustomerName, OrderDate, TotalAmount FROM Orders WHERE OrderDate >= '2024-01-01'"

    Dim connString As String = "Data Source=YOUR_SERVER_NAME;Initial Catalog=YOUR_DB_NAME;Integrated Security=True;"

    Using conn As New SqlConnection(connString)
        Try
            conn.Open()
            Dim dataAdapter As New SqlDataAdapter(customQuery, conn)
            Dim reportDataSet As New YourTypedDataSet()

            dataAdapter.Fill(reportDataSet, "YourTargetTable")

            ' Refresh the report with the new custom data
            Dim crystalReport As New YourCrystalReportFile()
            crystalReport.SetDataSource(reportDataSet)
            CrystalReportViewer1.ReportSource = crystalReport
            CrystalReportViewer1.RefreshReport()
        Catch ex As Exception
            MessageBox.Show($"Oops, error generating report: {ex.Message}")
        End Try
    End Using
End Sub

Quick Best Practices:

• Always use Using statements for database connections to ensure they're properly cleaned up.
• For secure queries (to prevent SQL injection), use parameterized commands instead of raw strings. Here's a quick example:

  Dim cmd As New SqlCommand("SELECT * FROM Orders WHERE CustomerID = @CustID", conn)
  cmd.Parameters.AddWithValue("@CustID", txtCustomerID.Text)
  dataAdapter.SelectCommand = cmd
  

• Make sure your Crystal Report layout matches the columns returned by your custom query—if the query changes the fields, update the report design accordingly.

内容的提问来源于stack exchange，提问作者Eswaramoorthy Karthikeyan