Ứng dụng GenCode trong SQL Server

Declare @tenTable nvarchar(50)

set @tenTable='tbChungTu_ChiTiet' 



Declare @tenClass nvarchar(50)

set @tenClass=replace( @tenTable ,'tb','')



Declare @tenKhoaChinh nvarchar(50)

set @tenKhoaChinh=(

SELECT COLUMN_NAME

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

WHERE TABLE_NAME =@tenTable

  AND CONSTRAINT_NAME IN (

    SELECT CONSTRAINT_NAME

    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

    WHERE TABLE_NAME = @tenTable AND CONSTRAINT_TYPE = 'PRIMARY KEY'

)

)



print N'B1: Từ dotnet tạo mới 1 Class (trong model) có tên:'

print '//'

print 'Cls_'  + replace( @tenTable,'tb','')

print '//'

print ' public class  Cls_'  + replace( @tenTable,'tb','')

print '{'

print '}'

print '//'

print '//'



print N'//Các thuộc tính:'

print N'//'

print 'public int '+@tenKhoaChinh+'_Find { set; get; }'



Declare @COLUMN_Name nvarchar(50)

Declare @DATA_TYPE nvarchar(50)



Declare @ListField nvarchar(500)

set @ListField=''

Declare @ListFieldCoAcong nvarchar(500)

set @ListFieldCoAcong=''



Declare @ListColGRV nvarchar(500)

set @ListColGRV=''



DECLARE X CURSOR FOR

SELECT COLUMN_Name,DATA_TYPE

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @tenTable

OPEN X

FETCH NEXT FROM X

INTO  @COLUMN_Name,@DATA_TYPE



WHILE @@FETCH_STATUS = 0

BEGIN

    declare @kieu nvarchar(10)

    if @DATA_TYPE='nvarchar'

        set @kieu='String'

    else if @DATA_TYPE='int'

        set @kieu='int'

    else if @DATA_TYPE='date'

        set @kieu='DateTime'

    else if @DATA_TYPE='float'

        set @kieu='Double'

    else

        set @kieu='String'



    print 'public '+@kieu+' '+@COLUMN_Name+' { set; get; }'



    set @ListField=@ListField+ @COLUMN_Name +','

    set @ListFieldCoAcong=@ListFieldCoAcong +@COLUMN_Name +','

    set @ListColGRV=@ListColGRV +', this.Col_'+ @COLUMN_Name

    FETCH NEXT FROM X

    INTO @COLUMN_Name,@DATA_TYPE    

END

CLOSE X;

DEALLOCATE X;



print '//'

print 'public Cls_'+@tenClass+'()'

print '{'

print '}'

print '//'

print 'public Cls_'+@tenClass+'(int value_key)'

print '{'

print '    DataRow dr = getDataRow(id);'

print '    KhoiTaoThuocTinh(dr);'

print '}'

print '//'

print 'public Cls_'+@tenClass+'(DataRow dr)'

print '{'

print '    KhoiTaoThuocTinh(dr);'

print '}'

print '//'

print 'public void KhoiTaoThuocTinh(DataRow dr)'

print '{'

DECLARE C_KTTT CURSOR FOR

SELECT COLUMN_Name,DATA_TYPE

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @tenTable

OPEN C_KTTT

FETCH NEXT FROM C_KTTT

INTO  @COLUMN_Name,@DATA_TYPE



WHILE @@FETCH_STATUS = 0

BEGIN

    if @DATA_TYPE='int'

        print @COLUMN_Name +' = Convert.ToInt32( dr["'+@COLUMN_Name+'"]);'

    else if @DATA_TYPE='date'

         print @COLUMN_Name +' = Convert.ToDateTime( dr["'+@COLUMN_Name+'"]);'

    else if @DATA_TYPE='float'

        print @COLUMN_Name +' = Convert.ToDouble( dr["'+@COLUMN_Name+'"]);'

    else

       print @COLUMN_Name +' = dr["'+@COLUMN_Name+'"].ToString();'

    



    FETCH NEXT FROM C_KTTT

    INTO @COLUMN_Name,@DATA_TYPE

END

CLOSE C_KTTT

DEALLOCATE C_KTTT

print '}'



-- getDataTable

print 'public DataTable getDataTable()'

print '{'

print '     return'

print '         new SQLLib().getDataTable'

print '         ('

print '             "Select * from '+@tenTable+'"'

print '         );'

print ' }'



print 'public DataRow getDataRow(int value_key)'

print '{'

print ' return'

print '      new SQLLib().getDataRow'

print '      ('

print '          "Select * from '+@tenTable+' Where '+@tenKhoaChinh+'="+value_key;'

print '      );'

print '}'



-- doInsert

print 'public int doInsert()'

print '{'

print 'try{'

print N'    //Xóa dấu , dư trong biến _str'

print '    SQLLib _sql = new SQLLib();'

print '    string _str= "Insert '+@tenTable+'( '+@ListField+') "'

print '    + " Values('+@ListFieldCoAcong+')";'



DECLARE Y CURSOR FOR

SELECT COLUMN_Name,DATA_TYPE

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @tenTable

OPEN Y

FETCH NEXT FROM Y

INTO  @COLUMN_Name,@DATA_TYPE



WHILE @@FETCH_STATUS = 0

BEGIN

    print ' _str = _str.Replace("@'+@COLUMN_Name+'", "''"+'+@COLUMN_Name+'+"''");'

    FETCH NEXT FROM Y

    INTO @COLUMN_Name,@DATA_TYPE

    

END

CLOSE Y;

DEALLOCATE Y;

print ' return _sql.doSQL(_str);'

print '}'

print 'catch (Exception ex){'

print '    if (ex.Message.Contains("PRIMARY"))'

print N'    throw new Exception('+@tenKhoaChinh+' + " đã tồn tại");'

print '    else'

print '    throw ex;'

print '    }'

print '}'



print 'public int doUpdate()'

print '{'

print 'try{'

print '    SQLLib _sql = new SQLLib();'

print N'    //Xóa dấu , dư trong biến _str'

print '    string _str= "Update '+@tenTable+' "'

print '        + "Set "'



DECLARE Y CURSOR FOR

SELECT COLUMN_Name,DATA_TYPE

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @tenTable

OPEN Y

FETCH NEXT FROM Y

INTO  @COLUMN_Name,@DATA_TYPE



WHILE @@FETCH_STATUS = 0

BEGIN

    print '    + " ,'+@COLUMN_Name+' =@'+@COLUMN_Name+'"'

   -- print ' _str = _str.Replace("@'+@COLUMN_Name+'", "''"+'+@COLUMN_Name+'+"''");'

    FETCH NEXT FROM Y

    INTO @COLUMN_Name,@DATA_TYPE

    

END

CLOSE Y;

DEALLOCATE Y;

print ' + " Where '+@COLUMN_Name+'=@'+@COLUMN_Name+'_Find");'



DECLARE C_TT_SQL_up CURSOR FOR

SELECT COLUMN_Name,DATA_TYPE

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @tenTable

OPEN C_TT_SQL_up

FETCH NEXT FROM C_TT_SQL_up

INTO  @COLUMN_Name,@DATA_TYPE



WHILE @@FETCH_STATUS = 0

BEGIN

    print ' _str = _str.Replace("@'+@COLUMN_Name+'", "''"+'+@COLUMN_Name+'+"''");'

    FETCH NEXT FROM C_TT_SQL_up

    INTO @COLUMN_Name,@DATA_TYPE    

END

CLOSE C_TT_SQL_up

DEALLOCATE C_TT_SQL_up



print ' _str = _str.Replace("@'+@tenKhoaChinh+'_Find", "''"+'+@tenKhoaChinh+'Find+"''");'



print '    return _sql.doSQL(_str);'

print '}'

print 'catch (Exception ex){'

print '    if (ex.Message.Contains("PRIMARY"))'

print N'    throw new Exception('+@tenKhoaChinh+' + " đã tồn tại");'

print '    else'

print '    throw ex;'

print '    }'

print '}'

print 'public int doDelete()'

print '{'

print '      SQLLib _sql = new SQLLib();'

print '      return _sql.doSQL("Delete from '+@tenTable+' Where '+@tenKhoaChinh+'=" + '+@tenKhoaChinh+');'

print '}'

  

print 'public int countBy'+@tenKhoaChinh+'('+@tenKhoaChinh+')'

print '{'

print '    string _str = "Select Count(*) from '+@tenTable+' Where '+@tenKhoaChinh+'=@'+@tenKhoaChinh+'";'

print '    _str = _str.Replace("@'+@tenKhoaChinh+'", '+@tenKhoaChinh+'.ToString());'

print '    object gt = new SQLLib().getValue(_str);'

print '    return (int)gt;'

print '}'



print N'//'

print N'//Nếu có Gridview thì tạo nhanh'

print N'//Bước 1: vào Gridview thêm 1 cột nhanh Col1,Col2'



DECLARE grcC CURSOR FOR

SELECT COLUMN_Name,DATA_TYPE

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @tenTable

OPEN grcC

FETCH NEXT FROM grcC

INTO  @COLUMN_Name,@DATA_TYPE



WHILE @@FETCH_STATUS = 0

BEGIN 

    print ' private System.Windows.Forms.DataGridViewTextBoxColumn Col_'+@COLUMN_Name+';'

    FETCH NEXT FROM grcC

    INTO @COLUMN_Name,@DATA_TYPE    

END

CLOSE grcC;

DEALLOCATE grcC;





DECLARE grcC1 CURSOR FOR

SELECT COLUMN_Name,DATA_TYPE

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @tenTable

OPEN grcC1

FETCH NEXT FROM grcC1

INTO  @COLUMN_Name,@DATA_TYPE

print 'FORM ::'

WHILE @@FETCH_STATUS = 0

BEGIN 

    print ' this.Col_'+@COLUMN_Name+' = new System.Windows.Forms.DataGridViewTextBoxColumn();'

    FETCH NEXT FROM grcC1

    INTO @COLUMN_Name,@DATA_TYPE    

END

CLOSE grcC1;

DEALLOCATE grcC1;



print '  this.dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] {'

print @ListColGRV

print '});'





DECLARE grcC3 CURSOR FOR

SELECT COLUMN_Name,DATA_TYPE

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @tenTable

OPEN grcC3

FETCH NEXT FROM grcC3

INTO  @COLUMN_Name,@DATA_TYPE

print 'Column: ::'

WHILE @@FETCH_STATUS = 0

BEGIN 

    --print ' this.Col_'+@COLUMN_Name+' = new System.Windows.Forms.DataGridViewTextBoxColumn();'

    print '//'

    print 'this.Col_'+@COLUMN_Name+'.DataPropertyName = "'+@COLUMN_Name+'";'

    print 'this.Col_'+@COLUMN_Name+'.HeaderText = "'+@COLUMN_Name+'";'

    print 'this.Col_'+@COLUMN_Name+'.Name = "Col_'+@COLUMN_Name+'";'

    print '//'

    FETCH NEXT FROM grcC3

    INTO @COLUMN_Name,@DATA_TYPE    

END

CLOSE grcC3;

DEALLOCATE grcC3;