C# 需要使用数据库表架构在 .net 中获取空数据表

Need to get empty datatable in .net with database table schema

提问by Ronnie Overby

What is the best way to create an Empty DataTable object with the schema of a sql server table?

使用 sql server 表的架构创建空 DataTable 对象的最佳方法是什么？

采纳答案by AdaTheDev

A statement I think is worth mentioning is SET FMTONLY:

我认为值得一提的声明是SET FMTONLY：

SET FMTONLY ON;
SELECT * FROM SomeTable
SET FMTONLY OFF;

No rows are processed or sent to the client because of the request when SET FMTONLY is turned ON.

当 SET FMTONLY 被打开时，由于请求，没有行被处理或发送到客户端。

The reason this can be handy is because you can supply any query/stored procedure and return just the metadata of the resultset.

这很方便的原因是因为您可以提供任何查询/存储过程并仅返回结果集的元数据。

回答by Ed Harper

Assuming that you can connect to the SQL database which contains the table you want to copy at the point it time you want to do this, you could use a conventional resultset to datatable conversion, using

假设您可以连接到包含要复制的表的 SQL 数据库，那么您可以使用传统的结果集到数据表的转换，使用

select * from <tablename> where 1=2

as your source query.

作为您的源查询。

This will return an empty result set with the structure of the source table.

这将返回一个带有源表结构的空结果集。

回答by Shoaib Shaikh

Try: SELECT TOP 0 * FROM [TableName]

尝试： SELECT TOP 0 * FROM [TableName]

and use SQLDataAdapter to fill a DataSet, then get the Table from that DataSet.

并使用 SQLDataAdapter 填充一个数据集，然后从该数据集中获取表。

回答by TGnat

You can always create your own:

您始终可以创建自己的：

        DataTable table = new DataTable("TableName");

        table.Columns.Add(new DataColumn("Col1", typeof(int)));
        table.Columns.Add(new DataColumn("Col2", typeof(int)));
        table.Columns.Add(new DataColumn("Col3", typeof(string)));
        table.Columns.Add(new DataColumn("Col4", typeof(int)));
        table.Columns.Add(new DataColumn("Col5", typeof(string)));

The obvious draw back being that you will have to update your code whenever the database schema changes.

明显的缺点是，每当数据库架构更改时，您都必须更新代码。

回答by Ronnie Overby

Here's what I did:

这是我所做的：

var conn = new SqlConnection("someConnString");
var cmd = new SqlCommand("SET FMTONLY ON; SELECT * FROM MyTable; SET FMTONLY OFF;",conn); 
var dt = new DataTable();
conn.Open();
dt.Load(cmd.ExecuteReader());
conn.Dispose();

Works well. Thanks AdaTheDev.

效果很好。感谢 AdaTheDev。

回答by stentor

this works:

这有效：

Class BlankTableWithSourceTableSchema
    Inherits DataTable
    Public Sub New(ByVal connstr As String, ByVal sourcetable As String)
        Try
            Using connection As SqlServerCe.SqlCeConnection = New SqlServerCe.SqlCeConnection(connstr)
                Dim adapter As SqlServerCe.SqlCeDataAdapter = New SqlServerCe.SqlCeDataAdapter("SELECT * FROM " & sourcetable, connection)
                adapter.TableMappings.Add("Table", "ABlankTable")
                adapter.FillSchema(Me, SchemaType.Mapped)
            End Using
        Catch ex As Exception
        End Try
    End Sub
End Class

回答by Sayak

Class BlankTableWithSourceTableSchema
    Inherits DataTable
    Public Sub New(ByVal connstr As String, ByVal sourcetable As String)
        Try
            Using connection As SqlServerCe.SqlCeConnection = New SqlServerCe.SqlCeConnection(connstr)
                Dim adapter As SqlServerCe.SqlCeDataAdapter = New SqlServerCe.SqlCeDataAdapter("SELECT * FROM " & sourcetable, connection)
                adapter.TableMappings.Add("Table", "ABlankTable")
                adapter.FillSchema(Me, SchemaType.Mapped)
            End Using
        Catch ex As Exception
        End Try
    End Sub
End Class

回答by Richard R

All of these solutions are correct, but if you want a pure code solution that is streamlined for this scenario.

所有这些解决方案都是正确的，但是如果您想要针对此场景进行简化的纯代码解决方案。

No Data is returned in this solution since CommandBehavior.SchemaOnly is specified on the ExecuteReader function(Command Behavior Documentation)

由于在 ExecuteReader 函数上指定了 CommandBehavior.SchemaOnly（命令行为文档），因此此解决方案中未返回任何数据

The CommandBehavior.SchemaOnly solution will add the SET FMTONLY ON; sql before the query is executed for you so, it keeps your code clean.

CommandBehavior.SchemaOnly 解决方案将添加 SET FMTONLY ON; sql 在为您执行查询之前，它使您的代码保持干净。

public static DataTable GetDataTableSchemaFromTable(string tableName, SqlConnection sqlConn, SqlTransaction transaction)
{
    DataTable dtResult = new DataTable();

    using (SqlCommand command = sqlConn.CreateCommand())
    {
        command.CommandText = String.Format("SELECT TOP 1 * FROM {0}", tableName);
        command.CommandType = CommandType.Text;
        if (transaction != null)
        {
            command.Transaction = transaction;
        }

        SqlDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly);

        dtResult.Load(reader);

    }

    return dtResult;
}

回答by askids

I know that this is an old question and specific to SQL Server. But if you are looking for generic solution that will work across different databases, use Richard's solution, but modify it to use "SELECT * FROM {0} WHERE 1=0"and change the types to use generic ADO.Net types IDataReader, IDbCommand etc.

我知道这是一个老问题，特定于 SQL Server。但是，如果您正在寻找适用于不同数据库的通用解决方案，请使用 Richard 的解决方案，但修改它以使用"SELECT * FROM {0} WHERE 1=0"和更改类型以使用通用 ADO.Net 类型 IDataReader、IDbCommand 等。

Most modern relational databases are intelligent enough to identify the 1=0 condition and will not run it like a regular tablescan query. I have tried this on SQL Server, Oracle and DB2 with tables have few 100 million records also. All do return empty result back in matter of few milliseconds.

大多数现代关系数据库都足够智能，可以识别 1=0 条件，并且不会像常规 tablescan 查询那样运行它。我已经在 SQL Server、Oracle 和 DB2 上尝试过这个，表也只有几亿条记录。所有都在几毫秒内返回空结果。

回答by bob number 2

Here's what I did, which provides a blank DataTable ready to be used:

这是我所做的，它提供了一个准备使用的空白数据表：

SqlConnection _sqlConnection = new SqlConnection ();
_sqlConnection.ConnectionString = @"Data Source=<SQL_Server/Instance>; Initial Catalog=<database_name>; Integrated Security=False; User ID=<user_id>;Password=<passowrd>";
_sqlConnection.Open ();
SqlCommand _sqlCommand = new SqlCommand ( "select * from DatabaseName.dbo.viewName", _sqlConnection ); 
_dataSet = new DataSet ();
_sqlDataAdapter = new SqlDataAdapter ( _sqlCommand );
_sqlDataAdapter.Fill ( _dataSet );
_schemaTable = new DataTable ();
_sqlDataAdapter.FillSchema ( _schemaTable, SchemaType.Source );
dataGridView.DataSource = _schemaTable;
_sqlConnection.Close ();