SQL, C#, Code Generator, Sql Meta Information, T4 Templates
A C# Strongly Typed Representation of Sql Schema
Introducing Sql Meta! My latest code generation tool.
- What is it?
- A C# assembly / source code that uses dapper and ADO to query information about your SQL database and return the results as strongly typed C# objects.
- Why would I use it?
- You probably shouldn't :) Entity Framework is pretty solid and this is my take on the benefits of auto code generation without any of the draw backs of the current iteration of Entity Framework. I do hear that EF 7 is going to be good.
- In combination with T4 Templates you could use this library to generate code/classes/patterns in any language that is based on your SQL Schema definitions.
- One example template, included in this post, simply loops through each sql table and uses 'Sql Meta' queried schema info to create a C# class file that represents all the columns of the table as C# auto properties. Other examples are more complex and can create an entire data access layer using Dapper, SQL Meta, and a data base context pattern similar to the Entity Framework. The real offering here is just the meta data of your Sql Database.
- Did you write All of it?
- No way. I looked up a bunch of sql queries for sql schema information and modified them to fit my needs. I borrowed the T4 template manager from other sources as linked in the top of the files.
- I also Sql traced my Sql Server while using the Entity Framework 'Wizard' to steal some of the queries they use to build the EDMX file.
- Can I contribute?
- Heck yes! I think this is wide open for template contributions. Git Hub Repo
- What problem(s) are you solving?
- Many popular patterns are heavily inspired by the definition of the persistent data often referred to as ORM, Domain Driven Design, or Active Record. It simply means that if you have a 'Widget' table in SQL, it then makes sense to have corresponding classes/ representations of that 'object' as well as the default plumbing of inserting, retrieving, and deleting the record in whatever language/stack you are using. So this is Yet Another Data Access Solution with the ability to spawn whatever code you want based on your sql schema. *I'm not promoting or claiming that these patterns are correct or useful across the board, just that they are popular and largely inspired by the persistent storage definitions which begs the need to query sql schema.
- Boredom of writing plumbing / busy work code when I could be solving new and more exciting problems, yet not sacrificing ANY code quality, testability, or separation of concerns by using the Entity Framework which lacks an interface for their context class out of the box.
- Take 100% control of my database using the smallest lightest weight and fastest tools available like Dapper or a tool of your choosing while enjoying the rapid development of boiler plate data access code automatically generated.
Download the VS2013 Project DO NOT FORGET to update the connection string in the manager (line 38ish) and or the tests (MetaRepositoryTests line 12ish) to actually make it work!
Let's see some code!
This is the Json results of a SQL Database with an Order and Customer table.
This template loops through the tables and creates a simple C# class representation.
This template loops through the tables and creates stored procedures against them.
This template literally creates an entire data access layer by reading all expected sprocs and custom sprocs as well. It makes a TON of assumptions so unless your schema is exactly like mine, this would only serve as a starting point for another project.