Recently while developing our demo project I had to a write a lot of repetitive code for executing the database queries (insert, update, delete). So, I have put all the repetitive code into a class library, imported and used the library for db related actions i.e. open, close, execute insert, update, and delete queries. I'm wondering if this approach is an efficient way to do so.
/*Connection manager class */
public abstract class ConnectionManager
{
private string ConnectionString { get; set; }
public ConnectionManager(string passedOnConstring)
{
if (!(String.IsNullOrWhiteSpace(passedOnConstring))
{
ConnectionString = passedOnConstring;
}
else
{
throw new ArgumentException("Invalid string connection, it cannot be null or empty.");
}
}
public abstract SqlConnection OpenConnection();
public abstract void CloseConnection();
}
public class MsSqlConnectionManager : ConnectionManager
{
private readonly string _conString;
private SqlConnection _dbconnect;
public MsSqlConnectionManager(string passedOnConstring) : base(passedOnConstring)
{
_conString = passedOnConstring;
}
public override SqlConnection OpenConnection()
{
try
{
_dbconnect = new SqlConnection(_conString);
if (_dbconnect.State != System.Data.ConnectionState.Open)
{
_dbconnect.Open();
}
return _dbconnect;
}
catch (SqlException)
{
throw;
}
}
public override void CloseConnection()
{
try
{
if (_dbconnect.State == System.Data.ConnectionState.Open)
{
_dbconnect.Close();
}
}
catch (SqlException)
{
throw;
}
}
}
/*DB Command Class */
public class DBCommand
{
private readonly ConnectionManager _conManager;
private readonly string _dbCommand;
private int _rowsAffected;
private readonly Dictionary<string, SqlParameter> _params;
public DBCommand(string dbCommand, ConnectionManager dbConnection, Dictionary<string, SqlParameter> queryParams)
{
if ((dbConnection != null) && (!String.IsNullOrWhiteSpace(dbCommand)) && (queryParams != null))
{
_conManager = dbConnection;
_dbCommand = dbCommand;
_params = queryParams;
}
else
{
throw new ArgumentException("Invalid connection string or database command, it cannot be null or empty.");
}
}
public int ExecCmdwithParams()
{
try
{
using (var _sqlCmd = new SqlCommand(_dbCommand, _conManager.OpenConnection()))
{
_sqlCmd.CommandType = CommandType.Text;
foreach (var keyValuePair in _params)
{
_sqlCmd.Parameters.Add(keyValuePair.Value);
}
_rowsAffected = _sqlCmd.ExecuteNonQuery();
}
Console.WriteLine(_rowsAffected);
//Return Affected Rows
}
catch (SqlException)
{
throw;
}
finally
{
_conManager.CloseConnection();
}
return _rowsAffected;
}
}
Then I have used the code to execute queries as follows:
/* Main program execution*/
private static void Main(string[] args)
{
string conString = @"";/* connection string */
string dbCommand = @"INSERT INTO [dbo].[TestTable]([Name]) VALUES (@Name)";
var _params = new Dictionary<string, SqlParameter>();
_params["Name"] = new SqlParameter("Name", "Paramatervalue");
var dbCon = new DBCommand(dbCommand, new MsSqlConnectionManager(conString), _params);
dbCon.ExecCmdwithParams();
// Console.ReadLine();
}