import * as azdata from 'azdata' ; export class DataScripter { private _resultSet : azdata . SimpleExecuteResult ; private _tableName : string ; private _insertTableDefintionSql : string ; private _datatypes : string [ ] = [ ] ; constructor ( resultSet , tableName ) { this . _tableName = tableName ; this . _resultSet = resultSet ; this . _insertTableDefintionSql = "" ; } public Script ( ) { let scripted = [ ] ; scripted . push ( this . getTempTableSql ( ) ) ; if ( this . hasIdentityColumn ( ) ) { scripted . push ( `\nset identity_insert [#temp${ this . _tableName }] on;\n\n` ) ; } scripted . push ( this . getInsertTableDefinitionSql ( ) ) ; for ( let i = 0 ; i !== this . _resultSet . rowCount ; i ++ ) { scripted . push ( this . getDataRow ( this . _resultSet . rows [ i ] , i , this . _resultSet . rowCount ) ) ; } if ( this . hasIdentityColumn ( ) ) { scripted . push ( `\nset identity_insert [#temp${ this . _tableName }] off;` ) ; } return scripted . join ( '\n' ) ; } private getInsertTableDefinitionSql ( ) { if ( this . _insertTableDefintionSql !== "" ) { return this . _insertTableDefintionSql ; } let insert = [ ] ; this . _resultSet . columnInfo . forEach ( column => { insert . push ( `[${ column . columnName }]` ) ; } ) ; return this . _insertTableDefintionSql = `insert [#temp${ this . _tableName }] (${ insert . join ( "," ) })` ; } private hasIdentityColumn ( ) { for ( let i = 0 ; i !== this . _resultSet . columnInfo . length ; i ++ ) { if ( this . _resultSet . columnInfo [ i ] . isIdentity ) { return true ; } } return false ; } private getTempTableSql ( ) { let columnInfo = this . _resultSet . columnInfo ; let create = [ ] ; create . push ( `--create table [#temp${ this . _tableName }] (` ) ; for ( let i = 0 ; i !== columnInfo . length ; i ++ ) { let dataType = this . getDataType ( columnInfo [ i ] ) ; let isNull = columnInfo [ i ] . allowDBNull ? " NULL" : "" ; let isIdentity = columnInfo [ i ] . isIdentity ? " identity" : "" ; let tail = ( i === columnInfo . length - 1 ) ? ");" : "," ; create . push ( `--[${ columnInfo [ i ] . columnName }] ${ dataType }${ isNull }${ isIdentity }${ tail }` ) ; } return create . join ( "\n" ) + "\n\n" ; } private getDataType ( columnInfo ) { this . _datatypes . push ( columnInfo . dataTypeName ) ; if ( columnInfo . dataTypeName . indexOf ( "char" ) >= 0 || columnInfo . dataTypeName . indexOf ( "text" ) >= 0 || columnInfo . dataTypeName . indexOf ( "binary" ) >= 0 ) { return `[${ columnInfo . dataTypeName }] (${ columnInfo . columnSize === 2147483647 ? "max" : columnInfo . columnSize })` ; } if ( columnInfo . dataTypeName === "decimal" || columnInfo . dataTypeName === "numeric" ) { return `[${ columnInfo . dataTypeName }] (${ columnInfo . numericPrecision },${ columnInfo . numericScale })` ; } if ( columnInfo . dataTypeName . indexOf ( "geometry" ) >= 0 ) { this . _datatypes [ this . _datatypes . length - 1 ] = "geometry" ; return "geometry" ; } return `[${ columnInfo . dataTypeName }]` ; } private escapeQuotes ( input ) { return input . replace ( /'+/g , "''" ) ; } private getDataRow ( row , currentIndex , rowCount ) { let rowData = [ ] ; try { for ( let i = 0 ; i !== this . _resultSet . columnInfo . length ; i ++ ) { if ( row [ i ] . isNull ) { rowData . push ( "NULL" ) ; continue ; } switch ( this . _datatypes [ i ] ) { case "varchar" : case "char" : case "text" : case "xml" : rowData . push ( `'${ this . escapeQuotes ( row [ i ] . displayValue ) }'` ) ; break ; case "nvarchar" : case "ntext" : case "nchar" : rowData . push ( `N'${ this . escapeQuotes ( row [ i ] . displayValue ) }'` ) ; break ; case "date" : case "datetime" : case "datetime2" : case "smalldatetime" : case "time" : rowData . push ( `'${ row [ i ] . displayValue }'` ) ; break ; case "decimal" : case "numeric" : case "real" : case "float" : case "money" : case "smallmoney" : rowData . push ( row [ i ] . displayValue . replace ( "," , "." ) ) ; break ; case "uniqueidentifier" : rowData . push ( `'{${ row [ i ] . displayValue }}'` ) ; break ; case "bit" : case "int" : case "bigint" : case "smallint" : case "tinyint" : case "geometry" : case "binary" : case "image" : case "timestamp" : case "varbinary" : rowData . push ( row [ i ] . displayValue ) ; break ; default : rowData . push ( `'${ row [ i ] . displayValue }'` ) ; break ; } } } catch ( e ) { if ( e instanceof Error ) { console . log ( e ) ; rowData . push ( "" ) ; } else { throw e ; } } let tail = " UNION ALL" ; if ( ( currentIndex + 1 ) % 5000 === 0 ) { tail = `; \nGO\n${ this . getInsertTableDefinitionSql ( ) }` ; } if ( currentIndex === rowCount - 1 ) { tail = ";" ; } return "select " + rowData . join ( "," ) + tail ; } }