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

Create Data Access with SQL Database using MaterialSkin, Dapper ORM in C#

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

public partial class Form1 : Form

to

public partial class Form1 : MaterialSkin.Controls.MaterialForm

Step 4: 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 should create a helper class, then add 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;Password=123@qaz;" 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