How to retrieve data from SQL database in C# using Dapper ORM
By FoxLearn 11/20/2024 9:47:12 AM 14.2K
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 ofCategory
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 selectedCategoryID
and returns a list ofProduct
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