Practical_PowerShell/02d_Generate_CSharpClasses.ps1 at master · SQLJana/Practical_PowerShell · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121

##-----------------------------------------------------------------------
##
#******************* Fourth attempt *****************************
##
##-----------------------------------------------------------------------


#Cleanup the old files
#Get-Item $GeneratorLocation\Output\*.cs | Remove-Item


# Let us make a function by parameterizing key inputs
#-----------------------------------------------------------------------

#######################
<#
.SYNOPSIS
    Generates C# classes based on table structures - one for each table

.DESCRIPTION
    Pass in the appropriate inputs. DatabaseName can come from the pipleline

.EXAMPLE

    Out-CSharpClass `
        -Database 'DataStudio4' `
        -Verbose

    Example takes the defaults except for the DatabaseName parameter

.EXAMPLE

    Out-CSharpClass `
        -Database 'DataStudio4' `
        -GeneratorSQLFile 'C:\1Presentations\Practical_PowerShell\02_Generate_CSharpClasses\ModelGenerator.sql' `
        -TableListSQL 'SELECT name FROM sys.tables' `
        -OutputFolder 'C:\1Presentations\Practical_PowerShell\02_Generate_CSharpClasses\Output' `
        -Namespace 'MyCompany.Business' `
        -PlaceHolderSchema '&Schema' `
        -PlaceHolderTableName '&TableName' `
        -PlaceHolderNamespace '&Namespace' `
        -Verbose

    This simple example generates classes for database DataStudio4 and specifies each parameter


.NOTES

    This function is for illustration purposes only!

Version History
    v1.0  - Jana Sattainathan [Twitter: @SQLJana] [Blog: sqljana.wordpress.com] - Initial Release
#>
function Out-CSharpClass
{

    [CmdletBinding()]
    param(
            [Parameter(Mandatory=$false)][string]$ServerInstance = 'localhost',

            [Parameter(Mandatory=$true)][string]$DatabaseName,

            [ValidateScript({Test-Path $_ -PathType ‘Leaf’})]
            [Parameter(Mandatory=$false)][string]$GeneratorSQLFile = 'C:\1Presentations\Practical_PowerShell\02_Generate_CSharpClasses\ModelGenerator.sql',

            [Parameter(Mandatory=$false)][string]$TableListSQL = 'SELECT name FROM sys.tables',

            [ValidateScript({Test-Path $_ -PathType ‘Container’})]
            [Parameter(Mandatory=$false)][string]$OutputFolder = 'C:\1Presentations\Practical_PowerShell\02_Generate_CSharpClasses\Output',

            [Parameter(Mandatory=$false)][string]$Namespace = 'MyCompany.Business',

            [Parameter(Mandatory=$false)][string]$PlaceHolderSchema = '&Schema',

            [Parameter(Mandatory=$false)][string]$PlaceHolderTableName = '&TableName',

            [Parameter(Mandatory=$false)][string]$PlaceHolderNamespace = '&Namespace'
    )


    #Get the list of tables in the database to generate c# models for
    $tables = Invoke-Sqlcmd2 `
                -ServerInstance $ServerInstance `
                -Database $DatabaseName `
                -Query $TableListSQL `
                -As DataRow `
                -Verbose


    foreach ($table in $tables)
    {
        #Decide on the file name for table
        $tableName = $table[0]
        $outputFile = "$OutputFolder\$tableName.cs"
        Write-Verbose "Generating for $tableName to file $outputFile"

        #Warn if the file already exists!
        if (Test-Path -LiteralPath $outputFile)
        {
            Write-warning "$outputFile already exists. Overwriting!"
        }

        #Replace variables with values (returns an array that we convert to a string to use as query)
        $GeneratorSQLFileWSubstitutions = (Get-Content $GeneratorSQLFile).
                                                Replace($PlaceHolderSchema,'dbo').
                                                Replace($PlaceHolderTableName, $tableName).
                                                Replace($PlaceHolderNamespace, $Namespace) | Out-String

        Write-verbose 'Ouputing for $tableName to $outputFile'

        #The command generates .cs file content for model using "PRINT" statements which then gets written to verbose output (stream 4)
        # ...capture the verbose output and redirect to a file
        (Invoke-Sqlcmd2 `
                -ServerInstance $ServerInstance `
                -Database $DatabaseName `
                -Query $GeneratorSQLFileWSubstitutions `
                -Verbose) 4> $outputFile

    }
}