aeslinger0/sqlsharpener

Parses SQL files to create a meta-object hierarchy with which you can generate C# code such as stored procedure wrappers.

sqlsharpener's Issues

Dependancy Issues with Microsoft.Data.Tools.Schema.Sql

I was attempting to test this library with generating my EF classes, and when I ran the "Custom Tool" from the content menu of my TT file, I get the following error:

Severity Code Description Project File Line Suppression State
Error Running transformation: System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.Data.Tools.Schema.Sql, Version=12.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.
File name: 'Microsoft.Data.Tools.Schema.Sql, Version=12.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'
at Microsoft.SqlServer.Dac.Model.TSqlModel..ctor(SqlServerVersion modelTargetVersion, TSqlModelOptions modelCreationOptions)
at SqlSharpener.MetaBuilder.LoadModel()
at SqlSharpener.MetaBuilder.get_Tables()
at Microsoft.VisualStudio.TextTemplating84159610F57874C491F71FE10F447E99B01EE123BEC660EE937086DB49B427A007E765001AD2BA8136FE03AB1833E02A9B714FE13BA1A2A0ED94B7BDBB97E585.GeneratedTextTransformation.TransformText()

WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value HKLM\Software\Microsoft\Fusion!EnableLog to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog]. TestGenerateEF C:\Users\bsimbeck\Documents\GNGV2\Test\TestGenerateEF\TestGenerateEF\TextTemplate1.tt 1

Based on what is generated, it looks like it is happening around the following line of my TT file
<# foreach(var tbl in meta.Tables){ #>

I have tried to include the missing DLL library in my solution as well as copy them to the Package folder with the rest of the DLL's it looks like SQLSharpener needs. I have included my solution folder as a zip file for you.
TestGenerateEF.zip

Would like to add Indexes property in the MetaBuilder class

I am really loving the library. I am using this to generate ORM for ServiceStack.OrmLite and would like to add a string enumeration for Indexes in the MetaBuilder so on my application startup I can not only generate the tables but also the indexes.
I am also willing to submit a pull request.

Add support for schema

I can get the schema name from a relationship object. However, I really need it on the table object as well.

XML/JSON support

if we had some sproc that returned an xml (or json) column, e.g.:

select id, name, tags=(
    select key [@key], value [@value]
    from tagTable where some_id=o.id
    for xml path('tag'), type)
from someTable o

and sqlsharpener could parse the for xml syntax to generate this corresponding dto:

public class sproc_result
{
    public int id;
    public string name;
    public List<tag> tags;

    public class tag { string key; string value; }
}

it seems very difficult but it would be a great feature

Updated NuGet

Hi, I was wondering if you plan on publishing an updated NuGet package with the view changes that were recently added?

Loading a view throws exception

I am having troubles referencing any of my views. The error occurs in the Column class on line 103.

The following expression returns an empty list. The column it fails on is the first [Id] column of the view. It says it is a SqlComputedColumn. I do not know enough about the Microsoft.SqlServer.Dac.Model classes to try to resolve myself.

SqlObject.GetReferenced(dac.Column.DataType).ToList()

Error       Running transformation: System.InvalidOperationException: Sequence contains no elements
   at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
   at SqlSharpener.Model.Column..ctor(TSqlObject tSqlObject, TSqlObject tSqlTable, IEnumerable`1 primaryKeys, IDictionary`2 foreignKeys)
   at SqlSharpener.Model.View..ctor(TSqlObject tSqlObject, IEnumerable`1 primaryKeys, IDictionary`2 foreignKeys)
   at SqlSharpener.MetaBuilder.<>c__DisplayClass10.<LoadModel>b__9(TSqlObject sqlView)
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at SqlSharpener.MetaBuilder.LoadModel(TSqlModel model)
   at SqlSharpener.MetaBuilder.LoadModel()
   at SqlSharpener.MetaBuilder.get_Tables()

SQL Scripts to repoduce

CREATE TABLE [dbo].[Port] (
    [Id]                 SMALLINT       IDENTITY (1, 1) NOT NULL,
    [Name]               VARCHAR (50)   NOT NULL,
    [Abbreviation]       VARCHAR (3)    NOT NULL,
    [Active]             BIT            NOT NULL,
    [Longitude]          DECIMAL (9, 6) NULL,
    [Latitude]           DECIMAL (9, 6) NULL,
    [PortTypeId]         SMALLINT       NOT NULL,
    [CreatedBy]          VARCHAR(255)   NOT NULL,
    [CreatedDateTime]    DATETIME2      NOT NULL,
    [UpdatedBy]          VARCHAR(255)       NULL,
    [UpdatedDateTime]    DATETIME2          NULL,
    [RowVersion]         ROWVERSION     NOT NULL,
    CONSTRAINT [PK_Port] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [CK_Port_Latitude_Latitude] CHECK ([Latitude] IS NULL AND [Longitude] IS NULL OR [Latitude] IS NOT NULL AND ([Latitude]>=(-90) AND [Latitude]<=(90)) AND [Longitude] IS NOT NULL AND ([Longitude]>=(-180) AND [Longitude]<=(180))),
    CONSTRAINT [FK_Port_PortTypeId] FOREIGN KEY ([PortTypeId]) REFERENCES [dbo].[PortType] ([Id]),
    CONSTRAINT [UQ_Port_Abbreviation] UNIQUE NONCLUSTERED ([Abbreviation] ASC),
    CONSTRAINT [UQ_Port_Name] UNIQUE NONCLUSTERED ([Name] ASC)
);

CREATE TABLE [dbo].[PortType]
(
    [Id]           SMALLINT      IDENTITY (1, 1) NOT NULL,
    [Name]         VARCHAR(20)   NOT NULL,
    [RowVersion]   ROWVERSION    NOT NULL,
    CONSTRAINT [PK_PortType] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [UX_PortType_Name] UNIQUE NONCLUSTERED ([Name] ASC)
)

CREATE VIEW [dbo].[RemotePort]
AS
SELECT [Port].*
  FROM [dbo].[Port]
 INNER JOIN [dbo].[PortType]
         ON [PortType].[Id] = [Port].[PortTypeId]
 WHERE [PortType].[Name] = 'Remote Port'