Month: June 2015

Dto Generation

I’m finally getting around to my original goal of Dto Generation. I could probably have skipped everything that came before but that’s too late now.

Yesterday in my searches I discovered something new and wondrous Code Generation and T4 Text Templates. This seems to be exactly what I am looking for. Since my requirements are for run-time text generation, I’m following this guide verbatim. I’ve thrown the new new text templates into a new folder for clarity.

Continue reading Dto Generation

Actors keep showing up, but where’s the audience?

I am the audience and I am making this application for my own use. I believe that software benefits greatly when the developers are users too. When the developer are primary users, then the software may benefit even more so since there is then an instantaneous user feedback loop.

This post is finally going to deal with DTO generation I hope. We’ll see. The lead picture on this post shows what the project looks like right now. Not much there right now.

The easiest way to start off getting column information from the DB I know of is GetSchema. And supplying the name of one of these SQL Server Schema Collections. My DBs are fairly small, so this approach should be within reason for now.

        private void DownloadSchema(Messages.SqlConnectionMessage sqlConnectionMsg)
        {
            try
            {
                using(var connection = new SqlConnection(sqlConnectionMsg.ConnectionString))
                {
                    connection.Open();
                    var columnTable = connection.GetSchema("Columns");
                    //Do something with these columns
                }
            }
            catch(System.Exception)
            {
                Sender.Tell(new Messages.SqlConnectionFailureMessage());
            }
        }

I’ve heard of SQL Server Management Objects and it doesn’t seem like a bad option. I might go back later and use that.

For now, I’ve got these columns in a collection

                    connection.Open();
                    var columnTable = connection.GetSchema("Columns");
                    var columnList = from DataRow row
                                     in columnTable.Rows
                                     select new Messages.DataColumnMessage(row);
                    var first = columnList.First();
                    //Do something more with these columns

By default, I’m putting immutable objects under the “Messages” category as a matter of principle. Not because I necessarily know that I will use them as a message. Here is the DataColumnMessage, the behemoth that it is

    internal class DataColumnMessage
    {
        #region Public Constructors
        /// <summary>
        /// A datarow from the column table. https://msdn.microsoft.com/library/ms254969.aspx
        /// </summary>
        /// <param name="row"></param>
        public DataColumnMessage(DataRow row)
        {
            TableCatalog = row["table_catalog"].ToString();
            TableSchema = row["table_schema"].ToString();
            TableName = row["table_name"].ToString();
            ColumnName = row["column_name"].ToString();
            OrdinalPosition = Convert.ToInt32(row["ordinal_position"]);
            ColumnDefault = row["column_default"].ToString();
            IsNullable = row["is_nullable"].ToString().Equals("YES", StringComparison.InvariantCultureIgnoreCase);
            DataType = row["data_type"].ToString();
            CharacterMaximumLength = Helpers.ConvertNullable(Convert.ToInt32, row["character_maximum_length"]);
            CharacterOctetLength = Helpers.ConvertNullable(Convert.ToInt32, row["character_octet_length"]);
            NumericPrecision = Helpers.ConvertNullable(Convert.ToInt32, row["numeric_precision"]);
            NumericPrecisionRadix = Helpers.ConvertNullable(Convert.ToInt32, row["numeric_precision_radix"]);
            NumericScale = Helpers.ConvertNullable(Convert.ToInt32, row["numeric_scale"]);
            DatetimePrecision = Helpers.ConvertNullable(Convert.ToInt32, row["datetime_precision"]);
            CharacterSetCatalog = Helpers.ConvertNullable(Convert.ToString, row["character_set_catalog"]);
            CharacterSetName = Helpers.ConvertNullable(Convert.ToString, row["character_set_name"]);
            CollationCatalog = Helpers.ConvertNullable(Convert.ToString, row["collation_catalog"]);
        }
 
        #endregion Public Constructors
 
        #region Public Properties
 
        public int? CharacterMaximumLength
        { get; private set; }
 
        public int? CharacterOctetLength
        { get; private set; }
 
        public string CharacterSetCatalog
        { get; private set; }
 
        public string CharacterSetName
        { get; private set; }
 
        public string CollationCatalog
        { get; private set; }
 
        public string ColumnDefault
        { get; private set; }
 
        public string ColumnName
        { get; private set; }
 
        public string DataType
        { get; private set; }
 
        public int? DatetimePrecision
        { get; private set; }
 
        public bool IsNullable
        { get; private set; }
 
        public int? NumericPrecision
        { get; private set; }
 
        public int? NumericPrecisionRadix
        { get; private set; }
 
        public int? NumericScale
        { get; private set; }
 
        public int OrdinalPosition
        { get; private set; }
 
        public string TableCatalog
        { get; private set; }
 
        public string TableName
        { get; private set; }
 
        public string TableSchema
        { get; private set; }
 
        #endregion Public Properties
    }

Odds are good that I won’t use all those fields. But I typed them out anyway. I also created a helper function.

namespace LeastCost.Messages
{
    public static class Helpers
    {
        #region Public Methods
        public static T ConvertNullable<T>(Func<object, T> convert, object valToConvert)
        {
            return (valToConvert == DBNull.Value) ? default(T) : convert(valToConvert);
        }
 
        #endregion Public Methods
    }
}

Now it is time to group these columns into tables.

        private void DownloadSchema(Messages.SqlConnectionMessage sqlConnectionMsg)
        {
            try
            {
                DataTable columnTable;
                using(var connection = new SqlConnection(sqlConnectionMsg.ConnectionString))
                {
                    connection.Open();
                    columnTable = connection.GetSchema("Columns");
                }
                ProcessColumnTable(columnTable);
            }
            catch(System.Exception)
            {
                Sender.Tell(new Messages.SqlConnectionFailureMessage());
            }
        }
 
        private void ProcessColumnTable(DataTable columnTable)
        {
            var columnList = from DataRow row
                             in columnTable.Rows
                             select new Messages.DataColumnMessage(row);
 
            var tableBuckets = new Dictionary<string, IList<Messages.DataColumnMessage>>();
            foreach(var column in columnList)
            {
                IList<Messages.DataColumnMessage> addToThisList;
                if(!tableBuckets.TryGetValue(column.TableName, out addToThisList))
                {
                    addToThisList = new List<Messages.DataColumnMessage>();
                    tableBuckets[column.TableName] = addToThisList;
                }
                addToThisList.Add(column);
            }
 
            foreach(var table in tableBuckets)
            {
                var msg = new Messages.TableMessage(table.Value);
                //Who do we tell to process these tables?
            }
        }

And the table message

    internal class TableMessage
    {
        #region Public Constructors
        public TableMessage(IList<DataColumnMessage> columns)
        {
            Columns = columns;
            Name = columns.First().TableName;
        }
 
        #endregion Public Constructors
 
        #region Public Properties
 
        public IList<DataColumnMessage> Columns
        { get; private set; }
 
        public string Name
        { get; private set; }
 
        #endregion Public Properties
    }

The important question is now who do we tell about these TableMessages? How about

namespace LeastCost.Actors
{
    internal class DtoGeneratorActor: ReceiveActor
    {
        #region Public Constructors
        public DtoGeneratorActor()
        {
            Receive<Messages.TableMessage>(x => System.Diagnostics.Debug.WriteLine("Acknowledge -" + x.Name));
        }
 
        #endregion Public Constructors
    }
}

This actor’s sole purpose will be to generate the DTO’s text. And since it’s purpose is limited, let’s make it a router. The App.config looks like this

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="akka" type="Akka.Configuration.Hocon.AkkaConfigurationSection, Akka" />
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
  </startup>
  <akka>
    <hocon>
      <![CDATA[
          akka {
            actor{
              deployment{
                /sqlConnectRouter {
                  router = smallest-mailbox-pool
                  resizer {
                    enabled = on
                    lower-bound = 1
                    upper-bound = 5
                  }
                }
                /dtoGeneratorRouter {
                  router = round-robin-pool
                  nr-of-instances = 15
                }
              }
            }
          }
      ]]>
    </hocon>
  </akka>
</configuration>

The SqlConnectionActor is updated to spread messages

        private void ProcessColumnTable(DataTable columnTable)
        {
            var columnList = from DataRow row
                             in columnTable.Rows
                             select new Messages.DataColumnMessage(row);
 
            var tableBuckets = new Dictionary<string, IList<Messages.DataColumnMessage>>();
            foreach(var column in columnList)
            {
                IList<Messages.DataColumnMessage> addToThisList;
                if(!tableBuckets.TryGetValue(column.TableName, out addToThisList))
                {
                    addToThisList = new List<Messages.DataColumnMessage>();
                    tableBuckets[column.TableName] = addToThisList;
                }
                addToThisList.Add(column);
            }
 
            var dtoWorkers = Context.ActorSelection("/user/" + Actors.ActorPaths.DtoGeneratorPool);
            foreach(var table in tableBuckets)
            {
                var msg = new Messages.TableMessage(table.Value);
                dtoWorkers.Tell(msg);
            }
        }

I’m horrible at this. I can do everything EXCEPT create a DTO. I’m going to push that to the next blog post, AGAIN. All the code so far is here on github.

Making it up as I go

My very first action on this new post was to tweak the color scheme. Not because it provides any utilitarian purpose yet, but because I have a fondness for the dark theme and is my favorite Visual Studio theme. So I have spent fifteen minutes playing around with different color combinations as provided by MahApps.Metro.

Now that pretty colors are sufficient to continue, I’m actually going to make some functionality. Well, after I explain this post’s name. I have a bad tendency to think up an idea and just go start working on it, making it up as I go. This started out as one of those times before realizing that a couple of pieces of paper outlining the general flow is not a bad idea. So I have a lot of scrap paper scattered about my desk with scribblings so incomprehensible that future archeologists are going to frame them in a museum and claim the use of hieroglyphics didn’t end until a much later time period than previously believed.

Continue reading Making it up as I go

Because I can

Without exception, my best and worst ideas are motivated by the reason “because I can”. This is likely poor motivation but I stand by it. Unfortunately, this is not this post’s motivation, which is “because it could be useful”. So, I’m taking a detour from my usual topic of my video game.

Today’s topic is rather customized Data Transfer Object (DTO) generation. This is definitely a matter of utility.

This part is where “because I can” comes in. I shall use C#, WPF, Akka.NET, and Visual Studio 2015 RC.

The minimal viable product for me is the ability to connect to a database, and then dump all tables into a DTO C# class. Now the first question you might ask is “is that not the entire scope of this?” And you would be mostly right, except that I also have a wishlist a mile long but that will come later if at all.

So here I go:BecauseICan_2

First order of business is the NuGet Package for one of my favorite libraries: MvvmLight. BecauseICan_3

Actually, I’ve installed a couple of packages.BecauseICan_4

Data Connection Dialog because I’m lazy and this seemed the easiest and quickest way for me to get a generic sql connection going. MahApps.Metro because they are great and make visual design easier on me. Akka.NET because I’m planning on farming out the file creations to Actors (ha, never heard of actors farming). MvvmLight to simplify my WPF. And the others are supporting packages.

I’ve made a very simple form to start off with BecauseICan_5

The creator of MvvmLight has an interesting blog post here which I used and butchered the concept for my own purposes.

There is now a static resource ViewModelLocator, of which one is in App.xaml

      <locator:ViewModelLocator x:Key="VmLocator"/>

That class looks like this

    public class ViewModelLocator
    {
        static ViewModelLocator()
        {
            ServiceLocator.SetLocatorProvider(() => SimpleIoc.Default);
 
            SimpleIoc.Default.Register<ActorSystem>(() => ActorSystem.Create("LeastCost"), true);
            SimpleIoc.Default.Register<AnalysisViewModel>();
        }
        public AnalysisViewModel AnalysisVm
        {
            get
            {
                return ServiceLocator.Current.GetInstance<AnalysisViewModel>();
            }
        }
    }

And is used like this

<Controls:MetroWindow x:Class="LeastCost.MainWindow"
                      xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
                      xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
                      xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
                      xmlns:Controls="clr-namespace:MahApps.Metro.Controls;assembly=MahApps.Metro"
                      xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
                      xmlns:local="clr-namespace:LeastCost"
                      mc:Ignorable="d"
                      DataContext="{Binding Source={StaticResource VmLocator}, Path=AnalysisVm }"
                      Title="LeastCost DTO Generation" Height="350" Width="525">
  <Grid>
    <Button Content="Connect to DB" Command="{Binding CmdConnect}" Width="150" Height="30"/>
  </Grid>
</Controls:MetroWindow>

The viewmodel being used in the window is this

    public class AnalysisViewModel
    {
        private readonly ActorSystem _actorSystem;
 
        public AnalysisViewModel(ActorSystem actorSystem)
        {
            _actorSystem = actorSystem;
            CmdConnect = new RelayCommand(DialogConnect);
        }
 
        public RelayCommand CmdConnect { get; private set; }
 
        private void DialogConnect() ....
    }

So when I click the button I get
BecauseICan_6
BecauseICan_7

Next post on this topic will deal with how to use that sql connection to get all the data I need and dump it to custom C# class files.

Before I forget, all my code on this is here on github.