Xin chào các bạn, bài viết hôm nay mình sẽ chia sẽ đến các bạn Class SQLHelper.cs sử dụng Dapper ORM dùng thể thực hiện các truy vấn: Thêm, xóa, sửa... với dữ liệu SQL Server trong lập trình C# Winform.

[C#] Share Class SQLHelper using Dapper ORM Winform

Trong Class này mình đã viết những hàm làm việc với SQL server các bạn có thể sử dụng một cách dễ dàng vào nhanh chóng.

Class helper này cung cấp sẵn cho các bạn các phương thức trả dữ liệu về Object hoặc DataTable, truy vấn dữ liệu Async (bất đồng bộ).

Có thể thực hiện các câu lệnh truyền T-SQL hoặc sử dụng Store Procedure.

Những phương thức trong Dapper với Class SQLHelper.cs

1. Insert
2. Update
3. Delete
4. ExecProcedureDataAsDataTable
5. ExecProcedureDataAsyncAsDataTable
6. ExecQueryDataAsDataTable
7. ExecQueryDataAsyncAsDataTable
8. ExecProcedureData
9. ExecProcedureDataFistOrDefault
10. ExecProcedureDataAsync
11. ExecProcedureDataFirstOrDefaultAsync
12. ExecProcedureNonData
13. ExecProcedureNonDataAsync
14. ExecQueryData
15. ExecQueryDataFistOrDefault
16. ExecQueryDataAsync
17. ExecQueryDataFirstOrDefaultAsync
18. ExecQueryNonData
19. ExecQueryNonDataAsync
20. ExecProcedureSacalar
21. ExecProcedureSacalarAsync
22. ExecQuerySacalar
23. ExecQuerySacalarAsync

Để sử dụng thư viện này các bạn cần cài đặt thư viện Dapper từ Nuget với lệnh sau:

Install-Package Dapper -Version 2.0.35
Install-Package Dapper.Contrib -Version 2.0.35

Và 1 file DatabaseConfig, dùng để cấu hình thông tin kết nối SQLserver, để các bạn sử dụng.

1. Class DatabaseConfig.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Management;
using System.Text;
using System.Windows.Forms;

namespace NTA_Stone.Module
{
    class configDatabase
    {
        //NTAStone
        //user: NTAStone
        //pass: I5xl?44g
        public static string CONNECT_STRING_SQLITE = $@"Data Source={Application.StartupPath}\db\data.db;Version=3";
        public static string IP_SERVER_NAME = "";
        public static string USERNAME_DB = "";
        public static string PASSWORD_DB= "";
        public static string DATABASE = "";
        public static int TIME_OUT = 3;
        public static bool IS_CONNECTED = true;
        public static string CONNECTION_STRINGS="";        
        public enum LOGIN_STATUS
        {
            LOGGINED,
            LOCKED,
            UN_LOGGIN,
        }

        public static string ConnectString()
        {
            CONNECTION_STRINGS = $"Server={IP_SERVER_NAME};Database={DATABASE};User Id={USERNAME_DB};Password = {PASSWORD_DB}; ;Connection Timeout={TIME_OUT}";
            return CONNECTION_STRINGS;
        }

        public static bool CheckConnectionString(string connnection_string)
        {
            try
            {
                SqlConnection conn = new SqlConnection(connnection_string);
                conn.Open();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static string GetSerialHDD()
        {
            ManagementObjectSearcher searcher = new ManagementObjectSearcher("SELECT * FROM Win32_DiskDrive");


            foreach (ManagementObject wmi_HD in searcher.Get())
            {
                return wmi_HD["SerialNumber"].ToString();
            }
            return null;
        }
    }
}

2. Class SQLHelper.cs

using Dapper;
using Dapper.Contrib.Extensions;
using DevExpress.XtraEditors;
using NTA_Stone.Module;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace NTA_Stone
{
    class SQLHelper
    {
        public static long Insert<T>(T entityToInsert) where T : class
        {
            using (var connection = new SqlConnection(configDatabase.CONNECTION_STRINGS))
            {
                connection.Open();
                long re = 0;
                try
                {
                    re = connection.Insert(entityToInsert);
                }
                catch (Exception ex)
                {
                    re = 0;
                    XtraMessageBox.Show(ex.Message);
                }
                return re;
            }
        }

        public static bool Update<T>(T entityToUpdate) where T : class
        {
            using (var connection = new SqlConnection(configDatabase.CONNECTION_STRINGS))
            {
                connection.Open();
                var result = false;
                try
                {
                    result = connection.Update(entityToUpdate);
                }
                catch (Exception ex)
                {
                    result = false;
                    XtraMessageBox.Show(ex.Message);
                }
                return result;
            }
        }

        public static bool Delete<T>(T entityToDelete) where T : class
        {
            using (var connection = new SqlConnection(configDatabase.CONNECTION_STRINGS))
            {
                connection.Open();
                bool success = false;
                try
                {
                    success = connection.Delete(entityToDelete);
                }
                catch (Exception ex)
                {
                    XtraMessageBox.Show(ex.Message);
                }
                return success;
            }
        }
        public static DataTable ExecProcedureDataAsDataTable(string ProcedureName, object parametter = null)
        {
            using (var connection = new SqlConnection(configDatabase.CONNECTION_STRINGS))
            {
                connection.Open();
                DataTable table = new DataTable();
                try
                {
                    var reader = connection.ExecuteReader(ProcedureName, param: parametter, commandType: CommandType.StoredProcedure);

                    table.Load(reader);
                }
                catch (Exception ex)
                {
                    HelperMessage.Instance.ShowMessageOKWarning(ex.Message, "Lỗi trả về từ Server: ");
                }

                return table;
            }
        }



        public static async Task<DataTable> ExecProcedureDataAsyncAsDataTable(string ProcedureName, object parametter = null)
        {
            return await WithConnection(async c =>
            {
                var reader = await c.ExecuteReaderAsync(ProcedureName, param: parametter, commandType: CommandType.StoredProcedure);
                DataTable table = new DataTable();
                table.Load(reader);
                return table;
            });

        }


        public static DataTable ExecQueryDataAsDataTable(string T_SQL, object parametter = null)
        {
            using (var connection = new SqlConnection(configDatabase.CONNECTION_STRINGS))
            {
                connection.Open();
                var reader = connection.ExecuteReader(T_SQL, param: parametter, commandType: CommandType.Text);
                DataTable table = new DataTable();
                table.Load(reader);
                return table;
            }
        }

        public static async Task<DataTable> ExecQueryDataAsyncAsDataTable(string T_SQL, object parametter = null)
        {
            return await WithConnection(async c =>
            {
                var reader = await c.ExecuteReaderAsync(T_SQL, param: parametter, commandType: CommandType.Text);
                DataTable table = new DataTable();
                table.Load(reader);
                return table;
            });

        }

        public static IEnumerable<T> ExecProcedureData<T>(string ProcedureName, object parametter = null)
        {
            using (var connection = new SqlConnection(configDatabase.CONNECTION_STRINGS))
            {
                connection.Open();
                return connection.Query<T>(ProcedureName, param: parametter, commandType: CommandType.StoredProcedure);
            }
        }

        public static T ExecProcedureDataFistOrDefault<T>(string ProcedureName, object parametter = null)
        {
            using (var connection = new SqlConnection(configDatabase.CONNECTION_STRINGS))
            {
                connection.Open();
                return connection.QueryFirstOrDefault<T>(ProcedureName, parametter, commandType: CommandType.StoredProcedure);
            }
        }

        public static async Task<IEnumerable<T>> ExecProcedureDataAsync<T>(string ProcedureName, object parametter = null)
        {

            return await WithConnection(async c =>
            {
                return await c.QueryAsync<T>(ProcedureName, parametter, commandType: CommandType.StoredProcedure);
            });


        }

        public static T ExecProcedureDataFirstOrDefaultAsync<T>(string ProcedureName, object parametter = null)
        {
            using (var connection = new SqlConnection(configDatabase.CONNECTION_STRINGS))
            {
                connection.Open();
                return connection.QueryFirstOrDefaultAsync<T>(ProcedureName, parametter, commandType: CommandType.StoredProcedure).Result;
            }
        }

        public static int ExecProcedureNonData(string ProcedureName, object parametter = null)
        {
            using (var connection = new SqlConnection(configDatabase.CONNECTION_STRINGS))
            {
                connection.Open();
                //return affectedRows 
                return connection.Execute(ProcedureName, parametter, commandType: CommandType.StoredProcedure);
            }
        }

        public static int ExecProcedureNonDataAsync(string ProcedureName, object parametter = null)
        {
            using (var connection = new SqlConnection(configDatabase.CONNECTION_STRINGS))
            {
                connection.Open();
                //return affectedRows 
                return connection.ExecuteAsync(ProcedureName, parametter, commandType: CommandType.StoredProcedure).Result;
            }
        }

        public static IEnumerable<T> ExecQueryData<T>(string T_SQL, object parametter = null)
        {
            using (var connection = new SqlConnection(configDatabase.CONNECTION_STRINGS))
            {
                connection.Open();
                return connection.Query<T>(T_SQL, parametter, commandType: CommandType.Text);
            }
        }

        public static T ExecQueryDataFistOrDefault<T>(string T_SQL, object parametter = null)
        {
            using (var connection = new SqlConnection(configDatabase.CONNECTION_STRINGS))
            {
                connection.Open();
                return connection.QueryFirstOrDefault<T>(T_SQL, parametter, commandType: CommandType.Text);
            }
        }

        public static async Task<IEnumerable<T>> ExecQueryDataAsync<T>(string T_SQL, object parametter = null)
        {

            return await WithConnection(async c =>
            {
                return await c.QueryAsync<T>(T_SQL, parametter, commandType: CommandType.Text);
            });


        }

        public static T ExecQueryDataFirstOrDefaultAsync<T>(string T_SQL, object parametter = null)
        {
            using (var connection = new SqlConnection(configDatabase.CONNECTION_STRINGS))
            {
                connection.Open();
                return connection.QueryFirstOrDefaultAsync<T>(T_SQL, parametter, commandType: CommandType.Text).Result;
            }
        }

        public static int ExecQueryNonData(string T_SQL, object parametter = null)
        {
            using (var connection = new SqlConnection(configDatabase.CONNECTION_STRINGS))
            {
                connection.Open();
                return connection.Execute(T_SQL, parametter, commandType: CommandType.Text);
            }
        }

        public static async Task<int> ExecQueryNonDataAsync(string T_SQL, object parametter = null)
        {

            return await WithConnection(async c =>
            {
                return await c.ExecuteAsync(T_SQL, parametter, commandType: CommandType.Text);
            });



        }

        public static async Task<T> WithConnection<T>(Func<IDbConnection, Task<T>> getData)
        {

            using (var connection = new SqlConnection(configDatabase.CONNECTION_STRINGS))
            {
                await connection.OpenAsync(); // Asynchronously open a connection to the database
                return await getData(connection); // Asynchronously execute getData, which has been passed in as a Func<IDBConnection, Task<T>>
            }

        }

        public static object ExecProcedureSacalar(string ProcedureName, object parametter = null)
        {
            using (var connection = new SqlConnection(configDatabase.CONNECTION_STRINGS))
            {
                connection.Open();
                return connection.ExecuteScalar<object>(ProcedureName, parametter, commandType: CommandType.StoredProcedure);
            }

        }

        public static object ExecProcedureSacalarAsync(string ProcedureName, object parametter = null)
        {
            using (var connection = new SqlConnection(configDatabase.CONNECTION_STRINGS))
            {
                connection.Open();
                return connection.ExecuteScalarAsync<object>(ProcedureName, parametter, commandType: CommandType.StoredProcedure).Result;
            }

        }

        public static object ExecQuerySacalar(string T_SQL, object parametter = null)
        {
            using (var connection = new SqlConnection(configDatabase.CONNECTION_STRINGS))
            {
                connection.Open();
                return connection.ExecuteScalar<object>(T_SQL, parametter, commandType: CommandType.Text);
            }

        }

        public static object ExecQuerySacalarAsync(string T_SQL, object parametter = null)
        {
            using (var connection = new SqlConnection(configDatabase.CONNECTION_STRINGS))
            {
                connection.Open();
                return connection.ExecuteScalarAsync<object>(T_SQL, parametter, commandType: CommandType.Text).Result;
            }

        }

    }
}

Các bạn có thể tham khảo thêm về Dapper trong các bài viết trước của mình:

[C#] Giới thiệu thư viện Dapper ORM C#

C#] Hướng dẫn thêm, lưu, xóa, sửa, tìm kiếm sqlserver sử dụng thư viện Dapper ORM

Thanks for watching!

DOWNLOAD SOURCE