DataAdapters against Typed DataSets = SQL Schema nightmares...

Question

I have seen many references stating that TableAdapters are weak and silly, and that any real dev would use DataAdapters. I don't know if that is true or not, but I am exploring the matter, and stressing out over how bad this whole 'DataAdapter/TableAdapter against a Typed DataSets' smells.

Let me try to explain...

Suppose I have my Typed DataSet defind in the xsd file, and now I'm ready to create a DataAdapter in code, against that schema...(By the way, I am using OleDb to access free-standing .dbf files in a folder... No SQL server stored procedures to call here, just plain old raw tables, ready for action.)

From my studies so far, here is how I see the DataAdapter used in conjunction with a Typed DataSet. Tell me if I am wrong. (Then I have my big complaint / question at the end.)

public DataTable GetJobsByCustomer(string CustNo)
{
    OleDbConnection conn1 = new OleDbConnection(dbConnectionString);
    conn1.Open();

    LMVFP ds1 = new LMVFP(); //My Typed DataSet

    string sqlstring = @"SELECT act_compda, contact, cust_num, est_cost, invoiced, job_hours,
                        job_invnum, job_num, job_remark, job_start, mach_cost, mat_cost, mat_mkup,
                        p_o_num, priority, quote_no, quoted_by, ship_date, ship_info, shop_notes, status, total_cost
                        FROM job_info
                        WHERE (cust_num = ?) AND (status = 'A')
                        ORDER BY priority";

    OleDbDataAdapter JobsAdapter = new OleDbDataAdapter(sqlstring,conn1);
    JobsAdapter.SelectCommand.Parameters.Add("?", OleDbType.VarChar,6).Value=CustNo;

    JobsAdapter.Fill(ds1, "Jobs"); // A table schema in the Typed DataSet

    return ds1.Jobs;

}

Is that how it goes? It does work, so that's good. And indeed the strongly typed behavior is great.

Now, my gripe.... You mean to tell me that I've got maintain the same exaxt SQL syntax in my DAL method (GetJobsByCustomer) to match the schema of the table in the xsd? It's crazy to have so much maintenance and dis-join between my hand-coded SQL and the xsd schema. There's no error cathing at all, since you are writing a text string!! You get to find out at run time if it will work.

When your typing all the SQL in code, it's terrible to have to look back and forth to keep your coded SQL in synch with the xsd table schema.

Surely I am missing something.

What a farse. The typed dataset works with beatiful intellisense and all, because it's generated from the schema, but when it comes down to it, it's just a pain to may to write SQL that matches the Typed schema. All they've done is move the headache to a new area.

Please tell me I am missing sometehing here that will make this much better.

Answer 1

I don't believe you're missing anything; maintaining this type of code is never fun. Thankfully we now have LINQ to SQL and Entity Framework which can both reduce the amount of manual code maintenance necessary to keep your model objects in sync with your database.

Answer 2

I second Adam's appreciation for LINQ to SQL and EF, but I'm thinking this wouldn't be an option for you (yet) because of the lack of support for third-party DBMS. On the other hand, a third-party ORM (e.g. NHibernate) may be an option.

Perhaps I don't pay enough attention, but I'm not aware of any good reason to avoid TableAdapters vs DataAdapters. Do you have a link or two?