relax! it’s just 0s and 1s

with the use of LINQ and Entity Framework, I haven’t been writing much ADO.NET code to for the Data Access layer. Until recently, when one of my projects in the solution is still .NET 2.0 based, with the standard ADO.NET Data Access. its been some time, since I saw the SqlConnection and SqlCommand classses.

offline, I was working with a pastime application of mine, demonstrating Data Source independence. It is a provider architecture, where the specific data source is easily pluggable.

The base interface (IDataProvider) is used by the application. I already had the SqlDataProvider defined for SQL Databases. I tried switiching the provider to a MySql data source. At the end of it, just wanted to publish a couple of snippets to do data access tasks using C# and MySql.

• You can use the ADO.NET Driver provided by MySql. Download the latest MySql Connector for .NET from: http://dev.mysql.com/downloads/connector/net. This is a free developer version of the component.
• You can download the appropriate connector based on the .NET version.
• You can also use the Odbc Driver, but my snippet is for the MySql connector.
• Add a reference to the MySqlData.DLL to your project. Do not forget to ship this DLL.

Add the following namespace:

using System.Data;
using MySql.Data.MySqlClient;

C# and MySql Data Access without a Transaction.

using (MySqlConnection connection = new MySqlConnection(mySqlConnectionString))
{
    using (MySqlCommand command = new MySqlCommand())
    {
        command.Connection = connection;
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "SELNewRecord";
 
        command.Parameters.AddWithValue("Param1", value1);
 
        connection.Open();
 
        using (MySqlDataReader reader = command.ExecuteReader())
        {
            // read the contents.
        }
    }
}

C# and MySql Data Access with a Transaction.

using (MySqlConnection connection = new MySqlConnection(mySqlConnectionString))
{
    using (MySqlCommand command = new MySqlCommand())
    {
        command.Connection = connection;
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "INSNewRecord";
 
        connection.Open();
 
        using (command.Transaction = connection.BeginTransaction())
        {
            command.ExecuteNonQuery();
            // Execute additional SQL Queries.
 
            command.Transaction.Commit();
        }
    }
}

after all the latest ORM technologies and ease of code writing, it feels nice to write a good old data access snippet. though only once every 12 months… 

as part of some WSE implementation, I had a small utility to read the details of a X509 Certificate. especially the SKID (Subject Key Identifier), of the certificate. actually, WSE comes with a certificate reader tool, which reads the SKID of the certificate.

however, i had 2 issues, using this tool:

• i needed to read the properties from a file, which was the  X509 certificate, instead of reading it from the certificate stores.
• i also needed a string representation of the certificate to be stored in the database. (i like the idea of a database oriented certificate management)

In order to read the X509Certificate properties, there are 2 namespaces available.

using Microsoft.Web.Services2.Security.X509;
using System.Security.Cryptography.X509Certificates;

 however, of the 2 namespaces,  the Microsoft.Web.Services2.Security.X509 seems to give the Subject Key Identifier of the certificate. it makes all the more sense to use this namespace, when you are working with WSE enabled web services.

the code snippet to read the certificate properties: (certificate is assumed to be in a file location)

using (FileStream stream = new FileStream(certificateFilePath, FileMode.Open))
{
    byte[] blob = new byte[(int)stream.Length];
    stream.Read(blob, 0, (int)stream.Length);
 
    using (X509Certificate cert = new X509Certificate(blob))
    {
        this.textBoxBlob.Text = Convert.ToBase64String(blob);
        this.textBoxSubject.Text = cert.Subject;
        this.textBoxTokenIssuer.Text = cert.Issuer;
        this.textBoxSKID.Text = Convert.ToBase64String(cert.GetKeyIdentifier());
        this.textBoxExpiry.Text = cert.GetExpirationDateString();
    }
}

Notes:

• You can get the WSE DLL from here.
• the X509Certificate belongs to the Microsoft.Web.Services2.Security.X509 namespace.
• Convert.ToBase64String(blob) is very useful if you want to store the certificate in a database field. it is one of the ways to eliminate certificate management, for your application.
• The Subject Key Identifier is what uniquely identifies your certificate. when WSE is used in a declarative manner, typically the SKID, Subject and Token Issuer are used in the configuration files.

 there’s a solution to every problem; given enough time and money..

I had a stored procedure which returned multiple result sets.

the stored proc in short, looked as follows: (trimmed down for relevant code)

CREATE PROCEDURE SELMessages
AS
BEGIN
 
   -- prefix blah blah
 
 Declare @Messages TABLE
 (
       MessageID int,
       MessageName nvarchar(256)
 )
 
 Declare @MessageRecipients TABLE
 (
       MessageID int,
       RecipientName nvarchar(256)
 )
 
    -- interim blah blah
 
    -- Result Set 1
    SELECT  MessageID, MessageName FROM @Messages
   
    -- Result Set 2
    SELECT  MessageID, RecipientName FROM @MessageRecipients
 
END
GO

using Linq to SQL (or DLinq or L2S) i had to use this stored procedure.
I dragged the SProc into the designer, and tried to use it in the code. the Linq to SQL designer had generated the following code:

[System.Data.Linq.Mapping.DatabaseAttribute(Name="MessageDB")]
public partial class MessageDBDataContext : System.Data.Linq.DataContext
{
 
 private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();
 
        // more code
 
 [Function(Name="dbo.SELMessages")]
 public ISingleResult SELMessages()
 {
  IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
  return ((ISingleResult)(result.ReturnValue));
 }
 
        // more code
}

the result to be written was a class which looked like:

public partial class SELMessagesResult
{  
 private int _MessageID;
 
 private string _MessageName;
 
 public SELMessageRecipientsResult()
 {
 }
 
 [Column(Storage="_MessageID", DbType="Int NOT NULL")]
 public int MessageID
 {
  get
  {
   return this._MessageID;
  }
  set
  {
   if ((this._MessageID != value))
   {
    this._MessageID = value;
   }
  }
 }
 
 [Column(Storage="_MessageName", DbType="NVarChar(256) NOT NULL", CanBeNull=false)]
 public string MessageName
 {
  get
  {
   return this._MessageName;
  }
  set
  {
   if ((this._MessageName != value))
   {
    this._MessageName = value;
   }
  }
 }
}

my code snippet to use the Stored proc was as follows:

using (MessageDBDataContext messageDbDataContext = new MessageDBDataContext())
{
    return messageDbDataContext.SELMessages();
}

the above code, did not give me the expected behavior. it failed to identify the 2 result sets.

as you can see, the return type of the method is just the result SELMessagesResult,which represents our first result set only.

 I tried to solve the issue and found this post.

it pretty much solved my problem. the only difference is that, instead of the existing tables being returned, my Stored Proc returned  result sets based on some temporary tables it was creating.

based on the post, these were the steps, I followed:

• created the following class to represent the first result set. the designer had already created this class. (SELMessagesResult)
• created the following class to represent the second result set. SELMessageRecipientsResult)

 public partial class SELMessageRecipientsResult
{
    private int _MessageID;
 
    private string _MessageRecipient;
 
    public SELMessageRecipientsResult()
    {
    }
 
    [Column(Storage = "_MessageID", DbType = "Int NOT NULL")]
    public int MessageID
    {
        get
        {
            return this._MessageID;
        }
        set
        {
            if ((this._MessageID != value))
            {
                this._MessageID = value;
            }
        }
    }
 
    [Column(Storage = "_MessageRecipient", DbType = "NVarChar(256) NOT NULL", CanBeNull = false)]
    public string MessageRecipient
    {
        get
        {
            return this._MessageRecipient;
        }
        set
        {
            if ((this._MessageRecipient != value))
            {
                this._MessageRecipient = value;
            }
        }
    }
}

• created a partial class obviously named same as my Data Context class. (right click on .dbml >> view code)

 partial class MessageDBDataContext
 {
 }

• added a new method to this class with following signature:

public IMultipleResults SELMessagesWithMultipleRS()

• Attributed the method with the following code:

[ResultType(typeof(SELMessagesResult))]
[ResultType(typeof(SELMessageRecipientsResult))]
[Function(Name = "dbo.SELMessages")]
public IMultipleResults SELMessagesWithMultipleRS()

• the full method looks as follows: 

partial class MessageDBDataContext
{
    [ResultType(typeof(SELMessagesResult))]
    [ResultType(typeof(SELMessageRecipientsResult))]
    [Function(Name = "dbo.SELMessages")]
    public IMultipleResults SELMessagesWithMultipleRS()
    {
        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
        return ((IMultipleResults)(result.ReturnValue));
    }
}

• As the post explains, used the new method as follows:

using (MessageDBDataContext messageDbDataContext = new MessageDBDataContext())
{
    // Get both the result set.
    var results = messageDbDataContext.SELMessagesWithMultipleRS();
 
    // Get the first result.
    List messages = results.GetResult().ToList();
 
    // Get the second result.
    List messageRecipients = results.GetResult().ToList();
 
    // Do the necessary processing with the 2 sequences.
 
}

spot on.. all was in place and I tested the code for expected behavior.

notes:

• since the original data context designer class is auto-generated by the LINQ to SQL designer, it is good to create a partial class. that way, you need not create it every time you change the DBML.
• you don’t need to attribute the class with the ‘DatabaseAttribute‘ Attribute. .NET is happy with a single annotation.
• you don’t need an instance of the DataContext in this class. (since it is a partial class)

hopefully, this issue will be resolved in their next release of Linq to SQL designer.

there’s a solution to every problem; given enough time and money.. 

in .net,  2 ways of probably many more, to invoke imaging devices like scanner etc and get the scanned objects/documents are discussed below.

One is using TWAIN. TWAIN is an API used by software applications to talk to imaging devices. I believe it is an old API, but still quite famous. my multimedia courses always made reference to the TWAIN standards, and the section always ended with a reference citing, TWAIN to be very complicated and restricted to work with. 

The word TWAIN is from Rudyard Kipling’s “The Ballad of East and West” - “…and never the twain shall meet…” - reflecting the difficulty at the time of connecting scanners and personal computers. It was up-cased to TWAIN to make it more distinctive.

The word TWAIN is not an official acronym; however, it is widely known as “Technology Without Any Impressive Name.”  - Wikipedia

however, the second and the latest API is the WIA Library. (Windows Image Acquisition Library) this is a later API than TWAIN, and said to be more standards-conformant, though I have no idea yet. I haven’t worked with TWAIN to see the difficulties.

the WIA library is probably in-built in VISTA and Windows 2003 Operating systems, but I couldn’t spot it in XP.

getting onto the code, in order to scan and process a document using c#,

• add a reference to the WIALib.dll (Microsoft Windows Image Acquisition 1.01 Type Library)
• include the following using directives. 

using System.Runtime.InteropServices;
using WIALib;

 I ended up with the following snippet which scans and processes the scanned document:

private void ScanDocuments()
{
    // WIA manager COM object.
    // Allows the user to select an imaging device like scanner/camera etc.
    WiaClass wiaManager = null;
 
    // WIA devices collection COM object.
    // The collection of imaging devices.
    CollectionClass wiaDevicesCollection = null;
 
    // WIA root device COM object.
    // Represents the selected imaging device.
    ItemClass wiaRootDeviceItem = null;
 
    // WIA collection COM object.
    // Collection of WIA Image items.
    CollectionClass wiaImageItems = null;
 
    // WIA image COM object.
    // Represents the first of our selected image.
    ItemClass wiaFirstScannedItem = null;
 
    try
    {
        // create COM instance of WIA manager
        wiaManager = new WiaClass();
 
        // call Wia.Devices to get all devices
        wiaDevicesCollection = (CollectionClass)wiaManager.Devices;
 
        // No Devices found.
        if (null == wiaDevicesCollection || 0 == wiaDevicesCollection.Count)
        {
            throw new Exception("No WIA devices found!");
        }
 
        // = Nothing for COM.
        object useDialogFlag = System.Reflection.Missing.Value;
 
        // User will select a root device here.
        wiaRootDeviceItem = (ItemClass)wiaManager.Create(ref useDialogFlag);
 
        // No device selected. Just return.
        if (null == wiaRootDeviceItem)
        {
            return;
        }
 
        // Get the list of images.
        wiaImageItems = (CollectionClass)wiaRootDeviceItem.GetItemsFromUI(WiaFlag.SingleImage, WiaIntent.ImageTypeColor);
 
        // If there is a problem, return.
        if (null == wiaImageItems)
        {
            return;
        }
 
        // We'll grab the first picture only.
        bool useFirstImageOnly = true;
 
        // Iterate through the images and select the first one.
        foreach (object wiaImageObject in wiaImageItems)
        {
            if ((useFirstImageOnly))
            {
                wiaFirstScannedItem = (ItemClass)Marshal.CreateWrapperOfType(wiaImageObject, typeof(ItemClass));
 
                // Get a temporary file name.
                string tempFileName = System.IO.Path.GetTempFileName();
 
                // Copy the scanned object to the temporary file, in a synchronous manner.
                wiaFirstScannedItem.Transfer(tempFileName, false);
 
                // Work with the tempFile.
 
                useFirstImageOnly = false;
 
                // If you want to get hold of all the scanned items,
                // remove the 'useFirstImageOnly' flag.
            }
 
            // Release the enumerated COM object image.
            Marshal.ReleaseComObject(wiaImageObject);
        }
    }
    catch (Exception ex)
    {
        throw new Exception("Acquire from WIA Imaging failed: " + ex.Message);
    }
    finally
    {
        // Release the COM objects used.
 
        if (null != wiaFirstScannedItem)
        {
            Marshal.ReleaseComObject(wiaFirstScannedItem);
        }
 
        if (null != wiaImageItems)
        {
            Marshal.ReleaseComObject(wiaImageItems);
        }
 
        if (null != wiaRootDeviceItem)
        {
            Marshal.ReleaseComObject(wiaRootDeviceItem);
        }
 
        if (null != wiaDevicesCollection)
        {
            Marshal.ReleaseComObject(wiaDevicesCollection);
        }
 
        if (null != wiaManager)
        {
            Marshal.ReleaseComObject(wiaManager);
        }
    }
}

couple of things I noticed:

• when I use the method to invoke the graphic device (scanner, digital camera etc), the imaging device GUI is launched. as good as if you invoked the scanner/digital camera device using Start >> etc..
• once you scan N number of objects, the above code snippet selects just the first one.

as you can see from the comments, you can easily process all the scanned documents., instead of just the first one.

I was browsing through the TWAIN standards and looking for .NET snippets to scan documents, and wanted a cleaner solution to scan a document. the WIA code seemed a little compact.

there’s a solution to every problem; given enough time and money.. 

I was working with an email component, where different mails needed to be sent based on business scenarios. these emails were html formatted, and localization prone.. hence the html content was stored in resource files. I like the strongly-type resources in .NET 2.0 and above.

the whole html was not static, and hence it had placeholders (format strings) to insert business data values, during their actual use.

so a code snippet to construct the email subject would look like: 

string mailSubject = String.Format(CultureInfo.CurrentCulture,
Resource. EmailSubjectKey, blahObject.Id, blahObject.Name);

when I was unit testing the code snippet, I found that the above line gave me a Format exception. I checked the resource string. the Resource.EmailSubjectKey apparently had a valid content with the format strings {0} and {1} for Id and Name. no issues so far..

curious for the behind the scene action, I reflected on the String.Format method, and found that it internally called the StringBuilder.AppendFormat method, which is as follows: 

public StringBuilder AppendFormat(IFormatProvider provider,
string format, params object[] args)
{
    // some top level blah blah..    
    while (index < length)
    {
        ch = chArray[index];
        index++;
        if (ch == '}')
        {
            if ((index < length) && (chArray[index] == '}'))
            {
                index++;
            }
            else
            {
                FormatError();
            }
        }
        if (ch == '{')
        {
            if ((index < length) && (chArray[index] == '{'))
            {
                index++;
            }
            else
            {
                index--;
                break;
            }
        }
        chArray[num4++] = ch;
    } // some more blah..

at this point, I realized that curly braces might be the issue. I checked back my html and sure enough, I was using inline css. 

    &;lt;style type="text/css"&;gt;
        .title
        {
        margin-left: 10px;
        font-family: Calibri, Verdana, Tahoma;
        font-size: small;
        } 
    &;lt;/style&;gt;

the AppendFormat chartered into the CSS curly braces and hence,  the unit case failed and the Format exception. now I had 2 solutions for this:

• attribute based styles: (further inline css)
• con the AppendFormat method..

I had a single CSS class for the whole Email, and hence the following attribute based style works: 

<body style="margin-left: 10px; font-family:
Calibri, Verdana, Tahoma; font-size: small;">

but if you are figuring out how to get past the AppendFormat method, notice the snippet: 

        if (ch == '{')
        {
            if ((index < length) && (chArray[index] == '{'))
            {
                index++;
            }

if the method finds 2 consecutive opening braces ‘{‘, it bypasses it and does not do any format string substitution. that means, the following works perfectly well.

&;lt;style type="text/css"&;gt;
        .title
        {{
        margin-left: 10px;
        font-family: Calibri, Verdana, Tahoma;
        font-size: small;
        }} 
&;lt;/style&;gt;

so now, the above snippet actually puts things in place. AppendFormat doesn’t wander into the CSS area. the CSS engine doesn’t mind the duplicate braces, either.

there’s a solution to every problem; given enough time and money..

The other day, one of my developer friend asked me for a trivial function.  At least it looked trivial to start with. The task was to simply read the raw text of a PDF file using c#.. a small function to do this. No formatting, no image considerations.. just a plain dump of the PDF text.

My default approach was to go for acrobat32.exe and read the text. However, I realized that an adobe installation, com server registration, other unforeseen grumpy issues etc, may hinder the use of the exe. Also, I wasn’t sure of the free adobe reader acrobat32.exe to do the task. I browsed through some other PDF libraries.

The PDFBox for .NET built using IKVM seemed apt for the simple task at hand. 

Here are the steps to get the PDF text:

• Add reference to the IKVM.GNU.Classpath.dll and PDFBox-0.7.3.dll libraries in your project.
• Place the IKVM.Runtime.dll and FontBox-0.1.0-dev.dll libraries in your bin folder. It seems IKVM.Runtime.dll is always required to be present in the runtime folder, but need not be referenced. The other DLL depends on the PDF, you are trying to read. In my case, I needed the FontBox-0.1.0-dev.dll to be present. (the WA DOL driving guide PDF)
• Use the following method to get the text: 

    private static string GetPdfText(string pdfFilePath)
    {
        string extractedPdfText = null;
 
        org.pdfbox.pdmodel.PDDocument pdfDocument = org.pdfbox.pdmodel.PDDocument.load(pdfFilePath);
 
        if (null != pdfDocument)
        {
            org.pdfbox.util.PDFTextStripper pdfTextStripper = new org.pdfbox.util.PDFTextStripper();
 
            // actual extraction of the text.
            extractedPdfText = pdfTextStripper.getText(pdfDocument);
        }
 
        return extractedPdfText;
    }

This open source PDF library provides a host of PDF manipulation capabilities. If you are looking for PDF document merging, page separation, indexes, bookmark reading etc, this is the library. My friend was happy with the small method.. I was happy that someone built a .NET version of this good java PDF library.

there’s a solution to every problem; given enough time and money..

my application, a WCF service, uses Enterprise Application blocks, with a heavy dose of Validation blocks. Along with, Data, Exception and Logging blocks. there were 2 consumers of the application, a console host and a web host.

in a matter of time, the App.config and the web.config grew big, and was getting very difficult to maintain. (monstrous) to add to that, there were a lot of Ent Lib config settings duplicated between the .config files and synchronization was obviously an issue.  

this was primarily because, we were doing configuration based validation, and there were many fields to be validated across different validators. (50-70 fields with 3-4 validations each, do the math)

seeking isolation of the ent lib config settings for clarity and config re-use, i tumbled upon this good post by Tom. after skimming through the initial part, what i found the most suitable, was using a .NET Framework 2.0 feature, more than any Ent Lib configuration features.

 app.config/web.config

<?xml version=”1.0″ encoding=”utf-8″?>

<configuration>

  <configSections>

    <section name=”validation” type=”Microsoft.Practices.EnterpriseLibrary.Validation.

Configuration.ValidationSettings, Microsoft.Practices.

EnterpriseLibrary.Validation, Version=3.1.0.0,

 Culture=neutral, PublicKeyToken=null” />

    <section name=”loggingConfiguration” type=”Microsoft.Practices.EnterpriseLibrary.Logging.

Configuration.LoggingSettings, Microsoft.Practices.

EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral,

PublicKeyToken=null” />

  </configSections>

  <validation configSource=”validations.config” />

  <loggingConfiguration configSource=”logging.config” />

</configuration>

what this meant was i could pluck out the existing validation/logging/exception sections from my app.config and web.config files, put them in their respective config files and add the source files as part of my original WCF service, rather than the consumers.

e.g. a sample exception config file begins like this:

<?xml version=”1.0″ encoding=”utf-8″?>

  <exceptionHandling>

   …. <!– disaster recovery –>

  </exceptionHandling>

So the WCF service ended up with 4 config files, validations.config, logging.config, exceptions.config and data.config.

the build output dumped all the files into a common bin folder, and hence they found each other. i haven’t tried to link the external config files, if they are in a different directory.

<validation configSource=”??” />

probably it will work with a relative/absolute path too..

nevertheless, it was suitable for my requirements. now the console host, iis host and the soon to be added windows service host, all link to a single set of external config files.

the config management looks simple and clean now.

there’s a solution to every problem; given enough time and money..