C# SQL Data Adapter Fill on existing typed Dataset

Question

I have an option to choose between local based data storing (xml file) or SQL Server based. I already created a long time ago a typed dataset for my application to save data local in the xml file.

Now, I have a bool that changes between Server based version and local version. If true my application get the data from the SQL Server.

I'm not sure but It seems that Sql Adapter's Fill Method can't fill the Data in my existing schema

SqlCommand cmd = new SqlCommand("Select * FROM dbo.Categories WHERE CatUserId = 1", _connection);
                cmd.CommandType = CommandType.Text;

                _sqlAdapter = new SqlDataAdapter(cmd);
                _sqlAdapter.TableMappings.Add("Categories", "dbo.Categories");
                _sqlAdapter.Fill(Program.Dataset);

This should fill my data from dbo.Categories to Categories (in my local, typed dataset). but it doesn't. It creates a new table with the name "Table". It looks like it can't handle the existing schema.

I can't figure it out. Where is the problem?

btw. of course the database request I do isn't very useful that way. It's just a simplified version for testing...

Answer 1

Try _sqlAdapter.TableMappings.Add("Table", "Categories");, but as i remember you will have to add column mapping also. btw, you can try to create typed dataadapter, it is useless thing, but you can take mapping from there.

Answer 2

The Fill overload you are using, passing in a DataSet will always create a NEW DataTable in the supplied DataSet with name "Table" (MSDN).

So, I think you either need to change your TableMapping to:

_sqlAdapter.TableMappings.Add("Table", "Categories");

(MSDN) assuming your DataTable name is "Categories".

Or, don't use TableMappings and just supply the second argument - the name of the DataTable in that DataSet you want to populate (MSDN). The approach I usually use is actually to pass the DataTable itself that you want to populate, instead of the DataSet (MSDN).