Windows Forms: Create Data Access with SQL Database in C#

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