Hybrid View

  1. #1
    Touch Premium Member
    Join Date
    Sep 2011
    Location
    Tamworth, NSW, Australia
    Posts
    523
    Vote Rating
    10
    marc.fearby will become famous soon enough

      0  

    Thumbs up MSSQL Stored Procedure to generate an Ext JS Model

    MSSQL Stored Procedure to generate an Ext JS Model


    I've been using a stored procedure written by Leon C. Tayson to generate c# objects based on tables in SQL Server and decided to change it to generate an Ext JS model to save typing. Here is the version I've modified spit out an Ext.data.Model:

    Code:
    USE [MyDatabase]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    /*======================================================================
    ExtJS Model Generator
    This SP accepts a database object (table, view) name's parameter and 
    generates an ExtJS model based on the object's fields
    Sample Usage:
    USE Northwind
    EXEC procGenerateExtJsModel 'Shippers'
    Author: Marc Fearby
    Based on procGenerateEntityClass by Leon C. Tayson
    http://www.planet-source-code.com/vb...=1039&lngWId=5
    ======================================================================*/
    CREATE PROCEDURE [dbo].[procGenerateExtJsModel]
    @ObjectName varchar(100)
    ASDECLARE @name varchar(35), @type varchar(35)
    DECLARE objCursor CURSOR
    FOR
    SELECT sc.name, st.name type FROM syscolumns sc INNER JOIN systypes st ON st.xusertype = sc.xusertype WHERE Id=OBJECT_ID(@ObjectName)
    DECLARE @declarationCodes varchar(8000)
    SET @declarationCodes = ''
    OPEN objCursor
    FETCH NEXT FROM objCursor INTO @name, @type
    DECLARE @extType varchar(35) -- ExtJS type
    DECLARE @ctorParms varchar(4000)
    SET @ctorParms = ''
    IF @@FETCH_STATUS <> 0
     BEGIN
     CLOSE objCursor
     DEALLOCATE objCursor
     PRINT 'Error... Please check passed parameter'
     RETURN
    END
    WHILE @@FETCH_STATUS = 0
    BEGIN
     SET @extType = CASE
      WHEN @type LIKE '%char%' OR @type LIKE '%text%' THEN 'string'
      WHEN @type IN ('decimal', 'numeric')            THEN 'float'
      WHEN @type LIKE '%money%'                       THEN 'float'
      WHEN @type = 'bit'                              THEN 'boolean'
      WHEN @type LIKE '%int%'                         THEN 'int'
      WHEN @type LIKE '%uniqueidentifier%'            THEN 'string'
      WHEN @type LIKE '%datetime%'                    THEN 'date'
      ELSE 'auto' END
     -- Remove the 'tb' part of the table name
     IF LEFT(@ObjectName, 2) = 'tb' BEGIN
      SET @ObjectName = SUBSTRING(@ObjectName, 3, LEN(@ObjectName))
     END
     SET @declarationCodes = @declarationCodes + '        { name: ''' + @name + ''', type: ''' + @extType + ''' },' + CHAR(13)
     IF @extType = 'date' BEGIN 
      SET @declarationCodes = LEFT(@declarationCodes, LEN(@declarationCodes) - 4)
      SET @declarationCodes = @declarationCodes + ', dateFormat: ''MS''},' + CHAR(13)
     END
     FETCH NEXT FROM objCursor
     INTO @name, @type
    END
    PRINT 'Ext.define(''' + @ObjectName + ''', {'
    PRINT '    extend: ''Ext.data.Model'','
    PRINT '    fields: [' 
    PRINT LEFT(@declarationCodes, LEN(@declarationCodes) - 2)
    PRINT '    ]'
    PRINT '});'
    CLOSE objCursor
    DEALLOCATE objCursor
    Perhaps this might prove useful to somebody using ExtJS in a Microsoft environment.

  2. #2
    Sencha - Support Team scottmartin's Avatar
    Join Date
    Jul 2010
    Location
    Houston, Tx
    Posts
    9,085
    Vote Rating
    467
    scottmartin has a brilliant future scottmartin has a brilliant future scottmartin has a brilliant future scottmartin has a brilliant future scottmartin has a brilliant future scottmartin has a brilliant future scottmartin has a brilliant future scottmartin has a brilliant future scottmartin has a brilliant future scottmartin has a brilliant future scottmartin has a brilliant future

      0  

    Default


    Thank you for the contribution.

    Regards,
    Scott.

  3. #3
    Sencha Premium Member
    Join Date
    Dec 2010
    Posts
    12
    Vote Rating
    0
    sinclas is on a distinguished road

      0  

    Default Sencha Architect version?

    Sencha Architect version?


    Will there a version that could be used to create models within Sencha Architect. I have a table with >100 fields and would like to auto-generate the model. Currently I would comma-delineate it but it will not have the nice types and 'MS' date field config that the Stored Procedure creates for me. Any ideas?

    Thanks for the work, it works well!

    Steve

  4. #4
    Sencha - Ext JS Dev Team evant's Avatar
    Join Date
    Apr 2007
    Location
    Sydney, Australia
    Posts
    17,006
    Vote Rating
    650
    evant has a reputation beyond repute evant has a reputation beyond repute evant has a reputation beyond repute evant has a reputation beyond repute evant has a reputation beyond repute evant has a reputation beyond repute evant has a reputation beyond repute evant has a reputation beyond repute evant has a reputation beyond repute evant has a reputation beyond repute evant has a reputation beyond repute

      0  

    Default


    100 fields, in a grid? Ewww :P
    Evan Trimboli
    Sencha Developer
    Twitter - @evantrimboli
    Don't be afraid of the source code!

  5. #5
    Sencha Premium Member
    Join Date
    Dec 2010
    Posts
    12
    Vote Rating
    0
    sinclas is on a distinguished road

      0  

    Default


    100 Fields in a Model. 5 fields in a grid. The rest will be for entry in a pop-up editor window.

  6. #6
    Touch Premium Member
    Join Date
    Sep 2011
    Location
    Tamworth, NSW, Australia
    Posts
    523
    Vote Rating
    10
    marc.fearby will become famous soon enough

      0  

    Default


    Quote Originally Posted by sinclas View Post
    100 Fields in a Model. 5 fields in a grid. The rest will be for entry in a pop-up editor window.
    Sounds like you should just use a 5-field model on that grid and use a different model with appropriate fields for the popups.

    Sencha Architect generating models from the database does sound like a very nice feature to have, one day :-)