Générer de la classe à partir de la table de base de données

Je ne pouvais pas Alex en réponse à des travaux sur Sql Server 2008 R2. Donc, je l'ai réécrit en utilisant les mêmes principes de base. Il permet maintenant de schémas et de plusieurs corrections ont été apportées pour la colonne des mappages de propriétés (y compris la cartographie nullable date de types nullables C# types de valeur). Voici le 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

Il produit C# comme suit:

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

Il peut être une bonne idée d'utiliser EF, Linq to Sql, ou même de l'Échafaudage; cependant, il ya des moments où un morceau de codage comme c'est pratique. Franchement, je n'aime pas utiliser EF propriétés de navigation où le code qu'il génère faite de 19 200 séparer les appels de base de données pour remplir un 1000 ligne de la grille. Cela pourrait avoir été réalisé dans une seule base de données téléphonique. Néanmoins, il pourrait être simplement que votre architecte technique ne veut pas que vous utiliser EF et la comme. Donc, vous avez à revenir à un code comme celui-ci... d'ailleurs, il peut aussi être une idée pour décorer chacune des propriétés avec des attributs pour DataAnnotations, etc., mais je vais garder cette strictement POCO.

MODIFIER
Fixe pour TimeStamp et Guid?

Un peu en retard mais j'ai créé un outil en ligne pour aider à créer un C# (ou autre) des objets à partir de SQL résultat, Table SQL et SQL SP.

sql2object.com

Cela peut vraiment sûr que vous ayez à taper tous vos propriétés et types.

Si les types ne sont pas comptabilisés à la valeur par défaut est sélectionné.

Oui ce sont de grands si votre la simple utilisation d'un ORM comme Dapper.

Si votre aide .Net, vous pouvez générer un fichier XSD au moment de l'exécution avec un ensemble de données à l'aide de la méthode WriteXmlSchema. http://msdn.microsoft.com/en-us/library/xt7k72x8(v=vs. 110).aspx

Comme ceci:

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();

À partir de là, vous pouvez utiliser xsd.exe pour créer une classe qui est la sérialisation XML par le Développeur Invite de Commande.
http://msdn.microsoft.com/en-us/library/x6c1kb0s(v=vs. 110).aspx

comme ceci:

xsd C:\Person.xsd /classes /language:CS

J'ai essayé d'utiliser les suggestions ci-dessus et dans le processus amélioré les solutions dans ce fil.

Disons que vous utilisez une classe de base (ObservableObject dans ce cas) qui implémente l'Événement PropertyChanged, vous faites quelque chose comme cela. Je vais probablement écrire un article de blog, un jour, dans mon blog
sqljana.wordpress.com

S'il vous plaît ne remplacez les valeurs pour les trois premières 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

La classe de base est basé sur Josh Smith article ici
De http://joshsmithonwpf.wordpress.com/2007/08/29/a-base-class-which-implements-inotifypropertychanged/

Je n'ai renommer la classe d'être appelé ObservableObject et a également bénéficié d'une c# 5 fonctionnalité à l'aide de la CallerMemberName attribut

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

Voici la partie que vous les gars, vous allez aimer un peu plus. J'ai construit un script Powershell pour générer de toutes les tables dans une base de données SQL. Il est basé sur une Powershell gourou nommé Tchad Miller Invoke-SQLCmd2 applet de commande qui peut être téléchargé à partir d'ici:
http://gallery.technet.microsoft.com/ScriptCenter/7985b7ef-ed89-4dfd-b02a-433cc4e30894/

Une fois que vous avez cette applet de commande, le script Powershell pour générer pour toutes les tables devient simple (faire remplacer les variables par vos propres valeurs).

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

créer la PROCÉDURE pour créer un code personnalisé à l'aide du modèle de

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

---

maintenant créer un code personnalisé

par exemple de classe c#

exec [createCode] @TableName='book',@templet =' 
public @ColumnType @ColumnName   { get; set; }  //@ColumnDesc  '

de sortie est

public class  book  
{ 
public long ID   { get; set; }  //   
public String Title   { get; set; }  //Book Title  
}

pour 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  ='
}'

de sortie est

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

pour la classe java

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

de sortie est

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

pour android sugarOrm modèle

exec [createCode] @TableName='book'
, @befor  ='@Table(name = "@TableName")
public class @TableName
{',
@templet  =' 
@Column(name = "@ColumnName")
public @ColumnType @ColumnName ;//@ColumnDesc  
' ,
@after  ='
}'

de sortie est

@Table(name = "book")
public class book
{ 
@Column(name = "ID")
public long ID ;//  
@Column(name = "Title")
public String Title ;//Book Title  
}

Commercial, mais permet d'économiser beaucoup de temps pour les développeurs qui n'utilisent pas Entity Framework.

https://codverter.com/src/sqltoclass

Cet outil en ligne génère une classe à partir de la table SQL. La classe est basée sur la CREATE TABLE script dans MS-SQL, ORACLE ou MYSQL bases de données, à une classe en C# et d'autres langages de programmation.

La conversion est basée sur le 'CREATE TABLE' script et effectue les opérations suivantes:

• Crée une classe nommée comme la table
• Crée des propriétés de la classe pour chacune des colonnes de la table avec les mêmes noms et les types de données équivalent
• Crée un constructeur avec toutes les table les champs de la marque "Ajouter Constructeur" sélection
• Vous pouvez choisir de générer Properties, Full Properties ou Fields

Je suis confus quant à ce que vous voulez, mais voici les options générales lors de la conception de ce que vous souhaitez concevoir.

1. L'aide de l'ORM dans votre version de Visual Studio.
2. Écrire un vous-même, semblable à votre exemple de code. Comme d'habitude, un tutoriel est votre meilleur ami si vous ne savez pas comment.
3. Utiliser un autre ORM comme NHibernate.

Dans l'appréciation de Alex de la solution et Guilherme pour demander j'ai fait ceci pour MySQL pour générer des classes C#

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!

légèrement modifié à partir du haut de réponse:

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

qui rend la sortie nécessaires pour la pleine LINQ en C# déclaration

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

La façon la plus simple est EF, désosser. http://msdn.microsoft.com/en-US/data/jj593170

Un petit plus pour les solutions avant:
object_id(@TableName) ne fonctionne que si vous êtes dans le schéma par défaut.

(Select id from sysobjects where name = @TableName)

fonctionne dans n'importe quel schéma fourni @tableName est unique.