I have a need to allow users to build up a connection string and store it in a config file. Pretty simple need, and I would imagine rather standard. After all, I can't imagine any user I deploy to would have the same connection string I do, much less the same database. As it turns out, providing a robust piece of code to get this done will have to be all custom code. Given how common this scenario is, I'm more than a bit disappointed that MS just punted on this entire thing. Do they really expect end users to just wander around and edit config file XML syntax by hand and get it right? Do they really think not having the ability to do a "Test Connection" is a good idea?
I know about the support article from MS, but that uses interop back to plain ADO. I really can't believe they would not provide a managed code alternative that works with ADO.NET. Furthermore, the DbConnectionStringBuilder class in ADO.NET 2.0 starts to solve the problem, but stops short, like Frank Costanza on a drive through New York City. That class gives us some capability to build up the connection string, but none to visually build it up, much less edit a connection string in-place in the config file.
After way more time searching around the Internet than I care to admit, I'd like to think that I'm missing some simple link, but it looks like MS expects every single .NET developer to implement their own Connection String Wizard from scratch. Feel free to reply if you know of any pre-built solutions.
I wrote a VS.NET 2005 class library that I wanted to use from Delphi. Using COM Interop, this should be a breeze. Simply call "regasm MyLibrary.dll /tlb" and I should get a type library that I can import in Delphi. If that's all there was to it, then I wouldn't be blogging. 
The first problem I had was that when I ran this command, I got an error stating:
RegAsm error: Failed to load 'C:\MyLibrary.dll' because it is not a valid .NET assembly
This was weird, considering it was a .NET assembly. The problem was that the regasm being used was the one from the .NET 1.1 framework, so it choked on my .NET 2.0 compiled application. Simply running the VS 2005 Command Prompt and re-running regasm got the type library I wanted - or so I thought.
I marked the classes I wanted exported via COM with the [ComVisible(true)] attribute, but the only thing exported to the type library was classes with no members. In order to get the members exported, I ended up putting the [ClassInterface(ClassInterfaceType.AutoDual)] attribute on the class, too. This isn't the optimal approach, according to MSDN, so I'll end up extracting the members to an interface and exposing that instead, but at least I'm up and running for the moment.
I just came across this site today. It shows a bunch of different connection string sample for various databases and technologies. It also links back to downloads for the databases. All in all, a very focused and nicely done reference web site.
After writing a .NET application that used the DataGridView, I noticed that there was no ColumnType drop down to allow the user to easily edit a date/time field. At first, I came across a really good write up on MSDN on how to accomplish this. However, it was written in VB, so I couldn't use it as-is inside my C# application (yes, I could have turned it into an assembly, but then I wouldn't get the chance to learn how things work!). After converting the code to C#, I found that essentially the same thing is now in the core .NET documentation. The main guts of creating a custom column means you need to implement the IDataGridViewEditingControl interface.
After all of this, things went along fairly smoothly in sample applications. Then I plugged it into my real application and scratched my head as I started getting errors about the rowIndex being out of range (ArgumentOutOfRangeException) even though the DataGridView had 5 elements, and the rowIndex being passed in was 0. It turns out this was happening whenever I would check the Value of the cell because the RowIndex was set to -1. This led me to the .NET documentation which talked about the concept of shared rows for the DataGridView. The one sentence summary on shared rows is that they are used in a DataGridView to ease memory use for a bunch of rows that are very similar. There are all sorts of internal rules that determine whether a row can be shared or not, but the long and short of it is that my DataGridView was using shared rows, as was evident from the RowIndex property being set to -1. Since the samples don't take this into account, I changed the code, and now everything is working fine. You can download my modified class to see how I made things work. If you have any feedback on this code, please feel free to leave a comment.
Other references:
I'm using the Enterprise Library Data Access Application Block in order to use a variety of different databases on the backend without changing my code. One thing that recently came up was how to properly use a TIMESTAMP field in MSSQL. I don't want to use SqlDbType.Timestamp, as that refers to a specific DBMS implementation, and there is no DbType.Timestamp at the base class level for me. The following code snippet does work, though, so use this to get unstuck:
db.AddOutParameter(cmd, "@newLastChanged", DbType.Binary, 8);
Time to confess: I'm a fan of data binding. I've loved it ever since Delphi 3 made it workable by introducing TClientDataset. It's powerful, but makes application code fairly simple. Yes, it can be - and often, is - abused when used incorrectly, but overall, it's a good concept.
I recently worked on getting data binding in a .NET 2.0 application to work with a lookup combobox. The concept is pretty straight-forward (and very analogous to Delphi's TDBLookupCombobox). Databinding in .NET is pretty impressive, and a step forward from where Delphi currently is. For example, you can bind straight to business objects as opposed to being required to consume database resources/components to complete data binding. One big snag was in getting the lookup relationship to respect null values properly. For example, assume you have 2 very simple classes, Order and Supplier, that are bound via BindingSource components. Furthermore, assume you have your data binding set up properly to have a ComboBox set with DataSource=orderBindingSource, DisplayMember=Name, ValueMember=SupplierID, and nameComboBox.DataBindings.Add(new Binding("SelectedValue", this.orderBindingSource, "SupplierID", true)); (see this reference or this reference for more details). Lastly, if you populate your objects as in the example below, you will notice that the original display of the combobox is populated with the first item in the supplier list. If you scroll to the next record and then back to the first record, the combobox will display properly.
private void btnPopulate_Click(object sender, EventArgs e)
{
orderBindingSource.Add(new Order(1, "Test1", null));
orderBindingSource.Add(new Order(2, "Test2", 1));
supplierBindingSource.Add(new Supplier(1, "Supplier X"));
supplierBindingSource.Add(new Supplier(2, "Supplier Y"));
}
There are a couple of ways to fix this behavior. First, you could just reverse the order and populate the supplierBindingSource first. Alternatively, you can leave the population order alone and just call ResetBindings() after you're done populating all of the data.
private void btnPopulate_Click(object sender, EventArgs e)
{
orderBindingSource.Add(new Order(1, "Test1", null));
orderBindingSource.Add(new Order(2, "Test2", 1));
supplierBindingSource.Add(new Supplier(1, "Supplier X"));
supplierBindingSource.Add(new Supplier(2, "Supplier Y"));
orderBindingSource.ResetBindings(false);
}
One other quick note. When you want to set the Order.SupplierID field to null, you cannot write code like this:
SupplierNameCombo.SelectedValue = null;
Instead, you can either set the SelectedIndex to -1, or (IMO) more elegantly, write code like this:
SupplierNameCombo.SelectedValue = DBNull.Value;
I've been writing a lot of test cases and projects in VS.NET lately. (Ed note: No, this doesn't mean I've abandoned Delphi. I still love Delphi, and use it as my full-time tool. However, I always have - and always will - keep abreast on the entire world of development tools.) One thing that I thought would be cool to have would be a simple expert - or "addin", in VS.NET parlance - to ZIP up all of the source files automatically. I thought this would be a good opportunity to learn about the addin framework for VS.NET, but like a good developer, I thought I'd check to see if anyone else has written this already. In short order, I found an article detailing a tool named ZIPStudio. That article also provides source and a setup msi file to install the addin, but it only works for older versions of VS.NET. A few clicks later, I found the author's web site with an update of ZIPStudio that will work in VS.NET 2005.
After installing the tool, and testing it on a few solutions, it appears to be exactly what I was after. Highly recommended for those of you who want to move project source around between machines.
I just spent some time checking out how SqlDependency works. SqlDependency is an ADO.NET 2.0 class that is a high level wrapper for detecting changes in a database and getting automatic notification back to your application. This technology is very useful for caching rarely changing data. Essentially, you register interest in a query, and if something changes that would make that query different, you get a callback to your application to let you know that you need to update your local cache.
All in all, it's a pretty mixed review right now. It's pretty stringent on the requirements, not terribly efficient, and probably not the most practical thing for a multi-tier system. However, what it does, it does well. Here is my quick list of things learned:
- You need to make sure that the DB is compatible with MSSQL2005. You can set the compatibility level in SQL Server Management Studio by selecting the Properties page of the database.
- You need to SET ENABLE_BROKER on the database, along with making sure other OPTIONS are in compliance, to allow Query Notifications to work.
- Be sure to prefix the table in the select with the schema information (e.g. select CategroyId, CategoryName from dbo.Categories). Also be sure to check out the documentation for other limitations on queries.
- The OnChange event fires once and then gets consumed, so you need to hook up the event again after it fires.
- OnChange runs in a separate thread, so be sure to update the UI properly.
- The Queues and Services in MSSQL 2005 are not being automatically deleted when you call SqlDependency.Stop. This is a known issue, and the query to delete the extra objects can be found in this discussion.
- It appears that the notification fires if ANY field in the table tied to the query is changed - even if that field is not part of the query you registered for notification. This seems like a bug to me.
- Query Notifications are not very granular in reporting WHAT changed. Right now, this essentially forces you to invalidate an entire cache as opposed to just updating individual elements that have changed.
- SqlDependency is the high-level wrapper. You can also use the low-level SqlNotificationRequest class to get more control over the process, if you so choose.
- For ASP.NET, you will most likely use the SqlCacheDependency class.
- Oracle also has support for this technology. Check out the article, Following the changes. Part I and Part II.
Other resources:
The following code snippet shows how to pick up the SQL that will be generated by a given LINQ query. The DataContext.GetQueryText() method makes this very easy. However, what we REALLY need is an easy way to go the other way, too. For example, given an adhoc string, generate the DLINQ query to allow further processing. I've looked through the Interactive Query sample, but that's not exactly what I want. The 2 main things wrong with it are that the baseQuery is already defined as an IQueryable variable, as opposed to a simple string; and the code to build up the expression tree is much too complex for every day needs. If we had a generic string to IQueryable method, we wouldn't need all of the expression code.
static void Main(string[] args)
{
string s;
var q = from e in db.Employees select e.FirstName;
s = db.GetQueryText(q);
Console.WriteLine(s);
Console.ReadLine();
}
|