views:

784

answers:

2

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.

A: 

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.

Adam Alexander
A: 

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?

Daniel Pratt
I think the fuss with TableAdapters had to do with Joins, maybe? Can a TA only write to one table, but a DA can handle joined tables?
MattSlay
Here's some poop about why you can't easily use Joins in TableAdapters, if updating is required: http://www.asp.net/learn/data-access/tutorial-01-cs.aspx Read all the crap in step 5. Smell bad?
MattSlay
Both DataAdapters and TableAdapters are designed to work with one "DataTable". Whether you can update multiple DBMS tables is a function of how the adapter is configured and/or the capabilities of the DBMS.
Daniel Pratt
@MattSlay: The article references an issue with TableAdapters, but I don't see how using a DataAdapter would make it better.
Daniel Pratt
@Daniel: Agreed. Neither one is painless in this regard.
MattSlay