基于MySQL表创建C#类

Question

.Net 或 Visual Studio 中是否有内置的东西可以让我基于 MySql 表创建类。我想我正在谈论坚持。我只希望该类是表的 1 对 1 映射。有免费的东西存在吗？

Answer 1

也许你需要这样的东西：

select 'my_table' into @table; #table name
select 'my_database' into @schema; #database name
select concat('public class ',@table,'{') union
select concat('public ',tps.dest,' ',column_name,'{get;set;}') from  information_schema.columns c
join( #datatypes mapping
select 'char' as orign ,'string' as dest union all
select 'varchar' ,'string' union all
select 'longtext' ,'string' union all
select 'datetime' ,'DateTime?' union all
select 'text' ,'string' union all
select 'bit' ,'int?' union all
select 'bigint' ,'int?' union all
select 'int' ,'int?' union all
select 'double' ,'double?' union all
select 'decimal' ,'double?' union all
select 'date' ,'DateTime?' union all
select 'tinyint' ,'bool?'
) tps on c.data_type like tps.orign
where table_schema=@schema and table_name=@table union
select '}';

Answer 2

我调整了MeelStorm的sql，因为它出现了一些关于语言的错误。我还放置了其他类型的数据，并删除了类声明，因为这对我来说是不必要的。所以最终的结果是：

select concat('public ',tps.dest,' ',column_name,'{get;set;}') as code 
from  information_schema.columns c
join(
select 'char' as orign ,'string' as dest union all
select 'varchar' ,'string' union all
select 'longtext' ,'string' union all
select 'datetime' ,'DateTime' union all
select 'text' ,'string' union all
select 'bit' ,'int' union all
select 'bigint' ,'int' union all
select 'int' ,'int' union all
select 'double' ,'double' union all
select 'decimal' ,'double' union all
select 'date' ,'DateTime' union all
select 'tinyint' ,'bool'
) tps on c.data_type like tps.orign
where table_schema='your_schema' and table_name='your_table' 
order by c.ordinal_position

希望有帮助。干杯!

Answer 3

这是伟大的工作：

http://www.code4copy .com/post/generate-c-sharp-model-class-mysql-table

创建一个过程如下：

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $

CREATE DEFINER=`root`@`localhost` PROCEDURE `GenCSharpModel`(in pTableName VARCHAR(255) )
BEGIN
DECLARE vClassName varchar(255);
declare vClassCode mediumtext;
declare v_codeChunk varchar(1024);
DECLARE v_finished INTEGER DEFAULT 0;
DEClARE code_cursor CURSOR FOR
    SELECT code FROM temp1; 

DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET v_finished = 1;

set vClassCode ='';
/* Make class name*/
    SELECT (CASE WHEN col1 = col2 THEN col1 ELSE concat(col1,col2)  END) into vClassName
    FROM(
    SELECT CONCAT(UCASE(MID(ColumnName1,1,1)),LCASE(MID(ColumnName1,2))) as col1,
    CONCAT(UCASE(MID(ColumnName2,1,1)),LCASE(MID(ColumnName2,2))) as col2
    FROM
    (SELECT SUBSTRING_INDEX(pTableName, '_', -1) as ColumnName2,
        SUBSTRING_INDEX(pTableName, '_', 1) as ColumnName1) A) B;

    /*store all properties into temp table*/
    CREATE TEMPORARY TABLE IF NOT EXISTS  temp1 ENGINE=MyISAM  
    as (
    select concat( 'public ', ColumnType , ' ' , FieldName,' { get; set; }') code
    FROM(
    SELECT (CASE WHEN col1 = col2 THEN col1 ELSE concat(col1,col2)  END) AS FieldName, 
    case DATA_TYPE 
            when 'bigint' then 'long'
            when 'binary' then 'byte[]'
            when 'bit' then 'bool'
            when 'char' then 'string'
            when 'date' then 'DateTime'
            when 'datetime' then 'DateTime'
            when 'datetime2' then 'DateTime'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then 'decimal'
            when 'float' then 'float'
            when 'image' then 'byte[]'
            when 'int' then 'int'
            when 'money' then 'decimal'
            when 'nchar' then 'char'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'double'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'mediumint' then 'INT'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'DateTime'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'string'
            when 'year' THEN 'UINT'
            else 'UNKNOWN_' + DATA_TYPE
        end ColumnType
    FROM(
    select CONCAT(UCASE(MID(ColumnName1,1,1)),LCASE(MID(ColumnName1,2))) as col1,
    CONCAT(UCASE(MID(ColumnName2,1,1)),LCASE(MID(ColumnName2,2))) as col2, DATA_TYPE
    from
    (SELECT SUBSTRING_INDEX(COLUMN_NAME, '_', -1) as ColumnName2,
    SUBSTRING_INDEX(COLUMN_NAME, '_', 1) as ColumnName1,
    DATA_TYPE, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS  WHERE table_name = pTableName) A) B)C);

    set vClassCode = '';
    /* concat all properties*/
    OPEN code_cursor;

            get_code: LOOP

                FETCH code_cursor INTO v_codeChunk;

                IF v_finished = 1 THEN
                    LEAVE get_code;
                END IF;

                -- build code
                select  CONCAT(vClassCode,'\r\n', v_codeChunk) into  vClassCode ;

            END LOOP get_code;

        CLOSE code_cursor;

drop table temp1;
/*make class*/
select concat('public class ',vClassName,'\r\n{', vClassCode,'\r\n}');
END

但是需要一些手动工作。

Answer 4

您可以为此使用实体框架。它与 MySQL 连接良好。
我一直在关注本教程： http://www.devart.com/dotconnect /mysql/articles/tutorial_ef.html

Answer 5

似乎有一种方法可以让 EntityFramework 与 MySQL 一起使用

将 MySQL 与 Entity Framework 结合使用

< a href="http://weblogs.asp.net/gunnarpeipman/archive/2010/12/09/getting-mysql-work-with-entity-framework-4-0.aspx" rel="nofollow noreferrer">http://weblogs.asp.net/gunnarpeipman/archive/2010/12/09/getting-mysql-work-with-entity-framework-4-0.aspx

Answer 6

您还可以将 LINQ to SQL 与 MySQL 结合使用。但是，您应该进行一些研究以找到您必须安装的正确提供程序。

我认为这里几乎已经涵盖了：

LINQ to MySQL

Answer 7

我将 NHibernate 与 MyGeneration 结合使用

MyGeneration 是一个程序，可以读取数据库架构并根据模板生成代码（在NHibernate 的情况，实体和映射）

Answer 8

第一个示例非常好，但缺少一些类型，因此我分享添加缺少的类型（set、float 等..）

select 'table_name' INTO @table; #table name
select 'db_name' into @schema; #database name
select concat('public class ',@table,'{') union
select concat('public ',tps.dest,' ',column_name,'{get;set;}') from  information_schema.columns c
join( #datatypes mapping
select 'char' as orign ,'string' as dest union all
select 'varchar' ,'string' union all
select 'longtext' ,'string' union all
select 'datetime' ,'DateTime?' union all
select 'text' ,'string' union all
select 'bit' ,'int?' union all
select 'shorte_prodottoe_prodotto' ,'int?' union all
select 'bigint' ,'int?' union all
select 'float' ,'float' union all
select 'smallint' ,'sbyte' union all
select 'int' ,'int?' union all
select 'double' ,'double?' union all
select 'decimal' ,'double?' union all
select 'date' ,'DateTime?' union all
select 'boolean' ,'bool' union all
select 'set' ,'string' union all
select 'tinyint' ,'bool?'
) tps on c.data_type like tps.orign
where table_schema=@schema and table_name=@table union
select '}';

Answer 9

虽然它没有给出期望的结果，但已经很接近了。可以开发。

SET @tableName = "users";
SET @classPrefix = "public class ";
SET @class = @classPrefix + @tableName + " {\r" ;
SET @propList = "";
    
    
SELECT @propList := CONCAT("public ", co.DATA_TYPE, " ", co.COLUMN_NAME, " { get; set; }\r")
    FROM INFORMATION_SCHEMA.COLUMNS AS co
    INNER JOIN INFORMATION_SCHEMA.TABLES as ta
    WHERE ta.TABLE_TYPE = 'BASE TABLE' AND ta.TABLE_SCHEMA= 'tradecenter' AND co.TABLE_NAME = @tableName AND ta.TABLE_NAME = @tableName
    ORDER BY co.ORDINAL_POSITION;
    
SELECT CONCAT(@class, @propList, "}")

Answer 10

下面是一个将 mysql 表转换为带有必填字段的 C# 模型类的示例。希望对您有所帮助。

select 'mytable' INTO @table; #table name
select 'myDB' into @schema; #database name
select concat('public class ',@table,'{') union
select concat('public ',ttc.dtype,case when IS_NULLABLE = 'YES' and ttc.dtype != 'string' then '?' else '' end,' ',column_name,'{get;set;}') from  information_schema.columns c
join( #datatypes mapping
select 'char' as orign ,'string' as dtype union all
select 'varchar' ,'string' union all
select 'longtext' ,'string' union all
select 'text' ,'string' union all
select 'set' ,'string' union all
select 'bit' ,'int' union all
select 'shorte_prodottoe_prodotto' ,'int' union all
select 'int' ,'int' union all
select 'smallint' ,'sbyte' union all
select 'bigint' ,'long' union all
select 'float' ,'double' union all
select 'double' ,'double' union all
select 'decimal' ,'double' union all
select 'date' ,'DateTime' union all
select 'datetime' ,'DateTime' union all
select 'boolean' ,'bool' union all
select 'tinyint' ,'bool'
) ttc on c.data_type like ttc.orign
where table_schema=@schema and table_name=@table union
select '}';

Answer 11

NHibernate 可以连接到 MySQL 并且是免费的：

https://nhibernate.info/

Answer 12

Subsonic（开源）可与 MySQL (5.0+) 配合使用，并特别支持 InnoDB -

http://subsonicproject.com/