Post
Ask Question

DataAccessLayer API in C#

Introduction

 
DataAccessLayer is a code purely in c#. Only the DLL is released, to be referenced in the BLL layer.
 

Versatility and simplicity of use

  1. Connect to the following databases
    Oracle 10/11 - SQLServer 2008/2012/2014 - MySQL 5.X.X
     
  2. This allows you to manage the CRUD functions in the database.
     
  3. Only two methods exposed by the DLL are invoked:
    - ActionOnDB for Insert, Delete, Update
    - SelectFromDB for Select
     
  4. It is possible to choose the type of data returned: JSON or SERIALIZED data.
     
  5. It is possible to execute a stored procedure or a query.
 

Usability

 
The DLL is used as the last layer of architecture, with 3 or more levels. Below is an example that is only a standard of implementation and development of BLL, but the developer is free to develop and implement the BLL layer as he sees fit.
 
You refer to the DLL as being in a BLL project library.
 

In a base class (BaseManage) to inherit

 
Create a type variable DAL BaseMenageDAL objDAL. Set the constructor to be accepted as the type of connection and the connection string.
  1. public BaseManage(TypeConnection typeConnectionBLL, string ConnectionString) {  
  2.  try {  
  3.   strConnection = ConnectionString;  
  4.   this.typeConnectionBLL = typeConnectionBLL;  
  5.   objDAL = new BaseMenageDAL((BaseMenageDAL.TypeConnection) typeConnectionBLL, ConnectionString);  
  6.  } catch (Exception Error) {  
  7.   throw new Exception("Error on the procedure : " + System.Reflection.MethodInfo.GetCurrentMethod().ToString() + "-" + Error.Message);  
  8.  }  
  9. }   
Then, we create the 3 enums exposed in the DLL
  1. public enum TypeResult {  
  2.  JSON = BaseMenageDAL.TypeResult.JSON,  
  3.   SERIALIZED = BaseMenageDAL.TypeResult.SERIALIZED  
  4. }  
  5.   
  6. public enum TypeConnection {  
  7.  SQLServer = BaseMenageDAL.TypeConnection.SQLServer,  
  8.   Oracle = BaseMenageDAL.TypeConnection.Oracle,  
  9.   MySQL = BaseMenageDAL.TypeConnection.MySQL  
  10. }  
  11.   
  12. public enum ActionOnDB {  
  13.  storeInsert = BaseMenageDAL.Actions.storeInsert,  
  14.   storeUpdate = BaseMenageDAL.Actions.storeUpdate,  
  15.   storeDelete = BaseMenageDAL.Actions.storeDelete,  
  16.   storeSelect = BaseMenageDAL.Actions.storeSelect,  
  17.   queryInsert = BaseMenageDAL.Actions.queryInsert,  
  18.   queryUpdate = BaseMenageDAL.Actions.queryUpdate,  
  19.   queryDelete = BaseMenageDAL.Actions.queryDelete,  
  20.   querySelect = BaseMenageDAL.Actions.querySelect,  
  21. }  
To set the type of connection from the caller, create an enum type property TypeConnection.
  1. public TypeConnection typeConnectionBLL { get; set; } 
We will do two generic ways as shown in the example.
  1. internal int Managing(Object obj, string Store) {  
  2.  try {  
  3.   int ret = 0;  
  4.   ret = objDAL.ActionOnDB(obj, Store.Trim());  
  5.   objDAL = null;  
  6.   return ret;  
  7.  } catch (Exception Error) {  
  8.   throw new Exception("Error on the procedure : " + System.Reflection.MethodInfo.GetCurrentMethod().ToString() + "-" + Error.Message);  
  9.  }  
  10. }  
  11. internal object Selecting(Object obj, string Store, BaseMenageDAL.TypeResult type) {  
  12.  try {  
  13.   var ret = objDAL.SelectFromDB(obj, Store.Trim(), type);  
  14.   objDAL = null;  
  15.   return ret;  
  16.  } catch (Exception Error) {  
  17.   throw new Exception("Error on the procedure : " + System.Reflection.MethodInfo.GetCurrentMethod().ToString() + "-" + Error.Message);  
  18.  }  
  19. }  
The only methods for invoking the library are:
  1. ActionOnDB  for Insert,Delete,Update  
  2. electFromDB  for Select        
ActionOnDB to Insert, Delete, and Update.
 
SelectFromDB to Select.
  1. public class ManagingUser: BaseManage {  
  2.  public ManagingUser(TypeConnection typeConnectionBLL, string ConnectionString): base(typeConnectionBLL, ConnectionString) {}  
  3.  public class Registry {  
  4.   public string PK_Registry {  
  5.    get;  
  6.    set;  
  7.   }  
  8.   public string Name {  
  9.    get;  
  10.    set;  
  11.   }  
  12.   public string Surname {  
  13.    get;  
  14.    set;  
  15.   }  
  16.   public string Address {  
  17.    get;  
  18.    set;  
  19.   }  
  20.   public int ? _Eta {  
  21.    get;  
  22.    set;  
  23.   }  
  24.   public int ? Eta {  
  25.    get {  
  26.     return this._Eta.HasValue ?  
  27.      this._Eta.Value :  
  28.      (int ? ) null;  
  29.    }  
  30.   
  31.    set {  
  32.     this._Eta = value;  
  33.    }  
  34.   }  
  35.   private DateTime ? _Data = null;  
  36.   public DateTime ? Data {  
  37.    get {  
  38.     return this._Data.HasValue ?  
  39.      this._Data.Value :  
  40.      (DateTime ? ) null;  
  41.    }  
  42.   
  43.    set {  
  44.     this._Data = value;  
  45.    }  
  46.   }  
  47.  }  
  48.   
  49.  public int ManageUser(Object obj, ActionOnDB action) {  
  50.   int ret = 0;  
  51.   string Store = string.Empty;  
  52.   try {  
  53.    switch (typeConnectionBLL) {  
  54.   
  55.     case TypeConnection.SQLServer:  
  56.     case TypeConnection.MySQL:  
  57.      {  
  58.       switch (action) {  
  59.        case ActionOnDB.storeInsert:  
  60.         {  
  61.          Store = "sp_RegistryInsert";  
  62.         }  
  63.         break;  
  64.        case ActionOnDB.storeUpdate:  
  65.         {  
  66.          Store = "sp_RegistryUpdate";  
  67.         }  
  68.         break;  
  69.        case ActionOnDB.storeDelete:  
  70.         {  
  71.          Store = "sp_RegistryDelete";  
  72.         }  
  73.         break;  
  74.       }  
  75.      }  
  76.      break;  
  77.     case TypeConnection.Oracle:  
  78.      {  
  79.       switch (action) {  
  80.        case ActionOnDB.queryInsert:  
  81.         {  
  82.          Store = "insert into Registry (Name,Surname,Address,Eta) values ";  
  83.          Store += "( '" + ((Registry) obj).Name + "' ,";  
  84.          Store += " '" + ((Registry) obj).Surname + "' ,";  
  85.          Store += " '" + ((Registry) obj).Address + "' ,";  
  86.          Store += " " + ((Registry) obj).Eta + " )";  
  87.         }  
  88.         break;  
  89.        case ActionOnDB.queryUpdate:  
  90.         {  
  91.          Store = " UPDATE Registry  ";  
  92.          Store += " set Name= '" + ((Registry) obj).Name + "' ,";  
  93.          Store += " Surname='" + ((Registry) obj).Surname + "' ,";  
  94.          Store += " Address='" + ((Registry) obj).Address + "' ,";  
  95.          Store += " Eta=" + ((Registry) obj).Eta + " ";  
  96.          Store += " WHERE PK_Registry=" + ((Registry) obj).PK_Registry + " ";  
  97.         }  
  98.         break;  
  99.   
  100.        case ActionOnDB.queryDelete:  
  101.         {  
  102.          Store = "DELETE FROM Registry  ";  
  103.          Store += " WHERE PK_Registry=" + ((Registry) obj).PK_Registry + " ";  
  104.         }  
  105.         break;  
  106.       }  
  107.       obj = null;  
  108.      }  
  109.      break;  
  110.    }  
  111.    ret = Managing(obj, Store.Trim());  
  112.    return ret;  
  113.   } catch (Exception Error) {  
  114.    throw new Exception("Error on the procedure : " + System.Reflection.MethodInfo.GetCurrentMethod().ToString() + "-" + Error.Message);  
  115.   }  
  116.  }  
  117.  public object SelectUser(Object obj, ActionOnDB action, TypeResult type) {  
  118.   string Store = string.Empty;  
  119.   try {  
  120.    switch (typeConnectionBLL) {  
  121.     case TypeConnection.SQLServer:  
  122.     case TypeConnection.MySQL:  
  123.      {  
  124.       switch (action) {  
  125.        case ActionOnDB.storeSelect:  
  126.         {  
  127.          Store = "sp_RegistrySelect";  
  128.         }  
  129.         break;  
  130.       }  
  131.      }  
  132.      break;  
  133.   
  134.     case TypeConnection.Oracle:  
  135.      {  
  136.       switch (action) {  
  137.        case ActionOnDB.querySelect:  
  138.         {  
  139.          Store = "SELECT * FROM Registry";obj = null;  
  140.         }  
  141.         break;  
  142.       }  
  143.      }  
  144.      break;  
  145.    }  
  146.   
  147.    var ret = Selecting(obj, Store.Trim(), (BaseMenageDAL.TypeResult) type);  
  148.    return ret;  
  149.   } catch (Exception Error) {  
  150.    throw new Exception("Error on the procedure : " + System.Reflection.MethodInfo.GetCurrentMethod().ToString() + "-" + Error.Message);  
  151.   }  
  152.  }  
  153. }  
  154. }   
As shown, only two methods are invoked to manage the selection of the class base.
 
The caller (WEB-API) refers to the BLL. Create the object of the ManagingUser class, setting the type connection and the connection string.
  1. ManagingUser objManagingUser = new ManagingUser((BaseManage.TypeConnection)objRegistry.settingconnection.typeConnectionBLL, objRegistry.settingconnection.strConnection); 
In the Controller API two MenageUser and SelecUser actions are created.
 
In the case of Selection, you invoke the method SelectUser from the object ManagingUser setting the execution of a stored or query and the type of data returned.
 
In the case of Insert/Update/Delete, you invoke the method ManageUser from the object. ManagingUser affects the passing object and type of action to be performed.
  1. public class UserApiController: ApiController {  
  2.   
  3.  [HttpPost]  
  4.  [ActionName("MenageUser")]  
  5.  public int MenageUser(Registry objRegistry) {  
  6.    int ret = 0;  
  7.    ManagingUser objManagingUser = new ManagingUser((BaseManage.TypeConnection) objRegistry.settingconnection.typeConnectionBLL, objRegistry.settingconnection.strConnection);  
  8.    ret = objManagingUser.ManageUser(objRegistry, (BaseManage.ActionOnDB) objRegistry.settingconnection.actionOnDB);  
  9.    objManagingUser = null;  
  10.    return ret;  
  11.   }  
  12.   [HttpPost]  
  13.   [ActionName("SelectUser")]  
  14.  public string SelectUser(SettingConnection obj) {  
  15.   ManagingUser objManagingUser = new ManagingUser((BaseManage.TypeConnection) obj.typeConnectionBLL, obj.strConnection);  
  16.   var res = objManagingUser.SelectUser(null, (BaseManage.ActionOnDB) obj.actionOnDB, (BaseManage.TypeResult) obj.typeResult);  
  17.   objManagingUser = null;  
  18.   
  19.   
  20.   return res.ToString();  
  21.  }  
  22. }   
API is recalled from FrontEnd.
 
In the case of selection, the settings for connection to the API are set using the properties of the SettingConnection object.
  1. public void LoadSQL() {  
  2.  try {  
  3.   
  4.   SettingConnection objSettingConnection = new SettingConnection();  
  5.   objSettingConnection.strConnection = strSQL.Trim();  
  6.   objSettingConnection.typeConnectionBLL = SettingConnection.TypeConnection.SQLServer;  
  7.   objSettingConnection.actionOnDB = SettingConnection.ActionOnDB.storeSelect;  
  8.   objSettingConnection.typeResult = SettingConnection.TypeResult.SERIALIZED;  
  9.   ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////call api login    
  10.   dynamic data = Utility.CallAPI(uri, "UserApi/SelectUser", objSettingConnection);  
  11.   objSettingConnection = null;  
  12.   DataSet dataSet = JsonConvert.DeserializeObject < DataSet > (data.ToString());  
  13.   dgwSQL.DataSource = dataSet.Tables[0];  
  14.   Reload();  
  15.   objSettingConnection = null;  
  16.   
  17.  } catch (Exception Error) {  
  18.   throw new Exception("Error on the procedure : " + System.Reflection.MethodInfo.GetCurrentMethod().ToString() + "-" + Error.Message);  
  19.  }  
  20. }   
In the case of Insert/Update/Delete you set the connection settings to the API and pass the object (Registry).
  1. private void btnSQL_Click(object sender, EventArgs e) {  
  2.  try {  
  3.   
  4.   string API = string.Empty;  
  5.   SettingConnection objSettingConnection = new SettingConnection();  
  6.   objSettingConnection.strConnection = strSQL.Trim();  
  7.   objSettingConnection.typeConnectionBLL = SettingConnection.TypeConnection.SQLServer;  
  8.   Registry objRegistry = new Registry();  
  9.   API = "UserAPI/MenageUser";  
  10.   objRegistry.Name = txtname.Text;  
  11.   objRegistry.Surname = txtsurname.Text;  
  12.   objRegistry.Address = txtaddress.Text;  
  13.   objRegistry.Eta = Convert.ToInt16(txtEta.Text.Trim());  
  14.   objRegistry.Data = DateTime.Now;  
  15.   if (txtPKSQL.Text != "") {  
  16.    objRegistry.PK_Registry = txtPKSQL.Text;  
  17.    objSettingConnection.actionOnDB = SettingConnection.ActionOnDB.storeUpdate;  
  18.   } else {  
  19.    API = "UserAPI/MenageUser";  
  20.    objSettingConnection.actionOnDB = SettingConnection.ActionOnDB.storeInsert;  
  21.   }  
  22.   objRegistry.settingconnection = objSettingConnection;  
  23.   dynamic data = Utility.CallAPI(uri, API, objRegistry);  
  24.   LoadSQL();  
  25.   objSettingConnection = null;  
  26.   objRegistry = null;  
  27.   
  28.  } catch (Exception ex) {  
  29.   string ms = ex.Message;  
  30.  }  
  31. }   
An example windows application is released, but nothing prevents you from using the DLL with  Web App, WCF, WebAPI.
 
Download DLL, example Project, Script Create DB (SQL, MySQL):
 
http://samotech.altervista.org/utility.html
 

Summary

 
In this article, we learned about the DataAccessLayer API in C#.


Similar Articles