从 C# 数据表创建 SQL Server 表

Question

提问by alchemical

I have a DataTable that I manually created and loaded with data using C#.

我有一个 DataTable，我使用 C# 手动创建并加载了数据。

What would be the most efficient way to create a table in SQL Server 2005 that uses the columns and data in the DataTable?

在 SQL Server 2005 中创建使用 DataTable 中的列和数据的表的最有效方法是什么？

Answer 1

采纳答案by Remus Rusanu

It's a little bit unusual in SQL to create tables out of a client supplied definition of a Datatable object. Tables are carefully crafted entities in SQL, with deploy time placement consideration of choosing the proper disk, with indexing consideration at design time and with all the issues involved in properly modeling a database.

在 SQL 中根据客户端提供的 Datatable 对象定义创建表有点不寻常。表是 SQL 中精心制作的实体，部署时考虑选择合适的磁盘，设计时考虑索引以及正确建模数据库所涉及的所有问题。

Better you'd explain what you're trying to achieve so we understand what advice to give.

最好解释一下您要实现的目标，以便我们了解要提供什么建议。

As a side note, in SQL 2008 there is a very easy way to create a table out of a client defined Datatable: pass the DataTable as a Table value parameter, then issue a SELECT * INTO <tablename> FROM @tvp, this will effectively transfer the definition of the Datatable andits content data into a real table in SQL.

作为一个方面说明，在2008年的SQL有创建一个表出来定义数据表中的客户端的一个非常简单的方法：通过数据表的表值参数，然后发出SELECT * INTO <tablename> FROM @tvp，这将有效地传输数据表的定义和内容数据在 SQL 中转换为真实的表。

Answer 2

回答by Charles Bretana

If you mean from any arbitrary ADO.Net DataTable, I think you'd have to code that as a DDL 'code generation' tool, iterating through the DataTables' columns collection as you construct the "Create Table... " DDL Statement.

如果您的意思是来自任意 ADO.Net DataTable，我认为您必须将其编码为 DDL“代码生成”工具，在构建“创建表...”DDL 语句时遍历 DataTables 的列集合。

Then connect to the desired database and execute the constructed Create Table DDL statement.

然后连接到所需的数据库并执行构造的 Create Table DDL 语句。

Answer 3

回答by Douglas

I would just build a Create Table statement based on the DataTable and send that to Database. You can use SMO (SQL Server Managment objects) as well. Not sure what would be the fastest.

我只会基于 DataTable 构建一个 Create Table 语句并将其发送到数据库。您也可以使用 SMO（SQL Server 管理对象）。不知道什么是最快的。

This is definitely something that could go into a framework level class for reuse.

这绝对是可以进入框架级类以供重用的东西。

The following link contains information (and code sample of a SqlTableCreator) on how to do this: Creating a new table in SQL Server from ADO.NET DataTable. You can find forks of SqlTableCreatorhere, here, and here.

以下链接包含有关SqlTableCreator如何执行此操作的信息（和代码示例）：从 ADO.NET DataTable 在 SQL Server 中创建新表。您可以在SqlTableCreator此处、此处和此处找到叉子。

Hope that helps.

希望有帮助。

Answer 4

回答by Marc Gravell

How efficient do you need? I would probably write my own TSQL (based on the DataTable's columns) to create the table + columns, but to fill it you have a choice; if you have a moderate number of rows, a SqlDataAdaptershould be fine. If you have lotsof data, then SqlBulkCopyaccepts a DataTableand table name...

您需要多高效？我可能会编写自己的 TSQL（基于DataTable的列）来创建表 + 列，但要填充它，您有一个选择；如果您的行数适中，aSqlDataAdapter应该没问题。如果您有大量数据，则SqlBulkCopy接受一个DataTable和表名...

Answer 5

回答by Amin

public static string CreateTABLE(string tableName, DataTable table)
{
    string sqlsc;
    sqlsc = "CREATE TABLE " + tableName + "(";
    for (int i = 0; i < table.Columns.Count; i++)
    {
        sqlsc += "\n [" + table.Columns[i].ColumnName + "] ";
        string columnType = table.Columns[i].DataType.ToString();
        switch (columnType)
        {
            case "System.Int32":
                sqlsc += " int ";
                break;
            case "System.Int64":
                sqlsc += " bigint ";
                break;
            case "System.Int16":
                sqlsc += " smallint";
                break;
            case "System.Byte":
                sqlsc += " tinyint";
                break;
            case "System.Decimal":
                sqlsc += " decimal ";
                break;
            case "System.DateTime":
                sqlsc += " datetime ";
                break;
            case "System.String":
            default:
                sqlsc += string.Format(" nvarchar({0}) ", table.Columns[i].MaxLength == -1 ? "max" : table.Columns[i].MaxLength.ToString());
                break;
        }
        if (table.Columns[i].AutoIncrement)
            sqlsc += " IDENTITY(" + table.Columns[i].AutoIncrementSeed.ToString() + "," + table.Columns[i].AutoIncrementStep.ToString() + ") ";
        if (!table.Columns[i].AllowDBNull)
            sqlsc += " NOT NULL ";
        sqlsc += ",";
    }
    return sqlsc.Substring(0,sqlsc.Length-1) + "\n)";
}

Answer 6

回答by rasputino

Regarding Amin answer I added primary keys to his code.

关于 Amin 的回答，我在他的代码中添加了主键。

public static string CreateTABLEPablo(string connectionString, string tableName, System.Data.DataTable table)
{
    string sqlsc;
    //using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
    using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString))
    {
        connection.Open();
        sqlsc = "CREATE TABLE " + tableName + "(";
        for (int i = 0; i < table.Columns.Count; i++)
        {
            sqlsc += "\n" + table.Columns[i].ColumnName;
            if (table.Columns[i].DataType.ToString().Contains("System.Int32"))
                sqlsc += " int ";
            else if (table.Columns[i].DataType.ToString().Contains("System.DateTime"))
                sqlsc += " datetime ";
            else if (table.Columns[i].DataType.ToString().Contains("System.String"))
                sqlsc += " nvarchar(" + table.Columns[i].MaxLength.ToString() + ") ";
            else if (table.Columns[i].DataType.ToString().Contains("System.Single"))
                sqlsc += " single ";
            else if (table.Columns[i].DataType.ToString().Contains("System.Double"))
                sqlsc += " double ";
            else
                sqlsc += " nvarchar(" + table.Columns[i].MaxLength.ToString() + ") ";



            if (table.Columns[i].AutoIncrement)
                sqlsc += " IDENTITY(" + table.Columns[i].AutoIncrementSeed.ToString() + "," + table.Columns[i].AutoIncrementStep.ToString() + ") ";
            if (!table.Columns[i].AllowDBNull)
                sqlsc += " NOT NULL ";
            sqlsc += ",";
        }

        string pks = "\nCONSTRAINT PK_" + tableName + " PRIMARY KEY (";
        for (int i = 0; i < table.PrimaryKey.Length; i++)
        {
            pks += table.PrimaryKey[i].ColumnName + ",";
        }
        pks = pks.Substring(0, pks.Length - 1) + ")";

        sqlsc += pks;
        connection.Close();

    }
    return sqlsc + ")";
}

Answer 7

回答by LoneBunny

I know this question is rather old, but I just had something very similar that I needed to write. I took what I did and altered the examples provided by both Amin and rasputino and created an example that will output just the SQL. I added a few features and avoided concatenation to help improve upon a process that is inherently a poor performer.

我知道这个问题已经很老了，但我只是有一些非常相似的东西需要写。我采用了我所做的并更改了 Amin 和 rasputino 提供的示例，并创建了一个仅输出 SQL 的示例。我添加了一些功能并避免串联，以帮助改进本质上表现不佳的流程。

/// <summary>
/// Inspects a DataTable and return a SQL string that can be used to CREATE a TABLE in SQL Server.
/// </summary>
/// <param name="table">System.Data.DataTable object to be inspected for building the SQL CREATE TABLE statement.</param>
/// <returns>String of SQL</returns>
public static string GetCreateTableSql(DataTable table)
{
    StringBuilder sql = new StringBuilder();
    StringBuilder alterSql = new StringBuilder();

    sql.AppendFormat("CREATE TABLE [{0}] (", table.TableName);

    for (int i = 0; i < table.Columns.Count; i++)
    {
        bool isNumeric = false;
        bool usesColumnDefault = true;

        sql.AppendFormat("\n\t[{0}]", table.Columns[i].ColumnName);

        switch (table.Columns[i].DataType.ToString().ToUpper())
        {
            case "SYSTEM.INT16":
                sql.Append(" smallint");
                isNumeric = true;
                break;
            case "SYSTEM.INT32":
                sql.Append(" int");
                isNumeric = true;
                break;
            case "SYSTEM.INT64":
                sql.Append(" bigint");
                isNumeric = true;
                break;
            case "SYSTEM.DATETIME":
                sql.Append(" datetime");
                usesColumnDefault = false;
                break;
            case "SYSTEM.STRING":
                sql.AppendFormat(" nvarchar({0})", table.Columns[i].MaxLength);
                break;
            case "SYSTEM.SINGLE":
                sql.Append(" single");
                isNumeric = true;
                break;
            case "SYSTEM.DOUBLE":
                sql.Append(" double");
                isNumeric = true;
                break;
            case "SYSTEM.DECIMAL":
                sql.AppendFormat(" decimal(18, 6)");
                isNumeric = true;
                break;
            default:
                sql.AppendFormat(" nvarchar({0})", table.Columns[i].MaxLength);
                break;
        }

        if (table.Columns[i].AutoIncrement)
        {
            sql.AppendFormat(" IDENTITY({0},{1})", 
                table.Columns[i].AutoIncrementSeed, 
                table.Columns[i].AutoIncrementStep);
        }
        else
        {
            // DataColumns will add a blank DefaultValue for any AutoIncrement column. 
            // We only want to create an ALTER statement for those columns that are not set to AutoIncrement. 
            if (table.Columns[i].DefaultValue != null)
            {
                if (usesColumnDefault)
                {
                    if (isNumeric)
                    {
                        alterSql.AppendFormat("\nALTER TABLE {0} ADD CONSTRAINT [DF_{0}_{1}]  DEFAULT ({2}) FOR [{1}];", 
                            table.TableName, 
                            table.Columns[i].ColumnName, 
                            table.Columns[i].DefaultValue);
                    }
                    else
                    {
                        alterSql.AppendFormat("\nALTER TABLE {0} ADD CONSTRAINT [DF_{0}_{1}]  DEFAULT ('{2}') FOR [{1}];", 
                            table.TableName, 
                            table.Columns[i].ColumnName, 
                            table.Columns[i].DefaultValue);
                    }
                }
                else
                {
                    // Default values on Date columns, e.g., "DateTime.Now" will not translate to SQL.
                    // This inspects the caption for a simple XML string to see if there is a SQL compliant default value, e.g., "GETDATE()".
                    try
                    {
                        System.Xml.XmlDocument xml = new System.Xml.XmlDocument();

                        xml.LoadXml(table.Columns[i].Caption);

                        alterSql.AppendFormat("\nALTER TABLE {0} ADD CONSTRAINT [DF_{0}_{1}]  DEFAULT ({2}) FOR [{1}];", 
                            table.TableName, 
                            table.Columns[i].ColumnName, 
                            xml.GetElementsByTagName("defaultValue")[0].InnerText);
                    }
                    catch
                    {
                        // Handle
                    }
                }
            }
        }

        if (!table.Columns[i].AllowDBNull)
        {
            sql.Append(" NOT NULL");
        }

        sql.Append(",");
    }

    if (table.PrimaryKey.Length > 0)
    {
        StringBuilder primaryKeySql = new StringBuilder();

        primaryKeySql.AppendFormat("\n\tCONSTRAINT PK_{0} PRIMARY KEY (", table.TableName);

        for (int i = 0; i < table.PrimaryKey.Length; i++)
        {
            primaryKeySql.AppendFormat("{0},", table.PrimaryKey[i].ColumnName);
        }

        primaryKeySql.Remove(primaryKeySql.Length - 1, 1);
        primaryKeySql.Append(")");

        sql.Append(primaryKeySql);
    }
    else
    {
        sql.Remove(sql.Length - 1, 1);
    }

    sql.AppendFormat("\n);\n{0}", alterSql.ToString());

    return sql.ToString();
}

Here's a simple test to use this method and get the SQL:

这是使用此方法并获取 SQL 的简单测试：

DataTable table = new DataTable("Users");

table.Columns.Add(new DataColumn()
{
    ColumnName = "UserId",
    DataType = System.Type.GetType("System.Int32"),
    AutoIncrement = true,
    AllowDBNull = false,
    AutoIncrementSeed = 1,
    AutoIncrementStep = 1
});

table.Columns.Add(new DataColumn()
{
    ColumnName = "UserName",
    DataType = System.Type.GetType("System.String"),
    AllowDBNull = true,
    DefaultValue = String.Empty,
    MaxLength = 50
});

table.Columns.Add(new DataColumn()
{
    ColumnName = "LastUpdate",
    DataType = System.Type.GetType("System.DateTime"),
    AllowDBNull = false,
    DefaultValue = DateTime.Now, 
    Caption = "<defaultValue>GETDATE()</defaultValue>"
});

table.PrimaryKey = new DataColumn[] { table.Columns[0] };

string sql = DataHelper.GetCreateTableSql(table);

Console.WriteLine(sql);

And finally, the output:

最后，输出：

CREATE TABLE [Users] (
    [UserId] int IDENTITY(0,1) NOT NULL,
    [UserName] nvarchar(50),
    [LastUpdate] datetime NOT NULL,
    CONSTRAINT PK_Users PRIMARY KEY (UserId)
);

ALTER TABLE Users ADD CONSTRAINT [DF_Users_UserName]  DEFAULT ('') FOR [UserName];
ALTER TABLE Users ADD CONSTRAINT [DF_Users_LastUpdate]  DEFAULT (GETDATE()) FOR[LastUpdate];

I agree with the original answer that states that data management isn't something that should be done haphazardly. It does indeed require a lot of thought to keep the DB running smoothly and allowing for maintainability in the future. But, there are times when a coding solution is necessary and I'm hoping this might help someone out.

我同意最初的答案，即数据管理不应随意进行。确实需要深思熟虑才能保持数据库平稳运行并考虑到将来的可维护性。但是，有时需要编码解决方案，我希望这可以帮助某人。

Answer 8

回答by Adam White

Here is some code that I have written to do just this thing for work. It has been tested and used in a production environment for script generation. It handles DBNulland primary keys properly and does not fail if there are none or only one. It is also more performant than the other suggestions here because it uses StringBuilder, Linq's Aggregateand does not call ToString()repeatedly.

这是我为了工作而编写的一些代码。它已经过测试并在生产环境中用于脚本生成。它DBNull正确处理和主键，如果没有或只有一个，也不会失败。它也比这里的其他建议更高效，因为它使用StringBuilder, LinqAggregate并且不会ToString()重复调用。

Note: If your data is coming from an external source, please make sure your code always sanitizes the input to this method or check the output of this method before blindly executing the generated script against your database.

注意：如果您的数据来自外部来源，请确保您的代码始终清理此方法的输入或检查此方法的输出，然后再针对您的数据库盲目执行生成的脚本。

    /// <summary>
    /// Creates a SQL script that creates a table where the columns matches that of the specified DataTable.
    /// </summary>
    public static string BuildCreateTableScript(DataTable Table)
    {
        if (!Helper.IsValidDatatable(Table, IgnoreZeroRows: true))
            return string.Empty;

        StringBuilder result = new StringBuilder();
        result.AppendFormat("CREATE TABLE [{1}] ({0}   ", Environment.NewLine, Table.TableName);

        bool FirstTime = true;
        foreach (DataColumn column in Table.Columns.OfType<DataColumn>())
        {
            if (FirstTime) FirstTime = false;
            else
                result.Append("   ,");

            result.AppendFormat("[{0}] {1} {2} {3}",
                column.ColumnName, // 0
                GetSQLTypeAsString(column.DataType), // 1
                column.AllowDBNull ? "NULL" : "NOT NULL", // 2
                Environment.NewLine // 3
            );
        }
        result.AppendFormat(") ON [PRIMARY]{0}GO{0}{0}", Environment.NewLine);

        // Build an ALTER TABLE script that adds keys to a table that already exists.
        if (Table.PrimaryKey.Length > 0)
            result.Append(BuildKeysScript(Table));

        return result.ToString();
    }

    /// <summary>
    /// Builds an ALTER TABLE script that adds a primary or composite key to a table that already exists.
    /// </summary>
    private static string BuildKeysScript(DataTable Table)
    {
        // Already checked by public method CreateTable. Un-comment if making the method public
        // if (Helper.IsValidDatatable(Table, IgnoreZeroRows: true)) return string.Empty;
        if (Table.PrimaryKey.Length < 1) return string.Empty;

        StringBuilder result = new StringBuilder();

        if (Table.PrimaryKey.Length == 1)
            result.AppendFormat("ALTER TABLE {1}{0}   ADD PRIMARY KEY ({2}){0}GO{0}{0}", Environment.NewLine, Table.TableName, Table.PrimaryKey[0].ColumnName);
        else
        {
            List<string> compositeKeys = Table.PrimaryKey.OfType<DataColumn>().Select(dc => dc.ColumnName).ToList();
            string keyName = compositeKeys.Aggregate((a,b) => a + b);
            string keys = compositeKeys.Aggregate((a, b) => string.Format("{0}, {1}", a, b));
            result.AppendFormat("ALTER TABLE {1}{0}ADD CONSTRAINT pk_{3} PRIMARY KEY ({2}){0}GO{0}{0}", Environment.NewLine, Table.TableName, keys, keyName);
        }

        return result.ToString();
    }

    /// <summary>
    /// Returns the SQL data type equivalent, as a string for use in SQL script generation methods.
    /// </summary>
    private static string GetSQLTypeAsString(Type DataType)
    {
        switch (DataType.Name)
        {
            case "Boolean": return "[bit]";
            case "Char": return "[char]";
            case "SByte": return "[tinyint]";
            case "Int16": return "[smallint]";
            case "Int32": return "[int]";
            case "Int64": return "[bigint]";
            case "Byte": return "[tinyint] UNSIGNED";
            case "UInt16": return "[smallint] UNSIGNED";
            case "UInt32": return "[int] UNSIGNED";
            case "UInt64": return "[bigint] UNSIGNED";
            case "Single": return "[float]";
            case "Double": return "[double]";
            case "Decimal": return "[decimal]";
            case "DateTime": return "[datetime]";
            case "Guid": return "[uniqueidentifier]";
            case "Object": return "[variant]";
            case "String": return "[nvarchar](250)";
            default: return "[nvarchar](MAX)";
        }
    }

An example of the generated output:

生成的输出示例：

CREATE TABLE [Order] (
   [OrderID] [bigint] UNSIGNED NOT NULL 
   ,[Description] [nvarchar](250) NULL 
   ,[Flag] [bit] NULL 
   ,[Quantity] [int] NULL 
   ,[Price] [decimal] NULL 
   ,[Customer] [nvarchar](MAX) NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE Order
   ADD CONSTRAINT pk_OrderIDCustomer PRIMARY KEY (OrderID, Customer)
GO

Everything is included except for Helper.IsValidDatatable(), but you get the idea. This should at least be replaced by a null check and probably check against zero DataColumns. In fact, if your curious, this code comes from a larger (but still under 1000 lines) open-source C# class library of mine that facilitates moving data from a C# class object to a DataTable, then to SQL scripts and back again. It also contains several helper data access methods for more succinct code. I call it EntityJustworks and it is where the body of the method IsValidDatatable() lives as well (In the Helper.cs class file). You can access the code either via CodePlex (https://entityjustworks.codeplex.com) or view a complete listing of all the other places (GitHub, Code.MSDN, Pastebin, ect) that EntityJustworks can be acquired by visiting its blog post (http://www.csharpprogramming.tips/2015/01/entity-justworks-class-to-sql.html).

除之外的所有内容都包括在内Helper.IsValidDatatable()，但您明白了。这至少应该由空检查替换，并且可能检查零数据列。事实上，如果您好奇的话，这段代码来自我的一个更大（但仍然不到 1000 行）的开源 C# 类库，它有助于将数据从 C# 类对象移动到 DataTable，然后移动到 SQL 脚本，然后再返回。它还包含多个辅助数据访问方法，用于更简洁的代码。我将其称为 EntityJustworks，它也是 IsValidDatatable() 方法的主体所在的位置（在 Helper.cs 类文件中）。您可以通过 CodePlex ( https://entityjustworks.codeplex.com) 或查看可以通过访问其博客文章 ( http://www.csharpprogramming.tips/2015/01/entity-justworks)获得 EntityJustworks 的所有其他地方（GitHub、Code.MSDN、Pastebin 等）的完整列表-class-to-sql.html）。

Answer 9

回答by safeer tp

Here is some code that I have written to do just this thing for work in progres sql.

这是我为在 progres sql 中工作而编写的一些代码。

int count = dataTable1.Columns.Count - 1;
for (int i = 0; i < dataTable1.Columns.Count; i++)
{
   if (i == count)
   {
       name += dataTable1.Columns[i].Caption + " VARCHAR(50)";
   }
   else
   {
       name += dataTable1.Columns[i].Caption + " VARCHAR(50)" + ", ";
   }
}

// Your SQL Command to create a table
string createString = "CREATE TABLE " + tableName + " (" + name + ")";                     
//SqlCommand create = new SqlCommand(createString, connection);
NpgsqlCommand create = new NpgsqlCommand(createString, connection);
connection.Open();
create.ExecuteNonQuery();

从 C# 数据表创建 SQL Server 表

提问by alchemical

采纳答案by Remus Rusanu

回答by Charles Bretana

回答by Douglas

回答by Marc Gravell

回答by Amin

回答by rasputino

回答by LoneBunny

回答by Adam White

回答by safeer tp

相关推荐

最近更新

标签

从 C# 数据表创建 SQL Server 表

提问by alchemical

采纳答案by Remus Rusanu

回答by Charles Bretana

回答by Douglas

回答by Marc Gravell

回答by Amin

回答by rasputino

回答by LoneBunny

回答by Adam White

回答by safeer tp

相关推荐

C# IF 语句多个条件，同一个语句

C# XPATH 查询中的特殊字符

C# Console.WriteLine 为十六进制

C#：前置到文件的开头

相关推荐

最近更新

标签