How can I generate a class from a SQL Server table object?
I'm not talking about using some ORM. I just need to create the entities (simple class). Something like:
public class Person
{
public string Name { get;set; }
public string Phone { get;set; }
}
Given some table like:
+----+-------+----------------+
| ID | Name | Phone |
+----+-------+----------------+
| 1 | Alice | (555) 555-5550 |开发者_如何学Go
| 2 | Bob | (555) 555-5551 |
| 3 | Cathy | (555) 555-5552 |
+----+-------+----------------+
Set @TableName to the name of your table.
declare @TableName sysname = 'TableName'
declare @Result varchar(max) = 'public class ' + @TableName + '
{'
select @Result = @Result + '
public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
select
replace(col.name, ' ', '_') ColumnName,
column_id ColumnId,
case typ.name
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 'double'
when 'image' then 'byte[]'
when 'int' then 'int'
when 'money' then 'decimal'
when 'nchar' then 'string'
when 'ntext' then 'string'
when 'numeric' then 'decimal'
when 'nvarchar' then 'string'
when 'real' then 'float'
when 'smalldatetime' then 'DateTime'
when 'smallint' then 'short'
when 'smallmoney' then 'decimal'
when 'text' then 'string'
when 'time' then 'TimeSpan'
when 'timestamp' then 'long'
when 'tinyint' then 'byte'
when 'uniqueidentifier' then 'Guid'
when 'varbinary' then 'byte[]'
when 'varchar' then 'string'
else 'UNKNOWN_' + typ.name
end ColumnType,
case
when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier')
then '?'
else ''
end NullableSign
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
where object_id = object_id(@TableName)
) t
order by ColumnId
set @Result = @Result + '
}'
print @Result
I could not get Alex's answer to work on Sql Server 2008 R2. So, I rewrote it using the same basic principles. It now allows for schemas and several fixes have been made for column-property mappings (including mapping nullable date types to nullable C# value types). Here is the Sql:
DECLARE @TableName VARCHAR(MAX) = 'NewsItem' -- Replace 'NewsItem' with your table name
DECLARE @TableSchema VARCHAR(MAX) = 'Markets' -- Replace 'Markets' with your schema name
DECLARE @result varchar(max) = ''
SET @result = @result + 'using System;' + CHAR(13) + CHAR(13)
IF (@TableSchema IS NOT NULL)
BEGIN
SET @result = @result + 'namespace ' + @TableSchema + CHAR(13) + '{' + CHAR(13)
END
SET @result = @result + 'public class ' + @TableName + CHAR(13) + '{' + CHAR(13)
SET @result = @result + '#region Instance Properties' + CHAR(13)
SELECT
@result = @result + CHAR(13)
+ ' public ' + ColumnType + ' ' + ColumnName + ' { get; set; } ' + CHAR(13)
FROM (SELECT
c.COLUMN_NAME AS ColumnName,
CASE c.DATA_TYPE
WHEN 'bigint' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Int64?'
ELSE 'Int64'
END
WHEN 'binary' THEN 'Byte[]'
WHEN 'bit' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'bool?'
ELSE 'bool'
END
WHEN 'char' THEN 'string'
WHEN 'date' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTime?'
ELSE 'DateTime'
END
WHEN 'datetime' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTime?'
ELSE 'DateTime'
END
WHEN 'datetime2' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTime?'
ELSE 'DateTime'
END
WHEN 'datetimeoffset' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTimeOffset?'
ELSE 'DateTimeOffset'
END
WHEN 'decimal' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'decimal?'
ELSE 'decimal'
END
WHEN 'float' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Single?'
ELSE 'Single'
END
WHEN 'image' THEN 'Byte[]'
WHEN 'int' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'int?'
ELSE 'int'
END
WHEN 'money' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'decimal?'
ELSE 'decimal'
END
WHEN 'nchar' THEN 'string'
WHEN 'ntext' THEN 'string'
WHEN 'numeric' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'decimal?'
ELSE 'decimal'
END
WHEN 'nvarchar' THEN 'string'
WHEN 'real' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Double?'
ELSE 'Double'
END
WHEN 'smalldatetime' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTime?'
ELSE 'DateTime'
END
WHEN 'smallint' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Int16?'
ELSE 'Int16'
END
WHEN 'smallmoney' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'decimal?'
ELSE 'decimal'
END
WHEN 'text' THEN 'string'
WHEN 'time' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'TimeSpan?'
ELSE 'TimeSpan'
END
WHEN 'timestamp' THEN 'Byte[]'
WHEN 'tinyint' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Byte?'
ELSE 'Byte'
END
WHEN 'uniqueidentifier' THEN CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Guid?'
ELSE 'Guid'
END
WHEN 'varbinary' THEN 'Byte[]'
WHEN 'varchar' THEN 'string'
ELSE 'Object'
END AS ColumnType,
c.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = @TableName
AND ISNULL(@TableSchema, c.TABLE_SCHEMA) = c.TABLE_SCHEMA) t
ORDER BY t.ORDINAL_POSITION
SET @result = @result + CHAR(13) + '#endregion Instance Properties' + CHAR(13)
SET @result = @result + '}' + CHAR(13)
IF (@TableSchema IS NOT NULL)
BEGIN
SET @result = @result + CHAR(13) + '}'
END
PRINT @result
It produces C# like the following:
using System;
namespace Markets
{
public class NewsItem {
#region Instance Properties
public Int32 NewsItemID { get; set; }
public Int32? TextID { get; set; }
public String Description { get; set; }
#endregion Instance Properties
}
}
It may be an idea to use EF, Linq to Sql, or even Scaffolding; however, there are times when a piece of coding like this comes in handy. Frankly, I do not like using EF navigation properties where the code it generates made 19,200 separate database calls to populate a 1000 row grid. This could have been achieved in a single database call. Nonetheless, it could just be that your technical architect does not want you to use EF and the like. So, you have to revert to code like this... Incidentally, it may also be an idea to decorate each of the properties with attributes for DataAnnotations, etc., but I'm keeping this strictly POCO.
EDIT Fixed for TimeStamp and Guid?
VB version
declare @TableName sysname = 'myTableName'
declare @prop varchar(max)
PRINT 'Public Class ' + @TableName
declare props cursor for
select distinct ' public property ' + ColumnName + ' AS ' + ColumnType AS prop
from (
select
replace(col.name, ' ', '_') ColumnName, column_id,
case typ.name
when 'bigint' then 'long'
when 'binary' then 'byte[]'
when 'bit' then 'boolean'
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 'integer'
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 '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'
end ColumnType
from sys.columns col join sys.types typ on col.system_type_id = typ.system_type_id
where object_id = object_id(@TableName)
) t
order by prop
open props
FETCH NEXT FROM props INTO @prop
WHILE @@FETCH_STATUS = 0
BEGIN
print @prop
FETCH NEXT FROM props INTO @prop
END
close props
DEALLOCATE props
PRINT 'End Class'
A bit late but I've created a web tool to help create a C# (or other) objects from SQL result, SQL Table and SQL SP.
sql2object.com
This can really safe you having to type all your properties and types.
If the types are not recognised the default will be selected.
I'm trying to give my 2 cents
0) QueryFirst https://marketplace.visualstudio.com/items?itemName=bbsimonbb.QueryFirst
1) Sql2Objects Creates the class starting from the result of a query (but not the DAL)
2) https://learn.microsoft.com/en-us/ef/ef6/resources/tools
3) https://visualstudiomagazine.com/articles/2012/12/11/sqlqueryresults-code-generation.aspx
4) http://www.codesmithtools.com/product/generator#features
To print out NULLABLE properties, use this.
It adds a slight modification to Alex Aza's script for the CASE statement block.
declare @TableName sysname = 'TableName'
declare @result varchar(max) = 'public class ' + @TableName + '
{'
select @result = @result + '
public ' + ColumnType + ' ' + ColumnName + ' { get; set; }
'
from
(
select
replace(col.name, ' ', '_') ColumnName,
column_id,
case typ.name
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 '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'
else 'UNKNOWN_' + typ.name
end +
CASE
WHEN col.is_nullable=1 AND
typ.name NOT IN (
'binary', 'varbinary', 'image',
'text', 'ntext',
'varchar', 'nvarchar', 'char', 'nchar')
THEN '?'
ELSE '' END AS [ColumnType]
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
where object_id = object_id(@TableName)
) t
order by column_id
set @result = @result + '
}'
print @result
Yea these are great if your using a simple ORM like Dapper.
If your using .Net you can generate an XSD file at run time with any DataSet using the WriteXmlSchema method. http://msdn.microsoft.com/en-us/library/xt7k72x8(v=vs.110).aspx
Like this:
using (SqlConnection cnn = new SqlConnection(mConnStr)) {
DataSet Data = new DataSet();
cnn.Open();
string sql = "SELECT * FROM Person";
using (SqlDataAdapter Da = new SqlDataAdapter(sql, cnn))
{
try
{
Da.Fill(Data);
Da.TableMappings.Add("Table", "Person");
Data.WriteXmlSchema(@"C:\Person.xsd");
}
catch (Exception ex)
{ MessageBox.Show(ex.Message); }
}
cnn.Close();
From there you can use xsd.exe to create a class that's XML serializable from the Developer Command Prompt. http://msdn.microsoft.com/en-us/library/x6c1kb0s(v=vs.110).aspx
like this:
xsd C:\Person.xsd /classes /language:CS
If you have access to SQL Server 2016, you can use the FOR JSON (with INCLUDE_NULL_VALUES) option to get JSON output from a select statement. Copy the output, then in Visual Studio, paste special -> paste JSON as class.
Kind of a budget solution, but might save some time.
I tried to use the suggestions above and in the process improved upon the solutions in this thread.
Let us say you use a base class (ObservableObject in this case) that implements the PropertyChanged Event, you would do something like this. I will probably write a blog post one day in my blog sqljana.wordpress.com
Please do substitute the values for the first three variables:
--These three things have to be substituted (when called from Powershell, they are replaced before execution)
DECLARE @Schema VARCHAR(MAX) = N'&Schema'
DECLARE @TableName VARCHAR(MAX) = N'&TableName'
DECLARE @Namespace VARCHAR(MAX) = N'&Namespace'
DECLARE @CRLF VARCHAR(2) = CHAR(13) + CHAR(10);
DECLARE @result VARCHAR(max) = ' '
DECLARE @PrivateProp VARCHAR(100) = @CRLF +
CHAR(9) + CHAR(9) + 'private <ColumnType> _<ColumnName>;';
DECLARE @PublicProp VARCHAR(255) = @CRLF +
CHAR(9) + CHAR(9) + 'public <ColumnType> <ColumnName> ' + @CRLF +
CHAR(9) + CHAR(9) + '{ ' + @CRLF +
CHAR(9) + CHAR(9) + ' get { return _<ColumnName>; } ' + @CRLF +
CHAR(9) + CHAR(9) + ' set ' + @CRLF +
CHAR(9) + CHAR(9) + ' { ' + @CRLF +
CHAR(9) + CHAR(9) + ' _<ColumnName> = value;' + @CRLF +
CHAR(9) + CHAR(9) + ' base.RaisePropertyChanged();' + @CRLF +
CHAR(9) + CHAR(9) + ' } ' + @CRLF +
CHAR(9) + CHAR(9) + '}' + @CRLF;
DECLARE @RPCProc VARCHAR(MAX) = @CRLF +
CHAR(9) + CHAR(9) + 'public event PropertyChangedEventHandler PropertyChanged; ' + @CRLF +
CHAR(9) + CHAR(9) + 'private void RaisePropertyChanged( ' + @CRLF +
CHAR(9) + CHAR(9) + ' [CallerMemberName] string caller = "" ) ' + @CRLF +
CHAR(9) + CHAR(9) + '{ ' + @CRLF +
CHAR(9) + CHAR(9) + ' if (PropertyChanged != null) ' + @CRLF +
CHAR(9) + CHAR(9) + ' { ' + @CRLF +
CHAR(9) + CHAR(9) + ' PropertyChanged( this, new PropertyChangedEventArgs( caller ) ); ' + @CRLF +
CHAR(9) + CHAR(9) + ' } ' + @CRLF +
CHAR(9) + CHAR(9) + '}';
DECLARE @PropChanged VARCHAR(200) = @CRLF +
CHAR(9) + CHAR(9) + 'protected override void AfterPropertyChanged(string propertyName) ' + @CRLF +
CHAR(9) + CHAR(9) + '{ ' + @CRLF +
CHAR(9) + CHAR(9) + ' System.Diagnostics.Debug.WriteLine("' + @TableName + ' property changed: " + propertyName); ' + @CRLF +
CHAR(9) + CHAR(9) + '}';
SET @result = 'using System;' + @CRLF + @CRLF +
'using MyCompany.Business;' + @CRLF + @CRLF +
'namespace ' + @Namespace + @CRLF + '{' + @CRLF +
' public class ' + @TableName + ' : ObservableObject' + @CRLF +
' {' + @CRLF +
' #region Instance Properties' + @CRLF
SELECT @result = @result
+
REPLACE(
REPLACE(@PrivateProp
, '<ColumnName>', ColumnName)
, '<ColumnType>', ColumnType)
+
REPLACE(
REPLACE(@PublicProp
, '<ColumnName>', ColumnName)
, '<ColumnType>', ColumnType)
FROM
(
SELECT c.COLUMN_NAME AS ColumnName
, CASE c.DATA_TYPE
WHEN 'bigint' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Int64?' ELSE 'Int64' END
WHEN 'binary' THEN 'Byte[]'
WHEN 'bit' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Boolean?' ELSE 'Boolean' END
WHEN 'char' THEN 'String'
WHEN 'date' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END
WHEN 'datetime' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END
WHEN 'datetime2' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END
WHEN 'datetimeoffset' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTimeOffset?' ELSE 'DateTimeOffset' END
WHEN 'decimal' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END
WHEN 'float' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Single?' ELSE 'Single' END
WHEN 'image' THEN 'Byte[]'
WHEN 'int' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Int32?' ELSE 'Int32' END
WHEN 'money' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END
WHEN 'nchar' THEN 'String'
WHEN 'ntext' THEN 'String'
WHEN 'numeric' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END
WHEN 'nvarchar' THEN 'String'
WHEN 'real' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Double?' ELSE 'Double' END
WHEN 'smalldatetime' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END
WHEN 'smallint' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Int16?' ELSE 'Int16'END
WHEN 'smallmoney' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END
WHEN 'text' THEN 'String'
WHEN 'time' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'TimeSpan?' ELSE 'TimeSpan' END
WHEN 'timestamp' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END
WHEN 'tinyint' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Byte?' ELSE 'Byte' END
WHEN 'uniqueidentifier' THEN 'Guid'
WHEN 'varbinary' THEN 'Byte[]'
WHEN 'varchar' THEN 'String'
ELSE 'Object'
END AS ColumnType
, c.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = @TableName
AND ISNULL(@Schema, c.TABLE_SCHEMA) = c.TABLE_SCHEMA
) t
ORDER BY t.ORDINAL_POSITION
SELECT @result = @result + @CRLF +
CHAR(9) + '#endregion Instance Properties' + @CRLF +
--CHAR(9) + @RPCProc + @CRLF +
CHAR(9) + @PropChanged + @CRLF +
CHAR(9) + '}' + @CRLF +
@CRLF + '}'
--SELECT @result
PRINT @result
The base class is based on Josh Smith's article here From http://joshsmithonwpf.wordpress.com/2007/08/29/a-base-class-which-implements-inotifypropertychanged/
I did rename the class to be called ObservableObject and also took advantage of a c# 5 feature using the CallerMemberName attribute
//From http://joshsmithonwpf.wordpress.com/2007/08/29/a-base-class-which-implements-inotifypropertychanged/
//
//Jana's change: Used c# 5 feature to bypass passing in the property name using [CallerMemberName]
// protected void RaisePropertyChanged([CallerMemberName] string propertyName = "")
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Diagnostics;
using System.Reflection;
using System.Runtime.CompilerServices;
namespace MyCompany.Business
{
/// <summary>
/// Implements the INotifyPropertyChanged interface and
/// exposes a RaisePropertyChanged method for derived
/// classes to raise the PropertyChange event. The event
/// arguments created by this class are cached to prevent
/// managed heap fragmentation.
/// </summary>
[Serializable]
public abstract class ObservableObject : INotifyPropertyChanged
{
#region Data
private static readonly Dictionary<string, PropertyChangedEventArgs> eventArgCache;
private const string ERROR_MSG = "{0} is not a public property of {1}";
#endregion // Data
#region Constructors
static ObservableObject()
{
eventArgCache = new Dictionary<string, PropertyChangedEventArgs>();
}
protected ObservableObject()
{
}
#endregion // Constructors
#region Public Members
/// <summary>
/// Raised when a public property of this object is set.
/// </summary>
[field: NonSerialized]
public event PropertyChangedEventHandler PropertyChanged;
/// <summary>
/// Returns an instance of PropertyChangedEventArgs for
/// the specified property name.
/// </summary>
/// <param name="propertyName">
/// The name of the property to create event args for.
/// </param>
public static PropertyChangedEventArgs
GetPropertyChangedEventArgs(string propertyName)
{
if (String.IsNullOrEmpty(propertyName))
throw new ArgumentException(
"propertyName cannot be null or empty.");
PropertyChangedEventArgs args;
// Get the event args from the cache, creating them
// and adding to the cache if necessary.
lock (typeof(ObservableObject))
{
bool isCached = eventArgCache.ContainsKey(propertyName);
if (!isCached)
{
eventArgCache.Add(
propertyName,
new PropertyChangedEventArgs(propertyName));
}
args = eventArgCache[propertyName];
}
return args;
}
#endregion // Public Members
#region Protected Members
/// <summary>
/// Derived classes can override this method to
/// execute logic after a property is set. The
/// base implementation does nothing.
/// </summary>
/// <param name="propertyName">
/// The property which was changed.
/// </param>
protected virtual void AfterPropertyChanged(string propertyName)
{
}
/// <summary>
/// Attempts to raise the PropertyChanged event, and
/// invokes the virtual AfterPropertyChanged method,
/// regardless of whether the event was raised or not.
/// </summary>
/// <param name="propertyName">
/// The property which was changed.
/// </param>
protected void RaisePropertyChanged([CallerMemberName] string propertyName = "")
{
this.VerifyProperty(propertyName);
PropertyChangedEventHandler handler = this.PropertyChanged;
if (handler != null)
{
// Get the cached event args.
PropertyChangedEventArgs args =
GetPropertyChangedEventArgs(propertyName);
// Raise the PropertyChanged event.
handler(this, args);
}
this.AfterPropertyChanged(propertyName);
}
#endregion // Protected Members
#region Private Helpers
[Conditional("DEBUG")]
private void VerifyProperty(string propertyName)
{
Type type = this.GetType();
// Look for a public property with the specified name.
PropertyInfo propInfo = type.GetProperty(propertyName);
if (propInfo == null)
{
// The property could not be found,
// so alert the developer of the problem.
string msg = string.Format(
ERROR_MSG,
propertyName,
type.FullName);
Debug.Fail(msg);
}
}
#endregion // Private Helpers
}
}
Here is the part that you guys are going to like some more. I built a Powershell script to generate for all the tables in a SQL database. It is based on a Powershell guru named Chad Miller's Invoke-SQLCmd2 cmdlet which can be downloaded from here: http://gallery.technet.microsoft.com/ScriptCenter/7985b7ef-ed89-4dfd-b02a-433cc4e30894/
Once you have that cmdlet, the Powershell script to generate for all tables becomes simple (do substitute the variables with your specific values).
. C:\MyScripts\Invoke-Sqlcmd2.ps1
$serverInstance = "MySQLInstance"
$databaseName = "MyDb"
$generatorSQLFile = "C:\MyScripts\ModelGen.sql"
$tableListSQL = "SELECT name FROM $databaseName.sys.tables"
$outputFolder = "C:\MyScripts\Output\"
$namespace = "MyCompany.Business"
$placeHolderSchema = "&Schema"
$placeHolderTableName = "&TableName"
$placeHolderNamespace = "&Namespace"
#Get the list of tables in the database to generate c# models for
$tables = Invoke-Sqlcmd2 -ServerInstance $serverInstance -Database $databaseName -Query $tableListSQL -As DataRow -Verbose
foreach ($table in $tables)
{
$table1 = $table[0]
$outputFile = "$outputFolder\$table1.cs"
#Replace variables with values (returns an array that we convert to a string to use as query)
$generatorSQLFileWSubstitutions = (Get-Content $generatorSQLFile).
Replace($placeHolderSchema,"dbo").
Replace($placeHolderTableName, $table1).
Replace($placeHolderNamespace, $namespace) | Out-String
"Ouputing for $table1 to $outputFile"
#The command generates .cs file content for model using "PRINT" statements which then gets written to verbose output (stream 4)
# ...capture the verbose output and redirect to a file
(Invoke-Sqlcmd2 -ServerInstance $serverInstance -Database $databaseName -Query $generatorSQLFileWSubstitutions -Verbose) 4> $outputFile
}
create PROCEDURE for create custom code using template
create PROCEDURE [dbo].[createCode]
(
@TableName sysname = '',
@befor varchar(max)='public class @TableName
{',
@templet varchar(max)='
public @ColumnType @ColumnName { get; set; } // @ColumnDesc ',
@after varchar(max)='
}'
)
AS
BEGIN
declare @result varchar(max)
set @befor =replace(@befor,'@TableName',@TableName)
set @result=@befor
select @result = @result
+ replace(replace(replace(replace(replace(@templet,'@ColumnType',ColumnType) ,'@ColumnName',ColumnName) ,'@ColumnDesc',ColumnDesc),'@ISPK',ISPK),'@max_length',max_length)
from
(
select
column_id,
replace(col.name, ' ', '_') ColumnName,
typ.name as sqltype,
typ.max_length,
is_identity,
pkk.ISPK,
case typ.name
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 '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'
else 'UNKNOWN_' + typ.name
END + CASE WHEN col.is_nullable=1 AND typ.name NOT IN ('binary', 'varbinary', 'image', 'text', 'ntext', 'varchar', 'nvarchar', 'char', 'nchar') THEN '?' ELSE '' END ColumnType,
isnull(colDesc.colDesc,'') AS ColumnDesc
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
left join
(
SELECT c.name AS 'ColumnName', CASE WHEN dd.pk IS NULL THEN 'false' ELSE 'true' END ISPK
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
LEFT JOIN (SELECT K.COLUMN_NAME , C.CONSTRAINT_TYPE as pk
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
ON K.TABLE_NAME = C.TABLE_NAME
AND K.CONSTRAINT_NAME = C.CONSTRAINT_NAME
AND K.CONSTRAINT_CATALOG = C.CONSTRAINT_CATALOG
AND K.CONSTRAINT_SCHEMA = C.CONSTRAINT_SCHEMA
WHERE K.TABLE_NAME = @TableName) as dd
ON dd.COLUMN_NAME = c.name
WHERE t.name = @TableName
) pkk on ColumnName=col.name
OUTER APPLY (
SELECT TOP 1 CAST(value AS NVARCHAR(max)) AS colDesc
FROM
sys.extended_properties
WHERE
major_id = col.object_id
AND
minor_id = COLUMNPROPERTY(major_id, col.name, 'ColumnId')
) colDesc
where object_id = object_id(@TableName)
) t
set @result=@result+@after
select @result
--print @result
END
now create custom code
for example c# class
exec [createCode] @TableName='book',@templet ='
public @ColumnType @ColumnName { get; set; } // @ColumnDesc '
output is
public class book
{
public long ID { get; set; } //
public String Title { get; set; } // Book Title
}
for LINQ
exec [createCode] @TableName='book'
, @befor ='[System.Data.Linq.Mapping.Table(Name = "@TableName")]
public class @TableName
{',
@templet ='
[System.Data.Linq.Mapping.Column(Name = "@ColumnName", IsPrimaryKey = @ISPK)]
public @ColumnType @ColumnName { get; set; } // @ColumnDesc
' ,
@after ='
}'
output is
[System.Data.Linq.Mapping.Table(Name = "book")]
public class book
{
[System.Data.Linq.Mapping.Column(Name = "ID", IsPrimaryKey = true)]
public long ID { get; set; } //
[System.Data.Linq.Mapping.Column(Name = "Title", IsPrimaryKey = false)]
public String Title { get; set; } // Book Title
}
for java class
exec [createCode] @TableName='book',@templet ='
public @ColumnType @ColumnName ; // @ColumnDesc
public @ColumnType get@ColumnName()
{
return this.@ColumnName;
}
public void set@ColumnName(@ColumnType @ColumnName)
{
this.@ColumnName=@ColumnName;
}
'
output is
public class book
{
public long ID ; //
public long getID()
{
return this.ID;
}
public void setID(long ID)
{
this.ID=ID;
}
public String Title ; // Book Title
public String getTitle()
{
return this.Title;
}
public void setTitle(String Title)
{
this.Title=Title;
}
}
for android sugarOrm model
exec [createCode] @TableName='book'
, @befor ='@Table(name = "@TableName")
public class @TableName
{',
@templet ='
@Column(name = "@ColumnName")
public @ColumnType @ColumnName ;// @ColumnDesc
' ,
@after ='
}'
output is
@Table(name = "book")
public class book
{
@Column(name = "ID")
public long ID ;//
@Column(name = "Title")
public String Title ;// Book Title
}
Visual Studio Magazine published this:
Generating .NET POCO Classes for SQL Query Results
It has a downloadable project that you can build, give it your SQL info, and it will crank out the class for you.
Now if that tool just created the SQL Commands for SELECT, INSERT, and UPDATE....
Since no one mentioned it before, there is also Scaffold-DbContext
In NuGet Package Manager console type the following:
Scaffold-DbContext "Your Connection String" Microsoft.EntityFrameworkCore.SqlServer -OutputDir "Output Directory"
Just thought I'd add my own variation of the top answer for anyone who's interested. The main features are:
It will automatically generate classes for all the tables in the entire schema. Just specify the schema name.
It will add System.Data.Linq.Mapping attributes to the class and each property. Useful for anyone using Linq to SQL.
declare @TableName sysname declare @Result varchar(max) declare @schema varchar(20) = 'dbo' DECLARE @Cursor CURSOR SET @Cursor = CURSOR FAST_FORWARD FOR SELECT DISTINCT tablename = rc1.TABLE_NAME FROM INFORMATION_SCHEMA.Tables rc1 where rc1.TABLE_SCHEMA = @schema OPEN @Cursor FETCH NEXT FROM @Cursor INTO @TableName WHILE (@@FETCH_STATUS = 0) BEGIN set @Result = '[Table(Name = "' + @schema + '.' + @TableName + '")] public class ' + Replace(@TableName, '$', '_') + ' {' select @Result = @Result + ' [Column' + PriKey +'] public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; } ' from ( select replace(col.name, ' ', '_') ColumnName, col.column_id ColumnId, case typ.name 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 'double' when 'image' then 'byte[]' when 'int' then 'int' when 'money' then 'decimal' when 'nchar' then 'string' when 'ntext' then 'string' when 'numeric' then 'decimal' when 'nvarchar' then 'string' when 'real' then 'float' when 'smalldatetime' then 'DateTime' when 'smallint' then 'short' when 'smallmoney' then 'decimal' when 'text' then 'string' when 'time' then 'TimeSpan' when 'timestamp' then 'long' when 'tinyint' then 'byte' when 'uniqueidentifier' then 'Guid' when 'varbinary' then 'byte[]' when 'varchar' then 'string' else 'UNKNOWN_' + typ.name end ColumnType, case when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') then '?' else '' end NullableSign, case when pk.CONSTRAINT_NAME is not null and ic.column_id is not null then '(IsPrimaryKey = true, IsDbGenerated = true)' when pk.CONSTRAINT_NAME is not null then '(IsPrimaryKey = true)' when ic.column_id is not null then '(IsDbGenerated = true)' else '' end PriKey from sys.columns col join sys.types typ on col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id left outer join sys.identity_columns ic on ic.column_id = col.column_id and col.object_id = ic.object_id left outer join ( SELECT K.TABLE_NAME , K.COLUMN_NAME , K.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON C.TABLE_NAME = K.TABLE_NAME AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME where C.CONSTRAINT_TYPE = 'PRIMARY KEY' ) pk on pk.COLUMN_NAME = col.name and pk.TABLE_NAME = @TableName where col.object_id = object_id(@schema + '.' + @TableName) ) t order by ColumnId set @Result = @Result + ' } ' print @Result FETCH NEXT FROM @Cursor INTO @TableName end CLOSE @Cursor DEALLOCATE @Cursor GO
Added 6-29-22: Here's an updated variation for generating models for EF Core (dotNet 6.0).
CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @PrevChar CHAR(1)
DECLARE @OutputString VARCHAR(4000)
SET @OutputString = @InputString
SET @Index = 1
WHILE @Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @Index, 1)
SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
ELSE SUBSTRING(@InputString, @Index - 1, 1)
END
IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
SET @Index = @Index + 1
END
RETURN @OutputString
END
go
declare @TableName sysname
declare @Result varchar(max)
declare @schema varchar(20) = 'dbo'
DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT tablename = rc1.TABLE_NAME
FROM INFORMATION_SCHEMA.Tables rc1
where rc1.TABLE_SCHEMA = @schema
OPEN @Cursor FETCH NEXT FROM @Cursor INTO @TableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
set @Result = '[Table("' + @TableName + '", Schema = "' + @schema + '")]
public class ' + Replace(@TableName, '$', '_') + '
{'
select @Result = @Result + '
[Column("' + ColumnName + '"' + stringType + ')]
public ' + ColumnType + NullableSign + ' ' + PropertyName + ' { get; set; }
'
from
(
select
replace(col.name, ' ', '_') ColumnName,
replace(replace([dbo].[InitCap](col.name), ' ', ''), '_', '') PropertyName,
col.column_id ColumnId,
case typ.name
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 'double'
when 'image' then 'byte[]'
when 'int' then 'int'
when 'money' then 'decimal'
when 'nchar' then 'string'
when 'ntext' then 'string'
when 'numeric' then 'decimal'
when 'nvarchar' then 'string'
when 'real' then 'float'
when 'smalldatetime' then 'DateTime'
when 'smallint' then 'short'
when 'smallmoney' then 'decimal'
when 'text' then 'string'
when 'time' then 'TimeSpan'
when 'timestamp' then 'long'
when 'tinyint' then 'byte'
when 'uniqueidentifier' then 'Guid'
when 'varbinary' then 'byte[]'
when 'varchar' then 'string'
else 'UNKNOWN_' + typ.name
end ColumnType,
case
when col.is_nullable = 1
then '?'
else ''
end NullableSign,
case
when typ.name in ('char', 'nchar', 'nvarchar', 'varchar')
then ', TypeName = "' + typ.name + '(' + convert(varchar, col.max_length) + ')"'
else ''
end stringType
from sys.columns col
join sys.types typ on col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
left outer join sys.identity_columns ic on ic.column_id = col.column_id and col.object_id = ic.object_id
left outer join (
SELECT K.TABLE_NAME ,
K.COLUMN_NAME ,
K.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON C.TABLE_NAME = K.TABLE_NAME
AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG
AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA
AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME
where C.CONSTRAINT_TYPE = 'PRIMARY KEY'
) pk on pk.COLUMN_NAME = col.name and pk.TABLE_NAME = @TableName
where col.object_id = object_id(@schema + '.' + @TableName)
) t
order by ColumnId
set @Result = @Result + '
}
'
print @Result
FETCH NEXT FROM @Cursor INTO @TableName
end
CLOSE @Cursor DEALLOCATE @Cursor
GO
DROP FUNCTION [dbo].[InitCap]
GO
To print out NULLABLE properties WITH COMMENTS (Summary), use this.
It is a slight modification of first answer
declare @TableName sysname = 'TableName'
declare @result varchar(max) = 'public class ' + @TableName + '
{'
select @result = @result
+ CASE WHEN ColumnDesc IS NOT NULL THEN '
/// <summary>
/// ' + ColumnDesc + '
/// </summary>' ELSE '' END
+ '
public ' + ColumnType + ' ' + ColumnName + ' { get; set; }'
from
(
select
replace(col.name, ' ', '_') ColumnName,
column_id,
case typ.name
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 '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'
else 'UNKNOWN_' + typ.name
END + CASE WHEN col.is_nullable=1 AND typ.name NOT IN ('binary', 'varbinary', 'image', 'text', 'ntext', 'varchar', 'nvarchar', 'char', 'nchar') THEN '?' ELSE '' END ColumnType,
colDesc.colDesc AS ColumnDesc
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
OUTER APPLY (
SELECT TOP 1 CAST(value AS NVARCHAR(max)) AS colDesc
FROM
sys.extended_properties
WHERE
major_id = col.object_id
AND
minor_id = COLUMNPROPERTY(major_id, col.name, 'ColumnId')
) colDesc
where object_id = object_id(@TableName)
) t
order by column_id
set @result = @result + '
}'
print @result
Commercial, but CodeSmith Generator does that: http://www.codesmithtools.com/product/generator
I'm confused as to what you want out of this, but here are the general options when designing what you want to design.
- Using the built-in ORM in your version Visual Studio.
- Write one yourself, similar to your code example. As usual, a tutorial is your best friend if you're not sure how.
- Use an alternative ORM such as NHibernate.
In appreciation to Alex's solution and Guilherme for asking I made this for MySQL to generate C# classes
set @schema := 'schema_name';
set @table := 'table_name';
SET group_concat_max_len = 2048;
SELECT
concat('public class ', @table, '\n{\n', GROUP_CONCAT(a.property_ SEPARATOR '\n'), '\n}') class_
FROM
(select
CONCAT(
'\tpublic ',
case
when DATA_TYPE = 'bigint' then 'long'
when DATA_TYPE = 'BINARY' then 'byte[]'
when DATA_TYPE = 'bit' then 'bool'
when DATA_TYPE = 'char' then 'string'
when DATA_TYPE = 'date' then 'DateTime'
when DATA_TYPE = 'datetime' then 'DateTime'
when DATA_TYPE = 'datetime2' then 'DateTime'
when DATA_TYPE = 'datetimeoffset' then 'DateTimeOffset'
when DATA_TYPE = 'decimal' then 'decimal'
when DATA_TYPE = 'double' then 'double'
when DATA_TYPE = 'float' then 'float'
when DATA_TYPE = 'image' then 'byte[]'
when DATA_TYPE = 'int' then 'int'
when DATA_TYPE = 'money' then 'decimal'
when DATA_TYPE = 'nchar' then 'char'
when DATA_TYPE = 'ntext' then 'string'
when DATA_TYPE = 'numeric' then 'decimal'
when DATA_TYPE = 'nvarchar' then 'string'
when DATA_TYPE = 'real' then 'double'
when DATA_TYPE = 'smalldatetime' then 'DateTime'
when DATA_TYPE = 'smallint' then 'short'
when DATA_TYPE = 'smallmoney' then 'decimal'
when DATA_TYPE = 'text' then 'string'
when DATA_TYPE = 'time' then 'TimeSpan'
when DATA_TYPE = 'timestamp' then 'DateTime'
when DATA_TYPE = 'tinyint' then 'byte'
when DATA_TYPE = 'uniqueidentifier' then 'Guid'
when DATA_TYPE = 'varbinary' then 'byte[]'
when DATA_TYPE = 'varchar' then 'string'
else '_UNKNOWN_'
end, ' ',
COLUMN_NAME, ' {get; set;}') as property_
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = @table AND table_schema = @schema) a
;
Thanks Alex and Guilherme!
Grab QueryFirst, visual studio extension that generates wrapper classes from SQL queries. You not only get...
public class MyClass{
public string MyProp{get;set;}
public int MyNumberProp{get;set;}
...
}
And as a bonus, it'll throw in...
public class MyQuery{
public static IEnumerable<MyClass>Execute(){}
public static MyClass GetOne(){}
...
}
Are you sure you want to base your classes directly on your tables? Tables are a static, normalized data storage notion that belongs in the DB. Classes are dynamic, fluid, disposable, context-specific, perhaps denormalized. Why not write real queries for the data you want for an operation, and let QueryFirst generate the classes from that.
This post has saved me several times. I just want to add my two cents. For those that dont like to use ORMs, and instead write their own DAL classes, when you have like 20 columns in a table, and 40 different tables with their respective CRUD operations, its painful and a waste of time. I repeated the above code, for generating CRUD methods based on the table entity and properties.
declare @TableName sysname = 'Tablename'
declare @Result varchar(max) = 'public class ' + @TableName + '
{'
select @Result = @Result + '
public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
select
replace(col.name, ' ', '_') ColumnName,
column_id ColumnId,
case typ.name
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 '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'
else 'UNKNOWN_' + typ.name
end ColumnType,
case
when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier')
then '?'
else ''
end NullableSign
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
where object_id = object_id(@TableName)
) t
order by ColumnId
set @Result = @Result + '
}'
print @Result
declare @InitDataAccess varchar(max) = 'public class '+ @TableName +'DataAccess
{ '
declare @ListStatement varchar(max) ='public List<'+@TableName+'> Get'+@TableName+'List()
{
String conn = ConfigurationManager.ConnectionStrings["ConnectionNameInWeb.config"].ConnectionString;
var itemList = new List<'+@TableName+'>();
try
{
using (var sqlCon = new SqlConnection(conn))
{
sqlCon.Open();
var cmd = new SqlCommand
{
Connection = sqlCon,
CommandType = CommandType.StoredProcedure,
CommandText = "StoredProcedureSelectAll"
};
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var item = new '+@TableName+'();
'
select @ListStatement = @ListStatement + '
item.'+ ColumnName + '= ('+ ColumnType + NullableSign +')reader["'+ColumnName+'"];
'
from
(
select
replace(col.name, ' ', '_') ColumnName,
column_id ColumnId,
case typ.name
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 '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'
else 'UNKNOWN_' + typ.name
end ColumnType,
case
when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier')
then '?'
else ''
end NullableSign
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
where object_id = object_id(@TableName)
) t
order by ColumnId
select @ListStatement = @ListStatement +'
itemList.Add(item);
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return itemList;
}'
declare @GetIndividual varchar(max) =
'public '+@TableName+' Get'+@TableName+'()
{
String conn = ConfigurationManager.ConnectionStrings["ConnectionNameInWeb.config"].ConnectionString;
var item = new '+@TableName+'();
try
{
using (var sqlCon = new SqlConnection(conn))
{
sqlCon.Open();
var cmd = new SqlCommand
{
Connection = sqlCon,
CommandType = CommandType.StoredProcedure,
CommandText = "StoredProcedureSelectIndividual"
};
cmd.Parameters.AddWithValue("@ItemCriteria", item.id);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{'
select @GetIndividual = @GetIndividual + '
item.'+ ColumnName + '= ('+ ColumnType + NullableSign +')reader["'+ColumnName+'"];
'
from
(
select
replace(col.name, ' ', '_') ColumnName,
column_id ColumnId,
case typ.name
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 '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'
else 'UNKNOWN_' + typ.name
end ColumnType,
case
when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier')
then '?'
else ''
end NullableSign
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
where object_id = object_id(@TableName)
) t
order by ColumnId
select @GetIndividual = @GetIndividual +'
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return item;
}'
declare @InsertStatement varchar(max) = 'public void Insert'+@TableName+'('+@TableName+' item)
{
String conn = ConfigurationManager.ConnectionStrings["ConnectionNameInWeb.config"].ConnectionString;
try
{
using (var sqlCon = new SqlConnection(conn))
{
sqlCon.Open();
var cmd = new SqlCommand
{
Connection = sqlCon,
CommandType = CommandType.StoredProcedure,
CommandText = "StoredProcedureInsert"
};
'
select @InsertStatement = @InsertStatement + '
cmd.Parameters.AddWithValue("@'+ColumnName+'", item.'+ColumnName+');
'
from
(
select
replace(col.name, ' ', '_') ColumnName,
column_id ColumnId,
case typ.name
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 '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'
else 'UNKNOWN_' + typ.name
end ColumnType,
case
when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier')
then '?'
else ''
end NullableSign
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
where object_id = object_id(@TableName)
) t
order by ColumnId
select @InsertStatement = @InsertStatement +'
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}'
declare @UpdateStatement varchar(max) = 'public void Update'+@TableName+'('+@TableName+' item)
{
String conn = ConfigurationManager.ConnectionStrings["ConnectionNameInWeb.config"].ConnectionString;
try
{
using (var sqlCon = new SqlConnection(conn))
{
sqlCon.Open();
var cmd = new SqlCommand
{
Connection = sqlCon,
CommandType = CommandType.StoredProcedure,
CommandText = "StoredProcedureUpdate"
};
cmd.Parameters.AddWithValue("@UpdateCriteria", item.Id);
'
select @UpdateStatement = @UpdateStatement + '
cmd.Parameters.AddWithValue("@'+ColumnName+'", item.'+ColumnName+');
'
from
(
select
replace(col.name, ' ', '_') ColumnName,
column_id ColumnId,
case typ.name
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 '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'
else 'UNKNOWN_' + typ.name
end ColumnType,
case
when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier')
then '?'
else ''
end NullableSign
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
where object_id = object_id(@TableName)
) t
order by ColumnId
select @UpdateStatement = @UpdateStatement +'
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}'
declare @EndDataAccess varchar(max) = '
}'
print @InitDataAccess
print @GetIndividual
print @InsertStatement
print @UpdateStatement
print @ListStatement
print @EndDataAccess
Of course its not bulletproof code, and can be improved. Just wanted to contribute to this excelent solution
slightly modified from top reply:
declare @TableName sysname = 'HistoricCommand'
declare @Result varchar(max) = '[System.Data.Linq.Mapping.Table(Name = "' + @TableName + '")]
public class Dbo' + @TableName + '
{'
select @Result = @Result + '
[System.Data.Linq.Mapping.Column(Name = "' + t.ColumnName + '", IsPrimaryKey = ' + pkk.ISPK + ')]
public ' + ColumnType + NullableSign + ' ' + t.ColumnName + ' { get; set; }
'
from
(
select
replace(col.name, ' ', '_') ColumnName,
column_id ColumnId,
case typ.name
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 'string'
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 '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'
else 'UNKNOWN_' + typ.name
end ColumnType,
case
when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier')
then '?'
else ''
end NullableSign
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
where object_id = object_id(@TableName)
) t,
(
SELECT c.name AS 'ColumnName', CASE WHEN dd.pk IS NULL THEN 'false' ELSE 'true' END ISPK
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
LEFT JOIN (SELECT K.COLUMN_NAME , C.CONSTRAINT_TYPE as pk
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
ON K.TABLE_NAME = C.TABLE_NAME
AND K.CONSTRAINT_NAME = C.CONSTRAINT_NAME
AND K.CONSTRAINT_CATALOG = C.CONSTRAINT_CATALOG
AND K.CONSTRAINT_SCHEMA = C.CONSTRAINT_SCHEMA
WHERE K.TABLE_NAME = @TableName) as dd
ON dd.COLUMN_NAME = c.name
WHERE t.name = @TableName
) pkk
where pkk.ColumnName = t.ColumnName
order by ColumnId
set @Result = @Result + '
}'
print @Result
which makes output needed for full LINQ in C# declaration
[System.Data.Linq.Mapping.Table(Name = "HistoricCommand")]
public class DboHistoricCommand
{
[System.Data.Linq.Mapping.Column(Name = "HistoricCommandId", IsPrimaryKey = true)]
public int HistoricCommandId { get; set; }
[System.Data.Linq.Mapping.Column(Name = "PHCloudSoftwareInstanceId", IsPrimaryKey = true)]
public int PHCloudSoftwareInstanceId { get; set; }
[System.Data.Linq.Mapping.Column(Name = "CommandType", IsPrimaryKey = false)]
public int CommandType { get; set; }
[System.Data.Linq.Mapping.Column(Name = "InitiatedDateTime", IsPrimaryKey = false)]
public DateTime InitiatedDateTime { get; set; }
[System.Data.Linq.Mapping.Column(Name = "CompletedDateTime", IsPrimaryKey = false)]
public DateTime CompletedDateTime { get; set; }
[System.Data.Linq.Mapping.Column(Name = "WasSuccessful", IsPrimaryKey = false)]
public bool WasSuccessful { get; set; }
[System.Data.Linq.Mapping.Column(Name = "Message", IsPrimaryKey = false)]
public string Message { get; set; }
[System.Data.Linq.Mapping.Column(Name = "ResponseData", IsPrimaryKey = false)]
public string ResponseData { get; set; }
[System.Data.Linq.Mapping.Column(Name = "Message_orig", IsPrimaryKey = false)]
public string Message_orig { get; set; }
[System.Data.Linq.Mapping.Column(Name = "Message_XX", IsPrimaryKey = false)]
public string Message_XX { get; set; }
}
I packaged ideas from several SQL based answers here, mainly the root answer by Alex Aza, into klassify, a console application that generates all the classes for a specified database at once:
For example, given a table Users that looks like this:
+----+------------------+-----------+---------------------+
| Id | Name | Username | Email |
+----+------------------+-----------+---------------------+
| 1 | Leanne Graham | Bret | Sincere@april.biz |
| 2 | Ervin Howell | Antonette | Shanna@melissa.tv |
| 3 | Clementine Bauch | Samantha | Nathan@yesenia.net |
+----+------------------+-----------+---------------------+
klassify will generate a file called Users.cs that looks like this:
public class User
{
public int Id {get; set; }
public string Name { get;set; }
public string Username { get; set; }
public string Email { get; set; }
}
It will output one file for every table. Discard what you don't use.
Usage
--out, -o:
output directory << defaults to the current directory >>
--user, -u:
sql server user id << required >>
--password, -p:
sql server password << required >>
--server, -s:
sql server << defaults to localhost >>
--database, -d:
sql database << required >>
--timeout, -t:
connection timeout << defaults to 30 >>
--help, -h:
show help
Another Solution for Oracle DB -> C#
- Single Query
- No Function No Procedure
- Mulitple Table
Added DataAnnotations
- [Key]
- [Required]
- [Table]
- [StringLength]
- [Column]
- Nullable
Query
https://gist.github.com/omansak/f19eefffd2d639ac72a1f4b506d8471a
Output
[Table("AGENTS")]
public class Agents
{
[Key]
[Required]
[Column("INT_ID", TypeName = "NUMBER(10,0)", Order = 1)]
public long IntId { get; set; }
[Key]
[Required]
[StringLength(15)]
[Column("REFERENCE_CODE", TypeName = "VARCHAR2(15)", Order = 2)]
public string ReferenceCode { get; set; }
[Required]
[Column("PARENT_INT_ID", TypeName = "NUMBER(10,0)", Order = 3)]
public long ParentIntId { get; set; }
[Required]
[StringLength(200)]
[Column("TITLE", TypeName = "VARCHAR2(200)", Order = 4)]
public string Title { get; set; }
[Required]
[Column("START_DATE", TypeName = "DATE", Order = 5)]
public DateTime StartDate { get; set; }
[Required]
[Column("END_DATE", TypeName = "DATE", Order = 6)]
public DateTime EndDate { get; set; }
[Required]
[StringLength(1)]
[Column("AGENT_TYPE", TypeName = "VARCHAR2(1)", Order = 7)]
public string AgentType { get; set; }
[Required]
[Column("CREATE_DATE", TypeName = "DATE", Order = 8)]
public DateTime CreateDate { get; set; }
[Required]
[StringLength(32)]
[Column("CREATE_USER", TypeName = "VARCHAR2(32)", Order = 9)]
public string CreateUser { get; set; }
[StringLength(200)]
[Column("RESPONSIBLE_CONTACT", TypeName = "VARCHAR2(200)", Order = 10)]
public string ResponsibleContact { get; set; }
[StringLength(100)]
[Column("RESPONSIBLE_TITLE", TypeName = "VARCHAR2(100)", Order = 11)]
public string ResponsibleTitle { get; set; }
[StringLength(100)]
[Column("AGENCY_PLATE_NO", TypeName = "VARCHAR2(100)", Order = 12)]
public string AgencyPlateNo { get; set; }
[Column("AGENCY_COVER_AMOUNT", TypeName = "NUMBER(24,2)", Order = 13)]
public double? AgencyCoverAmount { get; set; }
[StringLength(100)]
[Column("MERSIS_NO", TypeName = "VARCHAR2(100)", Order = 14)]
public string MersisNo { get; set; }
[StringLength(100)]
[Column("TECH_PERSONEL_NO", TypeName = "VARCHAR2(100)", Order = 15)]
public string TechPersonelNo { get; set; }
[StringLength(100)]
[Column("TECH_PERSONEL_NAME", TypeName = "VARCHAR2(100)", Order = 16)]
public string TechPersonelName { get; set; }
[Column("COVER_END_DATE", TypeName = "DATE", Order = 17)]
public DateTime? CoverEndDate { get; set; }
[Column("BRANCH_NUMBER", TypeName = "NUMBER(10,0)", Order = 18)]
public long? BranchNumber { get; set; }
[Column("ACTION_NUMBER", TypeName = "NUMBER(10,0)", Order = 19)]
public long? ActionNumber { get; set; }
[Column("CLUB_PARTICIPATION_COUNT", TypeName = "NUMBER(10,0)", Order = 20)]
public long? ClubParticipationCount { get; set; }
[Column("AGENCY_CONTRACT_DATE", TypeName = "DATE", Order = 21)]
public DateTime? AgencyContractDate { get; set; }
[StringLength(200)]
[Column("KEP_ADDRESS", TypeName = "VARCHAR2(200)", Order = 22)]
public string KepAddress { get; set; }
}
The simplest way is EF, Reverse Engineer. http://msdn.microsoft.com/en-US/data/jj593170
I like to setup my classes with private local members and public accessors / mutators. So I've modified Alex's script above to do that as well for anyone that is intersted.
declare @TableName sysname = 'TABLE_NAME'
declare @result varchar(max) = 'public class ' + @TableName + '
{'
SET @result = @result +
'
public ' + @TableName + '()
{}
';
select @result = @result + '
private ' + ColumnType + ' ' + ' m_' + stuff(replace(ColumnName, '_', ''), 1, 1, lower(left(ColumnName, 1))) + ';'
from
(
select
replace(col.name, ' ', '_') ColumnName,
column_id,
case typ.name
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 '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'
else 'UNKNOWN_' + typ.name
end ColumnType
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
where object_id = object_id(@TableName)
) t
order by column_id
SET @result = @result + '
'
select @result = @result + '
public ' + ColumnType + ' ' + ColumnName + ' { get { return m_' + stuff(replace(ColumnName, '_', ''), 1, 1, lower(left(ColumnName, 1))) + ';} set {m_' + stuff(replace(ColumnName, '_', ''), 1, 1, lower(left(ColumnName, 1))) + ' = value;} }' from
(
select
replace(col.name, ' ', '_') ColumnName,
column_id,
case typ.name
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 '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'
else 'UNKNOWN_' + typ.name
end ColumnType
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
where object_id = object_id(@TableName)
) t
order by column_id
set @result = @result + '
}'
print @result
A small addition to the solutions before:
object_id(@TableName) works only if you are in the default schema.
(Select id from sysobjects where name = @TableName)
works in any schema provided @tableName is unique.
In case it's useful to anyone else, working on a Code-First approach using attribute mappings, I wanted something that just left me needing to bind an entity in the object model. So thanks to Carnotaurus' answer, I extended it as per their own suggestion and made a couple of tweaks.
This relies therefore on this solution comprising TWO parts, both of which are SQL Scalar-Valued functions:
- An 'Initial Caps' function (taken from: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8a58dbe1-7a4b-4287-afdc-bfecb4e69b23/similar-to-initcap-in-sql-server-tsql and slightly modified to meet my needs)
ALTER function [dbo].[ProperCase] (@cStringToProper varchar(8000))
returns varchar(8000)
as
begin
declare @Position int
select @cStringToProper = stuff(lower(@cStringToProper) , 1 , 1 , upper(left(@cStringToProper , 1)))
, @Position = patindex('%[^a-zA-Z][a-z]%' , @cStringToProper collate Latin1_General_Bin)
while @Position > 0
select @cStringToProper = stuff(@cStringToProper , @Position , 2 , upper(substring(@cStringToProper , @Position , 2)))
, @Position = patindex('%[^a-zA-Z][a-z]%' , @cStringToProper collate Latin1_General_Bin)
select @cStringToProper = replace(@cStringToProper, '_','')
return @cStringToProper
end
The output function itself, which extends Carnotaurus' solution by:
- Correctly outputting newline characters
- Performing some basic tabulation
- Writing out an appropriate a [Table] mapping (as suggested)
- Writing out an appropriate [Column] mapping, including the type name (as suggested)
- Allowing the Entity name to differ from the name of the table
- Fixes the limitation of the Print @Result truncating when you have tables with a large number of columns
CREATE FUNCTION [dbo].[GetEntityObject] (@NameSpace NVARCHAR(MAX), @TableName NVARCHAR(MAX), @EntityName NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) AS BEGIN
DECLARE @result NVARCHAR(MAX)
SET @result = @result + 'using System;' + CHAR(13) + CHAR(13)
IF (@NameSpace IS NOT NULL) BEGIN
SET @result = @result + 'namespace ' + @NameSpace + CHAR(13) + '{' + CHAR(13) END
SET @result = @result + '[Table(name: ' + CHAR(34) + @TableName + CHAR(34) + ')]' + CHAR(13) SET @result = @result + 'public class ' + @EntityName + CHAR(13) + '{' + CHAR(13)
SET @result = @result + '#region Instance Properties' + CHAR(13)
SELECT @result = @result + CHAR(13) + '[Column(name: ' + CHAR(34) + OriginalColumnName + CHAR(34) + ', TypeName = ' + CHAR(34) + DataType
+ CHAR(34) + ')]' + CHAR(13)
+ 'public ' + ColumnType + ' ' + ColumnName + ' { get; set; } ' + CHAR(13) FROM (
SELECT dbo.ProperCase (c.COLUMN_NAME) AS ColumnName
, CASE c.DATA_TYPE
WHEN 'bigint' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Int64?' ELSE 'Int64' END
WHEN 'binary' THEN 'Byte[]'
WHEN 'bit' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Boolean?' ELSE 'Boolean' END
WHEN 'char' THEN 'String'
WHEN 'date' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END
WHEN 'datetime' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END
WHEN 'datetime2' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END
WHEN 'datetimeoffset' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTimeOffset?' ELSE 'DateTimeOffset' END
WHEN 'decimal' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END
WHEN 'float' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Single?' ELSE 'Single' END
WHEN 'image' THEN 'Byte[]'
WHEN 'int' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Int32?' ELSE 'Int32' END
WHEN 'money' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END
WHEN 'nchar' THEN 'String'
WHEN 'ntext' THEN 'String'
WHEN 'numeric' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END
WHEN 'nvarchar' THEN 'String'
WHEN 'real' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Double?' ELSE 'Double' END
WHEN 'smalldatetime' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END
WHEN 'smallint' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Int16?' ELSE 'Int16'END
WHEN 'smallmoney' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END
WHEN 'text' THEN 'String'
WHEN 'time' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'TimeSpan?' ELSE 'TimeSpan' END
WHEN 'timestamp' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END
WHEN 'tinyint' THEN
CASE C.IS_NULLABLE
WHEN 'YES' THEN 'Byte?' ELSE 'Byte' END
WHEN 'uniqueidentifier' THEN 'Guid'
WHEN 'varbinary' THEN 'Byte[]'
WHEN 'varchar' THEN 'String'
ELSE 'Object'
END AS ColumnType
, c.ORDINAL_POSITION , c.COLUMN_NAME as OriginalColumnName ,c.DATA_TYPE as DataType
FROM INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME = @TableName) t ORDER BY t.ORDINAL_POSITION
SET @result = @result + CHAR(13) + '#endregion Instance Properties' + CHAR(13)
SET @result = @result + '}' + CHAR(13)
IF (@TableName IS NOT NULL) BEGIN
SET @result = @result + CHAR(13) + '}' END
return @result END
Usage from within MS SQL Management Studio:
SELECT dbo.GetEntityObject('MyNameSpace', 'MyTableName', 'MyEntityName')
will result in a column value you can copy and paste into Visual Studio.
If it helps anyone, then great!
Java class Generation
declare @TableName varchar(max) = 'Restaurants'
declare @Templete varchar(max) = '
public @ColumnType @ColumnName ; // @ColumnDesc
public @ColumnType get@ColumnName()
{
return this.@ColumnName;
}
public void set@ColumnName(@ColumnType @ColumnName)
{
this.@ColumnName=@ColumnName;
}
'
declare @before varchar(max)='public class @TableName
{'
declare @after varchar(max)='
}'
declare @result varchar(max)
set @before =replace(@before,'@TableName',@TableName)
set @result=@before
select @result = @result
+ replace(replace(replace(replace(replace(@Templete,'@ColumnType',ColumnType) ,'@ColumnName',ColumnName) ,'@ColumnDesc',ColumnDesc),'@ISPK',ISPK),'@max_length',max_length)
from
(
select
column_id,
replace(col.name, ' ', '_') ColumnName,
typ.name as sqltype,
typ.max_length,
is_identity,
pkk.ISPK,
case typ.name
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 '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'
else 'UNKNOWN_' + typ.name
END + CASE WHEN col.is_nullable=1 AND typ.name NOT IN ('binary', 'varbinary', 'image', 'text', 'ntext', 'varchar', 'nvarchar', 'char', 'nchar') THEN '?' ELSE '' END ColumnType,
isnull(colDesc.colDesc,'') AS ColumnDesc
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
left join
(
SELECT c.name AS 'ColumnName', CASE WHEN dd.pk IS NULL THEN 'false' ELSE 'true' END ISPK
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
LEFT JOIN (SELECT K.COLUMN_NAME , C.CONSTRAINT_TYPE as pk
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
ON K.TABLE_NAME = C.TABLE_NAME
AND K.CONSTRAINT_NAME = C.CONSTRAINT_NAME
AND K.CONSTRAINT_CATALOG = C.CONSTRAINT_CATALOG
AND K.CONSTRAINT_SCHEMA = C.CONSTRAINT_SCHEMA
WHERE K.TABLE_NAME = @TableName) as dd
ON dd.COLUMN_NAME = c.name
WHERE t.name = @TableName
) pkk on ColumnName=col.name
OUTER APPLY (
SELECT TOP 1 CAST(value AS NVARCHAR(max)) AS colDesc
FROM
sys.extended_properties
WHERE
major_id = col.object_id
AND
minor_id = COLUMNPROPERTY(major_id, col.name, 'ColumnId')
) colDesc
where object_id = object_id(@TableName)
) t
set @result=@result+@after
select @result
--print @result
Generate from Postgres DB
DO $$
DECLARE v_tabela varchar;
DECLARE v_cursor_colunas record;
DECLARE v_nome_coluna varchar;
DECLARE v_classe VARCHAR;
DECLARE v_tipo VARCHAR;
DECLARE v_schema_name VARCHAR;
BEGIN
v_schema_name := 'my-schema';
v_tabela := 'my-table';
select table_name INTO v_tabela from information_schema.tables where table_schema = v_schema_name
and table_type = 'BASE TABLE'
and table_name = v_tabela;
v_classe := E'\r\n' || 'public class ' || v_tabela || ' {' || E'\r\n';
FOR v_cursor_colunas IN
SELECT column_name as coluna, is_nullable as isnull, data_type as tipo, character_maximum_length as tamanho
FROM information_schema.columns
WHERE table_schema = v_schema_name
AND table_name = v_tabela
LOOP
IF v_cursor_colunas.tipo='character varying' THEN
v_tipo:= 'string';
ELSIF v_cursor_colunas.tipo='character' and v_cursor_colunas.tamanho=1 THEN
v_tipo:= 'char';
ELSIF v_cursor_colunas.tipo='character' and v_cursor_colunas.tamanho<>1 THEN
v_tipo:= 'string';
ELSIF v_cursor_colunas.tipo like 'timestamp%' THEN
v_tipo:= 'DateTime';
IF v_cursor_colunas.isnull='YES' then
v_tipo:= 'DateTime?';
END IF;
ELSIF v_cursor_colunas.tipo='boolean' THEN
v_tipo:= 'bool';
IF v_cursor_colunas.isnull='YES' then
v_tipo:= 'bool?';
END IF;
ELSIF v_cursor_colunas.tipo='integer' THEN
v_tipo:= 'int';
IF v_cursor_colunas.isnull='YES' then
v_tipo:= 'int?';
END IF;
ELSIF v_cursor_colunas.tipo='numeric' THEN
v_tipo:= 'double';
IF v_cursor_colunas.isnull='YES' then
v_tipo:= 'double?';
END IF;
ELSIF v_cursor_colunas.tipo='text' THEN
v_tipo:= 'string';
ELSE
v_tipo:= 'another';
END IF;
v_nome_coluna := v_cursor_colunas.coluna;
v_classe := v_classe || 'public ' || v_tipo || ' ' || v_cursor_colunas.coluna || ' { get; set; }' || E'\r\n';
END LOOP;
v_classe := v_classe || E'\r\n' || '}';
RAISE NOTICE '%' , v_classe;
END $$;
Another Solution for Postgres DB -> C#
- Compatible with NpgSQL
- Added DataAnnotations
SELECT
CASE
WHEN c.is_nullable ='NO' THEN '[Required]'||chr(10)
ELSE '' END
||CASE
WHEN c.data_type = 'character varying' THEN format('[StringLength(%s)]',c.character_maximum_length)||chr(10)
ELSE '' END
||'public '
||CASE
WHEN c.data_type = 'integer' THEN 'int'
WHEN c.data_type = 'boolean' THEN 'bool'
WHEN c.data_type = 'double precision' THEN 'double'
WHEN c.data_type = 'uuid' THEN 'Guid'
WHEN c.data_type = 'character varying' THEN 'string'
WHEN c.data_type = 'timestamp without time zone' THEN 'DateTime'
WHEN c.data_type = 'bigint' THEN 'long'
WHEN c.data_type = 'bytea' THEN 'byte[]'
ELSE 'object' END
||CASE
WHEN c.is_nullable='YES' AND NOT c.data_type = 'character varying' THEN '? '
ELSE ' ' END
||c.column_name||' {get;set;}'
,c.*
FROM information_schema."columns" c
WHERE 1=1
AND c.table_name='YOUR_TABLE_NAME'
I have tried with node.js and it's working fine for me.
- It will create model files for you. You can create multiple model files
Pre requisite: install node.js
Require changes:
- Create index.js file in your workspace
- Add your tables object in "allTable" (Highlighted in screenshot)
- Change folder path (I have given my system path)
- Execute command node index.js
node index.js
Output
const fs = require('fs/promises');
async function convertToDataType(dataArray, fileName) {
let count = 0;
let tempArray = [];
var dataTypeArray = [
{
"key": "bigint",
"value": "long"
},
{
"key": "binary",
"value": "byte[]"
},
{
"key": "bit",
"value": "bool"
},
{
"key": "char",
"value": "string"
},
{
"key": "date",
"value": "DateTime"
},
{
"key": "datetime",
"value": "DateTime"
},
{
"key": "datetime2",
"value": "DateTime"
},
{
"key": "datetimeoffset",
"value": "DateTimeOffset"
},
{
"key": "decimal",
"value": "decimal"
},
{
"key": "float",
"value": "double"
},
{
"key": "image",
"value": "byte[]"
},
{
"key": "int",
"value": "int"
},
{
"key": "money",
"value": "decimal"
},
{
"key": "nchar",
"value": "string"
},
{
"key": "ntext",
"value": "string"
},
{
"key": "numeric",
"value": "decimal"
},
{
"key": "nvarchar",
"value": "string"
},
{
"key": "real",
"value": "float"
},
{
"key": "smalldatetime",
"value": "DateTime"
},
{
"key": "smallint",
"value": "short"
},
{
"key": "smallmoney",
"value": "decimal"
},
{
"key": "text",
"value": "string"
},
{
"key": "time",
"value": "TimeSpan"
},
{
"key": "timestamp",
"value": "long"
},
{
"key": "tinyint",
"value": "byte"
},
{
"key": "uniqueidentifier",
"value": "Guid"
},
{
"key": "varbinary",
"value": "byte[]"
},
{
"key": "varchar",
"value": "string"
}
]
dataArray.map(i => {
let objDataType = '';
objDataType = dataTypeArray.filter(data => data.key == i.split(' ')[1].replace('[', '').replace(']', ''))[0].value;
if (objDataType == '') {
count++;
}
let isNull = i.includes('NULL') && !(i.includes('varchar') || i.includes('bit')) ? '?' : '';
isNull = i.includes('NOT NULL') ? '' : isNull;
const varValue = i.split(' ')[0].replace('[', '').replace(']', '');
if (count != 0) {
console.warn(`\n\n\n ======> Error:: Check data type is missing. Datatype => ${i.split(' ')[1]} Object Name: ${fileName} \n\n\n`);
} else {
tempArray.push(`public ${objDataType}${isNull} ${varValue} { get; set; }`);
}
});
return tempArray;
}
async function convertToModel() {
try {
let allTable = {
EmployeeAllowancesHistory: [
"[EmployeeAllowanceHistoryID] [int] NOT NULL",
"[EmployeeID] [int] NOT NULL",
"[AllowanceID] [int] NOT NULL",
"[DateID] [int] NULL",
"[Amount] [numeric] NOT NULL",
"[Insured] [bit] NULL",
"[ChangeDate] [datetime] NOT NULL",
"[NewAmount] [numeric] NULL"
], Cities: [
"[CityID] [int] NOT NULL",
"[CityCode] [varchar] NOT NULL",
"[CityNameAr] [varchar] NULL",
"[CityNameEn] [varchar] NULL",
"[InKSA] [bit] NOT NULL",
"[HighClass] [bit] NOT NULL",
"[TravelDays] [int] NULL"
], Regions: [
"[RegionID] [int] NOT NULL",
"[RegionCode] [nvarchar] NULL",
"[RegionNameEn] [nvarchar] NULL",
"[RegionNameAr] [nvarchar] NULL",
"[CityID] [int] NULL"
]
}
for (var file in allTable) {
const tableObject = await convertToDataType(allTable[file], file);
let tempContent = "[key]";
tableObject.map(obj => {
tempContent = `${tempContent}
${obj}`
});
const content = `using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace Core.Entities
{
public class ${file}
{
${tempContent}
}
}`
fs.writeFile(`/model_files/${file}.cs`, content);
}
console.log('Created successfully...');
} catch (err) {
console.log(err);
}
}
convertToModel();
![Interactive visualization of a graph in python [closed]](https://www.devze.com/res/2023/04-10/09/92d32fe8c0d22fb96bd6f6e8b7d1f457.gif)
加载中,请稍侯......
精彩评论