Creating C # classes based on MySQL table

Question

Is there anything built into .Net or a visual studio that will allow me to create classes based on the MySql table. I guess I'm talking about perseverance. I just want the class to be a mapping from table 1 to 1. Is there anything free?

Answer 1

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

Answer 2

I adjusted sql MeelStorm because it showed some errors regarding the language. I also put other data types, and I drop the class declaration because it is not necessary for me. So the end result:

 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 this helps. Hooray!

Answer 3

here is a lot of work:

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

Create the 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 is required.

Answer 4

You can use the Entity Framework for this. It goes well with MySQL. I follow this guide: http://www.devart.com/dotconnect/mysql/articles/tutorial_ef.html

Answer 5

there seems to be a way to get EntityFramework to work with MySQL

Using MySQL with the Entity Framework

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

Answer 6

You can also use LINQ to SQL with MySQL. But then you need to research a bit to find the right provider that you will need to install.

I think it pretty much covers this:

LINQ to MySQL

Answer 7

NHibernate can connect to MySQL for free:

http://community.jboss.org/wiki/DatabasesSupportedByNHibernate

Answer 8

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

http://subsonicproject.com/

Answer 9

I am using 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, Entities and Mappings)

Answer 10

Database table for C # object

Create C # class from database table

WPF desktop application

https://github.com/illesarnold/DTE

Answer 11

The first example is very good, but some types are missing, so I'm sharing the addition of 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 '}';