基于MySQL表创建C#类

Question

在.Net或Visual Studio中，有没有任何内置的功能可以让我基于一个MySQL表创建类呢？我想我是在谈论持久性。我只是希望这个类与表是一对一的映射关系。是否有免费的解决方案存在呢？

- user489041

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 '}';

- MeelStorm

---

不错；在紧急情况下需要快速完成这件事。做了一些小改动，创建了一个要点。https://gist.github.com/pdwetz/5368441 - pdwetz

绝对精妙的轻量级解决方案。一次就奏效了。 - Stephanie

使用 select concat('public ',tps.dest, IF(tps.dest = 'string', '', IF(is_nullable = 'NO', '', '?')) ... ，并从映射中删除 ?以正确映射可为NULL的类型。 - ijavid

如果您的数据库具有tinytext数据类型，您可以添加select 'tinytext' ,'string' union all。 - Talon

使用 if(c.IS_NULLABLE = 'NO', REPLACE(tps.dest, '?', '') 替代 tps.dest。 - NEER

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

希望这有所帮助。干杯！

- Arthur Accioly

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

然而，需要一些手动操作。

- Anupam Singh

Answer 4

你可以使用Entity Framework来完成这个操作。它能和MySQL很好地连接。 我一直在关注这个教程：http://www.devart.com/dotconnect/mysql/articles/tutorial_ef.html

- WimB

Answer 5

看起来有一种方法可以让EntityFramework与MySQL配合使用

使用MySQL与Entity Framework

http://weblogs.asp.net/gunnarpeipman/archive/2010/12/09/getting-mysql-work-with-entity-framework-4-0.aspx

- MedicineMan

Answer 6

虽然它没有达到预期的结果，但已经接近了。可以进一步开发。

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, "}")

- Ahmet Gençözlü

---

您的回答可以通过添加更多支持性信息来改进。请[编辑]以添加进一步的详细信息，比如引用或文档，以便其他人可以确认您的回答是正确的。您可以在帮助中心中找到有关如何编写良好回答的更多信息。 - Community

Answer 7

这是一个将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 '}';

- ARMilon

Answer 8

您也可以在 MySQL 中使用 LINQ to SQL。但是，您需要进行一些研究，以找到必须安装的正确提供程序。

我认为这里已经基本涵盖了：

LINQ to MySQL

- Smur

Answer 9

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

https://nhibernate.info/

- whosrdaddy

Answer 10

我使用MyGeneration与NHibernate

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

- Ortiga

---

@KrisKrause 是的，事实上我确实使用Fluent，但我认为OP更感兴趣的是MyGeneration：[...]它将允许我基于一个MySql表创建类。 - Ortiga