Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Announcement | We'll be doing maintenance during the next 2-3 hours, which may impact your experience. Thanks for your patience as we work on improving the community!

SQL Script for outputting Model Class File

Highlighted
Asteroid

I am using an SQL Script that takes table name and as input and print its C# Model class as output based on that tables fields 

 

My Questions :

Is there anyway to create a workflow using Alteryx Designer that takes a list of tables and pass table name one by one to that script and generate each file for each table

 

Here is the script i am using in SQL that outputs Model Class 

 

declare @TableName sysname = 'lpcms_User_Type_Menu'
declare @Result varchar(max) = 'public class ' + @TableName + '
{'

select @Result = @Result + '
    public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
    select 
        replace(col.name, ' ', '_') ColumnName,
        column_id ColumnId,
        case typ.name 
            when 'bigint' then 'long'
            when 'binary' then 'byte[]'
            when 'bit' then 'bool'
            when 'char' then 'string'
            when 'date' then 'DateTime'
            when 'datetime' then 'DateTime'
            when 'datetime2' then 'DateTime'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then 'decimal'
            when 'float' then 'double'
            when 'image' then 'byte[]'
            when 'int' then 'int'
            when 'money' then 'decimal'
            when 'nchar' then 'string'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'float'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'long'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'string'
            else 'UNKNOWN_' + typ.name
        end ColumnType,
        case 
            when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') 
            then '?' 
            else '' 
        end NullableSign
    from sys.columns col
        join sys.types typ on
            col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
    where object_id = object_id(@TableName)
) t
order by ColumnId

set @Result = @Result  + '
}'

print @Result
Highlighted
Alteryx Partner

Absolutely! The best part is that you don't need to do it row by row (you could if you want) but you can use Batch Macros to do all of it at the same time. You give it a list of things and the batch macro will run a process on each individual item. 

 

I would recommend watching this to start: https://community.alteryx.com/t5/Videos/Build-Your-First-Batch-Macro/td-p/52900 

 

If you have any questions please keep asking via the community

 

If this helps then mark as resolved so others can benefit


Happy Holidays

Joshua

Highlighted
Asteroid

Thanks for reply 

Can you provide any workflow for it according to my example if possible please 

 

Because i am trying to run my query in input tool but its not working (its running successfully in SQL Studio) 

Highlighted
Alteryx Partner

Sure, hopefully this gets close but you can modify if needed. 

 

This comes from the super smart @AmeliaG here: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Batch-Macro-Paramaterize-SQL-Statement...

 

I have also attached the workflow she created here for you. 

 

Let me know if this helps or you need something else! If it does please make sure to mark as resolved so others can benefit

Highlighted
Asteroid

As per my query written in SQL 

 

The output should be like this in a text file or in a one cell 

 

public class Carrier
{
public int ID { get; set; }

public Guid RowID { get; set; }

public DateTime DateCreated { get; set; }

public DateTime DateModified { get; set; }

public int CreatedBy { get; set; }

public int ModifiedBy { get; set; }

public string ChangeSource { get; set; }

public bool IsActive { get; set; }

public bool IsDeleted { get; set; }

public string JSONData { get; set; }

public string AlternateKey { get; set; }

public string Code { get; set; }

public string Name { get; set; }

public string Website { get; set; }

}

 

Carrier is the table name 

Labels