Need to get empty datatable in .net with database

Question

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

Answer 1

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

Answer 2

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

Answer 3

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.

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.

Answer 4

Try: SELECT TOP 0 * FROM [TableName]

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

Answer 5

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.

Answer 6

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