MSSQLLogin

How to use procedure in DapperClass in C# .Net

using Dapper;

using System;

using System.Data;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Windows.Forms;

class DataClassDapper

    {

        private string connectionString;

        private static DataClassDapper dataClass;

        public static DataClassDapper getInstance()

        {

            if (dataClass == null)

                dataClass = new DataClassDapper();

            return dataClass;

        }

        private DataClassDapper()

        {

            connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["PRDb"].ConnectionString;

        }

        public IDbConnection OpenConnection()

        {

            IDbConnection connection = new SqlConnection();

            connection.ConnectionString = connectionString;

            try

            {

                connection.Open();

            }

            catch (SqlException)

            {

                MessageBox.Show("Error Connecting database.");

            }

            return connection;

        }

  internal static bool ExecuteProc(string sql, List<SqlParameter> paramList = null)

        {

            try

            {

                using (IDbConnection connection = DataClass.getInstance().OpenConnection())

                {

                    DynamicParameters dp = new DynamicParameters();

                    if (paramList != null)

                        foreach (SqlParameter sp in paramList)

                            dp.Add(sp.ParameterName, sp.SqlValue, sp.DbType);

                

                    return connection.Execute(sql, dp, commandType: CommandType.StoredProcedure) > 0;

                }

            }

            catch (Exception e)

            {

                //do logging

                return false;

            }

        }

}

Your Model:

    public class Company : IEquatable<Company>

    {

        public int id { get; set; }

        public string name { get; set; }

               

        public string address { get; set; }

               

        public string phone { get; set; }

        public string fax { get; set; }

       

        public string email { get; set; }

        public string website { get; set; }

     }

using System;

using System.Linq;

using System.Text;

using System.Data.SqlClient;

using System.Threading.Tasks;

using System.Collections.Generic;

using PayrollManagementSystem.Model;

using PayrollManagementSystem.Database;

using Dapper;

public  class CompanyManager

    {

       public static CompanyManager companyManager;

        public CompanyManager()

        {

       

        }

  public bool AddNewCompanyProc(Company company)

        {

            string insertQuery = @"usp_insertCompany";

            List<SqlParameter> list = new List<SqlParameter>();

            SqlParameter param = new SqlParameter();

            param.ParameterName = "@name";

            param.Value = company.name;

            list.Add(param);

            SqlParameter param1 = new SqlParameter();

            param1.ParameterName = "@address";

            param1.Value = company.address;

            list.Add(param1);

         

            SqlParameter param2 = new SqlParameter();

            param2.ParameterName = "@phone";

            param2.Value = company.phone;

            list.Add(param2);

            SqlParameter param3 = new SqlParameter();

            param3.ParameterName = "@fax";

            param3.Value = company.fax;

            list.Add(param3);

            SqlParameter param4 = new SqlParameter();

            param4.ParameterName = "@email";

            param4.Value = company.email;

            list.Add(param4);

            SqlParameter param5 = new SqlParameter();

            param5.ParameterName = "@website";

            param5.Value = company.website;

            list.Add(param5);

            return DataClassDapper.ExecuteProc(insertQuery, list);

        }

}

CompanyEntryForm.cs

void SaveData()

        {

            try

            {

                Company company = new Company(0, tbxCompanyName.Text, tbxAddress.Text, tbxPhone.Text, tbxFax.Text, tbxEmail.Text, tbxWebAddress.Text);

                bool success= CompanyManager.getInstance().AddNewCompanyProc(company);

                if (success)

                {

                    KryptonMessageBox.Show("Company saved successfully!", "Add Company.", MessageBoxButtons.OK,

                        MessageBoxIcon.Information);

                }

                else

                {

                    KryptonMessageBox.Show("Company saved failed!", "Add Company.", MessageBoxButtons.OK,

                       MessageBoxIcon.Error);

                }

            }

            catch (Exception ex)

            {

            }

        }