ADO.NET Tips & Tricks – Part I


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…


Comments (2)

  1. Anonymous says:

    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.

  2. Anonymous says:

    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?