LINQ to SQL Connection Strings with Class Library and Web.Config

Most Microsoft technologies that you can operate with a GUI come with some tradeoffs.  Things have certainly improved over the years and now something like the LINQ to SQL designer is pretty trouble free – unless of course you have something like this fairly common scenario:

I had a class library (Data Access), and decided to add LINQ to SQL classes for a new database that was being introduced.
This class library is also ultimately being consumed by WCF web services.  I have dev, test, prod environments, so I use ASP.NET Web Deployment projects to change configuration per environment for things like appSettings and connectionStrings.

It therefore followed that I wanted to configure the LINQ DataContext connection properties in web.config.  Out of the box you’ll find your connection properties go into your Settings properties class, which gets a little bit in the way.

If you start playing around with the generated classes to change where you’re getting the connection info from then any changes in the designer will wipe them out, so a (relatively) pain free approach to setting your connection safely is the following:

Go to your LINQ to SQL designer and remove the Connection String, and set Application Settings to False

Create a new partial class to mirror your DataContext, and set the constructor to retrieve from your alternative source…

using System;
using System.Linq;
using System.Configuration;
using System.Data.Linq;

namespace CodeBureau.Services.DataAccess
{
    public partial class MyDataContext : DataContext
    {
        public MyDataContext()
            : base(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString, 
mappingSource) { OnCreated(); } } }

This will leave all your generated code intact, but will sort out your configuration woes.

Returning New Autonumber ID from Microsoft Access using ADO.NET and @@IDENTITY

Some things you hope you never have to know.  Well today I needed to call a query in a legacy Access database (insert) and return the ID of the new record.

I had some normal frustrations with parameters, data types and the like.  One piece of advice here – always make sure you explicitly set data types for parameters to Access queries, otherwise you’ll likely get issues when you make the call from ADO.NET, and the error message probably won’t tell you much at all.

When all that was resolved, I discovered that it’s actually possible to use the same connection you used for your insert, to get the new ID (for an Autonumber field), by simply executing the following SQL

SELECT @@IDENTITY

Surprising and simple

ASP.NET Data Binding – Accessing a parent data item from within a nested repeater

I’m maintaining an app at the moment that uses quite a few nested repeaters, and found that headers were being output when there was no data present.  It was found that the header was being written in the ItemTemplate of an ‘outer’ repeater, rather than as the HeaderTemplate of the ‘inner’ repeater.  The next problem was how to reference the outer repeater from the ‘inner’ HeaderTemplate…

The following will bind to a field called HeaderDescription.

<%# DataBinder.Eval(Container.Parent.Parent, “DataItem.HeaderDescription”) %>

The parent of the inner item is it’s repeater, so you have to go to it’s parent to get the right RepeaterItem.  Why don’t you just do the following you ask?

<%# DataBinder.Eval(Container.Parent.Parent.DataItem, “HeaderDescription”) %>

..’cos it doesn’t work – The Eval method expects a ‘Control’ as its first parameter.  There’s other ways to do this server-side, but the first option is probably the easiest.

To complete the picture and only show when there’s data you can add the following to the ‘inner’ repeater declaration

OnItemDataBound=”ItemDataBound” Visible=”false”

then..

        protected void ItemDataBound(object sender, RepeaterItemEventArgs e)
        {
            if (e.Item.ItemType == ListItemType.Item)
            {
                if (!e.Item.Parent.Visible)
                    e.Item.Parent.Visible = true;
            }

        }

This will ensure that you’ll only show if you’ve bound a ‘data’ item (remember you’re doing binding in the HeaderTemplate too).  You could also hook similar things into other events, but it’s generally more convenient to put these things into events that relate to the actual control (pre_render’s probably another good candidate as it will only get called once and you can check the count in the DataSource).

Technorati Profile

CommaDelimitedStringCollection – for when you want to write a comma delimited string from a collection !

Amazing that I still find BCL classes every day in .NET to do simple tasks.  I had a feeling that something may exist but didn’t expect to find it in the System.Configuration namespace.

using System.Configuration;

CommaDelimitedStringCollection strings = new CommaDelimitedStringCollection();

foreach(string item in myOtherCollection)
{
    strings.Add(item);
}

//Spit out your comma separated string
string output = strings.ToString();

Simple!

Using local SQL 2005 Express databases, |DataDirectory| setting and SQLBulkCopy with SemiColon delimited text files


Had some fun with this today as I was utterly confused why I wasn’t able to save data to my SQL2005 express DB (linked within a Windows Forms VS 2005 project), even though I wasn’t getting any errors from a SQLBulkCopy operation.  The connection string was set (from the wizard) to use ‘|DataDirectory|\dbname.mdf.

It turned out of course that the reason there wasn’t an error was because the data was being written fine, but just not to the database I was looking at.  By default any database linked in your project file will be copied to the output folder (bin\debug) when you build.  It does this ‘every time‘.  I thought I was going mad as I wasn’t getting primary key violations trying to insert the same data over and over again!

The useful info is in this MSDN blog entry.  It talks about different options for working around the local databases, but I plumped for a subfolder (called data) in my project, then setting ‘Copy if newer’ on the MDF file rather than ‘do not copy’.  This just means that if you make a schema change then it will blow everything away (probably what I normally want in debug-land).

The reason I was using the SQLBulkCopy class was so I could take in a dataset picked from a semicolon-delimited text file. 
The first thing to do was read the text file into a dataset – and after some mucking about with the Jet OleDb provider and avoiding changing registry entries (to get it to recognise ‘;’ as the delimiter) I found that a schema.ini file is what’s required.

Here’s some quick code I then used to get a DataSet from a semicolon-delimited Text File:

       

       /// <summary>
        /// Import from a given file to database
        /// </summary>
        /// <param name=”fileName”>File to import from (CSV)</param>
        
public void Import(string fileName)
        {
            
if (File.Exists(fileName))
            {
                SqlConnection connection 
= new SqlConnection(Properties.Settings.Default.RBStatsConnectionString);
                
//Get Dataset from file
                
DataSet importData GetDataSetFromFile(fileName);

                //Import using SQLBulkCopy
                
SqlBulkCopy copyManager = new SqlBulkCopy(connection.ConnectionString, SqlBulkCopyOptions.UseInternalTransaction);
                
copyManager.DestinationTableName “TShirtStatistic”;
                
copyManager.BatchSize importData.Tables[0].Rows.Count;
                try
                
{
                    copyManager.WriteToServer(importData.Tables[
0]);
                
}
                
catch (Exception exception)
                {
                    Console.WriteLine(exception)
;
                
}
                copyManager.Close()
;
                
connection.Close();
            
}

        }

        public DataSet GetDataSetFromFile(string fileName)
        {
            
//Ensure we’ve got a schema file to fudge the semicolon delimited text
            
string schemaFile Path.Combine(Path.GetDirectoryName(fileName), “schema.ini”);
            
File.Delete(schemaFile);
            string 
schemaContents “[” + Path.GetFileName(fileName) + “]\nColNameHeader=True\nCharacterSet=ANSI\nFormat=Delimited(;)”;
            
StreamWriter writer File.CreateText(schemaFile);
            
writer.Write(schemaContents);
            
writer.Close();

            string connectionString “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + Path.GetDirectoryName(fileName) + “\\;Extended Properties=’text;HDR=Yes;FMT=Delimited'”;
            string 
query “SELECT * FROM ” + Path.GetFileName(fileName);
            
OleDbConnection connection = new OleDbConnection(connectionString);
            
OleDbCommand command = new OleDbCommand(query, connection);

            connection.Open();

            OleDbDataAdapter adapter = new OleDbDataAdapter(query, connection);
            
DataSet ds = new DataSet();
            
adapter.Fill(ds);

            return ds;

        }

Issues with data binding, Typed Datasets, DataRows and Null Values

Had some fun this morning as we’re trying to get a simple framework set up for a web app, that others can understand, using out-of-the-box functionality if possible.  I tried to avoid the normal tendency to put the ‘architecture hat‘ on and overcook it.  If you can’t be bothered to read all of this then just scroll to the bottom for the ‘take home’ on binding against typed datasets with null values.


We started off the prototype with a typed Dataset (including a couple of DB tables, and key relationships between each), a table adapter for each table, and a DetailsView at the front end, bound to an objectdatasource, which in turn talks to a business object that calls the TableAdapter.  That’s a lot for one sentence so from top to bottom…


DetailsView –> ObjectDataSource –> BusinessService –> TableAdapter –> Database


This all sounds pretty straightforward, and it is – unless (as in this case) you decide to have your business object pass back a single (strongly typed) DataRow for your front end to bind against.  This seemed reasonable at the time because I only ever want to show one row here.


You’ll basically get a StrongTypingException for every null field you try and bind, because by default a null field value will raise an exception when accessed through its generated property. 


We got distracted for a while exploring this as the natural tendency is to think ‘ah – I’ll make sure it doesn’t raise the exception’ – treating the symptom rather than the cause.


It turns out that any non-string field in a typed dataset table can’t have a default of null (or anything other than ‘raise exception’).  If you stuff in a _null value directly in the XSD your fields will disappear from the public properties (rather curiously) when you compile – so that’s not an ‘enterprise’ option.


I tried changing the return type in my business method to a generic DataRow to try and force the data binding to NOT use the strongly typed properties (getting warmer but still no dice.  I knew I was missing something really simple…


I then stumbled across a post (bottom of the page) that illustrated databinding will use different methods to bind based on what interfaces it finds.  The binding mechanism basically has no choice but to use the strongly typed public properties on a strongly typed or generic DataRow, as there’s no way for it to successfully enumerate the values.  If you bind against almost anything else you’ll most likely have no issues (it appears the databinding doesn’t rely solely on ITypedList).  We’re now binding against a typed DataTable (rather than generic DataView) and all’s fine.  If you want (rather) more info on ITypedList then look here.


So…


Don’t databind against a typed (or untyped) DataRow!

Feelgood Friday – Refactoring with .NET Generics


As it’s Friday I thought – let’s do some cleaning up.  The following exercise is refactoring a class from an app I’m maintaining at the moment.  The app’s fine (functional etc), and was originally a .NET 1.1 build.  A combination of this, a previous, straight conversion to .NET 2.0 (with the minimum of mods) and a variety of historical developers (mostly junior) means that the code has a certain amount of ‘odour’ in numerous places. 


Lack of consistency isn’t the main problem, it’s more about doing things smartly rather than taking the path of least resistance with your current knowledge.  The latter generally means you don’t learn much, get much satisfaction or pride in your work, and you may end up a tad confused as to why you always end up with the ‘less glamourous’ projects.


Here’s the original code.  A simple collection class containing custom MenuItem objects. 


    /// <summary>
    /// Represents a collection of MenuItems.
    /// </summary>
    
public class MenuItems
    {
        
private readonly ArrayList palMenuItems = new ArrayList();


        
// Add a MenuItem to the Collection
        
public void Add(MenuItem objMenuItem)
        {
            palMenuItems.Add(objMenuItem)
;
        
}

        
// Retrieve a MenuItem from the Collection
        
public MenuItem Item(string strTitle)
        {
            
if (palMenuItems.Count > 0)
            {
                
foreach (MenuItem objMenuItem in palMenuItems)
                {
                    
if (objMenuItem.Title.ToLower() == strTitle.ToLower())
                    {
                        
return objMenuItem;
                    
}
                }
            }
            
return new MenuItem();
        
}

        
// Returns True if the specified Key exists in the collection
        
public bool Exists(string strTitle)
        {
            
if (palMenuItems.Count > 0)
            {
                
foreach (MenuItem objMenuItem in palMenuItems)
                {
                    
if (objMenuItem.Title.ToLower() == strTitle.ToLower())
                    {
                        
return true;
                    
}
                }
            }
            
return false;
        
}

        
// Remove a MenuItem from the Collection
        
public void Remove(string strTitle)
        {
            
if (palMenuItems.Count > 0)
            {
                
foreach (MenuItem objMenuItem in palMenuItems)
                {
                    
if (objMenuItem.Title.ToLower() == strTitle.ToLower())
                    {
                        palMenuItems.Remove(objMenuItem)
;
                        break;
                    
}
                }
            }
        }

        
// Clear the collections contents
        
public void Clear()
        {
            palMenuItems.Clear()
;
        
}

        
// Return the Number of MenuItems in the Collection
        
public int Count()
        {
            
return palMenuItems.Count;
        
}

        
// Access to the enumerator
        
public MenuItemEnumerator GetEnumerator()
        {
            
return new MenuItemEnumerator(this);
        
}

        
#region MenuItem Enumaration Class

        
//=================================================================================================
        // Inner Enumeration Class:
        //=================================================================================================
        
public class MenuItemEnumerator
        {
            
private readonly MenuItems pMenuItems;
            private int 
position 1;

            public 
MenuItemEnumerator(MenuItems objMenuItems)
            {
                pMenuItems 
objMenuItems;
            
}

            
public MenuItem Current
            {
                
get return (MenuItem) pMenuItems.palMenuItems[position]}
            }

            
// Declare the MoveNext method required by IEnumerator:
            
public bool MoveNext()
            {
                
if (position < pMenuItems.palMenuItems.Count – 1)
                {
                    position++
;
                    return true;
                
}
                
else
                
{
                    
return false;
                
}
            }

            
// Declare the Reset method required by IEnumerator:
            
public void Reset()
            {
                position 
1;
            
}

            
// Declare the Current property required by IEnumerator:
        
}

        
#endregion
    
}




Colorized by: CarlosAg.CodeColorizer


OK – Here’s the issues I see with the code.



  1. You can spot some room for improvement immediately with variable naming (a weird variation of hungarian notation) – palMenuItems, objMenuItems etc.
  2. This is a simple wrapper implementation.  In conjunction with the naming you may conclude this is an ex-VB programmer who hasn’t quite grasped object-oriented concepts yet (an observation – not a criticism).  If the developer knew about inheritance then they would have hopefully just inherited from one of the many applicable classes in System.Collections.  This would have also removed the need for implementing a custom Enumerator class.  Note that neither class actually inherits from any useful base class.  This is copy/paste pseudo-inheritance, and is rarely a wise idea.
  3. The Exists and Item methods use a simple loop mechanism to find the item they’re looking for.  This is potentially wasteful with many items, and goes back to the ‘path of least resistance’ thought.  It’s also using the inefficient ‘ToLower’ implementation to do comparison of what should be a key.  In practice the match doesn’t need to be case-insensitive, and if it did then a String.Compare should be used with the appropriate ‘ignorecase’ argument).  The developer clearly isn’t familiar (or maybe just not comfortable) with dictionaries or hashtables where access to items via a key is supported and far more efficient.
  4. The Count implementation is a method rather than a property (aargh!)
  5. It could be argued that the menuitem class itself (not shown here) is redundant as the ASP.NET framework does define a similar MenuItem class.  In reality the whole implementation could be replaced with a sitemap, a menu control, and some custom code, but I’ll leave that out of scope for now.
That gives some pointers as to what ‘could’ have been done in .NET 1.1.  .NET 2.0 of course introduced generics and so let’s follow that path.

The MenuItems class is used in a few places:



  • A menu control (that renders menu items)
  • Many pages that load items into the control (I already refactored some of this into inherited menu controls, where all pages shared the same ‘dynamic’ menu), but there’s plenty more improvements to be made.

The first thing is to say…


I don’t need a collections class…


The following is now in the Menu Control:


        //private MenuItems menuItems = new MenuItems(); – Out with the Old
        
private Dictionary<string, MenuItem> menuItems = new Dictionary<string, MenuItem>()//In with the new

        
public Dictionary<string, MenuItem> MenuItems
        {
            
get return menuItems}
            
set { menuItems = value; }
        }




Colorized by: CarlosAg.CodeColorizer

This simply uses a generic Dictionary object with a string key (as we find items by title).  Anywhere that references this dictionary will now need to add a ‘key’ in certain places (e.g. Add method), and the Item method will no longer work as that’s gone.  That needs to change to a class indexer.


Fortunately Visual Studio’s find and replace can cope with all of this as the code is largely consistent…



A quick compile should show all the patterns (from the errors) if there’s any inconsistencies.  There were probably about 200 references to patch here with about 4 different variations of naming and scope – but it took about 5 minutes to patch (including writing this text :-))


We then have to patch all the ‘Item’ references.  These are almost always setting a ‘selected’ property (Stopwatch on…)


As there’s only a few references here (about 20), I could patch them manually, but this could be done entirely by using regular expressions to do the find and replace.  http://msdn.microsoft.com/en-us/library/2k3te2cs.aspx shows all the options available.  I tend to only use regexp for large volume stuff where I get a return on my time investment to work out the expression! 


I’m just doing it in two steps without regexp as follows:



The code


            Menu1.MenuItems.Item(UserMenu.MyCommunicationItems).Selected = true;

becomes


            Menu1.MenuItems[UserMenu.MyCommunicationItems].Selected = true;


For extra points you then use Resharper to rename all of the obj and pal references to something reasonable, e,g, objMenuItem becomes menuItem.


Oh – and use Resharper’s ‘Find Usages’ on the MenuItems class to confirm it can be deleted.


And relax…..



In hindsight I would have used Resharper to ‘change signature’ on the methods in MenuItems (to patch all the references first), then converted to the generic Dictionary.  Could have been a bit quicker.


Hope this gives someone some courage to get in there and improve their code.  I firmly believe you’ve just got to ‘want’ to do it and then you’re biggest problem will be stopping yourself!



Developer Highway Code – Building secure software with .NET

Had some security training yesterday (don’t normally get training as a contractor so I was very happy to go along). 


The trainer mentioned the Developer Highway Code from Microsoft (seemingly originating from the UK based on the style of the publication).  Not only does it look cool, and have some rather amusing geek stuff (just see the ‘Reduce Coffee Now’ sign on the cover), it also has some really practical advice for people wanting to build secure applications.


Download it from Microsoft

VB.NET – I know I shouldn’t care but I just don’t like it!

Aaargh! I thought to myself for the umpteenth time as I looked through my inherited VB.NET web site (it’s bad enough that it was a web ‘site’ with dodgy auto-generated (i.e. NO) namespaces), but the following things are now officially going on my list of reasons I choose C# over VB.NET.  I know I shouldn’t care as it all compiles to the same thing, but (just from this project) –



  • VB.NET still allows you to dodge the Option Strict and Option Explicit (definitely a ‘Web Site’, as there’s no option in the property pages like for any other standard .NET project).  This means that you happily declare untyped variables, and do all sorts of dodgy late binding.  Some people obviously like this facility, but it’s one of the things that holds VB programmers back in the world of object-oriented design.
  • Modules still exist!  OK you could argue this is just a public class with everything marked as static, but it’s still ‘global’, and you don’t need to reference the module name when you access a method or variable.  This often leads to the ‘where the hell is that defined?’ question
  • Variants still exist.  This is obviously an extension of the first one, but it’s a big enough annoyance that people use to get themselves out of a (I don’t know how to implement this properly using object-oriented techniques) hole
  • Syntax ‘feel’ – e.g. Me vs this, and MyBase vs base – it feels a bit ‘Mickey Mouse’.
  • Verbose syntax causing RSI:

    • #Region “String Constant” vs #region whatever you like without having to put quotes around (C# regions can also be indented with the code unlike VB)
    • Global functions instead of operators like TryCast instead of ‘as’, CType instead of (Mytype)variable.  CStr, Cint etc still exist.  Not only is this more typing in many cases as you need to enter two or more parameters, it also feels like yet more ‘baggage’ from the bad old days, as you can still pass in your objects to generic ‘library’ functions rather than use methods on the object themselves (like ToString()).  Many VB programmers will lap this up because the language still allows them and they don’t have to learn something new.

  • Case insensitivity.  OK I’ll give you this one as it was one of the ‘speed’ things in VB6.  It does lead you back to the horrible pseudo-hungarian thing (for some people) though as you get naming conflicts with properties and variables if you follow the general pascal/camel C# standard (can’t remember whether there’s an option to switch that off though).  The alternative to the pseudo-hungarian notation is the _ prefix for class variables, but even that seems like too much of a concession.
  • Methods can still look like properties.  Call a method in VB without parameters and it will happily let you write MyMethod.  This just feels like inconsistency.
  • Tool support and productivity. 

    • VB.NET simply doesn’t have the same support in Visual Studio or refactoring addins (ReSharper’s only catching on to VB.NET now) This isn’t the language’s fault, but it’s easier to be more productive through tools with C#.  (I know this is improved with every release of VS).
    • The default VS 2005 refactoring capability (limited but present in some form) for C# is basically non-existent for VB.NET.  Maybe Microsoft think VB programmers don’t need refactoring support?
    • Intellisense is also generally rather lacking for VB.NET
    • XML Documentation comments (just now catching up, but was really lacking)

  • Angle brackets <> look uglier than square brackets [] for attributes (OK that’s a bit picky!)

The bottom line is that VB.NET lets you be sloppy, like VB always did.  The problem is that a fair percentage of those who migrate from VB to VB.NET (not all I agree – don’t shoot me!) take the same shortcuts that they always did – because they still can.  


My opinion is that on average you’ll find more elegant design, better formatted and more object-oriented code from C# programmers as they’re more likely to have come from a C++ or Java background.


Did I mention that I was a VB programmer for years, and have only dabbled a bit in Java and not really ever C++.  I loved VB6 (at the time) as I could get systems written quickly and well.  What I didn’t love was how much backwards-compatible support it kept leaving in with each new version, just so people could upgrade their crap legacy code.  I’ve seen some very clear, consistent, well formatted and commented VB.NET code, but I’ve seen an awful lot more that’s not.  The flip side is that C# coders often think they’re great just because they’re writing in C#.  I’ve seen plenty of horror stories there too, but the ‘bad’ percentage is much lower (sorry – it just is).


VB unfortunately doesn’t encourage discipline in programmers, whereas C# benefits from a clean slate without the historical baggage.  I believe the slightly more formal language specification of C#, and the fact it attracts more ‘OO’ coders, tends to lead people to think a little more about design rather than just skipping straight to implementation (which is where the ‘real’ problem is 🙂 ).  You can write the same crap code in any language you like.  VB unfortunately just makes it easier.