如何基于Orchard CMS开发模块实现外部SQL Server数据展示？

Absolutely, I’ve built several modules like this for Orchard CMS—let’s break down the core approach and walk through actionable examples to get you up and running quickly.

Orchard’s modular architecture is perfect for this use case. The key idea is to create a custom module that:

• Connects to your external SQL Server using a dedicated DbContext (so you don’t interfere with Orchard’s own database)
• Fetches data via a service layer (for clean separation of concerns)
• Displays the data either as a reusable widget (to drop into existing pages) or a standalone custom page.

1. Create a New Orchard Module #

Start by setting up your module structure. You can use Orchard’s CLI tool for this:

orchard module create /Name:ExternalDataModule

Alternatively, manually create a folder in your Orchard project’s Modules directory, add a .csproj file, and a Module.txt to define basic module info:

Name: ExternalDataModule
AntiForgery: enabled
Author: Your Name
Version: 1.0.0
Description: Displays data from an external SQL Server database

2. Configure External Database Connection #

Add your external SQL Server connection string to your project’s appsettings.json:

"ConnectionStrings": {
  "ExternalSqlServer": "Server=YOUR_SERVER;Database=YOUR_DB;User Id=YOUR_USER;Password=YOUR_PASS;TrustServerCertificate=True;"
}

3. Implement the Data Access Layer #

Create a standalone DbContext for your external database (don’t inherit from Orchard’s OrchardDbContext—keep it separate):

using Microsoft.EntityFrameworkCore;

namespace ExternalDataModule.Data
{
    public class ExternalDbContext : DbContext
    {
        public ExternalDbContext(DbContextOptions<ExternalDbContext> options) : base(options) { }

        // Map your external tables here
        public DbSet<Product> Products { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // If your entity name doesn't match the table name, map it explicitly
            modelBuilder.Entity<Product>().ToTable("Products", "ExternalSchema");
        }
    }

    // Example entity matching your external table structure
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public decimal Price { get; set; }
        public DateTime CreatedDate { get; set; }
    }
}

Register this DbContext in your module’s Startup.cs:

using ExternalDataModule.Data;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using OrchardCore.Modules;

namespace ExternalDataModule
{
    public class Startup : StartupBase
    {
        public override void ConfigureServices(IServiceCollection services)
        {
            var config = services.BuildServiceProvider().GetService<IConfiguration>();
            services.AddDbContext<ExternalDbContext>(options =>
                options.UseSqlServer(config.GetConnectionString("ExternalSqlServer")));
        }
    }
}

4. Build a Service Layer for Data Operations #

Wrap your data access logic in a service to keep your code clean and testable. First, define an interface:

using System.Collections.Generic;
using System.Threading.Tasks;
using ExternalDataModule.Data;

namespace ExternalDataModule.Services
{
    public interface IExternalDataService
    {
        Task<IEnumerable<Product>> GetLatestProductsAsync(int itemCount);
        Task<Product> GetProductByIdAsync(int id);
    }
}

Then implement the service:

using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using ExternalDataModule.Data;
using Microsoft.EntityFrameworkCore;

namespace ExternalDataModule.Services
{
    public class ExternalDataService : IExternalDataService
    {
        private readonly ExternalDbContext _dbContext;

        public ExternalDataService(ExternalDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        public async Task<IEnumerable<Product>> GetLatestProductsAsync(int itemCount)
        {
            return await _dbContext.Products
                .OrderByDescending(p => p.CreatedDate)
                .Take(itemCount)
                .ToListAsync();
        }

        public async Task<Product> GetProductByIdAsync(int id)
        {
            return await _dbContext.Products.FindAsync(id);
        }
    }
}

Register the service in Startup.cs:

services.AddScoped<IExternalDataService, ExternalDataService>();

5. Display Data: Two Common Options #

Option A: Reusable Widget (For Embedding in Pages) #

Widgets let you add your data display to any page via Orchard’s admin interface.

First, create a content part for your widget:

using OrchardCore.ContentManagement;

namespace ExternalDataModule.Models
{
    public class ProductListWidgetPart : ContentPart
    {
        public string Title { get; set; } = "Latest Products";
        public int ProductCount { get; set; } = 10;
    }
}

Then create a driver to handle display logic:

using System.Threading.Tasks;
using ExternalDataModule.Models;
using ExternalDataModule.Services;
using OrchardCore.ContentManagement.Display.ContentDisplay;
using OrchardCore.ContentManagement.Display.Models;
using OrchardCore.DisplayManagement.Views;

namespace ExternalDataModule.Drivers
{
    public class ProductListWidgetDriver : ContentPartDisplayDriver<ProductListWidgetPart>
    {
        private readonly IExternalDataService _dataService;

        public ProductListWidgetDriver(IExternalDataService dataService)
        {
            _dataService = dataService;
        }

        protected override async Task<IDisplayResult> Display(ProductListWidgetPart part, BuildDisplayContext context)
        {
            var products = await _dataService.GetLatestProductsAsync(part.ProductCount);
            
            return Initialize<ProductListWidgetViewModel>("ProductListWidget", model =>
            {
                model.Title = part.Title;
                model.Products = products;
            })
            .Location("Content:10");
        }

        // Add Editor logic here if you want admins to configure Title/ProductCount via the admin UI
    }

    public class ProductListWidgetViewModel
    {
        public string Title { get; set; }
        public IEnumerable<Product> Products { get; set; }
    }
}

Create a view for the widget (Views/ProductListWidget.cshtml):

<div class="external-product-widget">
    <h3>@Model.Title</h3>
    <div class="product-grid">
        @foreach (var product in Model.Products)
        {
            <div class="product-card">
                <h4>@product.Name</h4>
                <p>@product.Description</p>
                <span class="price">@product.Price.ToString("C")</span>
            </div>
        }
    </div>
</div>

Finally, register the widget in your module’s Manifest.cs or via the admin UI by creating a new content type that uses your ProductListWidgetPart.

Option B: Standalone Custom Page #

If you need a dedicated page for your data, create a controller and action:

using System.Threading.Tasks;
using ExternalDataModule.Services;
using Microsoft.AspNetCore.Mvc;

namespace ExternalDataModule.Controllers
{
    public class ExternalDataController : Controller
    {
        private readonly IExternalDataService _dataService;

        public ExternalDataController(IExternalDataService dataService)
        {
            _dataService = dataService;
        }

        public async Task<IActionResult> ProductList()
        {
            var products = await _dataService.GetLatestProductsAsync(20);
            return View(products);
        }
    }
}

Create a view for the action (Views/ExternalData/ProductList.cshtml):

@{
    ViewData["Title"] = "External Products";
}

<h1>@ViewData["Title"]</h1>

<table class="table">
    <thead>
        <tr>
            <th>Name</th>
            <th>Description</th>
            <th>Price</th>
            <th>Added Date</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var product in Model)
        {
            <tr>
                <td>@product.Name</td>
                <td>@product.Description</td>
                <td>@product.Price.ToString("C")</td>
                <td>@product.CreatedDate.ToString("MM/dd/yyyy")</td>
            </tr>
        }
    </tbody>
</table>

You can map a friendly URL to this action using Orchard’s routing system—add a Routes.cs file to your module:

using Microsoft.AspNetCore.Routing;
using OrchardCore.Modules;

namespace ExternalDataModule
{
    public class Routes : IRouteProvider
    {
        public void BuildEndpoints(IEndpointRouteBuilder endpoints)
        {
            endpoints.MapControllerRoute(
                name: "ExternalProductList",
                pattern: "external-products",
                defaults: new { controller = "ExternalData", action = "ProductList" }
            );
        }
    }
}

• Cache Data: Use Orchard’s caching services (like IDistributedCache) to cache frequent queries and reduce load on your external database.
• Error Handling: Add try/catch blocks in your service layer to handle database connection issues, and return user-friendly error messages.
• Permissions: Restrict access to your data pages/widgets using Orchard’s permission system (create a custom permission and check it in your driver/controller).
• Testing: Mock your IExternalDataService in unit tests to avoid hitting the real external database during testing.

内容的提问来源于stack exchange，提问作者Tony Tong