John Papa [MVP C#]

March 2005 - Posts

• Don't forget to Close that ADO.NET Connection

  Here's a quick tip in response to some questions I recently have been asked by several readers & bloggers: When using the Command.ExecuteReader method to get a DataReader, pass it the System.Data.CommandBehavior.CloseConnection argument. This argument closes the Connection when the DataReader is closed. Thus, this can save you a line of code to close your Connection. OK, 1 measely line isn't much to talk about, but then again it can solve another problem that we all run into from time to time: cleaning up our room. No, wait, I mean cleaning up our resources. It is easy to forget to close the connection when you are done with it. If you run the following code in debug mode, you will see the state of the connection at various points. The key here is that as soon as the DataReader is closed, the connection is also closed. (The code sample assumes that you have a button named Button1 and a TextBox named Text1 in an ASP.NET Web Form.) This is not rocket science, but then again, it can help you code better! CommandBehavior.CloseConnection private void Button1_Click(object sender, System.EventArgs e) {     string sConnString = "Server=localhost;Database=Northwind;Integrated Security=True";     string sSql = "SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryName";     SqlConnection cn = new SqlConnection(sConnString);     SqlCommand cmd = new SqlCommand(sSql, cn);     cn.Open();     System.Diagnostics.Debug.WriteLine("[Pre Loop] - Connection State is " + cn.State.ToString());     SqlDataReader rdr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);     while (rdr.Read())     {         System.Diagnostics.Debug.WriteLine("[Looping] - Connection State is " + cn.State.ToString());         TextBox1.Text += rdr["CategoryName"].ToString() + Environment.NewLine;     }     rdr.Close();     System.Diagnostics.Debug.WriteLine("[Post Loop] - Connection State is " + cn.State.ToString()); } Posted Mar 31 2005, 07:59 PM by John Papa with 7 comment(s) Filed under: ADO.NET

• ADO.NET 2's DataTable.Merge

  OK, so its not ground breaking, earth shattering nor will it solve world hunger ... but I am pretty stoked over the addition of the Merge method to the DataTable object in ADO.NET 2. It has always been an annoyance of mine that when I want to merge a DataTable into another one that I have to have a DataSet to perform the operation. What if I don't have nor want a DataSet? Well, in ADO.NET 1.x you have to create a DataSet anyway. I know its just an extra line of code or 2, but its the point of creating an object that really serves no purpose in my context that bothers me. OK, so this is not a huge issue by itself but again I feel like it strays from sound development practices. If the DataSet is already there ... fine. In the following code sample I set up 2 DataTable objects manually and then merge them. Unfortunately, I have to create a DataSet to perform the merge. ADO.NET 1.1 - DataSet.Merge private void Button1_Click( object sender, System.EventArgs e) {     // Set up a DataTable     DataTable oDt= new DataTable("Colors");     oDt.Columns.Add("ColorID", System.Type.GetType("System.Int32"));     oDt.Columns.Add("ColorName", System.Type.GetType("System.String"));     oDt.PrimaryKey = new DataColumn[]{oDt.Columns["ColorID"]};     oDt.Rows.Add(new object[]{1,"Red"});     oDt.Rows.Add(new object[]{2,"Teal"});     oDt.Rows.Add(new object[]{3,"Yellow"});     oDt.AcceptChanges();     // Set up a another DataTable that I will use to Merge     DataTable oDtMoreColors= new DataTable("Colors");     oDtMoreColors.Columns.Add("ColorID", System.Type.GetType("System.Int32"));     oDtMoreColors.Columns.Add("ColorName", System.Type.GetType("System.String"));     oDtMoreColors.PrimaryKey = new DataColumn[]{oDtMoreColors.Columns["ColorID"]};     oDtMoreColors.Rows.Add(new object[]{1,"Red"});     oDtMoreColors.Rows.Add(new object[]{2,"Blue"});     oDtMoreColors.Rows.Add(new object[]{4,"Green"});     oDtMoreColors.AcceptChanges();     // Create a DataSet so I can merge the DataTable objects     DataSet oDs = new DataSet("WhyOhWhy");     oDs.Tables.Add(oDt);     oDs.Merge(oDtMoreColors); } In ADO.NET 2 I can skip creating the DataSet and instead use the following line of code. ADO.NET 2 - DataTable.Merge     oDt.Merge(oDtMoreColors); While some topics like MARS are grabbing more of the press in ADO.NET 2, tweaks like the DataTable Merge method are flying under the radar. After all, I use these types of features all the time so if we can make it work better, then I am all for it. Posted Mar 30 2005, 08:45 PM by John Papa with 9 comment(s) Filed under: ADO.NET

• Reflector is Cool!

  I ran into a situation today where I wanted to know why ADO.NET's DataSet.Merge method worked the way it did. Then it occurred to me that I could use reflection to find out. Using the Reflector software found on Lutz Roeder's site I examined the DataSet.Merge method and got my answers. I know this software has been out there a while, but I do not often find a reason to use it. But when I do have a need (such as today), it is a very nice tool to have. Saved me a bunch of time and effort. You can jump into nested methods by clicking the links in the right hand pane. It is a pretty basic but very cool tool. Follows the KISS methodology (Keep It Simple, Stupid!), too. Special kudos to my friend/colleague Lloyd who reminded me of this tool just the other day. Posted Mar 29 2005, 11:00 AM by John Papa with 1 comment(s)

• Sharing Mistakes

  With VB 6 and ADO 2.x I can't tell you how many times I wrote the following type of code and only realized what I had done after I had pressed F5: The Infamous Infinite Loop Blunder '--- Using VB 6 and ADO 2.x oRs.Open Do While Not oRs.EOF     Debug.Print oRs(0) Loop Usually the moment after I ran this code I would realize that I was in an infinite loop. Then I would quickly put in the oRs.MoveNext just before the end of the loop and all would be well. Fortunately, in ADO.NET I no longer have this problem thanks to the DataReader and how it reads a record and moves the cursor to the next position all in one fell swoop. So .NET saved me from one of my most infamous blunders. Using C# and ADO.NET SqlDataReader oDr = oCmd.ExecuteReader(); while(oDr.Read()) {     Console.WriteLine(oDr[0]); } But of course I didn't get off that easy. I now have all new mistakes to make with .NET. My most common mistake these days occurs when I declare a variable inside of a "try" block and then use it outside of the "try" block. For example, I'll do something along the lines of the foo method below: Oops! public int foo(int y, int z) {     try     {         int x = y * z;     }     catch     {         // catch it here     }     // this will not compile!     return x; } But at least with this mistake I catch it when I build my C# project. Then I move the declaration of the variable x outside of the try block and all is well. OK, so now that I have opened up and shared some of my embarassing blunders, it's time for you to share some of your common blunders. Don't be shy! Posted Mar 28 2005, 05:11 PM by John Papa with 7 comment(s) Filed under: ADO.NET

• Heading to Boston

  Just confirmed that I'll be at VSLive in Boston this June. If you plan on attending VSLive, drop by. I'll be presenting 2 topics: Get Ready for ADO.NET 2 (5:45 pm to 6:45 pm, Wednesday, June 29, 2005) Data access using ADO.NET is at the core of most .NET enterprise applications. While ADO.NET 1.x is powerful, its successor ADO.NET 2 contains several new features that are can benefit an application. I'll review these new features and how to take advantage of them including binary serialization of DataSets, new features and objects built into the DataSet and enhancements to strongly typed DataSets. Lightweight Promotable Transactions using System Transactions and ADO.NET 2 (2:00 pm to 3:00 pm, Thursday, June 30) The.NET Framework v2 includes the System.Transactions namespace that enhances transactional support for managed code. I’ll discuss how System.Transactions can handle transactions without using other common practices such as deriving from a ServicedComponent, using interception, or using reflection. Designed to integrate with SQL Server 2005, System.Transactions supports the intelligent and automatic promotion of local lightweight transactions to fully distributed transactions. It also introduces a new class called LightweightTransactionManager, which is a faster alternative to using the DTC for local transactions. I will walk through examples that modify the default transactional settings of the implicit local transaction and modify the isolation level, timeout period and transaction’s context. I will demonstrate how and when the System.Transactions namespace delegates the promotion of a lightweight transaction to a fully distributed transaction. I will also discuss how to develop explicit transactions and manually choose when and if to enlist in a transaction context. Posted Mar 27 2005, 10:00 AM by John Papa with no comments Filed under: ADO.NET, SQL

• Finding NUnit

  Microsoft's Enterprise Library (Ent Lib) was developed using the oft spoken of Test Driven Development methodology (TDD). But of course if you are new to TDD or are aware of it but have not used it yet, you'll want to download and install nUnit to try it out. You can find NUnit here http://www.nunit.org and then navigate to their download page. All of the Ent Lib code has sub folders labelled "Test" which contain the test code that NUnit looks for. NUnit is pretty easy to set up and try out, especially if you load one of the QuickStarts that come with Ent Lib into Visual Studio.NET. Give it a shot and check it out. Its a pretty cool concept and in practical real world scenarios, it has helped my projects come together more quickly. It has been especially helpful on projects with large numbers of developers as it reduces the number of errors in unit testing. And when errors are present, it is easy for other developers to fix them. I'll talk a lot more about TDD and Ent Lib in specifics in future blogs. Once you find NUnit, you can start setting up your own classes that can be used by it. Setting up a class for the TDD model is quite simple. Below I have shown part of the SqlDatabaseFixture class from the Ent Lib DAAB (located in the Sql\Tests folder). You really only have to follow a few steps within the code. Surround your test class with the compilation directive Reference the NUnit.Framework and include it in a using statement Decorate your test class with the [TestFixture] attribute (NUnit looks for these attributes) Decorate your test method with the [Test] attribute and make sure they are declared as void Use one or more Assert statements to look for valid conditions (or invalid) Go ahead and give it a whirl in your code. SqlDatabaseFixture #if UNIT_TESTS using System.Data; using System.Data.SqlClient; using Microsoft.Practices.EnterpriseLibrary.Configuration; using Microsoft.Practices.EnterpriseLibrary.Data.Tests; using NUnit.Framework; namespace Microsoft.Practices.EnterpriseLibrary.Data.Sql.Tests { [TestFixture] public class SqlDatabaseFixture {     private static ConfigurationContext context = new TestConfigurationContext();     [Test]     public void ConnectionTest()     {     SqlDatabase sqlDatabase = new SqlDatabase();     sqlDatabase.ConfigurationName = "NewDatabase";     sqlDatabase.Initialize(new DatabaseConfigurationView(context));     IDbConnection connection = sqlDatabase.GetConnection();     Assert.IsNotNull(connection);     Assert.IsTrue(connection is SqlConnection);     connection.Open();     DBCommandWrapper cmd = sqlDatabase.GetSqlStringCommandWrapper("Select * from Region");     cmd.CommandTimeout = 60;     Assert.AreEqual(cmd.CommandTimeout, 60);     } } Here is the image of NUnit running the test fixture SqlDatabaseFixture for Ent Lib's DAAB QuickStart.   Posted Mar 26 2005, 06:37 PM by John Papa with 3 comment(s) Filed under: Ent Lib

• There's Only One First Time

  Well, I finally did it. Today I joined the ranks of bloggers everywhere. I am grateful that the crew at codebetter.com was gracious enough to accept me into their community. Thanks guys! I'll be posting code samples, random thoughts, links, research and some personal musings in my blog ... that is, once I decide on the color schema and theme for it ;-).  Well, I've got to get back to coloring Easter Eggs with my kids now. It's amazing how difficult it is to get that food coloring off of your clothes, skin and ... uh oh ... the dog!   Posted Mar 25 2005, 05:02 PM by John Papa with 5 comment(s)