CODEWITHSUNDEEP
c#datatableado.net
Turo
Turo

Reputation: 1607

Retrieve column names and types from SQL Server to DataTable C#

What would be the best way of building an empty DataTable based on existing table in SQL server? My current try is this one, which is just retyping this manually, so it is not great, especially for large data sets.

private DataTable createEmptyReadingDataTableReadyToSaveToDb()
{
    dtbl.Columns.Add("ProductId", typeof(string));
    dtbl.Columns.Add("Price", typeof(float));
    dtbl.Columns.Add("Revenue", typeof(float));
    dtbl.Columns.Add("URL", typeof(string));
    //  etc ....
    return dtbl;
}

I read today about schemas, which seem natural for this task. It got me more confused than I thought this would be. Anyways such approach below returns a datatable returning broad set of information about the dataset, but I do not find there accessors to get information about the interesting DataTable (below). Probably I do something wrong.

private static DataTable getReadingTableFromSchema()
{
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalDbConnnectionString"].ConnectionString))
    {
        string sql = "SELECT * FROM [Readings]";
        conn.Open();
        SqlCommand cmd = new SqlCommand(sql, conn);
        SqlDataReader reader = cmd.ExecuteReader();
        DataTable dtbl = reader.GetSchemaTable();
        return dtbl;
    }
}

I could also just try the approach with DataAdapter reading the data, then filling it (basically copying the DataTable) and then deleting all rows to have the table empty - but this would definitely affect the performance. What would be the right solution?

Upvotes: 1

Views: 4541

Answers (2)

Glauco Cucchiar
Glauco Cucchiar

Reputation: 774

you can use DataAdapter and fill DataTable with full schema:

private static DataTable getReadingTableFromSchema()
{
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalDbConnnectionString"].ConnectionString))
    {
        string sql = "SELECT * FROM [Readings]";
        conn.Open();
        SqlCommand cmd = new SqlCommand(sql, conn);
        DbDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dtbl = new DataTable();
        da.FillSchema(dtbl, SchemaType.Source);
        return dtbl;
    }
}

And, I suggest you to use "using" for command and adapter too

Upvotes: 5

Alberto Monteiro
Alberto Monteiro

Reputation: 6219

Using a bit of LINQ

using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalDbConnnectionString"].ConnectionString))
{
    conn.Open();
    using (var reader = new SqlCommand("SELECT * FROM [Readings] WHERE 1 = 0", conn).ExecuteReader())
    {
        var dataColumns = Enumerable.Range(0, reader.FieldCount)
                                    .Select(i => new DataColumn(reader.GetName(i), reader.GetFieldType(i)))
                                    .ToArray();

        var dataTable = new DataTable("Readings");
        dataTable.Columns.AddRange(dataColumns);
    }
}

Upvotes: 3

Related Questions