I wrote an article before, lazy gadget: Automatic Generation of Model,Insert,Select,Delete and Method of Exporting Excel But some people find this method troublesome. In fact, I do feel a bit troublesome, the trouble is the string stitching.
At this time, I think of T4 template. Students who have used EF's Database First to automatically generate entities understand that dbfirst has T4 template. Before we studied spring.net framework, we also used T4 template to automatically create warehouses by mapping numbers to entity classes. T4 templates actually have many application scenarios.
T4 templates are really convenient, but the principles used in them are similar to those I used to make winform gadgets. Entities are generated from mappings of data fields and types. The other is the basic knowledge of ado.net.
But in this way you have to understand the basic grammar of T4 templates. Each has its advantages and disadvantages, but in fact grammar is not very difficult.
First, I will use T4 template to create the Model in the simplest way.
<#@ template language="C#" debug="True" hostspecific="True" #> <#@ output extension=".cs" #> <#@ assembly name="System.Data" #> <#@ assembly name="System.xml" #> <#@ import namespace="System.Collections.Generic" #> <#@ import namespace="System.Data.SqlClient" #> <#@ import namespace="System.Data" #> using System.Collections.Generic; using System.Linq; using System.Text; using System.Text.RegularExpressions; using System.Windows.Forms; using System.Data; using CMS.Utilities; using System.Data.OleDb; using System.Configuration; using System.Data.SqlClient; using System; namespace MyProject.Entities { <# string connectionString = "server=192.168.2.230;database=databasename;uid=uid;pwd=123456"; string selectQuery = "select * from Team_"; string tableName="Team_"; SqlConnection conn = new SqlConnection(connectionString); conn.Open(); System.Data.DataTable schema = null; SqlCommand selectcommand = new SqlCommand(selectQuery, conn); SqlDataAdapter sda = new SqlDataAdapter(selectcommand); System.Data.DataSet dss = new System.Data.DataSet(); sda.Fill(dss); schema=dss.Tables[0]; System.Data.DataTable dt = dss.Tables[0]; System.Data.DataRow dr = dss.Tables[0].Rows[0]; SqlCommand command = new SqlCommand(selectQuery,conn); SqlDataAdapter ad = new SqlDataAdapter(command); #> public class <#= tableName#>Biz { public class <#= tableName#>Model { <# foreach (DataColumn dc in dss.Tables[0].Columns) { #> private <#= dc.DataType.Name #> _<#= dc.ColumnName.Replace(dc.ColumnName[0].ToString(), dc.ColumnName[0].ToString().ToLower()) #>; public <#= dc.DataType.Name #> <#= dc.ColumnName #> { get { return _<#= dc.ColumnName.Replace(dc.ColumnName[0].ToString(), dc.ColumnName[0].ToString().ToLower()) #>; } set { _<#= dc.ColumnName.Replace(dc.ColumnName[0].ToString(), dc.ColumnName[0].ToString().ToLower()) #> = value; } } <# } #> } } }
This is a simple way. < #@ import namespace = "System. Data" #> This code refers to the namespace and is used in your background code, which includes the following ado.net Namespaces that need to be referenced are basically written here. Using < # dosomething # > is the code you don't need to show. The other code is the code you use when you use T4 template to generate. Is it simple? The above code need not be explained in detail, use ado.net Link to the database to get table fields and types. Ctrl+S automatically generates entity classes.
Model Generated from Templates
using System.Collections.Generic; using System.Linq; using System.Text; using System.Text.RegularExpressions; using System.Windows.Forms; using System.Data; using CMS.Utilities; using System.Data.OleDb; using System.Configuration; using System.Data.SqlClient; using System; namespace MyProject.Entities { public class Team_Biz { public class Team_Model { private String _team_code; public String Team_code { get { return _team_code; } set { _team_code = value; } } private String _team_name; public String Team_name { get { return _team_name; } set { _team_name = value; } } private String _team_status; public String Team_status { get { return _team_status; } set { _team_status = value; } } private String _team_user; public String Team_user { get { return _team_user; } set { _team_user = value; } } private DateTime _team_date; public DateTime Team_date { get { return _team_date; } set { _team_date = value; } } } } }
The above is a Model generation for a single table, and the following code is all table generation entities in the database. Great similarities, little differences, pay attention to details.
3. All tables and entities in the database
<#@ template language="C#" debug="True" hostspecific="True" #> <#@ assembly name="System.Data" #> <#@ assembly name="System.xml" #> <#@ import namespace="System.Collections.Generic" #> <#@ import namespace="System.Data.SqlClient" #> <#@ import namespace="System.Data" #> <#@ output extension=".cs" #> using System; namespace Test.T4 { <# string connectionString="data source=(local);initial catalog=musicstore;user id=sa;password=123456;"; SqlConnection conn = new SqlConnection(connectionString); conn.Open(); DataTable schema = conn.GetSchema("TABLES"); string strSql = "select * from @tableName"; SqlCommand command = new SqlCommand(strSql,conn); SqlDataAdapter ad = new SqlDataAdapter(command); DataSet ds = new DataSet(); foreach(DataRow row in schema.Rows) { #> public class <#= row["TABLE_NAME"].ToString().Trim() #> { <# ds.Tables.Clear(); command.CommandText = strSql.Replace("@tableName",row["TABLE_NAME"].ToString()); ad.FillSchema(ds, SchemaType.Mapped, row["TABLE_NAME"].ToString()); foreach (DataColumn dc in ds.Tables[0].Columns) { #> public <#= dc.DataType.Name #> <#= dc.ColumnName #> { get; set; } <# } #> } <# } #> <# conn.Close(); #> }
DataTable schema = conn.GetSchema("TABLES"); is to get all table names in the database, then traverse all table names, traverse table fields and types, and generate entities based on these tables.
Another method is to use data stored procedures to automatically generate entities. This approach requires basic knowledge of SQL.
4. Stored Procedure Generating Entities
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; GO CREATE PROC [dbo].[p_db_wsp] @dbname VARCHAR(50) , --Database name @path VARCHAR(100) , --The directory name of the entity class, such as D:/My/Models @namespace VARCHAR(50) --Entity class namespace,The default value is Models AS --Judging whether a database exists IF ( DB_ID(@dbname) IS NOT NULL ) BEGIN IF ( ISNULL(@namespace, '') = '' ) SET @namespace = 'Models'; -- Allow configuration of advanced options EXEC sp_configure 'show advanced options', 1; -- Reconfiguration RECONFIGURE; -- Enable Ole Automation Procedures EXEC sp_configure 'Ole Automation Procedures', 1; -- Enable xp_cmdshell,You can write files to disk EXEC sp_configure 'xp_cmdshell', 1; -- Reconfiguration RECONFIGURE; DECLARE @dbsql VARCHAR(1000) , @tablename VARCHAR(100); SET @dbsql = 'declare wsp cursor for select name from ' + @dbname + '..sysobjects where xtype=''u'' and name <>''sysdiagrams'''; EXEC(@dbsql); OPEN wsp; FETCH wsp INTO @tablename;--Use cursor loops to traverse each table in the database WHILE ( @@fetch_status = 0 ) BEGIN --Combining fields and attributes in entity classes based on fields in tables DECLARE @nsql NVARCHAR(4000) , @sql VARCHAR(8000); SET @nsql = 'select @s=isnull(@s+char(9)+''private '',''using System;' + CHAR(13) + 'using System.Collections.Generic;' + CHAR(13) + 'using System.Text;' + CHAR(13) + 'namespace ' + @namespace + CHAR(13) + '{' + CHAR(13) + CHAR(9) + 'public class ' + @tablename + CHAR(13) + '{''+char(13)+char(9)+''private '')+ case when a.name in(''image'',''uniqueidentifier'',''ntext'',''varchar'',''ntext'',''nchar'',''nvarchar'',''text'',''char'') then ''string'' when a.name in(''tinyint'',''smallint'',''int'',''bigint'') then ''int'' when a.name in(''datetime'',''smalldatetime'') then ''DateTime'' when a.name in(''float'',''decimal'',''numeric'',''money'',''real'',''smallmoney'') then ''decimal'' when a.name =''bit'' then ''bool'' else a.name end+'' ''+lower(''_''+b.name)+'';''+char(13)+char(9)+''public ''+ case when a.name in(''image'',''uniqueidentifier'',''ntext'',''varchar'',''ntext'',''nchar'',''nvarchar'',''text'',''char'') then ''string'' when a.name in(''tinyint'',''smallint'',''int'') then ''int'' when a.name=''bigint'' then ''long'' when a.name in(''datetime'',''smalldatetime'') then ''DateTime'' when a.name in(''float'',''decimal'',''numeric'',''money'',''real'',''smallmoney'') then ''decimal'' when a.name =''bit'' then ''bool'' else a.name end +'' ''+b.name+char(13)+char(9)+''{''+char(13)+char(9)+char(9)+''get{return ''+lower(''_''+b.name)+'';}''+ char(13)+char(9)+char(9)+''set{''+lower(''_''+b.name)+''=value;}''+char(13)+char(9)+''}''+char(13) from ' + @dbname + '..syscolumns b, (select distinct name,xtype from ' + @dbname + '..systypes where status=0) a where a.xtype=b.xtype and b.id=object_id(''' + @dbname + '..' + @tablename + ''')'; EXEC sp_executesql @nsql, N'@s varchar(8000) output', @sql OUTPUT; SET @sql = @sql + CHAR(9) + '}' + CHAR(13) + '}'; --print @sql DECLARE @err INT , @fso INT , @fleExists BIT , @file VARCHAR(100); SET @file = @path + '/' + @tablename + '.cs'; EXEC @err= sp_OACreate 'Scripting.FileSystemObject', @fso OUTPUT; EXEC @err= sp_OAMethod @fso, 'FileExists', @fleExists OUTPUT, @file; EXEC @err = sp_OADestroy @fso; IF @fleExists != 0 EXEC('exec xp_cmdshell ''del '+@file+''''); --Existence deletes EXEC('exec xp_cmdshell ''echo '+@sql+' > '+@file+''''); --Write text into a file SET @sql = NULL; FETCH wsp INTO @tablename; END; CLOSE wsp; DEALLOCATE wsp; PRINT 'Generate success!'; END; ELSE PRINT 'The database does not exist!';
Call stored procedures: EXEC [dbo].[p_db_wsp]'Database name','save path: D: work new folder','generate entity class name';
In addition: github address: https://github.com/Jimmey-Jiang/JWorkHelper