This post will show you How to access data to SQL Database using Dapper ORM in C#
To access your sql database, you can use Dapper ORM as a simple way. As you know, Dapper ORM is an open source that's help you insert update delete data to your database.
Step 1: Click New Project, then select Visual C# on the left, then Windows and then select Windows Forms Application. Name your project "DataAccessDemo" and then click OK

Step 2: Right click on your project select Manage NuGet Packages -> Search material skin, dapper -> Install

Step 3: Design your form as below
First, you need to change inheritance from windows form to the MaterialForm
public partial class Form1 : Form
to
public partial class Form1 : MaterialSkin.Controls.MaterialForm

Step 4: To retrieve data from sql database, you should create a DataAccess class to retrive data from SQL Server
public static class DataAccess
{
public static List<Product> GetProducts()
{
using (IDbConnection db = new SqlConnection(Helper.ConnectionString))
{
if (db.State == ConnectionState.Closed)
db.Open();
return db.Query<Product>("select ProductID, ProductName, UnitPrice, UnitsInStock from Products", commandType: CommandType.Text).ToList();
}
}
}
You can also create a helper class to help you get the connection string from your App.config file by adding a ConnectionString property to get ConnectionString value from the App.config file
public static class Helper
{
public static string ConnectionString => ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
}
To map data return from SQL Server you need to create a Product class
public class Product
{
public int ProductID { get; set; }
public string ProductName { get; set; }
public decimal UnitPrice { get; set; }
public int UnitsInStock { get; set; }
}
We use the Northwind database to play demo. If you haven't got Northwind database, you can view How to download and restore Northwind database in SQL Server
You can add a connection string to the App.config file. This is the best way to change the connection string without recompiling your source code
<configuration>
<connectionStrings>
<add name="cn" connectionString="Data Source=.;Initial Catalog=Northwind;User ID=sa;[email protected];" providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>
Add code to handle button click event
public partial class Form1 : MaterialSkin.Controls.MaterialForm
{
MaterialSkinManager skinManager;//manage theme
public Form1()
{
InitializeComponent();
//Init theme
skinManager = MaterialSkinManager.Instance;
skinManager.AddFormToManage(this);
skinManager.Theme = MaterialSkinManager.Themes.DARK;
skinManager.ColorScheme = new ColorScheme(Primary.Green600, Primary.Green900, Primary.Green900, Accent.LightBlue200, TextShade.WHITE);
}
private void materialRaisedButton1_Click(object sender, EventArgs e)
{
materialListView1.Items.Clear();
List<Product> list = DataAccess.GetProducts();//Retrieve data from products table
foreach (Product p in list)
{
ListViewItem item = new ListViewItem(p.ProductID.ToString());//Add data to Material ListView
item.SubItems.Add(p.ProductName);
item.SubItems.Add(p.UnitPrice.ToString());
item.SubItems.Add(p.UnitsInStock.ToString());
materialListView1.Items.Add(item);
}
}
}
VIDEO TUTORIALS