How to Implement database entity generation tools

Document directory

•  

1 principle:

Use SqlDataReader. GetSchemaTable to obtain the column metadata and generate the required entity based on the column metadata. This program mainly uses the following three fields. For other fields of SchemaTable, see MSDN.

Name	Description
ColumnName	Column name; it may not be unique. If the name cannot be determined, null is returned. This name always reflects the recent renaming of columns in the current view or command text.
DataType	The. NET Framework type mapped to the column.
AllowDBNull	If the user can set this column to a null value, or if the provider cannot determine whether the user can set this column to a null value, set this value. Otherwise, this value is not set. A column may contain null values even if it cannot be set to null.

2. Implement (1) Table Structure

(2) generate a table script

USE [BPMDB]
GO
/***** Object: Table [dbo]. [PW_User] script Date: 13:13:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create table [dbo]. [PW_User] (
[ID] [uniqueidentifier] rowguidcol not null constraint [DF_PW_User_ID]
DEFAULT (newid ()),
[UserNo] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS not null,
[Password] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS not null,
[LastName] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS not null,
[FirstName] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL,
[Alias] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL,
[Sex] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL,
[BirthDate] [datetime] NULL,
[Phone] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL,
[Email] [nvarchar] (max) COLLATE Chinese_PRC_CI_AS NULL,
[ShowRule] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL,
[IsFreeze] [int] NULL,
CONSTRAINT [PK_PW_User_Id] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

(3) Code

/*************************************** ****************************
** Copyright (C) hbb0b0
** All rights reserved.
**
* Author: HBB0b0 (hbb0b0@163.com)
* Create Date: 13:00:59
* Description: A simple database entity generation tool.
**
** Date Author Description
**************************************** ****************************/
Using System;
Using System. Collections. Generic;
Using System. Linq;
Using System. Text;
Using System. Data;
Using System. Data. SQL;
Using System. Data. SqlClient;
Using System. Diagnostics;
Using System. Configuration;
Namespace HBB0b0
{
Class Program
{
Static void Main (string [] args)
{
Using (SqlConnection myConnection = new
SqlConnection (ConfigurationManager. ConnectionStrings ["sqlserver"]. ConnectionString ))
{
MyConnection. Close ();
MyConnection. Open ();
DataTable dt = null;
String tableName = "PW_User ";
Dt = GetTableSchema (myConnection, tableName );
WriteClassEntity (dt, tableName );
}
Console. Read ();
}
/// <Summary>
/// Obtain the schema of the specified table
/// </Summary>
/// <Param name = "connection"> </param>
/// <Param name = "tableName"> </param>
/// <Returns> </returns>
Private static DataTable GetTableSchema (SqlConnection connection, string
TableName)
{
Using (SqlCommand cmd = connection. CreateCommand ())
{
Cmd. CommandType = CommandType. Text;
Cmd. CommandText = string. Format ("select top (1) * from {0} with (nolock )",
TableName );
DataTable dt = null;
// Using (SqlDataReader read = cmd. ExecuteReader ())
{
SqlDataReader read = cmd. ExecuteReader ();
Dt = read. GetSchemaTable ();
}
Return dt;
}
}
/// <Summary>
/// Generate the object corresponding to the table
/// </Summary>
/// <Param name = "dtSchema"> </param>
/// <Param name = "tableName"> </param>
Private static void WriteClassEntity (System. Data. DataTable dtSchema, string
TableName)
{
StringBuilder sb = new StringBuilder ();
Sb. Append ("[Serializable]");
Sb. AppendLine ();
Sb. AppendFormat ("public class {0} Entity", tableName );
Sb. AppendLine ();
Sb. AppendLine ("{");
Foreach (System. Data. DataRow row in dtSchema. Rows)
{
// Column name
DataColumn nameCol =
DtSchema. Columns [dtSchema. Columns. IndexOf ("ColumnName")];
// Type
DataColumn typeCol =
DtSchema. Columns [dtSchema. Columns. IndexOf ("DataType")];
// Whether it is null
DataColumn allowDBNullCol =
DtSchema. Columns [dtSchema. Columns. IndexOf ("AllowDBNull")];
String symbolCanNull = "";
If (Boolean. Parse (row [allowDBNullCol]. ToString ())&&
Type. GetType (row [typeCol]. ToString (). IsValueType)
{
SymbolCanNull = "? ";
}
String info = string. Format ("public {0} {1} {2} {3} get; set; {4 }",
Row [typeCol]. ToString (),
SymbolCanNull,
Row [nameCol]. ToString (),
"{","}");
Sb. AppendLine (info );
}
Sb. AppendLine ("}");
// Trace. Listeners. Remove ("myListener ");
Console. WriteLine (sb. ToString ());
// Trace. Write (sb. ToString ());
// Trace. Flush ();
}
}
}

(4) running result