DBHelper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BookShop.DAL
{
    public class DBHelper
    {
        private static void PrepareCommand(IDbConnection con, IDbCommand cmd, string sql, CommandType ct, params IDataParameter[] parms)
        {
            if (con.State != ConnectionState.Open)
            {
                con.Open();
            }
            cmd.Connection = con;
            cmd.CommandText = sql;
            cmd.CommandType = ct;
            foreach (var item in parms)
            {
                cmd.Parameters.Add(item);
            }
        }

        public static int GetNonQuery(string conStr, string sql, CommandType ct, params SqlParameter[] parms)
        {
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection con = new SqlConnection(conStr))
            {
                PrepareCommand(con, cmd, sql, ct, parms);
                return cmd.ExecuteNonQuery();
            }
        }

        public static object GetScalar(string conStr, string sql, CommandType ct, params SqlParameter[] parms)
        {
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection con = new SqlConnection(conStr))
            {
                PrepareCommand(con, cmd, sql, ct, parms);
                return cmd.ExecuteScalar();
            }
        }

        public static DataSet GetSet(string conStr, string sql, CommandType ct, params SqlParameter[] parms)
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection(conStr))
            {
                using (SqlDataAdapter da = new SqlDataAdapter())
                {
                    PrepareCommand(con, da.SelectCommand = new SqlCommand(), sql, ct, parms);
                    da.Fill(ds);
                    return ds;
                }
            }
        }
    }
}