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.