Rants Tagged with “DataSets”
<< < 1 2 (Total Pages: 2/Total Results: 17)
I always forget this blog this, but when I am doing a database project using Typed DataSets, I almost always use a Component Surface to build my DataAdapters interactively. For example:
Here I add a component to my project:
Once I do this, I have a component surface to drop Data Adapters onto:
Now I can drop a Data Adapter (doesn't matter which kind) onto the surface to start the DataAdapter wizard (don't grimace, it's quite safe):
Within this wizard you can specify the connection, the SQL (or more appropriately, the stored procedures, or with SQL Server you can even have them make you Stored Procedures). Once you press finish, you'll have a data adapter and a connection string in on your design surface:
Next you can modify the properties of the data adapter to make it publically available (I'll show you what that means in just a minute):
Then you can specify the TableMappings (and consequently the Column Mappings):
This brings up the Mapping dialog where you can use a Typed DataSet to suggest the mappings:
Now you have a class that contains the DataAdapter. You would use it like so:
Adapters adapters = new Adapters(); adapters.sqlDataAdapter1.Fill(myDataSet);
Making the adapter public lets us call it from an instance of the component class. If you want to use my DataSet updater class (see http://wildermuth.com/content.aspx?id=rantview&rantid=156), you can use each of the adapters in series.
When I use this approach, I usually create an adapter for each type of Get operation I have (e.g. GetCustomers, GetCustomerByName, GetCustomerByID, GetCustomerByRegion). I then create an Adapter for each Table that will be updated. This allows my "Get" adapters to use batch queries to streamline the retrieval.
Hope this help...
After reading Rocky's blog about DataSets and Web Services, I am afraid that he is falling into the same trap that other's have (including the emminently qualified Tim Ewald) with respect to DataSets. DataSets work well in Web Services but not by default. As I mentioned in:
http://wildermuth.com/content.aspx?id=rantview&rantid=7
The reason I like DataSets across Web Services so much is simple. On my side of the interop wall, I can treat them like a set of data-centric objects. But when I expose them via Web Services, I can treat the entire DataSet as a document. I like doc-literal web services. The magic here lies in the fact that the schema of the DataSet can be simply described with XSD. In fact, for non-typed DataSets that can be described inline with WriteXmlSchema() (so it can be interpreted on the other side of the interop wall).
For typed DataSets it gets even better. An XSD is the source of Typed DataSets, so exposing your XSD to describe your 'document' is a natural way of doing things. In fact, if you're not using ?wsdl for your .NET web services (which you shouldn't!), you can refer to the .xsd to describe the types that your web service expose.
I am passionate about this because creating web services to banty around your business objects is fine, but seems wholly unnecessary. I hope I can get Rocky to see the light.
Addendum:
I love Sahil's discussion of this too:
http://dotnetjunkies.com/WebLog/sahilmalik/archive/2005/01/23/47832.aspx
For some time now I've been pushing the idea of doing DataSet updates using DataAdapters that use a 1-to-1 relationship between DataAdapter and logical data elements (e.g. Tables or Stored Procedures usually). This is especially true when you are dealing with related tables in DataSets (the sweet spot for DataSets IMHO). I've continually forgotten to post this code that I use to do these updates. The idea of this code is for the user to provide arrays of Tables and DataAdapters that imply the order of the updates. For example
// Array of DataTables from a Typed DataSet
DataTable[] updateTables = new DataTable[] {
dataSet.Customers,
dataSet.Orders,
dataSet.OrderDetails,
dataSet.Products
};
// Array of DataAdapters
DataAdapter[] updateAdapters = new DataAdapter[] {
customerAdapter,
orderAdapter,
orderDetailAdapter,
productAdapter
};
// Call the Update Method
UpdateDataSet(updateTables, updateAdapters);
This implies the order so that the helper function can do the right thing which is to delete bottom up, and insert/update top down:
// Enforces that updates will be written in the right order.
internal static void UpdateDataSet(DataTable[] tables, SqlDataAdapter[] adapters)
{
// Validate the input
if (tables.Length == 0 || adapters.Length == 0)
{
throw new ArgumentException("You must send at least one table and adapter");
}
if (tables.Length != adapters.Length)
{
throw new ArgumentException("The number of tables and adapters must be identical");
}
// Disable Constraints until end of process
tables[0].DataSet.EnforceConstraints = false;
using (SqlConnection conn = DataFactory.GetConnection() as SqlConnection)
{
SqlTransaction tx = null;
// Try and update the datasets with a transaction
try
{
// Open the connection
conn.Open();
// Start a transaction
tx = conn.BeginTransaction();
// Set the Upper and Lower Bounds
int min = tables.GetLowerBound(0);
int max = tables.GetUpperBound(0);
// Go through all the tables, and delete the deleted items (in reverse order)
for (int x = max; x >= min; --x)
{
DataRow[] updatingRows = tables[x].Select("", "", DataViewRowState.Deleted);
if (updatingRows != null && updatingRows.Length > 0)
{
adapters[x].DeleteCommand.Connection = conn;
adapters[x].DeleteCommand.Transaction = tx;
adapters[x].Update(updatingRows);
}
}
// Go through all tables and update/insert the items (in forward order)
for (int x = min; x <= max; ++x)
{
DataRow[] updatingRows = tables[x].Select("", "", DataViewRowState.Added | DataViewRowState.ModifiedCurrent);
if (updatingRows != null && updatingRows.Length > 0)
{
adapters[x].InsertCommand.Connection = conn;
adapters[x].InsertCommand.Transaction = tx;
adapters[x].UpdateCommand.Connection = conn;
adapters[x].UpdateCommand.Transaction = tx;
adapters[x].Update(updatingRows);
}
}
// Commit the transaction
tx.Commit();
// Mark all the items as accepted
for (int x = min; x <= max; ++x)
{
tables[x].AcceptChanges();
}
}
catch (Exception ex)
{
if (tx != null) tx.Rollback();
throw new ApplicationException("Failed to Update the database", ex);
}
finally
{
if (conn.State == ConnectionState.Open) conn.Close();
if (tx != null) tx.Dispose();
// Enable Constraints until end of process
tables[0].DataSet.EnforceConstraints = true;
}
}
}
This will eventually make it into the PowerToys project, but I haven't had time to refactor it yet. HTH
Recently I was talking with Rocky Lhotka and he said something interesting:
“Just when we got good at Client-Server, they switched things and had us doing n-Tier applications. Just when we got good at n-Tier development, internet applications took off.”
In my opinion he is right. It is interesting because client-server and n-Tier applications still exist, especially in enterprise development. I think we're good at client-server and n-Tier. The problem is that I think that much of browser based development attempts to apply n-Tier development.
What do I mean? In simple words, the web server is the middle tier. The browser is the client tier.
The idea behind n-Tier development is being able to separate the data work into a tier that can be scaled out. Luckily we know how to scale out webservers (into farms). Since we are securing webservers, we can isolate security issues from the client...just like we've done in n-Tier development.
I've had time lately to think about the nature of data in development lately. I've been talking with Rocky Lhotka and Michael Earls about it (as well as number of others) about the issues with dealing with data in applications.
The first camp is all about writing Business Objects. In this camp, you write classes that encapsulate the data, the data access and business rules about that data. This camp was the way to do it for years now. It proliferated in the Client-Server and n-Tier architecture camps.
Rocky Lhotka espouses his excellent CSLA.NET framework. If you are going the business object road, I wholeheartedly recommend it. It is designed around allowing object to exist locally or on separate servers through remoting.
The second camp is that data is all about data, so data is just a message to some system. With the excitement around Service Oriented Architectures (SOA), this view is starting to prevail.
Somewhere in the middle is where I sit. I think that data and business rules belong together, but the data access can be disconnected from it. So this is the interesting fact in my opinion...there are reasons to have the data access separated from the end users' machines (so perhaps remote data access), but once in the client, you want to have the business logic (and schema) as close to the client as possible. The closer it is to the client, the better it should scale. I don't like to see finely grained data access happening. Even in client-server apps, the more coarsely grained the data access, the better it should scale IMHO.
There is more I want to say on this, so stay tuned. I will be posting every day about this.
Tomorrow: “n-Tier, gone tomorrow”
I got to play with an Itanium 2 Box at the PDC today. Instead of following their script, I did what I've wanted to do for months...creating a huge DataSet. They had an interesting setup. You used a Pentium 4 box to develop code and then Terminal Service'd into a sixteen-way Itanium 2 machine to run the code. The 64 bit JIT's the IL to 64 bit code from the same assembly that the 32 bit JIT did to create the 32 bit code.
I say some interesting results:
- Created a 1 Gig DataSet on the Pentium 4 machine. Worked fine.
- Running the same test on the Itanium 2 worked just fine, faster but fine.
- Created a 3 Gig DataSet on the Pentium 4 machine caused OutOfMemoryExceptions. That's what I expected.
- On the Itanium 2 machine it worked!
With some encouragement by the Microsoft staff, we tested 4, 8 and 16 gig DataSets. Wahoo..it worked fine. There was a small problem with some internal issues with multiple threads and the DataSet, but that's to be expected. The 64 bit CLR is still pretty early on.
What really impressed me was how seemless the two worlds were. The only thing I really did that was specific to the 64 bit code, we to use UInt64's for some memory counters, but that was to be sure that I didn't overflow the numbers.
I really want to try this stuff on an AMD 64...maybe tomorrow!
Till tomorrow....
I have been thinking a lot about how Typed DataSets are generated and was spelunking through the code again when it got me thinking. The Typed DataSet generator doesn't really generate the code based on the .xsd, but on the DataSet. It simply loads the .xsd into a DataSet then interrogates the DataSet directly for everything (tables, columns, relationships, constraints). So if the Typed DataSet Designer cannot handle something (like relationships *without* constraints, see below), but the DataSet schema allows it...simply create the DataSet and save the .xsd file to see what it produces! This gets around some fundamental problems with the designer. It does require you start looking and understanding .xsd, but it is a useful skill to have anyway...right?
So my first relevation was how to add unconstrained relationships (no foreign key constraint, simply a way to navigate the data). Since the designer does not allow this, I looked at the .xsd and found that the DataSet handles this with a schema annotation:
<xs:schema>
<xs:annotation>
<xs:appinfo>
<msdata:Relationship name="ta2t"
msdata:parent="titleauthor"
msdata:child="titles"
msdata:parentkey="title_id"
msdata:childkey="title_id" />
</xs:appinfo>
</xs:annotation>
</xs:schema>
The five pieces of data in the msdata:Relationship element are the four pieces of data required when setting up a relationship. Pretty simple huh!
It makes me start to think of other DataSet allowable things that the Designer doesn't know about.
