Click here to Skip to main content
15,867,594 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello everyone. I am trying to use MySql with data mapping to obtain a record mapped to my liking. But I seem to be thwarted at every turn.

I have included, in the "What have you tried", a simplified version of what I'm trying to do.

When I run this code I get the following results:
- It does retrieve a record from the database.
- The debugger show that ds contains a table named testtable.
- That table contains one row with two column names: column_a and column_b.
- Those columns contain the correct data from the database.
- My attempt to convert the data into an object just returns null.

It would seem that my attempt to create a table mapping is way off base, because if I remove the three lines starting with var dtm=, I get the exact same results.

How would I get it to return ds with a table named MyData with columns MyDataA and MyDataB? And how do I turn that into a simple MyData POCO?

[edit] I corrected one issue: I was adding the parameter value to cmd.Parameters. I should have been adding it to da.SelectCommand.Parameters. As it was, I selecting all rows in the table. It only seemed to work because I only had 1 entry in db. I found that out from here.
That entry also explains that I should not be using AddWithValue(), but I will correct that after the code is functioning.
I have commented out the references to cmd in the example.

What I have tried:

C#
using var connection = new MySqlConnection("Server=localhost;Database=testing;Uid=dev;Pwd=notmyrealpassword;AllowUserVariables=True;");
connection.Open();

var sql = "SELECT column_a, column_b FROM testtable WHERE column_a=@p1;";

// var cmd = new MySqlCommand(sql, connection);
// cmd.Parameters.AddWithValue("@p1", "entry 1");

var da = new MySqlDataAdapter(sql, connection);
// da.SelectCommand = cmd;
da.SelectCommand.Parameters.AddWithValue("@p1", "entry 1");

var dtm = da.TableMappings.Add("MyData", "testtable");
dtm.ColumnMappings.Add("MyDataA", "column_a");
dtm.ColumnMappings.Add("MyDataB", "column_b");

var ds = new DataSet();
da.Fill(ds, "MyData");

connection.Close();
var data = ds.Tables["MyData"]?.Rows?.Cast<MyData>();
Posted
Updated 22-Dec-20 12:13pm
v5

"Cast" does not "create" new types. You "create" the POCO and load it from the data table.

How do I convert a datatable into a POCO object in Asp.Net MVC? - Stack Overflow[^]
 
Share this answer
 
Comments
C Pottinger 22-Dec-20 11:28am    
Thank you Gerry. I'll do that once the other issue is resolved, namely, why does the code above not map the retrieved data to a table in ds called MyData and columns called MyDataA and MyDataB?
Or am I missing some fundamental concept about what TableMapping is for?
I got it.
In addition to adding the parameters to the adapter's SelectCommand, I also needed to reverse the names of the tables in the TableMappings.Add statement.
Now I see a table name named MyData with columns name MyDataA and MyDataB.

I can now add the changes suggested by Gerry Schmitz.

The code should look like:
C#
using var connection = new MySqlConnection("Server=localhost;Database=testing;Uid=dev;Pwd=notmyrealpassword;AllowUserVariables=True;");
connection.Open();

var sql = "SELECT column_a, column_b FROM testtable WHERE column_a=@p1;";

var da = new MySqlDataAdapter(sql, connection);
da.SelectCommand.Parameters.AddWithValue("@p1", "entry 1");

var dtm = da.TableMappings.Add("testtable", "MyData");
dtm.ColumnMappings.Add("MyDataA", "column_a");
dtm.ColumnMappings.Add("MyDataB", "column_b");

var ds = new DataSet();
da.Fill(ds, "MyData");

connection.Close();
 
Share this answer
 
v3
Comments
C Pottinger 23-Dec-20 7:54am    
Nope - this did not do it. I was sure it did, but upon further inspection it does set the ds.Tables[0] TableName = "MyData". but looking for ds.Tables["MyData"].Rows[0]["MyDataA"] fails while ds.Tables["MyData"].Rows[0]["column_a"] returns "entry_1".
Aaaaaaaarrrrrrrrggggggggghhhhhhhhh !!!!!!!!
C Pottinger 23-Dec-20 10:08am    
Ah - answered by kieran501 on StackOverflow (https://stackoverflow.com/questions/65425079/how-do-i-get-mysqldataadapter-to-set-the-table-name-and-columns-using-tablemappi/65425599#65425599)
I just needed to reverse the order the parameters in the ColumnMappings calls (which I did try), AND fill "testtable" instead of "MyData" (which I hadn't).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900