Rants Tagged with “ADO.NET”
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...
In response to your rant about the inelegance of the CLR's null implementation:
I think you're off track here. ADO.NET mirrors the same type dissonance that exists between SQL Datatypes and most programming types. While Nullable<> will help, it isn’t a panecea. We will still need to check for null (or DbNull) values. In your example, I would have:
DataColumn dc = new DataColumn() ;
dc.Name = "TotalCost" ;
dc.type = typeof(money) ;
dc.Expression = "price * quantity" ;
Because this value could (DbNull == true), the resulting value of (price * NULL) is NULL just like in SQL. You could get around this by providing default values, but that just extends the problem.
This seems like an elegant solution…just check for IsNull first:
if (dataRow[“price”] == DbNull.Value)
{ // …
}
Or do it in your expression:
dc.Expression = “isnull(price, 0, price) * isnull(quantity, 0, quantity)”;
Does this make any sense?
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
I've been talking to
Microsoft's WebData/Visual Studio .NET teams about what I don't like about the Typed
DataSet Generator in 1.x. I've been hopeful that it will be fixed in 2.0. The good news is that its looking good that it might happen in the next Beta drop. I'll keep you updated!
I have finally gotten sick of GotDotNet's flakyness and moved the source and installer to my website. I am looking for a new home and may end up at SourceForge. If you have had any problems with the installer or finding the source, please visit
http://wildermuth.com/powertoys.
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 was taking a refresher MCSD test today to get ready to take one of the tests and came upon a question that is wrong. But it does infer that there is some confusion about how transactions are propogated to commands...or may be evidence that it is a bug. For example:
SqlConnection conn = null;
SqlTransaction tx = null;
try
{
// Create a new Connection
conn = new SqlConnection("Server=.;Database=Northwind;Integrated Security=true;");
// Open the Connection
conn.Open();
// Start a Transaction and create a new Command
tx = conn.BeginTransaction();
using (SqlCommand cmd = new SqlCommand())
{
// Set the Connection to the command
cmd.Connection = conn;
// NOTE: I do not explicitly set the TX to the Command
//cmd.Transaction = tx;
// Insert new values and execute it
// (within the transaction)
cmd.CommandText = @"INSERT INTO Customers (CustomerID, CompanyName)
VALUES ('ZZZZY', 'My New Company');";
cmd.ExecuteNonQuery();
// Insert new values, but the table name is wrong
cmd.CommandText = @"INSERT INTO Companies (CompanyID) VALUES('ANother Company')";
cmd.ExecuteNonQuery();
// We should never get here since the query is wrong
tx.Commit();
}
}
catch (Exception ex)
{
// Rollback the tx if error'd
if (tx != null) tx.Rollback();
}
finally
{
tx.Dispose();
// Close the connection just in case
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
This code fails because I do not explicitly set the transaction to the command. Unfortuately, you must set the connection and the transaction. This seems like a bug because you cannot execute a command on the connection (that has an pending transaction) without throwing an error.
The practice test asked me to specify a single missing line of code, so I could either set the command's connection property or it's transaction property, but not both. I suspect that there is confusion inside of MS about what is the expected behavior. But for now, I will just continue to set both and know that the test is wrong....
Chris Sells has just built a good
ADO.NET test application to access text files (comma delimited). I only wish I had done it first. It looks great!
