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