C # 클래스에서 데이터베이스 테이블을 생성하려면 어떻게해야합니까?

C # 클래스에서 데이터베이스 테이블을 생성하려면 어떻게해야합니까?

---

누구든지 주어진 클래스에 대한 데이터베이스 테이블을 자동 생성하는 방법을 알고 있습니까? 전체 지속성 레이어를 찾고있는 것이 아닙니다. 이미 사용중인 데이터 액세스 솔루션이 있지만 갑자기 많은 수의 클래스에서 많은 정보를 저장해야하므로 실제로 생성 할 필요가 없습니다. 이 모든 테이블을 손으로. 예를 들어 다음과 같은 클래스가 있습니다.

class Foo
{
    private string property1;
    public string Property1
    {
        get { return property1; }
        set { property1 = value; }
    }

    private int property2;
    public int Property2
    {
        get { return property2; }
        set { property2 = value; }
    }
}

다음 SQL을 기대합니다.

CREATE TABLE Foo
(
    Property1 VARCHAR(500),
    Property2 INT
)

복잡한 유형을 처리하는 방법도 궁금합니다. 예를 들어, 이전에 인용 된 클래스에서 다음과 같이 변경 한 경우 :

class Foo
{
    private string property1;
    public string Property1
    {
        get { return property1; }
        set { property1 = value; }
    }

    private System.Management.ManagementObject property2;
    public System.Management.ManagementObject Property2
    {
        get { return property2; }
        set { property2 = value; }
    }
}

어떻게 처리 할 수 ​​있습니까?

각 클래스의 속성을 열거하기 위해 리플렉션을 사용하여 직접 데이터베이스 스크립트를 자동 생성하려고 시도했지만 투박하고 복잡한 데이터 유형으로 인해 당황했습니다.

---

정말 늦었고, 나는 이것에 대해 10 분 정도만 보냈기 때문에 매우 엉성하지만 작동하고 좋은 출발점을 줄 것입니다.

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;

namespace TableGenerator
{
    class Program
    {
        static void Main(string[] args)
        {
            List<TableClass> tables = new List<TableClass>();

            // Pass assembly name via argument
            Assembly a = Assembly.LoadFile(args[0]);

            Type[] types = a.GetTypes();

            // Get Types in the assembly.
            foreach (Type t in types)
            {
                TableClass tc = new TableClass(t);                
                tables.Add(tc);
            }

            // Create SQL for each table
            foreach (TableClass table in tables)
            {
                Console.WriteLine(table.CreateTableScript());
                Console.WriteLine();
            }

            // Total Hacked way to find FK relationships! Too lazy to fix right now
            foreach (TableClass table in tables)
            {
                foreach (KeyValuePair<String, Type> field in table.Fields)
                {
                    foreach (TableClass t2 in tables)
                    {
                        if (field.Value.Name == t2.ClassName)
                        {
                            // We have a FK Relationship!
                            Console.WriteLine("GO");
                            Console.WriteLine("ALTER TABLE " + table.ClassName + " WITH NOCHECK");
                            Console.WriteLine("ADD CONSTRAINT FK_" + field.Key + " FOREIGN KEY (" + field.Key + ") REFERENCES " + t2.ClassName + "(ID)");
                            Console.WriteLine("GO");

                        }
                    }
                }
            }
        }
    }

    public class TableClass
    {
        private List<KeyValuePair<String, Type>> _fieldInfo = new List<KeyValuePair<String, Type>>();
        private string _className = String.Empty;

        private Dictionary<Type, String> dataMapper
        {
            get
            {
                // Add the rest of your CLR Types to SQL Types mapping here
                Dictionary<Type, String> dataMapper = new Dictionary<Type, string>();
                dataMapper.Add(typeof(int), "BIGINT");
                dataMapper.Add(typeof(string), "NVARCHAR(500)");
                dataMapper.Add(typeof(bool), "BIT");
                dataMapper.Add(typeof(DateTime), "DATETIME");
                dataMapper.Add(typeof(float), "FLOAT");
                dataMapper.Add(typeof(decimal), "DECIMAL(18,0)");
                dataMapper.Add(typeof(Guid), "UNIQUEIDENTIFIER");

                return dataMapper;
            }
        }

        public List<KeyValuePair<String, Type>> Fields
        {
            get { return this._fieldInfo; }
            set { this._fieldInfo = value; }
        }

        public string ClassName
        {
            get { return this._className; }
            set { this._className = value; }
        }

        public TableClass(Type t)
        {
            this._className = t.Name;

            foreach (PropertyInfo p in t.GetProperties())
            {
                KeyValuePair<String, Type> field = new KeyValuePair<String, Type>(p.Name, p.PropertyType);

                this.Fields.Add(field);
            }
        }

        public string CreateTableScript()
        {
            System.Text.StringBuilder script = new StringBuilder();

            script.AppendLine("CREATE TABLE " + this.ClassName);
            script.AppendLine("(");
            script.AppendLine("\t ID BIGINT,");
            for (int i = 0; i < this.Fields.Count; i++)
            {
                KeyValuePair<String, Type> field = this.Fields[i];

                if (dataMapper.ContainsKey(field.Value))
                {
                    script.Append("\t " + field.Key + " " + dataMapper[field.Value]);
                }
                else
                {
                    // Complex Type? 
                    script.Append("\t " + field.Key + " BIGINT");
                }

                if (i != this.Fields.Count - 1)
                {
                    script.Append(",");
                }

                script.Append(Environment.NewLine);
            }

            script.AppendLine(")");

            return script.ToString();
        }
    }
}

이 클래스를 어셈블리에 넣어 테스트합니다.

public class FakeDataClass
{
    public int AnInt
    {
        get;
        set;
    }

    public string AString
    {
        get;
        set;
    }

    public float AFloat
    {
        get;
        set;
    }

    public FKClass AFKReference
    {
        get;
        set;
    }
}

public class FKClass
    {
        public int AFKInt
        {
            get;
            set;
        }
    }

그리고 다음 SQL을 생성했습니다.

CREATE TABLE FakeDataClass
(
         ID BIGINT,
         AnInt BIGINT,
         AString NVARCHAR(255),
         AFloat FLOAT,
         AFKReference BIGINT
)


CREATE TABLE FKClass
(
         ID BIGINT,
         AFKInt BIGINT
)


GO
ALTER TABLE FakeDataClass WITH NOCHECK
ADD CONSTRAINT FK_AFKReference FOREIGN KEY (AFKReference) REFERENCES FKClass(ID)
GO

몇 가지 추가 생각 ... [SqlTable]과 같은 속성을 클래스에 추가하는 것이 좋습니다. 그러면 원하는 클래스에 대한 테이블 만 생성됩니다. 또한 이것은 톤, 버그 수정, 최적화 등을 정리할 수 있습니다 (FK Checker는 농담입니다).

---

@ 조나단 홀랜드

와, 이것이 제가 본 것 중에 StackOverflow 포스트에 넣은 것 중 가장 원시적 인 작업이라고 생각합니다. 잘 했어. 그러나 DDL 문을 문자열로 구성하는 대신 SQL 2005에 도입 된 SQL Server 관리 개체 클래스를 반드시 사용해야합니다 .

David Hayden has a post entitled Create Table in SQL Server 2005 Using C# and SQL Server Management Objects (SMO) - Code Generation that walks through how to create a table using SMO. The strongly-typed objects make it a breeze with methods like:

// Create new table, called TestTable
Table newTable = new Table(db, "TestTable");

and

// Create a PK Index for the table
Index index = new Index(newTable, "PK_TestTable");
index.IndexKeyType = IndexKeyType.DriPrimaryKey;

VanOrman, if you're using SQL 2005, definitely make SMO part of your solution.

---

Try out my CreateSchema extension method for objects at http://createschema.codeplex.com/

It returns a string for any object containing CREATE TABLE scripts.

---

I think for complex data types, you should extend them by specifying a ToDB() method which holds their own implementation for creating tables in the DB, and this way it becomes auto-recursive.

---

As of 2016 (I think), you can use Entity Framework 6 Code First to generate SQL schema from poco c# classes or to use Database First to generate c# code from sql. Code First to DB walkthrough

---

For complex types, you can recursively convert each one that you come across into a table of its own and then attempt to manage foreign key relationships.

You may also want to pre-specify which classes will or won't be converted to tables. As for complex data that you want reflected in the database without bloating the schema, you can have one or more tables for miscellaneous types. This example uses as many as 4:

CREATE TABLE MiscTypes /* may have to include standard types as well */
 ( TypeID INT,
   TypeName VARCHAR(...)
 )

CREATE TABLE MiscProperties
 ( PropertyID INT,
   DeclaringTypeID INT, /* FK to MiscTypes */
   PropertyName VARCHAR(...),
   ValueTypeID INT /* FK to MiscTypes */
 )

CREATE TABLE MiscData
 (  ObjectID INT,
    TypeID  INT
 )

CREATE TABLE MiscValues
 ( ObjectID INT, /* FK to MiscData*/
   PropertyID INT,
   Value VARCHAR(...)
 )

---

You can do the opposite, database table to C# classes here: http://pureobjects.com/dbCode.aspx

---

Also... maybe you can use some tool such as Visio (not sure if Visio does this, but I think it does) to reverse engineer your classes into UML and then use the UML to generate the DB Schema... or maybe use a tool such as this http://www.tangiblearchitect.net/visual-studio/

---

I know you're looking for an entire persistence layer, but NHibernate's hbm2ddl task can do this almost as a one-liner.

There is a NAnt task available to call it which may well be of interest.

---

Subsonic is also another option. I often use it to generate entity classes that map to a database. It has a command line utility that lets you specify tables, types, and a host of other useful things

---

Try DaoliteMappingTool for .net. It can help you generate the classes. Download form Here

---

There is a free app, Schematrix which generates classes from DB, check out if does the reverse too :) http://www.schematrix.com/products/schemacoder/download.aspx

ReferenceURL : https://stackoverflow.com/questions/47239/how-can-i-generate-database-tables-from-c-sharp-classes