ADO.NET Tips & Tricks - Part I

ADO.NET Tips & Tricks - Part I

  • Comments 2

This is either (a) the start of an occasional series discussing ways to get the most out of ADO.NET, or (b) Tim writing up a series of notes he made while reading Microsoft ADO.NET Core Reference - you decide!

  • If you're trying to create an OLE DB connection string to use with an OleDbConnection object, create an empty file with a .UDL extension on the desktop (e.g. copy nul conn.udl), and then double-click on it. Use the user interface to configure the connection, and then open the .UDL file up in Notepad. Bingo - a fully-populated connection string!
  • Details of all the provider-specific connection properties can be found in the MDAC SDK.
  • Rather than creating a new SqlCommand object and passing it a connection as a parameter to the constructor, why not simply call the SqlConnection.CreateCommand() method for the sake of simplicity? The C# using keyword can be applied to this to allow the object to be auto-disposed when finished with.
  • Speed up the performance of DataReader queries by (i) using ordinals to specify the column name (you can identify the specific ordinal with the GetOrdinal() method), and (ii) using a type-specific GetX() method rather than an indexer to retrieve the column contents. Thus myReader.GetString(0) rather than myReader["custName"].
  • When a row is updated, the original version is maintained in a cache. You can view each version using row["col", DataRowVersion.Current] or row["col", DataRowVersion.Original].
  • You can add your own custom property information to a dataset using the ExtendedProperties property. This returns a PropertyCollection object, so you can read and write properties as follows:
       ds.ExtendedProperties.Add("LastUpdated", "Value1");
       Console.WriteLine(ds.ExtendedProperties["LastUpdated"]);
  • You can filter a table with a SQL WHERE clause. Simply add it to the table with similar syntax to the following:
       foreach (DataRow row in tbl.Select("Country='UK' AND City = 'Nottingham'"))
          Console.WriteLine(row["CompanyName"]);
  • The DataTable.Select() method contains an overload to support sort orders also:
       tbl.Select("CompanyName LIKE 'Micro%'", "Country DESC")
  • You can create a DataView object using similar syntax; this can be bound to a Windows or web form control:
       vue = new DataView(tbl, criteria, sortOrder, rowState);

More soon...

  • I've release a connection string encryption class for ADO.Net on GotDotNet. You can find out more via my blog entry: http://weblogs.asp.net/donxml/posts/24923.aspx or just go directly to the GDN sample: http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=7d1d4954-a530-4329-8cc0-96a0c39ab4b2 It's an easy way to add encrypted connection strings to your project.
  • You mentioned using ordinals to avoid the field dictionary lookups, and even mentioned the reader.GetString() method. I have heard conflicting remarks regarding the performance of the Get* members of the reader class vs. doing a "manual" null check and then casting to your given type. Reflecting the reader members with Lutz Roeders tool made it look as if there was quite a bit going on in the Get*, calling GetString calls GetSqlString which returns a SqlString cast as such via the output of the SqlPrepareRecord which has 4 conditionals and a call to process row and yada yada yada. Seems like quite a bit of effort; what is the best way?
Page 1 of 1 (2 items)