Skip to main content

Generic Database Manager Class for CRUD operations

The Database Manager Class represents to establish a connection to the SQL database and to perfrom CRUD(Create, Read, Update and Delete) operations. The "GetSQLConnection" method gives you the SQL connection instance and "CreateSQLDataParameter" creates data parameter. The "GetFilledDataSet" method gives you the filled dataset. The DB Manager class uses custom Sql exception. You can see the code below.
DBManager.cs:
using System;
using System.Data;
using System.Data.SqlClient;
 
public class DBManager
{
    private DBManager() { }
 
    public static IDbConnection GetSQLConnection(string connectionString)
    {
        try
        {
            IDbConnection dbConnection = null;
            dbConnection = new SqlConnection(connectionString);
            try
            {
                dbConnection.Open();
            }
            catch (SqlException sqlEx)
            {
                CustomSqlException exCustSql = new CustomSqlException(string.Concat("Error occurred
               while connecting to the SQL database. Server Name: ", ((SqlConnection)dbConnection).DataSource, 
               ". Exception Message : ", sqlEx.Message));
                throw exCustSql;
            }
 
            return dbConnection;
        }
        catch (Exception ex)
        {
            throw;
        }
    }
 
    public static IDbDataParameter CreateSQLDataParameter(IDbCommand idbCommand,  
string parameterName, object parameterValue, DbType dbType)
    {
        return CreateSQLDataParameter(idbCommand, parameterName, parameterValue, dbType, -1);
    }
 
    public static IDbDataParameter CreateSQLDataParameter(IDbCommand idbCommand, string parameterName, 
object parameterValue, DbType dbType, int size)
    {
        IDbDataParameter param = idbCommand.CreateParameter();
        param.ParameterName = parameterName;
        param.DbType = dbType;
        if (size != -1)
        {
            param.Size = size;
        }
        param.Value = parameterValue;
 
        return param;
    }
 
    public static DataSet GetFilledDataSet(IDbCommand idbCommand)
    {
        IDataAdapter iDataAdapter = DBManager.CreateSQLDataAdapter(idbCommand);
        DataSet dataset = new DataSet();
        iDataAdapter.Fill(dataset);
        return dataset;
    }
 
    public static IDataAdapter CreateSQLDataAdapter(IDbCommand idbCommand)
    {
        if (idbCommand is SqlCommand)
        {
            return new SqlDataAdapter((SqlCommand)idbCommand);
        }
        else
        {
            throw new NotSupportedException("Command Type " + idbCommand.GetType().FullName + 
" are not supported!");
        }
    }
}
CustomSqlException.cs:
using System;
 
public class CustomSqlException : Exception
{
    public CustomSqlException() : base() { }
    
    public CustomSqlException(string message) : base(message) { }
    
    public CustomSqlException(string message, Exception innerException) : base(message, innerException) { }
}
DatabaseSettings.cs: 
using System;
using System.Web.Configuration;
public class DatabaseSettings
{
    public static String ConnectionString
    {
        get
        {
            return WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        }
    }
}

Popular posts from this blog

Error : Task Scheduler - This task requires that the user account specified has Log on as batch job rights

In one of the production environment, we were running with low disk space problem because of the logs. To cleanup the old logs created a  task within the scheduler and have specified "Run whether use is logged on or not". The user account we used was the system logged on user. The task was running fine, after few months as a security policy the password expired. Since the old password expired we had to apply the new password for the task also, but this time we decided to create a new account specifically for the task scheduler with password never expire option. While setting up the new user account and password, run into the below issue: "The task required that the user account specified has log on as batch job rights" To fix the issue follow below steps: From Run type "secpol.msc /s" and enter, the local security policy snap in will open. Select "Local Policies" in MSC snap in Select "User Rights Assignment" Righ...

ISAPI & CGI Restriction configuration missing in IIS

In windows 7 by default ISAPI & CGI Restrictions are not configured. To enable ISAPI & CGI restrictions, GoTo -> Control Panel -> Programs -> Click on "Windows features on or off -> Expand Internet Information Services - >Expand World Wide Web Services ->Select CGI and ISAPI extensions and Click OK. After enabling, Check in IIS, Open your IIS and the feature will be available. What is ISAPI and CGI restrictions : ISAPI and CGI restrictions are request handlers that allow dynamic content to execute on a server. These restrictions are either CGI files (.exe) or ISAPI extensions (.dll). You can add custom ISAPI or CGI restrictions if the IIS configuration system allows this.

Implementing Parallelism With A SearchResultCollection

Implementing Parallel.ForEach with a SearchResultCollection: The below piece of code helps to check the given user is a part of the given active directory group. To implement Parallel.ForEach with a SearchResultCollection, do the casting with the SearchResult object and covert it into a list. //// Directory Searcher var directorySearcher = new DirectorySearcher(string.Format("(CN={0})", groupName)); //// Find group var searchResultCollection = directorySearcher.FindAll().Cast ().ToList(); Parallel.ForEach(searchResultCollection, searchResult => {  // enumerate members  var resultPropColl = searchResult.Properties;  Parallel.ForEach(resultPropColl["member"].Cast ().ToList(), member =>  {    var memberEntry = new DirectoryEntry("LDAP://" + member);    var userProperties = memberEntry.Properties;    if (GetUserNameFromProperties(userProperties, "sAMAccountName") == userName)    {      return true; ...