使用DataContext将SQL Server数据映射至chromeExtData类的疑问

Alright, let's break this down for you step by step. Your core goal is to fetch a single row by ID into a chromeExtData instance, plus support writing that instance back to the database. Let's start with your existing code, fix the gaps, and cover the reverse operation.

1. Is Your Existing Query Code Valid? #

Your current code is mostly correct, but has a couple of key tweaks needed:

• Since you're querying by ID (which should be unique), you don't need .ToList() — use .FirstOrDefault() instead to get a single chromeExtData instance directly (or First() if you’re 100% sure the ID exists).
• The big catch: Your chromeExtData class has a List<string> transp property, but your database stores this as a comma-separated string (A,X,Y). The default ExecuteQuery<T> mapping won’t automatically convert this string to a List<string> — you’ll need to handle that manually.

Improved Fetch Code #

Here’s a revised version that fixes these issues:

int ID = 5;
chromeExtData chromeData = null;

using (var dc = new DataContext()) 
{
    // Fetch raw data with transp as a string (using dynamic for simplicity)
    var rawResult = dc.ExecuteQuery<dynamic>(@"
        SELECT lname, fname, mname, numsr, sor, pob, birthday, cstatus, miscnumbers, transp 
        FROM [People] 
        WHERE [ID] = {0}", ID).FirstOrDefault();

    if (rawResult != null)
    {
        // Map to your chromeExtData instance, splitting transp into a List<string>
        chromeData = new chromeExtData
        {
            lname = rawResult.lname,
            fname = rawResult.fname,
            mname = rawResult.mname,
            numsr = rawResult.numsr,
            sor = rawResult.sor,
            pob = rawResult.pob,
            birthday = rawResult.birthday,
            cstatus = rawResult.cstatus,
            miscnumbers = rawResult.miscnumbers,
            transp = !string.IsNullOrEmpty(rawResult.transp) 
                ? rawResult.transp.Split(',').ToList() 
                : new List<string>()
        };
    }
}

For stricter type safety, you could create a temporary DTO class that matches the database column types exactly (with transp as a string), then map that to chromeExtData instead of using dynamic.

2. Reverse Operation: Saving chromeExtData Back to the Database #

To write your chromeExtData instance back (either insert a new row or update an existing one), you’ll need to convert the List<string> transp back to a comma-separated string, then execute an INSERT or UPDATE command.

Example: Update an Existing Row #

using (var dc = new DataContext()) 
{
    // Convert transp list to comma-separated string
    string transpStr = string.Join(",", chromeData.transp);

    // Execute update command
    int rowsAffected = dc.ExecuteCommand(@"
        UPDATE [People] 
        SET lname = {0}, fname = {1}, mname = {2}, numsr = {3}, sor = {4}, 
            pob = {5}, birthday = {6}, cstatus = {7}, miscnumbers = {8}, transp = {9}
        WHERE ID = {10}",
        chromeData.lname, chromeData.fname, chromeData.mname, chromeData.numsr, chromeData.sor,
        chromeData.pob, chromeData.birthday, chromeData.cstatus, chromeData.miscnumbers, transpStr, ID);
}

Example: Insert a New Row #

using (var dc = new DataContext()) 
{
    string transpStr = string.Join(",", chromeData.transp);

    int rowsAffected = dc.ExecuteCommand(@"
        INSERT INTO [People] (lname, fname, mname, numsr, sor, pob, birthday, cstatus, miscnumbers, transp)
        VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9})",
        chromeData.lname, chromeData.fname, chromeData.mname, chromeData.numsr, chromeData.sor,
        chromeData.pob, chromeData.birthday, chromeData.cstatus, chromeData.miscnumbers, transpStr);
}

3. A More Elegant, Long-Term Approach (Optional) #

If you plan to work with this table often, consider using LINQ to SQL Entity Classes to eliminate manual mapping:

1. Generate an entity class for your People table (use Visual Studio’s LINQ to SQL designer or the sqlmetal.exe tool).
2. Extend the entity class with a partial class to handle transp conversion automatically:

public partial class People
{
    [System.Data.Linq.Mapping.NotMapped]
    public List<string> TranspList
    {
        get 
        { 
            return !string.IsNullOrEmpty(transp) 
                ? transp.Split(',').ToList() 
                : new List<string>(); 
        }
        set 
        { 
            transp = string.Join(",", value); 
        }
    }
}

Now you can query and save with zero manual mapping:

// Fetch a row
var person = dc.People.FirstOrDefault(p => p.ID == ID);
List<string> transpList = person.TranspList;

// Update and save changes
person.TranspList = new List<string> { "B", "C", "D" };
dc.SubmitChanges();

This approach is cleaner, type-safe, and cuts down on repetitive code.

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