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

To access your sql database, you can use Dapper ORM as a simple way. As you know, Dapper ORM (object relational mapping) is an open source that's help you map data between the object with the corresponding table in the database, it also supports crud operations such as, insert update delete data to the sql server database.

To play the demo, you can create a new windows forms application project. Next, right click on your project, then select the Manage NuGet Packages -> Search material skin, dapper and install it on your project.

Drag the MaterialListView, MaterialRaisedButton from the visual studio toolbox to your winform, then layout your UI as shown below.

To create a material form, you need to change inherit from windows forms to the MaterialForm as the following c# code.

public partial class Form1 : Form

to

public partial class Form1 : MaterialSkin.Controls.MaterialForm

To retrieve data from sql database in c#, you should create the DataAccess class allows you to retrive data from the sql server database.

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 the helper class to help you get the connection string from the App.config file by adding the ConnectionString property allows you to get the 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 database you need to create the 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 will 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 the 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 initialize the MaterialSkin for your windows forms application.

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);
}

Add the button click event handler allows you to load data from the sql database, then add data to the MaterialListView control as the following c# code.

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 TUTORIAL