tipos - sql server tinyint c#

Generar clase desde la tabla de la base de datos

Question

¿Cómo puedo generar una clase desde una tabla en un servidor SQL?

No estoy hablando de usar algunos ORM. Solo necesito crear las entidades (clase simple). Algo como:

public class Person
{
public string Name { get;set; }
public string Phone { get;set; }
}

Answer 1

Comercial, pero CodeSmith Generator lo hace: http://www.codesmithtools.com/product/generator

Answer 2

Establezca @TableName como el nombre de su tabla.

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 ''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
order by ColumnId
set @Result = @Result + ''
}''
print @Result

Answer 3

Estoy confundido en cuanto a lo que quieres de esto, pero aquí están las opciones generales al diseñar lo que deseas diseñar.

1. Usando el ORM incorporado en su versión de Visual Studio.
2. Escriba uno usted mismo, similar a su ejemplo de código. Como de costumbre, un tutorial es tu mejor amigo si no estás seguro de cómo hacerlo.
3. Use un ORM alternativo como NHibernate .

Answer 4

No pude obtener la respuesta de Alex para trabajar en Sql Server 2008 R2. Entonces, lo reescribí usando los mismos principios básicos. Ahora permite esquemas y se han realizado varias correcciones para asignaciones de propiedades de columnas (incluido el mapeo de tipos de fechas anulables a tipos de valores C # anulables). Aquí está el 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

Produce C # como el siguiente:

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

Puede ser una idea usar EF, Linq a Sql, o incluso Scaffolding; Sin embargo, hay momentos en que una pieza de codificación como esta es útil. Francamente, no me gusta utilizar las propiedades de navegación de EF donde el código que genera hace 19.200 llamadas de bases de datos separadas para llenar una cuadrícula de 1000 filas. Esto podría haberse logrado en una única llamada a la base de datos. Sin embargo, podría ser que su arquitecto técnico no quiera que use EF y similares. Por lo tanto, debe volver al código como este ... Incidentalmente, también puede ser una idea decorar cada una de las propiedades con atributos para Anotaciones de datos, etc., pero estoy manteniendo este POCO estrictamente.

EDIT ¿ Reparado para TimeStamp y Guid?

Answer 5

Para imprimir las propiedades NULLABLE WITH COMMENTS (Summary), use esto.
Es una ligera modificación de la primera respuesta

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

Answer 6

Para imprimir las propiedades NULLABLE, use esto.
Agrega una ligera modificación al guión de Alex Aza para el bloque de instrucciones CASE .

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

Answer 7

Sí, estos son geniales si estás usando un ORM simple como Dapper.

Si usa .Net, puede generar un archivo XSD en tiempo de ejecución con cualquier DataSet utilizando el método WriteXmlSchema. http://msdn.microsoft.com/en-us/library/xt7k72x8(v=vs.110).aspx

Me gusta esto:

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

Desde allí, puede usar xsd.exe para crear una clase que sea XML serializable desde el símbolo del sistema del desarrollador. http://msdn.microsoft.com/en-us/library/x6c1kb0s(v=vs.110).aspx

Me gusta esto:

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

Answer 8

Si tiene acceso a SQL Server 2016, puede usar la opción FOR JSON (con INCLUDE_NULL_VALUES) para obtener salida JSON de una instrucción select. Copie la salida, luego en Visual Studio, pegue especial -> pegue JSON como clase.

Una especie de solución de presupuesto, pero podría ahorrar algo de tiempo.

Answer 9

Traté de usar las sugerencias anteriores y en el proceso mejoró las soluciones en este hilo.

Digamos que usas una clase base (ObservableObject en este caso) que implementa el evento PropertyChanged, harías algo como esto. Probablemente escribiré una publicación de blog un día en mi blog sqljana.wordpress.com

Sustituya los valores de las tres primeras 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 clase base se basa en el artículo de Josh Smith aquí. http://joshsmithonwpf.wordpress.com/2007/08/29/a-base-class-which-implements-inotifypropertychanged/

Cambié el nombre de la clase para que se llamara ObservableObject y también aproveché la función de CA # 5 utilizando el atributo CallerMemberName.

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

Aquí está la parte que a ustedes les va a gustar un poco más. Creé un script Powershell para generar para todas las tablas en una base de datos SQL. Está basado en un gurú de Powershell llamado Cmdlet Invoke-SQLCmd2 de Chad Miller que se puede descargar desde aquí: http://gallery.technet.microsoft.com/ScriptCenter/7985b7ef-ed89-4dfd-b02a-433cc4e30894/

Una vez que tenga ese cmdlet, la secuencia de comandos de Powershell para generar para todas las tablas se vuelve simple (sustituya las variables por sus valores específicos).

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

Answer 10

Un poco tarde, pero he creado una herramienta web para ayudar a crear objetos C # (u otros) a partir del resultado de SQL, SQL Table y SQL SP.

sql2object.com

Esto realmente puede salvarte al tener que escribir todas tus propiedades y tipos.

Si los tipos no son reconocidos, se seleccionará el predeterminado.

Answer 11

Versión VB

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

Answer 12

Visual Studio Magazine publicó esto:

Generación de clases POCO de .NET para resultados de consulta SQL

Tiene un proyecto descargable que puedes construir, darle tu información de SQL y generará la clase para ti.

Ahora si esa herramienta acaba de crear los comandos SQL para SELECT, INSERT y UPDATE ....

Answer 13

ligeramente modificado desde la respuesta superior:

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

Answer 14

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.

Answer 15

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.

Answer 16

Here is an article I have written for Generating the Entity Class from the a SQL Table in MSSQL

Create Entity Class From SQL Table

Answer 17

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

Answer 18

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!

Answer 19

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:

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

2. 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!

Answer 20

The simplest way is EF, Reverse Engineer. http://msdn.microsoft.com/en-US/data/jj593170

Answer 21

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

Por supuesto, no es un código a prueba de balas, y se puede mejorar. Solo quería contribuir a esta excelente solución

Answer 22

You just did, as long as your table contains two columns and is called something like ''tblPeople''.

You can always write your own SQL wrappers. I actually prefer to do it that way, I HATE generated code, in any fashion.

Maybe create a DAL class, and have a method called GetPerson(int id) , that queries the database for that person, and then creates your Person object from the result set.

Answer 23

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
}