SqlDataAdapter.FillSchema with stored procedure that has temporary table

Question

I'm running into a problem similar to this question here (Display DataType and Size of Column from SQL Server Query Results at Runtime) which has not had a solid solution.

I wonder if anyone has found a solution or a work around for it. I can use SqlDataAdapter.Fill() with no problem, but .FillSchema() will report an error that the temp table I create in the stored procedure doesn't exist.

My goal is to be able to fill the DataTable with data and the schema from the source.

Misbehave

Answer 1

Stored procedures and temp tables generally don't mix well with strongly typed implementations of database objects.

If you change your #temp table to a table @variable that should solve your issue.

An example of how to change from temp table to table variable would be like:

    CREATE TABLE #tempTable (ID INT, Val VARCHAR(50))

to

    DECLARE @tempTable TABLE(ID INT, Val VARCHAR(50))

Appendicectomy

Answer 2

I ran into this same issue with SqlDataAdapter. I was able to resolve the issue by changing my code to use SqlDataReader instead.

If you do not need to modify the schema at all

The following code segments will work fine if you do not need to manipulate the schema of your DataTable object and you also plan to use the data returned from the stored procedure.

var command = new SqlCommand("stored procedure name")
{
    CommandType = CommandType.StoredProcedure 
};

var adapter = new SqlDataAdapter(command);
var dt = new DataTable();

adapter.Fill(dt);

var command = new SqlCommand("stored procedure name")
{
    CommandType = CommandType.StoredProcedure 
};

var reader = command.ExecuteReader();
var dt = new DataTable();

dt.Load(reader);

Both methods will populate a data table with column names and types that correspond to the names and types returned from the stored procedure call. If you need information about the schema, you can access it via the DataTable.Columns property.

If you need to modify the schema

If you need schema information only, or if you need to manipulate the schema in the DataTable object before populating it with data, the following method will work, even if SqlDataAdapter.FillSchema does not.

var command = new SqlCommand("stored procedure name")
{
    CommandType = CommandType.StoredProcedure 
};

var reader = command.ExecuteReader();

var schemaTable = reader.GetSchemaTable();

SqlDataReader.GetSchemaTable() will return a DataTable object with column metadata populated as rows.

You can enumerate the results to build and/or manipulate columns in a DataTable object that will hold the records returned by the SqlDataReader.

In my case, I needed every column to be a string to avoid any formatting "help" from Microsoft Excel when exporting the data to a spreadsheet. As an example, my code looks like this:

var dt = new DataTable();

foreach(DataRow row in schemaTable.Rows)
{
    dt.Columns.Add((string)row["ColumnName"], typeof(string));
}

dt.Load(reader);

Tactual

Answer 3

If you do not need DataSet, but just one DataTable, you can use

DataTable dt= new DataTable();

dt.Load(cmd.ExecuteReader());

For a DataSet, you must at least tell the names of the tables (this means the number) that the SP is returning.

DataSet ds = new DataSet();
SqlDataReader sdr = cmd.ExecuteReader();
ds.Load(sdr, LoadOption.OverwriteChanges,"Table1");

Behind

you did not like that approach? – Behind