Skip to content

Instantly share code, notes, and snippets.

@shukebeta
Forked from pdwetz/gist:5368441
Last active August 11, 2020 02:10
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save shukebeta/b315c940f63277fa982e6c93c0f97040 to your computer and use it in GitHub Desktop.
Save shukebeta/b315c940f63277fa982e6c93c0f97040 to your computer and use it in GitHub Desktop.
Outputs a POCO for a given MySql table. Based on http://stackoverflow.com/a/13918084/21865 with mild formatting changes and additional types added.
select '%TABLE_NAME%'
into @table;
select '%DB_NAME%'
into @schema;
select concat(' public class ', @table)
union
select ' {'
union
select concat(' public ', tps.dest, ' ', column_name, ' { get; set; }')
from information_schema.columns c
join (
select 'char' as orign, 'string' as dest
union all
select 'varchar', 'string'
union all
select 'timestamp', 'DateTime'
union all
select 'datetime', 'DateTime'
union all
select 'date', 'DateTime'
union all
select 'text', 'string'
union all
select 'binary', 'byte[]'
union all
select 'int', 'int'
union all
select 'decimal', 'decimal'
union all
select 'float', 'float'
union all
select 'tinyint', 'bool'
) tps on c.data_type like tps.orign
where table_schema = @schema
and table_name = @table
union
select ' }';
@shukebeta
Copy link
Author

shukebeta commented Aug 11, 2020

  1. Save this snippet into a file named 'Model.tmpl.sql'
  2. Save the following code into a file named 'addModel.sh'
  3. change a few variable definitions mentioned in the comment in the shell script
  4. enjoy!
#!/bin/bash

# generate a Model file base the DatabaseName & TableName
# search the word "YOUR" to find what you need to change

projectDir=YOUR_PROJECT_DIR
tmplDir=YOUR_TMPL_DIR

tmplFile=$tmplDir/Model.tmpl.sql

if [ $# -ne 2 ] ; then
    echo "USAGE: $0 DB_NAME TABLE_NAME"
    exit 1;
fi

DbName=${1}
ModelName=${2}

# change modelDir to your Model Directory
modelDir=$projectDir/YOUR_MODEL_DIR_PREFIX/$DbName
modelName=$modelDir/$ModelName.cs

mkdir -p $modelDir

# $modelTmplFile => real Model.cs
cat $modelTmplFile \
	| sed "s/%DB_NAME%/${DbName}/g" \
	| sed "s/%TABLE_NAME%/${ModelName}/g" > /tmp/tmpModel.sql

echo using System\; > $modelName
echo >> $modelName
echo namespace YOUR_NAMESPACE_PREFIX . $DbName >> $modelName
echo { >> $modelName

mysql < /tmp/tmpModel.sql | sed '1d' >> $modelDir/$ModelName.cs

echo } >> $modelName

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment