Create C# classes based of MySQL table
Solution 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 '}';
Solution 2
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!
Solution 3
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.
Solution 4
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
Solution 5
there appears to be a way to get EntityFramework working with MySQL
user489041
Updated on January 30, 2022Comments
-
user489041 over 2 years
Is there anything built into .Net or visual 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?
-
Ortiga over 12 years@KrisKrause true, in fact I do use fluent, but i think OP is more interested in MyGeneration: [...]
that will allow my to create classes based off of a MySql table
-
pdwetz about 11 yearsNice; needed a quick way for doing this while in a pinch. Created a gist with a few minor changes. gist.github.com/pdwetz/5368441
-
Stephanie over 8 yearsUtterly brilliant lightweight solution. Worked 1st time.
-
ijavid about 8 yearsuse
select concat('public ',tps.dest, IF(tps.dest = 'string', '', IF(is_nullable = 'NO', '', '?')) ...
and remove?
from mappings for correct NULLable mapping -
Talon almost 8 yearsyou could add
select 'tinytext' ,'string' union all
if your database has tinytext datatype -
Michael Moreno over 6 yearsThe link leads to a hacked website
-
NEER almost 5 yearsUse
if(c.IS_NULLABLE = 'NO', REPLACE(tps.dest, '?', '')
instead oftps.dest
-
Admin over 2 yearsYour answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.