How to retrieve data from SQL database in C# using Dapper ORM

Open Visual Studio, then click New Project, then select Visual C# on the left, then Windows and then select Windows Forms Application. Name your project "Dapper1" and then click OK

If you don't have Dapper installed in your project, you can do so by using NuGet Package Manager

Right click on your project select Manage NuGet Packages -> Search dapper -> Install

Drag and drop the Label, Combobox, DataGridView controls from Visual Toolbox onto your form designer, then design your form as below.

Ensure that you have the following model classes (Category and Product) to represent your data

Make sure the properties in the Product and Category classes match the columns returned by your SQL query.

public class Category
{
    public int CategoryID { get; set; }
    public string CategoryName { get; set; }
}

// Define a class that matches your stored procedure's result
public class Product
{
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public decimal UnitPrice { get; set; }
    public int UnitsInStock { get; set; }
    public string QuantityPerUnit { get; set; }
}

Add a connection string to the app.config file

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="cn" connectionString="Data Source=.;Initial Catalog=Northwind;User ID=sa;Password=123@qaz;" providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

Now, Create a DataService class, use Dapper to call the stored procedure and retrieve the data.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace Dapper1
{
    public static class DataService
    {
        // This method fetches all categories from the database and returns a list of Category objects.
        public static List<Category> GetAllCategory()
        {
            using(IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString))
            {
                if (db.State == ConnectionState.Closed)
                    db.Open();
                //Execute sql query
                return db.Query<Category>("select CategoryID, CategoryName from Categories").ToList();
            }
        }

        // Method to retrieve data from the stored procedure
        // This method fetches products based on the selected CategoryID and returns a list of Product objects.
        public static List<Product> GetProductByCategoryID(int categoryId)
        {
            using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString))
            {
                // Open the connection
                if (db.State == ConnectionState.Closed)
                    db.Open();
                // Call the stored procedure and map the result to a list of Product objects
                return db.Query<Product>("GetProductByCategoryId", new { CategoryID = categoryId }, commandType:CommandType.StoredProcedure).ToList();
            }
        }
    }
}

In your SQL Server database, you will need to have a stored procedure created.

For example: Create a GetProductByCategoryId stored procedure as below:

CREATE procedure [dbo].[GetProductByCategoryId]
(
	@CategoryId int
)
as
	select *from Products where CategoryID = @CategoryId

Now, inside your Windows Forms application, use DataService class responsible for fetching data from the database

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Dapper1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // Initialize category combo box
            cboCategory.DataSource = DataService.GetAllCategory(); // Assuming this returns a list of Category objects
            cboCategory.DisplayMember = "CategoryName"; // Display the category name
            cboCategory.ValueMember = "CategoryID"; // Use CategoryID as the value when selected
            // Fetch and display products based on the default selected category
            Category obj = cboCategory.SelectedItem as Category; // Get the selected category
            if (obj != null)
                dataGridView.DataSource = DataService.GetProductByCategoryID(obj.CategoryID); // Fetch products by CategoryID
        }

        private void cboCategory_SelectionChangeCommitted(object sender, EventArgs e)
        {
            // Get the selected category from the ComboBox
            Category obj = cboCategory.SelectedItem as Category;
            if (obj != null)
                dataGridView.DataSource = DataService.GetProductByCategoryID(obj.CategoryID); // Get products by the selected CategoryID and bind them to the DataGridView
        }
    }
}

In this example, we load a category into a ComboBox (cboCategory) and display the related products in a DataGridView (dataGridView) based on the selected category.

In the Form1_Load event handler, we're populating the cboCategory with data from DataService.GetAllCategory().

After setting the DataSource, the DisplayMember is set to show the category names (CategoryName), and the ValueMember to store the category ID (CategoryID). The selected category is then used to fetch products for that category.

Next, The SelectionChangeCommitted event is triggered when the user selects a new item from the combo box. In this event, you fetch the products related to the selected category.

VIDEO TUTORIAL