Machine coding for WinForm

DECLARE @objname varchar = ' Autoprojectmodel '
DECLARE @modelns varchar = ' ACL. Models '
DECLARE @dpclsname varchar = ' ACL. Updextdataprovider '--model data provides class name
DECLARE @datamodel varchar = ' ext_ ' [email protected]
DECLARE @clsname varchar (=replace) (@datamodel, ' ext_ ', ') + ' Model '
DECLARE @modelname varchar (90) = ' Automation Project module '

--type 0: Comment, 1: Access method, 2:model,3:dataprovider,4:viewmodel,5:codebehind,6:listviewui,7:datagridui,8 read stored procedure, 9 write stored procedure
CREATE TABLE # (MYFD varchar (max))
declare @readprocname varchar = ' ', @writeprocname varchar = '
SET @ Readprocname= ' Wp_getpager_ ' [email protected]+ ' _ds '
Set @writeprocname = ' Wp_ ' [email protected]+ ' _ Change '
INSERT INTO # (MYFD)
Select '//****************************************datagrid column bindings ****************** '
INSERT INTO # (MYFD)
Select ' dgmodlist.columns["cl ' + name+ '"]. DataPropertyName = "' +name+ '";
from syscolumns a,information_schema.columns b
where A.name=b.column_name
and [email protected] and object_name (ID) [email protected]

INSERT INTO # (MYFD)
Select '//****************************************form codebehind new ***************** '
Declare @fbstr varchar (max) = '
Set @fbstr +=char (Ten) + ' public projectreg (int ID) {'
+char (+) + ' InitializeComponent (); '
+char + ' _id = ID; '
+char (+) + ' if (_id > 0) '
+char (Ten) + ' Initview (); '
+char + '} '
+char (Ten) + ' list< ' [email protected]+ '. [email protected]+ ' > _models; '
+char + ' private void Initview () {'
+char (Ten) + ' _models = ' [email protected]+ '. ') [email protected]+ '. Popobjectlist (_id, "", ""); '
+char + ' if (_models.count > 0) {'
+char (Ten) [email protected]+ ' model = _models[0]; '
Select @fbstr + = char (Ten) + ' _ ' +name+ ' =model. ' +name+ '; '
from syscolumns a where object_name (ID) [email protected]
Select @fbstr +=char (Ten) + '}} '

Select @fbstr +=char + ' string _ ' +a.name+ ' {get {return txt ' +name+ '. Text.trim (); }set {txt ' +name+ '. Text=value; }} '
from syscolumns a where object_name (ID) [email protected]
Set @fbstr +=char (+ ' private void btnsave_ Click (object sender, EventArgs e) {'
Set @fbstr +=char (Ten) [Email protected]ame + ' model =new ' [email  Protected]+ ' (); '
Select @fbstr +=char + ' model. ' +name+ ' =_ ' +name+ '; '
from syscolumns a where object_name (ID) [email protected]
Set @fbstr +=char + ' handlemsg msg= ' [email  protected]+ '. ' [email protected]+ ' Change (model); ' The
Set @fbstr +=char (+) + ' MessageBox.Show (msg. MSG); '
Set @fbstr +=char (Ten) + '} '

INSERT INTO # (MYFD) VALUES (@fbstr)
INSERT INTO # (MYFD)
Select '//**************************************** ' [ email protected]+ ' ***************************************** '
declare @modelstr varchar (max)

Select @modelstr = ' using System; '
+char + ' using System.Collections.Generic; '
+char (+) + ' using System.Data; '
+char + ' using System.Runtime.Serialization; '
+char + ' using System.Text; '

Select @modelstr +=char (+) + ' namespace ' [Email protected]+char (+) + ' {' +char (+) + ' public class ' [email  protected]+ "+char (+) + ' {'

Select @modelstr +=char (+) + ' public string Action{get;set;} '

Select @modelstr +=char + ' public ' +
case where b.data_type= ' datetime ' Then ' string '
When b.data_type= ' nvarchar ' then ' string '
When b.data_type= ' varchar ' then ' string '
When b.data_type= ' uniqueidentifier ' then ' string '
When b.data_type= "money" then ' double '
When the b.data_type= ' bit ' then ' int '
When b.data_type= ' smallint ' then ' int '
When b.data_type= ' int ' OR b.data_type= ' bigint ' then ' int '
When b.data_type= ' decimal ' then ' double '
else B.data_type end+ ' +
Name+ ' {get;set;} '
+ ' from syscolumns a,information_schema.columns b
where A.name=b.column_name
and [email protected] and object_name (ID) [email protected]

Select @modelstr +=char + ' public ' [email protected]+ ' () {} '

Select @modelstr +=char + ' public ' [email protected]+ ' (DataRow dr) {'

Select @modelstr +=char (10) +
Case is b.data_type= ' datetime ' OR b.data_type= ' date ' Then ' if (! Convert.isdbnull (dr["' +name+ '"])) '
+ ' this. ' +name+ ' = Convert.todatetime (dr["' +name+ '"]). ToString ("Yyyy-mm-dd hh:mm:ss"); '
+ ' else this. ' +name + ' = ""; '
When b.data_type= ' nvarchar ' or b.data_type= ' varchar ' or b.data_type= ' uniqueidentifier ' then ' this. ' +name+ ' =dr["' +name+ '"]. ToString (); '
When b.data_type= "Money" or b.data_type= ' decimal ' Then
' If (Convert.isdbnull (dr["' +name+ '"])) ' +char (10) +
' This. ' +name+ ' = 0; ' +char (10) +
' Else ' +
' This. ' +name+ ' =convert.todouble (dr["' +name+ '"]); '
When b.data_type= ' bit ' or b.data_type= ' smallint ' or b.data_type= ' int ' then
' If (Convert.isdbnull (dr["' +name+ '"])) ' +char (10) +
' This. ' +name+ ' = 0; ' +char (10) +
' Else ' +
' This. ' +name+ ' =convert.toint32 (dr["' +name+ '"]); '
Else ' End
From syscolumns A,information_schema.columns b
where A.name=b.column_name
and [email protected] and object_name (ID) [email protected]

Select @modelstr +=char (+) + ' this. action= "edit"; '
Select @modelstr +=char (10) + '} '
+char + ' public static handlemsg ' [e-mail protected]+ ' Change (' [email protected]+ ' model ') {'
+char (Ten) + ' dictionary<string, object> dir = new dictionary<string, object> (); '
Select @modelstr +=char (+) + ' dir. ADD ("@ ' +name + '", model. ') +name+ '); '
From Syscolumns a where object_name (ID) [email protected]

Select @modelstr +=char + ' DataTable dt = ' [email protected]+ '. ' [Email protected]+ ' Change (dir); '
+char (Ten) + ' handlemsg msg = new handlemsg (dt); '
+char (Ten) + ' return msg; '
+char (10) + '} '

Select @modelstr +=char + ' public static list< ' [email protected]+ ' > popobjectlist (String parm, string userno) '
+char (10) + ' {'
+char (Ten) + ' dictionary<string, object> dir = new dictionary<string, object> (); '
+char (+) + ' dir. ADD ("@parm", parm); '
+char (+) + ' dir. ADD ("@userno", userno); '
+char (+) + ' DataTable dt = ' [email protected]+ '. Get ' [Email protected]+ ' List (dir); ' +char (10) +
' list< ' [email protected]+ ' > List = new list< ' [email protected]+ ' > (); ' +char (10) +
' foreach (DataRow dr in Dt. Rows) ' +char (10) +
' {' +char (10) +
' List. ADD (new ' [Email protected]+ ' (DR)); ' +char (10) +
'} ' +char (10) +
' Return list; ' +char (10) +
‘ }}}‘
Insert INTO # (MYFD)
Select @modelstr
Print @modelstr

Insert INTO # (MYFD)
Select '//****************************************data provider***************************************** '
DECLARE @dpstr varchar (max) = "

Select @dpstr +=char + ' public static DataTable Get ' [email protected]+ ' List (dictionary<string, object> parms) { ‘
+char (+) + ' DataTable dt = _dataprovider.execstoreprocedureforgettingtable (parms, "' [email protected]+ '"); '
+char (+) + ' return dt; '
+char (+) + '} ' +char (10)

Select @dpstr +=char + ' public static DataTable ' [e-mail protected]+ ' Change (dictionary<string, object> parms) {'
Select @dpstr +=char + ' DataTable dt = _dataprovider.execstoreprocedureforgettingtable (parms, "' [Email protected]+ ' ");‘
Select @dpstr +=char + ' return dt;} '
INSERT into # (MYFD) VALUES (@dpstr)

--********************************** output WPF Data ui**************************************

SELECT--cast (case when ISNULL (G.[value], ") =" then A.name ELSE G.[value] END as VARCHAR) +char (10)
' <gridviewcolumn header= ' + CAST (case is ISNULL (G.[value], ') = ' then A.name ELSE G.[value] END as VARCHAR)
+ ' "width=" celltemplateselector= "{x:null}" > ' +char (10)
+ ' <GridViewColumn.CellTemplate> ' +char (10)
+ ' <DataTemplate> ' +char (10)
+ ' <dockpanel lastchildfill= "True" > ' +char (10)
+ ' <rectangle dockpanel.dock= "right" fill= "#BABABE" width= "1" ></Rectangle> ' +char (10)
+ ' <textblock dockpanel.dock= "left" text= "{Binding ID}" width= "" horizontalalignment= "left" ></textblock > ' +char (10)
+ ' </DockPanel> ' +char (10)
+ ' </DataTemplate> ' +char (10)
+ ' </GridViewColumn.CellTemplate> ' +char (10)
+ ' </GridViewColumn> ' +char (10)
----(case if A.colorder=1 then d.name Else ' end) Table name,

From syscolumns a LEFT join systypes b
On A.xtype=b.xusertype
INNER JOIN sysobjects D
On a.id=d.id and d.name<> ' dtproperties '
Left Join Sys.extended_properties G
On a.id=g.major_id and a.colid = g.minor_id
WHERE D.[name] [email protected]--the name of the table you want to view, comment out, view field information for all tables in the current database
ORDER BY A.id,a.colorder

Insert INTO # (MYFD) VALUES ('
--*********************************************** read data stored procedure ************************************************ ')
--*********************************************** read data stored procedure **************************************************
DECLARE @fields varchar (max) = ", @sqlstr varchar (max) =", @orderfields varchar (90) = "
Select @fields =stuff ((select ', ' +name from syscolumns
where object_name (id) = ' [email protected]+ ' ORDER by Colorder for XML Path (')], 1, 1, ')
Select top 1 @orderfields =name from syscolumns where object_name (id) = "[Email protected]+ ' ORDER by Colorder
Set @sqlstr + = '--' +convert (varchar), GETDATE (), 120) + '
+char (10) + '--read ' [email protected]+ '
+char (+) + '--' [email protected]+ ' @parm = ' '
+char (+) + ' Create PROC ' [email protected]+ '
+char (Ten) + ' @id int=-1, '
+char (+) + ' @parm nvarchar (90) = ' ', '
+char (+) + ' @userno VARCHAR (20) = ' ', '
+char (Ten) + ' @isvalid INT =-1, '
+char (+) + ' @sortfields nvarchar (100) = ' ', '
+char (Ten) + ' @pageindex int=1, '
+char (Ten) + ' @pagesize int=500 '
+char (Ten) + ' as '
+char (+) + ' begin '
+char (10) + '--****************************************** data preparation ************************************** '


+char (10) + '--******************************************* variable definition *************************************** '

+char (10) + '--Build execution script '
+char (+) + ' declare @sql nvarchar (1800) = ' ', '
+char (10) + '--storage object '
+char (+) + ' @tablename NVARCHAR (+) = ' [email protected]+ ', '
+char (10) + '--return field '
+char (+) + ' @returnfields nvarchar (1000) = ' ', '
+char (Ten) + '--where condition '
+char (+) + ' @where nvarchar (+) = ' where 1=1 ', '
+char (10) + '--Last query count '
+char (Ten) + ' @lastcount int =-1 '

+char (10) + '-Calculates the total number of previously queried data '
+char (Ten) + ' Set @lastcount = (@pageindex-1) * @pagesize '
+char (Ten) + ' if @lastcount <0 set @lastcount = 0 '
+char (10) + '--Judgment sort field '
+char (+) + ' if @sortfields = ' '
+char (+) + ' Set @sortfields = ' [email protected]+ ']
+char (10) + '--******************************************* return field setting ***************************************** '
+char (+) + '--select stuff (select ', ' +name from syscolumns '
+char (+) + '--where object_name (id) = ' ext_autorunregister ' ORDER by Colorder for XML Path (') '), 1, 1, ') '
+char (+) + ' Set @returnfields = ' [email protected]+ ']
+char (10) + '--******************************************* constructed by condition where*************************************** '
+char (Ten) + ' if @id <>-1 '
+char + ' Set @where + = ' and id = ' +cast (@id as VARCHAR) '
+char (+) + ' if @userno <> ' '
+char + ' Set @where + = ' and userno like ' '% ' [email protected]+ '% ' '
+char (10) + '--******************************************* constructed by condition sql*************************************** '
+char (Ten) + ' Set @sql = ' '

+char (Ten) + ' Declare @lastmaxid int=0, @total int '
+char (10) + '--cache temporary table and create retrieve rowID '
+char (+) + ' If object_id (' "' tempdb. #t ') is not NULL '
+char (+) + ' drop table #t '

+char (+) + ' select Row_number () over (order by ' [email protected]+ ') rowid, * to #t from ' [email protected] '

+char (+) + ' Set @sql + = @where '

+char (10) + '--get the maximum ID of the data that was queried in the order of execution
+char + ' Set @sql + = ' Select Top ' +cast (@lastcount as nvarchar) + ' @lastmaxid =max (rowid) from #t '
+char (+) + ' Set @sql + = ' GROUP by rowID '

+char (10) + '--get the total number of eligible data
+char (+) + ' Set @sql + = ' Select @total =count (*) from #t "

+char (10) + '--********************************************** constructs sql*************************************** to perform the returned results **‘
+char (+) + ' Set @sql + = ' ' SELECT '
+char (Ten) + ' if @pagesize >0 '
+char + ' Set @sql + = ' top ' +cast (@pagesize as nvarchar) '
+char (+) + ' Set @sql + = ' rowid '

+char (+) + ' if @returnfields <> ' '
+char (+) + ' begin '
+char + ' Set @sql + = ', ' [email protected] '
+char (Ten) + ' end '
+char + ' Set @sql + = ' from #t where rowid> @lastmaxid order by rowID '

+char + ' Set @sql + = ' Select @total Total, ' +cast (@pagesize as nvarchar) + ' pagesize, ' +cast (@pageindex as nvarchar) + "' pageindex '

+char + ' Set @sql + = ' If object_id ("' tempdb '). #t ') is not NULL '
+char (+) + ' drop table #t '
+char + ' Set @sql + = ' If object_id ("' tempdb '). #x ') is not NULL '
+char (+) + ' drop table #x '

+char (+) + ' EXEC (@sql) '

+char (Ten) + ' END '

Insert INTO # (MYFD)
Select @sqlstr

Insert INTO # (MYFD) VALUES ('
--*********************************************** write data stored procedure ************************************************ ')
--*********************************************** writing to the data stored procedure **************************************************

Declare
@yesUpdateCondition VARCHAR = ' @id >0 '--this condition is required when updating a table
, @tbcols VARCHAR (2000) = "
, @tbcolsp VARCHAR (2000) = "
, @tbcolspv VARCHAR (2000) = "
, @space VARCHAR (10) = "

Select @tbcols = Stuff ((select ', ' +name from syscolumns
where object_name (ID) [email protected] ORDER by Colorder FOR XML Path (")), 1, 1, ')

Select @tbcolsp = Stuff ((select ', @ ' +name from syscolumns
where object_name (ID) [email protected] ORDER by Colorder FOR XML Path (")), 1, 1, ')

Select @tbcolspv = Stuff ((select ', ' +name+ ' [email protected] ' +name from syscolumns
where object_name (ID) [ Email protected] ORDER by Colorder FOR XML Path (")), ("), ")

CREATE TABLE #w (ID INT IDENTITY (), MYFD varchar (500))
Insert into #w (MYFD)
SELECT ' Create proc ' [email protected]
Insert into #w (MYFD)
SELECT ' @ ' + a.name+ ' +
case where b.data_type= ' datetime ' OR b.data_type= ' date ' then ' datetime= ' 1900-01-01 '
When b.data_type= ' nvarchar ' OR b.data_type= ' varchar ' then ' nvarchar (' +cast (a.length as varchar) + ') = ""

When b.data_type= ' uniqueidentifier ' then ' varchar (' +cast (a.length as varchar) + ') = ""
When b.data_type= "money" Then ' money=0 '
When b.data_type= ' bit ' then ' smallint=0 '
When b.data_type= ' smallint ' then ' smallint=0 '
When B.data_type= ' int. ' Then ' int=0 '
When b.data_type= ' decimal ' Then ' decimal (18,4) =0 '
else B.data_type end+ ', '
From syscolumns A,information_schema.columns b
where A.name=b.column_name
and [email protected] and object_name (ID) [email protected]

UPDATE #w SET myfd=replace (MYFD, ', ', ') WHERE [email protected] @ROWCOUNT +1
Insert into #w (MYFD)
Select ' As '
Insert into #w (MYFD)
Select ' SET xact_abort on '
Insert into #w (MYFD)
Select ' Begin Tran '
Insert into #w (MYFD)
Select ' Begin Try '
Insert into #w (MYFD)
Select @space + ' + ' declare @count int, @msg varchar (200) = ""
INSERT into #w (MYFD)
SELECT @space + ' if (' [email protected]+ ') '
INSERT into #w (MYFD)
SELECT @space + ' begin '
INSERT into #w (MYFD)
SELECT @[email protected]+ ' update ' [email protected]+ ' Set ' [email protected]
INSERT into #w (MYFD)
SELECT @[email protected]+ ' where [email protected] '
INSERT into #w (MYFD)
Select @[email protected]+ ' select @[email protected] @rowcount '
INSERT into #w (MYFD)
SELECT @space + ' End '
INSERT into #w (MYFD)
SELECT @space + ' Else '
INSERT into #w (MYFD)
SELECT @space + ' begin '
INSERT into #w (MYFD)
SELECT @[email protected]+ ' insert INTO ' [email protected]+ ' (' [email protected]+ ') '
INSERT into #w (MYFD)
Select @[email protected]+ ' SELECT ' [email protected]
INSERT into #w (MYFD)
Select @[email protected]+ ' select @[email protected] @rowcount '
INSERT into #w (MYFD)
SELECT @space + ' End '
INSERT into #w (MYFD)
Select @space + ' select @count tcount,case when @count >0 then ' execute successful ' else ' execution failed ' end Msg '
INSERT into #w (MYFD)
SELECT @space + ' Commit tran '
INSERT into #w (MYFD)
SELECT ' End Try '
INSERT into #w (MYFD)
SELECT ' Begin Catch '
INSERT into #w (MYFD)
Select @space + ' select @msg =error_message () '
INSERT into #w (MYFD)
Select @space + ' Select 0 tcount, @msg msg '
INSERT into #w (MYFD)
SELECT @space + ' rollback tran '
INSERT into #w (MYFD)
SELECT ' End Catch '

SELECT * FROM #
SELECT * FROM #w
drop table #w
DROP TABLE #

Machine coding for WinForm