Gorgo.Live.ToString()

Mariusz, Gorzoch tech Blog

BizTalk and Microsoft Access

leave a comment »

If you ever need to create solution where you will try to retrieve some data from Microsoft Access (MsAccess) you will hit two issues:

1. How to access MsAccess database

2. How should I provide path to the MsAccess database.

I’ve chance to go thru that so here are the answers:

Ad1: The easiest way is to write a peace of .Net code and call it inside “Expression shape”. In my case I wrote a generic class where you need to pass special XmlDocument which describe information which you need to insert into MsAccess database. Schema of this document looks like this:

Here we have two important things: Name attribute of “Table” element. This attribute is pointing name of the table to which we are trying to write. Second important thing is the “Rows” element, which contain one Row per each inserted record. Each Row element should contain all fields you want to fill with data as child elements. If you create structure like that, then you can use code shown bellow to insert data into MsAccess database:

public abstract class MsAccessHelper
{
     private const string IMAccessDSN = "IMDatabase";

     public static void IMAccessCreateRecord(
             XmlDocument RecordToInsert)
     {

         string ODBCConnectionString =
             string.Format("dsn={0}",IMAccessDSN);

         using (OdbcConnection dbConnection = new OdbcConnection(ODBCConnectionString))
         {
             try
             {
                 dbConnection.Open();
             }
             catch (Exception ex)
             {
                 string Message = string.Format("There is a problem with accessing DSN=’{0}’. Make sure that it exist as system DSN and user accessing has proper permission (Inner exception: ‘{1}’)",
                                                     IMAccessDSN, ex.Message);
                 throw new Exception(Message);
             }

             foreach (XmlNode row in RecordToInsert.SelectNodes("././/*[local-name()=’Row’]"))
             {
                 StringBuilder sbFields = new StringBuilder();
                 StringBuilder sbValues = new StringBuilder();
                 List<OdbcParameter> param = new List<OdbcParameter>();

                 int idx = 1;
                 foreach (XmlNode field in row.ChildNodes)
                 {
                     sbFields.AppendFormat("[{0}],", field.LocalName.Replace("."," "));
                     sbValues.AppendFormat("?,");

                     OdbcParameter par = new OdbcParameter();
                     par.ParameterName = "p" + idx.ToString();
                     par.OdbcType = OdbcType.VarChar;
                     par.Value = field.InnerText;
                     idx++;
                     param.Add(par);
                 }

                 string Fields = sbFields.ToString();
                 Fields = Fields.Substring(0, Fields.Length – 1);
                 string Values = sbValues.ToString();
                 Values = Values.Substring(0, Values.Length – 1);

                 string InsertSql = string.Format("insert into [{0}] ({1}) values({2})",
                                             RecordToInsert.DocumentElement.Attributes["Name"].Value,
                                             Fields,
                                             Values);

                 using (OdbcCommand command = new OdbcCommand(InsertSql, dbConnection))
                 {
                     command.Parameters.AddRange(param.ToArray());
                     command.ExecuteNonQuery();
                 }
             }
         }
      }
}

As you can see I’m using Odbc connection to access MsAccess database.

Ad2: Answer on the second question can be found in above code example. As I didn’t want to hard code path to the Access database I’ve decided to use windows ODBC administration panel and define my source over there. This is good approach as in case of moving your Access database you just need to update ODBC definition and you do not need to recompile and redeploy your solution.

Have fun

Advertisements

Written by Mariusz Gorzoch

13 April 2010 at 16:50

Posted in Bez kategorii

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: