Create C# classes based of MySQL table

Is there anything built into .Net or vi开发者_如何学Pythonsual studio that will allow my to create classes based off of a MySql table. I guess I am talking about persistence. I just want the class to be a 1 to 1 mapping of the table. Does anything free exist?

maybe you need something like this:

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

I adjusted the sql of MeelStorm because it was appearing some errors regarding the language. I put other types of data as well and I drop the class declaration because this is unnecessary to me. So the final result is:

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

Hope it helps. Cheers!

here is great work done :

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

Create a procedure as follows :

-- --------------------------------------------------------------------------------
-- 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

However a little manual work will require.

You can use the Entity Framework for this. It connects well with MySQL. I've been following this tutorial: http://www.devart.com/dotconnect/mysql/articles/tutorial_ef.html

there appears to be a way to get EntityFramework working with MySQL

Using MySQL with Entity Framework

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

You could also use LINQ to SQL with MySQL. But then, you should research a bit to find the correct provider you'd have to install.

I think it pretty much has it covered here:

LINQ to MySQL

I use NHibernate with MyGeneration

MyGeneration is a program that can read your database schema and generate code based on a template (in the case of NHibernate, the Entities and Mappings)

The first example is very good but some types are missing so I'm sharing adding missing types (set, float etc ..)

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

Although it does not give the desired result, it is close. can be developed.

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

Here is an example to convert mysql table to c# model class with required field. Hope it will be helpful for you.

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

NHibernate can connect to MySQL and is Free:

https://nhibernate.info/

Subsonic (open source) works with MySQL (5.0+) with special support for InnoDB -

http://subsonicproject.com/