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
- 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 how do I turn that into a simple MyData POCO?
 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;");
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");
var ds = new DataSet();
var data = ds.Tables["MyData"]?.Rows?.Cast<MyData>();