Results 1 to 10 of 10

Thread: MSSQL Stored Procedure to generate an Ext JS Model

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Sencha Premium Member
    Join Date
    Sep 2011
    Location
    Tamworth, NSW, Australia
    Posts
    1,287
    Vote Rating
    388
      0  

    Thumbs up 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,390
    Vote Rating
    498
      0  

    Default

    Thank you for the contribution.

    Regards,
    Scott.

  3. #3
    Sencha Premium Member
    Join Date
    Dec 2010
    Posts
    12
    Vote Rating
    0
      0  

    Default 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
    18,583
    Vote Rating
    873
      0  

    Default

    100 fields, in a grid? Ewww :P
    Evan Trimboli
    Twitter - @evantrimboli

  5. #5
    Sencha Premium Member
    Join Date
    Dec 2010
    Posts
    12
    Vote Rating
    0
      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
    Sencha Premium Member
    Join Date
    Sep 2011
    Location
    Tamworth, NSW, Australia
    Posts
    1,287
    Vote Rating
    388
      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 :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •