Db Manager class for third tier (Data Access Layer) in asp.net

Question

I am following 3-tier architecture for building a website. For this I created a class named DB Manager.

Here is the class

namespace DAL
{
    class DBManager
    {
        private static DataTable dt = new DataTable();
        private static string ConnectionString = System.Configuration.ConfigurationManager.AppSettings["SQLSERVER"];

        public static int ExecuteNonQuery(string query)
        {
            int result;
            SqlConnection con = new SqlConnection(ConnectionString);
            SqlCommand command = new SqlCommand(query, con);

            try
            {
                con.Open();
                result = command.ExecuteNonQuery();
                con.Close();
            }
            catch
            {
                result = -1;
            }
            finally
            {
                con.Close();
            }

            return result;
        }

        public static DataTable ExecuteDataTable(string query)
        {
            SqlConnection con = new SqlConnection(ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter();
            dt = new DataTable();

            try
            {
                con.Open();
                da.SelectCommand = new SqlCommand(query, con);
                con.Close();
                da.Fill(dt);
            }
            catch
            {
                dt.Rows.Clear();
            }

            return dt;
        }

        public static string ExecuteScaler(string query)
        {
            SqlConnection con = new SqlConnection(ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter();
            string result = string.Empty;

            try
            {
                con.Open();
                da.SelectCommand = new SqlCommand(query, con);
                con.Close();
                dt = new DataTable();
                da.Fill(dt);

                if (dt.Rows.Count == 1 && dt.Columns.Count == 1)
                {
                    result = dt.Rows[0][0].ToString();
                }

            }
            catch
            {
                result = string.Empty;
            }

            return result;
        }

        public static bool ExecuteReader(string query)
        {
            bool result = false;
            SqlConnection con = new SqlConnection(ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter();

            try
            {
                con.Open();
                da.SelectCommand = new SqlCommand(query, con);
                con.Close();
                dt = new DataTable();
                da.Fill(dt);

                if (dt.Rows.Count == 1 && dt.Columns.Count == 1)
                {
                    if (dt.Rows[0][0].ToString() == "true")
                    {
                        result = true;
                    }
                    else
                    {
                        result = false;
                    }
                }
            } catch (Exception)
            {
                result = false;
            }

            return result;
        }
    }
}

And when I query like this

query = "insert into client(account_id, name, receive_email) values(" + accountId + ", '" + clientBLL.name + "', " + clientBLL.receiveMail + ");";

But this method of querying is supposed to be very bad idea. The good method is to give parameters into the SqlCommand which data you want to insert or retrieve.

Like this

query = @"insert into accounts(email, password, user_type) output inserted.id values(@email, @password, @userType);";
cmd.Parameters.Add("email", SqlDbType.NVarChar).Value = bll.email;
cmd.Parameters.Add("password", SqlDbType.VarBinary).Value = bll.password;
cmd.Parameters.Add("userType", SqlDbType.Bit).Value = 0;

But my DB Manager class does not support this method. I want a Db Manager class which support this Sql Command querying method not the old one (which I expressed earlier). How can I do this.

Oh boy! *How can I do this*... Obviously you need to add parameters to your DB manager. I have a the complete class that I can pass to you. But honestly, why don't you use something that is readily available? Check [Dapper](https://github.com/StackExchange/Dapper), it's a pretty cool library. — Racil Hilan, Jan 27 '18 at 02:57
yes! but I don't want to use library please can you give me the class? — , Jan 27 '18 at 03:11

Racil Hilan · Accepted Answer · 2018-01-27T04:06:08.917

Obviously you need to add parameters to your DBmanager class. It is also missing some other important methods. Here is a complete class that you can use. I use the standard ConnectionStrings section in the web.config rather than the AppSettings section. You can either modify your web.config (recommended), or modify this class.

//Author: Racil Hilan
//You are free to modify and use this class in any project, personal or commercial,
//as long as you include this note. The author assumes no responsibility whatsoever
//for any damage that results from using this class, and does not guarantee in any way
//the suitability of this class for any purpose.
using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace DataLayer {
  /// <summary>Class that encapsulates a SQL Server database connection and CRUD operations.</summary>
  public class SQLServerDb : IDisposable {
    private DbConnection _con;

    /// <summary>Default constructor which uses the "DefaultConnection" connectionString.</summary>
    public SQLServerDb() : this("DefaultConnection") { }

    /// <summary>Constructor which takes the connection string name.</summary>
    /// <param name="connectionStringName"></param>
    public SQLServerDb(string connectionStringName) {
      string connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
      _con = new SqlConnection(connectionString);
    }

    /// <summary>Executes a non-query command.</summary>
    /// <param name="command">The command to execute.</param>
    /// <returns>The count of records affected by the command.</returns>
    public int ExecuteNonQuery(DbCommand command) {
      int result = 0;
      if (command == null)
        throw new ArgumentException("Command cannot be null.");
      try {
        _con.Open();
        result = command.ExecuteNonQuery();
      }
      finally {
        _con.Close();
      }
      return result;
    }

    /// <summary>Executes a command that returns a single scalar value.</summary>
    /// <param name="command">The command to execute.</param>
    /// <returns>The value returned by executing the command.</returns>
    public object ExecuteScalar(DbCommand command) {
      object result = null;
      if (command == null)
        throw new ArgumentException("Command cannot be null.");
      try {
        _con.Open();
        result = command.ExecuteScalar();
      }
      finally {
        _con.Close();
      }
      return result;
    }

    /// <summary>Executes a command that returns a DataSet.</summary>
    /// <param name="command">The command to execute.</param>
    /// <returns>The DataSet returned by executing the ecommand.</returns>
    public DataSet ExecuteDataSet(DbCommand command) {
      DataSet ds = new DataSet();
      if (command == null)
        throw new ArgumentException("Command cannot be null.");
      try {
        DbDataAdapter ad = new SqlDataAdapter((SqlCommand)command);
        ad.Fill(ds);
      }
      finally {
        _con.Close();
      }
      return ds;
    }

    /// <summary>Creates a command with the given parameters.</summary>
    /// <param name="commandText">The SQL query to execute.</param>
    /// <returns>The created command.</returns>
    public DbCommand GetSqlStringCommand(string commandText) {
      return GetCommand(commandText, CommandType.Text);
    }

    /// <summary>Creates a command with the given parameters.</summary>
    /// <param name="commandText">The name of the stored procedure to execute.</param>
    /// <returns>The created command.</returns>
    public DbCommand GetStoredProcedureCommand(string commandText) {
      return GetCommand(commandText, CommandType.StoredProcedure);
    }

    /// <summary>Creates a command with the given parameters.</summary>
    /// <param name="commandText">The name of the stored procedure to execute.</param>
    /// <returns>The created command.</returns>
    private DbCommand GetCommand(string commandText, CommandType commandType) {
      DbCommand command = _con.CreateCommand();
      command.CommandType = commandType;
      command.CommandText = commandText;
      return command;
    }

    /// <summary>Adds an in parameter to a command.</summary>
    /// <param name="command">The SQL query to execute</param>
    /// <param name="name">The name of the parameter.</param>
    /// <param name="dbType">The type of the parameter.</param>
    /// <param name="value">The value of the parameter.</param>
    public void AddInParameter(DbCommand command, string name, DbType dbType, object value) {
      AddParameter(command, name, dbType, value, ParameterDirection.Input, 0);
    }

    /// <summary>Adds an out parameter to a command.</summary>
    /// <param name="command">The SQL query to execute</param>
    /// <param name="name">The name of the parameter.</param>
    /// <param name="dbType">The type of the parameter.</param>
    /// <param name="size">The maximum size, in bytes, of the data within the column.</param>
    public void AddOutParameter(DbCommand command, string name, DbType dbType, int size) {
      AddParameter(command, name, dbType, null, ParameterDirection.Output, size);
    }

    /// <summary>Adds a parameter to a command.</summary>
    /// <param name="command">The SQL query to execute</param>
    /// <param name="name">The name of the parameter.</param>
    /// <param name="dbType">The type of the parameter.</param>
    /// <param name="value">The value of the parameter.</param>
    /// <param name="direction">The direction for the parameter.</param>
    /// <param name="size">The maximum size, in bytes, of the data within the column.</param>
    private void AddParameter(DbCommand command, string name, DbType dbType, object value, ParameterDirection direction, int size) {
      var parameter = command.CreateParameter();
      parameter.ParameterName = name;
      parameter.DbType = dbType;
      parameter.Value = value ?? DBNull.Value;
      parameter.Direction = direction;
      if (size > 0)
        parameter.Size = size;
      command.Parameters.Add(parameter);
    }

    public void Dispose() {
      if (_con != null) {
        _con.Dispose();
        _con = null;
      }
    }
  }
}

You can use it with your example like this:

var db = new SQLServerDb();
string sql = @"INSERT INTO accounts(email, password, user_type) VALUES(@email, @password, @userType);";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@email", DbType.String, bll.email);
db.AddInParameter(cmd, "@password", DbType.String, bll.password);
db.AddInParameter(cmd, "@userType", DbType.Boolean, bll.userType);
DataRow dr = db.ExecuteDataSet(cmd).Tables[0].Rows[0];

To use it with stored procedures instead of queries, use the GetStoredProcedureCommand() instead of the GetSqlStringCommand() one.

The password is In byte array because I encrypted it before storing. — , Jan 27 '18 at 04:21
You should not encrypt passwords, you should hash them, and the hash is string. Use the built-in `CreateHash()` and `VerifyHashedPassword()` functions for that purpose. Never try to implement your own security, always use a well-known library. — Racil Hilan, Jan 27 '18 at 04:28
I am using md5 encryption and store it in the database of Binary data type. What to change now? Database column type binary to nvarchar ? — , Jan 27 '18 at 04:33
No, to `varchar`. The hash only has ASCII characters, so you don't need the Unicode `nvarchar`. — Racil Hilan, Jan 27 '18 at 04:36
I heard that encrypted password changes sometimes when we store it in varchar or nvarchar in database is it true? — , Jan 27 '18 at 04:42
Again, you should NOT encrypt passwords. You must hash them. And store them in `varchar`, not `nvarchar`. Now what do you mean by change from time to time? Use the built-in functions that I mentioned above (or a well-known library) and no need to worry how it is done. — Racil Hilan, Jan 27 '18 at 05:00

Db Manager class for third tier (Data Access Layer) in asp.net

1 Answers1