c# dapper stored procedure returning null

Question

c# dapper stored procedure returning null

c# dapper mysql stored-procedures

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

JimmyNeutron

Popular Answer

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

fourwhey

View more on Stack Overflow

Bradley Grainger · Accepted Answer · 10/6/2018 5:17:40 AM

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

c# dapper stored procedure returning null

Question

Fastest Dapper Plus Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator