c# dapper stored procedure returning null

Question

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`)
)

fourwhey · Answer 1 · 2018-10-06T02:21:33.500

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

score 0 · Accepted Answer · answered Oct 06 '18 at 05:17

0

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; }
}

answered Oct 06 '18 at 05:17

Bradley Grainger

27,458
4
91
108

Sorry for the late response. I wanted to try that since it makes perfect sense. But I went to bed. The problem with that is that my variables in the stored procedure are named _itm_name and _itm_price. And in the insert method https://pastebin.com/iPxCXJ17 I have to use those sql variables to equal them with the user input. Will I have to rename all my variables to something like just itm_name ? – JimmyNeutron Oct 06 '18 at 10:41
Update: I've adjusted everything. Works like a charm! Thanks – JimmyNeutron Oct 06 '18 at 11:02

c# dapper stored procedure returning null

2 Answers2