Difficulty finding a good SqlDataAdapter tutorial / getting my code to work

Question

I asked a question before about how I can do a simple insert/update from a CSV file into a database. I was given some code that I have adopted here. The recordList is the parsed CSV file as a collection:

SqlDataAdapter dataAdpater = new SqlDataAdapter(
    "SELECT * FROM Cats WHERE UniqueCatName = @UniqueCatName", "data source=localhost;initial catalog=Kitties;integrated security=True;MultipleActiveResultSets=True;");

DataTable testTable = new DataTable();

  dataAdpater.Update(testTable);

foreach (var record in recordList)
{
    dataAdpater.SelectCommand.Parameters.AddWithValue("@UniqueCatName", record.UniqueCatName);

    int rowsAdded = dataAdpater.Fill(testTable);
    if (rowsAdded == 0)
    {
        testTable.Rows.Add(
            record.UniqueCatName,
            record.Forename,
            record.Surname
            );
    }
    else
    {

    }
}

dataAdpater.Update(testTable);

I'm kinda going in blind, I've looked at a ton of tutorials but I couldn't find one that clearly demonstrated how to use the SqlDataAdapter for both adding and updating.

From what I read, you have to specify an update command and an insert command? So I'm not -entirely- sure what's going on with the code above, but I guess it is able to do a simple insert without me giving extra instructions.

I couldn't figure out what to put in the 'else' bit though that the answerer to my previous question gave. I need to retrieve and update the particular row, but I don't know how to do that. Any ideas?

Personally I'd scrub the data adapters and build the sql myself... parameterised obviously! — Liath, Jan 21 '14 at 10:59
I was led to believe by googling that data adapters will do the operations in bulk fashion - so if the CSVs are massive, it will insert/update quite fast. Otherwise I'd just use Entity Framework to do them one by one. — NibblyPig, Jan 21 '14 at 11:00
I've not used them extensively (hence why I'm not attempting an answer) but I've always found them to be frustrating (and have come up with exactly the sort of issues you're facing). At the end of the day all they're doing is dynamically building insert statements - you can do that! If you go down this route make sure you do more than one insert per SqlCommand and batch appropriately — Liath, Jan 21 '14 at 11:04
According to http://stackoverflow.com/questions/13962704/how-sqldataadapter-works-internally?rq=1 it uses an internal batching system. I've done some stuff updating one by one and it takes quite a long time, enough that I'm willing to embrace the headache on this one :D — NibblyPig, Jan 21 '14 at 11:16

score 1 · Accepted Answer · answered Jan 21 '14 at 13:18

If anyone is interested, I have done it. This is the unrefactored version. It inserts if they don't exist, and updates existing records if they already do. I hope it helps someone out in the future:

SqlDataAdapter dataAdpater = new SqlDataAdapter(
    "SELECT * FROM Cats WHERE URN = @URN", "data source=...");

dataAdpater.InsertCommand = new SqlCommand("INSERT INTO Cats VALUES (@URN, @Forename, @Middlename, @Surname)", new SqlConnection("data source=..."));

dataAdpater.UpdateCommand = new SqlCommand("UPDATE Cats SET Forename=@Forename, Middlename=@Middlename, Surname=@Surname WHERE URN=@URN", new SqlConnection("data source=..."));

DataTable testTable = new DataTable();

dataAdpater.Update(testTable);

dataAdpater.SelectCommand.Parameters.Add("@URN", SqlDbType.NVarChar, 256, "URN");

dataAdpater.InsertCommand.Parameters.Add("@URN", SqlDbType.NVarChar, 256, "URN");
dataAdpater.InsertCommand.Parameters.Add("@Forename", SqlDbType.NVarChar, 256, "Forename");
dataAdpater.InsertCommand.Parameters.Add("@Middlename", SqlDbType.NVarChar, 256, "Middlename");
dataAdpater.InsertCommand.Parameters.Add("@Surname", SqlDbType.NVarChar, 256, "Surname");

dataAdpater.UpdateCommand.Parameters.Add("@URN", SqlDbType.NVarChar, 256, "URN");
dataAdpater.UpdateCommand.Parameters.Add("@Forename", SqlDbType.NVarChar, 256, "Forename");
dataAdpater.UpdateCommand.Parameters.Add("@Middlename", SqlDbType.NVarChar, 256, "Middlename");
dataAdpater.UpdateCommand.Parameters.Add("@Surname", SqlDbType.NVarChar, 256, "Surname");

foreach (var record in recordList)
{
    dataAdpater.SelectCommand.Parameters["@URN"].Value = record.URN;

    int rowsAdded = dataAdpater.Fill(testTable);
    if (rowsAdded == 0)
    {
        var newRow = testTable.NewRow();

        newRow["URN"] = record.URN;
        newRow["Forename"] = record.Forename;
        newRow["MiddleName"] = record.MiddleName;
        newRow["Surname"] = record.Surname;

        testTable.Rows.Add(newRow);
    }
    else
    {
        foreach (DataRow row in testTable.Rows)
        {
            if (row[1].ToString() == record.URN)
            {
                row["Forename"] = record.Forename;
                row["MiddleName"] = record.MiddleName;
                row["Surname"] = record.Surname;
            }
        }
    }
}

dataAdpater.Update(testTable);

Note: I had to add an index to my URN field or the update method took FOREVER to work. Inserting 13,000 records took 38 seconds, and updating all 13,000 records took 55 seconds. — NibblyPig, Jan 21 '14 at 15:41

score 0 · Answer 2 · answered Jan 21 '14 at 11:30

You could start off with something like this which will add all records from RecordList:

string connectionString = ....;
SqlDataAdapter  adapter = new SqlDataAdapter("Select * From Cats", connectionString);

// The command builder will generate the Add, Update and Delete commands
// based on the select command
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter);

DataTable testTable = new DataTable("Cats");
adapter.Fill(testTable);  // retrieve all existing rows

// Add each record from recordList
foreach (var record in recordList)
{
    // TODO - Handle duplicates
    testTable.Rows.Add(
        record.UniqueCatName,
        record.Forename,
        record.Surname
    );
}

adapter.Update(testTable);

Difficulty finding a good SqlDataAdapter tutorial / getting my code to work

2 Answers2