Everything I try to do it just keeps spiting out null. This is the method that gets the data
public List<Item> getItems(string item_name)
{
using (IDbConnection connection = new MySqlConnection(Helper.CnnVal("dbConn")))
{
connection.Open();
return connection.Query<Item>($"SELECT * FROM ITEMS WHERE itm_name = 'Jaje'").ToList();
}
}
NOTE: The query is hardcoded for testing purposes, usually replaced by a stored procedure Even while hardcoded it still returns null.
The item class
public class Item
{
public int _itm_id { get; set; }
public string _itm_name { get; set; }
public float _itm_price { get; set; }
}
I also have the following code in the .xaml.cs file that runs on a button press
List<Item> items = new List<Item>();
items = da.getItems(txtboxItemNameSearch.Text.Trim());
It always returns _itm_id = 0, _itm_name = null, _itm_price = 0. The connection is open, the insert method works fine. The row is not empty. I copied the select hardcoded statement from a query that worked perfectly.
This is the table
CREATE TABLE `items` (
`itm_id` INT(255) NOT NULL AUTO_INCREMENT,
`itm_name` VARCHAR(255) NOT NULL,
`itm_price` FLOAT(8,2) NOT NULL,
PRIMARY KEY (`itm_id`),
UNIQUE INDEX `itm_name` (`itm_name`)
)
Your database column is named itm_id
, but your C# class property name is _itm_id
. The names need to match for Dapper to assign values from the MySqlDataReader
row to the C# object.
Change your class to the following and the mapping should succeed:
public class Item
{
public int itm_id { get; set; }
public string itm_name { get; set; }
public float itm_price { get; set; }
}
The default handling for the mapper built into Dapper requires that class property names match the column names or be separated by an underscore.
For example, the column ItemName
will match the property ItemName
or Item_Name
and vice versa. There's more complex mapping available for columns that don't follow this scheme, if you want to go that route see this previous answer.
I created a quick example.
SQL
CREATE DATABASE TEST;
GO
USE TEST;
GO
CREATE TABLE ITEM (
[Id] INT IDENTITY(1, 1)
,[Name] VARCHAR(255)
,[Price] MONEY
);
GO
INSERT INTO ITEM ([Name], [Price])
SELECT 'item 1', 10
UNION SELECT 'item 2', 20
UNION SELECT 'item 3', 30
UNION SELECT 'item 4', 40
UNION SELECT 'item 5', 50
UNION SELECT 'item 6', 60
UNION SELECT 'item 7', 70
UNION SELECT 'item 8', 80
UNION SELECT 'item 9', 90
UNION SELECT 'item 10', 100;
GO
C#
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
var items = getItems("Item 1");
foreach (var item in items)
{
Console.WriteLine("Id: {0}", item.id);
Console.WriteLine("Name: {0}", item.name);
Console.WriteLine("Price: {0}", item.price);
Console.ReadLine();
}
}
public static IList<Item> getItems(string name)
{
using (var connection = new SqlConnection("Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=test;Integrated Security=True"))
{
return connection.Query<Item>($"SELECT * FROM ITEM WHERE name = @name",
new { name }).ToList();
}
}
}
public class Item
{
public int id { get; set; }
public string name { get; set; }
public decimal price { get; set; }
}
}
Output