Bite Size C# – DataTable to RecordSet Converter

When working on legacy systems you may sometimes find that a wide variety of technologies have been used, and short of rewriting a huge amount of code the only thing that can be done is to create some functionality to map between the different technologies utilised.

I had a scenario where I had to integrate 2 legacy applications utilising different technologies for database access, one utilised ADODB and the other ADO.NET. Due to time constraints I could not change the underlying code of the systems and rather decided to create a helper class to map an ADO.NET DataTable to an ADODB RecordSet in order to facilitate the integration.

My DTtoRSconvert is a static class that resides in the Core.Data.Help namespace. It contains a static method ConvertToRecordSet that takes a DataTable as a parameter and returns a RecordSet. It also has one static private method TranslateType which is used to map between the DataTable and RecordSet Data types.

Here is the code:

using System.Data;
using System.Reflection;
using ADODB;

namespace Core.Data.Helper
{
    //Helper class that convert ADO.Net DataTables to ADODB RecordSets
    public static class DTtoRSconvert
    {
        public static Recordset ConvertToRecordSet(DataTable inTable)
        {
            var recordSet = new Recordset { CursorLocation = CursorLocationEnum.adUseClient };

            var recordSetFields = recordSet.Fields;
            var inColumns = inTable.Columns;

            foreach (DataColumn column in inColumns)
            {
                recordSetFields.Append(column.ColumnName
                                    , TranslateType(column.DataType)
                                    , column.MaxLength
                                    , column.AllowDBNull
                                          ? FieldAttributeEnum.adFldIsNullable
                                          : FieldAttributeEnum.adFldUnspecified
                                    , null);
            }

            recordSet.Open(Missing.Value
                        , Missing.Value
                        , CursorTypeEnum.adOpenStatic
                        , LockTypeEnum.adLockOptimistic, 0);

            foreach (DataRow row in inTable.Rows)
            {
                recordSet.AddNew(Missing.Value,
                              Missing.Value);

                for (var columnIndex = 0; columnIndex < inColumns.Count; columnIndex++)
                {
                    recordSetFields[columnIndex].Value = row[columnIndex];
                }
            }

            return recordSet;
        }

        private static DataTypeEnum TranslateType(IReflect columnDataType)
        {
            switch (columnDataType.UnderlyingSystemType.ToString())
            {
                case "System.Boolean":
                    return DataTypeEnum.adBoolean;

                case "System.Byte":
                    return DataTypeEnum.adUnsignedTinyInt;

                case "System.Char":
                    return DataTypeEnum.adChar;

                case "System.DateTime":
                    return DataTypeEnum.adDate;

                case "System.Decimal":
                    return DataTypeEnum.adCurrency;

                case "System.Double":
                    return DataTypeEnum.adDouble;

                case "System.Int16":
                    return DataTypeEnum.adSmallInt;

                case "System.Int32":
                    return DataTypeEnum.adInteger;

                case "System.Int64":
                    return DataTypeEnum.adBigInt;

                case "System.SByte":
                    return DataTypeEnum.adTinyInt;

                case "System.Single":
                    return DataTypeEnum.adSingle;

                case "System.UInt16":
                    return DataTypeEnum.adUnsignedSmallInt;

                case "System.UInt32":
                    return DataTypeEnum.adUnsignedInt;

                case "System.UInt64":
                    return DataTypeEnum.adUnsignedBigInt;

                //System.String will also be handled by default
                default: 
                    return DataTypeEnum.adVarChar;
            }
        }
    }
}
Bite Size C# – DataTable to RecordSet Converter

Bite Size C# – XLS File Merger

This will be the last Bite Size C# post revolving around CSV and XLS files (for a while at least). The next one will be on a different and probably a bit more exciting topic.

Today we will look at my XLS File Merger, which resides in the Core.XLS namespace of my Core.dll library. It is a static class with one static method MergeFiles. The purpose of this method is to merge 2 or more XLS files into a single file, i.e. taking the worksheets of 2 or more files and combing them into a single file. The MergeFiles method takes 2 arguments: a list of locations of files to be merged (filePathList) and the destination file that will be created (outputFile).

Here is the code:

using System;
using Microsoft.Office.Interop.Excel;

namespace Core.XLS
{
    public static class XlsFileMerge
    {
        public static void MergeFiles(string[] filePathList, string outputFile)
        {
            var app = new Application {Visible = false};

            app.Workbooks.Add("");
            foreach (var file in filePathList)
            {
                app.Workbooks.Add(file);
            }


            for (var i = 2; i <= app.Workbooks.Count; i++)
            {
                var count = app.Workbooks[i].Worksheets.Count;

                app.Workbooks[i].Activate();
                for (var j = 1; j <= count; j++)
                {
                    var ws = (_Worksheet) app.Workbooks[i].Worksheets[j];
                    if (ws.UsedRange.Rows.Count <= 1) continue;
                    ws.Select(Type.Missing);
                    ws.Cells.Select();


                    var sel = (Range) app.Selection;
                    sel.Copy(Type.Missing);

                    var sheet = (_Worksheet) app.Workbooks[1].Worksheets.Add(
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing
                                                 );
                    sheet.Name = ws.Name;
                    sheet.Paste(Type.Missing, Type.Missing);
                }
            }
            app.Workbooks[1].SaveAs(@outputFile);
            app.Quit();
        }
    }
}
Bite Size C# – XLS File Merger

Bite Size C# – XLS Creator

Today we will look at my XLS Creator Class. It is very similar to my CSV Creator class discussed in an earlier post (BITE SIZE C# – CSV FILE CREATOR), except that it creates a well formed XLS file instead of a CSV file.

It is a static class contained in the Core.XLS namespace and contains 1 static method ExportToExcel. Like the CSV Creator class it uses a generic list of objects to construct a XLS file. The method also takes 2 string values xlsNameWithExt and sheetName.

xlsNameWithExt which is used to define the name and location to save the created XLS file and sheetName which is used to set the sheet name the data will be inserted on.

Here is the code:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;

namespace Core.XLS
{
  public static class XlsCreator
  {
  public static void ExportToExcel<T>(List<T> list, string xlsNameWithExt, string sheetName)
  {
  var columnCount = 0;

  var StartTime = DateTime.Now;

  var rowData = new StringBuilder();

  var properties = typeof (T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

  rowData.Append("<Row ss:StyleID=\"s62\">");
  foreach (var p in properties)
  {
  if (p.Name.ToUpper() == "ENTITYSTATE" || p.Name.ToUpper() == "ENTITYKEY")
    continue;

  if (p.PropertyType.Name != "EntityCollection`1" && p.PropertyType.Name != "EntityReference`1")
  {
    var type = "String";
    columnCount++;
    rowData.Append("<Cell><Data ss:Type=\"" + type + "\">" + p.Name + "</Data></Cell>");
  }
  else
    break;
  }
  rowData.Append("</Row>");

  foreach (var item in list)
  {
  rowData.Append("<Row>");
  for (var x = 0; x < columnCount; x++) //each (PropertyInfo p in properties)
  {
    var o = properties[x].GetValue(item, null);
    var value = o == null ? "" : o.ToString();
    rowData.Append("<Cell><Data ss:Type=\"String\">" + value + "</Data></Cell>");
  }
  rowData.Append("</Row>");
  }

  var sheet = @"<?xml version=""1.0""?>
    <?mso-application progid=""Excel.Sheet""?>
    <Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""
    xmlns:o=""urn:schemas-microsoft-com:office:office""
    xmlns:x=""urn:schemas-microsoft-com:office:excel""
    xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""
    xmlns:html=""http://www.w3.org/TR/REC-html40"">
    <DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office"">
    <Author>MSADMIN</Author>
    <LastAuthor>MSADMIN</LastAuthor>
    <Created>2011-07-12T23:40:11Z</Created>
    <Company>Microsoft</Company>
    <Version>12.00</Version>
    </DocumentProperties>
    <ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel"">
    <WindowHeight>6600</WindowHeight>
    <WindowWidth>12255</WindowWidth>
    <WindowTopX>0</WindowTopX>
    <WindowTopY>60</WindowTopY>
    <ProtectStructure>False</ProtectStructure>
    <ProtectWindows>False</ProtectWindows>
    </ExcelWorkbook>
    <Styles>
    <Style ss:ID=""Default"" ss:Name=""Normal"">
    <Alignment ss:Vertical=""Bottom""/>
    <Borders/>
    <Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""/>
    <Interior/>
    <NumberFormat/>
    <Protection/>
    </Style>
    <Style ss:ID=""s62"">
    <Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""
    ss:Bold=""1""/>
    </Style>
    </Styles>
    <Worksheet ss:Name=""" + sheetName + @""">
    <Table ss:ExpandedColumnCount=""" + (properties.Count() + 1) + @""" ss:ExpandedRowCount=""" +
    (list.Count() + 1) + @""" x:FullColumns=""1""
    x:FullRows=""1"" ss:DefaultRowHeight=""15"">
    " + rowData + @"
    </Table>
    <WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">
    <PageSetup>
    <Header x:Margin=""0.3""/>
    <Footer x:Margin=""0.3""/>
    <PageMargins x:Bottom=""0.75"" x:Left=""0.7"" x:Right=""0.7"" x:Top=""0.75""/>
    </PageSetup>
    <Print>
    <ValidPrinterInfo/>
    <HorizontalResolution>300</HorizontalResolution>
    <VerticalResolution>300</VerticalResolution>
    </Print>
    <Selected/>
    <Panes>
    <Pane>
      <Number>3</Number>
      <ActiveCol>2</ActiveCol>
    </Pane>
    </Panes>
    <ProtectObjects>False</ProtectObjects>
    <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
    </Worksheet>
    <Worksheet ss:Name=""Sheet2"">
    <Table ss:ExpandedColumnCount=""1"" ss:ExpandedRowCount=""1"" x:FullColumns=""1""
    x:FullRows=""1"" ss:DefaultRowHeight=""15"">
    </Table>
    <WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">
    <PageSetup>
    <Header x:Margin=""0.3""/>
    <Footer x:Margin=""0.3""/>
    <PageMargins x:Bottom=""0.75"" x:Left=""0.7"" x:Right=""0.7"" x:Top=""0.75""/>
    </PageSetup>
    <ProtectObjects>False</ProtectObjects>
    <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
    </Worksheet>
    <Worksheet ss:Name=""Sheet3"">
    <Table ss:ExpandedColumnCount=""1"" ss:ExpandedRowCount=""1"" x:FullColumns=""1""
    x:FullRows=""1"" ss:DefaultRowHeight=""15"">
    </Table>
    <WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">
    <PageSetup>
    <Header x:Margin=""0.3""/>
    <Footer x:Margin=""0.3""/>
    <PageMargins x:Bottom=""0.75"" x:Left=""0.7"" x:Right=""0.7"" x:Top=""0.75""/>
    </PageSetup>
    <ProtectObjects>False</ProtectObjects>
    <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
    </Worksheet>
    </Workbook>";

  using (var sw = new StreamWriter(xlsNameWithExt))
  {
  sw.Write(sheet);
  }
  }
  }
}
Bite Size C# – XLS Creator

Bite Size C# – XLS to CSV Converter

Let us have a look at my XLS to CSV Converter class today.

It is a static class, which is located in the Core.CSV namespace. It contains one static method CovertExcelToCsv which takes 3 parameters excelFilePath, csvOutputFile and worksheetNumber. The excelFilePath parameter is used to input the source Excel file, csvOutputFile is used to input the destination CSV file and lastly worksheetNumber is used to define which worksheet to convert, as CSV files do not have worksheets only one worksheet can be converted to CSV file at a time. The worksheetNumber parameter has a default value of 1 in the event that no value is passed in.

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace Core.CSV
{
 public static class XlsToCsvConverter
 {
  public static void CovertExcelToCsv(string excelFilePath, string csvOutputFile, int worksheetNumber = 1)
  {
   if (!File.Exists(excelFilePath)) throw new FileNotFoundException(excelFilePath);
   if (File.Exists(csvOutputFile)) throw new ArgumentException("File exists: " + csvOutputFile);

   // Connection string
   var connectionStr =
    String.Format(
     "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"",
     excelFilePath);
   var cnn = new OleDbConnection(connectionStr);

   // Get schema information and then data in spreadsheet
   var dt = new DataTable();
   try
   {
    cnn.Open();
    var schemaTable = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    if (schemaTable != null && schemaTable.Rows.Count < worksheetNumber)
     throw new ArgumentException("The worksheet number requested does not exist.");
    if (schemaTable != null)
    {
     var worksheet = schemaTable.Rows[worksheetNumber - 1]["table_name"].ToString().Replace("'", "");
     var sql = String.Format("select * from [{0}]", worksheet);
     var da = new OleDbDataAdapter(sql, cnn);
     da.Fill(dt);
    }
   }
   catch (Exception ex)
   {
    //Insert logging of exception here
    throw;
   }
   finally
   {
    // Close connection to the spreadsheet
    cnn.Close();
   }

   // Write out CSV data
   using (var wtr = new StreamWriter(csvOutputFile))
   {
    foreach (DataRow row in dt.Rows)
    {
     var firstLine = true;
     foreach (DataColumn col in dt.Columns)
     {
      if (!firstLine)
      {
       wtr.Write(",");
      }
      else
      {
       firstLine = false;
      }
      var data = row[col.ColumnName].ToString().Replace("\"", "");
      wtr.Write(String.Format("{0}", data));
     }
     wtr.WriteLine();
    }
   }
  }
 }
}
Bite Size C# – XLS to CSV Converter

Bite Size SQL – Basic Query Optimisation

How SQL queries are written can have a huge impact on how quickly and efficiently they execute. In this post I will explain some very basic methods that can be used to optimise SQL queries.

  • When writing select queries, only select the columns you need. In general using “SELECT *” is very bad practice.
  • Avoid using sub queries, rather use joins. What is meant by this is instead of using:
        SELECT COLUMNA FROM TABLEA WHERE COLUMNB IS IN (SELECT COLUMNB FROM TABLEB)
        Rather use:
        SELECT COLUMNA FROM TABLEA AS TA INNER JOIN TABLEB AS TB ON TA.COLUMNB = TB.COLUMNB
  • Use WHERE to limit the result set to only what you need. There is no point in pulling 10000 records if you are only looking for records, for example in a certain date range.
  • Use WITH NOLOCK with SELECT queries when feasible, this can prevent deadlocks which can cause serious performance issues. However note that using WITH NOLOCK can result in a non-accurate result set being returned which contains non committed records, so keep this in mind when using. But when querying a production system database this risk might be outweighed by the risk of deadlocks occuring.

One last point on stored procedures, this is not really an optimisation point as much as a best practice point. When developing stored procedures always write them to return result sets of the same dimensions. I have worked on numerous legacy systems were this was not the case and it results in a complete nightmare, especially with projects that utilise an ORM.

Bite Size SQL – Basic Query Optimisation

Bite Size C# – CSV File Creator

Today we will be looking at my CSV Creator class, which is included in my Core.dll library.

It has 1 static method that takes a generic list of objects and a CSV file name. It uses the generic list of objects to construct the CSV file. The properties of the object type contained in the list are used in the creation of the header row of the file. The CSV file name parameter is used to save the created file to disk.

This class in contained in my Core.CSV namespace. Here is the code:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;

namespace Core.CSV
{
    public static class CsvCreator
    {
        public static void CreateCsvFromGenericList<T>(List<T> list, string csvNameWithExt)
        {
            if (list == null || list.Count == 0) return;

            //get type of objects in list
            Type t = list[0].GetType();
            string newLine = Environment.NewLine;

            using (var sw = new StreamWriter(csvNameWithExt))
            {
                //Create an instance of the class
                object o = Activator.CreateInstance(t);
                //Get all properties from class created, this will be used for CSV header row
                PropertyInfo[] props = o.GetType().GetProperties();

                //Create header row based on properties of class
                foreach (
                    PropertyInfo prop in
                        props.Where(prop => prop.Name.ToUpper() != "ENTITYSTATE" 
                        && prop.Name.ToUpper() != "ENTITYKEY"))
                {
                    sw.Write(prop.Name.ToUpper() + ",");
                }
                sw.Write(newLine);

                //create data rows using each object in list
                foreach (T item in list)
                {
                    foreach (string rowData in from pi in props
                                               where !Convert.ToString(item.GetType()
                                                        .GetProperty(pi.Name)
                                                        .GetValue(item, null)).Contains("Unchanged") &&
                                                     !Convert.ToString(item.GetType().GetProperty(pi.Name)
                                                                           .GetValue(item, null))
                                                             .Contains("System.Data.EntityKey")
                                               select Convert.ToString(item.GetType()
                                                                           .GetProperty(pi.Name)
                                                                           .GetValue(item, null))
                                                             .Replace(',', ' ') + ',')
                    {
                        sw.Write(rowData);
                    }
                    sw.Write(newLine);
                }
            }
        }
    }
}

This class is useful when you simply want to create a CSV file from a generic object list quickly.

Bite Size C# – CSV File Creator

Bite Size C#

As a software developer I believe it is critical to build reusable code when possible. All software engineers have their toolbox of previously written code that they reuse when the need arises. Keeping this in mind I have built a Class Library, that I continually extend, that contains all the reusable functions that I use across multiple projects. I call this my Core.dll and include it in all the projects I work on.

The project contains multiple classes for a variety of functions, anything from string manipulation to file generation. I will be sharing little code bits from this Core library in the Bite Size C# posts.

Today we will look at my StringExtensions class, which falls in my Core.Extensions namespace.

using System;

namespace Core.Extension
{
    public static class StringExtensions
    {
        public static bool Contains(this string source, string toCheck, StringComparison comp)
        {      
            return source.IndexOf(toCheck, comp) >= 0;
        }

        public static string GetLast(this string source, int tailLength)
        {
            return tailLength >= source.Length ? source : source.Substring(source.Length - tailLength);
        }
    }
}

The Class contains 2 methods:

A Contains function that is similar to the built-in string.Contains method except that it takes a StringComparison parameter so that case sensitivity, culture and sort rules can be configured.

A GetLast method that passes back the requested last bit of a string (for example last 4 characters of a string.)

When this class is included in a project the following operations can be performed:

 private static void Main(string[] args)
        {
            string testString = "This is a test string";
            bool test = testString.Contains("STRING", StringComparison.InvariantCultureIgnoreCase);
                //This will return true as ignore case is set


            string resultString = -testString.GetLast(6);
                //This will return "string", the last 6 characters in the string
        }

This class acts as an extension class to the predefined C# string class, so its methods appear in the class method list exactly like the built-in string method do (such as string.Equals or string.Trim).

Bite Size C#