Full Report Project with SQL Server

What We Will Build

We will build a complete reporting application that can show product reports and order reports. This will combine everything you learned: SQL Server, datasets, RDLC reports, and Windows Forms.

1. Database Schema

-- Products table - stores product information
CREATE TABLE Products (
    ProductId INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Price DECIMAL(18,2) NOT NULL,
    Description NVARCHAR(500),
    Category NVARCHAR(50),
    QuantityInStock INT DEFAULT 0,
    IsActive BIT DEFAULT 1,
    CreatedDate DATETIME DEFAULT GETDATE()
);

-- Orders table - stores order header information
CREATE TABLE Orders (
    OrderId INT IDENTITY(1,1) PRIMARY KEY,
    OrderDate DATETIME DEFAULT GETDATE(),
    CustomerName NVARCHAR(100),
    TotalAmount DECIMAL(18,2)
);

-- OrderItems table - stores each item in an order
CREATE TABLE OrderItems (
    OrderItemId INT IDENTITY(1,1) PRIMARY KEY,
    OrderId INT FOREIGN KEY REFERENCES Orders(OrderId),
    ProductId INT FOREIGN KEY REFERENCES Products(ProductId),
    Quantity INT,
    UnitPrice DECIMAL(18,2),
    TotalPrice DECIMAL(18,2)
);

2. Dataset Configuration

// ProductDataSet.xsd
- Products Table
  - ProductId, Name, Price, Description, Category, QuantityInStock, IsActive

// OrderDataSet.xsd
- Orders Table
  - OrderId, OrderDate, CustomerName, TotalAmount
- OrderItems Table
  - OrderItemId, OrderId, ProductId, Quantity, UnitPrice, TotalPrice

3. Report Design

Product Report Layout

+------------------------------------------+
| Product Report                            |
|------------------------------------------|
| ID  Name      Price   Category    Stock   |
|------------------------------------------|
| 1   Laptop    $999.99  Electronics  5     |
| 2   Mouse     $29.99   Electronics  20    |
|------------------------------------------|
| Total Products: 2                        |
+------------------------------------------+

Order Summary Report Layout

+------------------------------------------+
| Order Summary                             |
|------------------------------------------|
| Order ID: 1   Date: 2024-01-15           |
| Customer: John Smith                      |
|------------------------------------------|
| Product     Quantity  Unit Price  Total   |
|------------------------------------------|
| Laptop      1         $999.99    $999.99 |
| Mouse       2         $29.99     $59.98  |
|------------------------------------------|
| Total: $1,059.97                         |
+------------------------------------------+

4. Report Service

public class ReportService
{
    private readonly string _connectionString;
    
    public ReportService(string connectionString)
    {
        _connectionString = connectionString;
    }
    
    // Get all active products for the product report
    public DataSet GetProductsForReport()
    {
        DataSet ds = new DataSet();
        string query = "SELECT * FROM Products WHERE IsActive = 1";
        
        using (SqlConnection conn = new SqlConnection(_connectionString))
        using (SqlDataAdapter adapter = new SqlDataAdapter(query, conn))
        {
            adapter.Fill(ds, "Products");
        }
        
        return ds;
    }
    
    // Get order details for the order report
    public DataSet GetOrderReport(int orderId)
    {
        DataSet ds = new DataSet();
        
        string orderQuery = "SELECT * FROM Orders WHERE OrderId = @OrderId";
        string itemsQuery = "SELECT * FROM OrderItems WHERE OrderId = @OrderId";
        
        using (SqlConnection conn = new SqlConnection(_connectionString))
        {
            // Get Order header
            using (SqlDataAdapter adapter = new SqlDataAdapter(orderQuery, conn))
            {
                adapter.SelectCommand.Parameters.AddWithValue("@OrderId", orderId);
                adapter.Fill(ds, "Orders");
            }
            
            // Get Order items
            using (SqlDataAdapter adapter = new SqlDataAdapter(itemsQuery, conn))
            {
                adapter.SelectCommand.Parameters.AddWithValue("@OrderId", orderId);
                adapter.Fill(ds, "OrderItems");
            }
        }
        
        return ds;
    }
}

5. Complete Windows Form

public partial class MainForm : Form
{
    private ReportService _reportService;
    
    public MainForm()
    {
        InitializeComponent();
        _reportService = new ReportService(
            "Server=localhost;Database=MyApp;Trusted_Connection=True;");
    }
    
    // Show the product report
    private void btnProductReport_Click(object sender, EventArgs e)
    {
        try
        {
            DataSet data = _reportService.GetProductsForReport();
            
            reportViewer.Reset();
            reportViewer.ProcessingMode = ProcessingMode.Local;
            reportViewer.LocalReport.ReportPath = "ProductReport.rdlc";
            
            ReportDataSource source = new ReportDataSource("ProductDataSet", data.Tables[0]);
            reportViewer.LocalReport.DataSources.Clear();
            reportViewer.LocalReport.DataSources.Add(source);
            
            reportViewer.RefreshReport();
        }
        catch (Exception ex)
        {
            MessageBox.Show($"Error: {ex.Message}", "Error", 
                MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }
    
    // Show the order report
    private void btnOrderReport_Click(object sender, EventArgs e)
    {
        try
        {
            int orderId = (int)cmbOrders.SelectedValue;
            DataSet data = _reportService.GetOrderReport(orderId);
            
            reportViewer.Reset();
            reportViewer.ProcessingMode = ProcessingMode.Local;
            reportViewer.LocalReport.ReportPath = "OrderReport.rdlc";
            
            reportViewer.LocalReport.DataSources.Add(
                new ReportDataSource("Orders", data.Tables[0]));
            reportViewer.LocalReport.DataSources.Add(
                new ReportDataSource("OrderItems", data.Tables[1]));
            
            reportViewer.RefreshReport();
        }
        catch (Exception ex)
        {
            MessageBox.Show($"Error: {ex.Message}", "Error", 
                MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }
    
    // Export the report to PDF
    private void btnExportPDF_Click(object sender, EventArgs e)
    {
        using (SaveFileDialog dialog = new SaveFileDialog())
        {
            dialog.Filter = "PDF Files|*.pdf";
            dialog.DefaultExt = "pdf";
            
            if (dialog.ShowDialog() == DialogResult.OK)
            {
                byte[] bytes = reportViewer.LocalReport.Render("PDF");
                File.WriteAllBytes(dialog.FileName, bytes);
                MessageBox.Show("Report exported successfully!", "Success", 
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
    }
}