使用ASP.NET Web API获取SharePoint列表数据至DataTable并输出JSON遇空值问题

I’ve dealt with this exact scenario before — let’s break down how to get your data loading correctly and output it as JSON without hitting those frustrating null returns.

Step 1: Verify SharePoint Client Context Setup (The #1 Null Cause) #

Nine times out of ten, a null result stems from a misconfigured or unauthenticated ClientContext. Let’s build a reliable connection first:

using Microsoft.SharePoint.Client;
using System.Net;

public ClientContext GetSharePointContext(string siteUrl, string username, string password, string domain)
{
    var context = new ClientContext(siteUrl);
    // Use Windows credentials (swap for App-Only auth if you're using a service account)
    context.Credentials = new NetworkCredential(username, password, domain);
    
    // Test the connection to catch silent failures early
    try
    {
        context.Load(context.Web, web => web.Title);
        context.ExecuteQuery();
        Console.WriteLine($"Connected to SharePoint site: {context.Web.Title}");
    }
    catch (Exception ex)
    {
        throw new InvalidOperationException("Failed to connect to SharePoint: " + ex.Message);
    }
    
    return context;
}

Pro Tip: For non-interactive scenarios (like a backend API), use App-Only authentication. Make sure your registered app has explicit Read permissions for the target list in SharePoint’s app management settings.

Step 2: Fetch List Data Properly (No More Null Items) #

Once your context works, retrieve list items intentionally — don’t skip loading specific fields, and always validate the list exists first.

public ListItemCollection GetSharePointListItems(ClientContext context, string listTitle)
{
    var list = context.Web.Lists.GetByTitle(listTitle);
    if (list == null)
        throw new ArgumentException($"List '{listTitle}' not found in the SharePoint site.");
    
    // Use CAML query to fetch items (adjust filters if needed)
    var camlQuery = CamlQuery.CreateAllItemsQuery();
    var items = list.GetItems(camlQuery);
    
    // Load ONLY the fields you need (this boosts performance and avoids nulls from unused fields)
    context.Load(items, 
        includes => includes.Include(
            item => item["Title"],
            item => item["Description"],
            item => item["Created"])); // Add your actual field internal names here
    
    context.ExecuteQuery();
    
    return items;
}

Critical Note: Always use field internal names (find these in SharePoint List Settings > Columns > Click the column > Check the URL for Field=InternalName). Using display names (especially those with spaces) will cause missing data or nulls.

Step 3: Convert List Items to DataTable #

Now map the retrieved ListItemCollection to a DataTable with a reusable method:

public DataTable ConvertItemsToDataTable(ListItemCollection items)
{
    var dt = new DataTable();
    
    // Add columns based on SharePoint fields (skip hidden/system fields)
    foreach (var field in items.Fields)
    {
        if (!field.Hidden && !field.ReadOnlyField)
        {
            dt.Columns.Add(field.InternalName, GetFieldType(field.TypeAsString));
        }
    }
    
    // Populate rows with item data
    foreach (ListItem item in items)
    {
        DataRow row = dt.NewRow();
        foreach (DataColumn col in dt.Columns)
        {
            // Handle null values to avoid DataTable errors
            row[col.ColumnName] = item[col.ColumnName] ?? DBNull.Value;
        }
        dt.Rows.Add(row);
    }
    
    return dt;
}

// Helper to map SharePoint field types to .NET types
private Type GetFieldType(string sharePointFieldType)
{
    return sharePointFieldType switch
    {
        "Text" => typeof(string),
        "Number" => typeof(int),
        "DateTime" => typeof(DateTime),
        "Boolean" => typeof(bool),
        // Add more mappings for your specific fields
        _ => typeof(string)
    };
}

Step 4: Return JSON from Your Web API #

Finally, wire this up to your API controller. We’ll use Newtonsoft.Json for reliable DataTable serialization:

using System.Web.Http;
using Newtonsoft.Json;

public class SharePointDataController : ApiController
{
    [HttpGet]
    public IHttpActionResult GetListData()
    {
        try
        {
            // Replace with your SharePoint details
            string siteUrl = "https://your-sharepoint-site-url";
            string listTitle = "Your Target List";
            string username = "your-username";
            string password = "your-password";
            string domain = "your-domain";
            
            // Get context and list items
            var context = GetSharePointContext(siteUrl, username, password, domain);
            var items = GetSharePointListItems(context, listTitle);
            
            if (items.Count == 0)
                return Ok("No data found in the SharePoint list.");
            
            // Convert to DataTable and serialize to JSON
            var dataTable = ConvertItemsToDataTable(items);
            string json = JsonConvert.SerializeObject(dataTable, Formatting.Indented);
            
            return Content(System.Net.HttpStatusCode.OK, json, "application/json");
        }
        catch (Exception ex)
        {
            return InternalServerError(ex);
        }
    }
}

Quick Troubleshooting Checks #

• Permissions: Confirm the user/app has Read access to the list and site.
• List Title: Typos here will result in a null list reference.
• Debug Execution: Add breakpoints to check if context.ExecuteQuery() throws hidden exceptions.
• Field Names: Double-check internal names — display names are not reliable for data retrieval.

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