I heard that people use entity framework to generate model related classes from database. Suppose if I do not want to use entity framework as data access layer rather I want to use MS data application block so then how can I auto generate models classes in MVC from database instead of writing model related classes manually. Please guide me with all the possible ways. Thanks
Asked
Active
Viewed 2.9k times
4
-
You should look up Linq To SQL and how to use the drag and drop UI that goes with it. Here's a tutorial: http://www.codeproject.com/Articles/22000/LINQ-to-SQL – Mathew Thompson May 22 '13 at 08:02
-
If you can generate xsd files from your database then you can use xsd.exe to generate classes from xsd files. – Denise Skidmore May 22 '13 at 13:28
-
I think you're looking for this tool https://visualstudiogallery.msdn.microsoft.com/72a60b14-1581-4b9b-89f2-846072eff19d – tsohtan May 24 '16 at 01:49
3 Answers
9
I know this is an old question but for a quick and easy class I use this in SMS. It adds the 'required' and 'string-length' data annotations I routinely use.
DECLARE @TableName VARCHAR(MAX) = 'tablename' -- Replace 'tablename' with your table name
DECLARE @NameSpace VARCHAR(MAX) = 'namespace' -- Replace 'namespace' with your class namespace
DECLARE @TableSchema VARCHAR(MAX) = 'dbo' -- Replace 'dbo' with your schema name
DECLARE @result varchar(max) = ''
SET @result = @result + 'using System;' + CHAR(13)
SET @result = @result + 'using System.ComponentModel.DataAnnotations;' + CHAR(13) + CHAR(13)
IF (@TableSchema IS NOT NULL)
BEGIN
SET @result = @result + 'namespace ' + @NameSpace + 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)
+ ' [Display(Name = "' + ColumnName + '")] ' + CHAR(13)
+ CASE bRequired WHEN 'NO'
THEN
CASE WHEN Len(MaxLen) > 0 THEN ' [Required, StringLength(' + MaxLen + ')]' + CHAR(13) ELSE ' [Required] ' + CHAR(13) END
ELSE
CASE WHEN Len(MaxLen) > 0 THEN ' [StringLength(' + MaxLen + ')]' + CHAR(13) ELSE '' END
END
+ ' 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 '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.IS_NULLABLE AS bRequired,
CASE c.DATA_TYPE
WHEN 'char' THEN CONVERT(varchar(10),c.CHARACTER_MAXIMUM_LENGTH)
WHEN 'nchar' THEN CONVERT(varchar(10),c.CHARACTER_MAXIMUM_LENGTH)
WHEN 'nvarchar' THEN CONVERT(varchar(10),c.CHARACTER_MAXIMUM_LENGTH)
WHEN 'varchar' THEN CONVERT(varchar(10),c.CHARACTER_MAXIMUM_LENGTH)
ELSE ''
END AS MaxLen,
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
thekonger
- 169
- 1
- 6
6
Good way to do it is to use ADO.NET Entity Data Model: In Visual Studio right click on your project -> "Add" -> "New Item" -> "Data" -> "ADO.NET Entity Data Model" -> "Generate from database" -> choose or create connection -> choose tables -> expand created *.tt file group -> You get it :-)
Alexander Imra
- 832
- 5
- 11
-
i said that i will not use entity framework rather i want to use MSDAAB instead then why you are saying to use EF? – Thomas May 22 '13 at 10:23
-
Sorry if it doesn't work for you. But for one-use it's the best way. After classes generation you don't need to use EF. – Alexander Imra May 22 '13 at 10:31
-
I've done this. But my class doesn't have a constructor. In your case, did it have a constructor? – msysmilu Feb 26 '16 at 12:00
0
Run the below script in SqlServerManagementStudio, For the required table you will get the result...
declare @TableName sysname = 'tblQuotations'
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
-
1This answer seems to be a near duplicate of several answers from [Generate class from database table](https://stackoverflow.com/q/5873170/3744182) including [this one](https://stackoverflow.com/a/5873231/3744182) by [Alex Aza](https://stackoverflow.com/users/732945/alex-aza) and [this one](https://stackoverflow.com/a/43357818/3744182) from [Sasha Bond](https://stackoverflow.com/users/2464815/sasha-bond), though there are differences in the `case typ.name` statement. – dbc Mar 23 '21 at 04:02
-
Comparing those answers with yours, your code does `when 'float' then 'float'` and `when 'nchar' then 'string'` which Sasha Bond's answer also does. Might you please [edit] your question to explain a little why you chose that version to model your answer (if you did) and how this code works? – dbc Mar 23 '21 at 04:03
-
And finally, when you copy and modify posts written by other stack overflow contributors, you must credit them as per https://stackoverflow.com/help/referencing: 1) Provide a link to the original page or answer. 2) Quote only the relevant portion. 3) Provide the name of the original author. Please [edit] your question to add the required attribution(s) if any. – dbc Mar 23 '21 at 04:05