Skip to main content

Read XML file and run commands in it for C#

Storing of SQL commands

When coding we use an offline database (or sandbox) that allows us to change whatever we like duirng development. As we make changes to the database we store the SQL commands that are used to make these changes.

Getting tired of copying and pasting the commands back into the SQL manager once we took the changes live I decided to write a some code to read from an XML file and implement the changes. 

Structure of the XML file

The strurcture we decided on was something that was general enough to allow for room to grow, so we came up with this:

XML structure:

<commands>
  <command type="sqlcommandtype">

    SQLCommandText
  </command>
  ...
</commands> 

Where:
  • sqlcommandtype: is the type of commnad: select, insert, update, alter, etc or disabled
  • SQLCommandText: is the actual command

Example of XML file

Here is an example of the a file:
<?xml version="1.0" encoding="utf-8"?>
<commands>
  <command type="disable">
    CREATE TABLE TempOrdersHeaderTbl (
    [TOHeaderID] AUTOINCREMENT,
    [CustomerID] INT ,
    [OrderDate] DateTime,
    [RoastDate]  DateTime,
    [RequiredByDate]  DateTime ,
    [ToBeDeliveredByID] INT ,
    [Confirmed] YESNO,
    [Done] YESNO,
    [Notes] MEMO,
    CONSTRAINT [pk_TOHeaderID] PRIMARY KEY (TOHeaderID)
    )
  </command>
  <command type="create">
      CREATE TABLE TempOrdersLinesTbl (
      [TOLineID] AUTOINCREMENT,
      [TOHeaderID]  INT ,
      [ItemID]  INT ,
      [ServiceTypeID] INT,
      [Qty] SINGLE ,
      [PackagingID] INT,
      [OriginalOrderID] INT,
      CONSTRAINT [pk_TOLineID] PRIMARY KEY (TOLineID)
      )
  </command>
  <command type="alter">
      ALTER ABLE CustomersTbl ADD SendDeliveryConfirmation YESNO
  </command>
  <command type="select">
    SELECT        TOP 10 CompanyName, SendDeliveryConfirmation
    FROM            CustomersTbl
  </command>

</commands>


The c# code to read and run

For the C3 I first did an html file:
<head runat="server">
    <title>XML TO SQL Read and run </title>
</head>
<body>
    <form id="frmXMLToSQL" runat="server">
    <div>
      <h1>Read XML and do the SQL</h1>
      File name:&nbsp;
      <asp:TextBox ID="FileNameTextBox" runat="server"
        Text="C:\test\SQLCommands.xml"
        Width="750px" />
      &nbsp;&nbsp;
      <asp:Button ID="GoButton" Text="Go" runat="server" onclick="GoButton_Click" />
      <br />
      <br />
      <asp:GridView ID="gvSQLResults" runat="server">
      </asp:GridView>
      <br />
      <asp:Panel ID="pnlSQLResults" runat="server">
      
      </asp:Panel>
      <br />

    </div>
    </form>
</body>
</html>

Logic of HTML

The logic it to take a file and read it run the commands store the commands and results in an class, then display the results in the Gridview on the form. For SELECT statements each statement would add a new gridview to the asp panel pnlSQLResults to display the results.

I am sure this can be made neater but is it really a back room task so the basics are in place.

Logic of C# code behind

the code behind essentially reads the data from the XML file, and places the result in a List of resutls. It looks for the XML node type that is an element, then when that element's name is "command", it then reads the value of the field that follows in since this is the SQL command.

In the code also It calls a general DB class that I wrote, that simply pulls the projects connection string for the database opens the connection and then depending on the instruction performs a nonQuerySQL or returns a DataSet with the data for Query based SQL. If you need this then comment and I will paste that.

So here is the code behind:
 using System;
using System.Collections.Generic;
using System.Web.UI.WebControls;
using System.Xml;
using TrackerDotNet.classes;

namespace MyApp.GeneralClasses
{
  public partial class XMLtoSQL : System.Web.UI.Page
  {

    class SQLCommand
    {
      string _type;
      string _sql;
      bool _result;

      public SQLCommand()
      {
        _type = "";
        _sql = "";
        _result = false;
      }
      public string type { get { return _type; } set { _type = value; } }
      public string sql { get { return _sql; } set { _sql = value; } }
      public bool result { get { return _result; } set { _result = value; } }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
    }

    protected void GoButton_Click(object sender, EventArgs e)
    {
      List<SQLCommand> _SQLCommands = new List<SQLCommand>();
      string _FileName = FileNameTextBox.Text;
      _FileName = _FileName.Replace(@"\",@"\\");

      XmlReader _XmlReader = XmlReader.Create(_FileName);

      while (_XmlReader.Read())
      {
        if ((_XmlReader.NodeType == XmlNodeType.Element) && (_XmlReader.Name == "command"))
        {
          SQLCommand _SQLCommand = new SQLCommand();
       
          _SQLCommand.type = _XmlReader.GetAttribute("type");
          _XmlReader.Read();      // next should be value
          _SQLCommand.sql= _XmlReader.Value;

          _SQLCommands.Add(_SQLCommand);
        }
      }

      for (int i = 0; i < _SQLCommands.Count; i++)
      {
        if (_SQLCommands[i].type == "select")
        {
          GridView _gvSelectResult = new GridView();
          System.Data.DataSet _ds = RunSelect(_SQLCommands[i].sql);
          _SQLCommands[i].result = (_ds != null);

          _gvSelectResult.DataSource = _ds;

          _gvSelectResult.DataBind();
          pnlSQLResults.Controls.Add(_gvSelectResult);
        }
        else if (_SQLCommands[i].type != "disalbled")
          _SQLCommands[i].result = RunCommand(_SQLCommands[i].sql);
      }

      // assign resutls to result panel
      gvSQLResults.DataSource = _SQLCommands;
      gvSQLResults.DataBind();
    }

    private System.Data.DataSet RunSelect(string pSQL)
    {
      GeneralDb _
GeneralDb = new GeneralDb();

      System.Data.DataSet _DataSet = _
GeneralDb.ReturnDataSet(pSQL);

      return _DataSet;
    }
    private bool RunCommand(string pSQL)
    {
     
GeneralDb _GeneralDb = new GeneralDb();
      bool _Success = false;

      _Success = _
GeneralDb.ExecuteNonQuerySQL(pSQL);
      return _Success;
    }
  }
}

Conculsion

The result is awesome, as I code I change the XML file I can copy and paste the SQL commands from the development environment, and they are run in anyway. I also found that with the live site I can use this to run queries to check things in the live environment, obviously I needed to add some logic

Comments

Popular posts from this blog

Bitcoin / Cryptocurrency – what is it and how can I benefit

What is it I started investigating Bitcoin when it was worth just over $1000 a bitcoin. I was interested in what it was and how it worked. A lot of people are saying we missed the boat, but I believe that everyone should at least try put a little money in now, or at least use a faucet (see below) to make a little micro-currency. You can read a Wiki article about bitcoin and its history etc. But what you need to know is that it is a currency, that is independent of country. No one really knows who invented the concept of a cryptocurrency since the person who published the paper used a nom de plume. All new cryptocurrencies work more or less the same way as Bitcoin. So as I explain below I interchange these terms. Bitcoin is the original cryptocurrency. How Bitcoin works The currency releases a coin based on a mathematical formula. There will never be more than 21 million bitcoins (other cryptocurrencies do not work like this). Each bitcoin can have divided into one hundred mil

Migrating QuickBooks to Sage One Cloud accounting - Part 1 Exporting the data

Some notes Sage means sage one online accounts, wherever we say Sage we mean Sage one. The QuickBooks we used was version 2012 Professional. But most of the information is similar. What you need > You need the templates from Sage and the data from QuickBooks, see below for how to do these. Get Import Templates from Sage To get the templates for the items go to help.accounting.sageone.co.za/en_za/accounting/from-your-previous-accounting-system.html Although you can construct the templates from the information in this post you can download samples of the templates need. Below are the links they provide: Use the following downloads which are referenced in the guide: General Ledger Accounts Import Template Customer Import Template Customer Outstanding Invoices Import Template Supplier Import Template Supplier Outstanding Invoices Import Template Item Import Template   Another useful page that is hard to find on their system is: help.accounting.sageone.co.za/en_za/ac

Mindfullness Meditation and Depression - in a pod cast

Over the last 3 years I have found that meditation has really assisted me, in resolving the depression I have had. I have planned to write about my experience for a while, and hopefully will get around to doing that. However today having listen to the latest podcast by Dan Harris on 10% happier, so many things just clicked in to place, so I want to share it. To understand what is covered in the podcast I would recommend you understand what mindfulness meditation is, and what the default mode network in the brain is (see links below). Listen to the pod cast - but here are some extracts (which I do not have permission to publish - and will remove if asked). Link to podcast: tumello.com/listen/H11a5NYJf; or itunes.apple.com/us/podcast/10-happier-with-dan-harris/id1087147821 Chuck Raison, a psychiatrist and a professor of psychiatry at the University of Wisconsin-Madison School of Medicine and Public Health, and Vlad Maletic, a clinical professor of neuropsychiatry and behavior sc