How to Create Data Access with SQL Database in C#

To access your sql database, you can use Dapper ORM as a simple way. Dapper ORM 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.

How to access data from sql database in C#

To play the demo, you can create a new Windows Forms Application project.

Next, you need to install the Dapper package via NuGet Package Manager by right clicking on your project, then select the Manage NuGet Packages -> Search material skin, dapper and install it on your project.

You can do this by running the following command in the Package Manager Console

Install-Package Dapper
Install-Package MaterialSkin.2 -Version 2.3.1

Drag and drop MaterialListView, MaterialRaisedButton controls 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

You need to establish a connection to your SQL database. This typically involves creating a SqlConnection object. Make sure you have appropriate connection string details to retrieve data from sql database in c#.

You can also 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