helperfunctions.bas

CodeWizardRC2.zip for Rainbow Souce Code

Attribute VB_Name = "HelperFunctions"
Function GetDrop(SPname As String) As String
    Dim s As String
    s = "if exists (select * from dbo.sysobjects where id = object_id(N'" & SPname & "') and OBJECTPROPERTY(id, N'IsProcedure') = 1)" & vbCrLf
    s = s & "drop procedure " & SPname & vbCrLf
    s = s & "GO" & vbCrLf
    s = s & vbCrLf
    GetDrop = s
End Function

Function GetCSXMLDoc(CSname As String) As String
    Dim s As String
    s = s & "/// <summary>" & vbCrLf
    s = s & "/// " & CSname & vbCrLf
    s = s & "/// </summary>" & vbCrLf
    GetCSXMLDoc = s
End Function

Function GetCSParamDef(c As Column, Optional Output As Boolean = False) As String
    Dim s As String

    If Output Then
        s = s & vbTab & "SqlParameter parameter" & c.name & " = new SqlParameter(""@" & c.name & """, " & GetCSharpSQLFullDataType(c) & ");" & vbCrLf
        s = s & vbTab & "parameter" & c.name & ".Direction = ParameterDirection.Output;" & vbCrLf
        s = s & vbTab & "myCommand.Parameters.Add(parameter" & c.name & ");" & vbCrLf
    Else
        s = s & vbTab & "SqlParameter parameter" & c.name & " = new SqlParameter(""@" & c.name & """, " & GetCSharpSQLFullDataType(c) & ");" & vbCrLf
        s = s & vbTab & "parameter" & c.name & ".Value = " & c.name & ";" & vbCrLf
        s = s & vbTab & "myCommand.Parameters.Add(parameter" & c.name & ");" & vbCrLf
    End If
    
    GetCSParamDef = s
End Function

Function GetIdentityKey(oTable As SQLDMO.Table) As String
    On Error Resume Next
    
    Dim col As SQLDMO.Column
    Dim IdentityCol As String
    Dim SKey As String
    
    SKey = oTable.Keys(1).KeyColumns(1)
    
    For Each col In oTable.Columns
        If col.Identity Then
            IdentityCol = col.name
        End If
        If col.IsRowGuidCol Then
            IdentityCol = col.name
        End If
    Next

    If SKey = IdentityCol Then GetIdentityKey = SKey
End Function

Function GetSQLFullDataType(col As SQLDMO.Column) As String
    Dim DataType As String
    Dim Length As Integer
    Dim s As String
    
    DataType = col.DataType
    Length = col.Length
    If Length > 8000 Then Length = 8000 'SQL_VARIANT
    
    If InStr(1, "int bigint bit datetime image int money ntext real smalldatetime smallint smallmoney sql_variant text timestamp tinyint uniqueidentifier", DataType) > 0 Then
        s = DataType
    Else
        s = DataType & "(" & Length & ")"
    End If
    
    GetSQLFullDataType = s

End Function

Function GetCSharpDataType(SQLDataType As String) As String
    
    Dim s As String
    
    Select Case SQLDataType
    
        Case "bit"
            s = "Boolean"
            
        Case "tinyint"
            s = "Byte"
            
        Case "binary", "image", "timestamp", "varbinary"
            s = "Byte[]"
            
        Case "datetime", "smalldatetime"
            s = "DateTime"
            
        Case "decimal", "money", "numeric", "smallmoney"
            s = "Decimal"
            
        Case "float"
            s = "Double"
            
        Case "uniqueidentifier"
            s = "Guid"
            
        Case "smallint"
            s = "Int16"
            
        Case "int"
            s = "int"
           
        Case "bigint"
            s = "Int64"
           
        Case "sql_variant"
            s = "Object"
           
        Case "real"
            s = "Single"
           
        Case "char", "nchar", "ntext", "nvarchar", "text", "varchar"
            s = "string"
            
        End Select
    
    GetCSharpDataType = s
End Function

Function GetCSharpSQLFullDataType(col As SQLDMO.Column) As String
    Dim DataType As String
    Dim Length As Integer
    Dim s As String
    
    DataType = col.DataType
    Length = col.Length
    If Length > 8000 Then Length = 8000 'SQL_VARIANT
    
    If InStr(1, "int bigint bit datetime image int money ntext real smalldatetime smallint smallmoney sql_variant text timestamp tinyint uniqueidentifier", DataType) > 0 Then
        s = GetCSharpSQLDataType(DataType)
    Else
        s = GetCSharpSQLDataType(DataType) & ", " & Length
    End If
    
    GetCSharpSQLFullDataType = s

End Function

Function GetCSharpSQLDataType(SQLDataType As String) As String
    Dim d As Dictionary
    
    Set d = New Dictionary
    
    d.Add "bigint", "BigInt"
    d.Add "binary", "Binary"
    d.Add "bit", "Bit"
    d.Add "char", "Char"
    d.Add "datetime", "DateTime"
    d.Add "decimal", "Decimal"
    d.Add "numeric", "Decimal"
    d.Add "float", "Float"
    d.Add "image", "Image"
    d.Add "int", "Int"
    d.Add "money", "Money"
    d.Add "nchar", "NChar"
    d.Add "ntext", "NText"
    d.Add "nvarchar", "NVarChar"
    d.Add "real", "Real"
    d.Add "smalldatetime", "SmallDateTime"
    d.Add "smallint", "SmallInt"
    d.Add "smallmoney", "SmallMoney"
    d.Add "text", "Text"
    d.Add "timestamp", "Timestamp"
    d.Add "tinyint", "TinyInt"
    d.Add "uniqueidentifier", "UniqueIdentifier"
    d.Add "varbinary", "VarBinary"
    d.Add "varchar", "VarChar"
    d.Add "variant", "Variant"
    
    GetCSharpSQLDataType = "SqlDbType." + d.Item(SQLDataType)
End Function

Function ColumnExists(oTable As SQLDMO.Table, name As String) As Boolean
    Dim col As SQLDMO.Column

    For Each col In oTable.Columns
        If (col.name = name) Then ColumnExists = True
    Next
End Function

'Added sub for RC2 - RV 2003/12/09
Sub GetTableProcNames(iTName As String, iPName As String, ByRef oTName As String, ByRef oPName As String)
    Dim RootTableName As String
    Dim ProcName As String
    Dim NameLen As Integer
    
    If InStr(iTName, "rb_") > 0 Then
        RootTableName = Mid(iTName, 4)
        ProcName = "rb_" & iPName
    Else
        RootTableName = iTName
        ProcName = iPName
    End If
    NameLen = Len(RootTableName)
    If InStr(NameLen, RootTableName, "s") > 0 Then
        RootTableName = Left(RootTableName, NameLen - 1)
    End If
    oTName = RootTableName
    oPName = ProcName
End Sub