ADO.NET Tips & Tricks - Part I
I don't know if you see this list of tips & tricks from Tim Sneath but it's really good:
I wait for the second part Tim ;-)
My comment on this will be about using ordinal with a Datareader. Fine if you don't change the Database structure. But if you are in an early stage the fields definition can change easily, so I prefer to use the fields name rather than their index.
I like the tbl.Select one, something I never think using it, but I surely will now !
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...